MySQL慢查询排查之 EXPLAIN

慢查询排查

// 查询mysql数据库的一些运行状态
show status;  
// 查看mysql数据库启动多长时间，myisam存储引擎长时间启动需要进行碎片整理
show status like 'uptime'; 
// 查看慢查询
show status like 'slow_queries';
// 查询慢查询时间
show variables like 'long_query_time';
// 设置慢查询时间 
set long_query_time = 0.5;

分析执行情况 EXPLAIN详解

分析查询语句的执行情况，可以分析出所查询的表的一些特征：EXPLAIN/DESCRIBE/DESC SELECT * FROM table;

每个字段说明：

id：SELECT标识符。这是SELECT的查询序列号。
select_type：表示SELECT语句的类型。它可以是以下几种取值：

SIMPLE：表示简单杳询，其中不包括连接查询和子查询；

PRIMARY：表示主查询，或者最外层的查询语句；

UNION：表示连接查询的第2个或后面的查询语句；

DEPENDENT UNION：连接查询中的第2个或后面的SELECT语句，取决于外面的查询；

UNION RESULT：连接查询的结果；

SUBQUERY：子查询中的第一个SELECT语句；

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询；

DERIVED：导出表的SELECT (FROM语句的子查询）。

table：表示查询的表。
type：表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型：

(1) system
    该表仅有一行的系统表。这是const连接类型的一个特例。
(2) const
    数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的査询优化中作为常量对待。const表查询速度很快，因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。
    在下面查询中，tb1_name可用const表：
    SELECT  *  from tb1_name WHERE primary_key=1;
    SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2
(3) eq_ref
    对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用，并且索引是UNIQUE或者PRIMARY KEY时，即可使用这种类型。
    eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。
    在下面例子中，MySQL可以使用eq_ref来处理ref_tables：
    SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn;
    SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1;
（4）ref
    对于来自前面的表的任意组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列在左子集，既索引中左边的部分列组合。ref可以用于使用=或者<=>操作符的带索引的列。
    以下的几个例子中，mysql将使用 ref 来处理ref_table：   
    select * from ref_table where key_column=expr; 
    select * from ref_table,other_table where ref_table.key_column=other_table.column; 
    select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
（5）ref_or_null
    这种连接类型类似ref，不同的是mysql会在检索的时候额外的搜索包含null值的记录。在解决子查询中经常使用该链接类型的优化。
    在以下的例子中，mysql使用ref_or_null 类型来处理 ref_table：
    select * from ref_table where key_column=expr or key_column is null;
（6）index_merge
    该链接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
（7）unique_subquery
    该类型替换了下面形式的IN子查询的ref：
    value in (select primary_key from single_table where some_expr)
（8）index_subquery
    这种连接类型类似 unique_subquery。可以替换IN子查询，不过它用于在子查询中没有唯一索引的情况下，
    例如以下形式：
    value in (select key_column from single_table where some_expr)
（9）range
    只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。ken_len包含所使用索引的最长关键元素。当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in操作符，用常量比较关键字列时，类型为range。
    下面介绍几种检索制定行的情况：
    select * from tbl_name where key_column = 10; 
    select * from tbl_name where key_column between 10 and 20; 
    select * from tbl_name where key_column in (10,20,30); 
    select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);
（10）index
     连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小。
（11）ALL
    对于前面的表的任意行组合，进行完整的表扫描。如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免ALL。

possible_keys：

possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。如果这个字段的值是NULL，就表示没有索引被用到。这种情况下，就可以检查WHERE子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引，然后再用explain 检查一下。

key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是NULL。想要让MySQL强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index或 ignore index。参考SELECT语法。

key_len

key_len 字段显示了mysql使用索引的长度。当key 字段的值为NULL时，索引的长度就是NULL。注意，key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

表示使用哪个列或常数与索引一起来查询记录。

rows

显示MySQL在表中进行查询时必须检查的行数。

Extra

本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释：

