共計 2499 個字符,預計需要花費 7 分鐘才能閱讀完成。
這篇文章給大家分享的是有關 MySQL 索引的示例分析的內容。丸趣 TV 小編覺得挺實用的,因此分享給大家做個參考,一起跟隨丸趣 TV 小編過來看看吧。
前言
我們知道,索引的選擇是優化器階段的工作,但是優化器并不是萬能的,它有可能選錯所要使用的索引。一般優化器選擇索引考慮的因素有:掃描行數,是否排序,是否使用臨時表。
使用 explain 分析 sql
explain 是很好的自測命令,勤于使用 explain 有助于我們寫出更合理的 sql 語句以及建立更合理的索引:
mysql explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| 1 | SIMPLE | t | NULL | range | a,b | b | 5 | NULL | 50223 | 1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)
其中:
table 字段:表示關于哪張表;
type 字段:system,const,eq_reg,ref,range,index,all。一般來說要達到 range 級別以上;
system、const:可以將查詢的變量轉為常量,如 id=1;id 為主鍵或唯一鍵;
eq_ref:訪問索引,返回某單一行的數據,通常在連接時出現,查詢使用的索引為主鍵或唯一鍵;
ref:訪問索引,返回某個值得數據(可能是多行),通常使用 = 時發生;
range:使用索引返回一個范圍內的行信息,如使用,,between
index:以索引的順序進行全表掃描,雖然有索引不用排序,但是要全表掃描;
all:全表掃描
key 字段:實際使用的索引;
key_len 字段:使用的索引長度(在不損失精度的情況下,長度越短越好);
ref 字段:顯示索引的哪一列被使用了;
rows 字段:MySQL 認為檢索需要的數據行數;
Extra 字段:查詢的額外信息,主要有以下幾種:
using index:使用了索引
using where:使用了 where 條件
using tmporary:用到臨時表去處理當前查詢
using filesort:用到額外的排序,如 order 字段無索引
range checked for eache record(index map:N):無索引可用
using index for group-by:表名可以在索引中找到分組所需的所有數據,不需要查詢實際的表
一般遇到 Using temporary 和 Using filesort 就要想辦法優化一下了,因為用不到索引。
MySQL 怎么計算需要檢索的行數
實際中,MySQL 所統計的掃描行數并不是精確值,有時候甚至會相差很遠,而掃描行數則是基于索引的基數來計算的。
在 MySQL 中,通過采樣統計的方式去獲取索引基數:系統默認選取 N 個數據頁,統計數據頁上不同值得平均值,然后乘以索引的頁面數得到基數,而且 MySQL 會在變更的數據行數超過 1/M 時來觸發重做索引統計的操作。
在 MySQL 中,有 2 種存儲索引統計的方式,可以通過設置 innodb_stats_persistent 參數來選擇:
設置為 on 的時候,表示統計信息會持久化存儲。這時,默認的 N 是 20,M 是 10。
設置為 off 的時候,表示統計信息只存儲在內存中。這時,默認的 N 是 8,M 是 16。
一般來說,基數統計出來的數據和真實的行數沒有很大差距,但是涉及到刪除數據新增數據比較頻繁的數據表,可能會出現數據表有 10 萬條數據但是基數統計卻有 20 萬的情況,這就可能是 MVCC 在作怪了,因為 MySQL 的 InnoDB 的事務支持,需要維持多個數據版本,就有可能某些事務還沒結束,還在使用刪除了很久的數據導致已刪除的數據空間無法釋放,而新增的數據又開辟了新的空間,那么這時候就導致基數統計中數據頁數量可能出現失誤,出現較大誤差。
一個很好的修正方式就是執行 analyze table 表名,該命令用來重新統計索引信息。
索引選錯了我們到底怎么辦
當我們正確的建立必須的索引后,大部分情況下,優化器其實并不會選擇錯索引,當我們遇到索引選錯的情況下,該怎么去處理呢?
1、使用 force index 強制使用某個索引。
2、轉換思路,優化一下 sql 語句可能就會使用到該使用的索引。
3、新建更合適的索引或刪除掉誤用到的不合理的索引。(有些時候,可能真的是這個索引是多余的,還不是最優的,優化器又剛好使用到了它)。
感謝各位的閱讀!關于“MySQL 索引的示例分析”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!