詳解MySQL InnoDB的索引擴(kuò)展
索引擴(kuò)展,InnoDB通過(guò)將主鍵列附加到每個(gè)輔助索引中來(lái)自動(dòng)擴(kuò)展該索引。創(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)上定義了主鍵。同時(shí)也在列(d)上定義了一個(gè)輔助索引，但I(xiàn)nnoDB擴(kuò)展了這個(gè)索引并且將它視為(d,i1,i2)來(lái)處理。
在決定如何使用以及是否使用該索引時(shí)，優(yōu)化器會(huì)考慮擴(kuò)展輔助索引的主鍵列。這可以產(chǎn)生更高效的查詢執(zhí)行計(jì)劃和更好的性能。
優(yōu)化器可以使用擴(kuò)展的二級(jí)索引來(lái)進(jìn)行ref、range和index_merge索引訪問(wèn)，進(jìn)行松散索引掃描，進(jìn)行連接和排序優(yōu)化，以及進(jìn)行MIN()/MAX()優(yōu)化。
下面的示例將顯示優(yōu)化器是否使用擴(kuò)展輔助索引來(lái)影響執(zhí)行計(jì)劃 向表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)化器不能使用主鍵，因?yàn)橹麈I包含列(i1、i2)，并且查詢沒有引用i2。相反，優(yōu)化器可以使用列(d)上的輔助索引k_d，執(zhí)行計(jì)劃取決于是否使用擴(kuò)展索引。
當(dāng)優(yōu)化器不考慮索引擴(kuò)展時(shí)，它將索引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)
當(dāng)優(yōu)化器考慮到索引擴(kuò)展時(shí)，它將k_d視為(d, i1, i2)。在這種情況下，它可以使用最左邊的索引前綴(d, i1)來(lái)生成更好的執(zhí)行計(jì)劃
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輸出顯示了使用擴(kuò)展索引所帶來(lái)的這些改進(jìn):
.key_len從4字節(jié)變成了8字節(jié)，指示鍵查找使用了列d和i1，不僅僅是d。
.ref的值從const變成了const,const,因?yàn)殒I查找使用兩個(gè)鍵的列而不是一個(gè)。
.rows:從5減到1，指示InnoDB將會(huì)檢查更少的行來(lái)生成查詢結(jié)果。
.Extra值從Using where;Using index變成了Using index。這意味著查詢記錄只需要使用索引而不用查詢數(shù)據(jù)行記錄。
可以使用show status來(lái)查看優(yōu)化器在使用與不使用擴(kuò)展索引時(shí)的差異:
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語(yǔ)句用來(lái)清除表的緩存和清除狀數(shù)據(jù)統(tǒng)計(jì)數(shù)據(jù)。
不使用索引擴(kuò)展時(shí)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)
使用索引擴(kuò)展時(shí)，show status產(chǎn)生的結(jié)果如下，其中handler_read_next的值從5減到1，指示使用這個(gè)索引更有效率:
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標(biāo)志允許優(yōu)化器在決定如何使用InnoDB表的輔助索引時(shí)使不使用主鍵列。默認(rèn)情況下，use_index_extensions是啟用的。為了檢查禁用索引擴(kuò)展是否可以提高性能可以執(zhí)行以下語(yǔ)句:
mysql> SET optimizer_switch = 'use_index_extensions=off'; Query OK, 0 rows affected (0.01 sec)
以上就是詳解MySQL InnoDB的索引擴(kuò)展的詳細(xì)內(nèi)容，更多關(guān)于MySQL 索引擴(kuò)展的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
如何選擇合適的MySQL日期時(shí)間類型來(lái)存儲(chǔ)你的時(shí)間
這篇文章主要介紹了如何選擇合適的MySQL日期時(shí)間類型來(lái)存儲(chǔ)你的時(shí)間，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2020-08-08
MySQL日志系統(tǒng)中最重要的日志為重做日志redo log和歸檔日志bin log，后者為MySQL Server層的日志，前者為InnoDB存儲(chǔ)引擎層的日志。今天通過(guò)本文給大家介紹MySQL中的redo log和undo log日志，感興趣的朋友一起看看吧2021-07-07
我們經(jīng)常在運(yùn)行MySQL時(shí)會(huì)出一些錯(cuò)誤，也經(jīng)常被這些錯(cuò)誤搞得暈頭轉(zhuǎn)向。當(dāng)然解決這些問(wèn)題的首要任務(wù)是找到日志信息。2010-07-07
詳解CentOS 6.5中安裝mysql 5.7.16 linux glibc2.5 x86 64(推薦)
這篇文章主要介紹了CentOS 6.5中安裝mysql 5.7.16 linux glibc2.5 x86 64(推薦)的相關(guān)資料,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-12-12
在高并發(fā)的場(chǎng)景下,大量的請(qǐng)求直接訪問(wèn)Mysql很容易造成性能問(wèn)題,下面這篇文章主要給大家介紹了關(guān)于MySQL如何保證備份數(shù)據(jù)一致性的相關(guān)資料,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-05-05
mysql 數(shù)據(jù)庫(kù)備份和還原方法集錦 推薦
本文討論 MySQL 的備份和恢復(fù)機(jī)制，以及如何維護(hù)數(shù)據(jù)表，包括最主要的兩種表類型：MyISAM 和 Innodb，文中設(shè)計(jì)的 MySQL 版本為 5.0.22。2010-03-03
mysql中update和select結(jié)合使用方式
這篇文章主要介紹了mysql中update和select結(jié)合使用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
禁止mysql做域名解析(解決遠(yuǎn)程訪問(wèn)mysql時(shí)很慢)
當(dāng)遠(yuǎn)程訪問(wèn)mysql時(shí)，mysql會(huì)解析域名，會(huì)導(dǎo)致訪問(wèn)速度很慢2010-04-04
mysql密碼正確無(wú)法登陸(host的問(wèn)題)
本文主要介紹了mysql密碼正確無(wú)法登陸(host的問(wèn)題)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
本文主要介紹了Mysql?DATETIME?毫秒坑的解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-01-01