乐趣区

关于mysql:MySQL优化索引性能优化

jiezi

索引对于良好的性能十分要害。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不失当的索引对性能的影响可能还不显著，但当数据量逐步增大时，性能则会急剧下降。

索引类型

B-Tree 索引

当人们议论索引的时候，如果没有特地指明类型，那多半说的是 B -Tree 索引。不过，底层的存储引擎也可能应用不同的存储构造，InnoDB 则应用的是 B +Tree。B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的间隔雷同。下图展现了 B -Tree 索引的形象示意，大抵反映了 InnoDB 索引是如何工作的。

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

叶子节点比拟特地，它们的指针指向的是被索引的数据，而不是其余的节点页。其实在根节点和叶子节点之间可能有很多层节点页，树的深度和表的大小间接相干。

B-Tree 对索引列是程序组织存储的，所以很适宜查找范畴数据。例如，在一个基于文本域的索引树上，按字母程序传递间断的值进行查找是十分适合的，所以像“找出所有以 I 到 K 结尾的名字”这样的查找效率会十分高。

假如有如下数据表：

CREATE TABLE people (last_name varchar(50)    not null,
       first_name varchar(50)   not null,
       dob date                             not null,
       gender enum('m', 'f')      not null,
       key(last_name, first_name, dob)
);

对于表中的每一行数据，索引中蕴含了 last_name、frst_name 和 dob 列的值，下图显示了该索引是如何组织数据的存储的。

延长浏览：
均衡二叉树、B 树、B+ 树、B* 树 了解其中一种你就都明确了
MySQL 中 B +Tree 索引原理

能够应用 B -Tree 索引的查问类型
全值匹配

全值匹配指的是和索引中的所有列进行匹配，例如后面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01 的人。

匹配最左前缀

后面提到的索引可用于查找所有姓为 Allen 的人，即只应用索引的第一列。

匹配列前缀

也能够只匹配某一列的值的结尾局部。例如后面提到的索引可用于查找所有以 J 结尾的姓的人。这里也只应用了索引的第一列。

匹配范畴值

例如后面提到的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里也只应用了索引的第一列。

准确匹配某一列并范畴匹配另外一列

后面提到的索引也可用于查找所有姓为 Allen，并且名字是字母 K 结尾（比方 Kim、Karl 等）的人。即第一列 last_name 全匹配，第二列 frst_name 范畴匹配。

只拜访索引的查问

B-Tree 通常能够反对“只拜访索引的查问”，即查问只须要拜访索引，而无须拜访数据行。

因为索引树中的节点是有序的，所以 除了按值查找之外，索引还能够用于查问中的 ORDER BY 操作（按程序查找）。一般来说，如果 B -Tree 能够依照某种形式查找到值，那么也能够依照这种形式用于排序。所以，如果 ORDER BY 子句满足后面列出的几种查问类型，则这个索引也能够满足对应的排序需要。

B-Tree 索引的限度
如果不是依照索引的最左列开始查找，则无奈应用索引

例如下面例子中的索引无奈用于查找名字为 Bill 的人，也无奈查找某个特定生日的人，因为这两列都不是最左数据列。相似地，也无奈查找姓氏以某个字母结尾的人。

不能跳过索引中的列

后面所述的索引无奈用于查找姓为 Smith 并且在某个特定日期出世的人。如果不指定名（first_name），则 MySQL 只能应用索引的第一列。

如果查问中有某个列的范畴查问，则其左边所有列都无奈应用索引优化查找

例如有查问 WHERElast_name=’Smith’ AND frst_name LIKE ‘J%’ AND dob=’1976-12-23’，这个查问只能应用索引的前两列，因为这里 LIKE 是一个范畴条件（然而服务器能够把其余列用于其余目标）。如果范畴查问列值的数量无限，那么能够通过应用多个等于条件来代替范畴条件。

索引列的程序很重要：这些限度都和索引列的程序无关。在优化性能的时候，可能须要应用雷同的列但程序不同的索引来满足不同类型的查问需要。

