一、索引的數(shù)據(jù)結(jié)構(gòu) B-Tree(mysql主要使用 B-tree 平衡樹(shù))

聚簇索引與非聚簇索引

聚簇索引：索引的葉節(jié)點(diǎn)指向數(shù)據(jù)

非聚簇索引：索引的葉節(jié)點(diǎn)指向數(shù)據(jù)的引用

myisam使用非聚簇索引,innodb使用聚簇索引

對(duì)于innodb引擎：

1. 主鍵索引既存儲(chǔ)索引值,又在葉中存儲(chǔ)行數(shù)據(jù)

2. 如果沒(méi)有主鍵,則會(huì)使用 unique key 做主鍵

3. 如果沒(méi)有unique,則mysql會(huì)生成一個(gè)rowid做主鍵　　

二、索引類(lèi)型

1. 主鍵索引

primary key() 要求關(guān)鍵字不能重復(fù),也不能為null,同時(shí)增加主鍵約束

主鍵索引定義時(shí),不能命名

2. 唯一索引

unique index() 要求關(guān)鍵字不能重復(fù),同時(shí)增加唯一約束

3. 普通索引

index() 對(duì)關(guān)鍵字沒(méi)有要求

4. 全文索引

fulltext key() 關(guān)鍵字的來(lái)源不是所有字段的數(shù)據(jù),而是字段中提取的特別關(guān)鍵字

關(guān)鍵字：可以是某個(gè)字段或多個(gè)字段,多個(gè)字段稱(chēng)為復(fù)合索引

建表：creat table student( stu_id int unsigned not null auto_increment, name varchar(32) not null default '', phone char(11) not null default '', stu_code varchar(32) not null default '', stu_desc text, primary key ('stu_id'), //主鍵索引 unique index 'stu_code' ('stu_code'), //唯一索引 index 'name_phone' ('name','phone'), //普通索引,復(fù)合索引 fulltext index 'stu_desc' ('stu_desc'), //全文索引) engine=myisam charset=utf8;更新：alert table student add primary key ('stu_id'), //主鍵索引 add unique index 'stu_code' ('stu_code'), //唯一索引 add index 'name_phone' ('name','phone'), //普通索引,復(fù)合索引 add fulltext index 'stu_desc' ('stu_desc'); //全文索引刪除：alert table sutdent drop primary key, drop index 'stu_code', drop index 'name_phone', drop index 'stu_desc';

三、索引使用原則

1. 列獨(dú)立

保證索引包括的字段獨(dú)立在查詢語(yǔ)句中,不能是在表達(dá)式中

2. 左前綴

like:匹配模式左邊不能以通配符開(kāi)始,才能使用索引

注意：前綴索引在排序 order by 和分組 group by 操作的時(shí)候無(wú)法使用.

3. 復(fù)合索引由左到右生效

建立聯(lián)合索引,要同時(shí)考慮列查詢的頻率和列的區(qū)分度.

1. index(a,b,c)

or的兩邊都有存在可用的索引,該語(yǔ)句才能用索引.

4. 不要濫用索引,多余的索引會(huì)降低讀寫(xiě)性能

即使?jié)M足了上述原則,mysql還是可能會(huì)棄用索引,因?yàn)橛行┎樵兗词故褂盟饕?也會(huì)出現(xiàn)大量的隨機(jī)io,相對(duì)于從數(shù)據(jù)記錄中的順序io開(kāi)銷(xiāo)更大.

四、mysql 中能夠使用索引的典型應(yīng)用

測(cè)試庫(kù)下載地址：https://downloads.mysql.com/d...

1. 匹配全值(match the full value)

對(duì)索引中所有列都指定具體值,即是對(duì)索引中的所有列都有等值匹配的條件.

例如,租賃表 rental 中通過(guò)指定出租日期 rental_date + 庫(kù)存編號(hào) inventory_id + 客戶編號(hào) customer_id 的組合條件進(jìn)行查詢,熊執(zhí)行計(jì)劃的 key he extra 兩字段的值看到優(yōu)化器選擇了復(fù)合索引 idx_rental_date:

MySQL [sakila]> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: constpossible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id key: rental_date key_len: 10 ref: const,const,const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)

explain 輸出結(jié)果中字段 type 的值為 const,表示是常量；字段 key 的值為 rental_date, 表示優(yōu)化器選擇索引 rental_date 進(jìn)行掃描.

2. 匹配值的范圍查詢(match a range of values)

對(duì)索引的值能夠進(jìn)行范圍查找.

