MySQL索引優(yōu)化分析

為什么你寫的sql查詢慢？為什么你建的索引常失效？通過本章內(nèi)容，你將學(xué)會(huì)Mysql性能下降的原因，索引的簡(jiǎn)介，索引創(chuàng)建的原則，explain命令的使用，以及explain輸出字段的意義。助你了解索引，分析索引，使用索引，從而寫出更高性能的sql語(yǔ)句。還在等啥子？擼起袖子就是干！

案例分析

我們先簡(jiǎn)單了解一下非關(guān)系型數(shù)據(jù)庫(kù)和關(guān)系型數(shù)據(jù)庫(kù)的區(qū)別。

MongoDB是NoSQL中的一種。NoSQL的全稱是Not only SQL，非關(guān)系型數(shù)據(jù)庫(kù)。它的特點(diǎn)是性能高，擴(kuò)張性強(qiáng)，模式靈活，在高并發(fā)場(chǎng)景表現(xiàn)得尤為突出。但目前它還只是關(guān)系型數(shù)據(jù)庫(kù)的補(bǔ)充，它在數(shù)據(jù)的一致性，數(shù)據(jù)的安全性，查詢的復(fù)雜性問題上和關(guān)系型數(shù)據(jù)庫(kù)還存在一定差距。

MySQL是關(guān)系性數(shù)據(jù)庫(kù)中的一種，查詢功能強(qiáng)，數(shù)據(jù)一致性高，數(shù)據(jù)安全性高，支持二級(jí)索引。但性能方面稍遜與MongoDB，特別是百萬(wàn)級(jí)別以上的數(shù)據(jù)，很容易出現(xiàn)查詢慢的現(xiàn)象。這時(shí)候需要分析查詢慢的原因，一般情況下是程序員sql寫的爛，或者是沒有鍵索引，或者是索引失效等原因?qū)е碌摹?/p>

公司ERP系統(tǒng)數(shù)據(jù)庫(kù)主要是MongoDB（最接近關(guān)系型數(shù)據(jù)的NoSQL），其次是Redis，MySQL只占很少的部分。現(xiàn)在又重新使用MySQL，歸功于阿里巴巴的奇門系統(tǒng)和聚石塔系統(tǒng)。考慮到訂單數(shù)量已經(jīng)是百萬(wàn)級(jí)以上，對(duì)MySQL的性能分析也就顯得格外重要。

我們先通過兩個(gè)簡(jiǎn)單的例子來(lái)入門。后面會(huì)詳細(xì)介紹各個(gè)參數(shù)的作用和意義。

說明：需要用到的sql已經(jīng)放在了github上了，喜歡的同學(xué)可以點(diǎn)一下star，哈哈。https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/

場(chǎng)景一：訂單導(dǎo)入，通過交易號(hào)避免重復(fù)導(dǎo)單

業(yè)務(wù)邏輯：訂單導(dǎo)入時(shí)，為了避免重復(fù)導(dǎo)單，一般會(huì)通過交易號(hào)去數(shù)據(jù)庫(kù)中查詢，判斷該訂單是否已經(jīng)存在。

最基礎(chǔ)的sql語(yǔ)句

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+

| id | transaction_id | gross | net | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date |

+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+

| 10000 | 81X97310V32236260E | 6.6 | 6.13 | 1 | 10 | ok | ok | auto | 1 | itdragon | 2017-08-18 17:01:49 |

+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

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

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

查詢的本身沒有任何問題，在線下的測(cè)試環(huán)境也沒有任何問題。可是，功能一旦上線，查詢慢的問題就迎面而來(lái)。幾百上千萬(wàn)的訂單，用全表掃描？啊？哼!

怎么知道該sql是全表掃描呢？通過explain命令可以清楚MySQL是如何處理sql語(yǔ)句的。打印的內(nèi)容分別表示：

id?: 查詢序列號(hào)為1。

select_type?: 查詢類型是簡(jiǎn)單查詢，簡(jiǎn)單的select語(yǔ)句沒有union和子查詢。

table?: 表是 itdragon_order_list。

partitions?: 沒有分區(qū)。

type?: 連接類型，all表示采用全表掃描的方式。

possible_keys?: 可能用到索引為null。

key?: 實(shí)際用到索引是null。

key_len?: 索引長(zhǎng)度當(dāng)然也是null。

ref?: 沒有哪個(gè)列或者參數(shù)和key一起被使用。

Extra?: 使用了where查詢。

因?yàn)閿?shù)據(jù)庫(kù)中只有三條數(shù)據(jù)，所以rows和filtered的信息作用不大。這里需要重點(diǎn)了解的是type為ALL，全表掃描的性能是最差的，假設(shè)數(shù)據(jù)庫(kù)中有幾百萬(wàn)條數(shù)據(jù)，在沒有索引的幫助下會(huì)異常卡頓。

