有情怀，有干货，微信搜寻【三太子敖丙】关注这个不一样的程序员。
本文 GitHub https://github.com/JavaFamily 已收录，有一线大厂面试残缺考点、材料以及我的系列文章。
前言
这天我正在午休呢，公司 DBA 就把我喊醒了，说某库呈现大量慢 SQL，很快啊，很快，我还没反馈过去，库就挂了，我心想当初的用户不讲武德啊，怎么在我睡觉的时候大量申请呢。
这是很常见的一个场景哈，因为很多业务开始数据量级不大，所以写 sql 的时候就没留神性能，等量级下来，很多业务就须要做调优了，在电商公司工作的这几年我也总结了不少，上面就分享给大家吧。
在代码开发过程中，咱们都会遵循一些 SQL 开发标准去编写高质量 SQL，来进步接口的 Response Time(RT)，对一些外围接口要求 RT 在 100ms 以内甚至更低。
因为业务后期数据量比拟小，根本都能满足这个要求，但随着业务量的增长，数据量也随之减少，对应接口的 SQL 耗时也在变长，间接影响了用户的体验，这时候就须要对 SQL 进行优化。
优化点次要包含 SQL 规范性查看，表构造索引查看，SQL 优化案例剖析，上面从这三方面结合实际案例聊聊如何优化 SQL。
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:00';text 类型查看
如果 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:00';from 查看
表的链接形式
在 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-%d');order 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, 'N12121');NOT 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 ….. and ….）就进行扫描，索引正确的索引程序应该是 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, ……. 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, ……. 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, ……. from (select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**
**union all** select id, ……. from t_user_msg where **1 and invitee_id='146737'**
**union all** select id, ……. 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 规范性查看，表构造查看，索引查看以及通过 SQL 改写来优化查问，在编写代码的过程，如果能提前做这些规范性查看，评估出本人认为现实的执行打算，而后通过 explain 解析出 MySQL CBO 的执行打算，两者做比照剖析差别，弄清楚本人的抉择和 CBO 的不同，岂但可能编写高质量的 SQL，同时也能分明 CBO 的工作原理。
文章继续更新，能够微信搜一搜「三太子敖丙 」第一工夫浏览，回复【 材料】有我筹备的一线大厂面试材料和简历模板，本文 GitHub https://github.com/JavaFamily 曾经收录，有大厂面试残缺考点，欢送 Star。