大家好！本次繼續帶來MYSQL之執行計劃的入門理解–下篇：

大表哥會帶來執行計劃中剩下的幾個指標理解： possible_keys，key，key_len，ref，rows，filtered，Extra

1）possible_keys 和 key

possible_keys： 表示索引訪問表的所有可能性，這個一般出現的越多，代表可能索引的索引就越多，就有可能存在重復索引的情況。
負面影響有2點：
1）增加了DML的索引維護，降低了該表的DML的效率
2）迷惑了優化器（特別是在統計信息不準的情況下）可能會選擇不好的索引作為執行計劃

key: 如果是索引訪問表的話，最終在possible_keys中選擇的一個索引名字

我們來看一個重復索引的例子： 很明顯 索引 idx_name 和 idx_name_age 是重復的關系。（索引idx_name_age 包含了 idx_name ）

mysql> create table t4 (id int not null primary key, name varchar(20),age int);
Query OK, 0 rows affected (0.01 sec)


mysql> alter table t4 add index idx_name (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table t4 add index idx_name_age (name,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

我們看一下 簡單的 select 查詢的執行計劃： 這里的possible_keys 是idx_name,idx_name_age ，實際優化器選擇的索引是idx_name

mysql> explain select * from t4 where name = 'BB';
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t4    | NULL       | ref  | idx_name,idx_name_age | idx_name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

那么如何查詢數據庫重復的索引呢？ 目前市面上有3種方式：
利用第三方工具：
1)pt: pt-duplicate-key-checker
2)mysql untility: mysqlindexcheck
（上面這2中工具的方法使用都十分簡單，大表哥就不展開介紹了，感興趣的同學可以自行查閱）

3）或者查詢sys數據庫下面的試圖 sys.schema_redundant_indexes

我們可以看到
貼心的mysql大叔建議我們 使用語句 sql_drop_index: ALTER TABLE testdb.t4 DROP INDEX idx_name 來刪除這個重復的索引。

mysql> select * from sys.schema_redundant_indexes where table_name = 't4'\G
*************************** 1. row ***************************
              table_schema: testdb
                table_name: t4
      redundant_index_name: idx_name
   redundant_index_columns: name
redundant_index_non_unique: 1
       dominant_index_name: idx_name_age
    dominant_index_columns: name,age
dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `testdb`.`t4` DROP INDEX `idx_name`
1 row in set (0.00 sec)

2）我們再來看一下 key_len 這個屬性

key_len 代表了索引的長度： 我們看 idx_name 這個索引的長度為83。

mysql> explain select * from t4 where name = 'BB';
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t4    | NULL       | ref  | idx_name,idx_name_age | idx_name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

這個83是如何計算的呢？ 我們知道 idx_name 只有單鍵值是 name varchar(20) , 每個字符根據數據庫編碼不一樣，像是utf8mb3是占3個字節，
像是utf8mb4是占4個字節，為了支持一些EMJO的表情符號。 大表哥的這個表就是 utf8mb4 的。

所以目前 是 每個字符占有4個字節 * 20的長度 = 80 目前這個索引的len 是80

并且還可以看到這個字段是允許為空的 ， 這個默認是 len +1 ，
并且name這個字段是變長字段 ， 這個默認是 len +2

綜上所述： 20 * 4 + 1 + 2 = 83 與執行計劃中的 key len 的長度是相符合的

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_0900_as_cs DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs
1 row in set (0.00 sec)

我們可以以此類推 看一下了另外一個索引的長度：idx_name_age

name: 20* 4 + 2 +1 = 83
age: (int 默認占4個字節 ) 4 + (可以為NULL) 1 = 5

name+age = 83+ 5 =88

mysql> explain select * from t4 use index(idx_name_age) where name = 'BB' and age =1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | ref  | idx_name_age  | idx_name_age | 88      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3）我們再來看一下 ref 這個屬性

執行計劃中ref這個屬性就是表示 where 后面所觸發的連接條件： 可以是表與表的連接，也可以是表與常量值的連接。

我們舉例來看一下： where name = ‘BB’ and age =1; 其中 ‘BB’ 和 1 都是常量，索引對應的ref是 const,const

mysql> explain select * from t4 use index(idx_name_age) where name = 'BB' and age =1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | ref  | idx_name_age  | idx_name_age | 88      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

我們再來看一下： 表與表的連接： 我們可以看到 ref 是 testdb.a.id

