时间:2021-07-01 10:21:17 帮助过:14人阅读
下面是我结合我自己创建的表以及执行相关sql语句总结的相关知识点。
为了接下来方便演示 EXPLAIN 的使用, 首先我们需要建立两个测试用的表, 并添加相应的数据:
DROP TABLE IF EXISTS `customers`; CREATE TABLE `customers` ( `customerNumber` int(11) NOT NULL, `customerName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `contactLastName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `contactFirstName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `addressLine1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `addressLine2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `state` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `postalCode` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `country` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `salesRepEmployeeNumber` int(11) NULL DEFAULT NULL, `creditLimit` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`customerNumber`) USING BTREE, INDEX `salesRepEmployeeNumber`(`salesRepEmployeeNumber`) USING BTREE, INDEX `customers_idx_combine_1`(`customerName`, `phone`, `customerNumber`) USING BTREE, CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
创建表成功后,插入一些测试数据。
EXPLAIN 命令的输出内容大致如下:
mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167; +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | customers | NULL | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
各列的含义如下:
接下来我们详细看一下每个字段的具体含义:
select_type
表示了查询的类型, 它的常用取值有:
SIMPLE:表示此查询不包含 UNION 查询或子查询
PRIMARY:表示此查询是最外层的查询
UNION:表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION:UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个 SELECT
DEPENDENT SUBQUERY:子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
最常见的查询类别应该是 SIMPLE
了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE
类型, 例如:
1.SIMPLE
情况:
mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167; +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | customers | NULL | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
2.UNION情况
当通过union来连接多个查询结果时,第二个之后的select其select_type为UNION
mysql> EXPLAIN SELECT customerNumber FROM customers WHERE customerNumber IN (125,144) UNION SELECT customerNumber FROM customers WHERE country IN (‘USA‘,‘France‘); +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | PRIMARY | customers | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using index | | 2 | UNION | customers | NULL | ALL | NULL | NULL | NULL | NULL | 122 | 20.00 | Using where | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.00 sec)
3.DEPENDENT UNION与DEPENDENT SUBQUERY
当union作为子查询时,其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY
mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber IN (SELECT customerNumber FROM customers WHERE customerNumber IN (125,144) UNION SELECT customerNumber FROM customers WHERE country IN (‘USA‘,‘France‘)); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | PRIMARY | customers | NULL | ALL | NULL | NULL | NULL | NULL | 122 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 20.00 | Using where | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.00 sec)
4.SUBQUERY
子查询中的第一个select其select_type为SUBQUERY
mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber=(SELECT customerNumber FROM customers WHERE customerNumber=124); +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | customers | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | customers | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber in (SELECT customerNumber FROM customers WHERE customerNumber=124); +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | customers | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | customers | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
思考下为什么一个用了in一个用了=反而select_type就不一样了????
5.DERIVED
mysql> EXPLAIN SELECT * FROM (SELECT COUNT(*) FROM customers WHERE customerNumber=124) a; +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | customers | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
表示查询涉及的表或衍生表
匹配的分区
type
字段比较重要, 显示连接使用了何种类型。从最好到最差的连接类型依次分别为const、eq_reg、ref、range、index和ALL 它提供了判断查询是否高效的重要依据依据。
通过 type
字段, 我们判断此次查询是 全表扫描
还是 索引扫描
等。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
type 常用的取值有:
system
: 表中只有一条数据. 这个类型是特殊的 const
类型
const
: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可。
例如下面的这个查询, 它使用了主键索引, 因此 type
就是 const
类型的
mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167; +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | customers | NULL | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
Mysql系列-性能优化神器EXPLAIN使用介绍及分析
标签:使用介绍 成功 命令 xpl ddr reference cte state 接下来