mysql單表中數(shù)據(jù)量到達(dá)一定數(shù)量后,查詢效率會(huì)變得很低,使用索引可以有效地提高mysql的查詢效率.但是索引使用不當(dāng),會(huì)使索引失效,起不到提升效率的作用,在實(shí)際項(xiàng)目中,要做好索引的優(yōu)化,合理的使用索引。關(guān)于索引的優(yōu)化付鹿，可以參考這篇文章知市，傳送門
為了分析sql語句執(zhí)行效率,使用explain 分析sql語句
使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語句次企，從而得知MySQL 是如何處理sql語句娃磺。

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

id

select 查詢的序列號(hào)，包含一組可以重復(fù)的數(shù)字，表示查詢中執(zhí)行sql語句的順序勉耀。一般有三種情況：
第一種：id全部相同，sql的執(zhí)行順序是由上至下粗蔚；
第二種：id全部不同尝偎，sql的執(zhí)行順序是根據(jù)id大的優(yōu)先執(zhí)行；
第三種：id既存在相同鹏控，又存在不同的致扯。先根據(jù)id大的優(yōu)先執(zhí)行，再根據(jù)相同id從上至下的執(zhí)行当辐。

select_type

select 查詢的類型抖僵，主要是用于區(qū)別普通查詢，聯(lián)合查詢缘揪，嵌套的復(fù)雜查詢
simple：簡單的select 查詢耍群，查詢中不包含子查詢或者union
primary：查詢中若包含任何復(fù)雜的子查詢义桂，最外層查詢則被標(biāo)記為primary
subquery：在select或where 列表中包含了子查詢
derived：在from列表中包含的子查詢被標(biāo)記為derived（衍生）MySQL會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里蹈垢。
union：若第二個(gè)select出現(xiàn)在union之后慷吊，則被標(biāo)記為union，若union包含在from子句的子查詢中曹抬，外層select將被標(biāo)記為：derived
union result：從union表獲取結(jié)果的select

partitions

表所使用的分區(qū)溉瓶，如果要統(tǒng)計(jì)十年公司訂單的金額，可以把數(shù)據(jù)分為十個(gè)區(qū)谤民，每一年代表一個(gè)區(qū)堰酿。這樣可以大大的提高查詢效率。

type

這是一個(gè)非常重要的參數(shù)张足，連接類型触创，常見的有：all , index , range , ref , eq_ref , const , system , null 八個(gè)級(jí)別。
性能從最優(yōu)到最差的排序：system > const > eq_ref > ref > range > index > all
在代碼中,若保證查詢至少達(dá)到range級(jí)別或者最好能達(dá)到ref則算是一個(gè)優(yōu)秀而又負(fù)責(zé)的程序員兢榨。
all：（full table scan）全表掃描無疑是最差嗅榕，若是百萬千萬級(jí)數(shù)據(jù)量，全表掃描會(huì)非常慢吵聪。
index：（full index scan）全索引文件掃描比all好很多凌那，畢竟從索引樹中找數(shù)據(jù)，比從全表中找數(shù)據(jù)要快吟逝。
range：只檢索給定范圍的行帽蝶，使用索引來匹配行。范圍縮小了块攒，當(dāng)然比全表掃描和全索引文件掃描要快励稳。sql語句中一般會(huì)有between，in囱井，>驹尼，< 等查詢。
ref：非唯一性索引掃描庞呕，本質(zhì)上也是一種索引訪問新翎，返回所有匹配某個(gè)單獨(dú)值的行。比如查詢公司所有屬于研發(fā)團(tuán)隊(duì)的同事住练，匹配的結(jié)果是多個(gè)并非唯一值地啰。
eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵讲逛，表中有一條記錄與之匹配亏吝。比如查詢公司的CEO，匹配的結(jié)果只可能是一條記錄盏混，
const：表示通過索引一次就可以找到蔚鸥，const用于比較primary key 或者unique索引惜论。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快株茶，若將主鍵至于where列表中来涨，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量。
system：表只有一條記錄（等于系統(tǒng)表）启盛，這是const類型的特列蹦掐，平時(shí)不會(huì)出現(xiàn)，了解即可