distinct
MySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。 
not exists
MySQL在查询时做一个LEFT JOIN优化时，当它在当前表中找到了和前一条记录符合LEFT JOIN条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：
select * from t1 left join t2 on t1.id=t2.id where t2.id is null;
假使 t2.id 定义为 not null。这种情况下，MySQL将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查找相同id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。
range checked for each record (index map: #)
mysql没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。
using filesort
MySQL需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。
using index
字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
using temporary
mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group by和order by子句，它以不同的方式列出了各个字段。
using where
where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的extra字段值不是using where并且表连接类型是all或index时可能表示有问题。
如果你想要让查询尽可能的快，那么就应该注意extra字段的值为using filesort和using temporary的情况。

MySQL慢sql优化方案

背景

明明只是简单的查询，却要等上10秒？数据量稍微增长，系统就频繁超时？ 慢查询是数据库性能的隐形杀手，而90%的问题可通过优化索引和SQL逻辑解决！下面通过10个真实场景的优化方案，从执行计划分析到索引设计，彻底告别慢查询！

一、未命中索引：全表扫描的灾难

问题场景

查询用户表中某手机号用户，但执行耗时2秒：

SELECT * FROM users WHERE phone = '13800138000';

分析过程

执行EXPLAIN查看执行计划：

EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

结果：type=ALL（全表扫描），rows=100000（扫描10万行）

原因：phone字段无索引，被迫扫描全表。

优化方案

为phone字段添加索引：

ALTER TABLE users ADD INDEX idx_phone(phone);

优化效果

执行计划变为type=ref（索引查找），rows=1
查询时间从2秒降至0.01秒

二、索引失效：隐式类型转换陷阱

问题场景

订单表按字符串类型的订单号查询，但索引未生效：

SELECT * FROM orders WHERE order_no = 10086; -- order_no是VARCHAR类型

分析过程

EXPLAIN结果显示type=ALL，索引idx_order_no未命中。
原因：order_no是字符串类型，但查询条件使用数字，触发隐式转换导致索引失效。

优化方案

保持字段与参数类型一致：

SELECT * FROM orders WHERE order_no = '10086';

优化效果

索引命中，查询时间从3秒降至0.02秒

三、最左前缀原则：复合索引的正确姿势

问题场景

商品表根据category_id和price查询，但查询依然慢：

SELECT * FROM products WHERE price > 100;

分析过程

已为(category_id, price)创建复合索引idx_cat_price。
原因：查询未包含category_id，不满足最左前缀原则，索引失效。

优化方案

调整查询条件或索引设计：

-- 方案1：添加category_id条件（如允许业务调整） SELECT * FROM products WHERE category_id=1 AND price > 100;

-- 方案2：单独为price创建索引 ALTER TABLE products ADD INDEX idx_price(price);

四、分页优化：避开LIMIT深分页

问题场景

分页查询第100000页数据，耗时8秒：

SELECT * FROM logs ORDER BY id LIMIT 1000000, 10;

分析过程

EXPLAIN显示type=ALL，需扫描前1000010行再丢弃。

优化方案

改用游标分页（基于ID连续递增）：

SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 10;

优化效果

扫描行数从100万降至10行，耗时从8秒降至0.005秒

五、子查询优化：改用JOIN提升效率

问题场景

查询未支付订单的用户信息，子查询耗时6秒：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'unpaid');

分析过程

子查询需全表扫描orders，生成临时表后再关联。

优化方案

改写为JOIN查询：

SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'unpaid';

优化效果

避免临时表，查询时间从6秒降至0.3秒

六、避免SELECT **：覆盖索引的魔力*

问题场景

查询用户姓名和邮箱，但查询缓慢：

SELECT name, email FROM users WHERE age > 30;

分析过程

存在索引idx_age(age)，但需回表查询name和email。

优化方案

创建覆盖索引：

ALTER TABLE users ADD INDEX idx_age_cover(age, name, email);

分析过程

存在索引idx_age(age)，但需回表查询name和email。

