这天我正在午休呢，公司DBA就把我喊醒了，说某库出现大量慢SQL，很快啊，很快，我还没反应过来，库就挂了，我心想现在的用户不讲武德啊，怎么在我睡觉的时候大量请求呢。
这是很常见的一个场景哈，因为很多业务开始数据量级不大，所以写sql的时候就没注意性能，等量级上去，很多业务就需要做调优了，在电商公司工作的这几年我也总结了不少，下面就分享给大家吧。
在代码开发过程中，我们都会遵循一些SQL开发规范去编写高质量SQL，来提高接口的Response Time(RT)，对一些核心接口要求RT在100ms以内甚至更低。
由于业务前期数据量比较小，基本都能满足这个要求，但随着业务量的增长，数据量也随之增加，对应接口的SQL耗时也在变长，直接影响了用户的体验，这时候就需要对SQL进行优化。
优化点主要包括SQL规范性检查，表结构索引检查，SQL优化案例分析，下面从这三方面结合实际案例聊聊如何优化SQL。
SQL规范性检查
每个公司都有自己的MySQL开发规范，基本上大同小异，这里罗列一些比较重要的，我工作期间经常接触的给大家。
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';
表的链接方式
在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条件后面，是不能进行任何运算，会导致索引失效。
#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后面的列有索引，索引可以消除排序带来的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后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。
字段顺序
排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。
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控制。
建表的时候主键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;
<h1 id="指定了id值插入后续自增就会从该值开始1索引禁止指定id值插入">指定了id值插入，后续自增就会从该值开始+1，索引禁止指定id值插入。</h1>
insert into booking(id, book_sn) values(1234551121, 'N12121');
根据业务含义，尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。
在创建表的时候，建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充响应的默认值。
字段的备注要能明确该字段的作用，尤其是某些表示状态的字段，要显式的写出该字段所有可能的状态数值以及该数值的含义。
不建议使用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)
<p>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 | | |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+</p>
<h1 id="table-表名">Table： 表名</h1>
<h1 id="non_unique-是否为unique-index0-是1-否">Non_unique ：是否为unique index，0-是，1-否。</h1>
<h1 id="key_name索引名称">Key_name：索引名称</h1>
<h1 id="seq_in_index索引中的顺序号单列索引-都是1复合索引-根据索引列的顺序从1开始递增">Seq_in_index：索引中的顺序号，单列索引-都是1；复合索引-根据索引列的顺序从1开始递增。</h1>
<h1 id="column_name索引的列名">Column_name：索引的列名</h1>
<h1 id="collation排序顺序如果没有指定ascdesc默认都是升序asc">Collation：排序顺序，如果没有指定asc/desc，默认都是升序ASC。</h1>
<h1 id="cardinality索引基数-索引列唯一值的个数">Cardinality：索引基数-索引列唯一值的个数。</h1>
<h1 id="sub_part前缀索引的长度例如index-member_name10长度就是10">sub_part：前缀索引的长度；例如index (member_name(10)，长度就是10。</h1>
<h1 id="packed索引的组织方式默认是null">Packed：索引的组织方式，默认是NULL。</h1>
<h1 id="nullyes索引列包含null值索引不包含null值">Null：YES:索引列包含Null值；'':索引不包含Null值。</h1>
<h1 id="index_type默认是btree其他的值fulltexthashrtree">Index_type：默认是BTREE，其他的值FULLTEXT，HASH，RTREE。</h1>
对于变长字符串类型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遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询（>=, >,
Original: https://www.cnblogs.com/aobing/p/13983556.html
Author: 敖丙
Title: 大厂是怎么进行SQL调优的？
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/592403/
转载文章受原作者版权保护。转载请注明原作者出处！