有情怀，有干货，微信搜寻【三太子敖丙】关注这个不一样的程序员。本文 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` = 1and 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` = 1and 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。