?
我们要做到不但会写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
删除重复记录:
最高效的删除重复记录方法 ( 因为使用了ROWID)例子:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
用TRUNCATE替代DELETE:
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。(TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML)。
使用表的别名
当在SQL语句中有连接多个表的时候,请使用表的别名并把别名的前缀置于每个列名上,这样就可以减少解析的时间并减少那些有列名歧义引起的语法错误。
使用用户自定义变量
用户自定义变量是一个用来存储内容的临时容器,在 MySQL 连接的整个过程都存在,可以使用 set 和 select 来定义:
set @columnName :=(select column_name from tableName where id =column_id);set @columnId :=888;
之后可以在任何使用表达式的地方使用自定义变量:
Select * from tableName where column_id >@columnId and column_name = @columnName;
在下列情况下不能使用自定义变量:
a) 使用自定义变量的查询,不能使用缓
b) 不能在使用常量或标识符的地方使用自定义变量,如表名,列名,limit 子句
c) 自定义变量只在一个连接中有效,不能作为连接间的通信
d) MySQL优化器在某些场景下会将这些变量优化掉,会导致不同的预期结果
1:自定义变量可以在给变量赋值的同时使用这个变量,如java的a =a+1 操作。
如实现行号的功能
set @rownum:=0;select @rownum:=@rownum+1 as num, resource as ip from tableName limit 10;
2:避免重复查询刚刚更新过的数据
如果想在更新行的同时又希望获取该行的信息,则可以使用自定义变量实现
方式一:update 之后,在 通过 select 查询
update tableName set userName =’Herbert’ where column_id =1;select userName from tableName where column_id =1;
方式二:定义自定义变量
updata tableName set userName = ‘Herbert’ where id =1 and @desc:= “Herbert”;select @desc;
这两种方式仍然需要两次网络来回,但是第二种方式不需要访问数据表,因此会很快。
?