高性能MySQL之创建高性能的索引篇

　　索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

一.索引的基础

1.索引的类型

　　索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

（1）B-Tree索引：B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

 

 

 加入一个表的索引列有性、名字和生日

B-Tree索引使用的查询有全值索引、匹配最左前缀、匹配列前缀、匹配范围值、精准匹配某一列并范围匹配另外一列、只访问索引的查询。还可以用于查询中的ORDER BY操作。

B-Tree索引的限制：

--如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bil1的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。

--不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。
--如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last name='Smith' AND first name LIKE 'J%' AND dob='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

 

（2）哈希索引：哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory 引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的限制：

--哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
--哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
--哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。

--哈希索引只支持等值比较查询，包括=、IN（）、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。

--访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
--如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

 

（3）空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS（）等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

 

（4）全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

 

二.高性能的索引策略

1.独立的列

　　我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
例如，下面这个查询无法使用actor_id列的索引：
　　mysq1>SELECT actor_id FROM sakila.actor WHERE actor_id+1=5；凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4，但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误：
　　mysq1> SELECT...MHERE TO_DAYS（CURRENT_DATE）-TO_DAYS（date_col）<=10；

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

　　有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

3.多列索引

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

　　这种索引策略，一般是由于人们听到一些专家诸如“把WHERE条件里面的列都建上索引”
　　这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

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

　　对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。

5.聚簇索引

 　　聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

6.覆盖索引

　　如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

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

　　MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序（不要和Extra列的“Using index”搞混淆了）。只有当索引的列顺序和ORDERBY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。

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

 

 

 

 8.压缩（前缀压缩）索引

　　MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

9.冗余和重复索引

　　

三.索引案例学习

　　假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢?出人意料的是第--件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHERE条件中的age BETWEEN 18 AND 25 就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引(或者是该索引的后续字段)进行排序了。如果这是很常见的WHERE条件，那么我们当然就会认为很多查询需要做排序操作(例如文件排序filesort)。

1.支持多种过滤条件

　　country列的选择性通常不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候将(sex, country)列作为前缀。这么做有两个理由: 第一点，如前所述几乎所有的查询都会用到sex列。前面曾提到，几乎每一个查询都会用到sex列，甚至会把网站设计成每次都只能按某- -种性别搜索用户。更重要的一点是，索引中加上这一列也没有坏处，即使查询没有使用sex列也可以通过下面的“诀窍”绕过。这个“诀窍”就是:如果某个查询不限制性别，那么可以通过在查询条件中新增ANDSEX IN('m' ,'f')来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效,但如果列有太多不同的值,就会让IN()列表太长，这样做就不行了。

　　这个案例显示了一个基本原则:考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。

　　接下来，需要考虑其他常见WHERE条件的组合，并需要了解哪些组合在没有合适索引的情况下会很慢。(sex, country, age).上的索引就是- 一个很明显的选择，另外很有可能还需要(sex, country, region, age)和(sex, country, region, city, age)这样的组合索引。这样就会需要大量的索引。如果想尽可能重用索引而不是建立大量的组合索引，可以使用前面提到的IN()的技巧来避免同时需要(sex, country, age)和(sex, country,region, age)的索引。如果没有指定这个字段搜索，就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束(组合所有国家、地区、性别将会是一个非常大的条件)。

　　这些索引将满足大部分最常见的搜索查询，但是如何为--些生僻的搜索条件(比如has_pictures、eye_ color. hair_ color 和education)来设计索引呢?这些列的选择性高、使用也不频繁，可以选择忽略它们，让MySQL多扫描一些额外的行即可。另一个可选的方法是在age列的前面加上这些列，在查询时使用前面提到过的IN()技术来处理搜索时没有指定这些列的场景。由于age列多半是范围查询，索引放在最后更适合。

　　前面提到可以在索引中加入更多的列，并通过IN()的方式覆盖那些不在WHERE子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。

2.避免多个范围条件

3.优化排序

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

　　这个查询同时使用了ORDERBY和LIMIT,如果没有索引的话会很慢。即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。下面这个查询就通过ORDERBY和LIMIT偏移量的组合翻页到很后面的时候:

　　mysql> SELECT <cols> FROM profiles WHERE sex= 'M' ORDER BY rating LIMIT 100000 10;
　　无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。一个更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第10000页。

四.维护索引和表

　　即使用正确的类型创建了表并加上了合适的索引，工作也没有结束:还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的:找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

1.找到并修复损坏的表

　　表损坏(corruption) 是很糟糕的事情。对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题一例如一些不应该发生的错误一可以尝试运行CHECK TABLE 来检查是否发生了表损坏(注意有些存储引擎不支持该命令;而有些引擎则支持以不同的选项来控制完全检查表的方式)。CHECK TABLE 通常能够找出大多数的表和索引的错误。

2.更新索引统计信息

3.减少索引和数据的碎片

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

行碎片(Row fragmentation)
　　这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问- -行记录，行碎片也会导致性能下降。

行间碎片(Intra -row fragmentation)
　　行间碎片是指逻辑.上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。

剩余空间碎片(Free space fragmentation)
　　剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。