?

知識點：Mysql 索引原理完全手冊(1)

知識點：Mysql 索引原理完全手冊(2)

知識點：Mysql 索引優化實戰(3)

知識點：Mysql 數據庫索引優化實戰(4)

一：插入訂單

業務邏輯：插入訂單數據，為了避免重復導單，一般會通過交易號去數據庫中查詢，判斷該訂單是否已經存在。

最基礎的sql語句

mysql> select * from book_order where order_id = "10000"; +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+ | id | order_id | general | net | stock_id | order_status | description | finance_desc | create_type | order_state | creator | create_time | +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+ | 10000 | 10000 | 6.6 | 6.13 | 1 | 10 | ok | ok | auto | 1 | itdragon | 2018-06-18 17:01:52 | +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+ mysql> explain select * from book_order where order_id = "10000"; +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | book_order | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

幾百上千萬的訂單，全表掃描!

通過explain命令可以清楚MySQL是如何處理sql語句的。打印的內容分別表示：

• id : 查詢序列號為1。
• select_type : 查詢類型是簡單查詢，簡單的select語句沒有union和子查詢。
• table : 表是 book_order。
• partitions : 沒有分區。
• type : 連接類型，all表示采用全表掃描的方式。
• possible_keys : 可能用到索引為null。
• key : 實際用到索引是null。
• key_len : 索引長度當然也是null。
• ref : 沒有哪個列或者參數和key一起被使用。
• Extra : 使用了where查詢。

是type為ALL，全表掃描，假設數據庫中有幾百萬條數據，在沒有索引的幫助下會異常卡頓。

初步優化：為order_id創建索引

mysql> create unique index idx_order_transaID on book_order (order_id); mysql> explain select * from book_order where order_id = "10000"; +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | book_order | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | NULL | +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

這里創建的索引是唯一索引，而非普通索引。

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

普通索引打印的type值是ref。表示非唯一性索引掃描。找到值還要繼續掃描，直到將索引文件掃描完為止。(這里沒有貼出代碼)，顯而易見，const的性能要遠高于ref。并且根據業務邏輯來判斷，創建唯一索引是合情合理的。

再次優化：覆蓋索引

mysql> explain select order_id from book_order where order_id = "10000"; +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | book_order | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | Using index | +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

這里將select * from改為了select order_id from后，Extra 顯示 Using index，表示該查詢使用了覆蓋索引，說明該sql語句的性能很好。若提示的是Using filesort(使用內部排序)和Using temporary(使用臨時表)則表明該sql需要立即優化了。

根據業務邏輯來的，查詢結構返回order_id 是可以滿足業務邏輯要求的。

二：查詢訂單

業務邏輯：優先處理訂單級別高，錄入時間長的訂單。

通過訂單級別和訂單錄入時間排序

最基礎的sql語句

mysql> explain select * from book_order order by order_state,create_time; +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | book_order | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

首先，采用全表掃描就不合理，還使用了文件排序Using filesort，更加拖慢了性能。 MySQL在4.1版本之前文件排序是采用雙路排序的算法，由于兩次掃描磁盤，I/O耗時太長。后優化成單路排序算法。其本質就是用空間換時間，但如果數據量太大，buffer的空間不足，會導致多次I/O的情況。其效果反而更差。與其找運維同事修改MySQL配置，還不如自己乖乖地建索引。

初步優化：為order_state,create_time 創建復合索引

mysql> create index idx_order_stateDate on book_order (order_state,create_time); mysql> explain select * from book_order order by order_state,create_time; +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | book_order | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

創建復合索引后你會驚奇的發現，和沒創建索引一樣？？？都是全表掃描，都用到了文件排序。是索引失效？還是索引創建失敗？

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

mysql> explain select order_state,create_time from book_order order by order_state,create_time; +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | book_order | NULL | index | NULL | idx_order_stateDate | 68 | NULL | 3 | 100 | Using index | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

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

MySQL沒有這么笨，可以使用force index 強制指定索引。在原來的sql語句上修改 force index(idx_order_stateDate) 即可。

mysql> explain select * from book_order force index(idx_order_stateDate) order by order_state,create_time; +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+ | 1 | SIMPLE | book_order | NULL | index | NULL | idx_order_stateDate | 68 | NULL | 3 | 100 | NULL | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

再次優化：訂單級別真的要排序么？

對于這種重復且分布平均的字段，排序和加索引的作用不大。

我們能否先固定 order_state 的值，然后再給 create_time 排序？

mysql> explain select * from book_order where order_state=3 order by create_time; +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | book_order | NULL | ref | idx_order_stateDate | idx_order_stateDate | 5 | const | 1 | 100 | Using index condition | +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

和之前的sql比起來，type從index 升級為 ref(非唯一性索引掃描)。索引的長度從68變成了5，說明只用了一個索引。ref也是一個常量。Extra 為Using index condition 表示自動根據臨界值，選擇索引掃描還是全表掃描?？偟膩碚f性能遠勝于之前的sql。

小結

建索引：

• 主鍵，唯一索引
• 經常用作查詢條件的字段需要創建索引
• 經常需要排序、分組和統計的字段需要建立索引
• 查詢中與其他表關聯的字段，外鍵關系建立索引

不要建索引：

• 百萬級以下的數據不需要創建索引
• 經常增刪改的表不需要創建索引
• 數據重復且分布平均的字段不需要創建索引
• 頻發更新的字段不適合創建索引
• where條件里用不到的字段不需要創建索引

talk is esay , show me the code

轉載于:https://www.cnblogs.com/yizhiamumu/p/9226142.html

總結

以上是生活随笔為你收集整理的知识点：Mysql 数据库索引优化实战(4)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。