概述
在關(guān)系數(shù)據(jù)庫中,索引是一種單獨(dú)的、物理的對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種存儲(chǔ)結(jié)構(gòu),它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁的邏輯指針清單。
mysql中支持hash和btree索引。innodb和myisam只支持btree索引,而memory和heap存儲(chǔ)引擎可以支持hash和btree索引
1、查看當(dāng)前索引使用情況
我們可以通過下面語句查詢當(dāng)前索引使用情況:
- Handler_read_first 代表讀取索引頭的次數(shù),如果這個(gè)值很高,說明全索引掃描很多。
- Handler_read_key代表一個(gè)索引被使用的次數(shù),如果我們新增加一個(gè)索引,可以查看Handler_read_key是否有增加,如果有增加,說明sql用到索引。
- Handler_read_next 代表讀取索引的下列,一般發(fā)生range scan。
- Handler_read_prev 代表讀取索引的上列,一般發(fā)生在ORDER BY … DESC。
- Handler_read_rnd 代表在固定位置讀取行,如果這個(gè)值很高,說明對大量結(jié)果集進(jìn)行了排序、進(jìn)行了全表掃描、關(guān)聯(lián)查詢沒有用到合適的KEY。
- Handler_read_rnd_next 代表進(jìn)行了很多表掃描,查詢性能低下。
其實(shí)比較多應(yīng)用場景是當(dāng)索引正在工作,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的值較高,則通常說明表索引不正確或?qū)懭氲牟樵儧]有利用索引
2、查看索引是否被使用到
SELECT
object_type,
object_schema,
object_name,
index_name,
count_star,
count_read,
COUNT_FETCH
FROM
PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage;
如果read,fetch的次數(shù)都為0的話,就是沒有被使用過的。
3、查看使用了哪些索引
explain相關(guān)sql,查看type表示查詢用到了那種索引類型
+-----+-------+-------+-----+--------+-------+---------+-------+
| ALL | index | range | ref | eq_ref | const | system | NULL |
+-----+-------+-------+-----+--------+-------+---------+-------+
從最好到最差依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system 表中只有一條記錄,一般來說只在系統(tǒng)表里出現(xiàn)。
- const 表示通過一次索引查詢就查詢到了,一般對應(yīng)索引列為primarykey 或者unique where語句中 指定 一個(gè)常量,因?yàn)橹黄ヅ湟恍袛?shù)據(jù),MYSQL能把這個(gè)查詢優(yōu)化為一個(gè)常量,所以非???。
- eq_ref 唯一性索引掃描。此類型通常出現(xiàn)在多表的 join 查詢,對于每一個(gè)從前面的表連接的對應(yīng)列,當(dāng)前表的對應(yīng)列具有唯一性索引,最多只有一行數(shù)據(jù)與之匹配。
- ref 非唯一性索引掃描。同上,但當(dāng)前表的對應(yīng)列不具有唯一性索引,可能有多行數(shù)據(jù)匹配。此類型通常出現(xiàn)在多表的 join 查詢, 針對于非唯一或非主鍵索引, 或者是使用了 最左前綴 規(guī)則索引的查詢.
- range 索引的范圍查詢。查詢索引關(guān)鍵字某個(gè)范圍的值。
- index 全文索引掃描。與all基本相同,掃描了全文,但查詢的字段被索引包含,故不需要讀取表中數(shù)據(jù),只需要讀取索引樹中的字段。
- all 全文掃描。未使用索引,效率最低。
順便提幾個(gè)優(yōu)化注意點(diǎn):
1、優(yōu)化insert語句:
1)盡量采用 insert into test values(),(),(),()...
2)如果從不同客戶插入多行,能通過使用insert delayed語句得到更高的速度,delayed含義是讓insert語句馬上執(zhí)行,其實(shí)數(shù)據(jù)都被放在內(nèi)存隊(duì)列中個(gè),并沒有真正寫入磁盤,這比每條語句分別插入快的多;low_priority剛好相反,在所有其他用戶對表的讀寫完后才進(jìn)行插入。
3)將索引文件和數(shù)據(jù)文件分在不同磁盤上存放(利用建表語句)
4)如果進(jìn)行批量插入,可以增加bulk_insert_buffer_size變量值方法來提高速度,但是只對MyISAM表使用
5)當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí),使用load data file,通常比使用insert快20倍
2、優(yōu)化group by語句:
默認(rèn)情況下,mysql會(huì)對所有g(shù)roup by字段進(jìn)行排序,這與order by類似。如果查詢包括group by但用戶想要避免排序結(jié)果的消耗,則可以指定order by null禁止排序。
3、優(yōu)化order by語句:
某些情況下,mysql可以使用一個(gè)索引滿足order by字句,因而不需要額外的排序。where條件和order by使用相同的索引,并且order by的順序和索引的順序相同,并且order by的字段都是升序或者降序。
4、優(yōu)化嵌套查詢:
mysql4.1開始支持子查詢,但是某些情況下,子查詢可以被更有效率的join替代,尤其是join的被動(dòng)表待帶有索引的時(shí)候,原因是mysql不需要再內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上需要兩個(gè)步驟的查詢工作。
最后提一個(gè)點(diǎn):
一個(gè)表最多16個(gè)索引,最大索引長度256字節(jié),索引一般不明顯影響插入性能(大量小數(shù)據(jù)例外),因?yàn)榻⑺饕臅r(shí)間開銷是O(1)或者O(logN)。不過太多索引也是不好的,畢竟更新之類的操作都需要去維護(hù)索引。
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對腳本之家的支持。
您可能感興趣的文章:- MySQL索引類型總結(jié)和使用技巧以及注意事項(xiàng)
- mysql索引使用技巧及注意事項(xiàng)
- MySQL數(shù)據(jù)庫優(yōu)化技術(shù)之索引使用技巧總結(jié)
- 一篇文章掌握MySQL的索引查詢優(yōu)化技巧
- MySQL索引知識(shí)的一些小妙招總結(jié)