通過(guò)一個(gè)實(shí)際生產(chǎn)環(huán)境中的數(shù)據(jù)存取需求，分析如何設(shè)計(jì)此存儲(chǔ)結(jié)構(gòu)，如何操縱存儲(chǔ)的數(shù)據(jù)，以及如何使操作的成本或代價(jià)更低，系統(tǒng)開(kāi)銷最小。同時(shí)，讓更多初學(xué)者明白數(shù)據(jù)存儲(chǔ)的表上索引是如何一個(gè)思路組織起來(lái)的，希望起到一個(gè)參考模板的價(jià)值作用。

1.測(cè)試用例描述

測(cè)試用例為B2C領(lǐng)域，一張用于存儲(chǔ)用戶選購(gòu)物品而生成的產(chǎn)品訂單信息表，不過(guò)去掉一些其他字段，以便用于測(cè)試，其表中的數(shù)據(jù)項(xiàng)也不特別描述，字段意思見(jiàn)表：

USE `test`;  
DROP TABLE IF EXISTS `test`.`goods_order`;  
CREATE TABLE `goods_order`(  
`order_id`        INT UNSIGNED      NOT NULL             COMMENT '訂單單號(hào)',  
`goods_id`        INT UNSIGNED      NOT NULL DEFAULT '0' COMMENT '商品款號(hào)',  
`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 '倉(cāng)庫(kù)id',  
`packet_id`       INT UNSIGNED  NOT NULL DEFAULT '0' COMMENT '儲(chǔ)位code',  
`gmt_create`      TIMESTAMP     NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加時(shí)間',  
`gmt_modify`      TIMESTAMP     NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新時(shí)間',  
PRIMARY KEY(order_id,`goods_id`)  
)ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; 

其中，主鍵信息：PRIMARY KEY(order_id,`goods_id`)，為何主鍵索引索引字段的順序?yàn)椋簅rder_id,`goods_id`，而不是： `goods_id`, order_id呢?原因很簡(jiǎn)單，goods_id在訂單信息表中的重復(fù)率會(huì)比order_id高，也即order_id的篩選率更高，可以減少掃描索引記錄個(gè)數(shù)，從而達(dá)到更高的效率，同時(shí)，下面即將會(huì)列出的SQL也告訴我們，有部分SQL語(yǔ)句的WHERE字句中只出現(xiàn)order_id字段，為此更加堅(jiān)定我們必須把字段：order_id作為聯(lián)合主鍵索引的頭部，`goods_id`為聯(lián)合主鍵索引的尾部。

數(shù)據(jù)存儲(chǔ)表設(shè)計(jì)的小結(jié)：

設(shè)計(jì)用于存儲(chǔ)數(shù)據(jù)的表結(jié)構(gòu)，首先要知道有哪些數(shù)據(jù)項(xiàng)，也即行內(nèi)常說(shuō)的數(shù)據(jù)流，以及各個(gè)數(shù)據(jù)項(xiàng)的屬性，比如存儲(chǔ)的數(shù)據(jù)類型、值域范圍及長(zhǎng)度、數(shù)據(jù)完整性等要求，從而確定數(shù)據(jù)項(xiàng)的屬性定義。存儲(chǔ)的數(shù)據(jù)項(xiàng)信息確定之后，至少進(jìn)行如下三步分析：

● 首先，確定哪些數(shù)據(jù)項(xiàng)或組合，可以作為記錄的唯一性標(biāo)志;

● 其次，要確定對(duì)數(shù)據(jù)記錄有哪些操作，每個(gè)操作的頻率如何，對(duì)網(wǎng)站等類型應(yīng)用，還需要區(qū)分前臺(tái)操作和后臺(tái)操作，也即分外部用戶的操作，還是內(nèi)部用戶的操作;

● 最后，對(duì)作為數(shù)據(jù)記錄操作的條件部分的數(shù)據(jù)項(xiàng)，分析其數(shù)據(jù)項(xiàng)的篩選率如何，也即數(shù)據(jù)項(xiàng)不同值占總數(shù)據(jù)記錄數(shù)的比例關(guān)心，比例越接近1則是篩選率越好，以及各個(gè)值得分布率;

綜上所述，再讓數(shù)據(jù)修改性操作優(yōu)先級(jí)別高于只讀性操作，就可以創(chuàng)建一個(gè)滿足要求且性能較好的索引組織結(jié)構(gòu)。

數(shù)據(jù)的存取設(shè)計(jì),就涉及一塊非常重要的知識(shí): 關(guān)系數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)和關(guān)系數(shù)據(jù)理論的范式。對(duì)于范式的知識(shí)點(diǎn)，特別解釋下，建議學(xué)到BCNF范式為止，1NF、2NF、3NF和BCNF之間的差別，各自規(guī)避的問(wèn)題、存在的缺陷都要一清二楚，但是在真實(shí)的工作環(huán)境中，不要任何存取設(shè)計(jì)都想向范式靠，用一句佛語(yǔ)準(zhǔn)確點(diǎn)表達(dá)：空即是色，色即是空。

#p#

2.用于生成測(cè)試數(shù)據(jù)的存儲(chǔ)過(guò)程代碼

創(chuàng)建索引，就離不開(kāi)表存儲(chǔ)的真實(shí)數(shù)據(jù)，為此編寫(xiě)一個(gè)存儲(chǔ)過(guò)程近可能模擬真實(shí)生產(chǎn)環(huán)境中的數(shù)據(jù)，同時(shí)也方便大家使用此存儲(chǔ)過(guò)程，在自己的測(cè)試環(huán)境中，真實(shí)感受驗(yàn)證，

存儲(chǔ)過(guò)程代碼：

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è)務(wù)邏輯描述

● 非注冊(cè)用戶，或網(wǎng)站的注冊(cè)用戶不登陸，都能可選購(gòu)買物品，生成訂單號(hào)對(duì)應(yīng)的用戶UID為系統(tǒng)默認(rèn)的;

● 訂單與用戶UID關(guān)聯(lián)、描述等信息，存儲(chǔ)其它的表中，通過(guò)訂單號(hào)的模式關(guān)聯(lián);

● 用戶的訂單信息，在未付款之前都可以再修改，付款之后則無(wú)法修改;

● 已經(jīng)付費(fèi)的訂單信息，自動(dòng)發(fā)送到物流部門(mén)，進(jìn)行后續(xù)工序的操作。處理完畢之后，會(huì)更新訂單中涉及物品的存儲(chǔ)位置信息;

● 定期讀取部分?jǐn)?shù)據(jù)到數(shù)據(jù)倉(cāng)庫(kù)分析系統(tǒng)，用于統(tǒng)計(jì)分析;

● 個(gè)人訂單查詢，前后臺(tái)都有;

● 購(gòu)物記錄查詢顯示;

#p#

4.根據(jù)業(yè)務(wù)規(guī)則描述需要使用操縱數(shù)據(jù)的SQL語(yǔ)句

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語(yǔ)句按觸發(fā)其執(zhí)行的用戶分類：

● 前臺(tái)用戶點(diǎn)擊觸發(fā)的操作而會(huì)執(zhí)行的SQL語(yǔ)句為：(1)、(2)、(3);

● 后臺(tái)內(nèi)部用戶點(diǎn)擊觸發(fā)的操作而會(huì)執(zhí)行的SQL語(yǔ)句為：(1)、(2)、(3)、(4)、(5)、(6)、(7);

● 后臺(tái)系統(tǒng)自動(dòng)定期執(zhí)行：(4)、(5)、(6)、(7)，工作時(shí)間正常情況每隔15分鐘執(zhí)行一次，以檢查是否有已付款而沒(méi)有準(zhǔn)備貨物的訂單、是否有收款而未發(fā)貨的訂單等;

● 統(tǒng)計(jì)分析系統(tǒng)定期導(dǎo)出數(shù)據(jù)而執(zhí)行的SQL語(yǔ)句為：(8)，頻率為每24小時(shí)一次;

我們?cè)俜治錾鲜隽谐鰜?lái)的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

我們已經(jīng)存在主鍵索引：PRIMARY KEY(order_id,`goods_id`)，另外考慮到此表數(shù)據(jù)的操作以SELECT和INSERT為主，UPDATE的SQL量其次，再根據(jù)上述SQL語(yǔ)句，為此我們可以初步確定需要?jiǎng)?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#

總結(jié)：

文章中也分析了為何聯(lián)合主鍵索引的順序?yàn)椋簅rder_id,`goods_id`，再補(bǔ)充下作為主鍵的聯(lián)合索引的字段屬性的其他特性：字段值寫(xiě)入之后不變化、字段值長(zhǎng)度短且最好為數(shù)值類型;

對(duì)于編號(hào)SQL：(8)，每天按更新日期讀取一次數(shù)據(jù)的操作，以采用全表掃描的方式實(shí)現(xiàn)，犧牲其數(shù)據(jù)讀取的性能，以減少更新字段修改日期的值而帶來(lái)的索引維護(hù)開(kāi)銷;

對(duì)于編號(hào)SQL：(4)、(5)，考慮到每次都是讀取最新的50條記錄，以及讀取的數(shù)據(jù)基本上可肯定為熱數(shù)據(jù)，為此不得不犧牲其中一條SQL的數(shù)據(jù)讀取性能，而少創(chuàng)建一個(gè)聯(lián)合索引，從而減少維護(hù)索引字段的IO量;

對(duì)于編號(hào)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語(yǔ)句，必須order_type出現(xiàn)在聯(lián)合中的位置要比order_status靠近頭部;

● gmt_modify字段出現(xiàn)在ORDER BY子句中，為此必須放到聯(lián)合索引字段的最后;

最后，再梳理一下從需求到設(shè)計(jì)存儲(chǔ)結(jié)構(gòu)，再到編寫(xiě)SQL和創(chuàng)建索引結(jié)構(gòu)，我們應(yīng)該做的步驟：

● 整理業(yè)務(wù)產(chǎn)生的數(shù)據(jù)流，讀取數(shù)據(jù)的方式;

● 整理清楚數(shù)據(jù)流中的每個(gè)數(shù)據(jù)項(xiàng)屬性信息;

● 分析業(yè)務(wù)指標(biāo)，推測(cè)需要存儲(chǔ)數(shù)據(jù)的規(guī)模(備注：一定要以多少GB作為容量單位);

● 選擇可能用于支持業(yè)務(wù)的硬件設(shè)備和數(shù)據(jù)庫(kù)架構(gòu);

● 把所有可能操縱數(shù)據(jù)的條件和操作類型，都整理清楚;

● 分析操縱數(shù)據(jù)條件字段各自的數(shù)據(jù)篩選率;

● 權(quán)衡各個(gè)SQL的性能和IO量，也即類似于哪個(gè)操作權(quán)重高一些，那些操作權(quán)重適當(dāng)?shù)鸵恍?

● 創(chuàng)建索引組織結(jié)構(gòu);

● 收集測(cè)試和生產(chǎn)環(huán)境的反饋信息，優(yōu)化索引組織結(jié)構(gòu);

備注：

本想再用測(cè)試環(huán)境結(jié)合業(yè)務(wù)的方式，跑一套模擬測(cè)試腳本程序，讓大家更加直觀地看到不同索引組織情況下，相同的SQL操作及頻率，數(shù)據(jù)庫(kù)服務(wù)器的處理能力和負(fù)載變化及對(duì)比信息，可惜唯一的服務(wù)器無(wú)法使用了，只好放棄。對(duì)于分析相同的SQL，走不通索引，其需要的邏輯IO和物理IO量也是一個(gè)辦法，此次就不分析了，有需要的朋友可以去玩玩。

【編輯推薦】

1. 教你如何利用MySQL學(xué)習(xí)MongoDB之SQL語(yǔ)法
2. 教你如何利用MySQL學(xué)習(xí)MongoDB之?dāng)?shù)據(jù)存儲(chǔ)結(jié)構(gòu)
3. 如何解決PHP+MySQL出現(xiàn)亂碼的現(xiàn)象
4. 教你如何利用MySQL學(xué)習(xí)MongoDB之安裝篇
5. MySQL配置時(shí)提示無(wú)法連接到MySQL本地服務(wù)器