【性能问题】如何解决MySQL索引失效带来的性能瓶颈这个棘手问题?

发布时间:2024年01月17日

一、背景

在上线的一个S1服务中,偶现db请求高时延,触发了sql执行超过2秒阈值告警,连续好几天都不规律触发告警,频率为每天1次。涉及的影响:如果超时,会导致新用户注册失败。所以Dev决定联合dba排查一下该现象的原因。

二、分析过程

2.1 live环境确认

dba根据告警日志信息,抓取抓取高时延SQL请求,在生产环境执行该sql,分析该SQL会全表扫描,没有预期的索引查询,耗时高达:40秒。

SELECT c1, c2, c3, c4, c5
	, c6, c7, c8, c9, c10
FROM tab_t1
WHERE c1 = 'v1'
	OR c2 = 'v2'
	OR c3 = 'v3'
	OR c4 = 'v4'
	OR c5 = 'v5'
	OR c6 = 'v6'
	OR c7 = 'v7'
	OR c8 = 'v8'
2.2 non-live环境复现

验证发现,nonlive的执行计划会走索引查询,而不是live环境的全表扫描。

2.3 nonlive&live执行计划不一致原因分析

数据量不一致:nolive环境查询出1条记录,live环境可以查询处20000+条记录,可能导致mysql优化器认为全表扫描比使用索引更高效(不是所有请求都走全表扫描可以解释live环境高时延偶现现象)

三、优化方式

or 语句改成 union 语句,强制mysql走索引查询

(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c1 = 'v1')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c2 = 'v2')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c3 = 'v3')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c4 = 'v4')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c5 = 'v5')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c6 = 'v6')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c7 = 'v7')
UNION
(SELECT c1, c2, c3, c4, c5
  , c6, c7, c8, c9, c10
FROM tab_t1
WHERE c8 = 'v8')

四、知识总结–MySQL索引失效的原因

MySQL索引失效的原因有很多,下面列举几个比较常见的原因。

1.数据量太大
如果数据库中的数据量太大,索引可能会失效。这是因为当查询条件返回的数据太多时,MySQL会选择放弃使用索引而使用全表扫描。

2.索引列数据类型不匹配
如果索引列的数据类型与查询条件的数据类型不匹配,索引也会失效。例如,如果索引列的数据类型是VARCHAR,而查询条件是INT,MySQL将无法使用该索引。

3.索引列被函数处理
如果查询条件中包含函数,那么MySQL将无法使用索引。例如,如果查询条件是“SELECT * FROM my_table WHERE YEAR(created_at) = 2022”,MySQL将无法使用索引。

4.索引列使用了函数
如果索引列中使用了函数,MySQL将无法使用该索引。例如,如果索引列是“CONCAT(first_name, ’ ', last_name)”而查询条件是“WHERE first_name = ‘Tom’”,MySQL将无法使用该索引。

5.索引列过多
如果表中有太多的索引列,索引的效率将降低。这是因为MySQL需要在多个索引中进行选择,导致查询变慢。

6.查询条件中使用了OR
如果查询条件中使用了OR,MySQL将无法使用索引。例如,“SELECT * FROM my_table WHERE age = 20 OR age = 30”,MySQL将无法使用索引。

7.索引列上存在NULL值
如果索引列上存在NULL值,MySQL将无法使用该索引。这是因为NULL值不能与其他值进行比较。

五、知识总结–如何检测MySQL索引失效

1.使用EXPLAIN
EXPLAIN命令可以帮助我们分析查询语句的执行计划,从而找出索引失效的原因。例如,我们可以运行“EXPLAIN SELECT * FROM my_table WHERE age = 20”来查看MySQL是否使用了索引。如果MySQL使用了索引,我们会在Extra列中看到“Using index”。

2.使用慢查询日志
MySQL慢查询日志可以记录执行时间超过指定阈值的SQL语句,从而帮助我们找出可能存在索引失效的查询语句。可以通过修改MySQL配置文件中的slow_query_log参数来开启慢查询日志。然后通过分析慢查询日志来确定哪些查询语句执行时间过长,并检查是否存在索引失效的情况。

3.使用监控工具
MySQL有很多监控工具可以帮助我们分析数据库的性能瓶颈。其中一些工具可以分析查询语句的执行计划,并找出可能存在索引失效的问题。例如,Percona Toolkit和pt-query-digest可以帮助我们分析查询语句的执行计划,并找出可能存在索引失效的问题。

六、知识总结–如何修复MySQL索引失效

1.优化查询语句
如果查询语句不够优化,MySQL可能会放弃使用索引。我们可以通过重构查询语句来解决这个问题。例如,我们可以尽可能地避免使用OR,避免在索引列上使用函数,避免在查询条件中使用NULL值等等。

2.添加索引
如果某个查询语句没有使用索引,我们可以考虑为相应的列添加索引。但是要注意不要过度添加索引,因为这会影响MySQL的性能。

3.优化数据表结构
如果数据表结构不够优化,索引的效率也会受到影响。我们可以考虑优化数据表结构,例如将大型文本字段拆分为多个字段,将多个表拆分为多个表等等。

4.调整MySQL配置参数
MySQL有许多配置参数可以影响索引的效率。我们可以根据具体情况调整这些参数,例如key_buffer_size、innodb_buffer_pool_size等等。

文章来源:https://blog.csdn.net/weixin_45589713/article/details/135654658
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。