初步優(yōu)化：為transaction_id創(chuàng)建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

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

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | itdragon_order_list | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | NULL |

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

這里創(chuàng)建的索引是唯一索引，而非普通索引。

唯一索引打印的type值是const。表示通過索引一次就可以找到。即找到值就結(jié)束掃描返回查詢結(jié)果。

普通索引打印的type值是ref。表示非唯一性索引掃描。找到值還要繼續(xù)掃描，直到將索引文件掃描完為止。(這里沒有貼出代碼)

顯而易見，const的性能要遠(yuǎn)高于ref。并且根據(jù)業(yè)務(wù)邏輯來(lái)判斷，創(chuàng)建唯一索引是合情合理的。

再次優(yōu)化：覆蓋索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

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

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | itdragon_order_list | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | Using index |

+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

這里將select * from?改為了?select transaction_id from?后

Extra 顯示 Using index，表示該查詢使用了覆蓋索引，這是一個(gè)非常好的消息，說明該sql語(yǔ)句的性能很好。若提示的是Using filesort(使用內(nèi)部排序)和Using temporary(使用臨時(shí)表)則表明該sql需要立即優(yōu)化了。

根據(jù)業(yè)務(wù)邏輯來(lái)的，查詢結(jié)構(gòu)返回transaction_id 是可以滿足業(yè)務(wù)邏輯要求的。

場(chǎng)景二，訂單管理頁(yè)面，通過訂單級(jí)別和訂單錄入時(shí)間排序

業(yè)務(wù)邏輯：優(yōu)先處理訂單級(jí)別高，錄入時(shí)間長(zhǎng)的訂單。

既然是排序，首先想到的應(yīng)該是order by， 還有一個(gè)可怕的 Using filesort 等著你。

最基礎(chǔ)的sql語(yǔ)句

mysql> explain select * from itdragon_order_list order by order_level,input_date;

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

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

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort |

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

首先，采用全表掃描就不合理，還使用了文件排序Using filesort，更加拖慢了性能。

MySQL在4.1版本之前文件排序是采用雙路排序的算法，由于兩次掃描磁盤，I/O耗時(shí)太長(zhǎng)。后優(yōu)化成單路排序算法。其本質(zhì)就是用空間換時(shí)間，但如果數(shù)據(jù)量太大，buffer的空間不足，會(huì)導(dǎo)致多次I/O的情況。其效果反而更差。與其找運(yùn)維同事修改MySQL配置，還不如自己乖乖地建索引。

初步優(yōu)化：為order_level,input_date 創(chuàng)建復(fù)合索引

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

mysql> explain select * from itdragon_order_list order by order_level,input_date;

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

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

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort |

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

創(chuàng)建復(fù)合索引后你會(huì)驚奇的發(fā)現(xiàn)，和沒創(chuàng)建索引一樣？？？都是全表掃描，都用到了文件排序。是索引失效？還是索引創(chuàng)建失敗？我們?cè)囍纯聪旅娲蛴∏闆r

mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

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

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

| 1 | SIMPLE | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | Using index |

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

將select * from?換成了?select order_level,input_date from?后。type從all升級(jí)為index，表示（full index scan）全索引文件掃描，Extra也顯示使用了覆蓋索引。可是不對(duì)啊！！！！檢索雖然快了，但返回的內(nèi)容只有order_level和input_date 兩個(gè)字段，讓業(yè)務(wù)同事怎么用？難道把每個(gè)字段都建一個(gè)復(fù)合索引？

MySQL沒有這么笨，可以使用force index 強(qiáng)制指定索引。在原來(lái)的sql語(yǔ)句上修改?force index(idx_order_levelDate)?即可。

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

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

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

| 1 | SIMPLE | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | NULL |

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

再次優(yōu)化：訂單級(jí)別真的要排序么？

其實(shí)給訂單級(jí)別排序意義并不大，給訂單級(jí)別添加索引意義也不大。因?yàn)閛rder_level的值可能只有，低，中，高，加急，這四種。對(duì)于這種重復(fù)且分布平均的字段，排序和加索引的作用不大。

我們能否先固定 order_level 的值，然后再給 input_date 排序？如果查詢效果明顯，是可以推薦業(yè)務(wù)同事使用該查詢方式。

mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;

+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

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

+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | itdragon_order_list | NULL | ref | idx_order_levelDate | idx_order_levelDate | 5 | const | 1 | 100 | Using index condition |

+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

