sql优化

sql优化的分类

1.硬件和操作系统层面的优化

2.架构设计方面的优化

高并发的场景下mysql数据库会承受巨大的并发压力，可以搭建mysql主从集群放置单台宕机导致mysql应用无法保证高可用，可以读写分离，避免读写冲突导致性能问题，还可以分库分表的机制，通过分库可以降低单个服务器节省的一个io压力通过分表的方式可以降低单表数据量，第四个针对热点数据可以引入更为高效的分布试数据库比如redis、MongoDB等他们可以很好的缓解mysql的访问压力同时还能提升数据的检索性能

3.程序配置优化

4.sql执行优化

以下都为sql优化

怎么进行sql优化

首先先判断什么样的sql需要优化，可以mysql中开启慢查询，设置成sql执行时长超过5s可以定义为慢sql,并记录到日志中，通过慢查询日志，定位到需要优化的sql,定位到之后，通过explain进行分析，看它是否走了索引，然后观察是否有原因导致索引失效.

1.避免使用select *

在实际业务场景中，可能我们真正需要使用的是数据中的一两列。查了很多数据但是不用就会浪费很多资源，多查出来的数据通过网络IO传输的过程中也会增加数据传输的时间，最重要的是select * 不会走覆盖索引，会出现大量的回表操作，从而导致查询的性能很低。

MySQL在解析过程中，会通过查询数据字典，将 * 转换成所有列名，这儿必将会大大耗费资源和时间
如果使用select * ，必然会使 覆盖索引的作用失效

2.用union all 代替union

union 会进行去重的操作 ,union all 不会. 在union 去重的过程中需要遍历，比较和排序，更耗时间，消耗CPU资源。

例如：

查询价格不高于5的书

select vend_id,prod_id,prod_price
from products 
where prod_price<=5

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+

查询vend_id为1001和1002

SELECT  vend_id,prod_id,prod_price 
from products
where  vend_id in(1001,1002)

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

(select vend_id,prod_id,prod_price from products where prod_price<=5)
UNION 
(SELECT  vend_id,prod_id,prod_price from products where  vend_id in(1001,1002))

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

可以看到结果集进行了去重

(select vend_id,prod_id,prod_price from products where prod_price<=5)
UNION all
(SELECT  vend_id,prod_id,prod_price from products where  vend_id in(1001,1002))

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

结果集没有进行去重

3.用小的结果集驱动大的结果集

用小表的数据集驱动大表的数据集

有order和user两张表,order10000条数据，user100条数据，如果想查所以有效用户的订单可以用in查询

select * 
from  order 
where user_id in (select id from user where status = 1)

也可以用exists关键字实现

select * 
from order 
where exists (select 1 from user where order.user_id=user.id and status = 1)

用in进行查询更合适

因为如果sql语句里面包含了in关键字，会优先执行in里面的子查询语句，然后再执行in外面的语句，如果in里面的数据量很少，作为条件查询速度更快

而如果sql语句里面包含了exists关键字，会优先执行exists左边的关键字，然后把他作为条件去更右边的语句匹配。

in适用于左边大表，右边小表

exists适用于左边小表，右边大表

不过用in,还是exists，核心思想都是小表驱动大表

4.多用limit

查询某用户的第一个订单，查看首单时间

select id,creat_date
from order 
where user_id = 123
order by creat_date asc

以上方法虽然没有问题，但是效率不高，想要先查询所有的数据，有点浪费资源

select id,create_date
from order 
where user_id=123
order by creat_date asc
limit 1;

以上方法使用只返回用户下单时间最小的那一条数据就可以了

5.高效的分页

select id,name,age
from user limit 10,20

表中数据量比较少的情况下，用limit分页没啥问题

但当表中数据量很多，可能就会出现性能问题

select id,name,age
from user limit 100000,20;

mysql会查到100020条数据，然后丢去前面的100000条，只查后面的20条，这样非常浪费资源。

对于海量数据的分页：

select id,name,age
from user where id>100000 limit 20;

先找到上次分页最大的id,然后利于id上的索引查询，要求id是连续的，并且是有序的

between

select id,name,age
from user where id between 100000 and 100020

6.用连接查询代替子查询

子查询

select * 
from  order 
where user_id in (select id from user where status = 1)

在涉及表的数量不多的情况下，子查询简单。

缺点是执行子查询时需要创建临时表，查询完毕后，需要再删除这些临时表，有一定额外的性能消耗.

select o.* from order o 
join user u 
on o.user_id=u.id
having u.status=1

7.join的表不能太多

根据阿里巴巴开发手册，join表的数量不应该超过3个

过多的join

select a.name,b.name,c.name,d.name
from a
join b on a.id=b.a_id
join c on c.b_id=b.id
join d on d.c_id=c.id
join e on e.d_id=d.id
join f on f.e_id=e.id
join g on g.f_id=f.id