MySQL中建立及最佳化索引組織圖的思路

測試案例描述

測試案例為B2C領域，一張用於儲存使用者選購物品而產生的產品訂單資訊表，不過去掉一些其他欄位，以便用於測試，其表中的資料項目也不特別描述，欄位意思見表

 

  
USE `test`; 
  
DROP TABLE IF EXISTS `test`.`goods_order`; 
  
CREATE TABLE `goods_order`( 
  
`order_id`        INT UNSIGNED      NOT NULL             COMMENT ‘訂單單號’, 
  
`goods_id`        INT UNSIGNED      NOT NULL DEFAULT ’0′ COMMENT ‘商品款號’, 
  
`order_type`      TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘訂單類型’, 
  
`order_status`    TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘訂單狀態’, 
  
`color_id`        SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘顏色id’, 
  
`size_id`         SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘尺寸id’, 
  
`goods_number`    MEDIUMINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘數量’, 
  
`depot_id`        INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘倉庫id’, 
  
`packet_id`       INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘儲位code’, 
  
`gmt_create`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘添加時間’, 
  
`gmt_modify`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘更新時間’, 
  
PRIMARY KEY(order_id,`goods_id`) 
  
)ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’; 
 

其中，主鍵資訊：PRIMARY KEY(order_id,`goods_id`)，為何主鍵索引索引欄位的順序為：order_id,`goods_id`，而不是： `goods_id`, order_id呢?原因很簡單，goods_id在訂單資訊表中的重複率會比order_id高，也即order_id的篩選率更高，可以減少掃描索引記錄個數，從而達到更高的效率，同時，下面即將會列出的SQL 也告訴我們，有部分SQL 語句的WHERE字句中只出現order_id欄位，為此更加堅定我們必須把欄位：order_id作為聯合主鍵索引的頭部，`goods_id`為聯合主鍵索引的尾部。

資料存放區表設計的小結：

設計用於儲存資料的表結構，首先要知道有哪些資料項目，也即行內常說的資料流，以及各個資料項目的屬性，比如儲存的資料類型、範圍範圍及長度、資料完整性等要求，從而確定資料項目的屬性定義。儲存的資料項目資訊確定之後，至少進行如下三步分析：

•  首先，確定哪些資料項目或組合，可以作為記錄的唯一性標誌;
•  其次，要確定對資料記錄有哪些操作，每個操作的頻率如何，對網站等類型應用，還需要區分前台操作和後台操作，也即分外部使用者的操作，還是內部使用者的操作;
•  最後，對作為資料記錄操作的條件部分的資料項目，分析其資料項目的篩選率如何，也即資料項目不同值佔總資料記錄數的比例關心，比例越接近1則是篩選率越好，以及各個值得分布率;

綜上所述，再讓資料修改性操作優先順序別高於唯讀性操作，就可以建立一個滿足要求且效能較好的索引組織圖。

資料的存取設計,就涉及一塊非常重要的知識: 關聯式資料庫的基礎知識和關係資料理論的範式。對於範式的知識點，特別解釋下，建議學到BCNF範式為止，1NF、2NF、3NF和BCNF之間的差別，各自規避的問題、存在的缺陷都要一清二楚，但是在真實的工作環境中，不要任何存取設計都想向範式靠，用一句佛語準確點表達：空即是色，色即是空。

用於產生測試資料的預存程序代碼

建立索引，就離不開表格儲存體的真實資料，為此編寫一個預存程序近可能類比真實生產環境中的資料，同時也方便大家使用此預存程序，在自己的測試環境中，真實感受驗證，

預存程序代碼：

 

  
DELIMITER $$ 
  
DROP PROCEDURE IF EXISTS `usp_make_data` $$ 
  
CREATE PROCEDURE `usp_make_data`() 
  
BEGIN 
  
DECLARE iv_goods_id INT UNSIGNED DEFAULT 0; 
  
DECLARE iv_depot_id INT UNSIGNED DEFAULT 0; 
  
DECLARE iv_packet_id INT UNSIGNED DEFAULT 0; 
  
  
  
SET iv_goods_id=5000; 
  
SET iv_depot_id=10; 
  
SET iv_packet_id=20; 
  
  
  
WHILE iv_goods_id>0 
  
DO 
  
START  TRANSACTION; 
  
WHILE iv_depot_id>0 
  
DO 
  
WHILE iv_packet_id>0 
  
DO 
  
INSERT INTO goods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify) 
  
VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2), 
  
iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),2,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY) 
  
); 
  
SET iv_packet_id=iv_packet_id-1; 
  
END WHILE; 
  
SET iv_packet_id=20; 
  
SET iv_depot_id=iv_depot_id-1; 
  
END WHILE ; 
  
  
  
COMMIT; 
  
SET iv_depot_id=10; 
  
SET iv_goods_id=iv_goods_id-1; 
  
END WHILE ; 
  
END $$ 
  
DELIMITER ; 
 

商務邏輯描述

