一、查询SQL尽量不要使用select *,而是具体字段
反例:
SELECT * FROM student
正例:
SELECT id,NAME FROM student
理由:
字段多时,大表能达到100多个字段甚至达200多个字段
只取需要的字段,节省资源、减少网络开销
select * 进行查询时,很可能不会用到索引,就会造成全表扫描
二、避免在where子句中使用 or 来连接条件
反例:
SELECT * FROM student WHERE id=1 OR salary=30000
正例:
使用 union all
SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000
或者分成两条Sql写
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
理由:
使用or可能会使索引失效,从而全表扫描
对于or没有索引的salary这种情况,假设它走了id的索引,但是走到salary查询条件时,它还得全表扫描。也就是说整个过程需要三步:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就搞定。虽然mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引还是可能失效的
三、避免在索引列上使用内置函数
业务需求:查询最近七天内新生儿(用学生表替代下)
给birthday字段创建索引:
ALTER TABLE student ADD INDEX idx_birthday (birthday)
当前时间加7天:
SELECT NOW()
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)
反例:
EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
正例:
使用 union all
EXPLAIN
SELECT * FROM student
WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
理由:
使用索引列上内置函数,索引失效,
索引生效
四、避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先,实在没办法,就只能使用,并不是不能使用。如果不能使用,SQL也就无需支持了。
反例:
EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000
使用!=和<>很可能会让索引失效
五、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';
正例:先过滤,后分组
select job,avg(salary) from employee
where job ='president' or job = 'managent'
group by job;
七、复合索引最左前缀特性
创建复合索引,也就是多个字段
ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
满足复合索引的左侧顺序,哪怕只是部分,复合索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='name1'
没有出现左边的字段,则不满足最左特性,索引失效
EXPLAIN
SELECT * FROM student WHERE salary=3000
复合索引全使用,按左侧顺序出现 name,salary,索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
虽然违背了最左特性,但MYSQL执行SQL时会进行优化,底层进行颠倒优化
EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'
理由:
复合索引也称为联合索引
当我们创建一个联合索引的时候,如(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