应尽量避免在 WHERE 子句中使用 != 或 <> 操作符,否则将导致引擎放弃使用索引而进行全表扫描。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些方式的LIKE(‘a%’),如下。
WHERE 子句中使用 LIKE进行模糊查询时,在关键词前加%或者前后都加%号都无法使用索引,而进行全表扫描,如下。
应尽量避免在 WHERE 子句中对字段进行 NULL 值判断,否则将导致引擎放弃使用索引而进行全表扫描,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个默认值,如 0 作为默认值,如下。
应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描。使用 OR 的字句可以分解成多个查询,并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引,用UNION ALL执行的效率更高,如下。
应尽量避免在 WHERE 子句中使用 IN 和 NOT IN ,否则将导致全表扫描,对于连续的数值,能用 BETWEEN AND 尽量避免使用 IN。一般,用 EXISTS 代替 IN 。若需要使用 IN,在 IN 后面值的列表中,按照值的分布数量降序排列,减少判断的次数,如下。
使用BETWEEN AND 替换 IN
使用EXISTS 替代IN,用NOT EXISTS 替代 NOT IN
使用LEFT JOIN 替换 IN
如上,我们使用了如下方式优化了IN 和 NOT IN:
.使用between 替换 in
.使用exists替代in、用not exists替代 not in
.使用left join 替换 in
应尽量避免在 WHERE 子句中对 “=” 左边的字段进行函数、算术运算及其他表达式运算,可以将表达式运算移至“=”右边,否则将导致引擎放弃使用索引而进行全表扫描,如下。
如果在 WHERE 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项,可以改为强制查询使用索引,如下。
避免使用 select * from table,用具体的字段列表代替 * ,避免返回用不到的任何字段,如下。
负向查询不能使用索引
select name from user where id not in (1,3,4);
应该修改为:
select name from user where id in (2,5,6);
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
前导模糊查询不能使用索引
如:
select name from user where name like '%zhangsan'
非前导则可以:
select name from user where name like 'zhangsan%'
建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。
数据区分不明显的不建议创建索引
如 user 表中的性别字段,可以明显区分的才建议创建索引,如身份证等字段。
字段的默认值不要为 null
这样会带来和预期不一致的查询结果。
在字段上进行计算不能命中索引
select name from user where FROM_UNIXTIME(create_time) < CURDATE();
应该修改为:
select name from user where create_time < FROM_UNIXTIME(CURDATE());
最左前缀问题
如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:
select username from user where username='zhangsan' and pwd ='axsedf1sd'
select username from user where pwd ='axsedf1sd' and username='zhangsan'
select username from user where username='zhangsan'
但是使用
select username from user where pwd ='axsedf1sd'
是不能命中索引的。
如果明确知道只有一条记录返回
select name from user where username='zhangsan' limit 1
可以提高效率,可以让数据库停止游标移动。
不要让数据库帮我们做强制类型转换
select name from user where telno=18722222222
这样虽然可以查出数据,但是会导致全表扫描。
需要修改为
select name from user where telno='18722222222'
如果需要进行 join 的字段两表的字段类型要相同
不然也不会命中索引。
任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
子查询的性能又比外连接性能慢,尽量用外连接来替换子查询。
Select * from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
在我们平时用limit,如:
Select * from A order by id limit 1,10;
这样在表数据很少的时候,看不出什么性能问题,倘若到达千万级,如:
Select * from A order by id limit10000000,10;
虽然都是只查询10记录,但是这个就性能就让人受不了了。所以为什么当表数据很大的时候,我们还继续用持久层框架如hibernate,ibatis就会有一些性能问题,除非持久层框架对这些大数据表做过优化。
在遇见上面的情况,我们可以用另外一种语句优化,如:
Select * from A where id>=(Select id from a limit 10000000,1) limit 10;
确实这样快了很多,不过前提是,id字段建立了索引。也许这个还不是最优的,其实还可以这样写:
Select * from A where id between 10000000 and 10000010;
这样的效率更加高。