MySQL like查询后置%索引失效分析_huchao_lingo的博客

相关文章推荐
高大的火龙果 · python获取本周一的时间_mob649e ...· 6 月前 ·
玩足球的黄花菜 · VS2017 未定义标识符_vs2017 ...· 7 月前 ·
温文尔雅的小蝌蚪 · mysql正则表达式提取字符串_mob64c ...· 1 年前 ·
求醉的大蒜 · mysql计算时间差函数 - ...· 1 年前 ·
打篮球的领带 · Python王牌加速库：奇异期权定价的利器- ...· 1 年前 ·
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索引 
结论：字段值的重复率过高会导致索引失效
                    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_
针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度。需建立前缀索引
	前缀索引应该足够长，使前缀索引的选择性趋紧于索引整个列的选择性。
	前缀索引又不能太长，降低索引的大小，减少空间占用。
	Alter table tableName add key/index (column(X)).
	缺点：无法应用于order by 和 group by，也无法做覆盖扫描。
计算合适前缀长度方法：
先计算比对整列统计数和前缀列统计数
select coun..
				背景：当在某列建立索引，并且在查询条件中使用like '%xxx%' 这样的语句时，会使得索引失效。当时实际的环境必须使用 like '%xxx%' 这样的条件，那么应该如何解决这个问题。
答：使用覆盖索引避免这个问题。即：在要查询的列和其他的列之间建立一个复合索引，然后查询时同时查询和复合索引有关的列即可避免全表扫描。
昨天早上接到一个任务，查询某类商品，数据库用的是mysql，版本是5.6.37，es_product 表数据不多，两万多条。执行语句我简化了一下，类似下面的这条sql：
SELECT * FROM es_product WHERE goods_code LIKE 'meiju%';
goods_code是建了索引的，索引类型Normal。
语句执行的很慢，我们看下它的执行计划是什么：
EXP...
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效，但有时需求就是如此，类似这样的需求还有很多，例如，搜索引擎需要根基用户数据的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是B+树索引能很好完成的工作。
通过数值比较，范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较，全文索引就是为这种场景设计的。
全文索引（Ful
				在使用mysql的时候经常遇到各种问题，之前都是每次遇到查询一下网上的解决方案解决掉，但是下次再遇到还是记不住，需要重新百度解决，今天又遇到了一个问题，顺便记录下来方便以后查阅，这篇文章会不断进行更新，记录我使用mysql过程中遇到的问题，各位小伙伴如果有遇到奇怪的问题，欢迎留言，我一起整理一下，方便大家查阅，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！
ERROR 1071 (42000): Specified key was t
网上都说学会mysql需要学会两个部分,索引和事务,其实在最近的Mysql学习过程中,我觉得应该是有三个部分的,索引,查询,事务.其中的查询主要是指查询优化即编写高效率的SQL语句.
本文记录一下学习MySQL的索引过程中的一些知识.主要为阅读《高性能MySQL》的一些理解和扩展.
（想自学习编程的小伙伴请搜索圈T社区，更多行业相关资讯更有行业相关免费视频教程。完全免费哦!）
什么是索引
1.最佳左前缀法则-如果索引了多列,要遵守最左前缀法则.指的是查询从索引的最左前列开始并且不跳过索引中的列.
2.不在索引上左任何操作(计算,函数，(自动or手动)类型转换),会导致索引失效而转向全表扫描
3.存储引擎不能使用索引中范围条件右边的列
4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
5.mysql在使用不等于（!=或者&lt;&gt;）的...