1.查询SQL尽量不要使用select * ,而是使用select 具体字段。
反例:
select * from employee;
正例:
select id , name from employee;
理由:
- 只取需要的字段,节省资源,减少网络开销。
- select * 进行查询时,很可能就不会使用到覆盖索引了,就会造成回表查询。
2.如果知道查询结果只有一条或者只要最大/最小一条记录,建议用limit 1。
反例:
select id, name from employee where name = 'jay';
正例:
select id, name from employee where name = 'jay' limit 1;
理由:
- 加上limit 1 后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高
- 当然,如果name是唯一索引的话,是不必要加上limit 1了,因为limit的存在主要就是为了防止全表扫描,从而提高性能,如果一个语句本身可以预知不用全表扫描,有没有limit,性能的差别并不大。
3.应尽量避免在where子句中使用or来连接条件。
假设现在需要查询userid为1或者age为18的用户
反例:
select * from user where userid = 1 or age = 18 ;
正例:
// 使用union all
select * from user where userid = 1 ;
unionall
select * from user where age = 18 ;
// 或者分开写两条查询的sql
select * from user where userid = 1 ;
select * from user where age = 18 ;
理由:
- 使用or可能使索引失效,从而进行全表扫描。
对于or+没有索引的age这种情况,假设它走了userId的索引,但是走到age查询条件时,它还得全表扫描,也就是需要三步过程:全表扫描+索引扫描+合并 如果它一开始就走全表扫描,直接一遍扫描就完事。mysql是有优化器的,处于效率和成本的考虑,遇到or条件,索引可能失效。
4.我们在做分页需求的时候,一般会使用limit实现,但是当偏移量特别大的时候,查询效率就变的低下。
反例:
select id, name, age from employee limit 10000, 10 ;
正例:
// 方案一: 返回上次查询的最大记录(偏移量)
select id, name from employee where id > 10000 limit 10 ;
// 方案二: orderby + 索引
select id, name from employee orderby id limit 10000, 10 ;
理由:
- 当偏移量最大的时候,查询效率就会越低,因为mysql并非是跳过偏移量直接去取后面的数据,而是先把偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的。
- 如果使用优化方案一, 返回上次最大查询记录(偏移量),这样可以跳过偏移量,效率提升不少。
- 方案二使用orderby+索引,也是可以提高查询效率的。
5.优化你的like语句。
日常开发中,如果用到模糊关键字查询,很容易想到like,但是like很可能让你的索引失效。
反例:
select userId, name from user where userId like '%123' ;
正例:
select userId, name from user where userId like '123%' ;
理由:
- 把%放前面,并不走索引。
- 把%放关键字后面,还是会索引的。
6.应尽量避免在where子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫描。
反例:
select * from user where age - 1 = 10 ;
正例:
select * from user where age = 11 ;
理由:
- 虽然age加了索引,但是因为对它进行运算,索引直接迷路了。。。
7.Inner join 、left join right join,优先使用Inner join,如果是left join,左边表结果尽量小
- .Innerjoin内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集。
- leftjoin在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
- rightjoin在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。
都满足SQL需求的前提下,推荐优先使用Innerjoin(内连接),如果要使用leftjoin,左边表数据结果尽量小,如果有条件的尽量放到左边处理。
反例:
select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id > 2 ;
正例:
select * from (select * from tab1 where id > 2 ) t1 left join tab2 t2 on t1.size = t2.size ;
理由:
- 如果inner join 是等值连接, 或许返回的行数比较少,所以性能相对会好一点
- 同理, 使用了左连接, 左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能表较少。
8.使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
表结构: 有一个联合索引idx_userid_age,userId在前,age在后
反例:
select * from user where age = 10 ;
正例:
select * from user where userid = 10 and age = 10 ;
理由:
- 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1),(k1,k2),(k1,k2,k3)三个索引,这就是最左匹配原则。
- 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的。
9.不要有超过5个以上的表连接。
- 连表越多,编译的时间和开销也就越大。
- 把连接表拆开成较小的几个执行,可读性更高。
- 如果一定需要连接很多表才能得到数据,那么意味着糟糕的设计了。
10.索引不宜太多,一般5个以内。。
- 索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率。
- insert或update时有可能会重建索引,所以建索引需要慎重考虑,视具体情况来定。
- 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要。