Sql 语句优化总结

一、查询SQL尽量不要使用select *，而是具体字段

反例：

SELECT * FROM student
1

正例：

SELECT id,NAME FROM student
1

理由：

字段多时，大表能达到100多个字段甚至达200多个字段

只取需要的字段，节省资源、减少网络开销

select * 进行查询时，很可能不会用到索引，就会造成全表扫描

二、避免在where子句中使用 or 来连接条件

反例：

SELECT * FROM student WHERE id=1 OR salary=30000
1

正例：
使用 union all

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000
1
2
3

或者分成两条Sql写

SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
1
2

理由：

使用or可能会使索引失效，从而全表扫描

对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

三、避免在索引列上使用内置函数

业务需求：查询最近七天内新生儿（用学生表替代下）

给birthday字段创建索引：

ALTER TABLE student ADD INDEX idx_birthday (birthday)
1

当前时间加7天：

SELECT NOW()	
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)
1
2

反例：

EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
1
2
3

正例：
使用 union all

EXPLAIN
SELECT * FROM student
WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
1
2
3

理由：
使用索引列上内置函数，索引失效，

索引生效

四、避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
SELECT * FROM student WHERE salary!=3000

EXPLAIN
SELECT * FROM student WHERE salary<>3000
1
2
3
4
5

使用!=和<>很可能会让索引失效

五、where中使用默认值代替null

1、并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关

2、如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的

3、如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

六、提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤：

select job，avg（salary） from employee  
group by job 
having job ='president' or job = 'managent';
1
2
3

正例：先过滤，后分组

select job，avg（salary） from employee 
where job ='president' or job = 'managent' 
group by job;
1
2
3

七、复合索引最左前缀特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
1

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='name1'
1
2

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
SELECT * FROM student WHERE salary=3000
1
2

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
1
2

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'
1
2

理由：

复合索引也称为联合索引

当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则

联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

八、不要有超过5个以上的表连接

关联的表个数越多，编译的时间和开销也就越大

每次关联内存中都生成一个临时表

应该把连接表拆开成较小的几个执行，可读性更高

如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

阿里规范中，建议多表联查三张表以下

九、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集

left join会返回左表所有的行，即使在右表中没有匹配的记录

right join会返回右表所有的行，即使在左表中没有匹配的记录

理由：

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点

同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

十、尽量使用union all替代union

理由：

union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复

union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION