MySQL like查询后置%索引失效分析

MySQL like查询后置%索引失效分析

表结构

CREATE TABLE `t_food_shop` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `shop_name` varchar(50) NOT NULL DEFAULT '' COMMENT '店铺名称',
  `shop_img` varchar(250) NOT NULL DEFAULT '' COMMENT '店铺图片',
  `category_name` varchar(50) NOT NULL DEFAULT '' COMMENT '品类名称',
  `price` varchar(50) NOT NULL DEFAULT '' COMMENT '消费价格',
  `area` varchar(50) NOT NULL DEFAULT '' COMMENT '所属地区',
  `collects` bigint(11) NOT NULL DEFAULT '0' COMMENT '收藏的数量',
  `shop_type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '店铺的分类（0,蓝丝,1,黄丝）',
  `shop_grade` decimal(3,1) NOT NULL DEFAULT '0.0' COMMENT '店铺评级',
  `address` varchar(128) NOT NULL DEFAULT '' COMMENT '店铺地址',
  `longitude` varchar(16) NOT NULL DEFAULT '' COMMENT '经度',
  `latitude` varchar(16) NOT NULL DEFAULT '' COMMENT '纬度',
  `geo_hash` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'geohash',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `openrice_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'openrice 店铺唯一id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_geohash` (`geo_hash`) USING BTREE,
  FULLTEXT KEY `idx_name` (`shop_name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=673319 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='美食店铺基本数据表';

查询执行计划

EXPLAIN
select id, shop_name, price, collects, shop_grade, address, longitude, latitude from t_food_shop  where geo_hash like 'wecny%';

geo_hash字段建立了索引，按照前缀查询，但是索引失效了。

“wecny”字符串长度为5，我们就来看看长度为5的字符串的重复率

select count(1) from t_food_shop;
SELECT (count(*) / 63061 ) percent,count(*) cnt,LEFT(geo_hash,5) prefix
FROM t_food_shop GROUP BY prefix ORDER BY percent DESC LIMIT 0,10;

可以看到，前缀为“wecny”的重复率竟然占到了0.2234，区分度大小了，mysql选择了全表扫描。
我们来试试百分比为0.0338前缀为"wecp3"的执行计划

EXPLAIN
select id, shop_name, price, collects, shop_grade, address, longitude, latitude from t_food_shop  where geo_hash like 'wecp3%';

可以看到使用到了idx_geohash索引

结论：字段值的重复率过高会导致索引失效