possible_keys

顯示查詢語句可能用到的索引(一個(gè)或多個(gè)或?yàn)閚ull)僵闯，不一定被查詢實(shí)際使用卧抗。僅供參考使用。

key

顯示查詢語句實(shí)際使用的索引鳖粟。若為null社裆，則表示沒有使用索引。

key_len

顯示索引中使用的字節(jié)數(shù)向图，可通過key_len計(jì)算查詢中使用的索引長度泳秀。在不損失精確性的情況下索引長度越短越好。key_len 顯示的值為索引字段的最可能長度榄攀，并非實(shí)際使用長度嗜傅，即key_len是根據(jù)表定義計(jì)算而得，并不是通過表內(nèi)檢索出的檩赢。

ref

顯示索引的哪一列或常量被用于查找索引列上的值吕嘀。

rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)贞瞒，值越大越不好偶房。

filtered

一個(gè)百分比的值，和rows 列的值一起使用军浆，可以估計(jì)出查詢執(zhí)行計(jì)劃(QEP)中的前一個(gè)表的結(jié)果集棕洋，從而確定join操作的循環(huán)次數(shù)。小表驅(qū)動(dòng)大表乒融，減輕連接的次數(shù)拍冠。

extra

Using filesort： 說明MySQL會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取簇抵。MySQL中無法利用索引完成的排序操作稱為“文件排序” 。出現(xiàn)這個(gè)就要立刻優(yōu)化sql射众。
Using temporary：使用了臨時(shí)表保存中間結(jié)果碟摆，MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于排序 order by 和 分組查詢 group by叨橱。 出現(xiàn)這個(gè)更要立刻優(yōu)化sql典蜕。
Using index： 表示相應(yīng)的select 操作中使用了覆蓋索引（Covering index）断盛，避免訪問了表的數(shù)據(jù)行，效果不錯(cuò)愉舔！如果同時(shí)出現(xiàn)Using where钢猛，表明索引被用來執(zhí)行索引鍵值的查找。如果沒有同時(shí)出現(xiàn)Using where轩缤，表示索引用來讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作命迈。
Covering Index：覆蓋索引也叫索引覆蓋，就是select 的數(shù)據(jù)列只用從索引中就能夠取得火的，不必讀取數(shù)據(jù)行壶愤，MySQL可以利用索引返回select 列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件馏鹤。
Using index condition： 在5.6版本后加入的新特性征椒，優(yōu)化器會(huì)在索引存在的情況下，通過符合RANGE范圍的條數(shù) 和 總數(shù)的比例來選擇是使用索引還是進(jìn)行全表遍歷湃累。
Using where： 表明使用了where 過濾
Using join buffer： 表明使用了連接緩存
impossible where： where 語句的值總是false勃救，不可用，不能用來獲取任何元素
distinct： 優(yōu)化distinct操作治力，在找到第一匹配的元組后即停止找同樣值的動(dòng)作蒙秒。

在查詢中使用索引

先創(chuàng)建兩個(gè)表

CREATE TABLE user
(
  id   int(11) UNSIGNED AUTO_INCREMENT
    PRIMARY KEY,
  name char(10) NOT NULL,
  age  int      NOT NULL
)
  ENGINE = InnoDB;

CREATE TABLE phone
(
  id    int(11) UNSIGNED AUTO_INCREMENT
    PRIMARY KEY,
  uid   int(11) UNSIGNED NOT NULL,
  phone char(10)         NOT NULL
)
  ENGINE = InnoDB;

mysql的主鍵帶有一個(gè)主鍵索引,在創(chuàng)建表的時(shí)候已經(jīng)建立
在簡單地查詢中使用索引

EXPLAIN SELECT name FROM user

這時(shí)候并沒有使用索引,因?yàn)槲覀儧]有加限制條件

