MySQL之高性能索引

痛定思痛。 2022-05-06 03:04 309阅读 0赞

> 索引在MySQL中也叫做`键（key）`是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是表中的数据越来越大时，索引对性能的影响愈发重要。不恰当的索引会随着数据量逐渐增大，性能则会急剧下降。索引优化应该是对查询性能优化最有效的手段了。创建一个真正的”最优“经常需要重写查询。

### 文章目录 ###

*  索引基础
 *  索引的类型
 *   *  B-Tree索引
 *  高性能的索引策略
 *   *  独立的列
     *  前缀索引和索引选择性
     *  多列索引
     *  选择合适的索引列顺序
     *  聚簇索引
 *  InnoDB和MyISAM的数据分布对比
 *   *  在InnoDB中按主键顺序插入行
     *  覆盖索引
 *  使用索引扫描来做排序
 *  压缩（前缀压缩）索引
 *  冗余和重复索引
 *  索引和锁
 *  索引案例学习
 *   *  支持多种过滤条件
     *  什么是范围条件？
     *  避免多个范围条件
     *  优化排序
 *  维护索引和表
 *   *  找到并修复损坏的表
     *  更新索引统计信息
     *  减少索引和数据的碎片化
 *  总结

# 索引基础 #

*  在MySQL中，存储引擎首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
 *  索引可以包含一个或多个列的值。如果包含多列，那么列的顺序非常重要，因为MySQL只能高效地使用索引的最左前缀列。
 *  ORM工具很难生成适合索引的查询。

# 索引的类型 #

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

## B-Tree索引 ##

*  通常说的索引就是B-Tree索引，它使用B-Tree数据结构来存储数据（即每一个叶子节点都包含指向下一个子节点的指针，从而方便叶子节点的范围遍历）。5.1之前Archive不支持任何索引，直到5.1才开始支持单个自增列（AUTO\_INCREMENT）的索引。
 *  存储引擎以不同的方式使用B-Tree索引，性能也各不相同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则案中案原始数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
 *  B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

# 高性能的索引策略 #

## 独立的列 ##

如果查询中的列不是独立的，则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;

## 前缀索引和索引选择性 ##

*  有时候需要索引很长的字符列，这会使索引变得大且慢。一个策略是模拟哈希索引，但这样还不够。
 *  通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cadinality）和数据表的记录总数（\#T）的比值，范围从1/\#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
 *  一般情况下某个列前缀的选择性也是足够高的，足以满足性能。对应BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
 *  诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
 *  为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。
 *  首先，找到最常见的城市列表

mysql> SELECT COUNT(*) cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

*  查询3个前缀字母

mysql> SELECT COUNT(*) cnt, LEFT(city, 3) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7时比较合适

*  计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;

通常精确到小数点后3位，选择性就基本可用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。  
查询显示当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。`指字节数，而不是字符数。`

*  前缀索引语法

mysql> ALTER TABLE city_demo ADD KEY (city(7));

*  前缀索引是一种能是索引更小、更快的有效方法，但也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描（如果WHERE条件的列和返回的数据在索引结果中，那么就不需要查表）。
 *  一个常见场景是针对很长的十六进制唯一ID使用前缀索引。此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。
 *  有时候后缀索引（suffix index）也有用途（例如，找到某个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。

## 多列索引 ##

*  一个常见的错误是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
 *  有时候如果无法设计一个“三星”索引，那么不然忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。
 *  在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新版本引入了“索引”合并的策略（index merge）。一定程序上可以使用表上的多个单列索引来定位指定的行。
 *  在MySQL 5.0版本上，查询能够同时使用多个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合和相交。
 *  效果如下：

*************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: film_actor
             type: index_merge
    possible_keys: PRIMARY,idx_fk_film_id
              key: PRIMARY,idx_fk_film_id
          key_len: 2,2
              ref: NULL
             rows: 29
            Extra: Using union(PRIMARY,idx_fk_film_id); Using where

可以在type列看到`index_merge`，在Extra列中还可以看到嵌套操作。

*  索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：
 *  当出现服务器对多个索引建相交操作时（通常有多个AND条件），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引。
 *  当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
 *  更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL 4.1或者更早的时代一样，将查询改写成UNION 的方式往往更好。
 *  如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看看是不是最优的。可以通过参数`optimizer_switch`来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

