SQL优化与分析
SQL优化与分析
前⾔：要做好 SQL 优化，我们先聊⼀聊在编写 SQL 时要经常⽤到的⼀些关键词，看看这些关键词的⽤途，以及应该如何使⽤；然后讨论下，⼀条 SQL 是如何被执⾏的，再根据 SQL 的执⾏规范说⼀说应该怎么写出⾼效的 SQL，最后拿出⼀个栗⼦，来分析下应该如何对问题 SQL 进⾏优化。
⼀、先说⼀说什么是 SQL
我们先看⼀看百度百科中关于 SQL 的描述
结构化查询语⾔ (Structured Query Language) ，简称 SQL，是⼀种特殊⽬的的编程语⾔，是⼀种数据库查询和程序设计语⾔，⽤于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本⽂件的扩展名。
标准的 SQL 包含 6 个组成部分，分别是：
数据查询语⾔（DQL）：⽤来检索数据，是最常⽤的 SQL，基本关键词（select）
数据操作语⾔（DML）：⽤来修改数据，如插⼊、更新、删除，基本关键词（insert，update，delete）
事务处理语⾔（TPL）：⽤来确保事务操作正常有效，基本关键词（commit，rollback）
数据控制语⾔（DCL）：⽤来进⾏数据权限控制，⼀般⽤来分配⽤户、⾓⾊以及数据可访问范围，基本关键词（grant，deny）
数据定义语⾔（DDL）：⽤来定义数据格式、关系，基本关键词（create table）
指针控制语⾔（CCL）：⽤来控制数据游标在不同数据⾏之间跳转，基本关键词（cursor）
  我们常说的优化，⼤部分所指的就是对使⽤数据查询语⾔编写的 SQL 在执⾏效率⽅⾯进⾏优化。
  当然，SQL 优化也不单单只有 DQL 优化，有时，为了满⾜效率预期，我们也需要对其他⽅⾯进⾏调整。
⼆、名词解释
  在讲解如何优化 SQL 之前，我们先介绍⼏个关键字，这些关键字⼤家在⽇常的开发中肯定经常⽤到，但具体是做什么⽤的恐怕就不那么清楚了，所以，让我们先从这