•  非註冊使用者，或網站的註冊使用者不登陸，都能可選購買物品，產生訂單號對應的使用者UID為系統預設的;
•  訂單與使用者UID關聯、描述等資訊，儲存其它的表中，通過訂單號的模式關聯;
•  使用者的訂單資訊，在未付款之前都可以再修改，付款之後則無法修改;
•  已經付費的訂單資訊，自動發送到物流部門，進行後續工序的操作。處理完畢之後，會更新訂單中涉及物品的儲存位置資訊;
•  定期讀取部分資料到資料倉儲分析系統，用於統計分析;
•  個人訂單查詢，前後台都有;
•  購物記錄查詢顯示;

根據商務規則描述需要使用操縱資料的SQL 陳述式

 

  
EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986; 
  
SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC; 
  
UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248; 
  
SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; 
  
SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; 
  
SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 
  
SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50; 
  
SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’; 
 

•  前台使用者點擊觸發的操作而會執行的SQL 陳述式為：01、02、03;
•  後台內部使用者點擊觸發的操作而會執行的SQL 陳述式為：01、02、03、04、05、06、07;
•  後台系統自動定期執行：04、05、06、07，工作時間正常情況每隔15分鐘執行一次，以檢查是否有已付款而沒有準備貨物的訂單、是否有收款而未發貨的訂單等;
•  統計分析系統定期匯出資料而執行的SQL 陳述式為：08，頻率為每24小時一次;

我們再分析上述列出來的SQL ，分為2類，一類是讀操作的SQL (備忘：SELECT操作)，另外一類為修改性操作(備忘：UPDATE、DELETE操作)，分別如下：

SELECT 的WHERE子句、GROUP BY子、ORDER BY 子句和HAVING 子句中，出現的欄位：

(1). order_id

(2). order_id+gmt_modify

(3). depot_id+gmt_modify

(4). depot_id+packet_id+gmt_modify

(5). goods_id+order_status+order_type

(6). goods_id+order_status+order_type+gmt_modify

(7). gmt_modify

修改性操作的WHERE子句中出現的條件欄位：

(8). order_id+ goods_id

我們已經存在主鍵索引：PRIMARY KEY(order_id,`goods_id`)，另外考慮到此表資料的操作以SELECT和INSERT為主，UPDATE的SQL 量其次，再根據上述SQL 語句，為此我們可以初步確定需要建立的索引：

 

  
ALTER TABLE goods_order 
  
ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify), 
  
ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify); 
 

總結：

文章中也分析了為何聯合主鍵索引的順序為：order_id,`goods_id`，再補充下作為主鍵的聯合索引的欄位屬性的其他特性：欄位值寫入之後不變化、欄位值長度短且最好為數實值型別;

對於編號SQL ：(8)，每天按更新日期讀取一次資料的操作，以採用全表掃描的方式實現，犧牲其資料讀取的效能，以減少更新欄位修改日期的值而帶來的索引維護開銷;

對於編號SQL ：(4)、(5)，考慮到每次都是讀取最新的50條記錄，以及讀取的資料基本上可肯定為熱資料，為此不得不犧牲其中一條SQL 的資料讀取效能，而少建立一個聯合索引，從而減少維護索引欄位的IO量;

對於編號SQL ：(6)、(7)，建立的聯合索引，需要特別注意聯合索引：idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)中的欄位順序，其中：

•  goods_id欄位的篩選率高於order_type,order_status，另外gmt_modify欄位只出現在ORDER BY子句中，為此只有讓goods_id欄位作為聯合索引的頭部，以提高索引的篩選率，從而提高索引的效率，減少邏輯或物理的讀。
•  order_status欄位只有0或1兩種值，而order_type有多種，以及根據SQL 語句，必須order_type出現在聯合中的位置要比order_status靠近頭部;
•  gmt_modify欄位出現在ORDER BY子句中，為此必須放到聯合索引欄位的最後;

最後，再梳理一下從需求到設計儲存結構，再到編寫SQL 和建立索引結構，我們應該做的步驟：

•  整理業務產生的資料流，讀取資料的方式;
•  整理清楚資料流中的每個資料項目屬性資訊;
•  分析業務指標，推測需要儲存資料的規模(備忘：一定要以多少GB 作為容量單位);
•  選擇可能用於支援業務的硬體裝置和資料庫結構描述;
•  把所有可能操縱資料的條件和操作類型，都整理清楚;
•  分析操縱資料條件欄位各自的資料篩選率;
•  權衡各個SQL 的效能和IO量，也即類似於哪個操作權重高一些，那些操作權重適當低一些;
•  建立索引組織圖;
•  收集測試和生產環境的反饋資訊，最佳化索引組織圖;

備忘：

本想再用測試環境結合業務的方式，跑一套類比測試指令碼程式，讓大家更加直觀地看到不同索引組織情況下，相同的SQL 操作及頻率，資料庫伺服器的處理能力和負載變化及對比資訊，可惜唯一的伺服器無法使用了，只好放棄。對於分析相同的SQL ，走不通索引，其需要的邏輯IO和物理IO量也是一個辦法，此次就不分析了，有需要的朋友可以去玩玩，另外建議初學者一定要好好閱讀下mysql 手冊上的相關章節內容：7.2.6. Index Merge Optimization。