共计 12409 个字符，预计需要花费 32 分钟才能阅读完成。
这篇文章主要介绍“怎么进行 SQL 调优”，在日常操作中，相信很多人在怎么进行 SQL 调优问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么进行 SQL 调优”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
SQL 规范性检查
每个公司都有自己的 MySQL 开发规范，基本上大同小异，这里罗列一些比较重要的，我工作期间经常接触的给大家。
select 检查
UDF 用户自定义函数
SQL 语句的 select 后面使用了自定义函数 UDF，SQL 返回多少行，那么 UDF 函数就会被调用多少次，这是非常影响性能的。
#getOrderNo 是用户自定义一个函数用户来根据 order_sn 来获取订单编号 select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between 2020-10-01 10:00:00 and 2020-10-02 10:00:00text 类型检查
如果 select 出现 text 类型的字段，就会消耗大量的网络和 IO 带宽，由于返回的内容过大超过 max_allowed_packet 设置会导致程序报错，需要评估谨慎使用。
# 表 request_log 的中 content 是 text 类型。 select user_id, content, status, url, type from request_log where user_id = 32121;group_concat 谨慎使用
gorup_concat 是一个字符串聚合函数，会影响 SQL 的响应时间，如果返回的值过大超过了 max_allowed_packet 设置会导致程序报错。
select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between 2020-10-01 10:00:00 and 2020-10-02 10:00:00内联子查询
在 select 后面有子查询的情况称为内联子查询，SQL 返回多少行，子查询就需要执行过多少次，严重影响 SQL 性能。
select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info m where status = 1 and create_time between 2020-09-02 10:00:00 and 2020-10-01 10:00:00from 检查
表的链接方式
在 MySQL 中不建议使用 Left Join，即使 ON 过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，SQL 性能变得很差，同时要清楚 ON 和 Where 的区别。
SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where b.`status` = 1 and a.create_time between 2020-10-01 00:00:00 and 2020-10-30 00:00:00 limit 100, 0;子查询
由于 MySQL 的基于成本的优化器 CBO 对子查询的处理能力比较弱，不建议使用子查询，可以改写成 Inner Join。
select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1 and create_time between 2020-10-01 00:00:00 and 2020-10-30 00:00:00) as b on a.member_id = b.member_id;where 检查
索引列被运算
当一个字段被索引，同时出现 where 条件后面，是不能进行任何运算，会导致索引失效。
#device_no 列上有索引，由于使用了 ltrim 函数导致索引失效 select id, name , phone, address, device_no from users where ltrim(device_no) = Hfs1212121 #balance 列有索引, 由于做了运算导致索引失效 select account_no, balance from accounts where balance + 100 = 10000 and status = 1;类型转换
对于 Int 类型的字段，传 varchar 类型的值是可以走索引，MySQL 内部自动做了隐式类型转换; 相反对于 varchar 类型字段传入 Int 值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。
#user_id 是 bigint 类型，传入 varchar 值发生了隐式类型转换，可以走索引。 select id, name , phone, address, device_no from users where user_id = 23126 #card_no 是 varchar(20)，传入 int 值是无法走索引 select id, name , phone, address, device_no from users where card_no = 2312612121;列字符集
从 MySQL 5.6 开始建议所有对象字符集应该使用用 utf8mb4，包括 MySQL 实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询 Join 时字段字符集不匹配导致索引失效，同时目前只有 utf8mb4 支持 emoji 表情存储。
character_set_server = utf8mb4 #数据库实例字符集 character_set_connection = utf8mb4 #连接字符集 character_set_database = utf8mb4 #数据库字符集 character_set_results = utf8mb4 # 结果集字符集group by 检查
前缀索引
group by 后面的列有索引，索引可以消除排序带来的 CPU 开销，如果是前缀索引，是不能消除排序的。
#device_no 字段类型 varchar(200)，创建了前缀索引。 mysql alter table users add index idx_device_no(device_no(64)); mysql select device_no, count(*) from users where create_time between 2020-10-01 00:00:00 and 2020-10-30 00:00:00 group by device_no;函数运算
假设需要统计某月每天的新增用户量，参考如下 SQL 语句，虽然可以走 create_time 的索引，但是不能消除排序，可以考虑冗余一个字段 stats_date date 类型来解决这种问题。
select DATE_FORMAT(create_time, %Y-%m-%d), count(*) from users where create_time between 2020-09-01 00:00:00 and 2020-09-30 23:59:59 group by DATE_FORMAT(create_time, %Y-%m-%dorder by 检查
前缀索引
order by 后面的列有索引，索引可以消除排序带来的 CPU 开销，如果是前缀索引，是不能消除排序的。
字段顺序
排序字段顺序，asc/desc 升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的 CPU 开销。
limit 检查
limit m,n 要慎重
对于 limit m, n 分页查询，越往后面翻页即 m 越大的情况下 SQL 的耗时会越来越长，对于这种应该先取出主键 id，然后通过主键 id 跟原表进行 Join 关联查询。
表结构检查
表 列名关键字
在数据库设计建模阶段，对表名及字段名设置要合理，不能使用 MySQL 的关键字，如 desc, order, status, group 等。同时建议设置 lower_case_table_names = 1 表名不区分大小写。
表存储引擎
对于 OLTP 业务系统，建议使用 InnoDB 引擎获取更好的性能，可以通过参数 default_storage_engine 控制。
AUTO_INCREMENT 属性
建表的时候主键 id 带有 AUTO_INCREMENT 属性，而且 AUTO_INCREMENT=1，在 InnoDB 内部是通过一个系统全局变量 dict_sys.row_id 来计数，row_id 是一个 8 字节的 bigint unsigned，InnoDB 在设计时只给 row_id 保留了 6 个字节的长度，这样 row_id 取值范围就是 0 到 2^48 – 1，如果 id 的值达到了最大值，下一个值就从 0 开始继续循环递增，在代码中禁止指定主键 id 值插入。
# 新插入的 id 值会从 10001 开始，这是不对的，应该从 1 开始。 create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键 id ,......) engine = InnoDB auto_increment = 10000; # 指定了 id 值插入，后续自增就会从该值开始 +1，索引禁止指定 id 值插入。 insert into booking(id, book_sn) values(1234551121, N12121NOT NULL 属性
根据业务含义，尽量将字段都添加上 NOT NULL DEFAULT VALUE 属性，如果列值存储了大量的 NULL，会影响索引的稳定性。
DEFAULT 属性
在创建表的时候，建议每个字段尽量都有默认值，禁止 DEFAULT NULL，而是对字段类型填充响应的默认值。
COMMENT 属性
字段的备注要能明确该字段的作用，尤其是某些表示状态的字段，要显式的写出该字段所有可能的状态数值以及该数值的含义。
TEXT 类型
不建议使用 Text 数据类型，一方面由于传输大量的数据包可能会超过 max_allowed_packet 设置导致程序报错，另一方面表上的 DML 操作都会变的很慢，建议采用 es 或者对象存储 OSS 来存储和检索。
索引检查
索引属性
索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的 count(*)说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键 id 的选择率是 100%，在 MySQL 中尽量所有的 update 都使用主键 id 去更新，因为 id 是聚集索引存储着整行数据，不需要回表，性能是最高的。
mysql select count(*) from member_info; +----------+ | count(*) | +----------+ | 148416 | +----------+ 1 row in set (0.35 sec) mysql show index from member_base_info; +------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | member_info | 0 | PRIMARY | 1 | id | A | 131088 | NULL | NULL | | BTREE | | | | member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | | | | member_info | 1 | idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | | | +------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ #Table：表名 #Non_unique ：是否为 unique index，0- 是，1- 否。 #Key_name：索引名称 #Seq_in_index：索引中的顺序号，单列索引 - 都是 1；复合索引 - 根据索引列的顺序从 1 开始递增。 #Column_name：索引的列名 #Collation：排序顺序，如果没有指定 asc/desc，默认都是升序 ASC。 #Cardinality：索引基数 - 索引列唯一值的个数。 #sub_part：前缀索引的长度；例如 index (member_name(10)，长度就是 10。 #Packed：索引的组织方式，默认是 NULL。 #Null：YES: 索引列包含 Null 值；: 索引不包含 Null 值。 #Index_type：默认是 BTREE，其他的值 FULLTEXT，HASH，RTREE。 #Comment：在索引列中没有被描述的信息，例如索引被禁用。 #Index_comment：创建索引时的备注。前缀索引
对于变长字符串类型 varchar(m)，为了减少 key_len，可以考虑创建前缀索引，但是前缀索引不能消除 group by，order by 带来排序开销。如果字段的实际最大值比 m 小很多，建议缩小字段长度。
alter table member_info add index idx_member_name_part(member_name(10));复合索引顺序
有很多人喜欢在创建复合索引的时候，总以为前导列一定是唯一值多的列，例如索引 index idx_create_time_status(create_time, status)，这个索引往往是无法命中，因为扫描的 IO 次数太多，总体的 cost 的比全表扫描还大，CBO 最终的选择是走 full table scan。
MySQL 遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询 (=, , , =, between hellip;.. and hellip;.) 就停止扫描，索引正确的索引顺序应该是 index idx_status_create_time(status, create_time)。
select account_no, balance from accounts where status = 1 and create_time between 2020-09-01 00:00:00 and 2020-09-30 23:59:59时间列索引
对于默认字段 created_at(create_time)、updated_at(update_time)这种默认就应该创建索引，这一般来说是默认的规则。
SQL 优化案例
通过对慢查询的监控告警，经常发现一些 SQL 语句 where 过滤字段都有索引，但是由于 SQL 写法的问题导致索引失效，下面二个案例告诉大家如何通过 SQL 改写来查询。可以通过以下 SQL 来捞取最近 5 分钟的慢查询进行告警。
select CONCAT( # Time: , DATE_FORMAT(start_time, %y%m%d %H%i%s), \n , # User@Host: , user_host, \n , # Query_time: , TIME_TO_SEC(query_time), Lock_time: , TIME_TO_SEC(lock_time), Rows_sent: , rows_sent, Rows_examined: , rows_examined, \n , sql_text, ) FROM mysql.slow_log where start_time between current_timestamp and date_add(CURRENT_TIMESTAMP,INTERVAL -5 MINUTE);慢查询 SQL
| 2020-10-02 19:17:23 | w_mini_user[w_mini_user] @ [10.200.20.11] | 00:00:02 | 00:00:00 | 9 | 443117 | mini_user | 0 | 0 | 168387936 | select id,club_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or applicant_id=12395) order by created_time desc limit 0,10; | 1219921665 |从慢查询 slow_log 可以看到，执行时间 2s，扫描了 443117 行，只返回了 9 行，这是不合理的。
SQL 分析
#原始 SQL，频繁访问的接口，目前执行时间 2s。 select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10; # 执行计划 +----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | t_user_msg | index | invite_id,app_id,team_id | created_time | 5 | NULL | 10 | Using where | +----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec)从执行计划可以看到，表上有单列索引 invite_id,app_id,team_id,created_time，走的是 create_time 的索引，而且 type=index 索引全扫描，因为 create_time 没有出现在 where 条件后，只出现在 order by 后面，只能是 type=index，这也预示着表数据量越大该 SQL 越慢，我们期望是走三个单列索引 invite_id，app_id，team_id，然后 type=index_merge 操作。
按照常规思路，对于 OR 条件拆分两部分，分别进行分析。
select id, hellip; hellip;. from t_user_msg where 1 and **(team_id in (3212) and app_id is not null)** order by created_time desc limit 0,10;从执行计划看走的是 team_id 的索引，没有问题。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | t_user_msg | ref | app_id,team_id | team_id | 8 | const | 30 | Using where; Using filesort |再看另外一个 sql 语句：
select id, hellip; hellip;. from t_user_msg where 1 and **(invite_id=12395 or app_id=12395)** order by created_time desc limit 0,10;从执行计划上看，分别走的是 invite_id,app_id 的单列索引，同时做了 index_merge 合并操作，也没有问题。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+ | 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9 | NULL | 2 | Using union(invite_id,app_id); Using where; Using filesort |通过上面的分析，第一部分 SQL 走的执行计划走 team_id 索引没问题，第二部分 SQL 分别走 invite_id,app_id 索引并且 index_merge 也没问题，为什么两部分 SQL 进行 OR 关联之后走 create_time 的单列索引呢，不应该是三个单列索引的 index_merge 吗?
index_merge 默认是在优化器选项是开启的，主要是将多个范围扫描的结果集合并成一个，可以通过变量查看。
mysql select @@optimizer_switch; | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,其他三个字段都传入的是具体的值，而且都走了相应的索引，只能怀疑 app_id is not null 这个条件影响了 CBO 对最终执行计划的选择，去掉这个条件来看执行计划，竟然走了三个单列索引且 type=index_merge，那下面只要搞定 app_id is not null 这个条件就 OK 了吧。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+ | 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9 | NULL | 32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |SQL 改写
通过上面分析得知，条件 app_id is not null 影响了 CBO 的选择，下面进行改造。
改写优化 1
根据 SQL 开发规范改写，将 OR 改写成 Union All 方式即可，最终的 SQL 如下：
select id, hellip; hellip;. from ( select id, hellip; hellip;. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)** **union all** select id, hellip; hellip;. from t_user_msg where **1 and invitee_id= 146737 ** **union all** select id, hellip; hellip;. from t_user_msg where **1 and app_id= 146737 ** ) as a order by created_time desc limit 0,10;一般情况下，Java 代码和 SQL 是分开的，SQL 是配置在 xml 文件中，根据业务需求，除了 team_id 是必填，其他两个都是可选的，所以这种改写虽然能提高 SQL 执行效率，但不适合这种业务场景。
改写优化 2
app_id is not null 改写为 IFNULL(app_id, 0) 0)，最终的 SQL 为：
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;改写优化 3
将字段 app_id bigint(20) DEFAULT NULL，变更为 app_id bigint(20) NOT NULL DEFAULT 0，同时更新将 app_id is null 的时候全部更新成 0，就可以将条件 app_id is not null 转换为 app_id 0，最终的 SQL 为：
select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;从执行计划看，两种改写优化方式都走三个单列索引，执行时间从 2s 降低至 10ms，线上采用的是优化 1 的方式，如果一开始能遵循 MySQL 开发规范就就会避免问题的发生。
到此，关于“怎么进行 SQL 调优”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
