SQL性能优化总结

1.避免使用select*: 只select需要的列。原因有二：①查询出数据过多，占用网络IO②select * 不走覆盖索引，出现大量回表操作，降低sql性能
2.尽量使用union all:如果查询的结果不对重复数据做要求，那么尽量使用union all代替union。union需要遍历排重和比较，降低sql性能
3.小表驱动大表：跨表查询时，先操作小表，确保小表的数据优先被查出
假设order有10000条数据，user有100条数据，使用以下sql语句效率更高：
select * from order where user_id in (select id from user where status=1)
假设order有100条数据，user有10000条数据，使用以下sql语句效率更高：
select * from order where exists （select 1 from user where order.user_id = user.id and status=1）
4.数据批量操作：适量一次处理多条数据，500以内为佳。太少频繁IO，太多速度太慢
5.in中不要太多的值：如果有in(1,2,3,4…1000)这种情况，直接在代码中做限制，尽量少在sql中处理逻辑
6.高效分页：
假设有sql select id，name, age from user limit 1000000,20，则会查询出10000020条然后丢弃前1000000条，效率低下
①如默认按id排序，且id是连续的且有序的，可以改成 select id， name， age from user where id > 1000000 limit 20
②如有唯一索引，也可以使用select id， name，age from user where id between 1000000 and 1000020 在唯一索引上分页。没有唯一索引会出现每页大小不一致的问题
7.使用连接代替子查询
如sql select * from order where user_id in (select id from user where status=1) 在执行时会创建临时表，性能降低
可以改为连接查询：select o.* from order o inner join user u on o.user_id = u.id where u.status=1
8.join表数量尽量不要超过3个，能使用inner join（已经自动是小表驱动大表）尽量不使用left join，如需使用left join要保证左边使用小表，右边使用大表
9.单表索引数量尽量控制在5个以内
10.数字不要存为字符串型，用bit存布尔值，用tinyint存枚举值，长度固定的字符串存char，金额使用decimal避免精度丢失
11.提升group by效率
如有sql select user_id, user_name, from order group by user_id having user_id <= 200，分组会降低sql性能
将sql改为 select user_id, user_name from order where user_id <= 200 group by user_id
12.索引优化：①先使用explain检查sql是否走了索引 ②如没有索引创建索引，如有索引检查索引失效的原因，具体包括：
1-不满足最左侧前缀原则
2-范围索引列没有放最后
3-使用了select *
4-索引列上由计算
5-索引上使用了函数
6-字符类型没加引号
7-用is null和is not null时没注意字段是否允许为空
8-like查询左边由%
9-使用or关键字时没有注意