6. Mysql的索引（索引优化）

 在Mysql中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。
 索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为Mysql只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型

• B-Tree索引
   B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。图5-1展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。
  image.png
   B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

高性能的索引策略

• 1.独立的列
   如果查询中的列不是独立的，则Mysql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的函数。

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

mysql> SELECT actor_id from sakila.actor where actor_id +1 = 5;

 我们应该简化WHERE条件的习惯，下面是另一个常见的错误：

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

• 2.前缀索引和索引选择性（极少用到，暂时不考虑）

• 3.多列索引

 例如,表film_actor在字段flim_id和actor_id上各有一个单列索。对于下面的这个查询WHERE条件：

mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1

 在Mysql 5.0 以及更新版本，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union）,AND条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：

image.png

 索引合并策略是一种优化的结果，但实际上说了表上的索引建得很糟糕：

• 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

• 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

• 更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取，这会使得查询的成本被"低估"，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性。

如果在 EXPLAIN 看到有索引合并，应该好好检查一下查询的表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引

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

 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。

当不需要考虑排序和分组时，将选择性最高（命中高，数据量小）的列放在前面通常是很友好的。这时候索引的作用只是用于优化WHERE条件的查找

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是应该创建一个（staff_id,custom_id）索引还是应该颠倒一下索引顺序？可以跑一下查询确认这个表中值的分布情况，并确定哪个列的选择性更高。

mysql> SELECT SUM(staff_id = 2),SUM(customer_id = 584) FROM payment\G
***************************1.row********************************
SUM(staff_id = 2) = 7992
SUM(customer_id = 584) = 30

 根据前面的经验法则，应该将索引customer_id放到前面，因为对应条件值customer_id数量更小。

customer_id的选择性更高，所以答案是将其作为索引列的第一列

mysql>ALTER TABLE payment ADD KEY(customer_id, staff_id);

• 5.聚簇索引（TODO）

• 6.覆盖索引（TODO）

• 7.使用索引扫描来做排序

 Mysql 可以使用同一个索引既满足排序，又用于查找。因此，如果可能，设计索引是应尽可能同时满足这两种任务，这样是最好的。

 只有当索引的顺序和ORDER BY字句的顺序完全一致，并且所有列的排序方法（倒序或者正序）都一样时，Mysql才能够使用索引来对结果做排序。

 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。

• 8.压缩（前缀索引）索引（极少用到）

• 9.冗余和重复索引

 Mysql允许在相同列上创建多个索引，但Mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

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

 上面在相同的列上创建了三个重复的索引。

 冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余索引甚至是对B-Tree索引来说）。如果再创建索引（B,A），则不再冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。

 冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A，B）而不是扩展已有的索引（A）。还有一种情况是将一个索引扩展为（A，ID）,其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中，所以这也是冗余的。

应该尽量扩展已有的索引而不是创建新索引

• 10.未使用的索引

 如果存在服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。

 定位：设置userstates服务器变量打开，然后让服务器运行一段时间，再通过查询INFORMATION_SCHEMA,INDEX_STATSTICS就能查找每个索引的使用频率。

• 11.避免多个范围条件

image.png

 假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：

  WHERE eye_color IN('brown','blue','hazel')
      AND hair_color IN('black','red','blonde','brown')
      AND sex            IN('M','F')
      AND last_oneline > DATE_SUB(NOW(), iINTERVAL 7 DAY)
      AND age            BETWEEN 18 AND 25

 这个查询有一个问题：它有两个范围条件，last_oneline列和age列，Mysql可以使用last_oneline列索引或者age列索引，但无法同时使用它们。

 如果条件中只有last_oneline而没有age，那么我们可能考虑在索引的后面加上last_oneline列。如果我们能够将其中的一个范围查询转换为一个简单的等值比较，这就可以使用到两个索引。

• 12.减少索引和数据的碎片
   B-Tree索引可能会碎片化，这会降低查询的效率。

 InnoDB可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

总结

• 1.单行访问是很慢的。如果服务器从存储中读取一个数据块知识为了获取其中一行，那么就浪费了很多工作。最好读取的块可能包含尽可能多需要的行。使用索引可以创建位置引用以提高效率。

• 2.按顺序访问范围数据时很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将航海按组进行聚合计算了。

• 3.索引覆盖查询是很快的。如果一个索引包含了查询的所有列，那么存储引擎就不需要回表查找行。这避免了大量的单行访问，而单行访问是很慢的。