mysql table combine_Mysql系列-性能优化神器EXPLAIN使用介绍及分析
MySQL 提供了一個 EXPLAIN 命令, 它可以對?SELECT?語句進行分析, 并輸出?SELECT?執(zhí)行的詳細信息, 以供開發(fā)人員針對性優(yōu)化。
EXPLAIN 命令用法十分簡單, 在 SELECT 語句前加上 Explain 就可以了, 例如:
EXPLAIN SELECT * from user_info WHERE id < 300;
下面是我結合我自己創(chuàng)建的表以及執(zhí)行相關sql語句總結的相關知識點。
準備
為了接下來方便演示 EXPLAIN 的使用, 首先我們需要建立兩個測試用的表, 并添加相應的數(shù)據(jù):
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;
DROP TABLE IF EXISTS`employees`;CREATE TABLE`employees` (
`employeeNumber`int(11) NOT NULL,
`lastName`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`firstName`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`extension`varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`email`varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`officeCode`varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`reportsTo`int(11) NULL DEFAULT NULL,
`jobTitle`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY(`employeeNumber`) USING BTREE,INDEX`reportsTo`(`reportsTo`) USING BTREE,INDEX`officeCode`(`officeCode`) USING BTREE
) ENGINE= InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
創(chuàng)建表成功后，插入一些測試數(shù)據(jù)。
EXPLAIN 輸出格式
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)
各列的含義如下:
id: SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符
select_type: SELECT 查詢的類型
table: 查詢的是哪個表
partitions: 匹配的分區(qū)
type: join 類型
possible_keys: 此次查詢中可能選用的索引
key: 此次查詢中確切使用到的索引
ken_len:?表示查詢優(yōu)化器使用了索引的字節(jié)數(shù)
ref: 哪個字段或常數(shù)與 key 一起被使用
rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值.
filtered: 表示此查詢條件所過濾的數(shù)據(jù)的百分比
extra: 額外的信息
接下來我們詳細看一下每個字段的具體含義：
select_type
select_type?表示了查詢的類型, 它的常用取值有:
SIMPLE：表示此查詢不包含 UNION 查詢或子查詢
PRIMARY：表示此查詢是最外層的查詢
UNION：表示此查詢是 UNION 的第二或隨后的查詢
DEPENDENT UNION：UNION 中的第二個或后面的查詢語句, 取決于外面的查詢
UNION RESULT：UNION 的結果
SUBQUERY：子查詢中的第一個 SELECT
DEPENDENT SUBQUERY：子查詢中的第一個 SELECT, 取決于外面的查詢. 即子查詢依賴于外層查詢的結果
DERIVED：當子查詢是from子句時，其select_type為DERIVED
最常見的查詢類別應該是?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 | | 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 | | 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.00sec)
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 | | 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)
table
表示查詢涉及的表或衍生表
partitions
匹配的分區(qū)
type
type字段比較重要, 顯示連接使用了何種類型。從最好到最差的連接類型依次分別為const、eq_reg、ref、range、index和ALL它提供了判斷查詢是否高效的重要依據(jù)依據(jù)。
通過?type字段, 我們判斷此次查詢是?全表掃描?還是?索引掃描?等。
type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref。
type常用的取值有:
system: 表中只有一條數(shù)據(jù). 這個類型是特殊的?const類型。
const: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù)。const 查詢速度非常快, 因為它僅僅讀取一次即可。
表示通過索引一次就找到了，const用于比較primary key 或者 unique索引。因為只需匹配一行數(shù)據(jù)，所有很快。如果將主鍵置于where列表中，mysql就能將該查詢轉換為一個const 。
例如下面的這個查詢, 它使用了主鍵索引, 因此?type就是?const類型的
mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber=128;+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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 row in set, 1 warning (0.00 sec)
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵 或 唯一索引掃描。
mysql> SELECT * FROMitems;+----+---------+
| id | item_no |
+----+---------+
| 1 | A-1 |
| 2 | A-2 |
| 3 | A-3 |
| 4 | A-4 |
| 5 | A-5 |
| 6 | A-10 |
| 7 | A-11 |
| 8 | A-20 |
| 9 | A-30 |
+----+---------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM customers LIMIT 10;+----------------+------------------------------+-----------------+------------------+-------------------+------------------------------+--------------+---------------+----------+------------+-----------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+-------------------+------------------------------+--------------+---------------+----------+------------+-----------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
| 114 | Australian Collectors, Co. | Ferguson | Peter | 03 9520 4555 | 636 St Kilda Road | Level 3 | Melbourne | Victoria | 3004 | Australia | 1611 | 117300.00 |
| 119 | La Rochelle Gifts | Labrune | Janine | 40.67.8555 | 67, rue des Cinquante Otages | NULL | Nantes | NULL | 44000 | France | 1370 | 118200.00 |
| 121 | Baane Mini Imports | Bergulfsen | Jonas | 07-98 9555 | Erling Skakkes gate 78 | NULL | Stavern | NULL | 4110 | Norway | 1504 | 81700.00 |
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.00 |
| 125 | Havel & Zbyszek Co | Piestrzeniewicz | Zbyszek | (26) 642-7555 | ul. Filtrowa 68 | NULL | Warszawa | NULL | 01-012 | Poland | NULL | 0.00 |
| 128 | Blauer See Auto, Co. | Keitel | Roland | +49 69 66 90 2555 | Lyonerstr. 34 | NULL | Frankfurt | NULL | 60528 | Germany | 1504 | 59700.00 |
| 129 | Mini Wheels Co. | Murphy | Julie | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | 1165 | 64600.00 |
| 131 | Land of Toys Inc. | Lee | Kwai | 2125557818 | 897 Long Airport Avenue | NULL | NYC | NY | 10022 | USA | 1323 | 114900.00 |
+----------------+------------------------------+-----------------+------------------+-------------------+------------------------------+--------------+---------------+----------+------------+-----------+------------------------+-------------+
10 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM items,customers WHERE customers.customerNumber=items.id;+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | items | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9 | 100.00 | NULL |
| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | yiibaidb.items.id | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
注意：ALL全表掃描的表是記錄最少的表如items表。
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質是也是一種索引訪問，它返回所有匹配某個單獨值的行，然而他可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體 。
mysql> SELECT * FROM customers WHERE salesRepEmployeeNumber=1165;+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.00 |
| 129 | Mini Wheels Co. | Murphy | Julie | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | 1165 | 64600.00 |
| 161 | Technics Stores Inc. | Hashimoto | Juri | 6505556809 | 9408 Furth Circle | NULL | Burlingame | CA | 94217 | USA | 1165 | 84600.00 |
| 321 | Corporate Gift Ideas Co. | Brown | Julie | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | 1165 | 105000.00 |
| 450 | The Sharp Gifts Warehouse | Frick | Sue | 4085553659 | 3086 Ingle Ln. | NULL | San Jose | CA | 94217 | USA | 1165 | 77600.00 |
| 487 | Signal Collectibles Ltd. | Taylor | Sue | 4155554312 | 2793 Furth Circle | NULL | Brisbane | CA | 94217 | USA | 1165 | 60300.00 |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
6 rows in set (0.00sec)
mysql> EXPLAIN SELECT * FROM customers WHERE salesRepEmployeeNumber=1165;+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customers | NULL | ref | salesRepEmployeeNumber| salesRepEmployeeNumber | 5 | const | 6 | 100.00 | NULL |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show index fromcustomers;+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers | 0 | PRIMARY | 1 | customerNumber | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | salesRepEmployeeNumber | 1 | salesRepEmployeeNumber| A | 16 | NULL | NULL | YES | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 1 | customerName | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 2 | phone | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 3 | customerNumber | A | 122 | NULL | NULL | | BTREE | | |
+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現(xiàn)了bettween、、in等的查詢。這種索引列上的范圍掃描比全索引掃描要好。只需要開始于某個點，結束于另一個點，不用掃描全部索引 。
mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber BETWEEN 1 AND 120;+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常為ALL快，因為索引文件通常比數(shù)據(jù)文件小。(Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取) 。
mysql> EXPLAIN SELECT id FROMitems;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | items | NULL | index| NULL | PRIMARY | 4 | NULL | 9 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00sec)
mysql> show index fromitems;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| items | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
ALL：Full Table Scan，遍歷全表以找到匹配的行 。
mysql> EXPLAIN SELECT item_no FROMitems;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | items | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00sec)
mysql> show index fromitems;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| items | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
possible_keys
查詢涉及到的字段上存在索引，則該索引將被列出，但不一定被查詢實際使用。
key
實際使用的索引，如果為NULL，則沒有使用索引。
查詢中如果使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中。
mysql> show index fromcustomers;+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers | 0 | PRIMARY | 1 | customerNumber | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | salesRepEmployeeNumber | 1 | salesRepEmployeeNumber | A | 16 | NULL | NULL | YES | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 1 | customerName | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 2 | phone | A | 122 | NULL | NULL | | BTREE | | |
| customers | 1 | customers_idx_combine_1 | 3 | customerNumber | A | 122 | NULL | NULL | | BTREE | | |
+-----------+------------+-------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00sec)
mysql> EXPLAIN SELECT customerName,phone,customerNumber fromcustomers;+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | index | NULL | customers_idx_combine_1 | 308 | NULL | 122 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
上述customerName,phone,customerNumber與customers_idx_combine_1索引順序一致，為覆蓋索引
mysql> EXPLAIN SELECT phone,customerName,customerNumber fromcustomers;+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | index | NULL | customers_idx_combine_1 | 308 | NULL | 122 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT customerName,phone,customerNumber,state fromcustomers;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | customers | NULL |ALL | NULL | NULL | NULL | NULL | 122 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
state字段為非索引列。
key_len
表示索引中使用的字節(jié)數(shù)，查詢中使用的索引的長度(最大可能長度)，并非實際使用長度，理論上長度越短越好。key_len是根據(jù)表定義計算而得的，不是通過表內檢索出的。
ref
顯示索引的那一列被使用了，如果可能，是一個常量const。
mysql> EXPLAIN SELECT customerNumber FROM customers WHERE customerNumber=114;+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 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 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
rows
根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)。
Extra
不適合在其他字段中顯示，但是十分重要的額外信息。
Using filesort?:mysql對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內的索引進行排序讀取。也就是說mysql無法利用索引完成的排序操作成為“文件排序” 。
總結
以上是生活随笔為你收集整理的mysql table combine_Mysql系列-性能优化神器EXPLAIN使用介绍及分析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql悲观锁只用于读取吗_MySQL
- 下一篇： mysql 索引分析工具_Mysql:性