©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>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,
latitudefrom 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,
latitudefrom t_food_shop where geo_hash like 'wecp3%';
可以看到使用到了idx_geohash索引
结论：字段值的重复率过高会导致索引失效