簡(jiǎn)介

Mysql 官方定義 : 索引(Index) 是幫助 Mysql 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

索引的目的在于提交查詢(xún)效率，可以類(lèi)比字典。簡(jiǎn)單理解為 “排好序的快讀查找數(shù)據(jù)結(jié)構(gòu)”。

數(shù)據(jù)本身之外，數(shù)據(jù)庫(kù)還維護(hù)著一個(gè)滿(mǎn)足特定查找算法的數(shù)據(jù)結(jié)構(gòu)哦，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)就是索引。

一般來(lái)說(shuō)，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在在盤(pán)上。

優(yōu)劣

優(yōu)勢(shì)：

提高了數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫(kù)的 IO 成本

通過(guò)索引對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了 CPU 的消耗

劣勢(shì)：

實(shí)際上索引也是一張表，該表保存了主鍵和索引字段，并指向?qū)嶓w表的記錄，所以索引列也要占用空間

索引大大提高了查詢(xún)速度，同時(shí)會(huì)降低更新表的速度。因?yàn)楦卤頃r(shí)不僅要保存數(shù)據(jù)，還要保存索引文件每次更新添加了索引列的字段。

索引分類(lèi)

單值索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單值索引

唯一索引：索引列的值必須唯一，但允許有空值

復(fù)合索引：一個(gè)索引包含單個(gè)列

基本語(yǔ)法

# 創(chuàng)建 如果是 CHAR，VARCHAR 類(lèi)型，length可以小于字段實(shí)際長(zhǎng)度，如果是 BLOB和TEXT類(lèi)型，必須指定 length

CREATE [UNIQUE] INDEX index_name ON table_name(columnname(length));

# 或

ALTER table_name ADD [UNIQUE] INDEX [index_name] ON (column(length))

#刪除

DROP INDEX [index_name] ON table_name

#查看

SHOW INDEX FREOM table_name\G

索引建立情況

主鍵自動(dòng)建立唯一索引

頻繁作為查詢(xún)條件的字段應(yīng)該創(chuàng)建索引

查詢(xún)中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系應(yīng)該創(chuàng)建索引

頻更新的字段不適合做索引

Where 條件中用不到的字段不適合創(chuàng)建索引

單鍵/組合鍵索引的選擇問(wèn)題(高并發(fā)下傾向創(chuàng)建組合索引)

查詢(xún)中排序的字段，排序字段若通過(guò)索引去訪問(wèn)將大大提高排序速度

查詢(xún)中統(tǒng)計(jì)或者分組的字段

不創(chuàng)建索引情況

表記錄太少

經(jīng)常增刪改的表

數(shù)據(jù)重復(fù)且分布平均的表字段，應(yīng)該只為最經(jīng)常查詢(xún)和排序的字段建立索引

Explain

查看執(zhí)行計(jì)劃： explain + SQL

表的讀取速度

數(shù)據(jù)讀取操作的操作類(lèi)型

那些索引可以使用

那些索引被實(shí)際使用

表之間的引用

每張表有多少行被優(yōu)化器查詢(xún)

id

id ： select 查詢(xún)的序列號(hào)，包含一組數(shù)字，表示查詢(xún)中執(zhí)行 select 子句或操作表的順序

id 相同 ： 執(zhí)行順序由上至下

id不同 ：如果是子查詢(xún)，id 的序號(hào)會(huì)遞增，id 值越大優(yōu)先級(jí)越高，越先被執(zhí)行

id 有相同又有不同的 ，同時(shí)存在

select_type

查詢(xún)類(lèi)型

含義

SIMPLE

簡(jiǎn)單的 select 查詢(xún)，不包含子查詢(xún)或者 UNION

PRIMARY

查詢(xún)中包含任何復(fù)雜的子部分，最外層查詢(xún)則被標(biāo)準(zhǔn)為 PRIMARY

SUBQUERY

在 SELECT 或 WHERE 列表中包含了子查詢(xún)

DERIVED

在 FROM 列表中包含的子查詢(xún)被標(biāo)記為 DERIVED(衍生) ,MYSQL 會(huì)遞歸執(zhí)行這些子查詢(xún)，把結(jié)果放在臨時(shí)表里

UNION

若第二個(gè) SELECT 出現(xiàn)在 UNION 之后，則被標(biāo)記為 UNION；若 UNION 包含在 FROM 子句的子查詢(xún)中，外層 SELECT將被標(biāo)記為：DERIVED

UNION RESULT

從 UNION 表獲取結(jié)果的 SELECT

table

顯示這一行數(shù)據(jù)是關(guān)于哪張表

type

type: 顯示查詢(xún)使用了哪種查詢(xún)類(lèi)型。常見(jiàn)查詢(xún)類(lèi)型如下：

ALL

index

range

ref

eq_ref

const, system

NULL

從最好到最差依次是：

system > const > eq_ref > ref > range > index > ALL

