数据库——MySQL索引优化分析3

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)