例如,檢索租賃表 rental 中客戶編號(hào) customer_id 在指定范圍內(nèi)的記錄：

MySQL [sakila]> explain select * from rental where customer_id >= 373 and customer_id < 400 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 718 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.05 sec)

類(lèi)型 type 為 range 說(shuō)明優(yōu)化器選擇范圍查詢,索引 key 為 idx_fk_customer_id 說(shuō)明優(yōu)化器選擇索引 idx_fk_customer_id 來(lái)加速拜訪,注意到這個(gè)列子中 extra 列為 using index codition ,表示 mysql 使用了 ICP(using index condition) 來(lái)進(jìn)一步優(yōu)化查詢.

3. 匹配最左前綴(match a leftmost prefix)

僅僅使用索引中的最左邊列進(jìn)行查詢,比如在 col1 + col2 + col3 字段上的聯(lián)合索引能夠被包括 col1、(col1 + col2)、(col1 + col2 + col3)的等值查詢利用到,可是不能夠被 col2、(col2、col3)的等值查詢利用到.

最左匹配原則可以算是 MySQL 中 B-Tree 索引使用的首要原則.

4. 僅僅對(duì)索引進(jìn)行查詢(index only query)

當(dāng)查詢的列都在索引的字段中時(shí),查詢的效率更高,所以應(yīng)該盡量避免使用 select *,需要哪些字段,就只查哪些字段.

5. 匹配列前綴(match a column prefix)

僅僅使用索引中的第一列,并且只包括索引第一列的開(kāi)頭一部分進(jìn)行查找.

例如,現(xiàn)在需要查詢出標(biāo)題 title 是以 AFRICAN 開(kāi)頭的電影信息,從執(zhí)行計(jì)劃能夠清楚看到,idx_title_desc_part 索引被利用上了：

MySQL [sakila]> create index idx_title_desc_part on film_text(title (10), description(20));Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0MySQL [sakila]> explain select title from film_text where title like 'AFRICAN%'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_text partitions: NULL type: rangepossible_keys: idx_title_desc_part,idx_title_description key: idx_title_desc_part key_len: 32 ref: NULL rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)

extra 值為 using where 表示優(yōu)化器需要通過(guò)索引回表查詢數(shù)據(jù).

6. 能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進(jìn)行范圍匹配(match one part exactly and match a range on another part)

例如,需要查詢出租日期 rental_date 為指定日期且客戶編號(hào) customer_id 為指定范圍的庫(kù)存：

MySQL [sakila]> MySQL [sakila]> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <=400\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: refpossible_keys: rental_date,idx_fk_customer_id key: rental_date key_len: 5 ref: const rows: 182 filtered: 16.85 Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)

7. 如果列名是索引,那么使用 column_name is null 就會(huì)使用索引.

例如,查詢支付表 payment 的租賃編號(hào) rental_id 字段為空的記錄就用到了索引：

MySQL [sakila]> explain select * from payment where rental_id is null \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: refpossible_keys: fk_payment_rental key: fk_payment_rental key_len: 5 ref: const rows: 5 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.00 sec)

五、存在索引但不能使用索引的典型場(chǎng)景

有些時(shí)候雖然有索引,但是并不被優(yōu)化器選擇使用,下面舉例幾個(gè)不能使用索引的場(chǎng)景.

1.以%開(kāi)頭的 like 查詢不能利用 B-Tree 索引,執(zhí)行計(jì)劃中 key 的值為 null 表示沒(méi)有使用索引

MySQL [sakila]> explain select * from actor where last_name like "%NI%"\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 200 filtered: 11.11 Extra: Using where1 row in set, 1 warning (0.00 sec)

因?yàn)?B-Tree 索引的結(jié)構(gòu),所以以%開(kāi)頭的插敘很自然就沒(méi)法利用索引了.一般推薦使用全文索引(Fulltext)來(lái)解決類(lèi)似的全文檢索的問(wèn)題.或者考慮利用 innodb 的表都是聚簇表的特點(diǎn),采取一種輕量級(jí)別的解決方式：一般情況下,索引都會(huì)比表小,掃描索引要比掃描表更快,而Innodb 表上二級(jí)索引 idx_last_name 實(shí)際上存儲(chǔ)字段 last_name 還有主鍵 actot_id,那么理想的訪問(wèn)應(yīng)該是首先掃描二級(jí)索引 idx_last_name 獲得滿足條件的last_name like '%NI%' 的主鍵 actor_id 列表,之后根據(jù)主鍵回表去檢索記錄,這樣訪問(wèn)避開(kāi)了全表掃描演員表 actor 產(chǎn)生的大量 IO 哀求.

