一、内容
1、select
1-1. 索引
- 尽量用 “=” 。避免用
<>
,!=
,null
,来增加索引使用率。 - 慎用 (not) in。可能导致全表扫描,可用
between xx and yy
代替;in
很多可用exist
代替。 - 避免 or 连接条件。或者考虑让每个条件列都有索引。
- 避免对字段进行计算。如函数、四则运算等。
- 复合索引的第一个字段才能保证使用索引。
- 索引不是越多越好。insert/update会导致重建索引。一般不要超过6个。
1-2. 数量
1、limit用于查询有限数量结果,也可用于分页。避免全表扫描。减少了不必要的数据库和应用间的网络传输
2、选合适数据类型。
- 用TINYINT取代SMALLINT,降低内存与磁盘消耗。varchar代替char,可节省存储空间,提高查询效率。
- 数值型字段取代字符型,降低比较次数。字符串会逐个比较每一个字符,而数字型只比一次。
3、如果允许,用UNION ALL代替UNION。因为 UNION ALL 不去重,效率更高。
4、避免使用 SELECT *
。避免全表扫描,不能有效利用索引,增加网络IO开销。
5、条件字段尽量索引。WHERE、JOIN、ORDER BY子句里面的列尽量被索引。
2、Insert语句
1、load文本文件比insert多条更快。
2、将大的INSERT、DELETE,UPDATE查询拆分多个小查询。
3、Group by
1、指定order by null禁止排序。默认情况group by col1,col2与order by col1, col2类似会对结果排序;Extra:filesort很费时间。
4、Order by
1、where条件和order by使用相同索引 && order by顺序和索引顺序相同 && order by字段均为升序或降序
例:下列SQL可以使用索引:
SELECT * FROM t1 ORDER BY key_part1, key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但以下几种情况下则不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
--order by的字段混合ASC和DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
--用于查询行的关键字与ORDER BY中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2;
--对不同的关键字使用ORDER BY:
5、嵌套查询
1、某些情况,子查询用JOIN替代
# SQL1
select * from sales2 where company_id not in ( select id from
company2 )
# SQL2
select * from sales2 left join company2 on sales2.company_id =
company2.id where sales2.company_id is null
# SQL1 VS SQL2
1、这里join之所以更快,是因为不需要在内存中创建临时表完成SQL1中两个步骤的查询工作。
2、当company2表的id有索引,性能会更好。
6、其他
1、索引提示:
- USE INDEX提示MySQL去引用参考索引,忽视其他。
- IGNORE INDEX忽略一个或多个索引。
- FORCE INDEX强制使用参考索引。
2、相同结果集的多次执行保持SQL语句前后一致,充分利用查询缓冲。
例:
第1次:SELECT price FROM order WHERE id = 123456 and region = 'BEIJING';
第2次:同样查询,请保持语句一致性,如不要将where里id和region调换。
3、SQL调优最好在开发环境用最贴近真实数据集和硬件环境进行测试,然后再发布到生产环境中。
二、参考
1、《深入浅出MySQL》-- 第18章 SQL优化