SQL优化

一、基础优化方法

1.正常的查询应该避免全表扫描，需要做的操作就是在where或order by 涉及字段建立好索引，即便是简单查询，表的数据是会随着天数的增加，越来越多的。

2.会造成索引失效的条件判断需要避免，比如!=或<>或一些运算，或函数什么的，使用or也会导致引擎放弃使用索引而导致全表扫描

3.  like 'abc%'  不会导致全表扫描，但 like '%abc%' 会，需要提高效率的话，可以考虑使用全文检索

4.in 和 not in 在连续值的情况下也会导致索引失效，例如 in (1,2,3),可以考虑替换成 between,例如  between 1 and 3

5.通常情况下，稍微复杂一点的sql可能会想到用子查询，但子查询都是容易让索引失效的，因此变成了慢查询，可以考虑快查询，比如一个结果可以通过一个复杂的SQL查询出来，但数据一多效率就慢，可以拆分成两三个查询，这时效率就会明显比慢查询高了

6.exists 是替换 in 的一个很好的选择，比如 select num from t where t in (select num from t2) 替换成 select num from t where exists (select 1 from t2 where num = t.num) 

7.建索引需要注意，并非所有索引都是能优化查询的，比如sex字段，大量重复，那么索引也是不起作用的。

8.索引页并非越多越好，只对查询有效，update和insert可能会重复建索引，所以一张表的所以建议不超过6个。

9.尽量不使用 * ，只取需要的字段，避免频繁创建和删除临时表

10.组合索引一定要注意顺序，例如id、name、age，当用到id、name两个条件时，索引也会生效，当用到name、age时，索引是不会生效的

二、中级优化方法

1.EXPLAN

说明：EXPLAN是用来查看sql的执行计划，一般建了索引，但查询还是很慢，就会用这个分析是否有用到索引，为什么索引会失效

type：连接类型，一个号的sql至少要达到range级别，杜绝出现all

key：用到的索引名，没有使用到为null

ken_len：索引长度

rows：扫描行数，该值是个预估值

extra：详细说明，注意，常见不太友好的值，如下：Using filesort，Using temporary。

2.使用合理的分页方式提高分页效率

select id,name,sex,age from user limit 88888,20

有人可能会发现，随着越往后分页，会越来越慢，因此可以考虑根据上一页的最大id来分页

select id,name,sex,age from user where  id > 88887  limit 20

3.避免隐式类型转换，小表驱动大表

4.分段查询，很多查询是通过日期范围来查询的，当日期范围太大了，扫描的行数会过多，也会影响查询效率的

三、高级优化方法

说明：高级优化方法一般是数据库容易达到IO或cup瓶颈了，才会考虑。

1.IO瓶颈两种原因

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询或新增产生大量的IO，降低查询效率，解决办法是分库、垂直分表

第二种：网络IO瓶颈，请求的数据太多，宽带不够，解决办法是分库

2.CUP瓶颈两种原因

第一种：SQL垃圾、效率低，解决办法就是增加索引

第二种：单表数据量太大，查询时扫描的太多，SQL效率低，CPU率先出现瓶颈，解决办法是水平分表

3.水平分库原理

概念：就是单个库变成多个相同的库，存储不同的数据，减轻单个库的压力。

结果：

• 数据库的结构一样
• 数据库表的数据不一样
• 所有库的并集就是全量数据

说明：库多了，IO和CPU压力自然就缓解了

4.水平分表

概念：将一个表的数据拆分到多个相同结构的表

结果：

• 每个表的结构一样
• 每个表的数据不一样
• 所有表的并集是全量数据

说明：系统的并发量没有上来，只是表的数据量增加了，减少了表的数据量，就增加了查询效率。

5.垂直分库

概念：以表为依据，根据业务不同，放到不同的库

结果：

• 每个库的结构都不一样
• 每个表的数据都不一样
• 每个库的并集是全量数据

说明：系统并发量上来了，每个库的表不多，数据库性能就能支撑的起了

6.垂直分表

概念：以字段为依据，区分字段的活跃性，分到不通的表（主表和从表中）。

结果：

• 每个表的结构不一样
• 每个表的数据不一样，但主表和从表之间有一个字段关联
• 所有表的并集是全量数据

说明：系统并发量并没有上来，但是由于表的字段多，数据量不多，并且热点数据和非热点数据在一起，单行的数据所需的空间较大，以至于数据库的缓存减少，查询时产生大量的IO，造成IO瓶颈。

四、分库分表的工具

目前用的最多的中间件：MyCat