《高性能mysql》三创建高性能的索引

一、B-TREE索引

B-TREE索引的限制
（1）如果不是按照索引的最左列进行查找，则无法使用索引
（2）不能跳过索引中的列
（3）如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

二、哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码记录在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引的限制
（1）哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行，不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
（2）哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
（3）哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值得。
（4）哈希索引只支持等值比较查询，也不支持任何范围查询。
（5）访问哈希索引的数据非常快，除非有很多哈希冲突。

三、索引的优点

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

四、高性能的索引策略

1. 独立的列
如果查询中的列不是独立的，则mysql就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
下面是两个错误的例子：
select actor_id from sakila.actor where actor.id +1 = 5;
select * from user where to_days(create_time) <= 10;

2. 前缀索引和索引选择性
索引的选择性是指，不重复的索引项和数据表的记录总数的比值。索引的选择性越高则查询效率越高。因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，性能也是最好的。
前缀索引时一种能使索引更小，更快的有效方法，但另一方面也有其缺点，mysql无法使用前缀索引做order by 和group by， 也无法使用前缀索引做覆盖扫描。

3. 多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能，mysql5.0更新了一种索引合并策略，一定程度上可以使用表上的多个单列索引来定位指定的行。这种算法有三种变种：or条件的联合，and条件的相交，组合前两种情况的联合及相交。
当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个做联合操作时，通常需要耗费大量的cpu和内存资源在算法的缓存、排序和合并操作上。
该执行计划还不如直接走全表扫描，这样做不但会消耗更多的cpu和内存资源，还可能会影响查询的并发性。

4. 选择合适的索引列顺序
当不需要考虑排序和分组时，将选择性最高的列放在放在前面通常是很好的

五. 聚簇索引

当表有聚簇索引时，它的数据行实际上存储在索引的叶子页中，因为无法把数据行同时存储在两个不同的地方，所以一个表只能有一个聚簇索引。叶子页包含了行的全部数据，但是节点页只包含了索引列。
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的数据。包含相邻键值的数据可能会相距甚远。

聚簇索引的优点

1. 可以把相关数据保存在一起
2. 数据访问更快
3. 聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
   使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
   聚簇索引的缺点
4. 如果数据都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
5. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
6. 更新聚簇索引列的代价很高。因为会强制InnoDB将每个被更新的行移动到新的位置。
7. 基于聚簇索引的表在插入新行， 或者主键被更新导致需要移动行的时候，可能面临‘页分裂’的问题。
8. 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
9. 二级索引访问需要两次索引查找，而不是一次。

六. 覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”。
覆盖索引的优点：
索引条目通常远小于数据行大小，所以如果只需要读取索引

7. 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，这样的索引完全是累赘，建议删除。

8. 索引和锁
索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处，首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销。其次，锁定超过需要的行会增加锁争用并减少并发性。
如果不能使用索引查找和锁定行的话问题可能会更糟糕，Mysql会做全表扫描并锁住所有的行，而不管是不是需要。
InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。