慢sql优化思路及使用规范

雪飘千里 2023-10-09 原文

1、索引优化

1.1 建表或加索引时，保证表里互相不存在冗余索引。

对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

1.2 复合索引

建立索引时，多考虑建立复合索引，并把区分度最高的字段放在最前面。
比如 select * from goods where goods_no = 'aaa' and state=1;
这种情况我们只需要建了一个复合索引就可以，这就相当于创建了(goods_no ,state)、(goods_no )两个索引，这就是最佳左前缀特性。

ALTER TABLE `goods` ADD INDEX `idx_goodsno_state` (`goods_no`,`state`) USING BTREE；

1.3 使用短索引

对串列进行MySql索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

1.4 单个表上的索引个数不能超过8个

索引不是越多越好，索引也要占据空间，同时维护索引也需要消耗时间。

1.5 在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。

where条件里等号左右字段类型必须一致，否则无法利用索引

数据类型不一致会导致索引失效

1.6 不要在列上进行运算，否则导致索引失效而进行全表扫描

索引列不要使用函数或表达式，否则无法利用索引。如where length(name)='Admin'或where user_id+2=10023。

再比如我们会在create_tm添加索引，便于按照时间查询，这样情况下，就不要在列上进行格式化

 SELECT IFNULL(count(1),0) as sfmSum FROM mg_order_new m1 
where  DATE_FORMAT(m1.create_tm, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

1.7 LIKE双百分号无法使用到索引

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

1.8 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

1.9 asc和desc混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用时会导致索引失效，所以跟产品经理沟通时，尽量不要存在这种排序

1.10 不等于、不包含不能用到索引的快速搜索

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

1.11 范围查询阻断，后续字段不能走索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

范围查询还有“IN、between”

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

优化：可以(order_status, created_at)互换前后顺序

2、sql优化

2.1 读取适当的记录 limit

假如我们确定记录只有一条，那还是要习惯加上limit 1, 这样在找到一条数据后就直接返回了，不会继续扫描表；

2.2 分组统计可以禁止排序

默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序

//隐式排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg
        where mg.state = 1 group by goods_no, serial_num
                                
//添加ORDER BY NULL禁止排序               
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg  
        where  mg.state = 1 group by goods_no, point_price  ORDER BY NULL

image.png

所以在需要分组并不需要对结果进行排序的情况下，我们可以禁止隐式排序

上面的例子在MySQL 5.7及更低版本生效，GROUP BY在某些条件下隐式排序。在MySQL 8.0中，不再发生这种情况，因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序。
不过目前公司线上环境通用的还是MySQL 5.7

2.3 事务里更新语句尽量基于主键或unique key，如update … where id=XX;

否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。
具体原理见 mysql多线程update死锁问题

2.4 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
例：SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');

子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。

在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为

但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，故生产环境应避免使用子查询

2.5 Using temporary 优化

多表关联left join其他表的时候，如果以其他表的字段作为查询条件都会产生临时表Using temporary; 这会使得性能受到影响
把非直接关联的表改为直接关联，可以通过改为不作为查询条件的子查询（不要在where后面使用子查询），

//优化前，出现了文件排序和临时表问题。
 EXPLAIN  SELECT  video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
     member.nickname
from app_recommend_controller
left join video on app_recommend_controller.video_id= video.id 
left join member on member.id= video.member_id
 WHERE video.display= 1   AND video.game_id= '9930'
ORDER BY video.upload_time  desc LIMIT 0,20

 ```
 //优化后
 EXPLAIN  SELECT  video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
   (select nickname form member where id= video.id) as   nickname   #这部分代替原来的内连接查询出来的昵称
   from app_recommend_controller
   left join video on app_recommend_controller.video_id= video.id 
   WHERE video.display= 1   AND video.game_id= '9930'
    ORDER BY app_recommend_controller.video_id desc LIMIT 0,20
 ```

通过把非直接关联表member 从join查询改为不作为查询条件的子查询，来优化 Using temporary

参考：https://www.cnblogs.com/jpfss/p/9156422.html

2.6 Using filesort

在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。
优化：1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。

2.7 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢

2.8 SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。

因为union all不需要去重，节省数据库资源，提高性能。

2.9 线上环境，多表join不要超过5个表

2.10 在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表

2.11 程序端SELECT语句必须指定具体字段名称，禁止写成 *

2.12 事务里包含SQL不超过5个

因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。

2.13 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整合在一起。

因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。

2.14 不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

2.15 用IN来替换OR

低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);

2.16 大分页

select * from _t where a = 1 and b = 2 order by id desc limit 10000, 10;

对于大分页，越往后性能越差。
优化：把上一次的最后一条数据，也即上面的id传过来，然后做“id < xxx”处理

2.17 count

count(主键 id)
InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
count(1)
InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
只看这上面这两个用法，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作
count(字段)
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count()
count()是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count()肯定不是 null，按行累加。按照效率排序的话，count() = count(1) > count(主键 id) > count(字段)，所以建议尽量使用 count(*)。