有关于优化SQL的建议
1、查询SQL尽量不要使用select *,而是select具体字段。
反例子:
-
select * from employee;
正例子:
-
select id,name from employee;
理由:
- 只取需要的字段,节省资源、减少网络开销。
- select * 进行查询时,很可能就不会使用到覆盖索引了,就会造成回表查询。
2、如果知道查询结果只有一条或者只要最大/最小一条记录,建议用limit 1
假设现在有employee员工表,要找出一个名字叫jay的人.
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
反例:
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来连接条件
新建一个user表,它有一个普通索引userId,表结构如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假设现在需要查询userid为1或者年龄为18岁的用户,很容易有以下SQL
反例:
select * from user where userid=1 or age =18
正例:
//使用union all
select * from user where userid=1
union all
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条件,索引可能失效,看起来也合情合理。
参考: