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。
原文鏈接：http://arlxy.iteye.com/blog/1191029
【編輯推薦】
