©2020-2024 ioDraw All rights reserved
<>MySQL like Post query % Index failure analysis
Table structure
CREATE TABLE `t_food_shop` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
COMMENT ' Primary key ', `shop_name` varchar(50) NOT NULL DEFAULT '' COMMENT ' Shop name ', `
shop_img` varchar(250) NOT NULL DEFAULT '' COMMENT ' Shop picture ', `category_name`
varchar(50) NOT NULL DEFAULT '' COMMENT ' Category name ', `price` varchar(50) NOT NULL
DEFAULT '' COMMENT ' Consumer price ', `area` varchar(50) NOT NULL DEFAULT '' COMMENT ' Region '
, `collects` bigint(11) NOT NULL DEFAULT '0' COMMENT ' Number of collections ', `shop_type`
tinyint(3) NOT NULL DEFAULT '0' COMMENT ' Classification of stores （0, Blue silk ,1, Yellow silk ）', `shop_grade` decimal
(3,1) NOT NULL DEFAULT '0.0' COMMENT ' Store rating ', `address` varchar(128) NOT NULL
DEFAULT '' COMMENT ' Shop address ', `longitude` varchar(16) NOT NULL DEFAULT '' COMMENT
' longitude ', `latitude` varchar(16) NOT NULL DEFAULT '' COMMENT ' latitude ', `geo_hash`
varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ''
COMMENT 'geohash', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP
COMMENT ' Creation time ', `update_time` timestamp NULL DEFAULT NULL ON UPDATE
CURRENT_TIMESTAMP COMMENT ' Update time ', `openrice_id` bigint(20) NOT NULL DEFAULT '0'
COMMENT 'openrice Shop only 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=' Basic data sheet of food shop ';
Query execution plan
EXPLAIN select id, shop_name, price, collects, shop_grade, address, longitude,
latitudefrom t_food_shop where geo_hash like 'wecny%';
geo_hash Field is indexed , Query by prefix , But the index failed .
“wecny” String length is 5, Let's take a look at the length 5 The repetition rate of the string
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;
Can see , Prefix is “wecny” The repetition rate accounts for 0.2234, The discrimination is too large ,mysql Full table scan selected .
Let's try the percentage 0.0338 Prefix is "wecp3" Implementation plan
EXPLAIN select id, shop_name, price, collects, shop_grade, address, longitude,
latitudefrom t_food_shop where geo_hash like 'wecp3%';
You can see that it has been used idx_geohash Indexes
conclusion ： Too high repetition rate of field value will lead to index invalidation
Technology