ySQL [sakila]> explain select * from (select actor_id from actor where last_name like '%NI%') a , actor b where a.actor_id = b.actor_id \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: indexpossible_keys: PRIMARY key: idx_actor_last_name key_len: 137 ref: NULL rows: 200 filtered: 11.11 Extra: Using where; Using index*************************** 2. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 1 filtered: 100.00 Extra: NULL

從執(zhí)行計(jì)劃中能夠看出,extra 字段 using wehre；using index.理論上比全表掃描更快一下.

2. 數(shù)據(jù)類(lèi)型出現(xiàn)隱式轉(zhuǎn)換的時(shí)候也不會(huì)使用索引

當(dāng)列的類(lèi)型是字符串,那么一定記得在 where 條件中把字符常量值用引號(hào)引起來(lái),否則即便這個(gè)列上有索引,mysql 也不會(huì)用到,因?yàn)?MySQL 默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索.

例如,演員表 actor 中的姓氏字段 last_name 是字符型的,但是 sql 語(yǔ)句中的條件值 1 是一個(gè)數(shù)值型值,因此即便存在索引 idx_last_name, mysql 也不能正確的用上索引,而是繼續(xù)進(jìn)行全表掃描：

MySQL [sakila]> explain select * from actor where last_name = 1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: ALLpossible_keys: idx_actor_last_name key: NULL key_len: NULL ref: NULL rows: 200 filtered: 10.00 Extra: Using where1 row in set, 3 warnings (0.00 sec)MySQL [sakila]> explain select * from actor where last_name = '1'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: refpossible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)

3. 復(fù)合索引的情況下,假如查詢條件不包括索引列最左邊部分,即不滿足最左原則 leftmost,是不會(huì)使用復(fù)合索引的.

4. 如果 MySQL 估計(jì)使用索引比全表掃描更慢,則不使用索引.

5. 用 or 分割開(kāi)的條件,如果 or 前的條件中的列有索引,而后面的列中沒(méi)有索引,那么涉及的索引都不會(huì)被用到.

六、查看索引使用情況

如果索引正在工作, Handler_read_key 的值將很高,這個(gè)值代表了一個(gè)行被索引值讀的次數(shù),很低的值表名增加索引得到的性能改善不高,因?yàn)樗饕⒉唤?jīng)常使用.

Handler_read_rnd_next 的值高則意味著查詢運(yùn)行低效,并且應(yīng)該建立索引補(bǔ)救.這個(gè)值的含義是在數(shù)據(jù)文件中讀下一行的哀求數(shù).如果正在進(jìn)行大量的表掃描,Handler_read_rnd_next 的值較高,則通常說(shuō)明表索引不正確或?qū)懭氲牟樵儧](méi)有利用索引,具體如下.

MySQL [sakila]> show status like 'Handler_read%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 1 || Handler_read_key | 5 || Handler_read_last | 0 || Handler_read_next | 200 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+

七、使用索引的小技巧

1. 字符串字段權(quán)衡區(qū)分度與長(zhǎng)度的技巧

截取不同長(zhǎng)度,測(cè)試區(qū)分度

# 這里假設(shè)截取6個(gè)字符長(zhǎng)度計(jì)算區(qū)別度,直到區(qū)別度達(dá)到0.1,就可以把這個(gè)字段的這個(gè)長(zhǎng)度作為索引了mysql> select count(distinct left([varchar]],6))/count(*) from table;#注意：設(shè)置前綴索引時(shí)指定的長(zhǎng)度表示字節(jié)數(shù),而對(duì)于非二進(jìn)制類(lèi)型(CHAR, VARCHAR, TEXT)字段而言的字段長(zhǎng)度表示字符數(shù),所# 以,在設(shè)置前綴索引前需要把計(jì)算好的字符數(shù)轉(zhuǎn)化為字節(jié)數(shù),常用字符集與字節(jié)的關(guān)系如下：# latin 單字節(jié)：1B# GBK 雙字節(jié)：2B# UTF8 三字節(jié)：3B# UTF8mb4 四字節(jié)：4B # myisam 表的索引大小默認(rèn)為 1000字節(jié),innodb 表的索引大小默認(rèn)為 767 字節(jié),可以在配置文件中修改 innodb_large_prefix # 項(xiàng)的值增大 innodb 索引的大小,最大 3072 字節(jié).

區(qū)別度能達(dá)到0.1,就可以.

