sql的一些优化思路与补充

思路

sql优化的核心:减少IO,减少CPU计算消耗

IO

减少网络IO和磁盘IO

网络IO

数据量一次不应过大,也不能过于频繁;

解释:机器的内存在操作一次内存过大的数据时,需要一次申请对应的内存(不论是磁盘还是内存)都会造成内存的过度的使用
对于程序而言jvm要考虑是不是扩容,要不要触发GC,系统内存可能需要判断内存要不要连续.过于频繁的请求显然也不可取.

总之:实践的结果是,大多数操作都不应该一次使用过大的数据量,也能频繁的请求
举例:批量插入数据时:每次插入的数据不能过大,但是也不能过小

磁盘IO

解释:磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。

节省IO有很多对应的sql技巧(不一一细说)
这里有个提纲的建议:往往sql优化是在联表和子查询下
这里有2个点:1.根据业务优化 2.写sql的思路要以结果为导向

单表查询

1.type要走索引
最好是const级别;ref (索引前缀,例如组合索引的的左匹配),range(索引范围),也可;index(索引全表扫描),可能就要考虑了 如果使用了ALL,就肯定要优化

2.extra
优先只用using index,尽量省掉using where (回表查询) ,
例如:
select * from user where name = ‘liam’;
select id,name from user where name = ‘liam’;

解释一下:回表扫描这里要介绍一下,聚集索引(clustered index )和普通索引(secondary index)
普通索引要走一遍索引树,
如果有些行的数据不在索引中,可能就需要走一遍聚集索引去定位行中的其他数据

3.能用limit就用

4.很多写法上的优化,例如 != 换成 in等等

5.分页查询遇到大的分页,带上where 条件 > * pageSize ,防止分页跳过太多数据

联表查询

单表的优化在这里是适用的

1.联表的查询关联条件:有无索引(基础),索引是不是同一属性
解释:关联条件要有索引

2.小表驱动大表

解释:left join和 right join都强制了由对应表驱动,而inner join在myql中是根据数据量来决定的,哪个数据量小就用哪个驱动straight_join会严格按照书写顺序去执行表的驱动

3.可以有效利用where条件来减小表的数据量

子查询

子查询往往是优先执行的

1.能不能不用子查询(结果为导向的sql,来优化),考虑优先联表

解释:因为子查询每次查询出来的结果会暂时存放在临时表里，也会进行IO，与其如此还不如直接使用join来进行关联。子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，不少情况下，子查询可以被更有效率的连接（JOIN）…替代。

2.注意尽量不要排序(除非非要找最大的,也请限制好数量,有些时候如果不写limit可能会被mysql优化掉)

3.如果非要子查询,单表查询的优化思路此处是适用的

一些补充

默认下:InnoDB隔离级别是:
可重复读(Repeatable Read)
在此级别之下,如果全表扫描,会用表锁,走索引,用行锁
补充:InnoDB中的行锁是通过给索引加上锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

但是互联网项目一般采用read commit 的的隔离级别
原因:
1.在Repeatable Read隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行
解释:锁表这么大的范围显然不太满足并发的要求
2.在Repeatable Read隔离级别下，存在间隙锁，导致出现死锁的几率比read commit 大的多.
解释:Repeatable Read为了防止幻读,会对一定的范围进行加锁(间隙锁),容易出现死锁

3.在read commit 隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性
解释:有时候,我们2个事务在执行时,seesion1可能会读到seesion2加锁的行;此时,在read commit 隔离级别下,seesion1会重新再读一遍数据且加锁;而在Repeatable Read下却只能等待.