MySQL高阶调优，一文让你从入门到精通！

2023-12-1209:52:54数据库教程Comments1,539 views字数 11341阅读模式

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【前言】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

文章受众主要为两类人：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

第二类人是专职DBA。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

理论+实战=高薪文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

文章能够让大家有所收获、有所借鉴那是最好的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【MySQL逻辑架构】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1、整体架构图文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

各层介绍：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1.1 连接层文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

最上层是客户端，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1.2 服务层文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1.3.引擎层 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

存储引擎负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

show engines:查看所有的数据库引擎文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

show variables like '%engine%' 查看默认的数据库引擎文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MyISAM和InnoDB对比文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1.4.存储层 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2、查询流程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL的查询流程大致是：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

客户端通过协议与DB服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。解析器将使用语法规则验证和解析查询；预处理器则根据一些规则进一步检查解析数是否合法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

流程图：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3、SQL的执行顺序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

一般SQL语法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL解析：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

真正执行的顺序：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4、SQL性能问题文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

常见的几个点：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • SQL逻辑复杂且糟糕
  • 无效的索引
  • 服务器调优和各个参数的配置（缓冲，线程数等）

SQL逻辑复杂且糟糕，通常表现在嵌入很多子查询，各种表关联等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

无效的索引，通常表现为索引建立了但没用上，索引分为单值索引和复合索引和唯一索引，所谓单值索引指的是一个索引有且只包含一个列，一个表中可以有多个单列索引。复合索引是指索引能够同时覆盖多个数据列；唯一索引是指索引列的值必须唯一，但可以为null。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

服务器调优一般部署MySQL阶段就调整好了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【索引知识】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

面试的时候，提起索引不要再拿目录类比索引的优点了，这样显的很菜。一句话：索引是数据结构，是一个排好序且快速查找的数据结构。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1、索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

查询是数据库的最主要功能之一，速度当然是越快越好，因此数据库系统的设计者会从查询算法的角度进行优化。常见的查询算法有顺序查找（linear search）、二分查找（binary search）、二叉树查找（binary tree search）等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

这种数据结构，就是索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

看一个例子：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

图1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

图1展示了一种可能的索引方式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2、索引结构文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL 索引一般是哈希表或 B+ 树，常用的 InnoDB 引擎默认使用的是 B+ 树来作为索引的数据结构。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.1为什么是B+树文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

B+树是B树的变种，是基于B树来改进的。为什么B+树会比B树更加优秀呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

B树：有序数组+平衡多叉树；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html


B+树：有序数组链表+平衡多叉树；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

B+ 树查找过程

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 表示数据项小于 17 的磁盘块，P2 表示数据项在 17 和 35 之间的数据项，P3 表示数据项大于 35 的数据项。非叶子节点不储存数据，只储存指引搜索方向的数据项。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

我们知道每次 IO 读取一个数据页的大小，也就是一个磁盘块。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

假设我们要查找 29 这个数据项，首先进行第一次 IO 将磁盘块 1 读进内存，发现17 < 29 < 35，然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存，发现26 < 29 < 30，然后选用 P2 指针将磁盘块 8 读进内存，在内存中做二分查找，找到 29，结束查询。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

通过分析查询过程，我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度，M 为每个磁盘块的数据项个数，N 为数据项总数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

从下面的公式可以看出如果数据量N一定，M越大相应的H越小。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

M 等于磁盘块的大小除以数据项大小，由于磁盘块大小一般是固定的，所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

如果真实数据放在非叶子结点，磁盘块存储的数据项会大幅度减少，树就会增高相应查询数据时的 IO 次数就会变多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3、索引实现文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在MySQL中，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3.1、MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

下图是MyISAM索引的原理图：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3.2、InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1、InnoDB的数据文件本身就是索引文件。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2、InnoDB的辅助索引data域存储相应记录主键的值而不是地址。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4、如何使用索引 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

说了那么多原理，总结一下索引的优缺点和使用时机。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4.1、优点文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • 提高数据检索的效率，降低数据库的IO成本；
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

