MySQL 索引最左匹配原则

MySQL 索引最左匹配原则

2016-10-24 22:11:58 zhifeng687 阅读数 4750

csdn原文：http://blog.csdn.net/zhu19774279/article/details/46473981

本文的原文地址在此：https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/，以下是译文。

-----------------------------------------------------------这是一条分割线-----------------------------------------------------------

我最近碰到了很多性能很糟糕的MySQL单表查询。原因很简单：索引创建得不正确，导致执行计划的性能低下。下面是一些能帮助你优化单表查询性能的要点。

免责声明：我会给出一些要点，但并不打算包含所有的可能情况。我100%相信你能够找到我的要点不适应的案例，但是我也相信大部分情况下，我写的这些要点会帮助到你。为了简单起见，我也不会讨论一些MySQL 5.6+版本的一些新特性，如Index Condition Pushdown。注意这些新特性会对响应时间有极大的影响（缩短或延长均有可能）。

索引能做什么？

索引主要做3件事：过滤(filter)，排序或分组(sort/group)，覆盖(cover)。前两个没什么好说的，但并不是每个人都知道什么叫“覆盖索引”。事实上这是个很简单的东西。

一个基本查询的工作流如下：

1. 使用索引以查找匹配的记录，并得到数据的指针。

2. 使用相关数据的指针。

3. 返回查询到的记录。

当可以使用覆盖索引时，索引将会覆盖查询中的所有字段，因此第二步将会被跳过，于是查询流程就变成了下面这样：

1. 使用索引以查找匹配的记录

2. 返回查询到的记录。

大部分情况下，索引都比较小，可以加载在内存中，而数据很大，无法全部存放在内存里：当使用覆盖索引时，可以避免很多的磁盘操作，因此对性能也会有极大的改善。

下面让我们来看一些常见的查询案例。

单个等于查询（Single equality）

这是最基本的情景：

 

[sql] view plain copy

1. SELECT * FROM t WHERE c = 100  

毫无疑问这种情况下，要给c字段创建索引。要注意的是，如果查询条件不够精确（if the criteria is not selective enough，这句话我不理解），优化器很可能会选择全表查询，因为这样有可能性能更好。

 

这种单个等于查询也包括只查询部分字段，而不是所有字段，如：

 

[sql] view plain copy

1. SELECT c1, c2 FROM t WHERE c = 100  

这里应该创建一个（c，c1，c2）的索引，因为这样是覆盖索引。注意不是创建（c1，c2，c）！这同样也是覆盖索引，但是对过滤没什么帮助（记住MySQL索引的最左原则）。

多个等于查询（Multiple equalities）

 

[sql] view plain copy

1. SELECT * FROM t WHERE c = 100 and d = 'xyz'  

这种情况也很容易优化：创建索引（c，d）或（d，c）。

 

最常见的错误是建立两个索引：一个是c，一个是d。尽管MySQL根据index_merge算法能同时使用这两个索引，但这样依然是糟糕的选择（详情参见以下几篇文章：https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/，https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/，https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/）

等于与不等于并存的查询（Equality and inequality）

 

[sql] view plain copy

1. SELECT * FROM t WHERE c > 100 and d = 'xyz'  

这种情况我们必须要小心，因为只要有一列使用了不等于计算，那么它将阻止其他列使用索引。

因此我们需要创建一个（d，c）的索引，这时候c和d两个条件都会走索引，这也是我们想要的结果。

而如果我们创建的是（c，d）索引，则只有c列的索引会被利用，这样效率会比较低。

因此，索引中字段的顺序对于这种等于/不等于并存的查询有极大的影响。

多个不等于查询（Multiple inequalities）

 

[sql] view plain copy

1. SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'  

这里有两个不等于，前面已经说了不等于会终止索引查询，因此我们不可能做到b、c、d都被索引覆盖（注释1）。因此我们必须要做出决定，到底是创建索引（d，b）还是索引（d，c）？

在不知道表里具体数据的情况下，创建上面任何一种都无所谓，最关键的是，一定要把等于条件（在这里是d）所在列，放在索引的最左侧。

注释1：事实上还是有一种“曲线救国”的方法，能同时满足所有条件，即按照字段b分区(partition on b)，然后创建索引（d，c），或按照字段c分区(partition onc)，然后创建索引（d，b）。这个的细节已经超出了本文的讨论范围，不过这也是这种情况下的一种解决方法。

多个等于与排序（Equalities and sort）

 

[sql] view plain copy

1. SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b  

就像第一节中写的那样，索引可以过滤、排序，因此这个查询很容易优化。不过和不等于类似，我们对于索引中字段的顺序必须足够小心：要求是先过滤后排序。

根据上面“先过滤后排序”的要求可知，（c，d，b）或（d，c，b）是不错的选择；而（b，c，d）或（b，d，c）则比较糟糕，因为他们只排序，不过滤。

如果是下面这种情况：

 

[sql] view plain copy

1. SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b  

我们可以创建一个集过滤、排序、覆盖于一体的索引：（c，d，b，c1，c2）。

 

不等于与排序（Inequality and sort）

常见的情况有2种。下面是情况一（不等于、等于、排序都有）：

[sql] view plain copy

1. SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b  

这种情况有两种思路：（d，b）或（d，c）。至于哪种效率更高，这取决于你的数据，需要具体情况具体分析。

 

情况二如下（只有不等于和排序）：

 

[sql] view plain copy

1. SELECT * FROM t WHERE c > 100 ORDER BY b  

这种情况没有等于条件，因此b和c只能选一种，具体选哪一种同样和你的数据有关。通常情况下，选过滤的会多一些（即c字段）。

总结