和之前的sql比起來(lái)，type從index 升級(jí)為 ref(非唯一性索引掃描)。索引的長(zhǎng)度從68變成了5，說明只用了一個(gè)索引。ref也是一個(gè)常量。Extra 為Using index condition 表示自動(dòng)根據(jù)臨界值，選擇索引掃描還是全表掃描。總的來(lái)說性能遠(yuǎn)勝于之前的sql。

上面兩個(gè)案例只是快速入門，我們需嚴(yán)記一點(diǎn)：優(yōu)化是基于業(yè)務(wù)邏輯來(lái)的。絕對(duì)不能為了優(yōu)化而擅自修改業(yè)務(wù)邏輯。如果能修改當(dāng)然是最好的。

索引簡(jiǎn)介

官方定義：索引（Index） 是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

大家一定很好奇，索引為什么是一種數(shù)據(jù)結(jié)構(gòu)，它又是怎么提高查詢的速度？我們拿最常用的二叉樹來(lái)分析索引的工作原理。看下面的圖片：

創(chuàng)建索引的優(yōu)勢(shì)

1 提高數(shù)據(jù)的檢索速度，降低數(shù)據(jù)庫(kù)IO成本：使用索引的意義就是通過縮小表中需要查詢的記錄的數(shù)目從而加快搜索的速度。

2 降低數(shù)據(jù)排序的成本，降低CPU消耗：索引之所以查的快，是因?yàn)橄葘?shù)據(jù)排好序，若該字段正好需要排序，則真好降低了排序的成本。

創(chuàng)建索引的劣勢(shì)

1 占用存儲(chǔ)空間：索引實(shí)際上也是一張表，記錄了主鍵與索引字段，一般以索引文件的形式存儲(chǔ)在磁盤上。

2 降低更新表的速度：表的數(shù)據(jù)發(fā)生了變化，對(duì)應(yīng)的索引也需要一起變更，從而減低的更新速度。否則索引指向的物理數(shù)據(jù)可能不對(duì)，這也是索引失效的原因之一。

3 優(yōu)質(zhì)索引創(chuàng)建難：索引的創(chuàng)建并非一日之功，也并非一直不變。需要頻繁根據(jù)用戶的行為和具體的業(yè)務(wù)邏輯去創(chuàng)建最佳的索引。

索引分類

我們常說的索引一般指的是BTree（多路搜索樹）結(jié)構(gòu)組織的索引。其中還有聚合索引，次要索引，復(fù)合索引，前綴索引，唯一索引，統(tǒng)稱索引，當(dāng)然除了B+樹外，還有哈希索引（hash index）等。

單值索引：一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

唯一索引：索引列的值必須唯一，但允許有空值

復(fù)合索引：一個(gè)索引包含多個(gè)列，實(shí)際開發(fā)中推薦使用

實(shí)際開發(fā)中推薦使用復(fù)合索引，并且單表創(chuàng)建的索引個(gè)數(shù)建議不要超過五個(gè)

基本語(yǔ)法：

創(chuàng)建：

create [unique] index indexName on tableName (columnName...)

alter tableName add [unique] index [indexName] on (columnName...)

刪除：

drop index [indexName] on tableName

查看：

show index from tableName

哪些情況需要建索引：

1 主鍵，唯一索引

2 經(jīng)常用作查詢條件的字段需要?jiǎng)?chuàng)建索引

3 經(jīng)常需要排序、分組和統(tǒng)計(jì)的字段需要建立索引

4 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

哪些情況不要建索引：

1 表的記錄太少，百萬(wàn)級(jí)以下的數(shù)據(jù)不需要?jiǎng)?chuàng)建索引

2 經(jīng)常增刪改的表不需要?jiǎng)?chuàng)建索引

3 數(shù)據(jù)重復(fù)且分布平均的字段不需要?jiǎng)?chuàng)建索引，如 true,false 之類。

4 頻發(fā)更新的字段不適合創(chuàng)建索引

5 where條件里用不到的字段不需要?jiǎng)?chuàng)建索引

性能分析

MySQL 自身瓶頸

MySQL自身參見的性能問題有磁盤空間不足，磁盤I/O太大，服務(wù)器硬件性能低。

1 CPU：CPU 在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時(shí)候

2 IO：磁盤I/O 瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候

3 服務(wù)器硬件的性能瓶頸：top,free,iostat 和 vmstat來(lái)查看系統(tǒng)的性能狀態(tài)

explain 分析sql語(yǔ)句

使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語(yǔ)句，從而得知MySQL 是如何處理sql語(yǔ)句。

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

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

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

id

select 查詢的序列號(hào)，包含一組可以重復(fù)的數(shù)字，表示查詢中執(zhí)行sql語(yǔ)句的順序。一般有三種情況：

