測試用例描述

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

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

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

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

•  首先，確定哪些數(shù)據(jù)項(xiàng)或組合，可以作為記錄的唯一性標(biāo)志;
•  其次，要確定對數(shù)據(jù)記錄有哪些操作，每個操作的頻率如何，對網(wǎng)站等類型應(yīng)用，還需要區(qū)分前臺操作和后臺操作，也即分外部用戶的操作，還是內(nèi)部用戶的操作;
•  最后，對作為數(shù)據(jù)記錄操作的條件部分的數(shù)據(jù)項(xiàng)，分析其數(shù)據(jù)項(xiàng)的篩選率如何，也即數(shù)據(jù)項(xiàng)不同值占總數(shù)據(jù)記錄數(shù)的比例關(guān)心，比例越接近1則是篩選率越好，以及各個值得分布率;

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

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

用于生成測試數(shù)據(jù)的存儲過程代碼

創(chuàng)建索引，就離不開表存儲的真實(shí)數(shù)據(jù)，為此編寫一個存儲過程近可能模擬真實(shí)生產(chǎn)環(huán)境中的數(shù)據(jù)，同時也方便大家使用此存儲過程，在自己的測試環(huán)境中，真實(shí)感受驗(yà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 ; 

業(yè)務(wù)邏輯描述

•  非注冊用戶，或網(wǎng)站的注冊用戶不登陸，都能可選購買物品，生成訂單號對應(yīng)的用戶UID為系統(tǒng)默認(rèn)的;
•  訂單與用戶UID關(guān)聯(lián)、描述等信息，存儲其它的表中，通過訂單號的模式關(guān)聯(lián);
•  用戶的訂單信息，在未付款之前都可以再修改，付款之后則無法修改;
•  已經(jīng)付費(fèi)的訂單信息，自動發(fā)送到物流部門，進(jìn)行后續(xù)工序的操作。處理完畢之后，會更新訂單中涉及物品的存儲位置信息;
•  定期讀取部分?jǐn)?shù)據(jù)到數(shù)據(jù)倉庫分析系統(tǒng)，用于統(tǒng)計(jì)分析;
•  個人訂單查詢，前后臺都有;
•  購物記錄查詢顯示;

根據(jù)業(yè)務(wù)規(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’; 

•  前臺用戶點(diǎn)擊觸發(fā)的操作而會執(zhí)行的SQL 語句為：01、02、03;
•  后臺內(nèi)部用戶點(diǎn)擊觸發(fā)的操作而會執(zhí)行的SQL 語句為：01、02、03、04、05、06、07;
•  后臺系統(tǒng)自動定期執(zhí)行：04、05、06、07，工作時間正常情況每隔15分鐘執(zhí)行一次，以檢查是否有已付款而沒有準(zhǔn)備貨物的訂單、是否有收款而未發(fā)貨的訂單等;
•  統(tǒng)計(jì)分析系統(tǒng)定期導(dǎo)出數(shù)據(jù)而執(zhí)行的SQL 語句為：08，頻率為每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

我們已經(jīng)存在主鍵索引：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); 

總結(jié)：

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

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

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

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

•  整理業(yè)務(wù)產(chǎn)生的數(shù)據(jù)流，讀取數(shù)據(jù)的方式;
•  整理清楚數(shù)據(jù)流中的每個數(shù)據(jù)項(xiàng)屬性信息;
•  分析業(yè)務(wù)指標(biāo)，推測需要存儲數(shù)據(jù)的規(guī)模(備注：一定要以多少GB 作為容量單位);
•  選擇可能用于支持業(yè)務(wù)的硬件設(shè)備和數(shù)據(jù)庫架構(gòu);
•  把所有可能操縱數(shù)據(jù)的條件和操作類型，都整理清楚;
•  分析操縱數(shù)據(jù)條件字段各自的數(shù)據(jù)篩選率;
•  權(quán)衡各個SQL 的性能和IO量，也即類似于哪個操作權(quán)重高一些，那些操作權(quán)重適當(dāng)?shù)鸵恍?
•  創(chuàng)建索引組織結(jié)構(gòu);
•  收集測試和生產(chǎn)環(huán)境的反饋信息，優(yōu)化索引組織結(jié)構(gòu);

備注：

本想再用測試環(huán)境結(jié)合業(yè)務(wù)的方式，跑一套模擬測試腳本程序，讓大家更加直觀地看到不同索引組織情況下，相同的SQL 操作及頻率，數(shù)據(jù)庫服務(wù)器的處理能力和負(fù)載變化及對比信息，可惜唯一的服務(wù)器無法使用了，只好放棄。對于分析相同的SQL ，走不通索引，其需要的邏輯IO和物理IO量也是一個辦法，此次就不分析了，有需要的朋友可以去玩玩，另外建議初學(xué)者一定要好好閱讀下mysql 手冊上的相關(guān)章節(jié)內(nèi)容：7.2.6. Index Merge Optimization。

原文鏈接：http://arlxy.iteye.com/blog/1191029

【編輯推薦】

1. 說說一些有用的MySQL語句
2. Craigslist采用MongoDB替代MySQL
3. MySQL中的NoSQL插件
4. SQL與NoSQL——MySQL與NoSQL的融合
5. 論MySQL何時使用索引，何時不使用索引