mysql> explain select * from t4 a, t4 b where a.id = b.id;
+----+-------------+-------+------------+--------+---------------+--------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+--------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index  | PRIMARY       | idx_name_age | 88      | NULL        |    1 |   100.00 | Using index |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY      | 4       | testdb.a.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+--------------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

也可以是函數的連接條件：ref 是 func

mysql> explain select * from t4 a, t4 b where a.id = lower(b.id);
+----+-------------+-------+------------+--------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | index  | NULL          | idx_name_age | 88      | NULL |    1 |   100.00 | Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY      | 4       | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+--------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

4）我們再來看一下 rows 和 filtered 這個屬性

rows ： 這個表示通過全表或者索引掃描的預估的行數
filtered: 表示某列通過條件過濾的后所能篩選的比率。

值得注意的是，這2個屬性的值都是預估的值，精確程度取決于表的統計信息和列上直方圖的采樣信息。

關于表和索引的統計信息 可以參考之前的文章： http://m.sunline.cc/db/336054

關于列的直方圖的信息 可以參考之前的文章： http://m.sunline.cc/db/336688

5）我們最后再來看一下 Extra 這個屬性

Extra列表示的是補充的額外的一些關于這個SQL執行計劃的一些信息。 這個Extra 的內容有好幾十種。

我們就來簡單的看一下，常見的一些Extra。

No tables used： 沒有實際的去訪問任何一張表

mysql>  explain select  now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Impossible WHERE： 1 = 2 是個偽命題， mysql大叔的優化器會直接 : say No!!

mysql> explain select * from t4 where 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

Using index: 覆蓋索引， 不回表，mysql 大叔喜歡的類型

mysql> explain select name from t4 where name = 'B';
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | ref  | idx_name,idx_name_age | idx_name | 83      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using index condition： 索引下推， MYSQL大叔優化器的宗旨是 能在innoDB 存儲引擎層解決問題的，就不會放到mysql server 層來解決。

我們看一下這個例子： where name like ‘jason%’ and age = 2;

索引 key(name,age) 這個中 name like ‘jason%’ 不是一個等值的查詢，是一個邊界前閉后開的掃描， 這個時候mysql 大叔的優化器 不會把 name like 'jason%'掃描到所有數據拿到
server 層與 進行 與 age = 2的過濾
而是 在innoDB存儲引擎 的存儲引擎層直接進行 age = 2的過濾， 再把最終的結果返回給 server 層， 這個優化就叫做 索引下推

mysql> create table tab1 (id int not null primary key, name varchar(20),age int, job varchar(20), key(name,age));
Query OK, 0 rows affected (0.02 sec)


mysql> insert into tab1  (id,name,age,job) values (1,'jason',20,'dev');
Query OK, 1 row affected (0.00 sec)


mysql> insert into tab1  (id,name,age,job) values (2,'jason',30,'dba');
Query OK, 1 row affected (0.01 sec)


mysql> insert into tab1  (id,name,age,job) values (3,'jason',40,'pm');
Query OK, 1 row affected (0.00 sec)


mysql> explain select * from tab1 where name like 'jason%' and age = 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tab1  | NULL       | range | name          | name | 88      | NULL |    3 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

我們也可以通過優化器的參數來打開和關閉這個 索引下推的功能
mysql 默認是 index_condition_pushdown=on

mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)




mysql> desc select * from tab1 where name like 'jason%' and age = 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tab1  | NULL       | range | name          | name | 88      | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

using where: 表示SQL 語句需要進行 server 層的過濾

where 條件中 ：job 列上不存在索引

mysql> explain select * from tab1 where name = 'jason' and job = 'dev';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tab1  | NULL       | ref  | name          | name | 83      | const |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec

Using filesort： 需要在server層進行內存或者文件排序的SQL

MYSQL大叔的優化器 不是很喜歡這個，應該在表設計中規避

mysql> explain select * from tab1 order by job desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | tab1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Using temporary： 需要在server層進行分組group by ,去重distinct 等操作的SQL。

MYSQL大叔的優化器 不是很喜歡這個，應該在表設計中規避

mysql> explain select job,count(1) from tab1 group by job;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | tab1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

最后到這里, mysql 執行計劃入門理解 上篇和下篇全部介紹完畢了。 開發和運維的同學，覺得有感興趣的話，可以轉發給你們周圍的小伙伴！