生死看淡，不服就干
生死看淡，看淡的不是生死，是扑面而来的种种挑战；不服就干，干的不是头脑发热，是破釜沉舟的决心！
我们要做到不但会写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 A
where 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;
image
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;
这两种方式仍然需要两次网络来回，但是第二种方式不需要访问数据表，因此会很快。
1.jpg
网友评论