哈希索引

哈希索引（hash index）基于哈希表实现，只有准确匹配索引所有列的查问才无效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保留指向每个数据行的指针。

在 MySQL 中，只有 Memory 引擎显式反对哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也反对 B -Tree 索引。值得一提的是，Memory 引擎是反对非惟一哈希索引的，这在数据库世界外面是比拟不同凡响的。如果多个列的哈希值雷同，索引会以链表的形式寄存多个记录指针到同一个哈希条目中。

因为索引本身只需存储对应的哈希值，所以索引的构造非常紧凑，这也让哈希索引查找的速度十分快。然而，哈希索引也有它的限度：

  • 哈希索引只蕴含哈希值和行指针，而不存储字段值，所以不能应用索引中的值来防止读取行。不过，拜访内存中的行的速度很快，所以大部分状况下这一点对性能的影响并不显著。
  • 哈希索引数据并不是依照索引值顺序存储的，所以也就无奈用于排序。
  • 哈希索引也不反对局部索引列匹配查找，因为哈希索引始终是应用索引列的全部内容来计算哈希值的。例如，在数据列（A,B）上建设哈希索引，如果查问只有数据列 A，则无奈应用该索引。
  • 哈希索引只反对等值比拟查问，包含 =、IN()、<=>（留神 <> 和 <=> 是不同的操作）。也不反对任何范畴查问，例如 WHERE price>100。
  • 拜访哈希索引的数据十分快，除非有很多哈希抵触（不同的索引列值却有雷同的哈希值）。当呈现哈希抵触的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比拟，直到找到所有符合条件的行。
  • 如果哈希抵触很多的话，一些索引保护操作的代价也会很高。例如，如果在某个选择性很低（哈希抵触很多）的列上建设哈希索引，那么当从表中删除一行时，存储引擎须要遍历对应哈希值的链表中的每一行，找到并删除对应行的援用，抵触越多，代价越大。

索引的长处

总结下来索引有如下三个长处：

  1. 索引大大减少了服务器须要扫描的数据量。
  2. 索引能够帮忙服务器防止排序和长期表。
  3. 索引能够将随机 I / O 变为程序 I /O。

索引的评级：

  1. 索引将相干的记录放到一起则取得一星；
  2. 如果索引中的数据程序和查找中的排列程序统一则取得二星；
  3. 如果索引中的列蕴含了查问中须要的全部列则取得三星。

高性能的索引策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数。例如 …where act_id + 1 = 5 或 …where to_days(current_date) – to_days(date_col) < 10。应该始终将索引列独自放在比拟符号的一侧。

前缀索引和索引选择性

有时候须要索引很长的字符列，这会让索引变得大且慢。通常能够索引开始的局部字符，这样能够大大节约索引空间，从而进步索引效率，但这样也会升高索引的选择性。索引的选择性是指，不反复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范畴从 1 /#T 到 1 之间。索引的选择性越高则查问效率越高，因为选择性高的索引能够让 MySQL 在查找时过滤掉更多的行。惟一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

窍门在于要抉择足够长的前缀以保障较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性靠近于索引整个列。换句话说，前缀的“基数”应该靠近于残缺列的“基数”。为了决定前缀的适合长度，须要找到最常见的值的列表，而后和最常见的前缀列表进行比拟。假如表 sakila.city_demo 只有一个列 city，类型为 VARCHAR(50)。首先，咱们找到最常见的城市列表：

当初查找到最频繁呈现的城市前缀，先从 3 个前缀字母开始：

每个前缀都比原来的城市呈现的次数更多，因而惟一前缀比惟一城市要少得多。而后咱们减少前缀长度，直到这个前缀的选择性靠近残缺列的选择性。通过试验后发现前缀长度为 7 时比拟适合：

计算适合的前缀长度的另外一个方法就是计算残缺列的选择性，并使前缀的选择性靠近于残缺列的选择性。上面显示如何计算残缺列的选择性：

