主頁 > 知識庫 > 詳解MySQL InnoDB的索引擴展

詳解MySQL InnoDB的索引擴展

熱門標簽:許昌外呼增值業(yè)務(wù)線路 申請400電話電話價格 廣東400企業(yè)電話申請流程 新鄉(xiāng)智能外呼系統(tǒng)好處 臨沂做地圖標注 宜賓全自動外呼系統(tǒng)廠家 石家莊400電話辦理公司 地圖標注客戶付款 咸陽防封電銷卡

索引擴展,InnoDB通過將主鍵列附加到每個輔助索引中來自動擴展該索引。創(chuàng)建如下表結(jié)構(gòu):

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)來處理。

在決定如何使用以及是否使用該索引時,優(yōu)化器會考慮擴展輔助索引的主鍵列。這可以產(chǎn)生更高效的查詢執(zhí)行計劃和更好的性能。

優(yōu)化器可以使用擴展的二級索引來進行ref、range和index_merge索引訪問,進行松散索引掃描,進行連接和排序優(yōu)化,以及進行MIN()/MAX()優(yōu)化。

下面的示例將顯示優(yōu)化器是否使用擴展輔助索引來影響執(zhí)行計劃 向表t1插入以下數(shù)據(jù):

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

假設(shè)執(zhí)行下面的查詢:

SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;

在這種情況下,優(yōu)化器不能使用主鍵,因為主鍵包含列(i1、i2),并且查詢沒有引用i2。相反,優(yōu)化器可以使用列(d)上的輔助索引k_d,執(zhí)行計劃取決于是否使用擴展索引。

當優(yōu)化器不考慮索引擴展時,它將索引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)

當優(yōu)化器考慮到索引擴展時,它將k_d視為(d, i1, i2)。在這種情況下,它可以使用最左邊的索引前綴(d, i1)來生成更好的執(zhí)行計劃

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表示優(yōu)化器將使用輔助索引k_d,但是EXPLAIN輸出顯示了使用擴展索引所帶來的這些改進:

.key_len從4字節(jié)變成了8字節(jié),指示鍵查找使用了列d和i1,不僅僅是d。

.ref的值從const變成了const,const,因為鍵查找使用兩個鍵的列而不是一個。

.rows:從5減到1,指示InnoDB將會檢查更少的行來生成查詢結(jié)果。

.Extra值從Using where;Using index變成了Using index。這意味著查詢記錄只需要使用索引而不用查詢數(shù)據(jù)行記錄。

可以使用show status來查看優(yōu)化器在使用與不使用擴展索引時的差異:

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語句用來清除表的緩存和清除狀數(shù)據(jù)統(tǒng)計數(shù)據(jù)。

不使用索引擴展時show status產(chǎn)生的結(jié)果如下:

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產(chǎn)生的結(jié)果如下,其中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)

系統(tǒng)變量optimizer_switch的use_index_extensions標志允許優(yōu)化器在決定如何使用InnoDB表的輔助索引時使不使用主鍵列。默認情況下,use_index_extensions是啟用的。為了檢查禁用索引擴展是否可以提高性能可以執(zhí)行以下語句:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

以上就是詳解MySQL InnoDB的索引擴展的詳細內(nèi)容,更多關(guān)于MySQL 索引擴展的資料請關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • mysqli擴展無法在PHP7下升級問題的解決
  • PHP使用PDO、mysqli擴展實現(xiàn)與數(shù)據(jù)庫交互操作詳解
  • php使用mysqli和pdo擴展,測試對比連接mysql數(shù)據(jù)庫的效率完整示例
  • PHP基于PDO擴展操作mysql數(shù)據(jù)庫示例
  • php安裝擴展mysqli的實現(xiàn)步驟及報錯解決辦法
  • PHP實現(xiàn)基于面向?qū)ο蟮膍ysqli擴展庫增刪改查操作工具類
  • CentOS 7下部署php7.1和開啟MySQL擴展的方法教程
  • PHP使用SWOOLE擴展實現(xiàn)定時同步 MySQL 數(shù)據(jù)
  • PHP使用mysqli擴展連接MySQL數(shù)據(jù)庫
  • MySQL 可擴展設(shè)計的基本原則

標簽:日照 鎮(zhèn)江 鷹潭 臺灣 合肥 阜新 貴州 北京

巨人網(wǎng)絡(luò)通訊聲明:本文標題《詳解MySQL InnoDB的索引擴展》,本文關(guān)鍵詞  詳解,MySQL,InnoDB,的,索引,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《詳解MySQL InnoDB的索引擴展》相關(guān)的同類信息!
  • 本頁收集關(guān)于詳解MySQL InnoDB的索引擴展的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章