Mysql索引優化

一、索引的數據結構 B-Tree（mysql主要使用 B-tree 平衡樹）

聚簇索引與非聚簇索引

聚簇索引：索引的葉節點指向數據

非聚簇索引：索引的葉節點指向數據的引用

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

對於innodb引擎：

1. 主鍵索引既存儲索引值，又在葉中存儲行數據

2. 如果沒有主鍵，則會使用 unique key 做主鍵

3. 如果沒有unique，則mysql會生成一個rowid做主鍵　　

二、索引類型

1. 主鍵索引

primary key() 要求關鍵字不能重複，也不能為null,同時增加主鍵約束

主鍵索引定義時，不能命名

2. 唯一索引

unique index() 要求關鍵字不能重複，同時增加唯一約束

3. 普通索引

index() 對關鍵字沒有要求

4. 全文索引

fulltext key() 關鍵字的來源不是所有字段的數據，而是字段中提取的特別關鍵字

關鍵字：可以是某個字段或多個字段，多個字段稱為複合索引

建表：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'), //普通索引，複合索引 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'), //普通索引，複合索引 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. 列獨立

保證索引包含的字段獨立在查詢語句中，不能是在表達式中

2. 左前綴

like:匹配模式左邊不能以通配符開始，才能使用索引

注意：前綴索引在排序 order by 和分組 group by 操作的時候無法使用。

3. 複合索引由左到右生效

建立聯合索引，要同時考慮列查詢的頻率和列的區分度。

1. index(a,b,c)

or的兩邊都有存在可用的索引，該語句才能用索引。

4. 不要濫用索引，多餘的索引會降低讀寫性能

即使滿足了上述原則，mysql還是可能會棄用索引，因為有些查詢即使使用索引，也會出現大量的隨機io，相對於從數據記錄中的順序io開銷更大。

四、mysql 中能夠使用索引的典型應用

測試庫下載地址：https://downloads.mysql.com/d...

1. 匹配全值（match the full value）

對索引中所有列都指定具體值，即是對索引中的所有列都有等值匹配的條件。

例如，租賃表 rental 中通過指定出租日期 rental_date + 庫存編號 inventory_id + 客戶編號 customer_id 的組合條件進行查詢，熊執行計劃的 key he extra 兩字段的值看到優化器選擇了複合索引 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 輸出結果中字段 type 的值為 const，表示是常量；字段 key 的值為 rental_date, 表示優化器選擇索引 rental_date 進行掃描。

2. 匹配值的範圍查詢（match a range of values）

對索引的值能夠進行範圍查找。

例如，檢索租賃表 rental 中客戶編號 customer_id 在指定範圍內的記錄：

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)

類型 type 為 range 說明優化器選擇範圍查詢，索引 key 為 idx_fk_customer_id 說明優化器選擇索引 idx_fk_customer_id 來加速訪問，注意到這個列子中 extra 列為 using index codition ,表示 mysql 使用了 ICP（using index condition） 來進一步優化查詢。

3. 匹配最左前綴（match a leftmost prefix）

僅僅使用索引中的最左邊列進行查詢，比如在 col1 + col2 + col3 字段上的聯合索引能夠被包含 col1、(col1 + col2)、（col1 + col2 + col3）的等值查詢利用到，可是不能夠被 col2、（col2、col3）的等值查詢利用到。

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

4. 僅僅對索引進行查詢（index only query）

當查詢的列都在索引的字段中時，查詢的效率更高，所以應該儘量避免使用 select *，需要哪些字段，就只查哪些字段。

5. 匹配列前綴（match a column prefix）

僅僅使用索引中的第一列，並且只包含索引第一列的開頭一部分進行查找。

例如，現在需要查詢出標題 title 是以 AFRICAN 開頭的電影信息，從執行計劃能夠清楚看到，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 表示優化器需要通過索引回表查詢數據。

6. 能夠實現索引匹配部分精確而其他部分進行範圍匹配（match one part exactly and match a range on another part）

例如，需要查詢出租日期 rental_date 為指定日期且客戶編號 customer_id 為指定範圍的庫存：

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 就會使用索引。

例如，查詢支付表 payment 的租賃編號 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)

五、存在索引但不能使用索引的典型場景

有些時候雖然有索引，但是並不被優化器選擇使用，下面舉例幾個不能使用索引的場景。

1.以%開頭的 like 查詢不能利用 B-Tree 索引，執行計劃中 key 的值為 null 表示沒有使用索引

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)

因為 B-Tree 索引的結構，所以以%開頭的插敘很自然就沒法利用索引了。一般推薦使用全文索引（Fulltext）來解決類似的全文檢索的問題。或者考慮利用 innodb 的表都是聚簇表的特點，採取一種輕量級別的解決方式：一般情況下，索引都會比表小，掃描索引要比掃描表更快，而Innodb 表上二級索引 idx_last_name 實際上存儲字段 last_name 還有主鍵 actot_id,那麼理想的訪問應該是首先掃描二級索引 idx_last_name 獲得滿足條件的last_name like '%NI%' 的主鍵 actor_id 列表，之後根據主鍵回表去檢索記錄，這樣訪問避開了全表掃描演員表 actor 產生的大量 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

