PHP面试必备 | MySQL 索引使用策略及优化

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《PHP面试必备 | MySQL 索引使用策略及优化》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

MySQL 的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。

本文讨论的高性能索引策略主要属于结构优化范畴。本文的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

一、示例数据库

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用 MySQL 官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的 E-R 关系图（引用自 MySQL 官方手册）：

二、最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和 B+Tree 中的 “最左前缀原理” 有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL 中的索引可以以一定顺序引用多个列，这种索引叫做联合索引。

一般的，一个联合索引是一个有序元组 ，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为 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

概要描述：

• id: 选择标识符
• select_type: 表示查询的类型。
• table: 输出结果集的表
• type: 表示表的连接类型

all（全表扫描） 、index（按照索引顺序的全表扫描）、range (有范围的索引扫描)
req (查找条件列使用了索引而且不为主键和unique, 使用该索引列的值并不唯一)、ref_eq（使用了主键或者唯一性索引进行查找的情况）、
const（主键放置到 where后面作为条件查询，mysql 优化器就能把这次查询优化转化为一个常量）

• possible_keys: 表示查询时，可能使用的索引
• key: 表示实际使用的索引
• key_len: 索引字段的长度
• ref: 列与索引的比较
• rows: 扫描出的行数 (估算的行数)
• Extra: 执行情况的描述和说明

四、具体内容

情况一：全列匹配

Index Selectivity = Cardinality / #T

显然选择性的取值范围为 (0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。例如，上文用到的 employees.titles 表，如果 title 字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

title 的选择性不足 0.0001（精确值为 0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。下面以 employees.employees 表为例介绍前缀索引的选择和使用。

从示例数据库图可以看到 employees 表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了，如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建或 ，看下两个索引的选择性：

显然选择性太低，选择性很好，但是 first_name 和 last_name 加起来长度为 30，有没有兼顾长度和选择性的办法？ >> 可以考虑用 first_name 和 last_name 的前几个字符建立索引，例如， 看看其选择性：

这时选择性已经很理想了，而这个索引的长度只有 18，比短了接近一半，我们把这个前缀索引 建上：

ALTER TABLE employees.employees ADD INDEX `first_name_last_name4 `(first_name, last_name (4)); 

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于 Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

点关注，不迷路

好了各位，以上就是这篇文章的全部内容了，能看到这里的人呀，都是人才。之前说过，PHP方面的技术点很多，也是因为太多了，实在是写不过来，写过来了大家也不会看的太多，所以我这里把它整理成了PDF和文档，如果有需要的可以

点击进入暗号: PHP+「平台」

更多学习内容可以访问【对标大厂】精品PHP架构师教程目录大全，只要你能看完保证薪资上升一个台阶（持续更新）

以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家，需要的可以加入我的 PHP技术交流群

好了，本文到此结束，带大家了解了《PHP面试必备 | MySQL 索引使用策略及优化》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！