PHP面试必备 | MySQL 索引使用策略及优化
|
MySQL 的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。 本文讨论的高性能索引策略主要属于结构优化范畴。本文的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。 一、示例数据库为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用 MySQL 官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的 E-R 关系图（引用自 MySQL 官方手册）：
二、最左前缀原理与相关优化高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和 B+Tree 中的 “最左前缀原理” 有关，下面通过例子说明最左前缀原理。 这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL 中的索引可以以一定顺序引用多个列，这种索引叫做联合索引。 一般的，一个联合索引是一个有序元组 <a1,a2,…,an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为 1 的特例。 以 employees.titles 表为例，下面先查看其上都有哪些索引：
三、EXPLAIN在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的 SQL 语句，找出这些 SQL 语句并不意味着完事了，些时我们常常用到 explain 这个命令来查看一个这些 SQL 语句的执行计划，查看该 SQL 语句有没有使用上了索引，有没有做全表扫描，这都可以通过 explain 命令来查看。 所以我们深入了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行 SQL 语句时哪种策略预计会被优化器采用。 EXPLAIN 出来的信息有 10 列，分别是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 概要描述：
四、具体内容情况一：全列匹配
很明显，当按照索引中所有列进行精确匹配（这里精确匹配指 “=” 或 “IN” 匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，例如我们将 where 中的条件顺序颠倒效果是一样的。 情况二：最左前缀匹配
当查询条件精确匹配索引的左边连续一个或几个列时，如或 <emp_no,title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了 PRIMARY 索引，但是 key_len 为 4，说明只用到了索引的第一列前缀。 情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供
此时索引使用情况和情况二相同，因为 title 未提供，所以查询只用到了索引的第一列，而后面的 from_date 虽然也在索引中，但是由于 title 不存在而无法和左前缀连接，因此需要对结果进行扫描过滤 from_date（这里由于 emp_no 唯一，所以不存在扫描）。 如果想让 from_date 也使用索引而不是 where 过滤，可以增加一个辅助索引 <emp_no,from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为 “隔离列” 的优化方法，将 emp_no 与 from_date 之间的 “坑” 填上。 首先我们看下 title 一共有几种不同的值：
只有 7 种。在这种成为 “坑” 的列值比较少的情况下，可以考虑用 “IN” 来填补这个 “坑” 从而形成最左前缀：
这次 key_len 为 56，说明索引被用全了，但是从 type 和 rows 看出 IN 实际上执行了一个 range 查询，这里检查了 7 个 key。 “填坑” 后性能提升了一点。如果经过 emp_no 筛选后余下很多数据，则后者性能优势会更加明显。当然，如果 title 的值很多，用填坑就不合适了，必须建立辅助索引。 情况四：查询条件没有指定索引第一列
由于不是最左前缀，索引这样的查询显然用不到索引。 情况五：匹配某列的前缀字符串
此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引。（原文表述有误，如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀） 情况六：范围查询
范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
可以看到索引对第二个范围索引无能为力。这里特别要说明 MySQL 一个有意思的地方，那就是仅用 explain 可能无法区分范围索引和多值匹配，因为在 type 中这两者都显示为 range。同时，用了 “between” 并不意味着就是范围查询，例如下面的查询：
看起来是用了两个范围查询，但作用于 emp_no 上的 “BETWEEN” 实际上相当于 “IN”，也就是说 emp_no 实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在 MySQL 中要谨慎地区分多值匹配和范围匹配，否则会对 MySQL 的行为产生困惑。 情况七、索引选择性与前缀索引 既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。 第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以 2000 作为分界线，记录数不超过 2000 可以考虑不建索引，超过 2000 条可以酌情考虑索引。 另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值： 显然选择性的取值范围为 (0,1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。例如，上文用到的 employees.titles 表，如果 title 字段经常被单独查询，是否需要建索引，我们看一下它的选择性：
title 的选择性不足 0.0001（精确值为 0.00001579），所以实在没有什么必要为其单独建索引。 有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。下面以 employees.employees 表为例介绍前缀索引的选择和使用。 从示例数据库图可以看到 employees 表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了，如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建或 <first_name,last_name>，看下两个索引的选择性：
显然选择性太低，选择性很好，但是 first_name 和 last_name 加起来长度为 30，有没有兼顾长度和选择性的办法？ >> 可以考虑用 first_name 和 last_name 的前几个字符建立索引，例如， 看看其选择性：
这时选择性已经很理想了，而这个索引的长度只有 18，比短了接近一半，我们把这个前缀索引 建上：
点关注，不迷路好了各位，以上就是这篇文章的全部内容了，能看到这里的人呀，都是人才。之前说过，PHP方面的技术点很多，也是因为太多了，实在是写不过来，写过来了大家也不会看的太多，所以我这里把它整理成了PDF和文档，如果有需要的可以 点击进入暗号: PHP+「平台」
更多学习内容可以访问【对标大厂】精品PHP架构师教程目录大全，只要你能看完保证薪资上升一个台阶（持续更新） 以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家，需要的可以加入我的 PHP技术交流群 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
