高性能MySQL第五章：创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响越发重要，但是如果设计了一堆糟糕的索引，反而会对性能带来负面影响，所以，正确的认识索引，幷根据索引的特性来设计索引是对查询性能优化最有效的手段

一、索引基础

1.索引的类型

B-Tree索引

存储引擎以不同的方式使用B-Tree索引，性能也各有优劣，例如，myISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储，然后myISAM索引通过数据的物理地址引用被索引的行，而InnoDB则根据主键引用被索引的行。
B-Tree数据结构中，所有的值都是顺序存储的，并且每一个叶子节点到根的距离相等，索引在B-Tree中的存储结构大致如下：

B-Tree索引有效的查询

1. 全值匹配：和索引中的所有列匹配
2. 匹配最左前缀：只使用索引的第一列
3. 匹配列前缀：只匹配某一列的值得开头部分
4. 匹配范围值：匹配某一列的范围
5. 精确匹配某一列幷范围匹配另一列
6. 只访问索引的查询：即只查询索引字段的值，不需要访问数据行

B-Tree的限制

• 如果不是按照索引的最左列开始查找，则无法使用所有
• 不能跳过索引中的列
• 如果条件是查询某个列的范围，则其右边的所有列都无法使用索引优化查找

哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才会有效，这是有哈希索引的特点决定的，哈希索引生成及使用原理如图

因为索引自身只需存储对应的hash值，索引索引的结构十分紧凑，查询速度非常快，当然也有其自身的局限性

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
• 哈希索引数据幷不是按照索引值顺序存储的所以无法排序
• 哈希索引页不支持部分索引列匹配查询，因为它需要使用所有索引列来计算哈希值
• 哈希索引只支持等值比较
• 当存在hash索引时，存储引擎会使用链表来存储冲突的数据行

空间索引

基本上用不到

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引适用于MATCH AGAINST操作，而不是普通的where条件操作

二、索引的优点

包括索引最主要的优化查询的特点，索引拥有以下三个优点

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

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

三、高性能索引的策略

1.独立的列

如果查询中的where条件不是独立的列，而是被函数操作，或者执行算法，则不会应用到索引，如下查询不会走索引

select actor_id from  actor where actor_id +1 = 5;

2.前缀索引和索引选择性

对于值很长的列，直接创建索引，会是索引变得大且慢，这时候我们就可以选择使用前缀索引，顾名思义就是使用值得前几个字符作为索引，创建语句如下

ALTER TABLE table ADD KEY(city(7))；

但是使用前缀索引会降低索引的选择性，通俗点说，就是会是索引的识别率降低，比如FC20302010和FC20402011,如果使用FC20作为前缀索引，那索引会同时扫描到这条数据。下面有两种方法来确定使用多长的前缀来作为索引可以增加索引的选择性。
方法1
1.执行下面语句，得到城市和数量的映射结构

select count(*) AS cnt, city from table GROUP BY city order by desc LIMIT 10

2.执行下面的语句，幷不断的调节n,直到得到的cnt和前缀的映射与上面语句相似

select count(*) AS cnt, left( city, n) from table GROUP BY left( city, n) order by desc LIMIT 10

最后得到的n就是要用来做索引的前缀长度

方法二：
1.执行下面语句

select count(distinct city)/count(*) from table

2.执行下面语句，幷不断的调节n,直到得到与上面语句相似的值

select count(distinct left(city,n))/count(*) from table

最后得到的n就是要用来做索引的前缀长度

3.多列索引

把where条件里面的列都建上索引，是一个非常错误的做法，如果一个where后面的多个查询列都需要索引，那就最好建立这几个列的联合索引。

4.选择合适的索引列顺序

在B-TREE中，索引列的顺序也意味着索引首先按照最左列进行排序，其次是第二列，等等，所以索引可以升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和 DISTINCT等子句的查询需求。

5.聚簇索引

聚簇索引幷不是单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行
当表有聚簇索引时，他的数据行是存放在索引的叶子页中，术语“聚簇”表示数据行和相邻的健值紧凑的存储在一起，因为无法同时把数据行存放在两个不同的地方

6.覆盖索引

覆盖索引指的是索引包含所要查找的所有列，这样，通过二级索引找到的叶子节点，不仅包含主键索引，还包含需要的列数据，这样就不用回表去进行第二次查询。

7.使用索引扫描来排序

当索引的列顺序和order by子句的顺序完全一致，并且所有的列的顺序都一样时，MySQL就能够使用索引来对结果进行排序。

8.压缩索引

mysql通过先完全保存索引块的第一个值，然后将其他的值和第一个值比较得到相同的前缀字节数和剩余的不同后缀部分，把这部分存储起来即可，但是这样的话，每次查询都需要从一个值开始扫描。

9.避免冗余和重复的索引

mysql允许创建多个索引，但是在索引过多的情况下，会降低mysql优化过程的性能，并且降低DDL的执行效率，因此我们应该注意避免使用多余的索引，比如 key（A）和k(A,B)就属于冗余索引。因为以A作为条件查询时，走key（A）和k(A,B)是一样的。

10.清除未的索引

有些索引可能永远都用不到，这些索引完全是累赘，应该删除。

四、案例

主要通过案列对比，说明索引的使用和一些注意点，这里不做累述，主要是用来对前面的理论进行支撑

五、总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

1. 单行访问很慢，需要读取一个数据库来找一行数据，所有应该使用索引创建位置引用来提升效率
2. 按顺序访问范围数据很快
3. 索引覆盖查询很快