索引擴展,InnoDB通過將主鍵列附加到每個輔助索引中來自動擴展該索引。創建如下表結構:
mysql> CREATE TABLE t1 (
-> i1 INT NOT NULL DEFAULT 0,
-> i2 INT NOT NULL DEFAULT 0,
-> d DATE DEFAULT NULL,
-> PRIMARY KEY (i1, i2),
-> INDEX k_d (d)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)
表t1在列(i1,i2)上定義了主鍵。同時也在列(d)上定義了一個輔助索引，但InnoDB擴展了這個索引并且將它視為(d,i1,i2)來處理。
在決定如何使用以及是否使用該索引時，優化器會考慮擴展輔助索引的主鍵列。這可以產生更高效的查詢執行計劃和更好的性能。
優化器可以使用擴展的二級索引來進行ref、range和index_merge索引訪問，進行松散索引掃描，進行連接和排序優化，以及進行MIN()/MAX()優化。
下面的示例將顯示優化器是否使用擴展輔助索引來影響執行計劃 向表t1插入以下數據:
mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'), (2, 4, '2001-01-01'),
->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
->(5, 5, '2002-01-01');
Query OK, 25 rows affected (0.05 sec)
Records: 25 Duplicates: 0 Warnings: 0
假設執行下面的查詢:
SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;
在這種情況下，優化器不能使用主鍵，因為主鍵包含列(i1、i2)，并且查詢沒有引用i2。相反，優化器可以使用列(d)上的輔助索引k_d，執行計劃取決于是否使用擴展索引。
當優化器不考慮索引擴展時，它將索引k_d僅視為(d)
mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: PRIMARY,k_d
key: PRIMARY
key_len: 4
ref: const
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
當優化器考慮到索引擴展時，它將k_d視為(d, i1, i2)。在這種情況下，它可以使用最左邊的索引前綴(d, i1)來生成更好的執行計劃
mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
在這兩種情況下，key表示優化器將使用輔助索引k_d，但是EXPLAIN輸出顯示了使用擴展索引所帶來的這些改進:
.key_len從4字節變成了8字節，指示鍵查找使用了列d和i1，不僅僅是d。
.ref的值從const變成了const,const,因為鍵查找使用兩個鍵的列而不是一個。
.rows:從5減到1，指示InnoDB將會檢查更少的行來生成查詢結果。
.Extra值從Using where;Using index變成了Using index。這意味著查詢記錄只需要使用索引而不用查詢數據行記錄。
可以使用show status來查看優化器在使用與不使用擴展索引時的差異:
mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> flush status;
Query OK, 0 rows affected (0.03 sec)
上面的flush table和flush status語句用來清除表的緩存和清除狀數據統計數據。
不使用索引擴展時show status產生的結果如下:
mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 5 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
使用索引擴展時，show status產生的結果如下，其中handler_read_next的值從5減到1，指示使用這個索引更有效率:
mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> flush status
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.01 sec)
系統變量optimizer_switch的use_index_extensions標志允許優化器在決定如何使用InnoDB表的輔助索引時使不使用主鍵列。默認情況下，use_index_extensions是啟用的。為了檢查禁用索引擴展是否可以提高性能可以執行以下語句:
mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)
以上就是詳解MySQL InnoDB的索引擴展的詳細內容，更多關于MySQL 索引擴展的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- mysqli擴展無法在PHP7下升級問題的解決
- PHP使用PDO、mysqli擴展實現與數據庫交互操作詳解
- php使用mysqli和pdo擴展，測試對比連接mysql數據庫的效率完整示例
- PHP基于PDO擴展操作mysql數據庫示例
- php安裝擴展mysqli的實現步驟及報錯解決辦法
- PHP實現基于面向對象的mysqli擴展庫增刪改查操作工具類
- CentOS 7下部署php7.1和開啟MySQL擴展的方法教程
- PHP使用SWOOLE擴展實現定時同步 MySQL 數據
- PHP使用mysqli擴展連接MySQL數據庫
- MySQL 可擴展設計的基本原則