些“最熟悉的陌⽣⼈”开始，对我们的⼯作内容做⼀个深度了解。
1. select：这个是 DQL 最常见的关键字，我们基本上⽤在了所有的查询开始的地⽅，在 DQL 语句中，select 表⽰⼀个查询的开始或者⼀个⼦查询的开始，紧跟在 select后
⾯的就是我们要从数据库中提取后返回给调⽤⽅的数据项，当 select后⾯跟随⼀个 *时，表⽰我们要将所有查询到的数据项都返回；
2. from：99.999% 的 DQL 中都会包含 from关键字，它⽤来标识我们要查询的数据的来源，from后⾯可以是数据表（table），视图（view）甚⾄是另⼀个 DQL （⼦查
询），from后⾯可以跟随⼀个或多个数据来源，使⽤ “,” （西⽂半⾓逗号）进⾏分割；
在某些数据库中，如 MySQL，在调⽤⼀个函数或直接返回⼀个⾃定义常量时可以不写 from 关键字，如我们配置在数据库连接池中，为连接对象验活的语句 select 1，或者直接调⽤⼀个函数，如查询当前数据库服务器时间 select now()，但不是所有的数据库都⽀持省略 from 关键字，如 ORACLE，所以为了保证数据库兼容，我们要求不能省略 from关键字，像上⾯两个语句，要改写为 select 1 from dual和 select now() from dual，dual 是数据库中提供的⼀个只有⼀列的系统表，⼀般我们称之为伪表或虚拟表。
3. inner join：⽤来做两个数据来源的关联使⽤，使⽤ on 关键字来进⾏数据关联，⼀般⽤在多表（视图）查询上，⼀般 inner join 也可以写为逗号分隔的⽅式，然后将 on
条件转义到 where 中；使⽤ inner join 只有当数据来源存在时，才会返回数据；
4. join：join 就是对 inner join 的简写
5. left join：左外连接，也可简称左连接，当使⽤ left join 进⾏关联时，处于 left join 后⽅的数据来源如果不存在满⾜条件的数据记录，但其他数据来源存在相关记录，则
返回除此来源外的其他数据项，此来源数据项返回 null
6. right join：右外连接，也可简称右连接，数据返回情况如 left join 相似，但区别是只要 right join 后⽅的数据来源存在符合条件的记录即返回数据；
7. full join：可以看做是 left join ∪ right join，也就是只要有满⾜条件的数据，就会返回，这个⽤的⽐较少；
8. where：条件起始关键字，跟随在 where 关键字后的就是数据查询条件，我看⼤家在写 SQL 经常会在 where 后⾯跟⼀个 1=1，貌似是⽅便了查询条件的拼写，但是，
这个在某些数据库或者引擎下是会影响执⾏效率的，应该尽量避免；
9. in：⼀般⽤在⼀个数据项符合多个条件时，但是，in 操作很慢，如果可能的话，还是写 exists ⼦查询⽐较好，还有，如果要使⽤的 in 条件选项很多的情况，jdbc 可能是
⽆法执⾏的，如 1000 个以上，这时就要考虑其他⽅法了；
10. like：做模糊查询时使⽤，使⽤左右 “%” 来进⾏匹配，如果使⽤左侧 % ，查询的时候⽤不上索引；
11. group by：数据项分组，⼀般配合聚合函数，如 sum avg 等使⽤，如果需要对分组结果在进⾏筛选的话，需要配合 having 关键字；
12. order by：结果排序；
四、优化策略
  不同的数据库甚⾄同⼀个数据库不同的数据引擎在 SQL 处理上都会存在差异，⽽咱们最常⽤的数据库是 MySQL，所以，咱们的优化策略主要针对的就是 MySQL，其他类型的数据库优化⽅案⼤家可以 Google 或者百度。
  要想做好 SQL 优化，我们需要先明⽩ MySQL 在执⾏⼀个 SQL 时的顺序，⼀条 SQL 是由多个完成不同任务的关键字组成的，⽽⼀个最完整的 SQL ⼤概是下⾯这样：
select
< select_list >
from
< left_table >< join_type >
join
< right_table >ON< join_condition >
where
< where_condition >
group by
< group_by_list >
having
< having_condition >
order by
< order_by_condition >
limit< limit_number >
  咱们跳过最底层的 SQL 检查和解析部分，直接看最终的执⾏顺序
-- 上⾯那个 SQL 在 MySQL 引擎中的执⾏顺序是这样滴
from
<left_table>
on
<join_condition><join_type>
join
<right_table>
where
<where_condition>
group by
<group_by_list>
having
<having_condition>
select
<select_list>
order by
<order_by_condition>
limit
<limit_number>
  很诧异是不是，和咱们写的 SQL 顺序发⽣了很⼤的变化，但却很符合查询的数据处理逻辑，先需要知道在什么地⽅获取数据，然后根据 on 条件进⾏数据关联，再通过where 条件筛选掉不符合条件的数据，接着进⾏分组、筛选分组结果，然后根据要求在筛选出需要返回的数据项，最后根据取数限定，返回⼀定量的数据。
  从 SQL 的执⾏顺序咱们可以看出，这就是⼀个在逐渐筛选数据的过程，⼀步⼀步的减少最终返回的数据量，⽽这也正是我们优化 SQL 的⽬标，让能⼤量减少数据量的筛选条件先执⾏，然后逐步细化调整；
  常⽤的 SQL 优化⽅法如下：