本文并没有包含所有的情况，但同样指出了一些你必须要小心的地方。今后，我会列举一个看起来十分复杂的例子，不过只要你把这篇文章看懂了，它其实很简单。

 

========================================================

《高性能MySQL》&《MySQL技术内幕 InnoDB存储引擎》笔记

 

第五章 创建高性能的索引 & 索引与算法

B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引和辅助索引。聚集索引的叶子结点存放的是一整行记录，而辅助索引叶子结点存放的是主键值。

许多数据库的文档这样告诉读者：聚集索引按照顺序物理地存储数据。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表连接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。（《MySQL技术内幕 InnoDB存储引擎》）

InnoDB只聚集在同一个页面中数据，包含相邻键值的页面可能相距甚远。（高性能MySQL）

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型

B-Tree索引

B+树，所有叶子节点在同一层，每一个叶子节点包含指向下一个叶子结点的指针。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

其中，索引对多个值进行排序的顺序是与定义索引时列的顺序一致的。

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

• 全字匹配：和索引中的所有列进行匹配，如查找姓名为Cuba Allen、出生于1960-01-01的人；
• 匹配最左前缀：即只使用索引的第一列，如查找所有姓为Allen的人；
• 匹配列前缀：匹配某一列的值的开头部分，如查找所有以J开头的姓的人。这里只使用了索引的第一列；
• 匹配范围值：如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列；
• 精确匹配某一列并范围匹配另一列：如查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配；
• 只访问索引的查询：覆盖索引；

如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人。类似的，也无法查找姓以某个字母结尾的人。

不能跳过索引中的列。也就是说，上述索引无法用于查找姓为Smith并且在某个特定日期出生的人。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查询WHERE 姓='Smith' AND 名 LIKE 'J%' AND 出生日期='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。（如果范围查询列值的数量有限，那么可以使用多个等于条件来代替范围条件）

到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时保持指向数据行的指针。

在MySQL中，只有Memory引起显示支持哈希索引。

• 哈希索引数据并不是按照索引数据顺序存储的，所以无法用于排序；
• 哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
• 哈希索引只支持等值比较查询，不支持任何范围查询；

InnoDB引擎有个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为。

索引的优点

最常见的B-Tree索引，按照顺序存储数据，所以可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成查询。据此特性，总结下来索引有如下三大优点：

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

评价一个索引是否适合某个查询的“三星系统”：

• 索引将相关的记录放到一起则获得一星；
• 索引中的数据顺序和查找中的排列顺序一致则获得二星；
• 索引中的列包含了查询需要的全部列则获得三星；

高性能的索引策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数，否则不会使用索引。

如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5

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

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？
通常可以索引开始的部分字符，这样可以大大节约空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为可以在查询时过滤掉更多的行。唯一索引的选择性是1.

诀窍在与既要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间。

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序建立多列索引。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列（在没有ORDER BY 或 GROUP BY的情况下）。
例如，在超市的销售记录表中：SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584，很自然的customer_id的选择性更高些，所以多列索引的顺序应该是(customer_id, staff_id)。

这样做有一个地方需要注意，查询的结果非常依赖与选定的具体值。例如，一个应用通常都有一个特殊的管理员账号，系统中所有其他用户都是这个用户的好友，所以系统通常通过它向网站的所有其他用户发送状态和其他消息。这个账号巨大的好友列表很容易导致网站出现服务器性能问题。

这实际上是一个非常典型的问题。任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的系统账号同样的问题。

从这个小案例可以看到经验法则和推论在多数情况下是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。

在InnoDB中，聚簇索引“就是”表。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB只能通过主键聚集索引！
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

MySQL中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

聚簇索引有一些重要的优点：

• 可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件可能都会导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

同时，聚簇索引也有一些缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖插入顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临叶分裂的问题。
• 二级索引访问需要两次索引查找，而不是一次。

MyISAM/InnoDB的主键索引和二级索引

MyISAM的主键索引和（所有其他的）二级索引的叶子节点中保存的都是指向行的物理位置的指针。

InnoDB的主键索引的叶子结点是数据行；（所有其他的）二级索引的叶子节点中保存的是主键值。
这样的策略减少了当出现行移动或数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个“指针”。

如果正在使用的InnoDB表没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就可以使用索引来直接获取列的数据，这样就不再需要读取数据行。我们称这样的索引为覆盖索引。

例如，表inventory有一个多列索引（store_id, film_id），MySQL如果只需要访问这两列，就可以使用这个索引做覆盖索引，如SELECT store_id, film_id FROM inventory.

利用索引扫描来做排序

只有当索引的列顺序和ORDER BY子句的列顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

例如，索引：UNIQUE KEY idx（rental_date, inventory_id, customer_id）

下面这个查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id DESC;

下面这个查询也没问题，因为ORDER BY使用的两列就是索引的最左前缀：

WHERE rental_date > '2005-05-25' ORDER BY rental_date , inventory_id;

下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：
  • 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;
• 下面这个查询的WHERE和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;

冗余和重复索引

重复索引是指在相同的列上按相同的顺序创建相同类型的索引。如：

1. CREATE TABLE test (

2. ID INT NOT NULL PRIMARY KEY,

3. A INT NOT NULL,

4. B INT NOT NULL,

5. UNIQUE(ID),

6. INDEX(ID)

7. ) ENGINE=InnoDB;

8.  

事实上，MySQL的主键约束和唯一约束都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。

冗余索引和重复索引有一些不同。如果创建了索引（A, B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，索引（A, B）也可以当做索引（A）来使用。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引（如扩展索引（A）为（A，B））。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变大太大，从而影响其他使用该索引的查询的性能。

一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。

作者：xiaogmail
链接：https://www.jianshu.com/p/bd8675e5c7b2
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。