MySQL字符串索引更合理的创建规则讨论
作者:风雨之间 发布时间:2024-01-24 19:10:55
前言
针对使用MySQL的索引,我们之前介绍过索引的最左前缀规则,索引覆盖,唯一索引和普通索引的使用以及优化器选择索引等概念,今天我们讨论下如何更合理的给字符串创建索引。
如何更好的创建字符串索引
我们知道,MySQL中,数据和索引都是在一颗 B+树 上,我们建立索引的时候,这棵树所占用的空间越小,检索速度就会越快,而varchar格式的字符串有些会很长,那么在效率为上的今天,我们如何更加合理的建立字符串的索引呢?
假如说我们一张表中存在 email 字段,现在要给 email 字段创建索引,email 字段值的格式为:zhangsan@qq.com。
有2种建立索引的方式:
1、直接给 email 字段建立索引:alter table t add index index1(email);
索引树结构为:
2、建立 email 的前缀索引:alter table t add index index2(email(6));
索引数据结构为:
此时我们的查询语句为:select id,name,email from t where email='zhangsh123@xxx.com';
当使用index1索引时其执行步骤为:
1、从index1索引树查找索引值为zhangsh123@xxx.com的主键值ID1;
2、根据ID1回表查到该行数据确实为zhangsh123@xxx.com,将结果加入结果集;
3、继续查找index1索引树下一个索引值是否满足zhangsh123@xxx.com,不满足则结束查询。
当使用index2索引时其执行步骤为:
1、从index2索引树查找索引值为zhangs的主键值ID1;
2、根据ID1回表查到该行数据确实为zhangsh123@xxx.com,将结果加入结果集;
3、 继续查找index2索引树下一个索引值是否满足zhangs,满足则继续回表查询该行数据是否为zhangsh123@xxx.com,不是则跳过继续查找;
4、持续查找index2索引树,直到索引值不是zhangs为止。
从以上分析中我们可以看出,全字段索引相比前缀索引来说,减少了回表的次数,但是如果我们将前缀从6个增加到7个8个的话,前缀索引回表的次数就会减少,也就是说,只要定义好前缀的长度,我们就能既节省空间又保证效率。
那么问题来了,我们怎么衡量使用前缀索引的长度呢?
1、使用 select count(distinct email) as L from t;
查询字段不同值的个数;
2、依次选取不同的前缀长度查看不同值的个数:
select
count(distinct left(email,4))as L4,
count(distinct left(email,5))as L5,
count(distinct left(email,6))as L6,
count(distinct left(email,7))as L7,
from t;
然后根据实际可接受的损失比例,选取适合的最短的前缀长度。
前缀的长度问题我们解决了,但是一个问题是,如果使用前缀索引,那我们索引覆盖的特性就用不到了。
用全字段索引时,当我们查询select id,email from t where email='zhangsh123@xxx.com';
时,不用回表直接就能查到id和email字段。
但是用前缀索引时,MySQL并不清楚前缀是否会整个覆盖email的值,无论是否全包含都会根据主键值回表查询判断。
所以说,使用前缀索引虽然能节省空间保证效率但是却不能用到覆盖索引的特性,是否使用就在于具体考虑了。
其他字符串索引创建方式
实际情况实际考虑,并不是所有的字符串都能使用前缀截取的方式创建索引,如身份证号或者ip这些字符串使用前缀索引就不合理了,身份证号一般同一个地区的人前几位都是一模一样的,使用前缀索引就不合理了,而ip值我们一般在实际中将其转化为数字去存储。
针对身份证号,我们可以使用倒叙存储,取前缀创建索引或者使用crc32()函数来获取一个hash校验码(int值)当做索引。
倒叙:select field_list from t where id_card = reverse('input_id_card_string');
crc32:select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
这两种方式相对来说效率都差不多,都不支持范围查找,支持等值查找。
在倒叙方式中,需要使用reverse函数,但是回表次数可能比hash方式多。
在hash方式中,需要新建一个索引字段并调用crc32()函数。(注意:crc32()函数获取的结果不保证能唯一,可能存在重复的情况,但是这种情况概率较小),回表次数少,几乎1次就行。
最后
针对字符串索引,一般有以下几种创建方式:
1、字符串较短,直接全字段索引
2、字符串较长,且前缀区分度较好,创建前缀索引
3、字符串较长,前缀区分度不好,倒叙或hash方式创建索引(这种方式范围查询就不行了)
4、根据实际情况,遇到特殊字符串,特殊对待,如ip。
来源:https://segmentfault.com/a/1190000021086051
猜你喜欢
- 前言字符串是 字符的序列 。字符串基本上就是一组单词。我几乎可以保证你在每个Python程序中都要用到字符串,所以请特别留心下面这部分的内容
- 技巧之一:提高使用Request集合的效率 访问一个ASP集合来提取一个值是费时的、占用计算资源的过程。因为这个操作包含了一系列对相关集合的
- 前言装饰器(decorator)在Python框架中扮演着重要角色,是Python中实现切面编程(AOP)的重要手段。aspect-orie
- 准确地讲,Python没有专门处理字节的数据类型。但由于str既是字符串,又可以表示字节,所以,字节数组=str。而在C语言中,我们可以很方
- 问题一:安装模块时出现报错 Microsoft Visual C++ 14.0 is required,也下载安装了运行库依然还是
- 直接点 某8网 https://*****.b*b.h*****y*8*.com/具体网址格式就是这样的但是为了安全起见,我就这样打码了.抛
- 写这篇博客主要是因为在修改DataFrame列值的时候经常遇到bug,但到目前还没把这种错误复现出来。DataFrame是Pandas中的主
- 这篇文章主要介绍了Python如何计算语句执行时间,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可
- preface:做着最近的任务,对数据处理,做些简单的提特征,用机器学习算法跑下程序得出结果,看看哪些特征的组合较好,这一系列流程必然要用到
- 首先要解释一下:“极致之美”不是说月儿的这篇文章,因为本人还没有自大到这种程度:P,它形容的是Lisp和javascript结合的优美形态。
- 这本入门手册是否合适你?我只想告诉你我非常喜欢这本书。我对Microsoft Access的经验足以让我跳过这本傻瓜系列教材,但是实际情况是
- HTML实体符号被用作实现保留字符(reserved characters)或者表达键盘无法输入的一些常用字符。在大多数浏览器中默认的字符集
- 如下所示:>>> import numpy as np>>> a = np.arange(12).res
- 1. 安装Opencv包pip install opvencv-python2.实现代码:视频转为图片:import cv2cap=cv2.
- 首先声明,在这组里我是个绝对的菜鸟。再次声明,小爝这个菜鸟在“网页设计”这个圈里混了快1年了。 摘要:我知道我有多少底,所以我在总结我的成长
- 如下所示:>>> import pandas as pd>>> import numpy as np#
- //1、运行到C盘根目录 //2、输入:SET ORACLE_SID = 你的SID名称 3、输入:sqlplus/nolog 4、输入:c
- 1.安装PDFminer3k使用pip 命令安装pip install pdfminer3k2.编写测试你可以在这里获得官方参考:PDFMi
- 首先,看看本文所面向的应用场景:我们有一个数据集df,现在想统计数据中某一列每个元素的出现次数。这个在我们前面文章《如何画直方图》中已经介绍
- 首先打开网站https://www.zymk.cn/1/37988.html打开开发者工具选择XHR标签页,没有找到什么再查看一下这些图片的