訪問(wèn)類(lèi)型

含義

system

表只有一行記錄(等于系統(tǒng)表)，這是 const 類(lèi)型的特例，平時(shí)不會(huì)出現(xiàn)，可以忽略不計(jì)

const

表示通過(guò)索引一次就找到了， const 用于比較 primary或者 unique索引。因?yàn)橹黄ヅ涞揭恍袛?shù)據(jù)，所以很快。如將主鍵置于 where 列表中， Mysql 就能將該查詢(xún)轉(zhuǎn)換為一個(gè)常量

eq_ref

唯一性索引掃描，對(duì)于每個(gè)索引建，表中只有一行數(shù)據(jù)與之匹配。常見(jiàn)于主鍵或唯一性索引掃描

ref

非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有航，本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行。然而，它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該是屬于查找和掃描的混合體

range

只檢索非定范圍的行，使用一個(gè)索引來(lái)選擇行。key 列顯示使用了那個(gè)索引。 一般就是在你的 where 語(yǔ)句中出現(xiàn)了 between、、in 等的查詢(xún)。這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某一點(diǎn)，而結(jié)束語(yǔ)另一點(diǎn)，不用掃描全部索引

index

Full Index Scan，index 與 ALL 區(qū)別為 index 類(lèi)型只遍歷索引樹(shù)。這通常比 ALL 快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。(雖然 all 和 index 都是讀全表，但 index是從索引中讀出，而 all 是從硬盤(pán)中讀的)

all

Full Table Scan，將遍歷全表以找到匹配的行

possible_keys

顯示可能應(yīng)用到這張表中的索引，一個(gè)或多個(gè)。查詢(xún)涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢(xún)實(shí)際使用。

key

實(shí)際使用的索引。如果為 NULL，則沒(méi)有使用索引。

查詢(xún)中若使用了覆蓋索引，則該索引僅出現(xiàn)在 key 列表中。

key_len

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢(xún)中使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好。 key_len 顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際長(zhǎng)度，即 key_len 是根據(jù)表定義計(jì)算而得，并不是通過(guò)表內(nèi)檢索出的。

ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)。哪些列或常量被用于查找索引列上的值

row

根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需記錄所需要讀取的行數(shù)

extra

不適合在其他列中顯示但十分重要的額外信息

信息

含義

Using filesort

說(shuō)明 mysql 會(huì)對(duì)數(shù)據(jù)使用一個(gè)額外的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。Mysql 中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”。

Usering temporary

使用了臨時(shí)表保存中間結(jié)果，Mysql 在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表，常見(jiàn)于排序 order by 和分組查詢(xún) group by

Using index

表示相應(yīng)的 select 操作中使用了覆蓋索引，避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)。如果同時(shí)出現(xiàn) using where ，表明索引被用來(lái)執(zhí)行索引鍵值的查找。如果沒(méi)有同時(shí)出現(xiàn) using where，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。Covering Index 覆蓋索引。理解方式一 ： select 的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行， Mysql 可以利用索引返回 select 列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說(shuō)查詢(xún)列要被所建的索引覆蓋。理解方式二 ： 索引是高校找到行的一個(gè)方法，但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù)。因此它不必讀取整個(gè)行，畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了他們索引的數(shù)據(jù)；當(dāng)能通過(guò)都區(qū)索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含(或覆蓋)了滿(mǎn)足查詢(xún)結(jié)果的數(shù)據(jù)就叫做覆蓋索引 。

using where

表示使用了 where 過(guò)濾

using join buffer

使用了連接緩存

impossible where

where 子句的值總是 false，不能用來(lái)獲取任何元組

select tables optimized away

在沒(méi)有 group by 子句的情況下，基于索引優(yōu)化 MIN/MAX 操作或者對(duì)于 MyISAM 存儲(chǔ)引擎優(yōu)化 COUNT(*) 操作，不必等到執(zhí)行階段在進(jìn)行計(jì)算，查詢(xún)執(zhí)行計(jì)劃生成的階段即完成優(yōu)化

distinct

優(yōu)化 distinct 操作，在中到第一匹配的元組后即停止找同樣值的動(dòng)作

使用情況

觀察，至少跑一天，看看生產(chǎn)的慢 SQL 情況

開(kāi)啟慢查詢(xún)?nèi)罩?#xff0c;設(shè)置閾值，比如超過(guò) 5 秒的就是慢 SQL，并把他們抓取出來(lái)

explain + 慢 SQL 分析

show profile ： 查詢(xún) SQL 在 Mysql 服務(wù)器里面的執(zhí)行細(xì)節(jié)和生命周期情況

運(yùn)維經(jīng)理或DBA，進(jìn)行 SQL 數(shù)據(jù)庫(kù)服務(wù)器參數(shù)調(diào)優(yōu)

總結(jié)

以上是生活随笔為你收集整理的mysql 索引生命周期_MYSQL 索引（一）--- 简介的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。