
大家好!本次繼續帶來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://www.sunline.cc/db/336054
關于列的直方圖的信息 可以參考之前的文章: http://www.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 執行計劃入門理解 上篇和下篇全部介紹完畢了。 開發和運維的同學,覺得有感興趣的話,可以轉發給你們周圍的小伙伴!




