令人困惑的mysql索引

我以为我已经理解了mysql的索引的原理，但是，Explain之后，又陷入了令我困惑的境地，看来还需要持续学习其底层原理，才能慢慢解开诸多困惑。这篇文章，只做简要的记录，如果有大佬精通，可以帮忙指点一二，不胜感激。

闲言少叙，进入文章的主题。

有这么一张表。

CREATE TABLE if NOT EXISTS `db_dreame_store`.`order` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增',
      `uid` varchar(50)  NOT NULL DEFAULT '' COMMENT '用户id',
      `email` varchar(50)  NOT NULL DEFAULT '' COMMENT '用户邮箱',
      `order_id` varchar(50)  NOT NULL DEFAULT '' COMMENT '订单id',
      `product_id` varchar(50)  NOT NULL DEFAULT '' COMMENT '产品id',
      `product_title` varchar(100)  NOT NULL DEFAULT '' COMMENT '产品名',
      `product_type` varchar(50)  NOT NULL DEFAULT '' COMMENT '订单类型：延保订单',
      `product_model` varchar(50) NOT NULL DEFAULT '' COMMENT '产品模型',
      `ignore_color` tinyint NOT NULL DEFAULT 0 COMMENT '忽略颜色0：否 1：是',
      `varant_id` varchar(50)  NOT NULL DEFAULT '' COMMENT '商品id',
      `current_quantity`int  NOT NULL DEFAULT 0 COMMENT '购买的商品数量',
      `extended_warranty_period` INT NOT NULL DEFAULT 0 COMMENT '延保时长（月）',
      `tag` varchar(255)  NOT NULL DEFAULT '' COMMENT '标签:延保时长，产品模型等',
      `country_code` varchar(10)  NOT NULL DEFAULT '' COMMENT '国家英文名缩写',
      `total_shop_money_amount` bigint  NOT NULL DEFAULT 0 COMMENT '订单店铺总金额',
      `total_shop_money_currency_code` varchar(10)  NOT NULL DEFAULT '' COMMENT '店铺金额货币代码',
      `total_presentment_money_amount` bigint  NOT NULL DEFAULT 0 COMMENT '客户支持总金额',
      `total_presentment_money_currency_code` varchar(10)  NOT NULL DEFAULT '' COMMENT '客户支持金额货币代码',
      `shop_money_amount` bigint  NOT NULL DEFAULT 0 COMMENT '店铺金额',
      `shop_money_currency_code` varchar(10)  NOT NULL DEFAULT '' COMMENT '店铺金额货币代码',
      `presentment_money_amount` bigint  NOT NULL DEFAULT 0 COMMENT '客户支持金额',
      `presentment_money_currency_code` varchar(10)  NOT NULL DEFAULT '' COMMENT '客户支持金额货币代码',
      `status` tinyint NOT NULL DEFAULT 0 COMMENT '订单状态 1：已支付2：取消3：关闭4：已完成 5.退款',
      `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '是否已删除 0 未删除 1已删除，默认为0',
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
      `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
      PRIMARY KEY (`id`),
      KEY idx_email (`email`),
      KEY idx_createtime (`create_time`),
      UNIQUE KEY `uni_orderid_productid` (`order_id`,`product_id`)
) DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';

case1:

有这么一个查询。

SELECT id,uid,email,order_id,product_id,product_title,product_type,product_model,extended_warranty_period,country_code,status,create_time,update_time FROM `order` WHERE order_id = '4706314813649' AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC,id DESC LIMIT 10

这个索引命中情况尚在我的理解范围之内，命中了建立在order_id字段上索引。排序走了内存排序的方式。

case2:

大家猜一下，下面这个查询，索引命中情况如何？

SELECT id,uid,email,order_id,product_id,product_title,product_type,product_model,extended_warranty_period,country_code,status,create_time,update_time FROM `order` WHERE order_id IN ('4706314813649','4706314813648','4706314813647','4706314813646','4706314813645','4706314813644','4706314813643','4706314813642','4706314813641','4706314813640','4706314813637','4706314813636','4706314813635','4706314813634','4706314813633','4706314813632') AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC,id DESC LIMIT 5

我原以为这个也应该命中uni_orderid_productid这个索引。然而，实际情况却并非如此。实际情况却是走了全表扫描的方式。索引竟然失效了。索引为什么失效了呢？是因为涉及到回表查询了么？

case3: 

那我们就来验证一下这个猜想，是否成立。

SELECT id,order_id,product_id FROM `order` WHERE order_id IN ('4706314813649','4706314813648','4706314813647','4706314813646','4706314813645','4706314813644','4706314813643','4706314813642','4706314813641','4706314813640','4706314813637','4706314813636','4706314813635','4706314813634','4706314813633','4706314813632') AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC,id DESC LIMIT 5

我只查询索引覆盖的列的数据，大家猜一猜，这种情况下，是否命中了索引呢？

这种情况下依然没有走索引的方式。

case4:

神奇的是，where IN查询，比上面的情形少查一个订单，就能够命中索引了。

SELECT id,uid,email,order_id,product_id,product_title,product_type,product_model,extended_warranty_period,country_code,status,create_time,update_time FROM `order` WHERE order_id IN ('4706314813649','4706314813648','4706314813647','4706314813646','4706314813645','4706314813644','4706314813643','4706314813642','4706314813641','4706314813640','4706314813637','4706314813636','4706314813635','4706314813634','4706314813633') AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC,id DESC LIMIT 5

这又超出了我的理解范围了，为什么where IN查询会导致索引失效呢？where IN查询为什么又和数量有关系呢？

case5:

SELECT id,uid,email,order_id,product_id,product_title,product_type,product_model,extended_warranty_period,country_code,status,create_time,update_time FROM `order` WHERE order_id IN ('4706314813649','4706314813648','4706314813647','4706314813646','4706314813645','4706314813644','4706314813643','4706314813642') AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC LIMIT 1

 如果limit数量比较小，竟然走的是建立在create_time这个字段上的索引。为什么要走这个索引呢？不能理解。

case6:

去掉limit查询，或者limit数量比较大，你猜猜，又是走的什么方式查询呢？

SELECT id,uid,email,order_id,product_id,product_title,product_type,product_model,extended_warranty_period,country_code,status,create_time,update_time FROM `order` WHERE order_id IN ('4706314813649','4706314813648','4706314813647','4706314813646','4706314813645','4706314813644','4706314813643','4706314813642') AND product_type = 'ExtendedWarranty' ORDER BY create_time DESC

命中了uni_orderid_productid这个索引。

总结

头疼，数据库索引太复杂了，这些case，给了我诸多的困惑，我还需要更深入地学习一下索引的底层原理，方可理解这些case。