优化方案

创建覆盖索引：

ALTER TABLE users ADD INDEX idx_age_cover(age, name, email);

优化效果

Extra=Using index，无需回表，耗时从1.2秒降至0.05秒

七、排序优化：利用索引避免Filesort

问题场景

按注册时间倒序查询用户，耗时4秒：

SELECT * FROM users ORDER BY register_time DESC LIMIT 100;

分析过程

EXPLAIN显示Using filesort，内存或磁盘排序成本高。

优化方案

为register_time创建索引：

ALTER TABLE users ADD INDEX idx_register_time(register_time);

优化效果

排序利用索引，耗时从4秒降至0.1秒

八、大事务拆分：减少锁竞争

问题场景

批量更新10万条用户状态，导致锁等待超时：

BEGIN;

UPDATE users SET status = 'active' WHERE create_time < '2023-01-01';

COMMIT;

分析过程

单事务更新数据量过大，长期持有行锁。

优化方案

分批次提交事务：

SET autocommit=0;

WHILE (需要更新的数据) DO

UPDATE users SET status = 'active' WHERE create_time < '2023-01-01' LIMIT 1000;

COMMIT;

SLEEP(1); -- 释放锁间隙

END WHILE;

SET autocommit=1;

优化效果

锁竞争减少，更新耗时从30秒降至5秒

九、避免函数运算：索引字段的纯洁性

问题场景

查询2023年注册的用户，索引未生效：

SELECT * FROM users WHERE YEAR(register_time) = 2023;

分析过程

对索引字段register_time使用函数，导致索引失效。

优化方案

改用范围查询：

SELECT * FROM users WHERE register_time >= '2023-01-01' AND register_time < '2024-01-01';

优化效果

索引命中，查询时间从3秒降至0.1秒

十、合理使用强制索引：打破优化器误判

问题场景

统计订单金额时优化器选择错误索引：

SELECT SUM(amount) FROM orders WHERE user_id = 100;

分析过程

存在idx_user_id和idx_amount索引，优化器误选idx_amount。

优化方案

强制指定索引：

SELECT SUM(amount) FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100;

优化效果

强制使用idx_user_id，扫描行数减少，耗时从2秒降至0.2秒

总结：优化是持续的过程

慢查询优化没有标准答案，需结合执行计划分析、索引设计与业务逻辑调整。

记住三个核心原则：

能用索引的不用全表
能批量的不单条
能拆事务的不长锁

MySQL慢sql的排查与优化

MySQL慢查询排查之 EXPLAIN

慢查询排查

分析执行情况 EXPLAIN详解

MySQL慢sql优化方案

背景

一、未命中索引：全表扫描的灾难

二、索引失效：隐式类型转换陷阱

三、最左前缀原则：复合索引的正确姿势

四、分页优化：避开LIMIT深分页

五、子查询优化：改用JOIN提升效率

六、避免SELECT **：覆盖索引的魔力*

七、排序优化：利用索引避免Filesort

八、大事务拆分：减少锁竞争

九、避免函数运算：索引字段的纯洁性

十、合理使用强制索引：打破优化器误判

总结：优化是持续的过程

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MySQL慢sql的排查与优化

MySQL慢查询排查之 EXPLAIN

慢查询排查

分析执行情况 EXPLAIN详解

MySQL慢sql优化方案

背景

一、未命中索引：全表扫描的灾难

二、索引失效：隐式类型转换陷阱

三、最左前缀原则：复合索引的正确姿势

四、分页优化：避开LIMIT深分页

五、子查询优化：改用JOIN提升效率

六、避免SELECT *：覆盖索引的魔力

七、排序优化：利用索引避免Filesort

八、大事务拆分：减少锁竞争

九、避免函数运算：索引字段的纯洁性

十、合理使用强制索引：打破优化器误判

总结：优化是持续的过程

热门文章

最新文章

相关电子书

六、避免SELECT **：覆盖索引的魔力*