MySQL索引

以下内容来自“高性能MySQL”的第5章“高性能的索引策略”。

 

一、 索引的优点：

总结下来主要有以下3点：索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随机I/O变为顺序I/O。

索引是否适合某个查询的评价方式（三星系统）：索引将相关的记录放到一起获得一星；索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的，一般对于中到大型的表索引非常有效。对于特大型的表，建立和使用索引的代价将随之增长，这种情况下需要可以区分出查询需要的一组数据而不是一条条的记录，则可以使用分区技术。

 

二、 高性能的索引策略：

1. 独立的列：指索引列不能是表达式的一部分，也不能是函数的参数。

2. 前缀索引和索引选择性：对很长的字符列进行索引时会让索引变的大且慢，具体解决可以是模拟哈希索引，或者是只索引开始的部分字符，这样可以节约索引空间从而提高索引效率，但是会降低索引的选择性。

索引的选择性：不重复的索引值（也称基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。唯一索引的选择性是1。

选择合适的前缀长度的可以找到最常见的值的列表，然后和最常见的前缀列表进行比较；或计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

缺点：MySQL无法使用前缀索引做“ORDER BY”和“GROUP BY”。

3. 多列索引：

4. 选择合适的索引列顺序：在一个多列“B-Tree”索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

如果选择索引列顺序的经验法则：将选择性最高的列放到索引的最前列。这个建议在某些场景可能有帮助，但是通常不如避免随机IO和排序那么重要。另外where子句中的排序、分组和范围条件等其它因素对查询的性能有很大的影响。

5. 聚簇索引：一个表只能有一个聚簇索引（覆盖索引可以模拟多个聚簇索引的情况），InnoDB将通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键做为聚簇索引。InnoDB只聚集在同一个页面中的记录。

优点：可以把相关数据保存在一起；聚簇索引将索引和数据保存在同一个“B-Tree”中，因此数据访问更快；使用覆盖索引扫描的查询可以直接使用节点中的主键值。

缺点：聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全放在内存中则访问数序没那么重要，聚簇索引也就没什么优势了；插入速度 严重依赖于插入顺序；更新聚簇索引列的代价很高；在插入新行，或者主键被更新导致需要移动行的时候可能面临“页分裂”的问题，会导致表占用更多的磁盘空间；聚簇索引可能导致全表扫描变慢，尤其是数据比较稀疏或由于页分裂导致数据存储不连续的时候；二级索引（非聚簇索引）可能比想象中的更大，因为其叶子节点包含了引用行的主键列。

如果在设计表和查询时能充分利用上面的有点，那就能极大地提升性能。

聚簇索引的主键列的影响：从性能角度考虑，使用UUID来做聚簇索引会很糟糕。主要在于：要写入的列可能已经不再缓存中，因此可能导致大量的随机I/O；写入是乱序的，InnoDB不得不频繁地做页分裂操作，会导致移动大量的数据；由于频繁的页分裂，也会变得稀疏并被不规则地填充，所以最终数据会有碎片。

对于高并发工作负载，在InnoDB中按顺序的主键插入可能导致明显的争用，主键的上届会称谓“热点”。另外“AUTO_INCREMENT”锁机制也会称为热点。

6. 覆盖索引：如果一个索引包含（覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。

好处：索引条目远小于数据行大小，如果只需要读取索引，则MySQL就会极大地减少数据访问量；因为索引是按照列值顺序存储的（至少在单个页内是这样），所以对于I/O密集型的范围查询会比随机从磁盘读取每行数据的I/O要少很多；对于某些存储引擎内存中只缓存索引，数据依赖于操作系统缓存，这种情况能减少一次系统调用；InnoDB的二级索引中保存呢了行的主键值，如果二级主键能覆盖查询，则可以避免对主键索引的二次查询。

7. 使用索引扫描来做排序：MySQL可以使用同一个索引既满足排序又用于查找行。

只有当索引的列顺序和“ORDER BY”子句的顺序完全一致，且所有列的排序方向都一样时，MySQL才能使用索引来对结果做排序。即：需要满足索引的最左前缀的要求。

一种额外情况是：前导列为常量的时候。即：多列索引中的前导列在where中指定取值做了条件过滤，然后在“ORDER BY”子句中和索引中其它列顺序一致。

8. 压缩（前缀压缩）索引：可以使用更少的空间，将更多的索引放入到内存中，代价是某些操作可能更慢。需要在CPU、内存资源与磁盘之间做权衡。

9. 冗余和重复索引：重复索引指在相同的列上按照相同的顺序创建的相同类型的索引。应当尽量避免，发现后也应该立即删除。

MySQL的唯一限制和主键限制都是通过索引来实现的，因此在创建索引时不用再为已有这种限制的列创建相同索引。

在大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是重复创建新索引。

一般来说，增加新索引将会导致插入、更新、删除等操作的速度变慢。

10. 索引和锁：InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。