1. 减少⼦查询的使⽤，尽量使⽤ join 的⽅式连接数据源，当⼀个查询中存在⼦查询的时候，数据库引擎会先将⼦查询提取出来执⾏，然后将结果在使⽤虚拟表或临时表的⽅
式嵌⼊到原始 SQL 中，这将严重影响执⾏效率；
2. 如⾮必要，所有的数据表关联都选择 (inner) join 的⽅式，不要使⽤ left join 或 right join；
3. 表关联条件（on）尽量使⽤索引列，索引将会显著提升查询效率，但索引也不是万能的，维护索引需要占⽤⼤量的存储空间，⽽且在插⼊或更新数据值会同时更新索引，
会影响写数据的速度；
4. 索引尽量创建在会对外进⾏关联的数据列上，如在销售模块中，我们有订单表，订单明细表，客户信息表，其中订单表中有客户 ID，订单明细表中有订单 ID，那么订单表
中的客户 ID 和订单明细表中的订单 ID 就应该创建索引；
5. 创建外键，这是⼀把双刃剑，有外键关系的表在进⾏关联时相对于索引还有⼀定的提升，但使⽤了外键在对数据进⾏增删改的时候有⼀定的顺序影响，因此我不建议⼤家使
⽤外键。
6. 条件类型，在执⾏查询时，作为变量的条件要尽可能的与要⽐对的数据列类型⼀致，减少数据库引擎转换数据类型的时间；
7. 如果要参与⽐对的条件需要⽤到函数，则尽量避免在数据列上使⽤，这样会造成索引失效，⽽形成全表扫描，增加数据量；
8. 如果查询条件中对同⼀个数据列有多个选项，不要使⽤ or 进⾏条件关联，⽽是使⽤ in；
9. 如果查询条件中是⼀个变量可能符合多个数据列，可以将 in 反过来写，如，订单表中，想要查 未交费 或 未开票 或 未发货 的数据，则条件可以写为 where 'N' in
(pay_status, invoice_status, send_status)；
10. 如果查询条件包含较多的 or，可以使⽤ union all 来分解为多个 and 条件的 SQL，避免因为使⽤ or 关系造成索引失效；
11. 避免不必要的分组和排序；
12. 如⾮必要，减少 like 的使⽤，like ⽐较在⼤部分情况下⽆法利⽤到索引；
13. 从我了解到的资料看，MySQL 在解析 from 中的表和 Where 中的条件时基本都是采⽤⾃左向右的⽅式进⾏处理，因此我们在写跨表查询和多条件查询时，要尽量将可以
⼤量过滤数据的条件写在前⾯，将可以利⽤索引的条件写在前⾯；
14. 在使⽤条件时，尽量减少 not、<> 这样的⽐较⽅式；
15. 能使⽤ between 时，尽量别拆成 in 条件；
16. 能使⽤去重⽅式获取数据，就不要使⽤ group by 进⾏分组；
17. 合理利⽤ limit，减少⼀次性传输⼤量数据的可能；
18. 合理利⽤游标技术，减少反复查询；
五、举个栗⼦☺
  下⾯，我出在 2019年1⽉ 造成 ⼤规模线上问题 的 SQL 来具体说明优化步骤。
-- 这个就是造成数据库 CPU 超负荷以⾄于停⽌对外服务的罪魁祸⾸
select
ss.djxh djxh,
ci.id cid,
ci.e_id eid,
ss.id sbid,
ci.shxydm shxydm,
ci.customer_name customer_name,
db.bblx_mc zsxm_mc,
case ss.tb_bz when'Y'then'已同步'else(case ss.sbzt when'Y'then'已申报'else'未申报'end)end as sbzt,
ss.sbrq sbrq,
ss.ybtse ybtse,
case ss.jkzt when'Y'then'已缴款'else'未缴款'end as jkzt,
ss.jkrq jkrq,
ss.sssq_q sssq_q,
ss.sssq_z sssq_z,
ss.zsxm_dm zsxm_dm,
ss.bblx_dm bblx_dm,
ss.swjgmc swjgmc,
ss.swjgjc swjgjc,
ss.nsrsbq nsrsbq
from agency_tax.sb_sbxx ss
left join`customer_info` ci on ci.id = ss.c_id
left join agency_tax.dm_bblx db on db.bblx_dm = ss.bblx_dm
left join employee_info ei on ei.id = ci.service_tax_id
left join(
select b.num, sbxx.sb_id, sbxx.bb_dm
from agency_tax.sb_sbxx_bbmx sbxx
left join(
select count(*) num, bbmx.sb_id sbid
from agency_tax.sb_sbxx_bbmx bbmx
where bbmx.state ='Y'
group by bbmx.sb_id
) b on b.sbid = sbxx.sb_id
where sbxx.state ='Y'
group by sbxx.id
) a on ss.id = a.sb_id
where ss.state ='Y'
and a.num >0
and ci.customer_name like'%柏%'
and( ss.sbzt ='Y'or ss.bblx_dm ='10411'or ss.tb_bz ='Y')
and ci.service_tax_id =696
group by ss.id
order by ss.sssq_z desc, ss.c_id, ss.id
limit15
  这个 SQL 的业务是查询符合条件税种申报、缴款情况，并返给⽤户查看，在发⽣问题的时刻，这个 SQL 涉及的数据表⾏数如下表：