通常来说（只管也有例外情况），这个例子中如果前缀的选择性可能靠近 0.031，基本上就可用了。能够在一个查问中针对不同前缀长度进行计算，这对于大表十分有用。上面给出了如何在同一个查问中计算不同前缀长度的选择性：

查问显示当前缀长度达到 7 的时候，再减少前缀长度，选择性晋升的幅度曾经很小了。

只看均匀选择性是不够的，也有例外的状况，须要思考最坏状况下的选择性。均匀选择性会让你认为前缀长度为 4 或者 5 的索引曾经足够了，但如果数据分布很不平均，可能就会有陷阱。

前缀索引是一种能使索引更小、更快的无效方法，但另一方面也有其毛病：MySQL 无奈应用前缀索引做 ORDER BY 和 GROUP BY，也无奈应用前缀索引做笼罩扫描。

多列索引

在多个列上建设独立的单列索引大部分状况下并不能进步 MySQL 的查问性能。MySQL 5.0 和更新版本引入了一种叫“索引合并”（index merge）的策略，肯定水平上能够应用表上的多个单列索引来定位指定的行。索引合并策略有时候是一种优化的后果，但实际上更多时候阐明了表上的索引建得很蹩脚：

  • 当呈现服务器对多个索引做相交操作时（通常有多个 AND 条件），通常意味着须要一个蕴含所有相干列的多列索引，而不是多个独立的单列索引。
  • 当服务器须要对多个索引做联合操作时（通常有多个 OR 条件），通常须要消耗大量 CPU 和内存资源在算法的缓存、排序和合并操作上。特地是当其中有些索引的选择性不高，须要合并扫描返回的大量数据的时候。通常来说，将查问改写成 UNION 的形式往往更好。
  • 更重要的是，优化器不会把这些计算到“查问老本”（cost）中，优化器只关怀随机页面读取。这会使得查问的老本被“低估”，导致该执行打算还不如间接走全表扫描。这样做岂但会耗费更多的 CPU 和内存资源，还可能会影响查问的并发性，但如果是独自运行这样的查问则往往会疏忽对并发性的影响。
适合的索引列程序

正确的索引列程序依赖于应用该索引的查问，并且同时须要思考如何更好地满足排序和分组的须要。

对于如何抉择索引的列程序有一个教训法令：将选择性最高的列放到索引最前列。这个倡议在某些场景可能有帮忙，但通常不如防止随机 IO 和排序那么重要，思考问题须要更全面。

当不须要思考排序和分组时，将选择性最高的列放在后面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。在这种状况下，这样设计的索引的确可能最快地过滤出须要的行，对于在 WHERE 子句中只应用了索引局部前缀列的查问来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查问条件的具体值无关，也就是和值的散布无关 。这和后面介绍的抉择前缀的长度须要思考的中央一样。 可能须要依据那些运行频率最高的查问来调整索引列的程序，让这种状况下索引的选择性最高。

聚簇索引

聚簇索引是一种数据存储形式，具体的细节依赖于其实现形式，InnoDB 的聚簇索引实际上在同一个构造中保留了 B -Tree 索引和数据行。当表有聚簇索引时，它的数据行实际上寄存在索引的叶子页（leaf page）中。术语“聚簇”示意数据行和相邻的键值紧凑地存储在一起，艰深地说就是将索引与数据寄存到了一起。因为无奈同时把数据行寄存在两个不同的中央，所以一个表只能有一个聚簇索引。

聚簇索引的长处有：

  1. 能够把相干数据保留在一起。例如实现电子邮箱时，能够依据用户 ID 来汇集数据，这样只须要从磁盘读取多数的数据页就能获取某个用户的全副邮件。如果没有应用聚簇索引，则每封邮件都可能导致一次磁盘 I /O。
  2. 数据拜访更快。聚簇索引将索引和数据保留在同一个 B -Tree 中，因而 从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
  3. 应用笼罩索引扫描的查问能够间接应用页节点中的主键值。

