MySQL优化主要分为结构优化（Schemeoptimization）和查询优化（Queryoptimization）。本文讨论的高性能索引策略主要属于结构优化的范畴。本文的内容完全建立在上述理论基础之上。事实上，一旦理解了指标背后的机制，那么高性能策略的选择就变成了纯粹的推理，也就可以理解这些策略背后的逻辑了。1.实例数据库为了讨论索引策略，需要一个数据量比较大的数据库作为实例。本文选择MySQL官方文档中提供的示例数据库之一：employees。该数据库关系复杂度适中，数据量大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：2.最左前缀原理及相关优化高效使用索引的首要条件是知道什么样的查询会使用索引。这个问题类似于B+Tree中的“最左前缀原则”，与最左前缀原则有关。下面的例子说明了最左前缀原则。先说一下联合索引的概念。在上面，我们都假设索引只引用单个列。事实上，MySQL中的索引可以按照一定的顺序引用多个列。这种索引称为联合索引。联合索引一般是一个有序的元组，其中每个元素是数据表的一列。其实要严格定义索引需要用到关系代数，但是这里不想过多讨论多关系代数的话题，那样会显得很无聊，所以这里就不做严格定义了.另外，单列索引可以看作是联合索引中元素个数为1的一种特殊情况。以employees.titles表为例，我们先看看它上面有哪些索引：3.EXPLAIN中日常工作中，我们有时会打开慢查询来记录一些执行时间较长的SQL语句，找出这些SQL语句并不代表工作就结束了。有时我们经常会使用explain命令来查看其中一条SQL语句的执行计划，检查SQL语句是否使用了索引，是否进行了全表扫描。这可以通过解释命令来完成。查看。所以我们对MySQL的基于成本的优化器有了深刻的了解，也可以得到很多细节，优化器可能会考虑的访问策略，运行SQL语句时希望优化器采用哪些策略。EXPLAIN的信息有10列，分别是id,select_type,table,type,possible_keys,key,key_len,ref,rows,ExtraSummarydescription:id:selectidentifierselect_type:表示查询的类型。table：输出结果集的表type：表示表的连接类型all（全表扫描），index（按索引顺序全表扫描），range（范围索引扫描）req（搜索条件列使用anindexanddoesnotprimarykeyandunique,索引列的值不唯一),ref_eq(当使用主键或唯一索引来查找时),const(主键放在where后面作为条件查询,而mysql优化器可以把这个Query优化转化为一个常量）possible_keys：表示查询时可能使用的索引key：表示实际使用的索引key_len：索引字段的长度ref：列与列的比较indexrows：扫描的行数（预估行数）Extra：执行情况的描述和解释4.具体内容情况一：所有列匹配explainSELECT*FROMemployees.titlesWHEREemp_no='10001'ANDtitle='高级E工程师'ANDfrom_date='1986-06-26';很明显，当对索引中的所有列执行精确匹配（这里的精确匹配是指“=”或“IN”匹配）时，可以使用索引。这里需要注意的一点是，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用合适的索引，例如，如果我们将条件的顺序颠倒过来其中，效果是一样的。情况2：最左边的前缀匹配EXPLAINSELECT*FROMemployees.titlesWHEREemp_no='10001';当查询条件正好匹配索引左边的一个或几个列时，如or，可以使用，但只能使用一部分，即条件构成的最左边前缀.上面的查询从分析结果中使用了PRIMARY索引，但是key_len为4，说明只使用了索引的第一列前缀。情况三：查询条件使用了索引中列的精确匹配，但是中间某个条件没有提供EXPLAINSELECT*FROMemployees.titlesWHEREemp_no='10001'ANDfrom_date='1986-0626';这时候索引的用法和情况两者是一样的，因为没有提供title，所以查询只使用了索引的第一列，后面的from_date也在索引中，但是无法与left前缀因为title不存在，所以需要对from_date进行扫描过滤结果（这里，由于emp_no是唯一的，所以没有扫描）。如果想让from_date使用index而不是wherefilter，可以添加一个辅助索引，上面的查询就会使用这个索引。此外，还可以使用一种叫做“隔离列”的优化方法来填补emp_no和from_date之间的“坑”。首先我们看title有几个不同的值：只有7个。在列值比较少的情况下变成“坑”，可以考虑用“IN”填满“坑”，形成最左边的前缀：这次key_len是56，说明索引被完全使用了，但是从type和rows看IN其实是进行了范围查询，这里检查了7个key。“填坑”后，性能略有提升。如果emp_no过滤后剩余的数据较多，后者的性能优势会更加明显。当然，如果title值很多，就不适合填坑了，必须建立一个辅助索引。情况四：查询条件没有指定索引的第一列。由于不是最左边的前缀，所以索引之类的查询显然不用索引。Case5：可以使用索引来匹配列的前缀字符串，但是如果通配符不只出现在末尾，则不能使用索引。（原文有错误，如果开头没有出现通配符%，可以使用索引，但根据具体情况，可能只使用其中一个前缀。）情况六：范围查询范围列可以使用索引（必须是最左边的前缀），但范围列之后的列不能使用索引。同时，索引最多用于一个范围列，所以如果查询条件中有两个范围列，则无法充分利用索引。您可以看到索引对第二个范围索引无能为力。这里MySQL有一个比较有意思的地方，就是只用explain可能无法区分范围索引和多值匹配，因为两者在type上都是显示为range。同时，使用“between”并不代表它是范围查询，比如下面的查询：看似使用了两个范围查询，但是作用于emp_no的“BETWEEN”其实等同于“IN”，也就是说emp_no其实是一个多值精确匹配。您可以看到此查询使用了索引的所有三列。因此，需要仔细区分MySQL中的多值匹配和范围匹配，否则会混淆MySQL的行为。情况七：索引选择性和前缀索引既然索引可以加快查询速度，那么是不是只要查询语句需要就建索引呢？答案是否定的。虽然索引加快了查询速度，但是索引也有代价：索引文件本身会消耗存储空间，索引会增加插入、删除、修改记录的负担。另外，MySQL在运行时也会消耗资源来维护索引。所以索引并不是越多越好。一般来说，有两种情况不建议建立索引。第一种情况是表记录比较少，比如一张表有一两千条甚至只有几百条记录，不需要建索引，让查询做全表就可以了扫描。至于记录多少算多，这个人有个人看法。我个人的经验是以2000为分界线。如果记录数不超过2000条，可以考虑不建索引，如果记录数超过2000条，可以考虑酌情建立索引。另一种不建议建立索引的情况是当索引的选择性较低时。所谓索引选择性（Selectivity）是指唯一索引值（也叫基数，Cardinality）与表记录数（#T）的比值：索引选择性=基数/#T显然，选择性的取值范围is(0,1]，选择性越高，索引的值就越大，这是由B+Tree的性质决定的。比如上面用到的employees.titles表，如果经常查询title字段需要单独建索引吗，我们看一下它的选择性：title的选择性小于0.0001（准确值为0.00001579），所以真的没必要单独建索引。有一种与索引选择性相关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引键，当前缀的长度合适的时候，前缀索引的选择性可以接近到完整列索引的那个，并且在sa现在，索引文件的大??小和维护开销都可以减少，因为索引键变得更短了。下面以employees.employees表为例介绍前缀索引的选择和使用。从示例数据库图中我们可以看出，employees表只有一个索引，所以如果我们要按姓名搜索一个人，只能扫描全表。如果我们频繁的按姓名搜索员工，这显然是非常低效的，所以我们可以考虑建立一个索引。有两个选项，buildor，看两个索引的选择性：明显选择性太低了，选择性很好，但是first_name和last_name的总长度是30，有没有平衡点长度和选择性之间的方式？>>可以考虑使用first_name和last_name的前几个字符来建立索引。比如看它的选择性：这时候选择性已经很理想了，这个索引的长度只有18，比那个短了将近一半。我们把这个建立一个前缀索引：ALTERTABLEemployees.employeesADDINDEX`first_name_last_name4`(first_name,last_name(4));此时再次按名称执行查询，与建立索引前的结果对比分析：性能提升显着，查询速度提升120多倍。前缀索引兼顾了索引大小和查询速度，但它的缺点是不能用于ORDERBY和GROUPBY操作，也不能用于Covering索引（即当索引本身包含所有需要的数据时对于查询，不再访问数据文件本身）。大家注意点，别迷路了，以上就是本文的全部内容，能看到这里的都是人才。前面说了PHP的技术点很多，也是因为太多了，写的太多了，写完了也不会看太多，所以我这里整理成了PDF和文档，有需要的可以点击进入秘籍：PHP+「平台」更多学习内容可以访问【比大厂】优质PHP架构师教程目录，只要会看，薪资高会更上一层楼（持续更新）。以上内容希望对大家有所帮助，很多PHPer在进阶的时候总会遇到一些问题和瓶颈。业务代码写多了就没有方向感。架构、高扩展、高性能、高并发、服务器性能调优、TP6、laravel、YII2、Redis、Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等进阶知识点级别的干货，可以免费分享给大家，需要的话可以加入我的PHP技术交流群
