mysql如何查看索引使用方式以及最佳化

標籤：

mysql中支援hash和btree索引。innodb和myisam只支援btree索引，而memory和heap儲存引擎可以支援hash和btree索引

我們可以通過下面語句查詢當前索引使用方式：

show status like ‘%Handler_read%‘;

+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+

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

如果Handler_read_rnd_next值很高意味著查詢運行效率很低，應該建立索引補救，這個值含義是在資料檔案中讀取下一行的請求數。如果進行中大量表掃描，Handler_read_rnd_next的數值將會很高。說明索引不正確或者沒有利用索引。

 

最佳化：

最佳化insert語句：

1.盡量採用 insert into test values(),(),(),()...

2.如果從不同客戶插入多行，能通過使用insert delayed語句得到更高的速度，delayed含義是讓insert語句馬上執行，其實資料都被放在記憶體隊列中個，並沒有真正寫入磁碟，這比每條語句分別插入快的多；low_priority剛好相反，在所有其他使用者對錶的讀寫完後才進行插入。

3.將索引檔案和資料檔案分在不同磁碟上存放（利用建表語句）

4.如果進行批量插入，可以增加bulk_insert_buffer_size變數值方法來提高速度，但是只對MyISAM表使用

5.當從一個文字檔裝載一個表時，使用load data file，通常比使用insert快20倍

最佳化group by語句：

預設情況下，mysql會對所有group by欄位進行排序，這與order by類似。如果查詢包括group by但使用者想要避免排序結果的消耗，則可以指定order by null禁止排序。

最佳化order by語句：

某些情況下，mysql可以使用一個索引滿足order by字句，因而不需要額外的排序。where條件和order by使用相同的索引，並且order by的順序和索引的順序相同，並且order by的欄位都是升序或者降序。

最佳化巢狀查詢：

mysql4.1開始支援子查詢，但是某些情況下，子查詢可以被更有效率的join替代，尤其是join的被動表待帶有索引的時候，原因是mysql不需要再記憶體中建立暫存資料表來完成這個邏輯上需要兩個步驟的查詢工作。

 

mysql如何查看索引使用方式以及最佳化