## 选择合适的索引列顺序 ##

*  正确的索引列顺序依赖于使用该索引的顺序，并且同时需要考虑更好地满足排序和分组的顺序（这使用与B-Tree索引；哈希索引和其他类型的索引并不会像B-Tree索引一样按顺序存储数据）。
 *  在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等待。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT 等子句的查询需求。
 *  对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要。
 *  当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE 条件的查找。这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
 *  最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

## 聚簇索引 ##

*  聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，InnoDB实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中（leaf page）。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起 。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
 *  InnoDB通过主键来聚簇数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面内可能会相距甚远。
 *  聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。
 *  聚簇数据的一些重要的优点：

1.  可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚簇数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
2.  数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
3.  使用覆盖索引扫描的查询可以直接使用页节点中的主键值。  
    `如果在设计表和查询时能充分利用上面的优点，那就能极大的提升性能。同时，聚簇索引也有一些缺点：`

*  聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
 *  插入速度严重依赖于插入顺序。按照主键的插入顺序是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表。
 *  更新聚簇索引列的代价很高，因为会强制InnoDB将每个更新的行移动到新的位置。
 *  基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
 *  聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
 *  二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
 *  二级索引访问需要两次索引查找，而不是一次。二级索引页节点保存的不是指向行的物理位置的指针，而是行的主键值。
 *  这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次 。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

# InnoDB和MyISAM的数据分布对比 #

*  聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。
 *  使用`OPTIMEIZE TABLE命令做优化`，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。
 *  MyISAM按照数据插入的顺序存储在磁盘上。索引则按照排序单独存储在.MYI文件中。
 *  在MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
 *  InnoDB支持聚簇索引，所以使用非常不同的方式存储数据。
 *  在InnoDB中，聚簇索引就是“表”，所以不像MyISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVVC的回滚指针 以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的所有列。
 *  InnoDB的二级索引存储的是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针让二级索引占用更多的空间。好处是，InnoDB在移动行时无需更新二级索引中的这个“指针”。

## 在InnoDB中按主键顺序插入行 ##

*  如果在InnoDB表中没有什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO\_INCREAMENT自增列。这样可以保证数据行是按顺序插入的，对应根据主键做关联操作的性能也会更好。
 *  最好是避免随机（不连续且值的分布范围非常大）的聚簇索引，特别是对于I/O密集型的应用。例如，使用UUID作为聚簇索引时非常槽糕的：它使数据完全没有聚集特性，完全随机的。这会导致索引占用的空间更大，原因是页分裂和碎片导致的。
 *  InnoDB默认的填充因子是页大小的15/16，留出部分空间用于以后修改。二级索引是不一样的。
 *  UUID聚簇索引，因为新行的主键值不一定比插入的大，所以InnoDB需要为新的行寻找合适的位置并且分配空间。这会造成很多的额外工作，并导致数据分布不够优化。缺点如下:

1.  写入的目标页可能已经刷到磁盘上并从缓存中删除，或者是还没有加载到内存中，InnoDB在插入之前不得不从先从磁盘读取目标页到内存中。这将导致大量的随机I/O。
2.  因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
3.  由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。
4.  在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZF TABLE来重建表并优化页的填充。

*  使用InnoDB时应该尽可能的按主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值来插入新行。
 *  顺序的主键什么时候会造成更坏的结果?  
    对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO\_INCREMENT 锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb\_autoinc\_lock\_mode 配置。如果你的服务器版本还不支持innodb\_autoinc\_lock\_mode 参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。

## 覆盖索引 ##

*  设计优秀的索引应该考虑到整个查询，而不仅仅是`WHERE`条件部分。如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们称之为“覆盖索引”。
 *  覆盖索引能够极大的提高性能，无须回表。会带来以下好处：

1.  索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对IO密集型的应用也有帮助，因为所有比数据更小，更容易全部放入内存中（MyISAM能压缩索引以变得更小）。
2.  因为索引是按照列值顺序存储的（至少单个页内如此），所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。对于某些存储引擎，例如MyISAM和Percona XtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
3.  一下存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
4.  由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

