深入理解MySql——高性能的索引策略

高性能索引的策略

创建高性能索引的几种方式：

1.独立的列
2.前缀索引和索引选择性
3.多列索引
4.选择合适的索引列顺序
5.主键索引自增和uuid的区别
6.覆盖索引
7.索引和锁

1.独立的列
我们要让mysql正当地使用索引，在查询中，这些索引的列要是“独立的列”。“独立的列”指的是索引列不能是表达式的一部分，也不能是函数的参数。

例如，我们不用用下面这个查询无法使用 actor_id 列的索引：

select actor_id from actor where actor_id + 1 = 5;

mysql无法自动解析actor_id + 1 = 5 ，我们要养成 始终将索引列单独放在比较符号的一侧。

2.前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢，对于blob,text或者很长的varchar类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

假设我们有一张City表

每个值都出现了45~65次，现在查找最频繁的城市前缀，先从3个前缀字母开始

每个前缀都比原来的城市出现的次数更多，因为唯一性下降了，然后我们增加前缀长度，直到前缀的选择性接近完整列的选择性。

计算合适的前缀长度另一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

假设完整列的选择性是0.0312，此时我们针对不同的列长度来分别计算列选择性。

查询显示当前前缀到达7的时候，再增加前缀长度，可选择性的提升幅度已经很小了。

只看平均选择性还是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，单如果数据分布很不均匀，就可能会有陷阱。如果观察前缀为4的最常出现城市的次数，可以看到明显不均匀：

前缀索引是一种能使索引更小，更快的有效方法，但另外一方面也有缺点，mysql无法对前缀索引做order by 和group by ,也无法使用前缀索引做覆盖扫描。

3.多列索引
很多人对多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者一些专家说“把where条件里的列都建立索引”这样模糊的建议导致的。

4.选择合适的索引列顺序
我们最容易遇到的困惑就是如何建立一个良好的索引列的顺序。
对于如何选择索引顺序有一个经验法则：
将选择性高的列放到索引的最前列。
根据B-tree的特性，这个时候索引确实能够最快过滤出需要的行。
假设有以下查询：
select * from payment where staff_id = 2 and customer_id = 584;
此时，可以distinct以下staff_id和customer_id的数量，确定那个列的可选择性更高。

5.主键索引自增和uuid的区别
我们创建两个表，分别为userinfo_incrid 和 userinfo_uuid ，然后主键分别为自增id和uuid，分别往每张表中插入100W行数据，检测一下时间：

注意到UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

我们看看往第一个表中插入数据时，索引发生了什么变化：

如图所示，因为主键的值是顺序的，所以把InnoDB的每一条记录都存储在上一条记录的后面，当达到页的最大填充因子的时候，下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。

对比一下uuid聚簇索引的表插入数据：

因为新行的主键值是没有规律的，并不一定比之前的大，所以InnoDB无法简单地把新行插入到索引的最后，二十需要为新的行寻找合适的位置——通常是中间位置——并且分配空间，这会增加许多的额外工作，并导致数据分布不够优化，下列是一些缺点：
1.因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量的数据，一次插入最少需要修改三个页。
2.由于频繁页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

把这些随机值载入到索引里后，还需要做一次optimize table 来重建表并优化页的填充。

因为这个案例可以看出，使用InnoDB时应该尽可能地按住键顺序插入数据，并且尽可能地使用单调增加的主键值来插入新行。

6.覆盖索引
通常大家都会根据查询的where条件来创建合适的索引，但是mysql也可以使用索引来直接获取数据（并不过原表）
，如果索引的叶子节点中已经包含要查询的数据，那么久没有必要再回表查询了。
如果一个索引包含所有需要查询字段的值，我们就称之为覆盖索引。

覆盖索引的好处：
1.索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大地减少访问数据量，对于I/O密集型的应用也非常有帮助，因为索引比数据更小，更容易放入没存中。
2.因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。

不是说所有类型的索引都可以成为覆盖索引，索引覆盖必须要存储索引列的值，而哈希索引，全文索引等都不存储列的值，所以mysql只能用b-tree索引做覆盖索引。

7.索引和锁

我们都知道InnoDB的行锁是由索引实现的，没有索引的话，InnoDB就会锁住所有的行，所以索引可以让查询锁定更少的行。

例如

select * from actor where actor_id <= 5 for update;
此时，如果有actor_id索引的话，只会锁住1~5行，否则将会进行全表扫描并锁住全部的行。