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='美食店铺基本数据表';
查询执行计划
EXPLAINselect 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) prefixFROM t_food_shop GROUP BY prefix ORDER BY percent DESC LIMIT 0,10;
可以看到，前缀为“wecny”的重复率竟然占到了0.2234，区分度大小了，mysql选择了全表扫描。
我们来试试百分比为0.0338前缀为"wecp3"的执行计划
EXPLAINselect id, shop_name, price, collects, shop_grade, address, longitude, latitude from t_food_shop where geo_hash like 'wecp3%';
可以看到使用到了idx_geohash索引
结论：字段值的重复率过高会导致索引失效
