MySQL优化（1）执行计划explain中type属性详解

1.初始化测试数据

1.初始化表格（user表）

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(18) NOT NULL AUTO_INCREMENT,
  `product_no` varchar(30) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_no_index` (`product_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

2.初始化表格（product表）

DROP TABLE IF EXISTS `xxx_audit_order`;
CREATE TABLE `xxx_audit_order` (
  `product_no` varchar(30) NOT NULL,
  `xxx_channel_code` int(30) DEFAULT NULL,
  `business_no` varchar(30) DEFAULT NULL,
  `xxx_product_id` int(30) DEFAULT NULL,
  `xxx_product_name` varchar(30) DEFAULT NULL,
  `xxx_audit_no` varchar(30) DEFAULT NULL,
  `xxx_audit_status` int(30) DEFAULT NULL,
  `inspection_report_no` varchar(30) DEFAULT NULL,
  `audit_report_no` varchar(30) DEFAULT NULL,
  `re_audit_count` int(30) DEFAULT NULL,
  `inspector_id` int(30) DEFAULT NULL,
  `remark` varchar(30) DEFAULT NULL,
  `delete_dt` int(30) DEFAULT NULL,
  `create_by` int(18) DEFAULT NULL,
  `create_dt` int(18) DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL,
  `update_dt` datetime DEFAULT NULL,
  `xxx_link` varchar(30) DEFAULT NULL,
  `service_standard_no` varchar(30) DEFAULT NULL,
  `depth_inspection` int(30) DEFAULT NULL,
  `execute_channel` int(30) DEFAULT NULL,
  `seller_type` int(30) DEFAULT NULL,
  PRIMARY KEY (`product_no`),
  KEY `xxx_audit_no_index` (`xxx_audit_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.初始化表格（user表数据初始化）

INSERT INTO `test`.`user` (`id`, `product_no`, `name`) VALUES ('1', 'C0512201907191455553490', '王乐');
INSERT INTO `test`.`user` (`id`, `product_no`, `name`) VALUES ('12', NULL, 'wqe');

4.初始化表格（product表函数）

新建函数生成100万条数据，便于测试查询性能。

DROP PROCEDURE if EXISTS BatchInsert;
delimiter $$
CREATE PROCEDURE BatchInsert(IN initId INT, IN loop_counts INT)
BEGIN
    DECLARE Var INT;
    DECLARE ID INT;
    SET Var = 0;
    SET ID = initId;
    set autocommit=0; -- 关闭自动提交事务，提高插入效率
    WHILE Var < loop_counts DO
        INSERT INTO `xxx_audit_order` (`product_no`,`xxx_channel_code`,`business_no`,`xxx_product_id`,`xxx_product_name`,`xxx_audit_no`,`xxx_audit_status`,`inspection_report_no`,`audit_report_no`,`re_audit_count`,`inspector_id`,`remark`,`delete_dt`,`create_by`,`create_dt`,`update_by`,`update_dt`,`xxx_link`,`service_standard_no`,`depth_inspection`,`execute_channel`,`seller_type`) 
        VALUES (CONCAT('20220704', 100000000000 + ID),106,'RS20190719143225916727',26958,'荣耀 Play',CONCAT('C0', 512201907191454553491 + ID),FLOOR(RAND()*10) % 4,'R1152109544189558784','R1152216911870734336',2,0,null,0,6532,UNIX_TIMESTAMP() + ID ,0,Now(),FLOOR(RAND()*10) % 3,'',0,1,null);
        SET ID = ID + 1;
        SET Var = Var + 1;
    END WHILE;
    COMMIT;
END$$;

delimiter ;  -- 界定符复原为默认的分号

5.初始化表格（product表数据初始化）

CALL BatchInsert(1, 1000000);

2.连接类型

当使用explain执行计划的时候，会生成类似查询结果的数据。其中的type列尤为重要，在MySQL官网中称之为join type，连接类型。表示查询语句在MySQL引擎中查询的扫描类型，如全表扫描等，是sql优化的一个重要指标。

1.all

表示全表扫描，表示查询语句在查询数据的时候会将一个表里面的全部数据都轮询一遍，然后取出所需要的数据，也就是sql语句其实只是起到一个筛选的作用。在我们需要查询一张表里面的全部的数据的时候这样做是可以的，但在做除此之外的所有的查询的时候全表扫描的可优化场景是相当之多的。

EXPLAIN SELECT business_no from xxx_audit_order WHERE xxx_audit_no = "20220704100000000009"

2.index

只是另外一种类型的全表扫描。这种查询在大多数情况下效率比全表扫描低，因为走完索引之后然后会回表查询。但是在排序的情况下，index可能比all搞笑，但前提是排序是根据表主键排序。因为按照索引扫描全表的数据是有序的
如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了。

-- 排序字段为非主键字段
SELECT * from xxx_audit_order ORDER BY xxx_audit_no

-- 排序字段为主键字段
SELECT * from xxx_audit_order ORDER BY product_no

3.range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>‘,’<'外，in和or也是索引范围扫描

4.ref

查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

5.ref_eq

使用了主键或者唯一性索引进行查找的情况。一般来说，ref_eq类型多半会表示为const，这个不重要

6.const

主键放置到where后面作为条件查询。效率最高