所以记住，索引功能是：搜索+排序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4.2、缺点文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • 索引提升查询效率的同时也会降低更新的效率，更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

可见，Hash Join也依赖Join Buffer，在最好的场景下，如果Join Buffer能覆盖驱动表所有相关字段，那么在查询的过程中驱动表和被驱动表都只需要扫描一次，如果散列算法够好，比较次数也只是被驱动表的记录数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

Hash Join只能用于等值连接，大表连接Hash Join的优化效果比较明显。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

优化思路文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • 用小结果集驱动大结果集，尽量减少 join 语句中的Nested Loop循环总次数。
  • 优先优化 Nested Loop 内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能。
  • 对被驱动表的 join 字段上建立索引，并且Join ON 条件的字段应该是相同类型的。
  • 当被驱动表的 join 字段上无法建立索引的时候，设置足够的 Join Buffer Size。
  • 对于非主键的连接查询，如果被驱动表数据特别多，建议先使用子查询查出一个临时的结果集然后再连接。（待验证）
  • 对于可以直接从一个表中取数据的情况。（例如同一个表中取交集，例如好友表，互相关注才是好友）这样的情况，使用 Join 效率是要高于子查询的。

【总结】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

掌握表连接算法，结合实践中不断的实验和摸索，从而真正达到高效使用MySQL算法的目的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【EXPLAIN执行计划解读】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

使用explain分析SQL的执行计划，从而知道MySQL是如何处理SQL语句的，有助于分析SQL语句的性能瓶颈。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

本文截图基于MySQL版本5.7.27。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1、explain的作用

通过explain+sql语句可以知道如下内容：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • 表的读取顺序。（id）
  • 数据读取操作的操作类型。（select_type）
  • 显示sql操作属于哪张表的（table)
  • 哪些索引可以使用。（possible_keys）
  • 哪些索引被实际使用。（key）
  • 表直接的引用。（ref）
  • 每张表有多少行被优化器查询。（rows）

2、explain包含的信息 

通过执行explain可以获得sql语句执行的相关信息。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

下面对explain的表头字段含义进行解释。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.1、ID 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

ID列：描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1> id相同，执行顺序从上到下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2> id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3> id相同不同，同时存在。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.2、select_type文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.3、table 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

显示sql操作属于哪张表的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.4、type 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

需要记住的文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

system>const>eq_ref>ref>range>index>ALL文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

一般来说，得保证查询至少达到range级别，最好能达到ref。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1> system文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

Const：表示通过索引一次就找到了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

注：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2> eq_ref 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3> ref 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4> range 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

这种索引的范围扫描比全索引扫描要好，因为索引的开始点和结束点都固定，范围相对较小。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5> index 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

6> ALL文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

全表扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.5、possible_keys和key、key_len 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

possible_keys:可能使用的key。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

Key:实际使用的索引。如果为NULL，则没有使用索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.6、ref 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.7、rows 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.8、filtered文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2.9、Extra文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

包含不适合在其他列中显示但十分重要的额外信息。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【总结】

  • id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。
  • type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。
  • key，实际被使用的索引列。
  • ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。
  • Extra，额外信息，使用优先级Using index>Using filesort>Using temporary。

着重关注上述五个字段信息，结合实践中不断的实验和摸索，对调优十分有用。 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【SQL调优实战】 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

1、环境准备文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

每张表模拟一些数据进去。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

