MySQL索引的示例分析

這篇文章給大家分享的是有關(guān)索引的示例分析的內容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

前言

我們知道，索引的選擇是優(yōu)化器階段的工作，但是優(yōu)化器并不是萬(wàn)能的，它有可能選錯所要使用的索引。一般優(yōu)化器選擇索引考慮的因素有：掃描行數，是否排序，是否使用臨時(shí)表。

使用explain分析sql

explain是很好的自測命令，勤于使用explain有助于我們寫(xiě)出更合理的sql語(yǔ)句以及建立更合理的索引：

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字段：表示關(guān)于哪張表；
type字段：system，const，eq_reg，ref，range，index，all。一般來(lái)說(shuō)要達到range級別以上；

system、const：可以將查詢(xún)的變量轉為常量，如id=1；id為主鍵或唯一鍵；
eq_ref：訪(fǎng)問(wèn)索引，返回某單一行的數據，通常在連接時(shí)出現，查詢(xún)使用的索引為主鍵或唯一鍵；
ref：訪(fǎng)問(wèn)索引，返回某個(gè)值得數據（可能是多行），通常使用=時(shí)發(fā)生；
range：使用索引返回一個(gè)范圍內的行信息，如使用>，<，between
index：以索引的順序進(jìn)行全表掃描，雖然有索引不用排序，但是要全表掃描；
all：全表掃描

key字段：實(shí)際使用的索引；

key_len字段：使用的索引長(cháng)度（在不損失精度的情況下，長(cháng)度越短越好）；

ref字段：顯示索引的哪一列被使用了；

rows字段：MySQL認為檢索需要的數據行數；

Extra字段：查詢(xún)的額外信息，主要有以下幾種：

using index：使用了索引
using where：使用了where條件
using tmporary：用到臨時(shí)表去處理當前查詢(xún)
using filesort：用到額外的排序，如order字段無(wú)索引
range checked for eache record(index map:N)：無(wú)索引可用
using index for group-by：表名可以在索引中找到分組所需的所有數據，不需要查詢(xún)實(shí)際的表

一般遇到Using temporary和Using filesort就要想辦法優(yōu)化一下了，因為用不到索引。

MySQL怎么計算需要檢索的行數

實(shí)際中，MySQL所統計的掃描行數并不是精確值，有時(shí)候甚至會(huì )相差很遠，而掃描行數則是基于索引的基數來(lái)計算的。

在MySQL中，通過(guò)采樣統計的方式去獲取索引基數：系統默認選取 N 個(gè)數據頁(yè)，統計數據頁(yè)上不同值得平均值，然后乘以索引的頁(yè)面數得到基數，而且MySQL會(huì )在變更的數據行數超過(guò) 1/M 時(shí)來(lái)觸發(fā)重做索引統計的操作。

在MySQL中，有2種存儲索引統計的方式，可以通過(guò)設置innodb_stats_persistent參數來(lái)選擇：

設置為 on 的時(shí)候，表示統計信息會(huì )持久化存儲。這時(shí)，默認的 N 是 20，M 是 10。

設置為 off 的時(shí)候，表示統計信息只存儲在內存中。這時(shí)，默認的 N 是 8，M 是 16。

一般來(lái)說(shuō)，基數統計出來(lái)的數據和真實(shí)的行數沒(méi)有很大差距，但是涉及到刪除數據新增數據比較頻繁的數據表，可能會(huì )出現數據表有10萬(wàn)條數據但是基數統計卻有20萬(wàn)的情況，這就可能是MVCC在作怪了，因為MySQL的InnoDB的事務(wù)支持，需要維持多個(gè)數據版本，就有可能某些事務(wù)還沒(méi)結束，還在使用刪除了很久的數據導致已刪除的數據空間無(wú)法釋放，而新增的數據又開(kāi)辟了新的空間，那么這時(shí)候就導致基數統計中數據頁(yè)數量可能出現失誤，出現較大誤差。

一個(gè)很好的修正方式就是執行analyze table 表名，該命令用來(lái)重新統計索引信息。

索引選錯了我們到底怎么辦

當我們正確的建立必須的索引后，大部分情況下，優(yōu)化器其實(shí)并不會(huì )選擇錯索引，當我們遇到索引選錯的情況下，該怎么去處理呢？

1、使用force index強制使用某個(gè)索引。

2、轉換思路，優(yōu)化一下sql語(yǔ)句可能就會(huì )使用到該使用的索引。

3、新建更合適的索引或刪除掉誤用到的不合理的索引。（有些時(shí)候，可能真的是這個(gè)索引是多余的，還不是最優(yōu)的，優(yōu)化器又剛好使用到了它）。