聚簇索引的毛病有:

  1. 聚簇数据最大限度地进步了 I / O 密集型利用的性能，但如果数据全副都放在内存中，则拜访的程序就没那么重要了，聚簇索引也就没什么劣势了。
  2. 插入速度重大依赖于插入程序。依照主键的程序插入是加载数据到 InnoDB 表中速度最快的形式。但如果不是依照主键程序加载数据，那么在加载实现后最好应用 OPTIMIZE TABLE 命令从新组织一下表。
  3. 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行挪动到新的地位。
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致须要挪动行的时候，可能面临“页决裂（pagesplit）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页决裂成两个页面来包容该行，这就是一次页决裂操作。页决裂会导致表占用更多的磁盘空间。
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比拟稠密，或者因为页决裂导致数据存储不间断的时候。
  6. 二级索引（非聚簇索引）可能比设想的要更大，因为在二级索引的叶子节点蕴含了援用行的主键列。
  7. 二级索引拜访须要两次索引查找，而不是一次。

如果正在应用 InnoDB 表并且没有什么数据须要汇集，那么能够定义一个代理键（surrogate key）作为主键，这种主键的数据应该和利用无关，最简略的办法是应用 AUTO_INCREMENT 自增列。这样能够保证数据行是按程序写入，对于依据主键做关联操作的性能也会更好。

最好防止随机的（不间断且值的散布范畴十分大）聚簇索引，特地是对于 I / O 密集型的利用。例如，从性能的角度思考，应用 UUID 来作为聚簇索引则会很蹩脚：它使得聚簇索引的插入变得齐全随机，导致页决裂和碎片，而且使得数据没有任何汇集个性。

应用 InnoDB 时应该尽可能地按主键程序插入数据，并且尽可能地应用枯燥减少的聚簇键的值来插入新行。

当然，程序的主键在某些时候也会造成更坏的后果。对于高并发工作负载，在 InnoDB 中按主键程序插入可能会造成显著的争用。主键的上界会成为“热点”。因为所有的插入都产生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是 AUTO_INCREMENT 锁机制，如果遇到这个问题，则可能须要思考从新设计表或者利用，或者更改 innodb_autoinc_lock_mode 配置。

延长浏览：高并发网站如何解决数据库主键自增的时候呈现反复？

笼罩索引

设计优良的索引应该思考到整个查问，而不单单是 WHERE 条件局部。索引的确是一种查找数据的高效形式，然而 MySQL 也能够应用索引来间接获取列的数据，这样就不再须要读取数据行。如果索引的叶子节点中曾经蕴含要查问的数据，那么还有什么必要再回表查问呢? 如果一个索引蕴含（或者说笼罩）所有须要查问的字段的值，咱们就称之为“笼罩索引”。

笼罩索引是十分有用的工具，可能极大地提高性能。考虑一下如果查问只须要扫描索引而无须回表，会带来多少益处：

  • 索引条目通常远小于数据行大小，所以 如果只须要读取索引，那 MySQL 就会极大地缩小数据访问量。这对缓存的负载十分重要，因为这种状况下响应工夫大部分破费在数据拷贝上。笼罩索引对于 I / O 密集型的利用也有帮忙，因为索引比数据更小，更容易全副放入内存中。
  • 因为索引是依照列值顺序存储的（至多在单个页内是如此），所以 对于 I / O 密集型的范畴查问会比随机从磁盘读取每一行数据的 I / O 要少得多。
  • 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因而要拜访数据须要一次零碎调用。这可能会导致重大的性能问题，尤其是那些零碎调用占了数据拜访中的最大开销的场景。
  • 因为 InnoDB 的聚簇索引，笼罩索引对 InnoDB 表特地有用。InnoDB 的二级索引在叶子节点中保留了行的主键值，所以 如果二级主键可能笼罩查问，则能够防止对主键索引的二次查问。
应用索引扫描来做排序

扫描索引自身是很快的，因为只须要从一条索引记录挪动到紧接着的下一条记录。但如果索引不能笼罩查问所需的全部列，那就不得不每扫描一条索引记录就都回表查问一次对应的行。这基本上都是随机 I /O，因而按索引程序读取数据的速度通常要比程序地全表扫描慢，尤其是在 I / O 密集型的工作负载时。

