创建高性能的索引

---

一、索引基础

要理解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的“索引 ”部分：如果在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在MySQL中，存储引擎用类似的方法使用索引 ，其先在索引中找到对应值，然后根据匹配的索引记录找到 对应的数据行。假如要运行下面的查询：
mysql> SELECT first_name from sakila.actor where actor_id=5;
如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含 该值的数据行。

索引可以包含一个列或多个列的值。如果索引 包含多个列，那么列的顺序也很重要。因为MySQL只能高效地使用索引的最左前缀列。

索引结构中字段先后不受制于查询中相等判断条件表达式字段的顺序，而受制于非等条件判断表达式。

索引的类型
在MySQL中，索引是在存储引擎层而不是在服务器层实现的。

B-Tree索引
全值匹配：指和索引中所有的列进行匹配。例如匹配key的所有字段(last_name,first_name,dob)。
匹配最左前缀：只使用索引的第一列，例如只使用last_name
匹配列前缀：也可匹配某列的值开头部分。比如J开头的人，这里只使用索引的第一列。
匹配范围值：查找姓在Allen到Barry之间的人。治理只使用索引的第一列。
精确匹配某一列并范围匹配另一列：第一列last_name精确匹配，第二列first_name范围匹配。
只访问索引的查询：即查询只需要访问索引。

B-Tree索引限制
如果不是按照索引的最左列开始查找，则无法使用索引。如上面例子的索引无法查找名称为Bill的人，也无法查找某个特定生日的人。
不能跳过索引中的列。无法查找姓为Smath并且在特定日期出生的人，因为跳过了first_name列。
如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查询。例如查询where last_name=”Smath” and first_name like “%J” and dob=’1970-02-10’。这个查询只能使用索引的前两列，因为like是 一个范围条件。

哈希索引

哈希索引使用哈希表实现，只有精确匹配索引所有列的查询才有效。

哈希索引限制
哈希索引只能包含哈希值和行指针。所以不能使用索引的值避免读行。
哈希索引并不是按照索引值顺序存储的，所以无法进行排序。
哈希索引不支持部分索引匹配列查找。
哈希索引只支持等值比较查询。
访问哈希索引非常快，除非有哈希冲突。当哈希出现冲突时，会进行链表存储。
哈希冲突时，索引重建代价会很高。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引（adaptive hash index）”。当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

二、索引的优点

1.索引大大减少了服务器需要扫描的数据量。
2.索引可以帮助服务器避免排序和临时表。
3.索引可以将随机I/O变为顺序I/O

三、高性能的索引策略

独立的列
select actor_id from actor where actor_id+1=5
actor_id+1=5无法被解析成actor_id=4，所以要将索引列单独存放在比较符合的一侧。

前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢。
通常可以使用索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
select count(DISTINCT city)/count(*) from city_demo
ALTER table city ADD KEY (city(7))
索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
不重复索引值/记录总数接近0.031就可以使用了。

多列索引
多个单列索引会引起索引的合并，并不是最优的策略。

选择合适的索引列顺序
当不需要考虑排序和分组时，将选择性最高的列放在通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。
select * from payment where staff_id=584 and customer_id=30.
通过执行
select sum(customer_id=30),sum(staff_id=584) from payment
哪个列的基数小，就把哪列放在最前列

前缀索引的条件值基数比正常值高的时候，索引基本没什么用。比如索引的列满足全表所有的行。

聚簇索引
聚簇索引不是一种单独的索引类型，而是一种数据存储方式。InnoDB聚簇索引保存了B-Tree索引和数据行。

覆盖索引
如果索引包含所需要查询字段的值，就称为覆盖索引。
覆盖索引的好处：
1、索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
2、因为索引是按照列值顺序存储的（至少在单个页内是这样），所以对于I/O密集型的范围查询会比随机从磁盘读一行数据的I/O要少得多。
3、由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

覆盖索引的缺点：
1、插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据页到InnoDB表中速度最快的方式。但是如果不按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
2、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。插入的时候会面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。
3、二级索引可能比想象的大，因为二级索引的叶子结点保存了引用行的主键。
4、二级索引访问需要两次索引查找，要回表，对于InnoDB，自适应hash索引能够减少这样的重复工作。

二级索引查找行，需要找到叶子节点所对应的主键值，再去找聚簇索引对应的值
使用了覆盖索引EXPLAIN的Extra列会显示Using index

在InnoDB中按主键顺序插入行
随机插入的缺点：
写入的目标页可能不在内存缓存区，那么插入记录的时候需要先从磁盘读取目标页到内存中。这会导致大量的随机I/O。如果是顺序插入，由于是插入到上一个记录的后面，则大多数情况下（不需要开辟新页的情况）磁盘页是已经加载到内存里的。
因为写入是乱序的，InnoDB可能需要不断的做页分裂操作，以便为新的行分配空间。而页分裂会导致移动大量的数据，而且一次分裂至少要修改三个页而不是一个页。
由于频繁的分页，页面会变得稀疏并被不规则的填充，最后会导致数据碎片。

顺序的主键什么时候会造成更坏的结果？
对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会变成“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制，如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景会工作的更好。

假如索引覆盖了where条件中的字段，但不是整个查询涉及的字段，还是会回表 获取数据行。
select * from products where actor=’SEAN’ and title like ‘%APOLLO%’
可以使用延迟关联解决：
select * from products JOIN(
select product_id from products where actor=’SEAN’ and title like ‘%APOLLO%’
) t1 ON t1.product_id=products.id

使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序（不要和Extra列的”Using index”搞混淆了）。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样时，MySQL才能够使用索引来对结果做排序。
如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。否则，MySQL都需要执行排序操作，而无法利用索引排序。

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

冗余和重复索引
重复索引是没有必要的
冗余索引可以满足不同条件的查询。

冗余索引和重复索引有一些不同。如果创建了索引(A,B)，在创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当作索引(A)来使用（这种冗余只是对B-Tree索引来说的）。如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀列。另外，其它不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

索引和锁
InnoDB在二级索引上使用共享锁，主键索引使用排他锁。

---