文章主要介紹mysql性能下降的原因，索引的簡介，索引常見的原則，explain命令的使用，以及explain輸出字段的意義

我們先簡單了解一下非關(guān)系型數(shù)據(jù)庫和關(guān)系型數(shù)據(jù)庫的區(qū)別
MongoDB是nosql中的一種。nosql的全稱是not onle sql 非關(guān)系型數(shù)據(jù)庫。
它的特點是性能高，擴張性強，模式靈活，在高并發(fā)場景表現(xiàn)非常優(yōu)秀
但目前它還只是關(guān)系型數(shù)據(jù)庫的補充，它在數(shù)據(jù)的一致性，數(shù)據(jù)的安全性，查詢的復(fù)雜性問題上和關(guān)系型數(shù)據(jù)庫還存在一定差距
mysql是關(guān)系型數(shù)據(jù)庫中的一種，查詢功能強，數(shù)據(jù)一致性高，數(shù)據(jù)安全性高，支持二級索引，但其性能方面稍遜MongoDB，特別是百萬級別以上的數(shù)據(jù)，很容易出現(xiàn)查詢慢的現(xiàn)象。
查詢慢的原因，一般情況下是程序員sql寫的不好，或者是沒有建索引，或者是索引失效等原因造成的。
我們來看兩個場景：

1.訂單導(dǎo)入，通過訂單編號避免重復(fù)導(dǎo)單

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

select * from t_order where order_sn = "2001154648454";
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
| id    | order_sn    | stock_id | order_status | descript | create_type | order_level | input_date          |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
| 1 | 2001154648454|        1 |           10 | ok       | auto        |           1 |2019-05-18 12:21:45 |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
explain select * from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

上面這個查詢自身沒有任何問題，在測試環(huán)境中也沒發(fā)現(xiàn)任何的問題。但如果一旦放到正式環(huán)境，放到一個有幾百上千萬的訂單上，用全表掃描，那個酸爽，哈哈哈
通過explain命令我們可以清楚mysql是如何處理sql語句的：打印的內(nèi)容分別表
-id：查詢的序列號為1
-select_type:查詢類型是簡單查詢，簡單的select語句沒有union和子查詢
-table：表是t_order。
-partitions：沒有分區(qū)。
-type：連接類型，all表示采用全表掃描方式。
-possible_keys：可能用到的索引為null。
-key：實際用到的索引是null。
-key_len：索引長度為null。
-ref：沒有哪個列或者參數(shù)和key一起被使用。
-Extra：使用了where查詢。
-rows：掃描出的行數(shù)
-filtered：執(zhí)行情況的描述和說明
因為測試數(shù)據(jù)庫中的數(shù)據(jù)量不大，所以rows和filtered提供的信息作用不大。
這里需要重點了解的是type為ALL,全表掃描的性能是最差的，假設(shè)數(shù)據(jù)庫中有幾百萬條數(shù)據(jù)，在沒有索引的幫助下會異常慢

我們先來第一步優(yōu)化下：為order_sn創(chuàng)建索引

create unique index idx_order_sn on t_order (order_sn);
explain select * from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| 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  |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

上面我們針對order_sn創(chuàng)建的索引是唯一索引，不是普通索引。
唯一索引打印的type值是const,表示通過索引一次就可以找到了。即找到值就結(jié)束掃描返回查詢結(jié)果。
普通索引打印的type值如果是ref的話。表示非唯一性索引掃描。找到值還要繼續(xù)掃描，直到將索引文件掃描完為止。

通過上面描述：顯而易見，const的性能要遠高于ref。并且根據(jù)業(yè)務(wù)邏輯來判斷，創(chuàng)建唯一索引是比較合理的。

我們繼續(xù)再優(yōu)化：覆蓋索引

explain select order_sn from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| 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 改為了 order_sn 后 Extra 顯示Using index，表示該查詢使用了覆蓋索引

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

根據(jù)業(yè)務(wù)邏輯來，查詢結(jié)果返回order_sn也可以滿足業(yè)務(wù)邏輯要求。

2.訂單管理頁面，通過訂單級別和訂單錄入時間排序

具體業(yè)務(wù)邏輯：優(yōu)先處理訂單級別高，錄入時間長的訂單。
說到排序，我們首先會想到的應(yīng)該是order by,這里面還有一個可怕的Using filesort（使用內(nèi)部排序）等著你。

explain select * from t_order 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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

從上面的運行結(jié)果我們可以看出，采用了全表掃描，還使用了Using filesort內(nèi)部排序--文件排序，這兩個都把性能拖慢了。
Mysql在4.1版本之前文件排序是采用雙路排序算法，由于兩次掃描磁盤，I/O耗時太長。后優(yōu)化成單路排序算法。其本質(zhì)就是用空間換時間，但如果數(shù)據(jù)量太大，buffer的空間不足，會導(dǎo)致多次I/O的情況。其效果反而更差。

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

create index idx_order_leveldate on t_order (order_level,input_date);
explain select * from t_order 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ù)合索引后你會驚奇的發(fā)現(xiàn)，和沒創(chuàng)建索引一樣？？？？？？都是全表掃描，都用到了文件排序。那是索引失效？還是索引創(chuàng)建失敗呢？

我們試著看看下面打印情況

explain select order_level,input_date from t_order 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 |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

上面我們將 * 換成了order_level,input_date后。type從all升級為index,表示全索引文件掃描，Extra也顯示使用了索引。
但里面還是有問題，檢索雖然快了，但返回的內(nèi)容只有order_level和input_date兩個字段，讓業(yè)務(wù)同事怎么用？而且我們也不能把每個用到的字段都建一個復(fù)合索引吧？
不過這里好在mysql沒有這么笨，可以使用force index 強制指定索引。在原來的sql語句上修改force index(idx_order_leveldate )就可以了。

explain select * from t_order 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  |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

根據(jù)業(yè)務(wù)邏輯我們再次優(yōu)化一下：就是訂單級別真的有必要排序么
這里面order_level的值可能只有低、中、高、加急、超時這5種。對于這種重復(fù)且分布平均的字段，排序和加索引的作用不大。
那我們能否先固定order_level的值，然后再給input_date排序呢？

explain select * from t_order 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比起來，type從index升級為ref（非唯一性索引掃描）。索引的長度從68變成了5，說明之用了一個索引，ref也是一個常量。
Extra 為Using index condition 表示自動根據(jù)臨界值，選擇索引掃描還是全表掃描?？偟膩碚f性能遠勝于之前的sql。
通過上面的案例我們需嚴謹一點：優(yōu)化是基于業(yè)務(wù)邏輯來的。絕對不能為了優(yōu)化而擅自修改業(yè)務(wù)邏輯。