第一種：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：簡(jiǎn)單的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

對(duì)java程序員來(lái)說，若保證查詢至少達(dá)到range級(jí)別或者最好能達(dá)到ref則算是一個(gè)優(yōu)秀而又負(fù)責(zé)的程序員。

all：（full table scan）全表掃描無(wú)疑是最差，若是百萬(wàn)千萬(wàn)級(jí)數(shù)據(jù)量，全表掃描會(huì)非常慢。

index：（full index scan）全索引文件掃描比all好很多，畢竟從索引樹中找數(shù)據(jù)，比從全表中找數(shù)據(jù)要快。

range：只檢索給定范圍的行，使用索引來(lái)匹配行。范圍縮小了，當(dāng)然比全表掃描和全索引文件掃描要快。sql語(yǔ)句中一般會(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

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

key

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

key_len

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

ref

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

rows

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

extra

Using filesort： 說明MySQL會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無(wú)法利用索引完成的排序操作稱為“文件排序” 。出現(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，表明索引被用來(lái)執(zhí)行索引鍵值的查找。如果沒有同時(shí)出現(xiàn)Using where，表示索引用來(lái)讀取數(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ù)的比例來(lái)選擇是使用索引還是進(jìn)行全表遍歷。

Using where： 表明使用了where 過濾

Using join buffer： 表明使用了連接緩存

impossible where： where 語(yǔ)句的值總是false，不可用，不能用來(lái)獲取任何元素

distinct： 優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動(dòng)作。

filtered

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

通過explain的參數(shù)介紹，我們可以得知:

1 表的讀取順序(id)

2 數(shù)據(jù)讀取操作的操作類型(type)

3 哪些索引被實(shí)際使用(key)

4 表之間的引用(ref)

5 每張表有多少行被優(yōu)化器查詢(rows)

性能下降的原因

從程序員的角度

1 查詢語(yǔ)句寫的不好

2 沒建索引，索引建的不合理或索引失效

3 關(guān)聯(lián)查詢有太多的join

從服務(wù)器的角度

1 服務(wù)器磁盤空間不足

2 服務(wù)器調(diào)優(yōu)配置參數(shù)設(shè)置不合理

總結(jié)

1 索引是排好序且快速查找的數(shù)據(jù)結(jié)構(gòu)。其目的是為了提高查詢的效率。

2 創(chuàng)建索引后，查詢數(shù)據(jù)變快，但更新數(shù)據(jù)變慢。

3 性能下降的原因很可能是索引失效導(dǎo)致。

4 索引創(chuàng)建的原則，經(jīng)常查詢的字段適合創(chuàng)建索引，頻繁需要更新的數(shù)據(jù)不適合創(chuàng)建索引。

5 索引字段頻繁更新，或者表數(shù)據(jù)物理刪除容易造成索引失效。

6 擅用 explain 分析sql語(yǔ)句

7 除了優(yōu)化sql語(yǔ)句外，還可以優(yōu)化表的設(shè)計(jì)。如盡量做成單表查詢，減少表之間的關(guān)聯(lián)。設(shè)計(jì)歸檔表等。

到這里，MySQL的索引優(yōu)化分析就結(jié)束了，有什么不對(duì)的地方，大家可以提出來(lái)。如果覺得不錯(cuò)可以點(diǎn)一下推薦。

參考文獻(xiàn)

MySQL order by排序優(yōu)化：?http://blog.51cto.com/ustb80/1073352

補(bǔ)充:

索引依托于存儲(chǔ)引擎的實(shí)現(xiàn)，因此，每種存儲(chǔ)引擎的索引都不一定完全相同，并且每種存儲(chǔ)引擎也不一定支持所有索引類型。所有存儲(chǔ)引擎支持每個(gè)表至少16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)。大多數(shù)存儲(chǔ)引擎有更高的額限制。

MySQL中索引的存儲(chǔ)類型有兩種：BTREE和HASH，具體和表的存儲(chǔ)引擎相關(guān)；

MyISAM和InnoDB存儲(chǔ)引擎只支持BTREE索引，MEMORY/HEAP存儲(chǔ)引擎可以支持HASH和BTREE索引。

普通索引

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);

ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

意思是查詢的時(shí)候，只需要檢索前面50個(gè)字符。

這里專門提一下，對(duì)字符串類型的字段進(jìn)行索引，如果可以盡可能的指定一個(gè)前綴長(zhǎng)度，例如，一個(gè)CHAR(255)的列，如果在前10個(gè)或者前30個(gè)字符內(nèi)，多數(shù)值是唯一的，則不需要對(duì)整個(gè)列進(jìn)行索引，短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間、減少I/O操作。

唯一索引

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。