只有当索引的列程序和 ORDER BY 子句的程序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才可能应用索引来对后果做排序。如果查问须要关联多张表，则只有当 ORDER BY 子句援用的字段全副为第一个表时，能力应用索引做排序。ORDER BY 子句和查找型查问的限度是一样的：须要满足索引的最左前缀的要求。否则，MySQL 都须要执行排序操作，而无奈利用索引排序。

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

索引案例

假如要设计一个在线约会网站，用户信息表有很多列，包含国家、地区、城市、性别、眼睛色彩，等等。网站必须反对下面这些特色的各种组合来搜寻用户，还必须容许依据用户的最初在线工夫、其余会员对用户的评分等对用户进行排序并对后果进行限度。那么应该如何设计索引满足下面的简单需要?

反对多种过滤条件

索引的程序并不是自觉地应用选择性最高的列作为索引的前置列，还要综合思考列的查找频率。例如在线交友网站能够把性别作为索引的第一列，因为简直每个查问都会应用到性别，而且即便不必该列时也能够应用 in（‘f’，‘m’）这样的形式来跳过。

防止多个范畴条件

对于含有多个范畴条件查问的语句，MySQL 无奈同时应用列上的索引，也就是说对于范畴条件查问，MySQL 无奈再应用范畴列前面的其余索引列了。不过对于“多个等值条件查问”（IN）则没有这个限度。

如果有两个范畴条件，能够尝试将其中一个通过利用改写成等值或列表查问。例如下面的最近一次上线工夫在本周内的，能够保护一个 active 的字段，登录时设置为 1，超过 7 天未登录设置为 0，而后进行等值查问。

优化排序

应用索引列来排序能够大大晋升查问的效率，不过也不是每个排序语句都能利用索引来进行优化，有些时候还是不得不应用文件排序。应用文件排序对小数据集是很快的，但如果一个查问匹配的后果有上百万行的话会怎么？例如如果 WHERE 子句只有 sex 列，如何排序？

对于那些选择性非常低的列，能够减少一些非凡的索引来做排序。例如，能够创立（sex，rating）索引用于上面的查问：

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

这个查问同时应用了 ORDER BY 和 LIMIT，如果没有索引的话会很慢。即便有索引，如果用户界面上须要翻页，并且翻页翻到比拟靠后时查问也可能十分慢。

无论如何创立索引，这种查问都是个重大的问题。因为随着偏移量的减少，MySQL 须要破费大量的工夫来扫描须要抛弃的数据。反范式化、事后计算和缓存可能是解决这类查问的仅有策略。一个更好的方法是限度用户可能翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜寻后果的第 10000 页。

优化这类索引的另一个比拟好的策略是应用提早关联，通过应用笼罩索引查问返回须要的主键，再依据这些主键关联原表取得须要的行。这能够缩小 MySQL 扫描那些须要抛弃的行数。

总结

在抉择索引和编写利用这些索引的查问时，有如下三个准则始终须要记住：

  1. 单行拜访是很慢的。特地是在机械硬盘存储中（SSD 的随机 I / O 要快很多，不过这一点依然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就节约了很多工作。最好读取的块中能蕴含尽可能多所须要的行。应用索引能够创立地位援用以晋升效率。
  2. 按程序拜访范畴数据是很快的，这有两个起因。第一，程序 I / O 不须要屡次磁盘寻道，所以比随机 I / O 要快很多（特地是对机械硬盘）。第二，如果服务器可能按须要程序读取数据，那么就不再须要额定的排序操作，并且 GROUP BY 查问也无须再做排序和将行按组进行聚合计算了。
  3. 索引笼罩查问是很快的。如果一个索引蕴含了查问须要的所有列，那么存储引擎就不须要再回表查找行。这防止了大量的单行拜访，而下面的第 1 点曾经写明单行拜访是很慢的。
退出移动版