SQL优化复习

SQL优化

为什么SQL优化这么重要？

答：SQL优化是提高系统性能性价比最高的一种方式

• 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
• 优化效果：硬件<系统配置<数据库表结构

1、Mysql层优化五个原则

五个原则

• 减少数据库访问
• 返回更少数据
• 减少交互次数
• 减少服务器CPU开销
• 利用更多资源

总结

• 尽可能利用索引
• 避免全表扫描
• 减少无效查询

2、SQL执行顺序

语法顺序

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>
1
2
3
4
5
6
7
8
9
10

从from表名

根据条件on，连接其他表

根据联表（虚表）的结果where

对虚表的筛选结果进行分组group by

再使用having关键字对分组结果进行筛选

select

distinct

order by

limit

3、优化策略（不走索引）

3.1、避免like不走索引

模糊查询会导致数据库引擎放弃索引进行全表扫描

原SQL

select * from user where username like '%李%'
1

优化方式：尽量在字段后面使用模糊查询

select * from user where username like '李%'
1

3.2、避免使用in和not in

使用该关键字会导致引擎走全表扫描

原SQL

select * from user where id in (2,3)
1

优化方式：使用between或者exists关键字代替

3.3、避免使用or

使用or关键字会导致数据库引擎放弃索引进行全表扫描

原SQL

select * from user where id=1 or id=3
1

优化方式：使用union代替or

select * from user where id=1
union
select * from user where id-3
1
2
3

3.4、避免null值判断

进行null值判断，会导致数据库引擎放弃索引进行全表扫描

原SQL

select * from user where score = null
1

优化方式：设计数据表时给字符设置默认值，例如0，对0值进行判断

3.5、避免在where关键字等号的左边进行表达式、函数操作等

会导致数据库引擎放弃索引进行全表扫描

原SQL

select * from user where score/10 = 9
1

优化

select * from user where score = 10*9
1

3.6、条件查询不能使用<>或者!=

3.7、避免where条件仅包含复合索引非前置列

复合（联合）索引：part1/part2/part3三列，但是sql语句where条件未包含part1，按照Mysql联合索引的最左匹配原则，不会走联合索引

select * from user where part2=1 and part3=2
1

3.8、隐式类型转换不能使用索引

假设keyPart字段为字符类型，但给定的是数值，涉及隐式类型转换，不能走索引

select * from user where keyPart = 123
1

3.9、order by 条件与where 条件

两者的条件要一致，否者order by 不会利用索引进行排序

4、Select优化

• 避免使用 select *
• 多表联查，小表在前，大表在后
• 使用表别名，表的别名前缀每个列名上，减少解析时间
• 用were代替having，从二者的执行顺序来进行考虑

总结：在查询过程中尽可能缩小结果集

5、增删改优化

• 插入操作：一条insert语句插入多条值更优，减少了sql语句的解析，sql语句短，减少网络传输的IO

6、查询条件优化

• 复杂查询使用中间临时表，暂存数据
• 优化group by，默认情况下会对分组结果进行排序
• join效率由于子查询
• 如果业务不需要尽量使用union all代替union