序号数据表名称数据⾏数
1sb_sbxx254389
2customer_info53801
3dm_bblx83
4employee_info2630
5sb_sbxx_bbmx179635
  我们可以看到其中数据量最多的 sb_sbxx 表，不过有 25 万⾏记录，但在发⽣问题的时刻，这条 SQL 语句执⾏耗时却达到了惊⼈的 200s，以⾄于造成数据库服务器
CPU 占⽤率过⾼（95%），进⽽引发数据库服务对外停⽌服务，造成⼤⾯积线上事故。
  让我们使⽤ EXPLAIN 关键字来查询这条 SQL 的执⾏计划，如果使⽤ Navicat ⼯具，可以在查询窗⼝上点 “解释” 按钮，来获取执⾏计划：
id select_type table type possible_keys
sql优化的几种方式
1PRIMARY ALL
1PRIMARY ss eq_ref PRIMARY,IDX_SB_SBXX_CID,IDX_SB_SBXX_CID_ZSXM,IDX_SB_SBXX_C
ID_ZSXM_SQ,IDX_SB_SBXX_CID_ZSXM_BBLX_SQ,IDX_SB_SBXX_SQ_STATE
1PRIMARY db eq_ref PRIMARY
1PRIMARY ci eq_ref PRIMARY
1PRIMARY ei const PRIMARY
2DERIVED sbxx index PRIMARY,IDX_SB_SBXX_BBMX_SBID,IDX_SB_SBXX_SBID_ZSXM,IDX_SB_SBXX_SBID_ZSXM_SQ,IDX_SB_SBXX_SBID_ZSXM_BBLX_SQ,IDX_SB_SBXX_SBID_ZSXM_BBLX 2DERIVED ref<auto_key0>
3DERIVED bbmx index IDX_SB_SBXX_BBMX_SBID,IDX_SB_SBXX_SBID_ZSXM,IDX_SB_SBXX_SBID_ZSXM_SQ,IDX_SB_SBXX_SBID_ZSXM_BBLX_SQ,IDX_SB_SBXX_SBID_ZSXM_BBLX_BB
  我们主要关注执⾏计划中的 rows 这⼀列，这⼀列表⽰执⾏这条 SQL 需要扫描的数据⾏数，可以看到，其中对 sbxx 和 bbmx 这两数据来源都进⾏ 16 万⾏的数据扫描；
  同时根据执⾏计划我们也可以看出，这条查询竟然包含了两段⼦查询；
  我们现在可以做⼀个初步的判断，这条 SQL 存在全表或⼤量数据扫描问题，这是我们要解决的第⼀个问题，出现全表扫描的原因可能是由于缺少索引或缺少有效的查询条件；
  我们反过来看 SQL，可以看到其中的⼦查询是
