Mysql执行计划Explain与索引优化

最新推荐文章于 2024-03-16 17:55:29 发布

风雨秋烟

最新推荐文章于 2024-03-16 17:55:29 发布

阅读量573

点赞数

分类专栏： mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_44088051/article/details/121991012

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : 竞拍测试
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : 120.24.70.201:33106
 Source Schema         : jypt

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 20/12/2021 16:17:26
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor`  (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of actor
-- ----------------------------
INSERT INTO `actor` VALUES (1, 'a', '2021-12-17 08:45:23');
INSERT INTO `actor` VALUES (2, 'b', '2021-12-17 08:45:36');
INSERT INTO `actor` VALUES (3, 'c', '2021-12-17 08:45:38');

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `hire_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_position`(`name`, `age`, `position`) USING BTREE,
  INDEX `idx_hire_time`(`hire_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100003 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'LiLei', 22, 'manager', '2021-12-17 16:30:58');
INSERT INTO `employees` VALUES (2, 'HanMeimei', 23, 'dev', '2021-12-17 16:31:18');
INSERT INTO `employees` VALUES (3, 'Lucy', 23, 'dev', '2021-12-17 16:31:46');

-- ----------------------------
-- Table structure for film
-- ----------------------------
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film`  (
  `id` int(11) NOT NULL,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of film
-- ----------------------------
INSERT INTO `film` VALUES (1, 'film1');
INSERT INTO `film` VALUES (2, 'film2');
INSERT INTO `film` VALUES (3, 'film3');

-- ----------------------------
-- Table structure for film_actor
-- ----------------------------
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `film_id` int(11) NULL DEFAULT NULL,
  `actor_id` int(11) NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_film_actor_id`(`film_id`, `actor_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of film_actor
-- ----------------------------
INSERT INTO `film_actor` VALUES (1, 1, 1, 'dfdfs');
INSERT INTO `film_actor` VALUES (2, 1, 2, 'fdsafd');
INSERT INTO `film_actor` VALUES (3, 1, 3, 'fdafds');

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NULL DEFAULT NULL,
  `b` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_a`(`a`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t1
-- ----------------------------

-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NULL DEFAULT NULL,
  `b` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_a`(`a`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 102 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t2
-- ----------------------------

SET FOREIGN_KEY_CHECKS = 1;

explain中的列

1. id列
id列的编号是 select 的序列号，有几个 select 就有几个id，id的执行顺序，如果id不相同从大到小，如果相同，就从上往下，下图就是一个id不同，按照id大小来执行的
在这里插入图片描述
2. select_type列
select_type 表示对应行是简单还是复杂的查询。
(1)、simple：简单查询。查询不包含子查询和union

explain select * from film where id = 2;

在这里插入图片描述
(2)、primary：复杂查询中最外层的 select
(3)、subquery：包含在 select 中的子查询（不在 from 子句中）
(4)、derived：包含在 from 子句后面的子查询，MySQL会将结果存放在一个临时表中，也称为派生表(derived就是派生表的含义)

EXPLAIN select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

在这里插入图片描述
上图分析一下首先是id为3的sql语句DERIVED(from后面的那条sql语句生成的派生表)，在执行id为2的SUBQUERY(select后面的子查询)，最后执行id为1的PRIMARY，注意id为1旁边是一个，意思是最外层的select是通过id为3那条sql语句得到的，derived3拆分开来derived派生3就是id等于3

3. table列
table的意思是这条sql语句执行了那张表的意思

4. type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref。

system：一张表只有一条记录
const：常量的意思，查询一条数据像查询一个常量一样快，用于primary key主键索引或 unique key唯一索引的条件查找，所以表最多有一个匹配行，读取1次，速度比较快

explain select * from  (select * from film where id = 1) tmp;

在这里插入图片描述
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录，比如下面这条sql语句，在做join关联的时候，film表的type是eq_ref，而film_actor 表示ALL，原因是
film.id是主键索引，肯定只有一条记录，而film_actor表的film_id并没有用到任何索引，可能会是多条。

explain select * from film_actor LEFT JOIN film ON film_actor.film_id = film.id;

在这里插入图片描述
ref：这个是普通索引返回多条数据，举个例子，唯一索引和主键索引都只有一行记录，而普通索引会返回多行记录

简单 select 查询，name是普通索引（非唯一索引）

EXPLAIN select * from film where name = 'film1';

在这里插入图片描述
2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分，只能用最左边开头的，使用遵循最佳左前缀法则

EXPLAIN select film_id from film LEFT JOIN film_actor ON film.id = film_actor.film_id

在这里插入图片描述
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

EXPLAIN select * from actor where id > 1;

在这里插入图片描述
index：覆盖索引的意思，主键索引构建的索引树存储的是，主键以及主键对应整行的数据，而二级索引树存储的是二级索引构建的索引树，有二级索引就会先去扫码二级索引树，如果二级索引有就直接拿到结果了，比方下面逐个sql语句，film有两个索引，id是主键索引，name是二级索引，我先去扫码二级索引树，二级索引存储了name和主键了，直接就拿出来了，不需要再去扫描主键构建的索引树了。

EXPLAIN select * from film;

在这里插入图片描述
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点，B+Tree树的每一个节点都会扫描

5. possible_keys列
有可能会用到的索引，会判断执行的sql有可能会用上那个索引

6. key列
实际用的索引， possible_keys是可能用上了那个索引，而key就是执行的sql真正用了那个索引，如果为null就是本地sql没有用上任何索引

7. key_len列
key_len计算规则如下：
字符串类型
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

下面这句代码是5个字节，int类型4个字节，允许为null1个字节，加起来就是5个字节，我们很容易判断film_actor表使用的是联合索引，遵循最佳左前缀原则，film_id字段为左边第一位，所以使用的是film_id

       EXPLAIN select * from film_actor where film_id = 3

8. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

9. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数

10. Extra列
这一列展示的是额外信息。常见的重要值如下：
(1)、Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，覆盖索引是没有去聚集索引构建的索引树扫描，而是在二级索引里扫描的，select的结果在二级索引里就可以拿到了，就不需要再去聚集索引的索引树里扫描了，没有做回表的操作，比如下面这个sql,film_id和actor_id一起组建的联合索引，我先去二级索引里面找，直接找到了film_id，无需回表再去聚集索引里面查询整行的数据了

EXPLAIN select film_id from film_actor where film_id = 1;

在这里插入图片描述
(2)、Using where：使用 where 语句来处理结果，where 后面的查询条件没有使用索引

EXPLAIN select * from actor where name = 'a'

在这里插入图片描述

(3)、Using index condition：where条件后的查询条件是索引字段，并且是范围查询

EXPLAIN select * from film_actor where film_id > 1;

在这里插入图片描述

(4)、Using temporary：需要一张临时表来处理数据，需要加索引来优化
actor表的name字段没有建立索引，查询的时候先会全部筛选出来，在内存中用一张临时表来过滤

 EXPLAIN select distinct name from actor;

在这里插入图片描述

film表name字段建立了普通索引，会先去二级索引里查询，查询的时候直接就会过滤重复的了

 EXPLAIN select distinct name from film;

在这里插入图片描述

(5)、Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

EXPLAIN select * from actor order by name;

索引最佳实践

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `position` varchar(20) DEFAULT NULL,
  `hire_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1.全值匹配

EXPLAIN select * from employees where name = 'LiLei';

在这里插入图片描述

EXPLAIN select * from employees where name = 'LiLei' and age = 22;

在这里插入图片描述

EXPLAIN select * from employees where name = 'LiLei' and age = 22 and position = 'manager';

在这里插入图片描述
2.最左前缀法则
如果索引了多列，要遵守最左前缀法则,从左边开始排序使用，也就是name age position 要从左到右使用，我们联想一下联合索引的B+Tree树的树，name age position 三个从第name开始从上而下排列，如果我直接从age position开始，那么根本没有排序走的就是全表，看下面的sql语句,第一条正确，第二条越过了age，记住一句话排好序的索引结构，age没有排序，position无法排序，第三条也是错误，还是那句话排好序的索引结构，name和age都没排序如何排序position

EXPLAIN select * from employees where name = 'Bill' and age = 31;  #正确
EXPLAIN select * from employees where name = 'Bill' and position = 'dev'; #错误
EXPLAIN select * from employees where position = 'dev';  #错误

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
left(name,3)为什么没有走索引，在联合索引里存储的name是数据库里name的所有信息，必须是等值才能走，left(name,3)已经把name直接拆分，无法匹配在联合索引树里进行等值匹配

alter table employees add index idx_hire_time (hire_time) USING BTREE;
EXPLAIN select * from employees where left(name,3) = 'LiLei';

在这里插入图片描述
date(hire_time)为什么失效，在普通索引里存储的是hire_time的年月日时分秒信息，而date(hire_time)做了转换，当然失效

explain select * from employees where date(hire_time) = '2021-12-17';

在这里插入图片描述
hire_time 进行范围全值查询，有可能走有可能不走，比方说只有几条数据的范围，还要先扫码二级索引在回表定位聚集索引拿数据，那我不如直接去聚集索引里面拿还要快，也有可能走了的，数据大可能会先去二级索引树拿到数据再去聚集索引树回表

EXPLAIN select * from employees where hire_time >= '2021-12-17 00:00:00' and hire_time >= '2021-12-17 23:59:59'

在这里插入图片描述

position = 'manager’是无效的，没有走索引，还是排好序那个逻辑，name是等值肯定拍好序的，name排好序age肯定也是有序的能走索引，但是age走的是范围，并非等值，所以position 就是无序的，无法走索引

EXPLAIN select * from employees where name='LiLei' and age > 22 and position = 'manager'

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
下面这条sql执行就是没有走聚集索引，在二级索引就可以拿到结果集了

explain select name,age from employees where name='LiLei' and age = 23 and position = 'manager'

6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
下面name!='LiLei’为什么会失效，范围太大了，我在二级索引每一个都要去比对，比对完了以后还要去会表聚集索引，不如直接一开始就去聚集索引查询

explain select * from employees where name!='LiLei'

在这里插入图片描述
7.is null,is not null 一般情况下也无法使用索引
is null和is not null如果走索引的话，先去二级索引一个一个的匹配，然后再回表去聚集索引查询，那我不如直接一开始就走聚集索引

explain select * from employees where name is null

8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
'%Lei’匹配的末尾开始的，而在B+Ttree树里存储的是全部name的值，我只有末尾的一部分根本无法找到具体的name值，无法排序

  EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

在这里插入图片描述
'Lei%'百分号在后匹配的是从前缀开始，这种为什么会会走索引，name是全值，但是从左到右匹配前面三个字母Lei是可以的，类似于name=‘Lei’

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

在这里插入图片描述
9.字符串不加单引号索引失效
name = '1000’改为name=1000肯定会失效的，在索引里存储的是字符串‘1000’，而不是1000所以肯定会失效

  EXPLAIN SELECT * FROM employees WHERE name = '1000';

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

	EXPLAIN select * from employees where name = 'LiLei' or name = 'HanMeimei'

在这里插入图片描述
11、联合索引第一个字段使用范围查找
name > 'LiLei’为什么会失效，首先我查询的是全部数据，我去联合索引树里找了过后还要回表，那mysql自己判断我还不如直接走聚集索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position = 'manager';

在这里插入图片描述
12、联合索引第一个字段使用范围查找使用覆盖索引
name > 'LiLei’范围查找使用覆盖索引就生效了，因为我去联合索引树扫码就可以拿到name的数据无需回表了，mysql自动判断这个可以走索引，但是第一个name使用的是范围，并非等值，后面的age和position无序了

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position = 'manager';

在这里插入图片描述

13、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
为了测试in和or，增加10万条数据

DROP PROCEDURE if EXISTS test_insert;
DELIMITER ;;
CREATE PROCEDURE test_insert()
BEGIN
DECLARE y BIGINT DEFAULT 1;
WHILE y<100000
DO
INSERT INTO employees(name,age,position) VALUE (CONCAT('wdp',y),y,'dev');
SET y=y+1;
END WHILE ;
commit;
END;;
CALL test_insert();

EXPLAIN SELECT name,age,position FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position = 'manager';

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position = 'manager';

在这里插入图片描述

14、order by优化
从key_len看出来只走了name，在联合索引树里name是等值排好序的，order by age肯定也是可以走索引的也排好序的，再走position肯定也可以

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  AND position = 'manager' ORDER BY age;

在这里插入图片描述

出现了Using filesort文件排序，代表ORDER BY position失效，因为name是等值肯定排好序的，但是没有age的排序，position无法生效

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'   ORDER BY position;

在这里插入图片描述
ORDER BY age, position这个没有Using filesort文件排序，是成功的name是等值，age在排序，再是position

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'   ORDER BY age, position;

在这里插入图片描述
ORDER BY position, age这个为什么失效，因为直接从position开始的，跳过了age肯定不能排序，就出现了Using filesort

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'   ORDER BY position, age;

在这里插入图片描述
position desc出现了Using filesort，顺序没有错name ，age，position ，但是mysql排序默认升序，position desc是降序，最终导致失败

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'   ORDER BY age asc, position desc;

在这里插入图片描述
name in出现了Using filesort，虽然数据量过大in是会走索引没有错，但是记住in并不是等值，既然不是等值下面的age和position就走不了索引，所以order by失效

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei')   ORDER BY age, position;

在这里插入图片描述
name > 'a’在order by排序为什么会失效，大于是范围，第一个就是范围就会判断还不如直接聚集索引，免得回表

EXPLAIN SELECT * FROM employees WHERE name > 'a' ORDER BY name;

在这里插入图片描述
范围要想生效，只能走覆盖索引，这样直接在联合索引树里可以查到，不需要再回表聚集索引了

EXPLAIN SELECT name FROM employees WHERE name > 'a' ORDER BY name;

在这里插入图片描述
15、分页优化
下面这条sql语句是查询出10010条记录，然后把前面10000条删掉，只要后面这10条记录

select * from employees limit 10000,10;

下面这条sql在衍生表里先用name走二级索引排序出来，再去做关联，这样就走到了索引，毕竟没有全表扫码了

select e.* from employees e INNER JOIN (SELECT id FROM employees ORDER BY name LIMIT 10000,10) ed ON e.id = ed.id

Join关联查询优化

增加t1和t2的数据

DROP PROCEDURE if EXISTS test_t1;
DELIMITER ;;
CREATE PROCEDURE test_t1()
BEGIN
DECLARE y BIGINT DEFAULT 1;
WHILE y<=10000
DO
INSERT INTO t1(a,b) VALUE (y,y);
SET y=y+1;
END WHILE ;
commit;
END;;
CALL test_t1();

DROP PROCEDURE if EXISTS test_t2;
DELIMITER ;;
CREATE PROCEDURE test_t2()
BEGIN
DECLARE y BIGINT DEFAULT 1;
WHILE y<=100
DO
INSERT INTO t2(a,b) VALUE (y,y);
SET y=y+1;
END WHILE ;
commit;
END;;
CALL test_t2();

驱动表的关联
在join之前先讲一个概念，就是小表驱动大表，小表称为驱动表，大表称为被驱动表，简单理解一下就是两张表进行join相连，要先执行小表的数据，用小表的数据去匹配大表的数据，inner join是mysql自行判断，那个是小表那个大表，可以改成straight_join，强制让左边的表驱动右边的表，例如：elect * from t2 straight_join t1 on t2.a = t1.a，left join左边是小表右边是大表，right join右边是小表左边是大表
1、嵌套循环连接 Nested-Loop Join(NLJ) 算法

EXPLAIN select * from t1 INNER JOIN t2 ON t1.a = t2.a;

在这里插入图片描述
上面sql执行流程
t2表先执行，t1表再执行，这是一个小表驱动大表，t1表10000条记录，t2表100条记录，可以看出，t2是小表，t1是大表，他们底层是这么执行的，先在磁盘扫描t2表，从上而下全表扫描，扫出来就是100行，然后再把t2表的a字段也就是关联字段拿去匹配t1表，相当于select * from t1 where t1.a=t2.a，由于a字段又是加了索引的，扫描就会很快，总共扫描100行，所以总共t1和t2在磁盘上就扫描了200次。

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

EXPLAIN select * from t1 INNER JOIN t2 ON t1.b = t2.b;

在这里插入图片描述
上面sql执行过程
t2表先执行，t1表再执行，t2是小表，t1是大表，但是他们没有索引了，没有索引做优化条件他们是怎么连接的，首先会执行t2表再磁盘扫描出100行记录，在内存中开辟出一块join_buffer的空间，把小表t2表的数据放入join_buffer中，再去扫描t1表的10000行记录，拿到内存中来，然后在内存进行比对，在join_buffer的数据是无序的，t1表一条一条的依次循环取join_buffer做比对出最终的数据，10000x100=1000000，扫描10100次，内存比对1000000次，得出最终结果。注意的是join_buffer的空间大小默认值为256k，也就是说驱动表(小表)数据量超过了256k的话，执行过程就是一次性放800条记录到join_buffer里去匹配，这样循环800行依次放行，被驱动(大表再来访问)join_buffer做对比；

BNL算法与NLJ算法的区别
有索引的情况默认选择了NLJ，没有索引选择了BNL算法，为什么要这么做，索引能够快速查找定位，这是毋庸置疑的，有索引的情况下走NLJ，没有索引为什么不走NLJ，我们举个例子还是上面的t1和t2表，假设我没有索引走NLJ算法，先在磁盘先把t2的100行记录扫描出来，去t1表里匹配(是在磁盘里匹配)，100行去t2表里匹配完就是100万次，在磁盘里面匹配了100万次，我在内存虽然也是匹配100万次，无论如何内存匹配肯定比磁盘匹配效率要高很多，这就是为什么有索引要走NLJ算法，没有索引要走BNL算法

in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据小于A表的数据时使用in，这个时候使用in会先执行B表，再用B表去匹配A表，这样就遵循了小表驱动大表

select * from A where id in (select id from B)

exists：当A表的数据大于B表时，使用exists，这样会先执行A表，再去匹配B表，遵循小表驱动大表的原则

select * from A where exists (select 1 from B where B.id = A.id)

风雨秋烟

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Mysql执行计划Explain与索引优化

explain中的列1. id列id列的编号是 select 的序列号，有几个 select 就有几个id，id的执行顺序，如果id不相同从大到小，如果相同，就从上往下，下图就是一个id不同，按照id大小来执行的2. select_type列select_type 表示对应行是简单还是复杂的查询。(1)、simple：简单查询。查询不包含子查询和unionexplain select * from film where id = 2;(2)、primary：复杂查询中最外层的 selec
复制链接

扫一扫