Mysql_索引优化_6

前缀压缩索引

    对于前缀压缩索引的使用，是在MyISAM中使用，为了让更多的索引可以放入内存中，在某些情况下可以非常明显地提高性能。默认情况下只会对字符串进行压缩，可以通过参数设置对整数进行压缩。

MyISAM的压缩处理

    先保存索引块的第一个值，然后将其他值和第一个值进行比较，得到相同前缀的字节数和剩余的不同后缀部分，然后将这部分存储起来，如果看官有看雷记之前的更新会发现，这个处理与MyISAM对行指针采用的前缀压缩相同。

    对于压缩要说明的一点是，它可以带来更多的空间节约，但是对应的代价则是某些操作会变慢，这源于每个值得压缩前缀都依赖于嵌满的值，因此也注定MyISAM在查找时无法在索引块里使用二分查找，只能从头开始扫描，如果是倒序扫描的话，所有查找某一行得操作平均下来都需要扫描半个索引块，在CPU密集型应用中，进行得扫描是随机查早，压缩索引使MyISAM在索引查找上慢好几倍，类比到压缩索引的倒序扫描就更慢，另外也是由于压缩索引可能只需要很小的磁盘空间，在IO密集型应用中对于一些查询带来得收益会比成本高很多，再创建表的CREATE TABLE语句中以PACK_KEYS参数进行索引压缩方式的控制。

关于CPU密集型、IO密集型

    CPU密集型，为计算密集型，一般指系统的硬盘、内存性能相对CPU要好很多，系统运作大多数状况是CPU loading100%，CPU要读写IO，IO在很短的时间完成，因为CPU的轮片机制要处理很多运算，所以致使CPU 的loading非常高。

    IO密集型，一般指系统的CPU性能相对硬盘、内存要好很多，系统的运作大部分情况下CPU在等IO的读写，CPU的使用率不高，简单来说便是大量的输入输出，如读、写文件，传输文件，网络请求此类。

    对于CPU密集型应用我们会考虑其处理场景是否真的有必要进行多线程处理，因为存在线程切换带来的上下文切换，会出现时间、资源消耗，一般不建议开启多线程，对于这一问题的根本原因，我们在此先埋一个伏笔，待我们进行多线程并发优化时再细作分析。对于线程间上下文切换的细节，各位看官可以到雷记个人博客关于多线程并发的系列帖中了解。

关于冗余、未使用索引的一些坑

    mysql允许在相同的列上创建多个索引的初衷，雷记认为是Mysql为多字段索引创建放开的限制，但是很不幸地是我们在实际中因为脑洞太大，搞了很多在相同列上相同顺序、相同类型地索引，mysql对于这些重复地索引进行单独维护，优化器在优化查询的时候会对这些索引逐个考虑，所以势必会影响性能，所以建议发现这种问题地索引要立即索引，下面举一个最为常见也最为普遍地问题：

create table rookie（

    id int not null primary key，

    a int not null，

    b int not null，

    unique（id），

    index（id）

）engine=Innodb；

    操作者的本意为创建一个rookie表，主键为id，先加上一个唯一限制，然后再加上索引以供查询，实际上mysql的唯一限制和主键限制都是通过索引实现，所以这个写法实际上在相同的id列上创建了三个重复的索引，这种场景只有一个理由可以解释，那便是需要不同类型的索引来满足不同的查询需求，否则毫无意义。

    对于冗余索引，通常发生在为表添加新索引的时刻，如新增一个索引(A,B)，并非扩展以后索引(A)，或者将索引扩展为(A,id)，id作为主键，在innodb引擎情况时主键列已经包含在二级索引中，以上均为冗余索引。正常情况下，雷记建议尽量扩展已有索引而不是创建新的索引，这便存在一个关于取舍的博弈，在扩展已有索引会导致索引变得太大，进而影响使用其他地方使用这个索引的查询性能减慢问题，所以需要操作前充分调研一番是有必要的，但是这并不是总是很现实，因此我们需要冗余索引。

    有过数据经验的人会发现，表中的索引越多数据的插入速度就会越慢，新增索引会导致insert、update、delete的速度下降，这种现象在新增索引达到内存瓶颈时尤其明显，当我们被性能问题困扰至临界点时便会要找出这些冗余索引，然后删掉它们，对此借助外部工具比自己分析写查询访问information_schema表要来的实惠更多，这里雷记推荐common_schema或pt_duplicate_key_checker来分析表结构找出冗余和重复索引，在删除时要注意上一次更新提到的索引扫描-数列常量化是否因为当前存储引擎索引存储结构会发生失效的问题，可以了解一下pt_upgrade工具的使用，来检测执行计划中的索引变更现象。

