⭐SQL优化

explain分析

EXPLAIN是MySQL中用于查询执行计划的关键字。通过EXPLAIN关键字可以查看MySQL数据库执行查询语句时的执行计划，包括使用的索引、表的读取顺序、连接方式等信息。这些信息可以帮助开发人员和数据库管理员优化查询语句，提高查询性能

即在一条sql语句前加explain即可得出一个该条sql的查询计划结果集。

重要的有id、type、key、key_len、rows、extra：

（1）id：id列可以理解为SQL执行顺序的标识，有几个select 就有几个id。

• id值不同：id值越大优先级越高，越先被执行；
• id值相同：从上往下依次执行；
• id列为null：表示这是一个结果集，不需要使用它来进行查询。

关注点： id 号每个号码，表示一趟独立的查询 , 一个 sql 的查询趟数越少越好

（2）select_type：查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询；

（3）table：表示 explain 的一行正在访问哪个表

（4）type：结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来。 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts 级别。（阿里巴巴开发手册要求）

 

system：表中只有一条数据匹配（等于系统表），可以看成 const 类型的特例

const：通过索引一次就找到了，表示使用主键索引或者唯一索引

eq_ref：主键或者唯一索引中的字段被用于连接使用，只会返回一行匹配的数据

ref：普通索引扫描，可能返回多个符合查询条件的行。

fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

ref_or_null：与ref方法类似，只是增加了null值的比较。

index_merge：表示查询使用了两个以上的索引，索引合并的优化方法，最后取交集或者并集，常见and ，or的条件使用了不同的索引。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值；

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。

index：索引全表扫描，把索引树从头到尾扫描一遍；

all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

（5）possible_keys：查询时可能使用到的索引

（6）key：实际使用哪个索引来优化对该表的访问

（7）key_len：实际上用于优化查询的索引长度，即索引中使用的字节数。通过这个值，可以计算出一个多列索引里实际使用了索引的哪写字段。（# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。）

（8）ref：显示哪个字段或者常量与key一起被使用

（9）rows：根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。

（10）extra：其他的一些额外信息

using index：使用覆盖索引

using index condition：查询的列未被索引覆盖，where筛选条件使用了索引

using temporary：用临时表保存中间结果，常用于 group by 和 order by 操作中，通常是因为 group by 的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了

using filesort：MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能

 

SQL语句优化：

1、减少请求的数据量：

（1）只返回必要的列，用具体的字段列表代替 select * 语句

也是最经典的问法：为什么不建议使用select * ，其本质含义就是减少一些不必要的字段的查询，避免浪费资源。

MySQL数据库是按照行的方式存储，而数据存取操作都是以一个页大小进行IO操作的，每个IO单元中存储了多行，每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。但是如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作。除此之外，当存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。

（2）只返回必要的行，使用 Limit 语句来限制返回的数据。

如果不使用 Limit 的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助 Limit 可以实现当找到指定行数时，直接返回查询结果，提高效率

2、优化深度分页的场景：利用延迟关联或者子查询

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

延迟关联示例如下，先快速定位需要获取的 id 段，然后再关联：

# 延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

# 覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询

select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id；

对于深度分页的情况，最好还是将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处 再 往后面遍历数据

3、分解大连接查询：

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

（1）减少锁竞争；

（2）让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

（3）分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

（4）在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

（5）查询本身效率也可能会有所提升。比如使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

4、避免使用select的内联子查询：

在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能

💡5、尽量使用Join代替子查询：

由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

select

b.member_id,b.member_type, a.create_time,a.device_model

from

member_operation_log a

inner join

(select member_id,member_type from member_base_info where `status` = 1) as b

on

a.member_id = b.member_id;

 

6、多张大数据量的表进行JOIN连接查询，最好先过滤在JOIN:

在多个表进行 join 连接查询的时候，最好先在一个表上先过滤好数据，然后再用过滤好的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间

7、避免在使用or来连接查询条件：

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

8、union、in、or 都能够命中索引，但推荐使用 in：

9、对于连续的数值，能用 between 就不要用 in：

10、小表驱动大表，即小的数据集驱动大的数据集

• 1 LEFT JOIN 左连接,左边为驱动表,右边为被驱动表.
• 2 RIGHT JOIN 右连接,右边为驱动表,左边为被驱动表.
• 3 INNER JOIN 内连接, mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表.

 

in 和 exists 都可以用于子查询，那么 MySQL 中 in 和 exists 有什么区别呢？

（1）使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。

（2）in在内表查询或者外表查询过程中都会用到索引；exists仅在内表查询时会用到索引

（3）一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。

（4）对于 not in 和 not exists，not exists 效率比 not in 的效率高，与子查询的结果集无关，因为 not in 对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

11、使用union all 替换 union：

当SQL语句需要union两个查询结果集合时，这两个结果集合会以union all的方式被合并，然后再输出最终结果前进行排序。如果用union all替代union，这样排序就不是不要了，效率就会因此得到提高.。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

12、优化Group by，使用where子句替换Having子句：

避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。

13、尽量使用数字型字段：

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

15、使用复合索引须遵守最左前缀原则：

复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。