产品中心
很多时候，我们的慢查询，都是因为没有加索引。如果没有加索引的话，会导致全表扫描的。因此，应考虑在 where 的条件列，建立索引，尽量避免全表扫描。
反例：
正例：
有时候我们明明加了索引了，但是索引却不生效。
在哪些场景，索引会不生效呢？主要有以下十大经典场景：
我们创建一个用户 user 表：
如果给数字加上’', 也就是说，传的是一个字符串呢，当然是走索引，如下图：
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。
我们还是用这个表结构：
对于 or+ 没有索引的 age 这种情况，假设它走了 userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描 + 索引扫描 + 合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql 优化器出于效率与成本考虑，遇到 or 条件，让索引失效，看起来也合情合理嘛。
注意：如果 or 条件的列都加了索引，索引可能会走也可能不走，大家可以自己试一试哈。但是平时大家使用的时候，还是要注意一下这个 or，学会用 explain 分析。遇到不走索引的时候，考虑拆开两条 SQL。
并不是用了 like 通配符，索引一定会失效，而是 like 查询是以 % 开头，才会导致索引失效。
like 查询以 % 开头，索引失效
把 % 放后面，发现索引还是正常走的，如下：
既然 like 查询以 % 开头，会导致索引失效。我们如何优化呢？
使用覆盖索引把 % 放后面 2.4 查询条件不满足联合索引的最左匹配原则
MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。
假设有以下表结构：
在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。
表结构：
一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：
所以不可以对索引列进行运算，可以在代码处理好，再传参进去。
表结构：
其实这个也是跟 mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者 < >，not in 的时候，留点心眼哈。
表结构:
单个 card 字段加上索引，并查询 name 为非空的语句，其实会走索引的，如下:图片
但是它两用 or 连接起来，索引就失效了，如下：
很多时候，也是因为数据量问题，导致了 MySQL 优化器放弃走索引。同时，平时我们用 explain 分析 SQL 的时候，如果 type=range, 要注意一下哈，因为这个可能因为数据量问题，导致索引无效。
新建两个表，一个 user，一个 user_job
执行左外连接查询，user_job 表还是走全表扫描，如下：
如果把它们的 name 字段改为编码一致，相同的 SQL，还是会走索引。
所以大家在做表关联时，注意一下关联字段的编码问题哈。
MySQL 中一张表是可以支持多个索引的。你写 SQL 语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由 MySQL 来确定的。
我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致 MySQL 选错索引。那么有哪些解决方案呢？
使用 force index 强行选择某个索引修改你的 SQl，引导它使用我们期望的索引优化你的业务逻辑优化你的索引，新建一个更合适的索引，或者删除误用的索引。
limit 深分页问题，会导致慢查询，应该大家都司空见惯了吧。
limit 深分页为什么会导致 SQL 变慢呢？假设我们有表结构如下：
limit 深分页，导致 SQL 变慢原因有两个：
limit 语句会先扫描 offset+n 行，然后再丢弃掉前 offset 行，返回后 n 行数据。也就是说 limit 100000,10，就会扫描 100010 行，而 limit 0,10，只扫描 10 行。limit 100000,10 扫描更多的行数，也意味着回表更多的次数。 3.2 如何优化深分页问题
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到 100000，则 SQL 可以修改为：
假设 B + 树的高度为 2 的话，即有一个根结点和若干个叶子结点。这棵 B + 树的存放总记录数为 = 根结点指针数 * 单个叶子节点记录行数。
如果一行记录的数据大小为 1k，那么单个叶子节点可以存的记录数 =16k/1k =16.非叶子节点内存放多少指针呢？我们假设主键 ID 为 bigint 类型，长度为 8 字节 (面试官问你 int 类型，一个 int 就是 32 位，4 字节)，而指针大小在 InnoDB 源码中设置为 6 字节，所以就是 8+6=14 字节，16k/14B =16*1024B/14B = 1170
因此，一棵高度为 2 的 B + 树，能存放 1170 * 16=18720 条这样的数据记录。同理一棵高度为 3 的 B + 树，能存放 1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B + 树高度一般为 1-3 层，已经满足千万级别的数据存储。
如果 B + 树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘 IO 变多，因此查询性能变慢。
一般超过千万级别，我们可以考虑分库分表了。
分库分表可能导致的问题：
因此，大家在评估是否分库分表前，先考虑下，是否可以把部分历史数据归档先，如果可以的话，先不要急着分库分表。如果真的要分库分表，综合考虑和评估方案。比如可以考虑垂直、水平分库分表。水平分库分表策略的话，range 范围、hash 取模、range+hash 取模混合等等。
一般来说，不建议使用子查询，可以把子查询改成 join 来优化。而数据库有个规范约定就是：尽量不要有超过 3 个以上的表连接。为什么要这么建议呢？我们来聊聊，join 哪些方面可能导致慢查询吧。
MySQL 中，join 的执行算法，分别是：Index Nested-Loop Join 和 Block Nested-Loop Join。
join 过多的问题：
一方面，过多的表连接，会大大增加 SQL 复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join 是在 join_buffer 内存做的，如果匹配的数据量比较小或者 join_buffer 设置的比较大，速度也不会太慢。但是，如果 join 的数据量比较大时，mysql 会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。
一般情况下，如果业务需要的话，关联 2~3 个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成 map，然后在业务层进行数据的拼装。
如果使用了 in，即使后面的条件加了索引，还是要注意 in 后面的元素不要过多哈。in 元素一般建议不要超过 500 个，如果超过了，建议分组，每次 500 一组进行哈。
反例：
InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志）。平时更新 SQL 执行得很快，其实是因为它只是在写内存和 redo log 日志，等到空闲的时候，才把 redo log 日志里的数据同步到磁盘中。
有些小伙伴可能有疑惑，redo log 日志不是在磁盘嘛？那为什么不慢？其实是因为写 redo log 的过程是顺序写磁盘的。磁盘顺序写会减少寻道等待时间，速度比随机写要快很多的。
更新 SQL 只是在写内存和 redo log 日志，等到空闲的时候，才把 redo log 日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致，就出现脏页。
InnoDB 存储引擎的 redo log 大小是固定，且是环型写入的，如下图（图片来源于 MySQL 实战 45 讲）：
那什么时候会刷脏页？有几种场景：
InnoDB 用缓冲池（buffer pool）管理内存，而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
order by 就一定会导致慢查询吗？不是这样的哈，因为 order by 平时用得多，并且数据量一上来，还是走文件排序的话，很容易有慢 SQL 的。听我娓娓道来，order by 哪些时候可能会导致慢 SQL 哈。
我们平时经常需要用到 order by ，主要就是用来给某些字段排序的。
比如以下 SQL:
查看 explain 执行计划的时候，可以看到 Extra 这一列，有一个 Using filesort，它表示用到文件排序。
order by 用到文件排序时，为什么查询效率会相对低呢？
order by 排序，分为全字段排序和 rowid 排序。它是拿 max_length_for_sort_data 和结果行数据长度对比，如果结果行数据长度超过 max_length_for_sort_data 这个值，就会走 rowid 排序，相反，则走全字段排序。
rowid 排序
rowid 排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个 SQL，使用 rowid 排序，执行过程是这样：
全字段排序
同样的 SQL，如果是走全字段排序是这样的：
sort_buffer 的大小是由一个参数控制的：sort_buffer_size。
借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入 sort_buffer，当快要满时。会排一下序，然后把 sort_buffer 中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。
order by 使用文件排序，效率会低一点。我们怎么优化呢？
有时候，我们查询一条很简单的 SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。
举一个生活的例子哈，你和别人合租了一间房子，这个房子只有一个卫生间的话。假设某一时刻，你们都想去卫生间，但是对方比你早了一点点。那么此时你只能等对方出来后才能进去。
这时候，我们可以用 show processlist 命令，看看当前语句处于什么状态哈。
之前见到过一个生产慢 SQL 问题，当 delete 遇到 in 子查询时，即使有索引，也是不走索引的。而对应的 select + in 子查询，却可以走索引。
MySQL 版本是 5.7，假设当前有两张表 account 和 old_account, 表结构如下：
但是如果把 delete 换成 select，就会走索引。
如下：
为什么 select + in 子查询会走索引，delete + in 子查询却不会走索引呢？
我们执行以下 SQL 看看：
group by 是怎么使用到临时表和排序了呢？我们来看下这个 SQL 的执行流程
临时表的排序是怎样的呢？
就是把需要排序的字段，放到 sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和 rowid 排序
如果是全字段排序，需要查询返回的字段，都放入 sort buffer，根据排序字段排完，直接返回如果是 rowid 排序，只是需要排序的字段放入 sort buffer，然后多一次回表操作，再返回。
group by 使用不当，很容易就会产生慢 SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。
如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是 tmp_table_size），会把内存临时表转成磁盘临时表。如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
从哪些方向去优化呢？
方向 1：既然它默认会排序，我们不给它排是不是就行啦。方向 2：既然临时表是影响 group by 性能的 X 因素，我们是不是可以不用临时表？
我们一起来想下，执行 group by 语句为什么需要临时表呢？group by 的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦？
可以有这些优化方案：
group by 后面的字段加索引order by null 不用排序尽量只使用内存临时表使用 SQL_BIG_RESULT
如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡，你可以加个内存条什么的一个道理。如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器 CPU 占用很高或者 IO利用率很高，这种情况下所有语句的执行都有可能变慢的哈。
如果测试环境数据库的一些参数配置，和生产环境参数配置不一致的话，也容易产生慢 SQL 哈。之前见过一个慢 SQL 的生产案例，就是测试环境用了 index merge，所以查看 explain 执行计划时，是可以走索引的，但是到了生产，却全表扫描，最后排查发现是生产环境配置把 index merge 关闭了。
到此这篇关于总结12个MySQL慢查询的原因分析的文章就介绍到这了,更多相关 MySQL慢查询 内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
