mysql索引探索

《令人困惑的mysql索引》在这篇文章中，遗留了几个困惑。为了解决这些困惑，我又进行了一些测试。

本文中索引的建立，并不一定科学，查询的SQL也不一定科学，只是为了理解mysql索引建的一些测试数据。

CREATE TABLE `cms_reviews`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(100)  NOT NULL COMMENT '实体（对象）类型',
  `entity_id` varchar(100)  NOT NULL COMMENT '实体（对象）ID',
  `reviewer_id` varchar(100) NOT NULL DEFAULT '' COMMENT '评价人ID',
  `entity_relation_id` varchar(100)  NOT NULL DEFAULT '' COMMENT '实体（对象）关联ID',
  `first_review_id` int NULL DEFAULT NULL COMMENT '首评Id',
  `label` varchar(255)  NOT NULL DEFAULT '' COMMENT '标签（JSON格式）',
  `content` varchar(2000)  NOT NULL DEFAULT '' COMMENT '评价内容',
  `image_url` varchar(2000)  NOT NULL DEFAULT '' COMMENT '图片（JSON格式）',
  `video_url` varchar(2000)  NOT NULL DEFAULT '' COMMENT '视频（JSON格式）',
  `rating` varchar(2000)  NOT NULL DEFAULT '' COMMENT '评分（JSON格式）',
  `is_anonymous` tinyint NOT NULL DEFAULT 0 COMMENT '是否匿名：0否、1是',
  `is_default` tinyint NOT NULL DEFAULT 0 COMMENT '是否为系统默认评价：0否、1是',
  `types` tinyint NOT NULL DEFAULT 1 COMMENT '评价类型：1首评、2追评',
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态：1待审核、2审核通过、3审核拒绝、4举报成功、5下架',
  `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '已删除：0否、1是',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_created_at`(`created_at` ASC) USING BTREE,
  INDEX `idx_first_review_id`(`first_review_id` ASC) USING BTREE,
  INDEX `idx_entity_id` (`entity_id` ASC) USING BTREE
) ENGINE = InnoDB COMMENT = '评价主表';

当where条件和order条件中字段索引不一致，这种情况下可能会发生优化器索引选择。原来我的理解是，无论哪种情况下，应该都是命中where条件中索引，当然，前提是where条件中索引的区分度很高。但是，在上篇文章中，有些情况还是让我出乎意料，在某些情况下还是有可能走order条件中的索引。

比如，

SELECT * FROM `cms_reviews` WHERE (entity_type = 'DREAME_MALL_ORDER_GOODS_TEST' AND entity_id IN ('20010100001884') AND is_deleted = 0 AND status =1 ) AND types = 2 ORDER BY created_at desc  LIMIT 10

 这种情况下，只查排序中的前几条，就使用了order排序中的字段的索引。我尝试着理解一下，根据b+树的特点，在idx_created_at这颗顺序树中，在（内存）页中去匹配条件效率可能更高，因为，这种情况下，可能一次就查询到满足条件的数据。

但是，如果没有limit限制，如果还是基于idx_created_at这个索引，在内存页中去匹配查询条件，就必然需要遍历idx_created_at这颗树上所有节点，这种情况就没有根据where条件中的idx_entity_id筛选出来的数据在内存中进行排序效率高，所以，在这种情况下，就应该选择idx_entity_id这个索引。

我们看一下去掉limit的情况下，执行计划。

SELECT * FROM `cms_reviews` WHERE (entity_type = 'DREAME_MALL_ORDER_GOODS_TEST' AND entity_id IN ('20010100001884') AND is_deleted = 0 AND status =1 ) AND types = 2 ORDER BY created_at desc  

看了一下执行计划，符合我们上面的推理情况。

所以，应该还有一种情况，也是有可能走idx_entity_id这个索引，offset比较大，基于同样的理由，如果还是选择idx_created_at这个索引，那么，很大的可能是要遍历大部分的节点，才能筛选出满足条件的数据。

我们可以再来验证一下这种情况，看是否符合我们的预期。

SELECT * FROM `cms_reviews` WHERE (entity_type = 'DREAME_MALL_ORDER_GOODS_TEST' AND entity_id IN ('20010100001884') AND is_deleted = 0 AND status =1 ) AND types = 2 ORDER BY created_at desc  LIMIT 10 OFFSET 10000

果然，这种情况下走的idx_entity_id这个索引。

到了这里，上篇文章中的某些困惑也就能够理解了。

接下来，我们需要思考另外一个问题，既然如此，我们是否有必要选择建立联合索引呢？从理论上看建立联合索引的意义并不大，因为只有第一种case走了idx_created_at这个索引。我们不妨以实际测试数据说话。

我用entity_id和update_at两个字段建立联合索引，并对比查询。

SELECT * FROM `cms_reviews` WHERE (entity_type = 'DREAME_MALL_ORDER_GOODS_TEST' AND entity_id IN ('20010100001884') AND is_deleted = 0 AND status =1 ) AND types = 2 ORDER BY created_at desc  LIMIT 10 OFFSET 10000

SELECT * FROM `cms_reviews` WHERE (entity_type = 'DREAME_MALL_ORDER_GOODS_TEST' AND entity_id IN ('20010100001884') AND is_deleted = 0 AND status =1 ) AND types = 2 ORDER BY updated_at desc  LIMIT 10 OFFSET 10000

在单表40W的数据量的情况下查询。两者查询时间没有差别，查询时间在一两百毫秒。

在单表50W的数据量的情况下查询。两者查询时间没有差别，查询时间在一两百毫秒。