一.等值查询
线上环境多次遇到表没有创建二级索引，只有主键索引。
SQL

/*====堆代码 duidaima.com ====*/
select sys_no,area_no,area_name,dc_no,dc_name,wh_no,wh_name, business_type,business_no,item_code,item_grade, item_result,item_remark,status, yn,ts,create_time,create_pin,update_time,update_pin
from
  evaluate_result
where
  yn = 1
  and wh_no = 'xxx'
  and business_no = 'xxx'
  and create_pin = 'xxx'
order by
  update_time desc;

# 执行用时
5 rows in set (7.311125 sec)

执行计划，显示全表扫描

表结构，显示查询字段无索引

mysql> show create table evaluate_result \G
*************************** 1. row ***************************
       Table: evaluate_result
Create Table: CREATE TABLE `evaluate_result` (
  `sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '内部主键',
  `wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT '仓库编码',
  `business_no` varchar(20) NOT NULL DEFAULT '' COMMENT '调研业务主键',
   ...
   PRIMARY KEY (`sys_no`)
) ENGINE=InnoDB AUTO_INCREMENT=2007412 DEFAULT CHARSET=utf8 COMMENT='评价结果表'
1 row in set (0.00 sec)

优化方法：创建索引

alter table evaluate_result add index idx_wh_bus_no(wh_no,business_no);

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: evaluate_result
   partitions: NULL
         type: ref
possible_keys: idx_wh_bus_no
          key: idx_wh_bus_no
      key_len: 160
          ref: const,const
         rows: 5
     filtered: 1.00
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

# 执行用时
5 rows in set (0.01 sec)

其中：
1. Extra: Using index condition，表明用到了索引下推；
2. key_len: 160，表明联合索引的两个字段都用到了，(32+20) * 3+2 *  2 = 160，其中 2 表示保存变长字段长度的存储空间。
等值查询索引生效的原因是相同值的数据组成单向链表，因此定位到满足条件的 5 行数据需要扫描的行数从  1377442 行降低到 5 行。

范围查询
SQL

select
  id
from
  board_chute
where
  status = 1
  and create_time <= date_sub(now(), interval 24 hour);

执行计划，显示全表扫描

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: ALL
possible_keys: idx_create_time
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 407632
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

查询字段有索引，但是索引失效

# 索引
  KEY `idx_create_time` (`create_time`),

# 条件
where
  status = 1
  and create_time <= date_sub(now(), interval 24 hour)

status 字段的区分度

mysql> select status,count(*) from board_chute group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|      0 |   407317 |
|      1 |     4309 |
+--------+----------+
2 rows in set (0.17 sec)

因此范围查询索引失效的原因是查看数据量大并且需要回表。

优化方法：创建联合索引实现覆盖索引

alter table board_chute add index idx_status_create_time(status, create_time);

执行计划，显示 Using index 表明用到了覆盖索引，不需要回表。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time,idx_status_create_time
          key: idx_status_create_time
      key_len: 8
          ref: NULL
         rows: 203816
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

范围查询索引生效的原因是叶子节点中除了保存索引，还保存指向下个节点的指针，因此遍历叶子节点就可以获得范围值。因此建议使用 between and 代替 in，如select * from T where k in (1,2,3,4,5);对应 5 次树的搜索，而select * from T where k between 1 and 5;对应 1 次树的搜索。

假设索引基于哈希表实现，可以通过散列函数将 key 值转换成一个固定的地址，如果发生哈希碰撞就在这个位置拉出一个链表。因此哈希表的优点是插入操作的速度快，根据 key 直接往后追加即可。但由于散列函数的离散特性，经过散列函数处理后的 key 将失去原有的顺序，所以哈希表无法满足范围查询，只适合等值查询。

注意上述索引生效的场景并非绝对成立，需要回表的记录越多，优化器越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

回表查询成本高有两点原因：
1.需要使用到两个 B+ 树索引，一个二级索引，一个聚簇索引；
2.访问二级索引使用顺序 I/O，访问聚簇索引使用随机 I/O。
因此有两条建议：
1.建议为区分度高的字段创建索引，并且将区分度高的字段优先放在联合索引前面；
2.建议优先使用覆盖索引，必须要回表时也需要控制回表的记录数，从而降低索引失效的风险。