SQL优化

首先排除索引失效的场景：

①：避免in或者not in

select a from b where c in(1,10)

优化：BETWEEN 1 AND 10

子查询的in用exist代替

②：模糊查询避免%在前

//索引失效
select id from a where name LIKE '%于%'
//索引有效
select id from a where name LIKE '于%'

③：索引字段避免or

select a from b where c.ip=11 or c.ip=12

优化：

SELECT * FROM t WHERE ip = 11
   UNION
SELECT * FROM t WHERE ip = 12

④：索引字段空判断

首先索引字段有NULL的情况，就不会走索引了

SELECT a FROM b WHERE c IS NULL

优化：如果非要在NULL值加索引，就得安排个默认值比如0

⑤：索引做函数计算
 

SELECT a FROM b WHERE c/2 = 5

优化：在后台做计算，算好了再传到SQL

⑥：索引隐式类型转换
 

//这里c是varchar类型
​​​​​​​select a from b where c=123; 

优化：在后台做好类型转换，转好了再传SQL

⑦：MYSQL有一个很重要的原则就是最左前缀原则

select a from b where c=10 and d=20 and e=30

假定一种场景：d字段里面有NULL值存在
如果索引的顺序是，INDEX(c,d,e)那么在d的时候索引失效了，e也不会走索引了。

要想让e走索引，INDEX(c,e,d)的顺序走。（当然了非空字段建索引这是最基本的常识）

分页优化：

通常的分页如下 或者是limit offset

随着开始行越来越大  假如有10w是开始行 一页是10行

limit语法并不是从10w行开始查的

而是查的0-10w零10行 把10w行之前的不要了

select * from 表  limit #{startLine}, #{pageSize};

 直梯式分页优化：先查出这一页最小的id值作为子查询的结果，再利用B+树的特性向后推10行

select  a  from 表 where id > ( select id from 表 order by id limit startLine, 1) limit pagesize;

再用expain语句看一下：执行的这个SQL到底走没走索引，排查一下

学习数据结构非常好用的网站：Data Structure Visualization