MySQL中創(chuàng)建及優(yōu)化索引組織結構的思路
通過一個實際生產環(huán)境中的數(shù)據(jù)存取需求，分析如何設計此存儲結構，如何操縱存儲的數(shù)據(jù)，以及如何使操作的成本或代價更低，系統(tǒng)開銷最小。同時，讓更多初學者明白數(shù)據(jù)存儲的表上索引是如何一個思路組織起來的，希望起到一個參考模板的價值作用。
1.測試用例描述
測試用例為B2C領域，一張用于存儲用戶選購物品而生成的產品訂單信息表，不過去掉一些其他字段，以便用于測試，其表中的數(shù)據(jù)項也不特別描述，字段意思見表：
- 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 '訂單狀態(tài)',
- `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 '數(shù)量',
- `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的篩選率更高，可以減少掃描索引記錄個數(shù)，從而達到更高的效率，同時，下面即將會列出的SQL也告訴我們，有部分SQL語句的WHERE字句中只出現(xiàn)order_id字段，為此更加堅定我們必須把字段：order_id作為聯(lián)合主鍵索引的頭部，`goods_id`為聯(lián)合主鍵索引的尾部。
數(shù)據(jù)存儲表設計的小結：
設計用于存儲數(shù)據(jù)的表結構，首先要知道有哪些數(shù)據(jù)項，也即行內常說的數(shù)據(jù)流，以及各個數(shù)據(jù)項的屬性，比如存儲的數(shù)據(jù)類型、值域范圍及長度、數(shù)據(jù)完整性等要求，從而確定數(shù)據(jù)項的屬性定義。存儲的數(shù)據(jù)項信息確定之后，至少進行如下三步分析：
● 首先，確定哪些數(shù)據(jù)項或組合，可以作為記錄的唯一性標志;
● 其次，要確定對數(shù)據(jù)記錄有哪些操作，每個操作的頻率如何，對網站等類型應用，還需要區(qū)分前臺操作和后臺操作，也即分外部用戶的操作，還是內部用戶的操作;
● 最后，對作為數(shù)據(jù)記錄操作的條件部分的數(shù)據(jù)項，分析其數(shù)據(jù)項的篩選率如何，也即數(shù)據(jù)項不同值占總數(shù)據(jù)記錄數(shù)的比例關心，比例越接近1則是篩選率越好，以及各個值得分布率;
綜上所述，再讓數(shù)據(jù)修改性操作優(yōu)先級別高于只讀性操作，就可以創(chuàng)建一個滿足要求且性能較好的索引組織結構。
數(shù)據(jù)的存取設計,就涉及一塊非常重要的知識: 關系數(shù)據(jù)庫的基礎知識和關系數(shù)據(jù)理論的范式。對于范式的知識點，特別解釋下，建議學到BCNF范式為止，1NF、2NF、3NF和BCNF之間的差別，各自規(guī)避的問題、存在的缺陷都要一清二楚，但是在真實的工作環(huán)境中，不要任何存取設計都想向范式靠，用一句佛語準確點表達：空即是色，色即是空。
#p#
2.用于生成測試數(shù)據(jù)的存儲過程代碼
創(chuàng)建索引，就離不開表存儲的真實數(shù)據(jù)，為此編寫一個存儲過程近可能模擬真實生產環(huán)境中的數(shù)據(jù)，同時也方便大家使用此存儲過程，在自己的測試環(huán)境中，真實感受驗證，
存儲過程代碼：
- 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 ;
3.業(yè)務邏輯描述
● 非注冊用戶，或網站的注冊用戶不登陸，都能可選購買物品，生成訂單號對應的用戶UID為系統(tǒng)默認的;
● 訂單與用戶UID關聯(lián)、描述等信息，存儲其它的表中，通過訂單號的模式關聯(lián);
● 用戶的訂單信息，在未付款之前都可以再修改，付款之后則無法修改;
● 已經付費的訂單信息，自動發(fā)送到物流部門，進行后續(xù)工序的操作。處理完畢之后，會更新訂單中涉及物品的存儲位置信息;
● 定期讀取部分數(shù)據(jù)到數(shù)據(jù)倉庫分析系統(tǒng)，用于統(tǒng)計分析;
● 個人訂單查詢，前后臺都有;
● 購物記錄查詢顯示;
#p#
4.根據(jù)業(yè)務規(guī)則描述需要使用操縱數(shù)據(jù)的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’;
8條SQL語句按觸發(fā)其執(zhí)行的用戶分類：
● 前臺用戶點擊觸發(fā)的操作而會執(zhí)行的SQL語句為：(1)、(2)、(3);
● 后臺內部用戶點擊觸發(fā)的操作而會執(zhí)行的SQL語句為：(1)、(2)、(3)、(4)、(5)、(6)、(7);
● 后臺系統(tǒng)自動定期執(zhí)行：(4)、(5)、(6)、(7)，工作時間正常情況每隔15分鐘執(zhí)行一次，以檢查是否有已付款而沒有準備貨物的訂單、是否有收款而未發(fā)貨的訂單等;
● 統(tǒng)計分析系統(tǒng)定期導出數(shù)據(jù)而執(zhí)行的SQL語句為：(8)，頻率為每24小時一次;
我們再分析上述列出來的SQL，分為2類，一類是讀操作的SQL(備注：SELECT操作)，另外一類為修改性操作(備注：UPDATE、DELETE操作)，分別如下：
SELECT 的WHERE子句、GROUP BY子、ORDER BY 子句和HAVING 子句中，出現(xiàn)的字段：
(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子句中出現(xiàn)的條件字段：
(8). order_id+ goods_id
我們已經存在主鍵索引：PRIMARY KEY(order_id,`goods_id`)，另外考慮到此表數(shù)據(jù)的操作以SELECT和INSERT為主，UPDATE的SQL量其次，再根據(jù)上述SQL語句，為此我們可以初步確定需要創(chuàng)建的索引：
- 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);
#p#
總結：
文章中也分析了為何聯(lián)合主鍵索引的順序為：order_id,`goods_id`，再補充下作為主鍵的聯(lián)合索引的字段屬性的其他特性：字段值寫入之后不變化、字段值長度短且最好為數(shù)值類型;
對于編號SQL：(8)，每天按更新日期讀取一次數(shù)據(jù)的操作，以采用全表掃描的方式實現(xiàn)，犧牲其數(shù)據(jù)讀取的性能，以減少更新字段修改日期的值而帶來的索引維護開銷;
對于編號SQL：(4)、(5)，考慮到每次都是讀取最新的50條記錄，以及讀取的數(shù)據(jù)基本上可肯定為熱數(shù)據(jù)，為此不得不犧牲其中一條SQL的數(shù)據(jù)讀取性能，而少創(chuàng)建一個聯(lián)合索引，從而減少維護索引字段的IO量;
對于編號SQL：(6)、(7)，創(chuàng)建的聯(lián)合索引，需要特別注意聯(lián)合索引：idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)中的字段順序，其中：
● goods_id字段的篩選率高于order_type,order_status，另外gmt_modify字段只出現(xiàn)在ORDER BY子句中，為此只有讓goods_id字段作為聯(lián)合索引的頭部，以提高索引的篩選率，從而提高索引的效率，減少邏輯或物理的讀。
● order_status字段只有0或1兩種值，而order_type有多種，以及根據(jù)SQL語句，必須order_type出現(xiàn)在聯(lián)合中的位置要比order_status靠近頭部;
● gmt_modify字段出現(xiàn)在ORDER BY子句中，為此必須放到聯(lián)合索引字段的最后;
最后，再梳理一下從需求到設計存儲結構，再到編寫SQL和創(chuàng)建索引結構，我們應該做的步驟：
● 整理業(yè)務產生的數(shù)據(jù)流，讀取數(shù)據(jù)的方式;
● 整理清楚數(shù)據(jù)流中的每個數(shù)據(jù)項屬性信息;
● 分析業(yè)務指標，推測需要存儲數(shù)據(jù)的規(guī)模(備注：一定要以多少GB作為容量單位);
● 選擇可能用于支持業(yè)務的硬件設備和數(shù)據(jù)庫架構;
● 把所有可能操縱數(shù)據(jù)的條件和操作類型，都整理清楚;
● 分析操縱數(shù)據(jù)條件字段各自的數(shù)據(jù)篩選率;
● 權衡各個SQL的性能和IO量，也即類似于哪個操作權重高一些，那些操作權重適當?shù)鸵恍?
● 創(chuàng)建索引組織結構;
● 收集測試和生產環(huán)境的反饋信息，優(yōu)化索引組織結構;
備注：
本想再用測試環(huán)境結合業(yè)務的方式，跑一套模擬測試腳本程序，讓大家更加直觀地看到不同索引組織情況下，相同的SQL操作及頻率，數(shù)據(jù)庫服務器的處理能力和負載變化及對比信息，可惜唯一的服務器無法使用了，只好放棄。對于分析相同的SQL，走不通索引，其需要的邏輯IO和物理IO量也是一個辦法，此次就不分析了，有需要的朋友可以去玩玩。
【編輯推薦】
