索引优化分析案例

预先准备好数据

  1. SET FOREIGN_KEY_CHECKS=0;
  2. DROP TABLE IF EXISTS `itdragon_order_list`;
  3. CREATE TABLE `itdragon_order_list` (
  4.   `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  5.   `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  6.   `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  7.   `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  8.   `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  9.   `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  10.   `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  11.   `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  12.   `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  13.   `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  14.   `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  15.   `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;
  19. 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');
  20. 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');
  21. 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');

逐步开始进行优化：

第一个案例：

  1. select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
  2. --通过查看执行计划发现type=all,需要进行全表扫描
  3. explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
  5. --优化一、为transaction_id创建唯一索引
  6.  create unique index idx_order_transaID on itdragon_order_list (transaction_id);
  7. --当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
  8.  explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
  10.  --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
  11.  explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

第二个案例

  1. --创建复合索引
  2. create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
  4. --创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
  5. explain select * from itdragon_order_list order by order_level,input_date;
  7. --可以使用force index强制指定索引
  8. explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
  9. --其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
  10. explain select * from itdragon_order_list where order_level=3 order by input_date;