精华内容
下载资源
问答
  • 数据库字符串字段 索引
    千次阅读
    2019-10-31 17:15:16

    如果有这样一个场景,某个字段保存的是邮箱,然后需要给这个字段建立索引,有如下几种解决方案:

    全字段索引

    alter table user add index index1(email);
    

    不建议用这种方式,因为索引树需要存储字段的全部值造成不必要的空间浪费

    使用前缀索引

    alter table user add index index1(email(6));
    

    这种方式可以节约索引的存储空间,但是如何合理的设置前缀的长度呢?
    (1)可以先计算email这个字段上有多少行不重复的行数

    select count(distinct email) as email_num from user;
    

    (2)然后依次选取不同长度的前缀来看这个值,再做适当的设计

    select count(distinct left(email,4)) as email_num4, 
            count(distinct left(email,5)) as email_num5,
            ...
    

    使用前缀索引的最大的一个缺点是一定会回表,如果设置的前缀长度小于字段实际长度,肯定是需要回表做精确比较的,但是即使把前缀的长度设置为字段的实际大小,也就是辅助索引已经存储了该字段的全部值,但是也会进行一次回表比对,因为系统并不确定前缀的长度是否包含了完整信息。也就是说,使用前缀索引就无法使用覆盖索引。

    对于邮箱这样的前缀在一定长度上重复率不大的情况下,使用前缀索引是不错的选择,但是如果有这样一个需求,需要对身份证号码做索引,而且这个表存储的只是一个市的居民身份证号码,身份证号码规则是这样的:一个18位,前6位的地址码,所以同一个地区的人的身份证号码前6位是一样的,这种情况下前缀索引就不好用了,下面给出解决方案。

    字符串逆序之后再使用前缀索引

    这种方式很适合上面提到的一个市的身份证号码字段,还有一种情况是该字段时一个时间戳,那前几个数字相似度很高等等。

    使用hash字段

    在数据库表上再增加一个字段存储需要建立的索引字段的hash值,对应的hash值可以用数据库(MySQL)的crc32()或者crc64()函数来计算,这种方式会有不同的字段值对应的hash值相同,因此每次查询的时候where后面加上索引比较之后还要进行精确值的比较:

    select  field from t where id_card_crc=crc32('input_id_card') and id_card=id_input_id_card
    

    总结

    (1)从占用额外的空间上看,采用哈希值字段索引会占用一个额外的字段空间,但是如果前缀的长度比较长的话,这个差距就可以忽略不计。
    (2)从cpu消耗上看,使用哈希索引需要调用crc32()函数。
    (3)从查询效率上看,使用hash值字段索引性能更稳定,因为crc64()结合了md5算法将冲突的概率降到了很低,因此平均扫描次数接近1。使用前缀索引会增加扫描次数。
    (4)无论使用哪种凡是,都无法进行覆盖索引,也无法使用范围查找

    更多相关内容
  • 本文分析了MySQL中字符串索引对update的影响。分享给大家供大家参考,具体如下: 对某一个类型为varchar的字段添加前缀索引后,基于该子段的条件查询时间基本大幅下降;但对于update操作,所耗的时间却急剧上升,...
  • MySQL – 给字符串索引

    千次阅读 2021-02-02 07:39:18
    并提到了字符串可以使用最左N个字符作为索引值(最左前缀)。在数据库选错索引怎么办里提到基数(cardinality)是选择使用哪个索引的很重要的指标。前缀索引下面举个例子:-- 使用整个字段索引mysql> alter table ...

    在索引里提到了索引占用的空间和索引占用值的关系。并提到了字符串可以使用最左N个字符作为索引值(最左前缀)。

    在数据库选错索引怎么办里提到基数(cardinality)是选择使用哪个索引的很重要的指标。

    前缀索引

    下面举个例子:

    -- 使用整个字段做索引

    mysql> alter table SUser add index index1(email);

    -- 使用前6个字符做索引

    mysql> alter table SUser add index index2(email(6));

    如果使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:

    zhangssxyz@xxx.com

    zhangssxyz@xxx.com

    如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:

    从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;

    到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;(回表)

    取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;

    重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。

    可以看到使用第二种方式建索引会导致回表,那么覆盖索引也用不上了。

    所以,如何给字符串建索引就成了空间(占用空间)和效率(基数)的权衡。前缀索引使用的好,就可以做到既节省空间,又不用额外增加太多的查询成本。

    如何确定前缀索引的长度

    首先,你可以使用下面这个语句,算出这个列上有多少个不同的值:

    mysql> select count(distinct email) as L from SUser;

    然后,依次选取不同长度的前缀来看这个值,比如我们要看一下 4~7 个字节的前缀索引,可以用这个语句:

    mysql> 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 SUser;

    使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如 5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6。

    前缀区分度不够怎么办

    比如身份证号,字符串很长,前缀部分区分度很低,有两个方法:

    倒序存储:把身份证倒过来存,因为身份证前几位区分度太低,但是后几位区分度很高:

    mysql> alter table t add index id_card_i(id_card(6));

    mysql> select field_list from t where id_card = reverse('input_id_card_string');

    添加一个身份证的hash字段

    mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

    mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

    这两个方法有个共同的缺点,不支持范围查询,只支持等值查询。

    展开全文
  • 对于长字符串,可用如下方式建立索引: (1)前缀索引 (2)字符串倒叙+前缀索引 (3)添加hash字段+并在hash字段上加索引 (4)字段拆分(一个字段可拆分为两个以上) 假设现在表User 中存在email字段,为长字符串...

    对于长字符串,可用如下方式建立索引:
    (1)前缀索引
    (2)字符串倒叙+前缀索引
    (3)添加hash字段+并在hash字段上加索引
    (4)字段拆分(一个字段可拆分为两个以上)

    假设现在表User 中存在email字段,为长字符串。如果email字段上没有索引,那么这个语句只能做全表扫描

    select f1,f2 from User where email = '1540984562@qq.com';
    

    在email字段上创建索引
    –创建的index1索引包含整个字符串

    alter table User add index index1(email);
    

    –创建的index2索引只包含字符串前6个字节

    alter table User add index index2(email(6));
    

    前缀索引占用的空间更小,但是会增加扫描的次数。
    使用前缀索引,定义好长度,就可以做到既节省空间,又不额外增加太多查询成本。
    在建立索引时关注区分度,可区分度越高,意味着重复的键值越少

    可以依次选取不同长度的前缀来看这个值,比如看4~7个字节的前缀索引:

    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 User;
    

    前缀索引对覆盖索引的影响

    如果我们的语句是:
    select id,email from User where email = ‘1540984562@qq.com’;
    这个语句只要求返回id和email字段。
    如果使用的整个字符串作为索引,会触发覆盖索引,不需要回表ID索引再查询。
    而如果使用的是前缀索引,就还得回表ID索引去获取email字段的完整值

    所以如果使用了前缀索引就相当于放弃掉了覆盖索引对查询性能的优化了。

    使用 倒序 与 hash 构造新字段

    1、倒序
    如果我们对身份证号码进行前缀索引提取,会发现,前面的号码重复率极高。
    此时可以使用倒序存储
    select field_list from t where id_card = reverse(‘input_id_card_string’);
    由于身份证最后几位的重复逻辑较小,所以最后6位很可能提供了足够的区分度
    2、hash字段
    在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。

    alter table t add id_card_crc int unsigned,add index(id_card_crc);
    

    然后每次插入新纪录的时候,都同时用crc32()这个函数得到校验码填到这个新字段。
    由于校验码可能存在冲突,也就是说两个不同的身份证号通过crc32()函数得到的结果可能相同,
    所以查询语句还要判断id_card是否精确相同。

    select field_list from t where id_card_crc = crc32('input_id_card_string') and id_card = 'input_id_card_string';
    

    两者异同点:
    相同
    都不支持范围查询,只支持等值查询
    不同
    1、占用额外空间不同:
    倒序存储不会消耗额外的存储空间。hash需要增加一个字段。
    若倒序存储使用4个字节作为前缀不够,也会消耗额外字段
    2、CPU消耗方面:
    倒序每次都要调用reverse函数
    hash需要调用crc32函数。reverse的时间复杂度会更小
    3、查询效率:
    hash字段查询性能相对更稳定,发生冲突概率很小,可以认为每次查询平均扫描行数为1.
    倒序存储方式仍然使用的是前缀索引方式,还会增加扫描行数

    总结:
    在这里插入图片描述

    展开全文
  • MySQL大表中重复字段的高效率查询方法MySQL 删除大表的性能问题解决方案MYSQL数据库中的现有表增加新字段(列)MySQL命令行中给表添加一个字段字段名、是否为空、默认值)MySql创建带解释的表及给表和字段加注释的...
  • 准备语句:DROP TABLE ix_test;CREATE TABLE ix_test(id_1 varchar(20) NOT NULL,PRIMARY KEY(id_1));INSERT INTO ix_test VALUES (1),(2...mysql会将数字在×××和字符串之间自动转换!这样下面两条语句的结果是一...

    准备语句:DROP TABLE ix_test;

    CREATE TABLE ix_test

    (id_1 varchar(20) NOT NULL,

    PRIMARY KEY(id_1));

    INSERT INTO ix_test VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

    mysql会将数字在×××和字符串之间自动转换!

    这样下面两条语句的结果是一样的:

    SELECT * FROM ix_test WHERE id_1=1;

    SELECT * FROM ix_test WHERE id_1='1';

    但是在索引使用情况方面,结果就完全不一样了!第一条不使用索引,第二条使用索引!

    两条语句,第一条使用了索引,但是扫描了全表,第二条直接索引到数据,只需要读取一行!mysql> explain select * from ix_test where id_1=1;

    +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+

    | 1 | SIMPLE | ix_test | index | PRIMARY | PRIMARY | 302 | NULL | 11 | Using where; Using index |

    +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+

    1 row in set (0.00 sec)

    mysql> explain select * from ix_test where id_1='1';

    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

    | 1 | SIMPLE | ix_test | const | PRIMARY | PRIMARY | 302 | const | 1 | Using index |

    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

    1 row in set (0.01 sec)

    但是如果将id_1字段变为×××,后面用×××或者是字符串去匹配都可以使用索引,而且索引直接命中!

    所以,多么坑爹的mysql sql优化器,多么痛的领悟!

    得出结论,对于where后面字段类型为字符串的数字,如果用×××去匹配(就是不用引号引上数字),则不能由索引直接命中,需要全部扫描。

    特别需要注意!

    展开全文
  • 特征码与加密字符串数据一一对应,作为索引保存在索引字段中。查询时使用两阶段查询策略,首先利用索引字段对加密数据进行一次粗糙查询以过滤掉与查询不相关的记录,然后在解密的数据上再进行一次精确查询,对粗糙...
  • Oracle数据库连接字符串

    千次阅读 2021-01-25 11:11:14
    Oracle数据库拼接字符串 字符串相加是我们工作中常见的操作,那么如何才能将两个字符串或多个字符串拼接·组合为一个字符串呢? 下文将讲述Oracle中字符串的操作方法,如下所示: 实现思路: 方式1: 使用concat连接...
  • mysql如何针对很长的字符串建立索引

    千次阅读 2019-10-10 20:30:07
    今天面试小米,面试官出了一道题,一个表有url和rank两个字段,其中url代表的是链接,值唯一,如www.baidu.com/1/2,rank代表的是这个链接的权重,范围在(1,10)。假如这张表中有1w条数据,现在要求给定一个url,...
  • 字符串类型的字段在各关系数据库中均占有重要地位。比如Oracle数据库中用于存储字符串类型数据的字段类型就超过了5种。遗憾的是,在日常工作中笔者发现很多开发者对这些类型并没有完整的认识,更不用说设计表结构时...
  • 创建数据库 CREATE DATABASE [IF NOT EXISTS] westos; 删除数据库 DROP DATABASE [IF EXISTS] westos 使用数据库USEschool`` 查看数据库SHOW DATABASES 学习思路: 对照 sqlyog可视化历史记录查看sql 固定的语法或...
  • 文章目录系列文章一、索引失效的十大原因 系列文章 一、原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》 二、一生挚友redo log、binlog《死磕MySQL系列 二》 一、索引失效的十大原因
  • 现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:mysql> create table SUser( ID bigint ...
  • 那么,类似邮箱地址这样的字符串,我们应该如何给它创建索引呢?这里简单介绍几种方法。 几种方法 比如我们有一张表user_info,存储了自增主键ID、邮箱地址、对应密码。 这里我们经常会用到的SQL操作是select email,...
  • 本文主要探讨数据库可变长字符串类型长度设计问题,最好的策略是只分配真正需要的空间,慷慨是不明智的。
  • 方法一:like SELECT * FROM 表名 WHERE 字段名 like "%字符%"; 方法二:find_in_set() 利用mysql 字符串函数 find_in_set();...SELECT * FROM users WHERE ...mysql有很多字符串函数 find_in_set(str1,s...
  • 目录 前言: 1. 实例分析 1.1 如何创建循环函数 1.2 进行查询操作 2. 索引选择 2.1 优化器 ...2.3 Mysql怎么得到索引的基数的呢?...2.4 选错索引的原因?...2.4.2 为什么选错了索引?...3. 选错索引,处理方...
  • mysql字符串与数字比较问题

    千次阅读 2021-01-18 19:20:06
    这个id的字段是varchar,建立的是字符串索引,转化为数字比较,是走不了字符串索引的。 所以需要做一下折中,如果是雪花id,则走第一种方案,否则走第二种方案: /*** @param offset* @return left是key,right是...
  • 数据库的哪些字段适合添加索引

    万次阅读 2018-03-18 14:14:31
    接着上一篇博文,谈一谈数据库中哪一些字段适合添加索引。1. 表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字,原因就是这些字段的值非常离散。MySQL ...
  • Mysql数据库字段数据类型、长度详解

    千次阅读 2021-01-19 13:21:06
    ENUM 类型字段可以从集合中取得一个值或使用 null 值,除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配,MySQL 会自动使用插入值的大小写转换成与集合中...
  • 数据库主键采用整型还是字符串

    千次阅读 2020-06-04 16:03:31
    整型的好处:速度快,自增方便。 字符串的好处:自定义方便。 主键索引会先存入内存区,如果用字符串占用内存会比较大。建议采用整型。
  • mysql 字段包含字符串的方法

    千次阅读 2019-08-09 09:37:15
    方法一:like SELECT * FROM 表名 WHERE 字段名 like "%字符%"; 方法二:find_in_set() 利用mysql 字符串函数 find_in_set(); ...SELECT * FROM users WHERE find_in_...mysql有很多字符串函数 find_in_set(str1,s...
  • 数据库】mysql哪些字段适合加索引

    千次阅读 2021-02-03 15:36:07
    数据库字段适合添加索引的有表中某个字段值离散度越高越适合,占用存储空间少的字段,存储空间固定的字段,where子句中经常用到的字段等1、表的某个字段值得离散度越高,该字段越适合选作索引的关键字。...
  • 这篇文章主要介绍了Mysql字符串字段判断是否包含某个字符串的2种方法,本文使用Like和find_in_set两种方法实现,需要的朋友可以参考下 假设有个表: 复制代码代码如下: CREATE TABLE users(id ...
  • MySQL 建立索引的时候如何排序?

    千次阅读 2021-01-19 07:47:33
    以下回复可能有需要更正的地方“表引擎在 innodb 的情况下建立 age、sex 的联合索引即可,innodb 会在建立索引时生成 btree 数据结构,这个数据结构上会隐式的加上主键“这是主键索引与二级索引之间的关联 ID,个人...
  • 2.3、text 文本类型 tinytext 可变长度,最多255个字符 text 可变长度,最多65535个字符 mediumtext 可变长度,最多2的24次方-1个字符 longtext 可变长度,最多2的32次方-1个字符 2.4、字符串类型比较 0.char类型的...
  • mysql字符串前缀索引

    千次阅读 2020-07-13 16:57:54
    mysql字符串前缀索引
  • 其中str代表从哪个字符串中搜索,substr代表搜索哪个子字符串,返回值为子字符串在原字符串中的位置,若不存在则返回0;position为开始计数位置,occurrence代表输出第几次出现的子字符串。如instr('helloworld','o'...
  • 通过sql查询语句,查询某个字段中包含特定字符串 例子:查询e_book表的types字段包含字符串"3",有下面4种方式: select * from e_book where types like "%3%"; select * from e_book where find_in_set('3', types...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 197,737
精华内容 79,094
关键字:

数据库字符串字段 索引