「Mysql索引原理（七）」覆蓋索引

? ?通常大家都會(huì)根據(jù)查詢的WHERE條件來創(chuàng)建合適的索引，不過這只是索引優(yōu)化的一個(gè)方面。設(shè)計(jì)優(yōu)秀的索引應(yīng)該考慮到整個(gè)查詢，而不單單是WHERE條件部分。索引確實(shí)是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要再回到表中查詢呢？ 如果一個(gè)索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡(jiǎn)單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名申請(qǐng)、虛擬空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、隆堯網(wǎng)站維護(hù)、網(wǎng)站推廣。

覆蓋索引是非常有用的工具，能夠極大地提高性能：

? ?在所有這些場(chǎng)景中，在索引中滿足查詢的成本一般比查詢行要小得多。

? ?不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引都不存儲(chǔ)索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

? ?當(dāng)發(fā)起一個(gè)唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

? ?索引覆蓋查詢還有很多陷阱可能會(huì)導(dǎo)致無法實(shí)現(xiàn)優(yōu)化。MySQL查詢優(yōu)化器會(huì)在執(zhí)行查詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋。假設(shè)索引覆蓋了wehre條件中的字段，但不是整個(gè)查詢涉及的字段。mysql5.5和更早的版本也總是會(huì)回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會(huì)被過濾掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

這里索引無法覆蓋該查詢，有兩個(gè)原因：

這條語句只檢索1行，而之前的 like '%Kim%'要檢索3行。

也有辦法解決上面所說的兩個(gè)問題，需要重寫查詢并巧妙設(shè)計(jì)索引。

? ?這種方式叫做延遲關(guān)聯(lián)，因?yàn)檠舆t了對(duì)列的訪問。在查詢第一個(gè)階段MySQL可以使用覆蓋索引，因?yàn)樗饕酥麈Iid的值，不需要做二次查找。

? ?在FROM子句的子查詢中找到匹配的id，然后根據(jù)這些id值在外層查詢匹配獲取需要的所有列值。雖然無法使用索引覆蓋整個(gè)查詢，但總算比完全無法利用索引覆蓋的好吧。

數(shù)據(jù)量大了怎么辦？

? ?這樣優(yōu)化的效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個(gè)people表有100萬行，我們看一下上面兩個(gè)查詢?cè)谌齻€(gè)不同的數(shù)據(jù)集上的表現(xiàn)，每個(gè)數(shù)據(jù)集都包含100萬行。

實(shí)例1中 ，查詢返回了一個(gè)很大的結(jié)果集，因此看不到優(yōu)化的效果。大部分時(shí)間都花在讀取和發(fā)送數(shù)據(jù)上了。

實(shí)例2中 ，經(jīng)過索引過濾，尤其是第二個(gè)條件過濾后只返回了很少的結(jié)果集，優(yōu)化的效果非常明顯：在這個(gè)數(shù)據(jù)及上性能提高了很多，優(yōu)化后的查詢效率主要得益于只需讀取40行完整數(shù)據(jù)行，而不是原查詢中需要的30000行。

實(shí)例3中 ，子查詢效率反而下降。因?yàn)樗饕^濾時(shí)符合第一個(gè)條件的結(jié)果集已經(jīng)很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

? ?在大多數(shù)存儲(chǔ)引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進(jìn)一步優(yōu)化InnoDB?；叵胍幌拢琁nnoDB的二級(jí)索引的葉子節(jié)點(diǎn)都包含了主鍵的值，這意味著InnoDB的二級(jí)索引可以有效地利用這些額外的主鍵列來覆蓋查詢。

? ?例如，people表中l(wèi)ast_name字段有一個(gè)二級(jí)索引，雖然該索引的列不包括主鍵id，但也能夠用于對(duì)id做覆蓋查詢：

select id,last_name from people where last_name='hua'

mysql索引怎么學(xué)

索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)?？梢缘玫剿饕谋举|(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。

可以理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”

在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù),

這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

mysql如何建立索引

我們可以通過查看索引的屬性來判斷創(chuàng)建索引的方法。

查看索引的語法格式如下：

SHOW INDEX FROM 表名 [ FROM 數(shù)據(jù)庫(kù)名]

語法說明如下：

表名：指定需要查看索引的數(shù)據(jù)表名。

數(shù)據(jù)庫(kù)名：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)，可省略。比如，SHOW INDEX FROM student FROM test; 語句表示查看 test 數(shù)據(jù)庫(kù)中 student 數(shù)據(jù)表的索引。

示例

使用 SHOW INDEX 語句查看《MySQL創(chuàng)建索引》一節(jié)中 tb_stu_info2 數(shù)據(jù)表的索引信息，SQL 語句和運(yùn)行結(jié)果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要參數(shù)說明如下：

參數(shù) 說明

Table 表示創(chuàng)建索引的數(shù)據(jù)表名，這里是 tb_stu_info2 數(shù)據(jù)表。

Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。

Key_name 表示索引的名稱。

Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。

Column_name 表示定義索引的列字段。

Collation 表示列以何種順序存儲(chǔ)在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無分類。

Cardinality 索引中唯一值數(shù)目的估計(jì)值。基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒有必要是精確的。基數(shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL 使用該索引的機(jī)會(huì)就越大。

Sub_part 表示列中被編入索引的字符的數(shù)量。若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；若整列被編入索引，則該列的值為 NULL。

Packed 指示關(guān)鍵字如何被壓縮。若沒有被壓縮，值為 NULL。

Null 用于顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒有，則該列的值為 NO。

Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 顯示評(píng)注。

網(wǎng)頁(yè)名稱：mysql索引怎么學(xué),MySQL索引的使用
文章起源：http://chinadenli.net/article8/hegcop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、商城網(wǎng)站、、網(wǎng)站設(shè)計(jì)、搜索引擎優(yōu)化、營(yíng)銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)