*  并不是索引类型的索引都可以成为覆盖索引。覆盖索引必须存储索引列的值，而哈希索引、空间索引和全文索引等并不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。
 *  当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN 的Extra 列可以看到“Using index”的信息 。`type列表示这个查询访问数据的方式，或者说MySQL查找行的方式。MySQL手册称之为连接方式（join type）。`
 *  索引覆盖查询还有很多陷阱可能导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引可以覆盖。如果索引覆盖了WHERE条件中的字段，但不是涉及整个查询的字段。MySQL5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。
 *  如以下查询：

mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%'\G;
    
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: products
             type: ref
    possible_keys: ACTOR,IX_PROD_ACTOR
              key: ACTOR
          key_len: 52
              ref: const
             rows: 10
            Extra: Using where

这里索引无法覆盖该查询，原因如下：

1.  没有任何索引可以覆盖这个查询。因为查询选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上MySQL还有一个捷径可以利用：WHERE条件列是索引可以覆盖的，因此MySQL可以利用该索引过滤之后在读取需要的数据行。
2.  MySQL不能在索引中执行`LIKE`操作。这个底层存储引擎API的限制，MySQL5.5或更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于等）。MySQL在索引中可以做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作。

*  可以重写查询并巧妙地设计索引。

mysql> EXPLAIN SELECT *
    
    
            -> FROM products
    
    
            ->    JOIN (
    
    
            ->       SELECT prod_id
    
    
            ->       FROM products
    
    
            ->       WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
    
    
            ->    ) AS t1 ON (t1.prod_id=products.prod_id)\G
    
    
        *************************** 1. row ***************************
                   id: 1
          select_type: PRIMARY
                table: <derived2>
                       ...omitted...
        *************************** 2. row ***************************
                   id: 1
          select_type: PRIMARY
                table: products
                       ...omitted...
        *************************** 3. row ***************************
                   id: 2
          select_type: DERIVED
                table: products
                 type: ref
        possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR
                  key: ACTOR_2
              key_len: 52
                  ref:
                 rows: 11
    Extra: Using where; Using index

我们把这种方式叫做延迟关联（deferred join），因为延迟了对列的访问。

*  当数据量很小的情况下，子查询带来的成本返回而从表中直接提取数据行更高。
 *  InnoDB的二级索引的叶子节点包含了主键的值，可以利用这些“主键”来做覆盖查询。
 *  MySQL5.6版本包含了在存储引擎API上的“索引条件推送”（index condition pushdown）。目前的API设计不允许MySQL将过滤条件传到存储引擎层，只能吧数据从存储引擎拉倒服务器，再根据查询条件过滤。

# 使用索引扫描来做排序 #

*  MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN的type列的值为index，则说明MySQL使用了索引扫描来做排序。
 *  扫描索引本身是很快的，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
 *  MySQL可以使用同一个索引即满足排序，又满足查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
 *  只有当索引列的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样时，MySQL才能够使用索引来对结果排序（如果需要按不同方向排序，一个技巧是存储该列值的反转串或相反数）。如果查询需要关联多张表，则只有ORDER BY使用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句需要满足索引的最左前缀的要求；否则MySQL需要执行排序操作，而无法利用索引排序。
 *  有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。  
    例如索引列`（rental_date, inventory_id, customer_id）`，MySQL可以使用下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序操作（filesort，其实并不一定使用磁盘文件）。

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G;

即使不满足索引的最左前缀要求，也可以用于索引排序，这是因为所有的第一列被指定为一个常数。这样组合在以前，就形成了索引的最左前缀。

**下面这些查询无法使用索引做排序：**

- 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：
    ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC
    - 下面这个查询的ORDER BY子句中引用了一个不在索引中的列：
    ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
    - 下面这个查询的WEHRE和ORDER BY中的列无法组合成索引的最左前缀：
    ... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
    - 下面这个查询索引列的第一列是范围条件，所以MySQL无法使用索引的其余列：
    ... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
    - 这个查询在inventory_id列上有多个等于条件。对应排序来说，这也是一种范围查询：
    ... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;

