高性能mysql 第五章 索引部分总结

高性能索引

1。索引基础：索引的作用类似'目录'帮助Query来快速定位数据行。

1.1索引类型：

1.1.1

b-tree索引

b-tree（balance tree）索引：使用平衡树（非平衡二叉树）来保存索引值，叶子结点的到根节点的距离相差不超过1；对于不同的引擎，不同的索引类型叶子结点保存的值可以不同，同过索引找到数据行的方式也不同

可以使用b-tree索引的查询类型:1.全值索引；2.最左前缀；3.前缀索引；4.匹配范围；5.精准匹配某一列，范围匹配另一列（可以在看作最左前缀的扩大化）6.覆盖查询（只访问索引的查询）

局限：范围查询对复合索引的截断作用。

1.1.2

hash索引

hash索引：hash索引使用索引字段的hash值（散列值）作为索引寻址的标识，找到对应hash值时即可通过hash表对应的指针找到表数据。

散列值通过散列函数获得：书中对一般例子中使用的MD5（）与sha1（）两个散列函数的劣势做了分析（这两个函数本来是两种加密函数），两者得到的散列值长度过长，浪费空间；

hash冲突：使用一个hash函数传入两个不同的值可能产生一样的散列值，这就会导致hash冲突；解决办法一般为修改散列函数，与再散列；以及增加where条件人工消除hash冲突的影响三种。

文中提到：比较廉价的方式是截取md5（）返回值的一部分作为散列值。

应该注意到：myisam，innodb引擎并不支持hash索引。但是innodb有一个特殊的功能“自适应hash索引”，使其给予b-tree索引建立一个hash索引，具有一定hash索引性质。

1.1.3空间数据索引（R-tree）

MYISAM支持R-tree,但是mysql数据库在空间索引方面是弱势的，pgsql的postGIS做的比较好。

1.1.4全文索引

全文索引与b-tree索引不冲突，适用于match，against操作。第七章会详细讨论

1.1.5其他索引

这里提到了tokudb引擎使用的分型树索引（fractal  tree  index）；以及后面将要讲解的聚簇索引以及覆盖索引。

 

5.2

索引优点：

1.大大减少了服务器需要扫描的数据量；

2.帮助服务器避免了排序和临时表；

3.将随机io变为顺序io；

 

5.3高性能索引

5.3.1

左值才是索引列

索引不能使用表达式的一部分例：where a_id+1=2

5.3.2

前缀索引与索引的选择性

这一节详细讨论了前缀索引对于选择多常记录作为索引字段合适的问题：

提出了一个‘基数’的概念，即前缀对应唯一条目的数量与总数的笔直。前缀的基数应该接近完整列

例：count（distinct  tt（city，3））/count（*）

这个值可以称为选择性

注：部分字段后缀索引有时候更加有效

5.3.3

索引合并

5.0以后会有一新增了索引合并这一优化策略：

问题：   对于一条sql中有多个单列索引可以用到，但是由于优化器对where子句的解析原因，只用到一个（一个索引即可过滤大量数据）或者干脆直接全表扫描（5.0以前优化器的缺陷）。

解决1：使用union all等保留字对数据进行sql进行拆分；

解决2:5.0mysql以上提供了一种索引合并策略，explain以后再会有类似extra：using union（primary，id_key）的提示项。

说明：索引合并大多数时候说明索引不太符合实际运用，需要优化更新索引。

 

 5.3.4 合适顺序的索引

（1）索引项先后顺序对效率的影响

   索引项先后顺序对效率的影响：多列索引中索引顺序的考量，1.可以由前缀索引的知识了解到，索引的顺序对索引的适用情景有较大的限制，这里讨论的是索引顺序对索引效率的影响，可以通过选择性计算的方式，将选择性高的数据放在前面（因为可以一次性过滤掉更多的数据）。    需要注意的是多列索引的索引项顺序是需要整体考量的，因为一条query的查询索引优化可能是另一条query的负优化。

 

（2）聚簇索引

（1）中讨论的索引项的顺序对索引的效率的影响。这里讨论索引中数据顺序对磁盘io的影响：

    对于聚簇索引我们需要明白，它是innodb中的索引类型，在前面的章节我们了解innodb与myisam引擎索引结构的区别的时候说明了，两者对表数据的存放有一些区别，innodb不同于myisam将数据与索引分卡存放的方式，而是将数据行与索引存放在一起，存放了数据行的索引就是聚簇索引，数据行保存在顺序的索引叶子页中，这是io是顺序的，相比随即io能节省很多的io时间。 所以innodb表需要一个主键，没有显示定义的话，innodb也会隐式的定义一个主键索引顺序保存数据。   

