建站
咨詢
售后
建站咨詢
新聞中心
這篇文章給大家分享的是有關(guān)MySQL索引的示例分析的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。
10年積累的成都網(wǎng)站建設(shè)、成都網(wǎng)站制作經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先做網(wǎng)站設(shè)計(jì)后付款的網(wǎng)站建設(shè)流程，更有太原免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
前言
我們知道，索引的選擇是優(yōu)化器階段的工作，但是優(yōu)化器并不是萬(wàn)能的，它有可能選錯(cuò)所要使用的索引。一般優(yōu)化器選擇索引考慮的因素有：掃描行數(shù)，是否排序，是否使用臨時(shí)表。
使用explain分析sql
explain是很好的自測(cè)命令，勤于使用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ō)要達(dá)到range級(jí)別以上；
system、const：可以將查詢的變量轉(zhuǎn)為常量，如id=1；id為主鍵或唯一鍵；
eq_ref：訪問(wèn)索引，返回某單一行的數(shù)據(jù)，通常在連接時(shí)出現(xiàn)，查詢使用的索引為主鍵或唯一鍵；
ref：訪問(wèn)索引，返回某個(gè)值得數(shù)據(jù)（可能是多行），通常使用=時(shí)發(fā)生；
range：使用索引返回一個(gè)范圍內(nèi)的行信息，如使用>，<，between
index：以索引的順序進(jìn)行全表掃描，雖然有索引不用排序，但是要全表掃描；
all：全表掃描
key字段：實(shí)際使用的索引；
key_len字段：使用的索引長(zhǎng)度（在不損失精度的情況下，長(zhǎng)度越短越好）；
ref字段：顯示索引的哪一列被使用了；
rows字段：MySQL認(rèn)為檢索需要的數(shù)據(jù)行數(shù)；
Extra字段：查詢的額外信息，主要有以下幾種：
using index：使用了索引
using where：使用了where條件
using tmporary：用到臨時(shí)表去處理當(dāng)前查詢
using filesort：用到額外的排序，如order字段無(wú)索引
range checked for eache record(index map:N)：無(wú)索引可用
using index for group-by：表名可以在索引中找到分組所需的所有數(shù)據(jù)，不需要查詢實(shí)際的表
一般遇到Using temporary和Using filesort就要想辦法優(yōu)化一下了，因?yàn)橛貌坏剿饕?/p>
MySQL怎么計(jì)算需要檢索的行數(shù)
實(shí)際中，MySQL所統(tǒng)計(jì)的掃描行數(shù)并不是精確值，有時(shí)候甚至?xí)嗖詈苓h(yuǎn)，而掃描行數(shù)則是基于索引的基數(shù)來(lái)計(jì)算的。
在MySQL中，通過(guò)采樣統(tǒng)計(jì)的方式去獲取索引基數(shù)：系統(tǒng)默認(rèn)選取 N 個(gè)數(shù)據(jù)頁(yè)，統(tǒng)計(jì)數(shù)據(jù)頁(yè)上不同值得平均值，然后乘以索引的頁(yè)面數(shù)得到基數(shù)，而且MySQL會(huì)在變更的數(shù)據(jù)行數(shù)超過(guò) 1/M 時(shí)來(lái)觸發(fā)重做索引統(tǒng)計(jì)的操作。
在MySQL中，有2種存儲(chǔ)索引統(tǒng)計(jì)的方式，可以通過(guò)設(shè)置innodb_stats_persistent參數(shù)來(lái)選擇：
設(shè)置為 on 的時(shí)候，表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。這時(shí)，默認(rèn)的 N 是 20，M 是 10。
設(shè)置為 off 的時(shí)候，表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中。這時(shí)，默認(rèn)的 N 是 8，M 是 16。
一般來(lái)說(shuō)，基數(shù)統(tǒng)計(jì)出來(lái)的數(shù)據(jù)和真實(shí)的行數(shù)沒(méi)有很大差距，但是涉及到刪除數(shù)據(jù)新增數(shù)據(jù)比較頻繁的數(shù)據(jù)表，可能會(huì)出現(xiàn)數(shù)據(jù)表有10萬(wàn)條數(shù)據(jù)但是基數(shù)統(tǒng)計(jì)卻有20萬(wàn)的情況，這就可能是MVCC在作怪了，因?yàn)镸ySQL的InnoDB的事務(wù)支持，需要維持多個(gè)數(shù)據(jù)版本，就有可能某些事務(wù)還沒(méi)結(jié)束，還在使用刪除了很久的數(shù)據(jù)導(dǎo)致已刪除的數(shù)據(jù)空間無(wú)法釋放，而新增的數(shù)據(jù)又開(kāi)辟了新的空間，那么這時(shí)候就導(dǎo)致基數(shù)統(tǒng)計(jì)中數(shù)據(jù)頁(yè)數(shù)量可能出現(xiàn)失誤，出現(xiàn)較大誤差。
一個(gè)很好的修正方式就是執(zhí)行analyze table 表名，該命令用來(lái)重新統(tǒng)計(jì)索引信息。
索引選錯(cuò)了我們到底怎么辦
當(dāng)我們正確的建立必須的索引后，大部分情況下，優(yōu)化器其實(shí)并不會(huì)選擇錯(cuò)索引，當(dāng)我們遇到索引選錯(cuò)的情況下，該怎么去處理呢？
1、使用force index強(qiáng)制使用某個(gè)索引。
2、轉(zhuǎn)換思路，優(yōu)化一下sql語(yǔ)句可能就會(huì)使用到該使用的索引。
3、新建更合適的索引或刪除掉誤用到的不合理的索引。（有些時(shí)候，可能真的是這個(gè)索引是多余的，還不是最優(yōu)的，優(yōu)化器又剛好使用到了它）。
感謝各位的閱讀！關(guān)于“MySQL索引的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
當(dāng)前文章：MySQL索引的示例分析
本文地址：http://www.ef60e0e.cn/article/gjspio.html
