根据您提供的信息,以下是对SQL优化步骤的详细解读和案例说明:
SQL优化五大步骤
1. 通过慢查日志等定位低效SQL语句
首先,通过数据库的慢查询日志(Slow Query Log)来识别执行时间较长的SQL语句。这些语句往往是性能瓶颈。
2. Explain 分析SQL的执行计划
使用 EXPLAIN
语句来分析SQL语句的执行计划,重点关注以下字段:
type
:判断查询是否利用了索引,以及索引的使用效率。key
:显示MySQL正在使用哪个索引。rows
:MySQL认为必须检查的用于执行查询的行数。filtered
:返回结果的行数占查询结果的百分比。extra
:包含MySQL解析查询语句的详细信息,如是否使用了临时表、是否进行了排序等。
3. Show Profile 分析
开启 SHOW PROFILE = 1;
来记录SQL语句的执行时间,并使用 SHOW PROFILES;
和 SHOW PROFILE FOR QUERY #{id};
来查看和分析。
4. Trace 分析
通过设置 SET optimizer_trace=enabled=on;
和 SET optimizer_trace_max_mem_size=1000000;
,可以获取优化器如何选择执行计划的详细信息。
5. 确定问题,通用解决方案
- 优化索引:确保索引设计合理,满足查询需求。
- 优化SQL语句:修改SQL语句结构,如避免使用
IN
查询和ORDER BY
组合,改用延迟关联。 - 改用其他实现方式:如使用ES或数仓进行数据检索。
- 数据拆分处理:对数据进行垂直或水平拆分,减轻数据库压力。
SQL优化案例
案例1:最左匹配
索引:KEY
idxshopidorderno(
shopid,
orderno)
SQL语句:select * from test where orderno=''
问题:由于 order_no
在索引的右侧,当只使用 orderno
作为查询条件时,无法利用索引。
解决:确保查询条件包含索引中的左侧列 shop_id
,或调整索引列的顺序。
案例2:隐式转换
索引:KEY
idx_mobile(
mobile`)
SQL语句:select * from _user where mobile=12345678901
问题:mobile
列是字符类型,而查询条件使用了数字,导致隐式类型转换,索引失效。
解决:确保查询条件与索引列类型一致。
案例3:大分页
索引:KEY
idxab_c(
a,
b,
c`)
SQL语句:select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
问题:大分页查询导致索引失效,因为MySQL无法利用索引快速定位到所需的记录。
解决:采用延迟关联或修改查询方式,如使用 id
列进行关联查询。
案例4:IN + ORDER BY
索引:KEY
idxshopidstatuscreated(
shopid,
orderstatus,
createdat`)
SQL语句:select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
问题:IN
查询与 ORDER BY
组合导致执行计划不准确。
解决:调整索引列的顺序,或改用延迟关联。
案例5:范围查询阻断
索引:KEY
idxshopidcreatedstatus(
shopid,
createdat,
orderstatus`)
SQL语句:select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
问题:范围查询导致后续字段无法利用索引。
解决:调整查询条件或索引列的顺序。
案例6:不等于、不包含
SQL语句:select * from _order where shop_id=1 and order_status not in (1,2)
问题:使用 NOT IN
、!=
、<>
等会导致索引失效。
解决:避免使用这些运算符,或改用其他查询方式。
案例7:优化器选择不使用索引
SQL语句:select * from _order where shop_id=1 and order_status=1
问题:当查询的数据量较大时,优化器可能选择不使用索引。
解决:优化索引设计,确保索引
Views: 0