MySQL中字符串查询效率大比拼

news/2024/10/31 0:18:04/

背景

最近有个同事对字符串加索引,加完后,发现多了个奇奇怪怪的数字
执行的SQL如下:

alter table string_index_test add index `idx_name` (`name`) USING BTREE;

这个奇怪数字就是191,它很是疑惑,也没指定索引的长度
在这里插入图片描述
通过查看MySQL官方文档

InnoDB has a maximum index length of 767 bytes for tables that use COMPACT or REDUNDANT row format, so for utf8mb3 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8mb3 columns with indexes longer than 191 characters, you must index a smaller number of characters.

In an InnoDB table that uses COMPACT or REDUNDANT row format, these column and index definitions are legal:
col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))

To use utf8mb4 instead, the index must be smaller:
col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))

大概意思就是InnoDB最大索引长度为 767 字节数,用的编码是utf8mb4,则可以存储191个字符(767/4 约等于 191),编码字段长度超出最大索引长度后MySQL 默认在普通索引追加了191

思考

1、MySQL中如何提高字符串查询效率?

对字符串加索引?
一般情况下,是不建议在字符串加索引,占空间
如果一定要加,建议可以指定长度,前提是字符串前面部分区分度好的话,此时这类索引就叫前缀索引

2、前缀索引有什么问题?

区分度不好的话,很容易发生碰撞,进而引发一系列问题
我们再通过执行计划来分析一波
在这里插入图片描述
上面分别演示了前缀索引和普通索引在只有where条件、order by和group by不同执行情况,可以看到Extra的说明,前缀索引只有where条件,无法使用覆盖索引,order by会使用filesort,group by会使用temporary和filesort
总的来说,前缀索引无法使用覆盖索引,进而导致order by和group by要使用文件排序,甚至临时表
前缀索引有这么些问题,不指定长度?怎么处理?

分析

准备了单表100W的数据进行测试
使用性能压力测试工具mysqlslap
性能测试脚本

mysqlslap -uroot -p --concurrency=100,200 --iterations=1 --number-of-queries=1 --create-schema=test --query=C:\xxx\query.sql

–concurrency=100,200 测试并发的线程数/客户端数,第一次100,第二次200
–iterations=1 指定测试重复次数1次
–number-of-queries=1 指定每个线程执行的 SQL 语句数量上限(不精确)
–create-schema=test 指定查询的数据库test

1、不加索引
查询的SQL:SELECT SQL_NO_CACHE * FROM string_index_test WHERE name=‘forlan’;

BenchmarkAverage number of seconds to run all queries: 8.328 secondsMinimum number of seconds to run all queries: 8.328 secondsMaximum number of seconds to run all queries: 8.328 secondsNumber of clients running queries: 100Average number of queries per client: 0BenchmarkAverage number of seconds to run all queries: 18.078 secondsMinimum number of seconds to run all queries: 18.078 secondsMaximum number of seconds to run all queries: 18.078 secondsNumber of clients running queries: 200Average number of queries per client: 0

2、加字符串索引
alter table string_index_test add index idx_name (name) USING BTREE;
查询的SQL:SELECT SQL_NO_CACHE * FROM string_index_test WHERE name=‘forlan’;

BenchmarkAverage number of seconds to run all queries: 0.250 secondsMinimum number of seconds to run all queries: 0.250 secondsMaximum number of seconds to run all queries: 0.250 secondsNumber of clients running queries: 100Average number of queries per client: 0BenchmarkAverage number of seconds to run all queries: 1.438 secondsMinimum number of seconds to run all queries: 1.438 secondsMaximum number of seconds to run all queries: 1.438 secondsNumber of clients running queries: 200Average number of queries per client: 0

3、使用CRC32创建索引

CRC全称为Cyclic Redundancy Check,又叫循环冗余校验。
CRC32是CRC算法的一种,返回值的范围0~2^32-1,使用bigint存储

加一个name_crc32列,创建这个列的所有,索引空间小很多,利用整型加速查询
加索引:alter table string_index_test add index idx_nam_crc32 (name_crc32) USING BTREE;
查询的SQL:SELECT SQL_NO_CACHE * FROM string_index_test WHERE name_crc32=CRC32(‘forlan’) and name=‘forlan’;
因为CRC32存在发生碰撞,所以加上name条件,才能筛选出正确的数据

