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,'王五')

可改写成如下形式：

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

降低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)

改写成：

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 优化

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秒