高性能Mysql——创建高性能的索引

索引类型

我们知道，索引的实现有很多种，在Mysql中，索引是在引擎中实现的，所以没有统一的索引类型标准。

B-Tree索引
关于这方面的索引原理，参考我之前写的文章伸缩自如的ElasticSearch——数据库索引原理。

哈希索引
基于哈希表的实现，只有精确匹配索引所有列的查询才有效。
哈希索引只适用于某些特定的场合，比如数据仓库应用中有一种经典的“星型”schema，需要关联很多查找表，哈希索引就非常适合表的需求。

其他一些索引我们不多做介绍了。

索引是最好的解决方案吗？
索引并不总是最好的解决方案，一般来说，对于非常小的表，大部分情况全表扫描更加有效。

高性能的索引策略

独立的列

我们通常会看到一些查询不当的使用索引，或者使得Mysql无法使用已有的索引。

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

什么是独立的列呢？独立的列是指，索引列不能是表达式的一部分，也不能是函数的参数。

比如下面这个查询无法使用user_id列的索引：

select user_id from user_info where user_id+1=5;

Mysql无法自动解析这个方程user_id+1=5，所以不会使用索引。
下面是另一种错误：

select date_col where to_days(date_col)<=10;

我们应当始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引，但有时候这样做还不够，有个办法和之前学习的blob和text的排序很像：索引开始的部分字符。

这种前缀索引的方式，可以大大节约索引的空间，但是会降低索引选择性。索引选择性是指，不重复的索引和数据表总数目的比值，当然可以知道，1:1的时候，前缀索引的能完全区分字符列。
Mysql规定：对于blob和text还有很长的varchar类型，必须使用前缀索引。

为了找出最佳的前缀索引的长度，可以使用下面的方式计算索引选择性：

left(city,3)是取的city这个列的前3个字符。我们可以看到，当前缀长度达到6的时候，再增加前缀长度，选择性提升的幅度已经很小了（越接近1越好）。

既然找到了最合适的前缀长度，下面演示如何创建前缀索引：

ALTER TABLE sakila.city_demo
ADD INDEX `city_index`(`city`(6));

多列索引

多列索引不是为每个列创建独立的索引，也不能按照错误的顺序创建多列索引。

先来看第一个问题，为什么不是为多个列创建独立的索引。
在多个列上创建独立的单索引，大部分情况下并不能提高Mysql的查询性能。
我们来看看一个例子。

select film_id,actor_id from film_actor
where actor_id=1 or film_id =1;

在Mysql老版本里，会使用全表扫描。
不过在Mysql5.0之后的版本中，引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单索引。不过，这种情况更多的时候说明，表上的索引建立的很糟糕。

再来看另一个问题，索引列的顺序。正确的顺序依赖于使用该索引的查询是什么样的，并且需要考虑如何更好的满足排序和分组的需要。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照左列进行排序，然后是第二列…
这样，索引才可以按照升序和降序进行扫描，以满足order by、group by、distinct等要求。

由此可见索引的顺序至关重要。一个重要的经验法则是：将索引选择性最高的列放在最前列。

覆盖索引

这节的内容，之前也在伸缩自如的ElasticSearch——数据库索引原理中提到了，不做赘述。

覆盖查询都具有以下两个特点：

• 查询中的所有字段都属于一个索引；
• 查询所返回的所有字段也都属于同一索引内。

覆盖索引能够避免Innodb表进行索引的二次查找，避免了对主键的二次查询。另外，可以把随机IO变为顺序IO加快查询效率。

使用索引扫描来做排序

Mysql有两种方式生成有序的结果：

• 排序操作
• 按索引顺序扫描

只有当索引的列顺序与Order by子句的顺序完全一致时，并且排序的方向只能是一种（asc/desc）Mysql才能使用索引来做排序（最左前缀原则）。
如果查询需要关联多张表，那么只有当Order by子句全是第一个表的列时，才能使用索引排序。
范围查询不会走索引查询(不是不会走索引)，比如user_id in (x,x)以及 where id>1

最左前缀原则举例：比如 index(id,name,telephone)，那么，order by id或者order by id,name都是满足最左前缀的，但是 order by name,telephone是不满足的。

重复索引和冗余索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
有时会在不经意间创建了重复的索引，例如下面的代码：

CREATE TABLE test(
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

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

上图两个例子，index(a)是index(a,b)的前缀索引，但是如果ab索引的值很大，就有必要建立一个index(a)加快查询。
而第二个index(a,id)则是完全没有必要的，因为index(a)会自动添加一个主键信息，相当于是index(a,id)，但没有必要建立index(a,id)。

索引优化

MRR

有时候尽管有了索引，但是还会出现不走索引的情况发生，多见于范围查找、JOIN链接操作等情况。
比如下面的语句：
SELECT * FROM order_details_table WHERE order_id>100000 AND order_id<102000（order_id不是主键）

假如这张表有index(order_id)，index(order_id,product_id)的辅助索引（非聚集索引），但是仍然可能不会走非聚集索引。通过explain命令，用户可能会发现优化器选择了PRIMARY聚集索引，相当于走了表扫描，而不是索引扫描。

原因在于用户要选择的数据是整行信息，而order_id索引不是覆盖索引，所以在order_id查询到指定数据后，还需要一次聚集索引来查找整行数据的信息，因此变成了磁盘上离散的操作。如果访问的数据量很小，可能还会使用非聚集索引index(order_id)来做，但是当超过某一个阈值（一般是20%左右），优化器会选择使用聚集索引来查找数据，减少IO次数。

如果用户使用的磁盘是固态硬盘，随机IO速度非常快，有足够信心来确认非聚集索引会有更好的性能，可以选择force index(order_id)关键字来做查找。

在MYSQL5.6开始支持Multi-Range Read(MRR)优化，用来解决上面的问题：

• MRR在查询非聚集索引的时候，会根据得到的查询结果，按照主键进行排序，然后再进行聚集索引查找（书签查找），这样做还可以减少缓冲池里页被替换的次数。

ICP

在Mysql 5.6开始，Index Condition Pushdown(ICP)在进行索引查询的时候，会在索引取出的同时进行where条件判断来过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。

比如下面的查询语句，有联合索引index(zip_code,last_name)
SELECT * FROM people WHERE zipcode='94043' AND last_name LIKE '%lee%'

如果支持ICP优化，那么在索引取出后，就会进行WHERE条件的过滤。如果不启用ICP，会在取出全部zipcode='94043'的数据后，才做WHERE条件的过滤。