*  下面理论上是可以使用索引做关联排序的，但优化器在优化时将film\_actor表作为关联的第二张表，所以实际上无法使用索引：

mysql> explain select actor_id, title from film_actor inner join film using(film_id) order by actor_id\G;

使用索引做排序的一个最重要的用法是当查询同时有ORDER BY 和LIMIT 子句的时候。

# 压缩（前缀压缩）索引 #

*  MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引放到内存中，在某些情况下能极大的提高性能。默认只压缩字符串，可以通过参数设置来对整数进行压缩。
 *  MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。
 *  压缩块使用更少的空间，代价是某些操作可能更慢。 因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC ——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。
 *  测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。
 *  可以在CREATE TABLE 语句中指定`PACK_KEYS`参数来控制索引压缩的方式。

# 冗余和重复索引 #

*  MySQL运行在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在查询的时候也需要逐个地进行考虑，这会影响性能。
 *  重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该尽量避免，一旦发现应该立即移除。
 *  索引类型不同，并不算重复的索引。例如经常有很好的理由创建KEY（col）和FULLTEXT KEY（col）两种索引。
 *  MySQL中主键限制和唯一限制都是通过索引来实现的。
 *  冗余索引和重复索引不同。如果创建了索引(A,B)，再创建A就是冗余索引，因为这只是前一个索引的前缀索引（只是针对B-Tree索引来说）。另外，其他不同类型的索引（例如哈希或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
 *  冗余索引通常发生在为表添加新索引的时候。例如，有人可能增加一个新的索引（A,B）,而不是扩展已有的索引列（A）。还有一种情况是将一个索引扩展为（A,ID），ID为主键，对于InnoDB来说主键已经包含在二级索引中了，所以这也是冗余的。
 *  大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
 *  表中的索引越多插入速度会越慢。一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
 *  解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。可以通过写一些复杂的访问INFORMATION\_SCHEMA 表的查询来找，不过还有两个更简单的方法。可使用Shlomi Noach的common\_schema 中的一些视图来定位，common\_schema 是一系列可以安装到服务器上的常用的存储和视图（[http://code.google.com/p/common-schema/][http_code.google.com_p_common-schema] ）。这比自己编写查询要快而且简单。另外也可以使用Percona Toolkit中的pt-duplicate-key-checker ，该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说，使用外部的工具可能更合适些；如果服务器上有大量的数据或者大量的表，查询INFORMATION\_SCHEMA 表可能会导致性能问题。

# 索引和锁 #

*  索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。锁定超过需要的行会增加锁争用并减少并发性。
 *  InnoDB只有在访问行的时候才会对其加锁，而索引能减少访问行的次数，从而减少锁的数量。`但这只有在InnoDB在存储引擎层能够过滤所有不需要的行时才有效。`如果索引无法过滤掉无效的行，则这些数据返回到服务器层以后，MySQL服务器才能应用WHERE子句，这时已经无法避免锁定行了。InnoDB已经锁住了这些行，到适当的时候才释放：在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;

上述查询会导致InnoDB锁定第一行，尽管并不需要。因为MySQL的执行计划是索引范围扫描。

*  就像上述展示的一样，即使使用了索引，MySQL也可能会锁定一些不需要的数据。如果不使用索引的话会更糟糕，MySQL会做全表扫描并锁定所有的行而不管需要不需要。
 *  InnoDB在二级索引上使用共享（读）锁，但访问主键索引时使用排它（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或非锁定查询要慢很多。

# 索引案例学习 #

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢?

首先要考虑的是使用索引来排序，还是检索数据在做排序。使用索引排序会严格限制索引和查询的设计。`WHERE条件中的范围查询和索引排序无法共同使用。`

## 支持多种过滤条件 ##

*  在有更多不同值的列上创建索引的选择性通常更好。因为可以让MySQL更有效的过滤掉不需要的行。
 *  我们建议在创建不同组合索引的时候将（sex,country）列作为前缀，它们的使用频率很高，虽然选择性不高。当查询中没有sex列时可以使用`IN`的“诀窍”绕过。例如，如果查询中不限制性别，则可以在查询中新增AND SEX IN(‘m’, ‘f’)来让MySQL选择该索引。只有加上这个条件才能匹配MySQL的最左前缀。但如果列中有太多不同的值，就会让IN()列表太长，这样做就不行了。
 *  基本原则是：我们应该考虑表中索引的项。我们应该在优化索引和优化查询中找到平衡点。
 *  查询只能使用索引的最左前缀，直到遇到第一个范围条件列。当前，可以使用IN()来代替范围查询，但并不总能转换。我们应该尽量将范围查询放到索引的后面，以便优化器能使用尽可能多的索引列。
 *  在索引中加入尽可能多的列，并通过IN()的方式覆盖那些不在WHERE子句中的列。但要注意，每多一个IN()条件，优化器需要做的组合都将以指数形式增加，最终会极大地降低查询性能。

## 什么是范围条件？ ##

从EXPLAIN中很难看成MySQL是要查询范围值，还是查询列表值。`type列都是range`。但是可以从where条件中值的范围和多个等于条件来得出不同。  
对于范围查询，MySQL无法使用范围后面的其他索引列了，但是对应“多个等值条件查询”则没有这个限制。

## 避免多个范围条件 ##

MySQL只能使用一个范围索引。

## 优化排序 ##

*  使用文件排序对小数据集是很快的。
 *  对于那些选择性非常低的列，可以增加一些特殊的索引做排序。例如，可以创建（sex,rating）索引用于下面的查询：

mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

*  即使有索引，但当翻页比较靠后时查询也可能会很慢。

mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;

无论如果创建索引，这个查询都是个很严重的问题。随用偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算、缓存可能是解决这类问题仅有的策略。一个更好的办法是限制用户能够翻页的数量，因为用户很少会在乎搜索结果的第10000页。

*  优化这类问题的另一个较好的策略是使用延迟关联，采用覆盖索引查询返回需要的主键，再更加主键关联原表获得需要的行。这可以减少MySQL扫描需要丢弃的行数。

mysql> SELECT <cols> FROM profiles INNER JOIN(SELECT <primary key cols> FROM profiles WHERE x.sex = 'M' ORDER BY rating LIMIT 100000, 10) AS x USING(<primary key cols>);

# 维护索引和表 #

我们需要维护表和索引来确保索引可以正常的工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

## 找到并修复损坏的表 ##

*  表损坏（corruption）是很糟糕的事情。MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题，MySQL本身的缺陷或者操作系统的问题导致索引损坏。
 *  损坏的索引通常会导致查询返回错误的结果或者莫须有的主键冲突。严重时甚至会导致数据库的崩溃。可以使用`CHECK TABLE`来检查是否发生了表损坏。
 *  可以使用`REPAIR TABLE`命令来修复损坏的表，但不同所有的存储引擎都支持该命令。也可以通过一个不做任何操作（no-op）的ALTER操作来重建表。例如，修改表的存储引擎为当前的引擎。

mysql> ALTER TABLE innodb_tb1 ENGINE=INNODB;

也可以使用存储引擎相关的离线工具，例如myisamchk或将数据导出一份在重新导入。不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。

*  如果InnoDB引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查原因。InnoDB的设计保证了它并不容易别损坏。发生损坏的原因一般是数据库的硬件问题例如内存或磁盘问题，或者是数据管理员的错误例如在MySQL外部操作了数据文件。常见的类似错误通常是尝试使用rsync备份InnoDB导致的。不存在什么查询能够让InnoDB表损坏。
 *  如果遇到损坏，要找出原因，而不是简单的修复，否则可能遇到不断的损坏。

## 更新索引统计信息 ##

*  MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。
 *  第一个API是`records_in_range()`，通过向存储引擎传入两个边界值获取这个范围内大概有多少条记录。对于MyISAM存储引擎，该接口返回精确值，但对于InnoDB  
    则返回一个估算值。
 *  第二个API是`info()`，该接口返回各种类型的数据，包含索引的基数（每个键值有多少条记录）。
 *  如果存储引擎向优化器提供的扫描行数是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计小心来估算扫描行数。MySQL优化器使用的是基本成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。可以通过`ANALYZE TABLE`来生成索引统计信息。
 *  每种存储引擎实现索引统计信息的方式不同，所以需要进行ANALYZE TABLE的频率也因不同的引擎而不同，每次运行的成本也不同：

1.  Memery引擎根本不会存储索引统计信息。
2.  MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表。
3.  直到MySQL 5.5版本，InnoDB不会在磁盘存储索引统计信息，而是通过随机的索引访问评估并将其存储在内存中。

*  可以使用`SHOW INDEX FROM`命令来查看索引的基数（Cardianlity）。例如：

mysql> SHOW INDEX FROM actor\G;
    *************************** 1. row ***************************
            Table: actor
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: actor_id
        Collation: A
      Cardinality: 200
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 2. row ***************************
            Table: actor
       Non_unique: 1
         Key_name: idx_actor_last_name
     Seq_in_index: 1
      Column_name: last_name
        Collation: A
      Cardinality: 121
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    2 rows in set (0.01 sec)

这里要特别主要索引列的基数（Cardinality），其显示了存储引擎估算索引列有多少个不同的取值。在MYSQL 5.0和更新的版本中，可以通过`INFORMATION_SCHEMA.STATISTICS`表很方便的查询到这些信息。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给MySQL带来额外的压力。

*  InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在老的InnoDB版本中，样本页面数是8，新版本的InnoDB可以通过参数innodb\_stats\_sample\_pages 来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。
 *  InnoDB会在表首次打开，或者执行ANALYZE TABLE，或者表的大小发生变化（表的大小超过十六分之一或者新插入了20亿行都会触发）的时候计算索引的统计信息。
 *  InnoDB在打开某些INFORMATION\_SCHEMA表，或使用SHOW TABLE STATUS和SHOW INDEX, 抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据，这可能是一个很严重的问题。尤其是当I/O比较慢的时候。客户端或者监控程序触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多的额外压力，这会让用户因为启动时间漫长而沮丧。只要SHOW INDEX 查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb\_stats\_on\_metadata 参数来避免上面提到的问题。
 *  一旦关闭索引统计信息的自动更新，那么就需要周期性地使用ANALYZE TABLE来手动更新。否则，索引统计信息就会永远不变。如果数据分布发生大的变化，可能会出现一些很糟糕的执行计划。

## 减少索引和数据的碎片化 ##

*  B-Tree索引可能会导致碎片化，这会降低查询的效率。碎片化的索引可能会以很差或无序的方式存储在磁盘上。
 *  根据设计，B-Tree需要随机磁盘访问才能定位到叶子叶，所以随机访问是不可避免的。然而，如果页子叶在物理分布上如果是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会下降很多倍。
 *  表的数据存储的碎片化有三种类型：

1.  行碎片（Row fragmentation）  
    这种碎片指的是数据行被存储在多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
2.  行间碎片（Intra-row fragmentation  
    行间碎片指的是逻辑上顺序的页，行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
3.  剩余空间碎片（Free space fragmentation）  
    剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

*  对应MyISAM表，这三种碎片都可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动短小的行并重写到一个片段中。
 *  可以通过`OPTIMIZE TABLE`或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于一些存储引擎如MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的InnoDB没有什么消除碎片化的方法。不过最新版本InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

# 总结 #

*  在MySQL，大多数情况下都会使用B-Tree索引。其他类型的索引大多只是满足特殊的目的。
 *  使用索引的三个原则：

1.  单行访问总是很慢的。特别是在机械硬盘存储中（SSD的随机IO快的多）。如果服务器从存储中读取一个数据块只是为了读取其中一行，那么就浪费了很多工作。最好读取的块中尽可能多的包含需要的行。
2.  按顺序访问数据总数很快的。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
3.  索引覆盖总是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

*  总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。
 *  如果表上的每一个查询都能有一个完美的索引来满足当然是最好的。但不幸的是，要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到“三星”的索引的（例如查询要按照两个列排序，其中一个列正序，另一个列倒序）。这时必须有所取舍以创建最合适的索引，或者寻求替代策略（例如反范式化，或者提前计算汇总表等）。
 *  那如何判断一个系统创建的索引是合理的呢？一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是有太多回表查询那些不在索引中的列的操作。

**参考《高性能MySQL》**

[http_code.google.com_p_common-schema]: http://code.google.com/p/common-schema/