1. 案例1
建表
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id,默认自增长',
`transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
`gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
`net` double DEFAULT NULL COMMENT '净收入(RMB)',
`stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
`order_status` int(11) DEFAULT NULL COMMENT '订单状态',
`descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
`finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
`create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
`order_level` int(11) DEFAULT NULL COMMENT '订单级别',
`input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
`input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;
插入数据
INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');
表结构
id transaction_id gross net stock_id order_status descript finance_descript create_type order_level input_user input_date
------ ------------------ ------ ------ -------- ------------ -------- ---------------- ----------- ----------- ---------- ---------------------
10000 81X97310V32236260E 6.6 6.13 1 10 ok ok auto 1 itdragon 2017-08-28 17:01:49
10001 61525478BB371361Q 18.88 18.79 1 10 ok ok auto 1 itdragon 2017-08-18 17:01:50
10002 5RT64180WE555861V 20.18 20.17 1 10 ok ok auto 1 itdragon 2017-09-08 17:01:49
- 初始查询
EXPLAIN SELECT * FROM itdragon_order_list WHERE transaction_id = "81X97310V32236260E";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE itdragon_order_list (NULL) ALL (NULL) (NULL) (NULL) (NULL) 3 33.33 Using where
- 第一次优化:为transaction_id创建唯一索引
- 当创建索引之后,唯一索引对应的type是const,通过索引一次就可以找到结果,普通索引对应的type是ref,表示非唯一性索引赛秒,找到值还要进行扫描,直到将索引文件扫描完为止,显而易见,const的性能要高于ref
CREATE UNIQUE INDEX idx_order_transaID ON itdragon_order_list (transaction_id);
EXPLAIN SELECT * FROM itdragon_order_list WHERE transaction_id = "81X97310V32236260E";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------------ ------------------ ------- ------ ------ -------- --------
1 SIMPLE itdragon_order_list (NULL) const idx_order_transaID idx_order_transaID 453 const 1 100.00 (NULL)
- 第二次优化:使用覆盖索引,查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
EXPLAIN SELECT transaction_id FROM itdragon_order_list WHERE transaction_id = "81X97310V32236260E";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------------ ------------------ ------- ------ ------ -------- -------------
1 SIMPLE itdragon_order_list (NULL) const idx_order_transaID idx_order_transaID 453 const 1 100.00 Using index
- 第三次优化:创建复合索引
- 创建索引之后发现跟没有创建索引一样,都是全表扫描,都是文件排序
CREATE INDEX idx_order_levelDate ON itdragon_order_list (order_level,input_date);
EXPLAIN SELECT * FROM itdragon_order_list ORDER BY order_level,input_date;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------- ------ ------- ------ ------ -------- ----------------
1 SIMPLE itdragon_order_list (NULL) ALL (NULL) (NULL) (NULL) (NULL) 3 100.00 Using filesort
- 第四次优化:可以使用force index强制指定索引
EXPLAIN SELECT * FROM itdragon_order_list FORCE INDEX(idx_order_levelDate) ORDER BY order_level,input_date;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------- ------------------- ------- ------ ------ -------- --------
1 SIMPLE itdragon_order_list (NULL) index (NULL) idx_order_levelDate 68 (NULL) 3 100.00 (NULL)
- 第五次优化:其实给订单排序意义不大,给订单级别添加索引意义也不大,因此可以先确定order_level的值,然后再给input_date排序
EXPLAIN SELECT * FROM itdragon_order_list WHERE order_level=3 ORDER BY input_date;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------------------- ---------- ------ ------------------- ------------------- ------- ------ ------ -------- --------
1 SIMPLE itdragon_order_list (NULL) ref idx_order_levelDate idx_order_levelDate 5 const 1 100.00 (NULL)