一、基础优化方法
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