聚簇索引的缺点

聚簇索引要求索引与数据行一起顺序保存就使得非顺序插入，修改等需要需要操作原有数据的操作需要对数据的移位（这样才能保证数据的顺序性），非顺序数据插入后最好使用optimize table重新组织一下表。

数据加入的过程中往往可能需要对数据也进行也分裂与也合并，导致数据页中的数据并不是紧靠的，会占用跟多空间，微微的降低全表扫描的效率。、

二级索引（innodb中的非主键索引）除索引项以外需要保存主键值。

 

5.3.5innodb与myisam数据分布对比、

这里在上文聚簇索引中基本都有涉猎，就不再赘述了。

 

5.3.6覆盖索引

顾名思义覆盖索引就是索引中包含索引包含查询需要的数据，包括select子句，与where子句。

优点：

只需要访问索引，而不需要访问数据行就能获取需要的数据；

缺点：

使用条件很苛刻，需要索引使用包含足够多的索引项才能使得索引在query中能被用到。

说明：对于不能使用覆盖索引的数据，可以采用‘延迟关联’的方法来处理，即在子查询中使用覆盖索引，返回数据在进行外层查询。即覆盖查询获取普通查询的条件值。

 

5.3.7索引排序

索引排序可以在explain的 type列看到index，说明使用了索引扫描排序，如果该索引不是覆盖索引，那么要根据扫描到的索引项到磁盘去读取数据，产生了大量的随机io所以时间会比全表扫描更久。当然比文件排序还是要快不少。

索引排序在既有order by又有limit的语句中很有用！

 

5.3.8压缩索引

myisam对索引的一种优化方式；

对索引进行前缀压缩，即将索引中相同的部分用一个简单的标识符替换如perform与performance可以替换为7；ance。

由于索引值做了简化，索引中存在依赖关系，影响了索引的查找，是一种cpu换io的优化。

5.3.9冗余与重复索引

应该尽量拓展目前已有的索引，而不是去建立新索引，多列索引与单列索引就很容易产生重复。

可以节省空间，同时方便了io

 

5.3.10未使用索引

除了重复索引与冗余索引，未使用的索引也是应该被清除的对象。可以通过打开userstate服务器变量，让服务器运行一段时间，再查询对应的information_schema.index_statistics就能知道索引的使用情况。

 

5.3.11索引与锁

innodb的行锁是加在索引上的，依赖于索引的存在。其中还有一个细节是innodb在二级索引上使用共享锁，一级索引使用排它锁。

 

5.4索引使用案例

5.4.1支持多种过滤条件

最左前缀的推广

可以通过将多列索引的最左前缀值罗列出来，来使用多列索引中的后缀部分。

 

5.4.2避免多个范围条件

多等值条件与范围条件在explain中显示的type都是range，但是范围条件会导致多列索引的后续索引项无法使用索引，而多等值条件没有这样的限制。

如果mysql能实现松散索引扫描范围条件就能突破限制

 

5.4.3优化排序

针对group by与limit的优化：使用limit可能使返回数据大量的被抛弃。

解决1：通过反范式，预先计算，缓存来处理。（从数据源上精细化数据）

解决2：通过延迟关联，使用覆盖索引，以最小的代价来确定得数据行，在读取数据行的其他具体内容。

 

5.5索引的维护

5.5.1定位表损坏并修复

myisam的系统崩溃可能导致表损坏，使用check table来排查，通过repair或者一个不做任何有效操作的alter语句来修复表。

innodb的表不容易损坏（应该是数据索引的高度纠缠，可以以我修复），innodb需要进入强制恢复模式来恢复表

 

5.5.2更新索引统计信息

mysql提供两个api来查看索引值分布：

1.records_in_range（），传入边界值来获取其中的记录数。myisam返回准确值，innodb返回估计值。

2.info（），返回包括索引基数在内的各种数据。

 

可以通过analyze table来重新生成统计信息来处理优化错误的情况。myisam将索引统计信息保存在磁盘中，通过analyze table命令来维护，innodb不在磁盘中储存维护这些信息，而是通过随机索引访问来评估并将其储存在内存中。

show information_schema.statistics。

 

5.5.3mysql碎片整理

碎片类型：1行碎片，2行间碎片，3剩余空间碎片

myisam可能三种碎片都有，而innodb不会有行碎片，它会移动短小的行并重写到一个片段中。

清理用optimize table 或者使用 不做任何有效操作alter。