自己总结的一些常见的SQL优化
SQL优化,面试管问我我能不知道?
1. 尽量不使用SELECT *
- 表示获取表中全部字段,它需要通过查询数据字典完成,这将意味着耗费更多的时间
同时,使用*写出来的SQL不够直观
2. 选择最有效率的表名
数据库的解析器按照从右到左的顺序处理FROM字句中的表名,FROM字句中写在最后的表名将最先被解析。
在FROM字句中包含多个表的情况下:
- 如果多个表之间没有关系,将记录和字段更少的表写在后面,然后依次类推,也就是说将记录条数少的表名写在后面
- 如果多个表之间有关联关系,将引用最多的表名写在最后,然后以此类推。也就是说被其他表所引用的表写在后面
3. WHERE字句中连接顺序
数据库采用自右向左的顺序解析WHRER字句,根据这个原理,那些表之间的连接必须写在左面,
而那些能过滤掉大量数据记录的必须写在右面
4. 关于DROP,TRUNCATE和DELETE
- DELETE是删除单条数据,可加WHERE字句,可回滚,逐行执行,速度慢,属于DML
- TRUNCATE删除表数据,但是表结构还在,不可回滚,速度快,不能带WHERE,属于DDL
- DROP删除表结构和表数据,不可回滚,不可带WHERE,速度快,属于DDL
5. 使用表或列的别名
如果表或列的名称太长,使用一些简单的别名可以提高SQL的性能
6. 为了获得相同结果集的多次查询,请保持前后SQL一致
这样可以充分的利用查询缓存
7. WHERE字句里面的列尽量被索引
8. ORDER BY字句里面的列尽量被索引
9. JOIN字句里面的列尽量被索引
10. 避免在索引列上使用计算
WHERE字句中,如果索引列是函数的一部分,优化器将不会使用索引,而使用全表扫描,这样会变慢
11. 总时使用索引的第一个列
如果索引是建立在多个列上,只有他的第一个列被WHERE字句引用时,
优化器才会选择使用索引,如果不引用第一个列,优化器将使用全表扫面而忽略索引。例如下面这样就不会使用索引了:
CRATE INDEX age_grade
ON student(age,grade);
SELECT name,age,grade
FROM student
WHERE grade < 60;
12. 明知道只有一条查询结果就用LIMIT 1
LIMIT 1会避免使用全表扫描,查询到结果就不会再继续扫描了
13. 使用LIMIT实现分页逻辑
不仅提高性能,还能减少数据库和应用之间的网络传输消耗
14. 如果查询结果集允许重复,可以收用UNION ALL代替UNION
UNION ALL不去重,效率会高一些
15. 比较运算符能用=就不用<>
增加使用索引的几率
16. 使用IN代替OR
SELECT * FROM student WHERE age= 15 OR age = 18 OR age = 20;
SELECT * FROM student WHERE age IN (15,18,20);
17. 使用>= 代替 >
e.g.
SELECT * FROM student WHERE age > 17
SELECT * FROM student WHERE age >= 18
第一个会首先定位到age = 17的记录然后扫描到第一个age > 17 的记录
第二个则直接跳到age = 18 的记录
还有很多…!