article表CREATE TABLE IF NOT EXISTS `article`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`author_id` INT (10) UNSIGNED NOT NULL,`category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL,`title` VARBINARY(255) NOT NULL,`content` TEXT NOT NULL);
class表 CREATE TABLE IF NOT EXISTS `class`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL);
book表 CREATE TABLE IF NOT EXISTS `book`(`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL); phone表CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)ENGINE = INNODB;
staffs表CREATE TABLE staffs(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间')CHARSET utf8 COMMENT'员工记录表';
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

2、单表优化案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

需求分析：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

查询category_id为1且comments大于1的情况下，views最多的article_id文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.7版本后添加了列filtered，意思是：指返回结果的行占需要读到的行(rows列的值)的百分比，filtered的数值其实越高，表示通过索引直接返回的行很多，数值较低时，一般出现在type=ALL或者index的情况。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

分析下这个执行计划，type=ALL全表扫，而且产生了filesort。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

where条件加个复合索引看看：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

create index idx_atc_ccv on article(category_id,comments,views);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

再看执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

虽然走了索引，但也走到了filesort，还是不够好；这个索引不起作用吗？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在Mysql中，索引中出现了范围查找，后面就失效，comments出现了范围，索引在找的时候，发现comments无法直接定位到，影响了order by views的索引排序，进而出现了filesort。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

那假设我们把sql调整为comments = 1再看看执行计划。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

filesort没有了，type一下从range变成了ref，执行计划是好的，但是业务变了，不行！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

那么怎么创建索引合适呢？既然范围之后索引失效，那么我们能不能绕过去？直接新建category_id, views的复合索引呢。（删除之前创建的索引）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划告诉我们，这个索引加的很合适！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

结论：type变成了range，这是可以忍受的，但是Extra里出现了filesort是无法接受的，但是我们建立了索引为什么没有用，这是因为按照Mysql的BTREE工作原理，先排序category_id，如果遇到相同的，再排序comments，如果遇到相同的，再排序views，当comments位置处于联合（复合）索引的中间位置时，Mysql无法对范围（range）后面的字段进行索引排序，从而后面的字段索引失效！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

3、两表优化案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

来看个SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from class left join book on class.card = book.card;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

明显这个type为ALL，索引也没有加。问题来了，索引加哪边？是加class.card还是book.card？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

我们都试试，先添加右边book表的索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

alter table book add index idx_b_card(card);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划走下：book的很明显的改变，type变成了ref文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

此时我把book表的索引删掉，而建立class左表的索引看看执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

明显，加了class表的索引后，发现type是index，并且rows20行记录，全索引扫描，性能不会有刚刚的好！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

同样的sql，同样的索引列，左连接的时候，加的索引所在的表不同，效果不同；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

结论：上面出现效果不同，这个是由左连接的特性决定的，left join 条件用于确定如何从右边搜索行，而左边一定是都有的；左边全有，确定核心的点在于确定如何从右表中搜索数据行，右边是关键点，要加索引！所以左连接索引加在右表上，同理，右连接也是相反加！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

有没有人好奇，如果两个索引都建呢会是什么样？我们尝试下加上看看：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

现在book和class表上的card字段都加了索引，效果比上面两个都好！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

4、三表优化案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

先把之前创建的索引都清除掉。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from classleft join book on class.card = book.card left join phone on book.card = phone.card; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

此时三个表都没有索引：我们走下执行计划后发现，Extra字段多了Using join buffer；首先join buffer意思是使用了连接缓存。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在5.7之后，Mysql对表和表之间的连接，做了优化缓存，实际上在A left join B的过程，Mysql会更在意B的表往A中相同的部分，所以类似一个for循环，最外层for A，内层是for B，找到B中的每一行满足A行的记录，因为是要A的全部，所以最外层一定是A，然后合并行，最后输出；而在3表中，等于3个for循环。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

其中其实发现有个Block Nested-Loop Join——BNL算法，这个算法将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。所以最外层的表是class，先for整个class，然后放在join buffer里，接下来循环内表的时候，直接取buffer的行去比对，减少对磁盘的IO。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

但是整个type=ALL，rows都是20，全表扫，这是我们无法接受的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

那么三张表怎么加索引呢？可以想想，左连接建右表上，那么这个是不是说class左表，建立索引在book和phone上？试试！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

走下执行计划看看：很明显，改善很多!文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

那么很明显这个原则也成立，总结下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

尽可能减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集，这里的例子，就是左表尽量数据小于右表，外层for的次数就减少了，IO次数也会降低。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

其实你可以试试，如果class表加了索引，效果会比右连接稍微好点，哈哈文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5、索引失效案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.1建个复合索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

create index idx_s_nap on staffs(name,age,pos);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name='July'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

索引会不会失效？执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

没问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

再来一个SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name='July' and age = 25; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

一样没问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

但是这里其实有个问题，Extra为Null。Extra为Null的时候，如果走了索引，说明这个查询，进行了回表！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

那么什么是回表呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

简单来说，如果你查询的字段，存在非索引字段，那么查询的时候，Mysql虽然根据了你的条件得到了这个记录，但是不在索引的字段无法通过索引的方式直接得到，只能通过拿到该条记录的主键索引，再从数据行里读，我们知道Mysql索引文件和数据文件是在两个不同的文件里的，要去读磁盘；所以索引文件建立的效果，就是帮助我们对数据进行排序和查找效率的优化，不至于去读数据行进行额外的IO开销；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

所以这里字段我用select *，因为复合索引里没有add_time这个字段，所以无法直接查出来add_time这个列的记录，要通过定位到主键，然后再读一次数据行才可以得到这个记录，称为回表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

如果SQL这么写，就不会出现回表，因为pos在索引列中！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select pos from staffs where name='July' and age = 25; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

我们来看一些特殊场景！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where age = 23 and pos = 'dev';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

再来一个sql：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name = 'zhangsan';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

走索引了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

总结：如果查询中没有开头的索引，不好意思，只能全表扫。违背了【最佳左前缀法则】文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

再看下这个sql：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name = 'zhangsan' and pos = 'dev'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

执行计划显示这个key_len和只有name的时候一样，说明只走了name索引，Extra中出现Using index condition，这个是5.6后新加的特性，会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；就是走到了索引上的意思。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.2、勿在索引列做任何操作文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

不要在索引列上做任何操作，包括计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where left(name, 4) = 'July';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

查找name左往右4个字符为July的行。索引失效了！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.3、范围之后全失效文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name = 'July' and age > 14 and pos = 'manager'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

age用到了索引，进行范围查找，但是后面的索引pos就失效了，这里要注意，5.7以前的优化，是如果出现了范围查找，则当前范围的索引也不走，而5.7后，范围索引之后的才失效，所以这里的key_len=78，单个name话是74，三个都走是140。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.4、不等于场景下索引失效文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name != 'July'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name <> 'July';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

在使用不等于的场景下，无法使用索引导致全表扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.5、is null、is not null无法使用索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name is null; select * from staffs where name is not null;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.6、Like百分写最右文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

like以通配符开头('%abc...')时，Mysql索引会失效变成全表扫！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name like '%July%';select * from staffs where name like '%July'; select * from staffs where name like 'July%'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

因为like是范围查找，百分号在后面，Mysql会拿到字典序进行排序的方式查找对应的情况，而百分号在前面，Mysql就不知道从哪个字母开始找，于是便全表扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

实际面试中经常会这么问：如何解决like '%xxx%' 字符时索引不被使用的情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

答案是用覆盖索引避免索引失效，我们这里的索引是(name, age, pos)，索引我们在查询的时候不要写select *，只要写具体的字段值，任何一个列被覆盖索引覆盖，就可以解决两边百分号的问题！！！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.7、字符串不加单引号索引失效文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

explain select * from staffs where name = 222; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

索引失效。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

而这个是成功走到索引的：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name = '222';文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

Mysql很聪明，你以为你给我的我就查不到了，你给我的Int型的时候，实际这个字段是varchar型，传入数字会隐式的帮你转换成varchar类型，前面说过不要让Mysql做这些自动或者手动的类型转换，否则索引失效！当然查询的结果，是不会有变化的，只是sql执行上有转换。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

5.8、少用or文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

SQL：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

select * from staffs where name = 'July' or name = 'z3'; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL高阶调优，一文让你从入门到精通！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

少用or，会导致索引失效，不是不用；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

【结语】 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

MySQL调优篇写到这里就差不多告一段落了，希望大家都能真真正正能写出高性能的SQL，结合实践中不断的实验和摸索，早日晋级资深或者架构师。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

来源：数据与人文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/57956.html

  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/57956.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定