溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL中InnoDB引擎如何對(duì)索引的擴(kuò)展

發(fā)布時(shí)間：2021-10-08 16:42:48 來源：億速云 閱讀：92 作者：柒染 欄目：MySQL數(shù)據(jù)庫

MySQL中InnoDB引擎如何對(duì)索引的擴(kuò)展，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

InnoDB引擎對(duì)索引的擴(kuò)展，自動(dòng)追加主鍵值及其對(duì)執(zhí)行計(jì)劃的影響。


MySQL中，使用InnoDB引擎的每個(gè)表，創(chuàng)建的普通索引（即非主鍵索引），都會(huì)同時(shí)保存主鍵的值。
比如語句
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;
創(chuàng)建了t1表，其主鍵為(i1, i2)，同時(shí)創(chuàng)建了基于d列的索引k_d，但其實(shí)在底層，InnoDB引擎將索引k_d擴(kuò)展成（d，i1，i2）。
InnoDB引擎這么做，是用空間換性能，優(yōu)化器在判斷是否使用索引及使用哪個(gè)索引時(shí)會(huì)有更多列參考，這樣可能生成更高效的執(zhí)行計(jì)劃，獲得更好的性能。
優(yōu)化器在ref、range和index_merge類型的訪問，Loose Index Scan訪問，連接和排序優(yōu)化， MIN()/MAX()優(yōu)化時(shí)使都會(huì)使用擴(kuò)展列。
我們來看個(gè)例子：
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)
在普通索引中追加擴(kuò)展主鍵是InnoDB在底層做的，show index等語句不顯示追加列，但我們可以通過其它方式來驗(yàn)證?？催@個(gè)SQL
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’
如果InnoDB沒有擴(kuò)展索引，索引k_d為（d），生成的執(zhí)行計(jì)劃應(yīng)該類似這樣，使用k_d索引找到d為’2000-01-01’的5行數(shù)據(jù)，再回表過濾出i1為3的，最后計(jì)算count?；蛘呤褂弥麈I索引找到i1為3的5行數(shù)據(jù)，再回表過濾出d為’2000-01-01’的，最后計(jì)算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擴(kuò)展了索引，索引k_d為（d，i1，i2），這時(shí)，優(yōu)化器可以使用最左邊的索引前綴（d，i1），生成的執(zhí)行計(jì)劃應(yīng)該類似這樣，使用k_d索引找到d為’2000-01-01’及i1為3的1行數(shù)據(jù)，然后計(jì)算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個(gè)字節(jié)，i1是INT類型占4個(gè)字節(jié)，所以查詢中使用的鍵值長度就是8個(gè)字節(jié)（key_len: 8）。
我們看看實(shí)際生成的執(zhí)行計(jì)劃
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)
果然跟我們的判斷一致，注意執(zhí)行計(jì)劃中的細(xì)節(jié)：

  • key_len從4字節(jié)變?yōu)?字節(jié)，表明鍵查找使用列d和i1，而不僅僅是d。

  • ref從const更改為const，const，表明查找使用兩個(gè)鍵值，而不是一個(gè)。

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

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


InnoDB引擎底層擴(kuò)展普通索引的情況，也可以通過跟MyISAM引擎對(duì)比來進(jìn)行旁證：
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)
可以看到，同樣的結(jié)構(gòu)同樣的數(shù)據(jù)，因?yàn)镸yISAM引擎不會(huì)在底層自動(dòng)擴(kuò)展普通索引，所以執(zhí)行計(jì)劃還是通過主鍵索引進(jìn)行處理。
按照官方手冊(cè)的說明，也可以用SHOW STATUS命令來驗(yàn)證
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表示在進(jìn)行索引掃描時(shí),按照索引從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)。使用MyISAM引擎的t1myisam表，Handler_read_next值為5，使用InnoDB引擎的t1表，Handler_read_next值減小到1，就是因?yàn)镮nnoDB引擎對(duì)索引進(jìn)行了主鍵擴(kuò)展，讀取的次數(shù)少，效率更好。
默認(rèn)情況下，優(yōu)化器分析InnoDB表的索引時(shí)會(huì)考慮擴(kuò)展列，但如果因?yàn)樘厥庠蜃寖?yōu)化器不考慮擴(kuò)展列，可以使用SET optimizer_switch = 'use_index_extensions=off’設(shè)置。
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)

看完上述內(nèi)容，你們掌握MySQL中InnoDB引擎如何對(duì)索引的擴(kuò)展的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手