從執行計劃中能夠看出，extra 字段 using wehre；using index。理論上比全表掃描更快一下。

2. 數據類型出現隱式轉換的時候也不會使用索引

當列的類型是字符串，那麼一定記得在 where 條件中把字符常量值用引號引起來，否則即便這個列上有索引，mysql 也不會用到，因為 MySQL 默認把輸入的常量值進行轉換以後才進行檢索。

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

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. 複合索引的情況下，假如查詢條件不包含索引列最左邊部分，即不滿足最左原則 leftmost，是不會使用複合索引的。

4. 如果 MySQL 估計使用索引比全表掃描更慢，則不使用索引。

5. 用 or 分割開的條件，如果 or 前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

六、查看索引使用情況

如果索引正在工作， Handler_read_key 的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表名增加索引得到的性能改善不高，因為索引並不經常使用。

Handler_read_rnd_next 的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果正在進行大量的表掃描，Handler_read_rnd_next 的值較高，則通常說明表索引不正確或寫入的查詢沒有利用索引，具體如下。

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. 字符串字段權衡區分度與長度的技巧

截取不同長度，測試區分度

# 這裡假設截取6個字符長度計算區別度，直到區別度達到0.1，就可以把這個字段的這個長度作為索引了mysql> select count(distinct left([varchar]],6))/count(*) from table;#注意：設置前綴索引時指定的長度表示字節數，而對於非二進制類型(CHAR, VARCHAR, TEXT)字段而言的字段長度表示字符數，所# 以，在設置前綴索引前需要把計算好的字符數轉化為字節數，常用字符集與字節的關係如下：# latin 單字節：1B# GBK 雙字節：2B# UTF8 三字節：3B# UTF8mb4 四字節：4B # myisam 表的索引大小默認為 1000字節，innodb 表的索引大小默認為 767 字節，可以在配置文件中修改 innodb_large_prefix # 項的值增大 innodb 索引的大小，最大 3072 字節。

區別度能達到0.1，就可以。

2. 左前綴不易區分的字段索引建立方法

這樣的字段，左邊有大量重複字符，比如url字段彙總的http://

1. 倒過來存儲並建立索引

2. 新增偽hash字段 把字符串轉化為整型

3. 索引覆蓋

概念：如果查詢的列恰好是索引的一部分，那麼查詢只需要在索引文件上進行，不需要回行到磁盤，這種查詢，速度極快，江湖人稱——索引覆蓋

4. 延遲關聯

在根據條件查詢數據時，如果查詢條件不能用的索引，可以先查出數據行的id，再根據id去取數據行。

eg.

//普通查詢 沒有用到索引select * from post where content like "%新聞%";//延遲關聯優化後 內層查詢走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. 重複索引和冗餘索引

重複索引：在同一列或者相同順序的幾個列建立了多個索引，成為重複索引，沒有任何意義，刪掉

冗餘索引：兩個或多個索引所覆蓋的列有重疊，比如對於列m,n ，加索引index m(m),indexmn(m,n),稱為冗餘索引。

7. 索引碎片與維護

在數據表長期的更改過程中，索引文件和數據文件都會產生空洞，形成碎片。修復表的過程十分耗費資源，可以用比較長的週期修復表。

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

8. innodb引擎的索引注意事項

Innodb 表要儘量自己指定主鍵，如果有幾個列都是唯一的，要選擇最常作為訪問條件的列作為主鍵，另外，Innodb 表的普通索引都會保存主鍵的鍵值，所以主鍵要儘可能選擇較短的數據類型，可以有效的減少索引的磁盤佔用，提高索引的緩存效果。

大家可以點擊加入群：606187239【JAVA大牛學習交流】

裡面有Java高級大牛直播講解知識點 走的就是高端路線

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

瓶頸 我這裡有一個JAVA的免費直播課程 講的是高端的知識點

基礎不好的誤入喲 只要你有1-5年的開發經驗

可以加群找我要課堂鏈接 注意：是免費的 沒有開發經驗誤入哦）

1、具有1-5工作經驗的，面對目前流行的技術不知從何下手，

需要突破技術瓶頸的可以加。2、在公司待久了，過得很安逸，

但跳槽時面試碰壁。需要在短時間內進修、跳槽拿高薪的可以加。

3、如果沒有工作經驗，但基礎非常紮實，對java工作機制，

常用設計思想，常用java開發框架掌握熟練的，可以加。

4、覺得自己很牛B，一般需求都能搞定。

但是所學的知識點沒有系統化，很難在技術領域繼續突破的可以加。

5. 群號：高級架構群 606187239備註好信息！

6.阿里Java高級大牛直播講解知識點，分享知識，

多年工作經驗的梳理和總結，帶著大家全面、

科學地建立自己的技術體系和技術認知！