select b.num, sbxx.sb_id, sbxx.bb_dm
from agency_tax.sb_sbxx_bbmx sbxx
left join(
select count(*) num, bbmx.sb_id sbid
from agency_tax.sb_sbxx_bbmx bbmx
where bbmx.state ='y'
group by bbmx.sb_id
) b on b.sbid = sbxx.sb_id
where sbxx.state ='Y'
group by sbxx.id
  进⼀步分析发现，这个⼦查询竟然⼜嵌套了⼀个⼦查询，更令⼈费解的是，这个查询竟然是对同⼀张表，通过对 SQL 分析，这个查询的⽬的应该是提取符合条件的记录总数，然后对外提供查询。
  当然，我们在没有分析业务的前提下，也不能贸然调整 SQL，所以需要进⼀步分析⼦查询数据的⽤途，从 SQL 中，我们可以看到⼦查询返回了 num、sb_id 和 bb_dm 三个字段，其中 sb_id 作为⼦查询与其他数据表的关联条件，num 在查询条件中出现了⼀次，⽽ bb_dm 从头⾄尾就没有被使⽤过，为了避免 SQL 是动态⽣成的，缺少使⽤条件，我们⼜进⼀步检查业务逻辑，发现仍然没有使⽤这个条件的位置，因此可以认为此节点⽆意义，可以忽略；
  再进⼀步分析，发现 SQL 中使⽤的全部都是 left join，结合我们前⾯说的内容，left join 会在右侧数据表不存在符合条件的数据时，检索左侧表的内容，⽽从业务⾓度分析，在这个查询中，左侧表 sb_sbxx 并不会出现在不存在于其他报表的数据，因此可以使⽤ inner join 替换掉现有的 left join
  然后，再分析查询条件，在查询条件中存在使⽤⼦查询结果的情况，同时存在 like 条件和 or 条件，根据优化策略，我们对查询条件的顺序进⾏调整，将可以⼤量过滤数据的条件前移，将 like 条件后置，同时将涉及⼦查询的条件先拆分出来放置到分组条件中，对 SQL 进⾏优化，得到以下优化结果
select
distinct
ss.djxh djxh,
ci.id cid,
ci.e_id eid,
ss.id sbid,
ci.shxydm shxydm,
ci.customer_name customer_name,
db.bblx_mc zsxm_mc,
case ss.tb_bz when'Y'then'已同步'else(case ss.sbzt when'Y'then'已申报'else'未申报'end)end as sbzt,
ss.sbrq sbrq,
ss.ybtse ybtse,
case ss.jkzt when'Y'then'已缴款'else'未缴款'end as jkzt,
ss.jkrq jkrq,
ss.sssq_q sssq_q,
ss.sssq_z sssq_z,
ss.zsxm_dm zsxm_dm,
ss.bblx_dm bblx_dm,
ss.swjgmc swjgmc,
ss.swjgjc swjgjc,
ss.nsrsbq nsrsbq,
count(distinct bbmx.id) num
from agency_tax.sb_sbxx ss
join`customer_info` ci on ci.id = ss.c_id
join agency_tax.dm_bblx db on db.bblx_dm = ss.bblx_dm
join employee_info ei on ei.id = ci.service_tax_id
join agency_tax.sb_sbxx_bbmx bbmx on bbmx.sb_id = ss.id
where
ci.service_tax_id =696
and ss.state ='Y'
and( ss.sbzt ='Y'or ss.bblx_dm ='10411'or ss.tb_bz ='Y')
and ci.customer_name like'%柏%'
group by ss.id
having count(distinct bbmx.id)>0
order by ss.sssq_z desc, ss.c_id, ss.id
limit15
  对优化前和优化后的 SQL 分别执⾏，时间分别为 2s 和 300ms（多次平均），基本可以认为 SQL 完成了优化；
  我们再次执⾏查询计划，发现仍然存在⼀个全表扫描，customer_info 表；
  纵观 SQL，我们可以看到 customer_info 除了提供结果数据外，还提供了⼀个筛选条件 service_tax_id，⽽通过分析表结构发现，service_tax_id 列缺少索引，并且该列可以过滤⼤量数据，因此可以考虑增加索引；
  增加索引后，再次执⾏优化后的 SQL，可以看到执⾏时间已经降低到 15ms（平均多次），⾄此，我们可以认为，本次 SQL 优化完成。
五、总结
  纵观问题 SQL，我们可以发现这条 SQL 基本上把所有编写 SQL 时应该避免的问题都⽤上了：
⽆意义的⼦查询
不正确的 join ⽅法
关键条件（字段）缺少索引
不必要的分组
查询条件顺序随意
过多的 or 条件（业务原因，因为影响不⼤，没有调整）
包含模糊查询（业务问题，但可以将模糊查询放到最后）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。