我们要做到不但会写SQL,还要做到写出性能优良的SQL语句 。
索引问题
经常发现有很多后台程序的性能问题是因为缺少何时索引造成的,有的表甚至没有索引 。这种情况往往是因为在设计表时,没有定义索引,而开发初期,由于表中的数据不多,所以感觉不到索引对性能的影响,但是一旦项目发布,表中数据多了之后,缺少索引对性能的影响便会越来越大 。
还有不要在建立索引的数据列上进行一下操作:
避免对索引字段进行计算操作;避免在索引字段上使用not、<>、!=;避免在索引字段单使用 IS NULL、IS NOT NULL;避免在索引字段单出现数据类型转换;避免在索引字段上使用函数;避免建立索引的列中使用空值 。
文章插图
SQL语句模型结构优化
a. ORDER BY + LIMIT组合的索引优化
如果一个SQL语句形如:SELECT [column1],[column2],…. FROM[TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];这个SQL语句优化比较简单,在[sort]这个栏位上建立索引即可 。
文章插图
b. WHERE + ORDER BY + LIMIT组合的索引优化
如果一个SQL语句形如:SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE]ORDER BY [sort]LIMIT [offset],[LIMIT];这个语句,如果你仍然采用第一个例子中建立索引的方法,虽然可以用到索引,但是效率不高 。更高效的方法是建立一个联合索引(columnX,sort)
文章插图
c. WHERE+ORDER BY多个栏位+LIMIT
如果一个SQL语句形如:SELECT * FROM [table]WHERE uid=1 ORDER x,y LIMIT 0,10;对于这个语句,大家可能是加一个这样的索引:(x,y,uid) 。但实际上更好的效果是(uid,x,y) 。这是由MySQL处理排序的机制造成的 。
文章插图
where子句使用IN 或 NOT IN的优化
in和not in 也要慎用,否则也会导致全表扫描 。
方案一:between替换in如SQL:SELECT id FROM A WHERE numin(1,2,3) 优化成:SELECT id FROM A WHEREnum between 1 and 3方案二:exist替换in如SQL:SELECT id FROM A WHERE numin(select num from b ) 优化成:SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)方案三:left join替换in如SQL:SELECT id FROM A WHERE num in(selectnum from B) 优化成:SELECT id FROM A LEFT JOIN BON A.num = B.num
文章插图
limit分页优化
当偏移量特别时,limit效率会非常低SELECT id FROM A LIMIT 1000,10 很快SELECT id FROM A LIMIT 90000,10 很慢优化方法:方法一:select id from A order by id limit 90000,10; 很快,0.04秒就OK 。因为用了id主键做索引当然快方法二:select id,title from Awhere id>=(select id from collect order byid limit 90000,1) limit 10;方法三:select id from A order by id between 10000000 and 10000010;尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t wherenum=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20