Mysql 索引策略

索引的策略

索引的星级

• 一星索引：索引将相关记录放在一起
• 二星索引：索引中的数据顺序和查找中的排序顺序一致
• 三星索引：索引中的列包含查询中需要的全部列

独立的列

如果查询中的列不是独立的，那么MySQL就不会使用索引

前缀索引和索引选择性

索引的选择性：不重复的索引值和数据表的记录总数的壁纸。索引选择性的值越高意味着可以过滤掉更多的行，意味着查询效率更高。

• 使用索引的前缀字符来代替整一个字符串。但是会降低索引选择性
• 如果是BLOB和TEXT数据或者是长字符串，必须定义前缀索引，因为MySQL不允许索引全文
• 最好尽可能使用足够长的前缀来提高索引选择性，不过短的省空间
• MySQL无法使用前缀索引进行ORDER BY 和 GROUP BY 操作

聚簇索引

聚簇索引是一种数据存储方式。它规定了数据在表中的物理存储顺序。意思是说索引项的顺序和表中记录的物理顺序一致。真实数据的物理顺序只能有一种，因此一张表最多只能穿件一个聚簇索引。

优点：

1. 把相关的数据保存在一起。
2. 数据访问更快。聚簇索引将索引和数据保存在同一个B树当中
3. 使用覆盖索引的查询可以直接使用叶节点的主键值

缺点：

1. 提高了IO密集型应用的性能。但是如果数据全部放在内存当中，访问的顺序就没有那么重要了
2. 插入的速度严重依赖于插入顺序。
3. 更新聚簇索引列的代价很高
4. 基于聚簇索引的表插入新行或者主键被更新导致需要移动行时，可能会产生页分裂的问题。
5. 可能导致全表扫描变慢
6. 二级索引（非聚簇索引）可能比想象的要大
7. 二级索引访问需要两次索引查找

最好避免使用随机的聚簇索引。在使用InnoDB的时候尽可能按主键顺序插入数据，并尽可能使用单调增加的聚簇键的值来插入新行

覆盖索引

覆盖索引：一个索引包含了所有要查询的字段的值。

优点：

1. 索引整体要比整一行数据行要小
2. 相比于随机从磁盘读取每一行的数据，IO密集型查询需要更少的IO操作
3. 许多数据引缓存索引的效率要比缓存数据的效率要高
4. 覆盖索引有助于InnoDB表，因为其包含了聚簇索引。

缺点：

1. 只能适用于B树索引，因为Hash索引，空间索引和全文索引都不存储键值。
2. 不是索引的引擎都支持
3. 可能会导致无法实现优化

索引作为排序

条件：

1. 索引的列顺序和ORDER BY 子句的顺序完全一致
2. 所有列的排序方向都一样
3. 如果需要关联多张表，ORDER BY子句的字段必须全部为第一张表
4. ORDER BY必须满足索引的最左前缀要求（除非where或者Join子句中对列指定了常量）
5. 索引列不能是范围条件或者是多个等于条件

包（前缀压缩）索引

MyISAM使用前缀索引来减少索引的大小。

压缩索引块的方式：先完全的存储第一个值，然后存储同样的前缀的字节数和不同的后缀的部分

前缀压缩的方式虽然能减少占用空间，但是会使某些操作变慢。MyISAM不支持二进制的二分查找，因此只能进行全局扫描。

对于CPU密集型的应用需要和磁盘之间进行权衡。对于IO密集型的应用，带来的好处更多

冗余和重复索引

重复的索引应该避免。如显式地为主键创建索引。因为MySQL默认会为主键建立索引，因此无需显式地为主键创建索引。

冗余索引，有索引（A，B），则索引（A，C）为冗余索引。索引多会导致插入，更新，删除数据变慢。

索引和锁

索引可以减少查询锁。这样会带来两个好处。

1. 减少锁定行时带来的开销
2. 减少锁争用和提高并发性

设置索引的技巧

过滤条件

考虑表中的所有选项。设计索引时，不要只为现有的查询考虑需要哪一些索引，还要考虑对查询的优化。如发现某些查询需要建立新的索引，但是这个索引又会降低另一个查询的效率。这时就应该考虑是否能优化原来的索引。应该同时优化查询和索引。

多范围查询

1. 尽量将需要使用范围查询的列放在索引的最后面（如果只存在一个范围查询条件）。也可以使用IN来代替范围查询。
2. 如果有多个范围查询条件，可以考虑将范围查询转化为一个简单的等值比较。如为过去七天登录过的用户设置active列，active为1表示过去七天登录过。

优化排序

1. 对于选择性非常低的列，可以添加特殊索引进行排序。
2. 使用延迟关联，使用覆盖索引返回需要的主键，然后根据主键关联原表获取需要的行

原则

1. 单行访问时很慢的。
2. 按顺序访问范围数据是很快的
3. 索引覆盖查询是很快的

扩展

聚簇索引和非聚簇索引

数据库索引原理