Mysql对字符串一部分添加索引

MySQL字符串截取与索引优化
本文介绍MySQL中字符串截取函数substring_index的用法,包括如何仅对字符串的特定部分创建索引,以及如何利用前缀索引提高查询效率。

例如在公司中,每个人的工作邮箱@之后的内容是一样的,那如何只对@之前的字符串创建索引呢?

首先需要了解以下几点:

一、MySQL中字符串的截取

MySQL中有专门的字符串截取函数:其中常用的有两种:

substring_index(str,delim,count) 和concat

 

1.substring_index(str,delim,count) 函数的使用较为普遍。

函数括号里面的依次为:

要分隔截取的字符串(如:”aaa_bbb_ccc”)、分隔符(如:“_”)

位置(表示第几个分隔符处,如:“1”)

count为正数,那么就是从左边开始数,函数返回第count个分隔符的左侧的字符串;

count为负数,那么就是从右边开始数,函数返回第count个分隔符右边的所有内容;

count可以为0,返回为空。


例子:

substring_index("aaa_bbb_ccc","_",1) ,返回为 aaa;

substring_index("aaa_bbb_ccc","_",2) ,返回为 aaa_bbb;

substring_index(substring_index("aaa_bbb_ccc","_",-2),"_",1) ,返回为 bbb;

2.concat是连接几个字符串

例子:concat(‘m’,’y’,’s’,’q’,’l’);

返回:mysql

 

二、依据表中的某个字段查询包含有这个字符的所有数据

1.查询user表中所有name包含“吴”的数据 

find_in_set:SELECT * FROM user WHERE find_in_set(‘吴’,name); 

2.使用正则,查询包含刘或者吴的数据

REGEXP:SELECT * FROM user WHERE name REGEXP ‘(‘吴’|‘刘’)’; 

 

三、使用

索引的设计者提出了个方案 --- 只对字符串的前几个字符进行索引。通过字符串的前几个字符我们已经能大概排序字符串了,剩下不能排序的可以通过遍历进行查找啊,这样只在B+树中存储字符串的前几个字符的编码,既节约空间,又减少了字符串的比较时间,还大概能解决排序的问题,何乐而不为,比方说我们在建表语句中只对name列的前10个字符进行索引可以这么写:

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    KEY name (name(10))
);    

name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码,这种只索引字符串值的前缀的策略是我们非常鼓励的,尤其是在字符串类型能存储的字符比较多的时候。

 

对于最开始的问题:如何只对@之前的字符串创建索引

CREATE TABLE person_info(
    email VARCHAR(100) NOT NULL,
    KEY email (substring_index(name,'@',1))
);    

 

### MySQL 字符串索引与数字索引性能对比分析 在MySQL中,字符串索引和数字索引的性能差异主要体现在存储空间、检索效率、插入性能以及I/O开销等方面。以下是详细分析: #### 1. **存储空间** - 数字索引(如`INT`或`BIGINT`)占用的空间较小,通常为4到8字节,具体取决于数据类型的选择[^1]。 - 字符串索引(如`VARCHAR`或`UUID`)占用的空间较大,尤其是当使用较长的字符串(如UUID)时,其存储需求显著增加。此外,InnoDB引擎会将主键作为聚集索引一部分,导致字符串主键进一步增加存储开销。 #### 2. **检索效率** - 数字索引的检索速度更快,因为整数比较的CPU开销较低,且B+树结构中的节点更紧凑,能够容纳更多的键值[^1]。 - 字符串索引的检索速度相对较慢,尤其是在区分度较低的情况下(例如身份证号前几位重复较多),可能导致索引选择性下降,进而影响查询性能[^2]。 #### 3. **插入性能** - 数字索引(特别是自增主键)在插入时性能更高,因为自增ID可以顺序写入,减少了页分裂的概率[^1]。 - 字符串索引(如UUID)由于其随机性,在插入时容易引发页分裂,导致索引碎片化严重,从而降低插入性能[^1]。 #### 4. **I/O开销** - 数字索引的I/O开销较低,因其占用空间小,能够在内存中缓存更多数据,减少磁盘读取次数。 - 字符串索引的I/O开销较高,不仅因为其本身占用空间大,还因为字符串比较操作需要更多的计算资源[^2]。 #### 5. **典型场景推荐** - 对于单机环境、数据量较大且频繁查询的场景,建议使用数字索引(如自增整数),以获得更高的查询和插入性能[^1]。 - 在分布式系统中,若追求唯一性和高并发写入能力,可考虑使用UUID或雪花ID(Snowflake)等字符串索引。 #### 示例代码:创建数字索引字符串索引 ```sql -- 创建数字索引 CREATE TABLE example_numeric ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); -- 创建字符串索引 CREATE TABLE example_string ( id CHAR(36) PRIMARY KEY, -- 使用UUID作为主键 name VARCHAR(255) ); ``` ### 总结 总体而言,数字索引在大多数情况下性能优于字符串索引,特别是在查询速度、插入性能和存储空间方面表现更佳。然而,字符串索引在某些特定场景下(如分布式系统或业务信息表达明确的需求)可能更具优势。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值