MySQL索引优化实战（二）

七旅之言
189 阅读6分钟

MySQL索引优化实战（二）

创建示例表

 CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

1.分页优化技巧

有一条分页的SQL语句

语句一：

SELECT * FROM employees LIMIT 90000,5

我们可以看到该语句使得MySQL从90001行开始显示数据，并且显示到90005行数据

image.png

因为主键是依次递增的，所以我们将LIMIT改成WHERE语句的条件查询

语句二：

SELECT * FROM employees WHERE id > 90000 LIMIT 5;

现在我们通过执行计划比较一下两者的执行效率

语句一：

image.png

语句二：

image.png

我们可以明显的看到，type属性的值是不同的，在语句一中进行了全表扫描，而语句二使用了范围查找并且使用了聚簇索引大大提高了效率，降低了扫描的行数。

通过上面的例子可知，如果在 主键自动递增（且数据条数没有发生改变）的情况下，可以用WHERE的条件查询代替LIMIT的查询。 （如果数据条数发生了改变，例如在id小于90000的值被删除了一条，则语句一和语句二得出的结果集会发生改变，因此该方法有一定的局限性）

根据非主键字段排序的分页排序

语句1：

SELECT * FROM employees ORDER BY name LIMIT 90000,5;

image.png

执行计划：

image.png

我们会发现，mySQL并没有使用 name字段的索引，而是使用了全表扫描，还使用了文件排序

原因：因为需要返回全部字段，如果使用name字段的索引需要进行回表，大大降低了效率，不如全表扫描

如果让排序时返回的字段尽量可能少，可以让排序和分页操作先查出主键，然后根据主键查到对应的记录

语句二：

SELECT * FROM employees e INNER JOIN (SELECT id FROM employees ORDER BY name Limit 90000,5) ed ON e.id = ed.id;

执行计划：

image.png 可以看到 驱动表使用了 索引直接进行排序，使用到了name字段的索引，大大提高了效率 执行时间少了一半以上

我们可以减少返回的字段信息，或者将字段信息返回为主键值，使得排序时候可以使用索引进行排序，然后将排序得出的结果集生成一张新的表，与之前的表进行内连接，因为新表的行数较少，且是使用主键进行匹配，所以效率比较高。

2.关联常见的两种算法

NLJ（Nested-Loop Join）嵌套循环连接算法

该算法的核心是，根据关联的表的关联行数大小分为驱动表和被驱动表，行数少的作为驱动表，行数多的作为驱动表。

下文将 被驱动表记为t1，驱动表记为t2；t1表记录为1000行，t2表记录为100行（已经过滤）

执行流程大概如下：

  • 从t2中读取一行数据（如果t2表中查询过滤条件的，会从过滤结果里取出一条记录）
  • 从第一步中取出关联的字段，到t1中查找
  • 取出表t1中满足条件的行，跟t2获取到的结果进行合并，作为结果返回给客户端
  • 重复上述3步

整个过程需要扫描整张t2表，也就是100行，那要不要扫描t1表1000行的呢？因为每次都要和t1表的字段进行比对。

如果与t1表关联的是t1表的索引字段，是不用扫描t1表的1000行的，而是扫描t1表的一行数据，因为可以根据索引直接找到被关联的行，进行直接合并，也就是说，一共只需要扫描t2表的100行，和t1表的100行，共200行。

但如果t1和t2表关联的不是索引字段，则使用NLJ算法的效率会很低，因为如果没有使用索引，每次从t2表中读取出一行数据，都需要扫描t1表的1000行，也就是说最终一共要扫描 1000 * 100 = 100000 行数据，并且是在磁盘中扫描的，效率极其低下，所以MySQL提供了另外一种算法 BNJ算法。

BNJ（Block Nested-Loop Join）基于块的嵌套循环连接算法

该算法的核心也是将关联表分为驱动表和被驱动表，行数少的作为驱动表，行数多的作为驱动表。但是不同的是，多了一个join-buffer的缓冲区

下文将 被驱动表记为t1，驱动表记为t2；t1表记录为1000行，t2表记录为100行（已经过滤），设join-buffer可存储100行数据

执行流程大概如下：

  • 把t2表的数据放入到join-buffer中
  • 把t1表中每一行取出来，和join-buffer中的数据逐行对比
  • 返回满足join的条件数据

整个过程被扫描行数可分为t1表（1000） + t2表（100行）也就是共1100行都进行了全表扫描，此时已经将t2表的数据全部读取到了join-buffer中，根据执行流程，需要将t1表的关联字段和t2表的关联字段在join-buffer中进行比对，又因为join-buffer中的数据是无序的，所以需要逐行比对，最多需要比对 1000 * 100 = 100000 次

问题1：JoinBuffer大小有限，数据量过多怎么办

比如join-buffer中能存储80条数据，但是此时有100条数据，此时会将数据进行分段，分为80条一段，20条一段，当第一段数据匹配完后从join-buffer中刷新掉，然后读取第二段的20条数据进行再次比对。

问题2：被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢

如果使用NLJ算法，也需要扫描 100000次，并且是磁盘扫描，而在join-buffer中使用的是内存扫描，效率远高于磁盘扫描，因此对于被驱动表的关链字段没有索引的关联查询，一般会使用BNL算法。

总结

  • 关联字段加索引，让MySQL做join操作时候尽量选择NLJ算法
  • 小表驱动大表，写多表连接SQL时，如果明知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去MySQL优化区判断的时间

straight_join：与join类似，但可以让左表作为驱动表，驱动右表作为被驱动表。

SELECT * FROM t2 straight_join t1 ON t2.a = t1.a

对于小表定义的明确，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

avatar
文章
阅读
粉丝