mysql 性能优化（九）limit、 join（聚集索引、二级索引，辅助索引）、in、exsits、count优化策略

最新推荐文章于 2024-10-19 17:38:00 发布

CodingAnHour

最新推荐文章于 2024-10-19 17:38:00 发布

阅读量1.1k

点赞数

分类专栏： MySql 文章标签： mysql优化 sql优化 sql优化策略

本文链接：https://blog.csdn.net/Extraordinarylife/article/details/120357694

版权

MySql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

1、limit分页优化

优化场景：当表数据非常大，百万、千万甚至更多，例如： select * from table limit 100000,10;随着limit的值越来越大，查询效率也越来越低。此sql结果是查询10条数据，实际上是从表中查询出100010条数据，之后抛弃前100000条数据，保留剩下的10条，所以查询分页越靠后的效率越低

1.1、若表id是连续自增的分页优化

如果表是中id是连续自增，没有间断的，可以直接用主键id当作查询条件

select * from employees where id > 100000 limit 10;

由mysql中数据结构B+树，主键存储是有序的，所以使用 id > 100000 即走了索引表，又优化了查询速度

EXPLAIN select * from table limit 100000,10;

EXPLAIN select * from employees where id > 100000 limit 10;

实际中，mysql内部索引优化器也会对我们写的sql进行优化，可能
EXPLAIN select * from table limit 100000,10;
也使用了索引，实际是因为mysql内部对sql进行了优化的原因

1.2、根据非主键字段排序优化排序

假设表table中name是索引字段

select * from table ORDER BY name limit 90000,5; sql这样写索引是无效的

扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。
可以如下方式写sql

SELECT
	* 
FROM
	`table` e
	INNER JOIN ( SELECT id FROM `table` ORDER BY NAME LIMIT 90000, 5 ) ed ON e.id = ed.id;

需要的结果与原 SQL 一致，执行的时间可能减少了一半以上

可以执行EXPLAIN操作试试

EXPLAIN select * from table e inner join (select id from table order by name limit 90000,5) ed on e.id = ed.id;

# 往t1表插入100行记录
# 往t2表插入1万行记录

虽然使用了衍生表，性能略有下降，但是子查询中使用了索引覆盖，完全走索引表，会非常高效的查询出分页的id衍生表。

2、Join关联查询优化

mysql的表关联常见有两种算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

EXPLAIN select*from t1 inner join t2 on t1.id= t2.id;

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

2.3、嵌套循环连接 Nested-Loop Join(NLJ) 算法

连接字段必须是主键索引字段
连接字段必须是主键索引字段
连接字段必须是主键索引字段

优化器一般会优先选择小表做驱动表。所以使用 inner join 时，sql实际执行的排在前面的表并不一定就是驱动表。
尽量使用小表在前，大表在后
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下（假设t1是小表，t2是大表）：

从表 t1 中读取一行数据；
从第 1 步的数据中，取出关联字段 a，到表 t2 中查找；
取出表 t2 中满足条件的行，跟 t1 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

整个读取过程

读取 t1 表的所有数据，然后遍历这每行数据中字段 a 的值（总共100次）
根据 t1 表中 a 的值主键索引扫描 t2 表中的对应行(扫描100次 t2 表的主键索引，1次扫描可以认为最终只扫描 t2 表一行完整数据，也就是总共 t2 表也扫描了100 行)。
因此整个过程扫描了 200 行，也就是2倍的t1表总数。

如果被驱动表的关联字段不是主键索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join 算法。

2.2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

join_buffer：MySQL对于table join的一个重要的优化手段，用来缓存JOIN KEY 二级索引（辅助索引）或无索引这两类 JOIN 检索的一个 BUFFER 内存区域块。在"暴力"连接的时候可以极大提高join查询的效率。
- 如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。
- 如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB或者根据实际情况综合考虑设置。
```
set session join_buffer_size = 1024 * 1024 * 1024;

set session join_buffer_size=default;
```
每张使用 InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引，它保存着每一行的数据，通常，聚集索引就是主键索引。为了得到更高效的查询、插入以及其他的数据库操作的性能，你必须理解 InnoDB 引擎是如何使用聚集索引来优化常见的查找和 DML 操作

如果你的表定义了一个主键，InnoDB 就使用它作为聚集索引。因此，尽可能的为你的表定义一个主键，如果实在没有一个数据列是唯一且非空的可以作为主键列，建议添加一个自动递增列作为主键列。

如果你的表既没有主键，又没有合适的唯一索引，InnoDB 内部会生成一个隐式聚集索引 —— GEN_CLUST_INDEX，该索引建立在由 rowid 组成的合成列上。数据行根据 InnoDB 分配的 rowid 排序，rowid 是一个 6 字节的字段，随着数据插入而单调递增。也就是说，数据行根据 rowid 排序实际上是根据插入顺序排序。
二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。

问题：
1. 相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间，那为什么要这样设计呢？
  　　InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。
2. 那么InnoDB有了聚簇索引，为什么还要有二级索引呢？
  聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。
  除了聚集索引外的其他索引类型都属于二级索引。在 InnoDB 中，二级索引中的每个记录都包含该行的主键列，以及二级索引指定的列；聚集索引中，InnoDB 通过主键值来查询数据行。
3. 为什么一般情况下，我们建表的时候都会使用一个自增的id来作为我们的主键？
  InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的，每插入一条记录，其实都是增加一个叶子节点，如果主键是顺序的，只需要把新增的一条记录存储在上一条记录的后面，当页达到最大填充因子的时候，下一跳记录就会写入新的页中，这种情况下，主键页就会近似于被顺序的记录填满。
若表的主键不是顺序的id，而是无规律数据，比如字符串，InnoDB无法加单的把一行记录插入到索引的最后，而是需要找一个合适的位置（已有数据的中间位置），甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标页可能不在内存中，这就导致了大量的随机IO操作，影响插入效率。除此之外，大量的页分裂会导致大量的内存碎片。

强烈建议不要使用UUID当作主键索引，使用整型来作为索引
强烈建议不要使用UUID当作主键索引，使用整型来作为索引
强烈建议不要使用UUID当作主键索引，使用整型来作为索引

EXPLAIN select*from t1 inner join t2 on t1.b= t2.b;

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

扫描总数=驱动表总数+被驱动表总数
在这里插入图片描述

把 t1 的所有数据放入到 join_buffer 中
把表 t2 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，所以扫描总数=驱动表总数+被驱动表总数

因 join_buffer 里的数据是无序的，因此对表 t2 中的每一行，都需要做100次判断（t1表总数）因此总判断数=驱动表总数 * 被驱动表总数

被驱动表的关联字段没索引或二级索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000（无索引全盘扫描） = 100万次，这个是磁盘扫描，效率非常低

NLJ 算法比 BNL算法性能更高实际开发中可以使用explain多试试

straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

3、in和exsits优化

# 子查询中数值少用in
select * from A where id in (select id from B)

exists：将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

# 表A数据少则用exists
select * from A where exists (select 1 from B where B.id = A.id)

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

4、count查询优化

# 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0; 
set global query_cache_type=0;

假设table表中有唯一索引、复合索引、单值索引中一种

EXPLAIN select count(1) from table; 
EXPLAIN select count(id) from table;
# 有索引name
EXPLAIN select count(name) from table; 
EXPLAIN select count(*) from table;
# 无索引字段，不考虑count
EXPLAIN select count(code) from table;