EXPLAIN SELECT name FROM user WHERE id=1

這時(shí)候索引已經(jīng)起作用了,是const類型
--
再來看另一種情況

EXPLAIN SELECT id FROM user WHERE id+1 =2

給user表中的name字段加上索引

ALTER TABLE `user` ADD INDEX `_name`(`name`) USING BTREE;

使用LIKE模糊查詢

EXPLAIN SELECT name FROM user WHERE name LIKE '王%'

EXPLAIN SELECT name FROM user WHERE name LIKE '%王'

再來試一下OR連接查詢

EXPLAIN SELECT id,uid FROM phone WHERE id=1 or id =2

再來試一下 IN約束查詢,這兩次查詢結(jié)果是相同的

EXPLAIN SELECT id,uid FROM phone WHERE id IN (1,2)

再來試一下OR查詢的另一種情況

EXPLAIN SELECT id,uid FROM phone WHERE id = 1 OR  uid=2

這種情況是可以使用union 連接查詢優(yōu)化的

EXPLAIN SELECT id
        FROM phone
        WHERE id = 1
        UNION ALL
        SELECT uid
        FROM phone
        WHERE uid = 2

我們可以看到,在查詢id時(shí)使用里索引,查詢uid時(shí),因?yàn)閡id沒有索引,所以沒有使用索引.

再來看一下很常用的連接查詢,

EXPLAIN SELECT user.id, name, uid, phone
        FROM user
               JOIN phone ON user.id = 1 AND uid = 2

ALTER TABLE `phone` ADD INDEX `_uid`(`uid`) USING BTREE;

跟剛才一樣,使用連接查詢

EXPLAIN SELECT user.id, name, uid, phone
        FROM user
               JOIN phone ON user.id = 1 AND uid = 2

這時(shí)候可以看到,查詢user表和phone表時(shí),都使用了索引。

總結(jié)

• 在有限制條件(WHERE,ON)的的查詢中,對(duì)約束字段盡量都加上索引.在數(shù)據(jù)量很大的時(shí)候,提升查詢效率特別明顯
• 不要在約束條件上進(jìn)行運(yùn)算,在查詢中對(duì)索引字段進(jìn)行運(yùn)算,會(huì)使用index索引,效果大打折扣
• 在使用LIKE 模糊匹配時(shí),盡量用'XXX%',不要使用'%XXX'
• 在經(jīng)常刪除琴许、更新數(shù)據(jù)的字段和數(shù)據(jù)量不大的時(shí)候(網(wǎng)上說小于百萬,因?yàn)槲覜]有試驗(yàn)過,不能確定),沒有必要使用索引,因?yàn)樗饕龝?huì)占據(jù)磁盤空間,而且也會(huì)降低插入和更新數(shù)據(jù)的效率税肪。
• 在查詢中不會(huì)使用約束條件的字段也不用加索引,比如在例子中,從來沒有對(duì)phone表中的phone字段使用過WHERE和ON條件,所以沒有必要給phone字段加索引。
• 查詢中,使用OR約束條件時(shí),OR兩邊的字段都必須有索引,如果只有一邊的字段有索引,可以使用union連接查詢提高效率榜田。
• 在連接查詢中益兄，對(duì)兩個(gè)表的關(guān)聯(lián)字段加上索引，可以有效地提高查詢效率箭券。
• NULL會(huì)導(dǎo)致索引形同虛設(shè)净捅，所以在設(shè)計(jì)表結(jié)構(gòu)時(shí)應(yīng)避免NULL 的存在（用其他方式表達(dá)你想表達(dá)的NULL，比如 -1辩块？）

最后感謝在網(wǎng)上無私分享的前輩們

我也是剛剛在學(xué)習(xí)mysql優(yōu)化方面的知識(shí),一邊學(xué)習(xí),一邊總結(jié),文中如果有錯(cuò)誤的地方,還請(qǐng)各位大神批評(píng)指正