MySQL索引失效

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。一般情况下我们建立索引的字段是在查询语句中经常使用的字段，实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。那么为了能有效的使用索引，提高效率，就需要规避索引失效的情况。
以下是我测试的表结构

CREATE TABLE `trade` (
  `trade_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `trade_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `shop_id` int(11) NOT NULL DEFAULT '0' COMMENT '店铺ID',
  `goods_id` int(11) NOT NULL DEFAULT '0' COMMENT '货品ID',
  `goods_num` int(11) NOT NULL DEFAULT '0' COMMENT '货品数量',
  `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`trade_id`),
  KEY `IX_trade_status` (`trade_status`) USING BTREE,
  KEY `IX_shop_goods_num` (`shop_id`,`goods_id`,`goods_num`) USING BTREE,
  KEY `IX_remark` (`remark`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中使用了三个索引，一个是订单状态，一个是联合索引对应字段店铺ID、货品ID和数量，还有一个备注，这个索引字段是字符串。

1、like以百分号开头

如果索引字段是文本，并且查询条件中以“%”开头，那么索引会失效。但是如果开头是固定文字，然后后续使用“%”，索引正常生效。
如下两个sql：

EXPLAIN SELECT * FROM trade WHERE remark LIKE "换物流%"

该sql正常使用了索引IX_remark，但是如果将“%”放在前，则不会使用：

EXPLAIN SELECT * FROM trade WHERE remark LIKE "%换物流%"

2、联合索引不符合最左原则

最左原则就是指，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合，所以在建立联合索引的时候查询最频繁的条件要放在左边。例如我们建立的店铺-货品-数量索引，比如使用了店铺的筛选条件索引才会生效，只使用其他的索引是不会生效的。
例如下面的sql，虽然这个联合索引我没有使用全只用了两个字段，但是也是生效的。

EXPLAIN SELECT * FROM trade WHERE shop_id = 2 AND goods_id = 10

但是下面这个sql，只使用了后面两个字段则是不生效的。

EXPLAIN SELECT * FROM trade WHERE goods_id = 10 AND goods_num = 5

关于最左原则，原理就是在存储索引的时候是按照B+树存储的，并且每一条索引都是按照字段顺序进行存储，那么排序也是按照字段顺序一个一个进行排序，也就是先按照shop_id排序，那么我搜索条件只使用了goods_id就是没有效果的，因为仅凭goods_id是没有办法在索引表中定位的，所以索引也就失效了。

3、使用不明确判断被查询优化器优化

我们进行查询的时候经常会使用大于或者小于这种条件，那么使用这种不明确的查询的时候索引能否生效呢，其实是不确定的。
例如使用trade_status这个字段，本身它是索引字段，然后现在的取值有10,20,30,40,100,110，其中要查询状态值小于40的数据：

EXPLAIN SELECT * FROM trade WHERE trade_status < 40

我们可以看到，possible_kyes是有IX_trade_status的，但是key中并没有使用成功，说明这个索引是被查询优化器优化掉了，因为查询出的数据偏多，不如全表扫描效率高。
但是如果我换一个范围，大于100的状态，现在表内是有500多条数据，状态等于110的只有2条，也就是真正的结果集只有两条，这个时候是会使用索引的。

EXPLAIN SELECT * FROM trade WHERE trade_status > 100

明显可以看到，正常使用了IX_trade_status的索引，所以在使用大于、小于、不等于的情况下，索引是否使用是不确定的，但是大部分情况下是会被优化掉的，只有在结果集很小，效率明显比全表扫描快的时候索引才会生效，所以在日常使用中还是要避免使用这种判断，以免索引失效。

小结

暂时先更新这些，后续还会有其他索引失效的情况进行举例，例如or语句前后没有同事使用索引、数据类型错误、使用函数等。虽然使用的表和字段都比较简单，但是可以相对直观的感受到索引的使用情况，使用好索引才能更好地提高效率。