MySQL中InnoDB引擎對索引的擴充套件

資料和雲發表於2020-03-24
MySql索引套件

原文連結：  


摘要： InnoDB引擎對索引的擴充套件，自動追加主鍵值及其對執行計劃的影響。


MySQL中，使用InnoDB引擎的每個表，建立的普通索引（即非主鍵索引），都會同時儲存主鍵的值。
比如語句




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;

建立了t1表，其主鍵為(i1, i2)，同時建立了基於d列的索引k_d，但其實在底層，InnoDB引擎將索引k_d擴充套件成（d，i1，i2）。
InnoDB引擎這麼做，是用空間換效能，最佳化器在判斷是否使用索引及使用哪個索引時會有更多列參考，這樣可能生成更高效的執行計劃，獲得更好的效能。
最佳化器在ref、range和index_merge型別的訪問，Loose Index Scan訪問，連線和排序最佳化， MIN()/MAX()最佳化時使都會使用擴充套件列。
我們來看個例子：



root@database-one 15:15:  [gftest]> 
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.06 sec)



root@database-one 15:15:  [gftest]> 
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.01 sec)

Records: 25  Duplicates: 0  Warnings: 0



root@database-one 15:21:  [gftest]> 
show 
index 
from t1;

+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment | Index_comment |

+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t1    |          0 | PRIMARY  |            1 | i1          | A         |           5 |     
NULL | 
NULL   |      | BTREE      |         |               |

| t1    |          0 | PRIMARY  |            2 | i2          | A         |          25 |     
NULL | 
NULL   |      | BTREE      |         |               |

| t1    |          1 | k_d      |            1 | d           | A         |           5 |     
NULL | 
NULL   | YES  | BTREE      |         |               |

+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 
rows 
in 
set (0.01 sec)

在普通索引中追加擴充套件主鍵是InnoDB在底層做的，show index等語句不顯示追加列，但我們可以透過其它方式來驗證。看這個SQL

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’

如果InnoDB沒有擴充套件索引，索引k_d為（d），生成的執行計劃應該類似這樣，使用k_d索引找到d為’2000-01-01’的5行資料，再回表過濾出i1為3的，最後計算count。或者使用主鍵索引找到i1為3的5行資料，再回表過濾出d為’2000-01-01’的，最後計算count。下面僅示意走k_d索引的情況：



mysql> 
EXPLAIN 
SELECT 
COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G

*************************** 1. 
row ***************************

           
id: 1

  select_type: SIMPLE

        
table: t1

         
type: 
ref

possible_keys: PRIMARY,k_d

          
key: k_d

      key_len: 4

          
ref: const

         
rows: 5

        Extra: 
Using 
where; Using index

如果InnoDB擴充套件了索引，索引k_d為（d，i1，i2），這時，最佳化器可以使用最左邊的索引字首（d，i1），生成的執行計劃應該類似這樣，使用k_d索引找到d為’2000-01-01’及i1為3的1行資料，然後計算count



mysql> 
EXPLAIN 
SELECT 
COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G

*************************** 1. 
row ***************************

           
id: 1

  select_type: SIMPLE

        
table: t1

         
type: 
ref

possible_keys: PRIMARY,k_d

          
key: k_d

      key_len: 8

          
ref: const,const

         
rows: 1

        Extra: 
Using 
index

並且d列是DATE型別佔4個位元組，i1是INT型別佔4個位元組，所以查詢中使用的鍵值長度就是8個位元組（key_len: 8）。
我們看看實際生成的執行計劃



root@database-one 15:35:  [gftest]> 
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.01 sec)

果然跟我們的判斷一致，注意執行計劃中的細節：

  • key_len從4位元組變為8位元組，表明鍵查詢使用列d和i1，而不僅僅是d。

  • ref從const更改為const，const，表明查詢使用兩個鍵值，而不是一個。

  • rows從5減少到1，表明檢索更少的行。

  • Extra從Using where; Using index改為Using index，表示只用索引讀取，不必回表。


InnoDB引擎底層擴充套件普通索引的情況，也可以透過跟MyISAM引擎對比來進行旁證：



root@database-one 16:07:  [gftest]> 
CREATE 
TABLE t1MyISAM (

    ->   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 = MyISAM;

Query OK, 0 rows affected (0.01 sec)



root@database-one 16:07:  [gftest]> 
INSERT 
INTO t1myisam 
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.02 sec)

Records: 25  Duplicates: 0  Warnings: 0



root@database-one 16:07:  [gftest]> 
EXPLAIN 
SELECT 
COUNT(*) 
FROM t1myisam 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G

*************************** 1. 
row ***************************

           
id: 1

  select_type: SIMPLE

        
table: t1myisam

   
partitions: 
NULL

         
type: 
ref

possible_keys: PRIMARY,k_d

          
key: PRIMARY

      key_len: 4

          
ref: const

         
rows: 4

     filtered: 16.00

        Extra: 
Using 
where

1 
row 
in 
set, 1 
warning (0.01 sec)

可以看到，同樣的結構同樣的資料，因為MyISAM引擎不會在底層自動擴充套件普通索引，所以執行計劃還是透過主鍵索引進行處理。
按照官方手冊的說明，也可以用SHOW STATUS命令來驗證



root@database-one 16:12:  [gftest]> 
FLUSH 
TABLE t1;

Query OK, 0 rows affected (0.00 sec)



root@database-one 16:12:  [gftest]> 
FLUSH 
STATUS;

Query OK, 0 rows affected (0.14 sec)



root@database-one 16:12:  [gftest]> 
SELECT 
COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01';

+
----------+

| COUNT(*) |

+
----------+

|        1 |

+
----------+

1 row in 
set (0.03 sec)



root@
database-one 16:12:  [gftest]> 
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)



root@
database-one 16:13:  [gftest]> 
FLUSH 
TABLE t1myisam;

Query OK, 0 rows affected (0.01 sec)



root@database-one 16:13:  [gftest]> 
FLUSH 
STATUS;

Query OK, 0 rows affected (0.00 sec)



root@database-one 16:13:  [gftest]> 
SELECT 
COUNT(*) 
FROM t1myisam 
WHERE i1 = 3 
AND d = 
'2000-01-01';

+
----------+

| COUNT(*) |

+
----------+

|        1 |

+
----------+

1 row in 
set (0.01 sec)



root@
database-one 16:13:  [gftest]> 
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)

Handler_read_next表示在進行索引掃描時,按照索引從資料檔案裡取資料的次數。使用MyISAM引擎的t1myisam表，Handler_read_next值為5，使用InnoDB引擎的t1表，Handler_read_next值減小到1，就是因為InnoDB引擎對索引進行了主鍵擴充套件，讀取的次數少，效率更好。
預設情況下，最佳化器分析InnoDB表的索引時會考慮擴充套件列，但如果因為特殊原因讓最佳化器不考慮擴充套件列，可以使用SET optimizer_switch = 'use_index_extensions=off’設定。



root@database-one 16:26:  [gftest]> 
SET optimizer_switch = 
'use_index_extensions=off';

Query OK, 0 rows affected (0.01 sec)



root@database-one 16:26:  [gftest]> 
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.02 sec)


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31556440/viewspace-2682185/，如需轉載，請註明出處，否則將追究法律責任。

相關文章