关于索引与表的维护

    前文书里我们对合适索引的选择与使用进行了分析，现在我们来了解一下更为关键的部分-维护工作，大致可以分为三个阶段：

找到并修复损坏的表

    表损坏一般是由于硬件问题，mysql本身缺陷或者操作系统非正常运行导致，索引也会被破坏，损坏的索引会查询返回错误结果、主键冲突等异常问题，甚至更恶劣地数据库崩溃，在遭遇到古怪问题时，可以尝试check table检查是否发生表损坏，在发生问题时尽量优先考虑数据backup，然后再尝试利用alter等命令恢复表结构，这样可以避免数据的全丢失，至少给自己一次改过地机会，这种滋味说实话挺苦涩地。

    由于innodb的设计保证了其不容易被损坏，所以当innodb引擎的表发生损坏时一定是出现了严重错误，类似数据库硬件地内存、硬盘问题，DBA地错误操作，不存在执行查询让innodb表损坏，如果某条查询导致innodb数据损坏，则是遇到了bug，这次执行的查询并非根本原因。

    如果真的不幸发生了数据损坏，不要妄图简单的修复后继续使用，而是要排查出真正地问题所在永久的把问题解决掉，环境地数据有时并不会给我们提供太多地试错机会，关于innodb引擎下地恢复数据雷记后续会安排更新进行分析。

维护准确的索引统计信息

    mysql查询优化器通过records_in_range()和info()这两个api了解存储引擎内索引值地分布信息，以决定如何使用索引。

    records_in_range：向存储引擎传入边界值获取范围内的记录条数；

    info:获取返回各类型地数据，包括索引基数；

    如果存储引擎向优化器提供的扫描行信息不准确，优化器会使用索引统计信息估算扫描行数，在表没有统计信息或者统计信息不准确时优化器便会因此做出错误的决定，对此可以使用ANALYZE TABLE来重新生成统计信息来避免这个问题；

减少索引和数据地碎片

    btree索引会出现碎片化问题，碎片化的索引以近似无序地方式存贮于磁盘上，由于btree地随机磁盘访问会导致查询性能很低。

    表数据存储的碎片化分为三种情况：

        行碎片：数据行被存储为多个地方地多个片段中；

        行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储，十分影响全秒扫描地性能；

        剩余空间碎片：数据页中有大量的空余空间，会导致服务器读取大量不需要的数据；

    对这类问题可以采用OPTIMIZE TABLE或导出-导入地方式重新整理数据，对于MyISAM地情况可以通过排序算法重建索引消除碎片。对于不支持OPTIMIZE TABLE地存储引擎，可以通过ALTER TABLE table ENGINE=engine;的方式重建表，对于开启了expand_fast_index_creation参数的percona server可以消除表和索引的碎片化，对于标准版本的mysql只能消除表的碎片化，或者删除所有索引，然后重建表可以模拟这个percona server的功能。

结语

    关于索引的优化策略大致就是这样，在mysql中和索引这般相似地精妙设计，可说是浩如烟海，还需多做研究。从之前地更新中可以发觉mysql的存储引擎对于其行为操作有重要地决定作用，那么后续雷记找时间做一系关于mysql存储引擎地分享，敬请期待。另外为了方便大家更好的交流问题和想法，雷记组建了一个交流群，有兴趣加入的话，可以给公众号留言。