Mysql sql优化

这里引用深入Mysql实战

为什么要优化

1. 提高资源利用率
2. 避免短板效应
3. 提高系统吞吐量
4. 同时满足更多用户的在线需求

简单来说，优化的目的是为了提高资源的利用率，让资源充分发挥价值。常见场景下，一台服务器有四大资源：cup、内存网络和磁盘，一旦其中某个资源出现问题，整个服务器提供服务的能力就会变差，优化的最终目的是为了同时满足更多用户的的在线需求

Mysql优化目标

Mysql 优化的目标主要有三个：

1. 减少磁盘IO，在数据库中主要是来自于像全表扫描这种扫描大量数据快的场景，然后就是日志以及数据块的写入所带俩的压力。
2. 减少网络带宽主要是包括两个方面，第一，sql查询时，返回太多数据；第二，插入场景下交互次数过多。
3. 降低cpu的消耗，主要包括三个方面，第一，Mysql本身的逻辑(join多表链接)，第二，额外的技术操作，比如排序分组（order by、group by）第三，是聚合函数（max、min、sum）

下面是我对sql优化的一些总结，如有错误欢迎指出来

减少IO磁盘优化

1. 可以通过加索引来避免全表扫描。
2. select查询的时候不使用select * ，新增的时候同理
3. 尽量避免使用子查询（原因写在子查询优化）

减少网络带宽优化

1. sql查询时使用limit减少查询返回的数据

2. 减少插入情况下交互次数（如下）

批量插入语句：

insert into test (id,name) values(1,'张山')
insert into test (id,name) values(2,'李四')
insert into test (id,name) values(3,'王五')
1
2
3

可改写成如下形式：

insert into test (id,name) values(1,'张山'),(2,'李四'),(3,'王五')
1

降低cpu消耗

1. order by 优化，给order by 字段添加索引避免额外的排序，减少cup资源的消耗
2. group by 优化，也可以给group by 添加索引
3. 是聚合函数（max、min、sum）可以创建一张统计表，用定时任务把数据更新到统计表，之后直接查询统计表即可

---

其他 Select优化

子查询优化

为什么要对子查询进行优化:
第一点：执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
第二点：子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响；对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
优化方案Join替代
如下sql：

select * from test where id in (select test1_id from test2)
1

改写成：

select * from test t1 left join test2 t2 on t1.id = t2.test1_id
1

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
1

Join 优化

1. 当你使用left join 或者right join 时使用小表驱动原则减少循环查询次数，或者可以使用inner join 替代，inner join 会默认使用小表驱动
2. 关联字段添加索引
   
   如上图所示，这两个表join关联。sql语句执行计划，出现了join_buffer，执行计划部分Bloack Nested-Loop
   join_buffer（Bloack Nested-Loop）表示不能通过索引去做关联条件的匹配
   我们可以看到，通过b表关联访问a时，rows是127042，整个访问过程的代价特别大，对于这种场景是给关联条件添加索引，加好索引后我们再看执行计划
   
   可以看到rows从127042降到了125，前面执行时间接近2分钟，后面只需要0.31秒