mysql explain type 常见类型

mysql explain type 常见类型

常见的值有8种

性能从最好到最差：null > system > const > eq_ref > ref > range > index > all

场景举例

select version()

mysql版本：5.7.28-log

创建表：公司表、部门表、用户表

DROP TABLE IF EXISTS `company`;
CREATE TABLE `company`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `province` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `county` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `compnay_code_wysy`(`code`) USING BTREE,
  INDEX `company_status`(`status`) USING BTREE,
  INDEX `company_province_city`(`province`, `city`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

用存储过程初始化数据

DELIMITER $
DROP PROCEDURE
IF
	EXISTS insert01;
CREATE PROCEDURE insert01 ( ) BEGIN
DECLARE
	i INT;
TRUNCATE TABLE company;
TRUNCATE TABLE person;
TRUNCATE TABLE department;

SET i = 1000;
WHILE
	i <= 9999 DO
INSERT INTO `my_explain`.`company` ( `id`, `name`, `code`, `status`, `province`, `city`, `county` )
VALUES
	(
		LPAD( i, 4, 0 ),
		CONCAT( LPAD( i, 4, 0 ), '公司' ),
		LPAD( i, 4, 0 ),
		LPAD( i, 4, 0 ),
		LPAD( i, 4, 0 ),
		LPAD( i, 4, 0 ),
		LPAD( i, 4, 0 ) 
	);
INSERT INTO `my_explain`.`person` ( `id`, `name`, `company_id`, `company_code`, `status` )
VALUES
	(
		LPAD( i, 4, 0 ),
		CONCAT( LPAD( i, 4, 0 ), 'name' ),
		LPAD( MOD ( i, 100 ) + 1000, 4, 0 ),
		LPAD( MOD ( i, 100 ) + 1000, 4, 0 ),
		LPAD( i, 4, 0 ) 
	);
INSERT INTO `my_explain`.`department` ( `id`, `name`, `company_id`, `company_code`, `status` )
VALUES
	(
		LPAD( i, 4, 0 ),
		CONCAT( LPAD( i, 4, 0 ), '部门' ),
		LPAD( MOD ( i, 100 ) + 1000, 4, 0 ),
		LPAD( MOD ( i, 100 ) + 1000, 4, 0 ),
		LPAD( i, 4, 0 ) 
	);

SET i = i + 1;

END WHILE;

END $DELIMITER;

1、const

-- const：code字段具有唯一索引，可以为null。也可以使用主键
EXPLAIN SELECT
	* 
FROM
	company c 
WHERE
	c.`code` = '1000'

• code字段具有唯一索引，可以为null。也可以使用主键

2、eq_ref

-- eq_ref：多表查询，code字段具有唯一索引，且不能为null。也可以使用主键
EXPLAIN SELECT
	* 
FROM
	person p,
	company c 
WHERE
	p.`company_code` = c.`code`

• 多表查询，code字段具有唯一索引，且不能为null。也可以使用主键

3、ref

-- ref：单表或多表，status字段具有普通索引
EXPLAIN SELECT
	* 
FROM
	person p,
	company c 
WHERE
	p.`status` = c.`status`
	
EXPLAIN SELECT
	* 
FROM
	company c 
WHERE
	c.`status` = '1000'

• 单表或多表，status字段具有普通索引

4、range

-- range：有索引的字段使用 in、> 、< 等，但并非一定是range类型，如果扫描的行数接近全表，也会使用 ALL 类型
EXPLAIN SELECT
	* 
FROM
	company c 
WHERE
	c.`id` IN ( '1000', '1001', '1002' )
	
EXPLAIN SELECT
	* 
FROM
	company c 
WHERE
	c.`code` > '9000'

• 有索引的字段使用 in、> 、< 等，但并非一定是range类型，如果扫描的行数接近全表，也会使用 ALL 类型

5、index

-- index：查询的所有字段在某个索引内
EXPLAIN SELECT
	province,
	city 
FROM
	company c 
WHERE
	city > '9000'

• 查询的所有字段在某个索引内