MySQL索引優化分析
為什麼你寫的sql查詢慢?為什麼你建的索引常失效?通過本章內容,你將學會MySQL性能下降的原因,索引的簡介,索引創建的原則,explain命令的使用,以及explain輸出欄位的意義。助你了解索引,分析索引,使用索引,從而寫出更高性能的sql語句。還在等啥子?擼起袖子就是干!
案例分析
我們先簡單了解一下非關係型資料庫和關係型資料庫的區別。
MongoDB是NoSQL中的一種。NoSQL的全稱是Not only SQL,非關係型資料庫。它的特點是性能高,擴張性強,模式靈活,在高並發場景表現得尤為突出。但目前它還只是關係型資料庫的補充,它在數據的一致性,數據的安全性,查詢的複雜性問題上和關係型資料庫還存在一定差距。MySQL是關係性資料庫中的一種,查詢功能強,數據一致性高,數據安全性高,支持二級索引。但性能方面稍遜與MongoDB,特別是百萬級別以上的數據,很容易出現查詢慢的現象。這時候需要分析查詢慢的原因,一般情況下是程序員sql寫的爛,或者是沒有鍵索引,或者是索引失效等原因導致的。公司ERP系統資料庫主要是MongoDB(最接近關係型數據的NoSQL),其次是Redis,MySQL只佔很少的部分。現在又重新使用MySQL,歸功於阿里巴巴的奇門系統和聚石塔系統。考慮到訂單數量已經是百萬級以上,對MySQL的性能分析也就顯得格外重要。我們先通過兩個簡單的例子來入門。後面會詳細介紹各個參數的作用和意義。
場景一:訂單導入,通過交易號避免重複導單
業務邏輯:訂單導入時,為了避免重複導單,一般會通過交易號去資料庫中查詢,判斷該訂單是否已經存在。
最基礎的sql語句
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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
查詢的本身沒有任何問題,在線下的測試環境也沒有任何問題。可是,功能一旦上線,查詢慢的問題就迎面而來。幾百上千萬的訂單,用全表掃描?啊?哼!
怎麼知道該sql是全表掃描呢?通過explain命令可以清楚MySQL是如何處理sql語句的。列印的內容分別表示:id : 查詢序列號為1。select_type : 查詢類型是簡單查詢,簡單的select語句沒有union和子查詢。table : 表是 itdragon_order_list。partitions : 沒有分區。type : 連接類型,all表示採用全表掃描的方式。possible_keys : 可能用到索引為null。key : 實際用到索引是null。
key_len : 索引長度當然也是null。ref : 沒有哪個列或者參數和key一起被使用。Extra : 使用了where查詢。因為資料庫中只有三條數據,所以rows和filtered的信息作用不大。這裡需要重點了解的是type為ALL,全表掃描的性能是最差的,假設資料庫中有幾百萬條數據,在沒有索引的幫助下會異常卡頓。初步優化:為transaction_id創建索引
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 |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
這裡創建的索引是唯一索引,而非普通索引。
唯一索引列印的type值是const。表示通過索引一次就可以找到。即找到值就結束掃描返回查詢結果。普通索引列印的type值是ref。表示非唯一性索引掃描。找到值還要繼續掃描,直到將索引文件掃描完為止。(這裡沒有貼出代碼)顯而易見,const的性能要遠高於ref。並且根據業務邏輯來判斷,創建唯一索引是合情合理的。再次優化:覆蓋索引
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,表示該查詢使用了覆蓋索引,這是一個非常好的消息,說明該sql語句的性能很好。若提示的是Using filesort(使用內部排序)和Using temporary(使用臨時表)則表明該sql需要立即優化了。
根據業務邏輯來的,查詢結構返回transaction_id 是可以滿足業務邏輯要求的。場景二,訂單管理頁面,通過訂單級別和訂單錄入時間排序
業務邏輯:優先處理訂單級別高,錄入時間長的訂單。
既然是排序,首先想到的應該是order by, 還有一個可怕的 Using filesort 等著你。最基礎的sql語句
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耗時太長。後優化成單路排序演算法。其本質就是用空間換時間,但如果數據量太大,buffer的空間不足,會導致多次I/O的情況。其效果反而更差。與其找運維同事修改MySQL配置,還不如自己乖乖地建索引。初步優化:為order_level,input_date 創建複合索引
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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
創建複合索引後你會驚奇的發現,和沒創建索引一樣???都是全表掃描,都用到了文件排序。是索引失效?還是索引創建失敗?我們試著看看下面列印情況
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升級為index,表示(full index scan)全索引文件掃描,Extra也顯示使用了覆蓋索引。可是不對啊!!!!檢索雖然快了,但返回的內容只有order_level和input_date 兩個欄位,讓業務同事怎麼用?難道把每個欄位都建一個複合索引?
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 |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
再次優化:訂單級別真的要排序么?
其實給訂單級別排序意義並不大,給訂單級別添加索引意義也不大。因為order_level的值可能只有,低,中,高,加急,這四種。對於這種重複且分布平均的欄位,排序和加索引的作用不大。
我們能否先固定 order_level 的值,然後再給 input_date 排序?如果查詢效果明顯,是可以推薦業務同事使用該查詢方式。
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比起來,type從index 升級為 ref(非唯一性索引掃描)。索引的長度從68變成了5,說明只用了一個索引。ref也是一個常量。Extra 為Using index condition 表示自動根據臨界值,選擇索引掃描還是全表掃描。總的來說性能遠勝於之前的sql。
上面兩個案例只是快速入門,我們需嚴記一點:優化是基於業務邏輯來的。絕對不能為了優化而擅自修改業務邏輯。如果能修改當然是最好的。
索引簡介
官方定義:索引(Index) 是幫助MySQL高效獲取數據的數據結構。
大家一定很好奇,索引為什麼是一種數據結構,它又是怎麼提高查詢的速度?我們拿最常用的二叉樹來分析索引的工作原理。看下面的圖片: