12个Mysql慢查询原因整理

本文最后更新于:10 天前

12个Mysql慢查询原因整理


学会用 explain 分析
explain select * from user_info where name =’xxx’ ;
平时我们用 explain 分析 SQL 的时候,如果 type=range, 要注意一下,可能因为数据量问题,导致索引无效

1、SQL没加索引

where 的条件列,建立索引,尽量避免全表扫描

2、SQL索引不生效

2.1 隐式的类型转换,索引失效
查询语句不加单引号时,是字符串数字的比较,它们类型不匹配,MySQL 会做隐式的类型转换,把它们转换为浮点数再做比较

2.2 查询条件包含 or,可能导致索引失效
遇到不走索引的时候,考虑拆开两条 SQL

2.3 like 通配符可能导致索引失效
并不是用了 like 通配符,索引一定会失效,而是 like 查询是以 % 开头,才会导致索引失效。
优化:

  • 使用覆盖索引
  • % 放后面

2.4 查询条件不满足联合索引的最左匹配原则
查询条件列 name 不是联合索引 idx_userid_name 中的第一个列,索引不生效
在联合索引中,查询条件满足最左匹配原则时,索引才正常生效。

2.5 在索引列上使用 mysql 的内置函数

优化
可以把内置函数的逻辑转移到右边

2.6 对索引进行列运算(如,+、-、*、/), 索引不生效
不可以对索引列进行运算,可以在代码处理好,再传参进去。

2.7 索引字段上使用(!= 或者 < >),索引可能失效

2.8 索引字段上使用 is null, is not null,索引可能失效
单个not null 生效 多个not nullor连接会失效

2.9 左右连接,关联的字段编码格式不一样
做表关联时,注意一下关联字段的编码问题

2.10 优化器选错了索引

3、limit 深分页问题

原因:

  • limit 语句会先扫描 offset+n 行,然后再丢弃掉前 offset 行,返回后 n 行数据。也就是说 limit 100000,10,就会扫描 100010 行,而 limit 0,10,只扫描 10 行。
  • limit 100000,10 扫描更多的行数,也意味着回表更多的次数。

优化:
可以通过减少回表次数来优化。一般有标签记录法延迟关联法

4、单表数据量太大

原因
因为维护索引的 B+ 树结构层级变得更高了,查询一条数据时,需要经历的磁盘 IO 变多,因此查询性能变慢

分析
InnoDB 存储引擎最小储存单元是页,一页大小就是 16k

  • 如果一行记录的数据大小为 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 层,已经满足千万级别的数据存储

优化
一般超过千万级别,我们可以考虑分库分表了。

分库分表可能导致的问题:

  • 事务问题
  • 跨库问题
  • 排序问题
  • 分页问题
  • 分布式 ID
  • 方案:
    1、历史数据归档
    2、考虑垂直、水平分库分表。水平分库分表策略的话,range 范围hash 取模range+hash 取模混合等等

5、 join 或者子查询过多

业务需要的话,关联 2~3 个表是可以接受的,但是关联的字段需要加索引哈。如果需要关联更多的表,建议从代码层面进行拆分,
在业务层先查询一张表的数据,然后以关联字段作为条件查询关联表形成 map,然后在业务层进行数据的拼装

6、in 元素过多

in 元素一般建议不要超过 500 个,如果超过了,建议分组,每次 500 一组进行
分批进行,每批 500 个

1
select user_id,name from user where user_id in (1,2,3...500);

如果传参的 ids 太多,还可以做个参数校验

7、数据库在刷脏页

当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为 “脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为 “干净页”。一般有更新 SQL 才可能会导致脏页
深入了解分析

8、order by 文件排序

看一遍就理解:order by 详解

9、 拿不到锁

表被锁住了
可以用 show processlist 命令,看看当前语句处于什么状态

10、delete + in 子查询不走索引!

MySQLselect in 子查询做了优化,把子查询改成 join 的方式,所以可以走索引,对于 delete in 子查询,MySQL 却没有对它做这个优化

11、group by 使用临时表

group by 一般用于分组统计,它表达的逻辑就是根据一定的规则进行分组。日常开发中,我们使用得比较频繁。如果不注意,很容易产生慢 SQL

优化

  • group by 后面的字段加索引
  • order by null 不用排序
  • 尽量只使用内存临时表
  • 使用 SQL_BIG_RESULT

参考:[看一遍就理解:group by 详解](看一遍就理解:group by 详解)

12、系统硬件或网络资源

  • 如果数据库服务器内存、硬件资源,或者网络资源配置不是很好,这时候可以升级配置。
  • 如果数据库压力本身很大,比如高并发场景下,大量请求到数据库来,数据库服务器 CPU 占用很高或者 IO利用率很高,这种情况下所有语句的执行都有可能变慢

13、其他

如果测试环境数据库的一些参数配置,和生产环境参数配置不一致的话,也容易产生慢 SQL 哈。之前见过一个慢 SQL 的生产案例,就是测试环境用了 index merge,所以查看 explain 执行计划时,是可以走索引的,但是到了生产,却全表扫描,最后排查发现是生产环境配置把 index merge 关闭

转自:


12个Mysql慢查询原因整理
https://calmchen.com/posts/71efa875.html
作者
Calm
发布于
2022年8月30日
更新于
2022年8月30日
许可协议