2. 左前綴不易區(qū)分的字段索引建立辦法

這樣的字段,左邊有大量重復(fù)字符,比如url字段匯總的http://

1. 倒過(guò)來(lái)存儲(chǔ)并建立索引

2. 新增偽hash字段 把字符串轉(zhuǎn)化為整型

3. 索引覆蓋

概念：如果查詢的列恰好是索引的一部分,那么查詢只需要在索引文件上進(jìn)行,不需要回行到磁盤(pán),這種查詢,速度極快,江湖人稱(chēng)——索引覆蓋

4. 延遲關(guān)聯(lián)

在根據(jù)條件查詢數(shù)據(jù)時(shí),如果查詢條件不能用的索引,可以先查出數(shù)據(jù)行的id,再根據(jù)id去取數(shù)據(jù)行.

eg.

//普通查詢 沒(méi)有用到索引select * from post where content like "%新聞%";//延遲關(guān)聯(lián)優(yōu)化后 內(nèi)層查詢走content索引,取出id,在用join查所有行select a.* from post as a inner join (select id from post where content like "%新聞%") as b on a.id=b.id;

5. 索引排序　

排序的字段上加入索引,可以提高速度.

6. 重復(fù)索引和冗余索引

重復(fù)索引：在同一列或者相同順序的幾個(gè)列建立了多個(gè)索引,成為重復(fù)索引,沒(méi)有任何意義,刪掉

冗余索引：兩個(gè)或多個(gè)索引所覆蓋的列有重疊,比如對(duì)于列m,n ,加索引index m(m),indexmn(m,n),稱(chēng)為冗余索引.

7. 索引碎片與維護(hù)

在數(shù)據(jù)表長(zhǎng)期的更改過(guò)程中,索引文件和數(shù)據(jù)文件都會(huì)產(chǎn)生空洞,形成碎片.修復(fù)表的過(guò)程十分耗費(fèi)資源,可以用比較長(zhǎng)的周期修復(fù)表.

//清理辦法alert table xxx engine innodb;//或optimize table xxx;

8. innodb引擎的索引注意事項(xiàng)

Innodb 表要盡量自己指定主鍵,如果有幾個(gè)列都是唯一的,要選擇最常作為拜訪條件的列作為主鍵,另外,Innodb 表的普通索引都會(huì)保存主鍵的鍵值,所以主鍵要盡可能選擇較短的數(shù)據(jù)類(lèi)型,可以有效的減少索引的磁盤(pán)占用,提高索引的緩存效果.

大家可以點(diǎn)擊加入群：606187239【JAVA大牛學(xué)習(xí)交流】

里面有Java高級(jí)大牛直播講解知識(shí)點(diǎn) 走的就是高端路線

(如果你想跳槽換工作 但是技術(shù)又不夠 或者工作上遇到了

瓶頸 我這里有一個(gè)JAVA的免費(fèi)直播課程 講的是高端的知識(shí)點(diǎn)

基礎(chǔ)不好的誤入喲 只要你有1-5年的開(kāi)發(fā)經(jīng)驗(yàn)

可以加群找我要課堂鏈接 注意：是免費(fèi)的 沒(méi)有開(kāi)發(fā)經(jīng)驗(yàn)誤入哦)

1、具有1-5工作經(jīng)驗(yàn)的,面對(duì)目前流行的技術(shù)不知從何下手,

需要突破技術(shù)瓶頸的可以加.2、在公司待久了,過(guò)得很安適,

但跳槽時(shí)面試碰壁.需要在短時(shí)間內(nèi)進(jìn)修、跳槽拿高薪的可以加.

3、如果沒(méi)有工作經(jīng)驗(yàn),但基礎(chǔ)非常扎實(shí),對(duì)java工作機(jī)制,

常用設(shè)計(jì)思想,常用java開(kāi)發(fā)框架掌握熟練的,可以加.

4、覺(jué)得本身很牛B,一般需求都能搞定.

但是所學(xué)的知識(shí)點(diǎn)沒(méi)有系統(tǒng)化,很難在技術(shù)領(lǐng)域繼續(xù)突破的可以加.

5. 群號(hào)：高級(jí)架構(gòu)群 606187239備注好信息!

6.阿里Java高級(jí)大牛直播講解知識(shí)點(diǎn),分享知識(shí),

多年工作經(jīng)驗(yàn)的梳理和總結(jié),帶著大家全面、

科學(xué)地建立本身的技術(shù)體系和技術(shù)認(rèn)知!