这里引用深入Mysql实战
为什么要优化
- 提高资源利用率
- 避免短板效应
- 提高系统吞吐量
- 同时满足更多用户的在线需求
简单来说,优化的目的是为了提高资源的利用率,让资源充分发挥价值。常见场景下,一台服务器有四大资源:cup、内存网络和磁盘,一旦其中某个资源出现问题,整个服务器提供服务的能力就会变差,优化的最终目的是为了同时满足更多用户的的在线需求
Mysql优化目标
Mysql 优化的目标主要有三个:
- 减少磁盘IO,在数据库中主要是来自于像全表扫描这种扫描大量数据快的场景,然后就是日志以及数据块的写入所带俩的压力。
- 减少网络带宽主要是包括两个方面,第一,sql查询时,返回太多数据;第二,插入场景下交互次数过多。
- 降低cpu的消耗,主要包括三个方面,第一,Mysql本身的逻辑(join多表链接),第二,额外的技术操作,比如排序分组(order by、group by)第三,是聚合函数(max、min、sum)
下面是我对sql优化的一些总结,如有错误欢迎指出来
减少IO磁盘优化
- 可以通过加索引来避免全表扫描。
- select查询的时候不使用select * ,新增的时候同理
- 尽量避免使用子查询(原因写在子查询优化)
减少网络带宽优化
-
sql查询时使用limit减少查询返回的数据
-
减少插入情况下交互次数(如下)
批量插入语句:
insert into test (id,name) values(1,'张山')
insert into test (id,name) values(2,'李四')
insert into test (id,name) values(3,'王五')
可改写成如下形式:
insert into test (id,name) values(1,'张山'),(2,'李四'),(3,'王五')
降低cpu消耗
- order by 优化,给order by 字段添加索引避免额外的排序,减少cup资源的消耗
- group by 优化,也可以给group by 添加索引
- 是聚合函数(max、min、sum)可以创建一张统计表,用定时任务把数据更新到统计表,之后直接查询统计表即可
其他 Select优化
子查询优化
为什么要对子查询进行优化:
第一点:执行子查询时,MySQL 需要为内层查询语句的查询结果建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。
第二点:子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响;对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
优化方案Join替代
如下sql:
select * from test where id in (select test1_id from test2)
改写成:
select * from test t1 left join test2 t2 on t1.id = t2.test1_id
Limit 优化
为什么要对Limit进行优化:
当数据库有10w条数据,此时我要查出最后10条就得 limit 99990,10此时Mysql排序出前99990条记录后仅仅需要返回最后10条记录,前99990条记录造成额外的代价消耗
优化方案一使用覆盖索引,记录直接从索引中获得,效率最高,但是这种情况只适合查询字段比较少的情况(毕竟你也不可能给所有字段添加索引) ·
什么是覆盖索引:
第一,非主键查询,入口是二级索引(就是你平时定义的普通索引),通过二级索引,第一个过程返回聚集索引的id(主键id)因为二级索引里面存的就是聚集索引的id;第二个过程是回表,相当于再做一次数据检索,然后从聚集索引中获取数据
第二,主键查询,入口是直接通过聚集索引的id,可以在聚集索引中获取数据
第三,覆盖索引,入口是二级索引,直接从二级索引当中获取数据,前提是你所查询的字段都带有索引
优化方案二sql改写
优化前提是create_time字段有索引,思路是从索引中取出20条满足条件的主键值,然后回表获取记录
select * from test t1 inner join (select id from test order by create_time limit 99990,10) t2 on t1.id = t2.id
Join 优化
- 当你使用left join 或者right join 时使用小表驱动原则减少循环查询次数,或者可以使用inner join 替代,inner join 会默认使用小表驱动
- 关联字段添加索引
如上图所示,这两个表join关联。sql语句执行计划,出现了join_buffer,执行计划部分Bloack Nested-Loop
join_buffer(Bloack Nested-Loop)表示不能通过索引去做关联条件的匹配
我们可以看到,通过b表关联访问a时,rows是127042,整个访问过程的代价特别大,对于这种场景是给关联条件添加索引,加好索引后我们再看执行计划
可以看到rows从127042降到了125,前面执行时间接近2分钟,后面只需要0.31秒