mysql慢查询优化

如下表：

CREATE TABLE "goods_relation_inherit_goods_flow" (
  "id" bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  "mall_flow_id" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'mall_flow_id',
  "goods_id" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  "goods_name" varchar(255) NOT NULL DEFAULT '' COMMENT '商品名',
  "out_goods_id" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '外部商品id',
  "out_goods_name" varchar(255) NOT NULL DEFAULT '' COMMENT '外部商品名',
  "out_sold_count_month" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '销量',
  "out_sold_count_total" bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '销量',
  "crawl_time" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '销量的时间',
  "status" tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  "out_detail_url" varchar(4096) NOT NULL DEFAULT '' COMMENT '外部商品详情链接',
  "out_pic_url" varchar(2048) NOT NULL DEFAULT '' COMMENT '外部商品图片链接',
  "is_deleted" tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1已删除,0未删除',
  "gmt_created" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间戳',
  "gmt_modified" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间戳',
  "out_goods_sn" varchar(255) NOT NULL DEFAULT '' COMMENT '商品货号',
  "transferred_out_pic_url" varchar(2048) DEFAULT '' COMMENT '转换过的外部图片链接',
  "out_goods_detail" text COMMENT '外部商品详情',
  "out_source_type" tinyint(3) DEFAULT '0' COMMENT '外部商品来源',
  "goods_commit_id" bigint(20) DEFAULT '0' COMMENT '商品提交的id',
  "check_status" tinyint(3) DEFAULT '0' COMMENT '检测是否可以转为商品',
  "transfer_status" tinyint(3) DEFAULT '0' COMMENT '修改来源服务',
  "source_service" varchar(32) DEFAULT '' COMMENT '修改来源服务',
  "out_comment_count" bigint(20) NOT NULL DEFAULT '-1' COMMENT '外部商品评论数',
  PRIMARY KEY ("id"),
  KEY "idx_mallflowid_goodsid" ("mall_flow_id","goods_id"),
  KEY "idx_outgoodsid" ("out_goods_id"),
  KEY "idx_mallflowid_outgoodsid" ("mall_flow_id","out_goods_id"),
  KEY "idx_goods_commit_id" ("goods_commit_id"),
  KEY "idx_gmt_created" ("gmt_created"),
  KEY "idx_mallflowid_checkstatus_transferstatus" ("mall_flow_id","check_status","transfer_status"),
  KEY "idx_transfer_status" ("transfer_status"),
  KEY "idx_gmt_modified" ("gmt_modified")
) ENGINE=InnoDB AUTO_INCREMENT=27508135 DEFAULT CHARSET=utf8mb4 COMMENT='流水表'

查询语句

select id, mall_flow_id, goods_id, goods_name, out_goods_id, out_goods_name, out_sold_count_month, 
    out_sold_count_total, status, out_detail_url, out_pic_url, is_deleted, gmt_created, 
    gmt_modified, crawl_time, out_goods_sn, transferred_out_pic_url, out_thumb_url, out_low_price, 
    out_high_price, out_source_type, goods_commit_id, check_status, transfer_status, 
    source_service, out_comment_count
   from goods_relation_inherit_goods_flow
   WHERE  mall_flow_id = 52469 and is_deleted = 0 order by id desc limit 0,1    慢查询
   
select * from  `goods_relation_inherit_goods_flow` where mall_flow_id=52469  and is_deleted=0 order by id desc  慢
   
select id, mall_flow_id, goods_id, goods_name, out_goods_id, out_goods_name, out_sold_count_month, 
    out_sold_count_total, status, out_detail_url, out_pic_url, is_deleted, gmt_created, 
    gmt_modified, crawl_time, out_goods_sn, transferred_out_pic_url, out_thumb_url, out_low_price, 
    out_high_price, out_source_type, goods_commit_id, check_status, transfer_status, 
    source_service, out_comment_count
   from goods_relation_inherit_goods_flow
   WHERE  mall_flow_id = 52469 and is_deleted = 0 order by id desc   不慢
   
   
select * from  `goods_relation_inherit_goods_flow` where mall_flow_id=52469  and is_deleted=0  limit 0,1 不慢

以上语句慢查询：

这几个数字，会影响 索引的选择。
你要按主键逆序排序，就得加一条独立的 mall_flow_id 进来。
另外，如果 新加的索引的过滤性 没有我截图的数字高，那还是可能选不中这条 独立索引的。


就是索引启用了 ICP 功能。
你前面有一堆 mall_flow_id 的联合索引。
但这些索引里面没有一个有 is_deleted 字段

不加，那些索引 可以直接在 索引上做一些过滤操作。
加了，就得回表去读。
但是 你还是要排序的。
去了 is_deleted 后
这个 很耗时。
需要在内存里面单独创建排序索引。

最终解决：一、强制一个索引 去掉is_deleted （业务上可以 不去300ms 去掉 30ms） 二、建立mall_flow_id的单个索引