BenchmarkAverage number of seconds to run all queries: 0.266 secondsMinimum number of seconds to run all queries: 0.266 secondsMaximum number of seconds to run all queries: 0.266 secondsNumber of clients running queries: 100Average number of queries per client: 0BenchmarkAverage number of seconds to run all queries: 0.390 secondsMinimum number of seconds to run all queries: 0.390 secondsMaximum number of seconds to run all queries: 0.390 secondsNumber of clients running queries: 200Average number of queries per client: 0

总结

  • 通过对字符串加索引,可以提高查询效率,但需要注意指定长度,无法使用覆盖索引
  • 通过使用CRC32,需要额外存一个字段,将字符串转为整数存储,节省空间,效率提升并不是很大,但存在碰撞问题,可以加多字符串筛选条件
  • -对于CRC32存在碰撞问题,可以使用CRC64减少碰撞,但需要安装 common_schema database函数库

http://www.ppmy.cn/news/143533.html

相关文章

iphone开蓝牙wifi上网慢_iPhone手机网速慢解决方法

原标题:iPhone手机网速慢解决方法 最近小编听到很多使用iPhone XR手机小伙伴反映苹果手机网速慢,有时候手机连不上wifi。浏览网页都打不开,图片要加载半天,那遇到iphone连接wifi网速很慢怎么办?下面给大家分享三个解决iphone XR网…

iphone连接到wifi,无法上网(Android,笔记本电脑可以)

iphone连接wifi,连接成功,无网络 之前也遇到过,忘记是怎么搞好的了 这次搬家之后又遇到了 路由器重启、重置无果 又看到有人说,手动设置ip地址,不用动态方式 试了下也不行 最后找到iphone社区的一个帖子 设置-无线局…

ipad和iphone是不是计算机,iPad和iPhone哪个好

iPad和iPhone哪个好 iPad和iPhone是目前全球最受欢迎的苹果科技产品,iPad的是指苹果平板电脑,而iPhone是指的是苹果手机。有网友疑问iPad和iPhone哪个好?其实这两者并不是同一类型的数码设备,因此不可以直接说iPad与iPhone哪个好&…

ipad 计算机连接网络设置方法,ipad怎么通过联网的电脑上网,

第一步:右键单击--网络邻居--属性--无线网络连接--属性--无线网络配置--选中“用Windows配置我的无线网络设置”复选框--高级--选择“仅计算机到计算机(特定)”(不要勾选“自动连接到非首选的网络”复选框)--确定返回“无线网络连接属性”窗口--单击左下角的“添加”…

WIN10如果将电脑网络分享给iphone

1、先查看本机无线网卡是否支持此功能。单击开始-所有程序-附件-运行(快捷键:win键R),输入cmd,回车,输入命令:netsh wlan show drivers(也可以直接用鼠标复制粘贴在cmd窗口里),回车。 找到“支持的承载网络”后面写的是“是”还是…

如何享受免费网络iPhone 5连接电脑上网

iPhone5连接电脑上网教程步骤如下: 首先,用usb连接IPhone与电脑网络邻居属性中查看现在本地IP,并记下。然后再进入IPHONE上开启”个人热点“功能。然后在电脑上打开“网络邻居”-属性会多出一个网卡。按住ctrl键选中两个网卡&#xff…

windows电脑文件传输至ipad/iphone

前言:个人分享而已,好坏对错与否勿喷,介意就别看。文明上网。 tips1:本方法适用于稍微有点计算机基础的伙伴们。 tips2:本方法需要你的电脑上已经安装并配置好了python,只要你电脑可以进行python代码程序…

如何将电脑(网线)网络共享给iPhone苹果手机(不需要数据线)

0.环境准备 电脑:win10连接了网线的手机:iphone11电脑可上网,手机想用电脑的网络 步骤1 1.进入设置—> 移动热点 2.打开移动热点开关 3.编辑网络名称和密码等 4.这时用手机连接此热点,如果能连上就不用看之后的步骤了&#x…