精华内容
下载资源
问答
  • 今天这篇文章就来探讨一下在 Mysql如何给一个字符串加索引才能达到性能最佳。本文首发于作者的微信公众号【 码猿技术专栏 】,原创不易,喜欢的朋友支持一下,谢谢!!!陈某将会从 什么是前缀索引 、 前缀索引和...

    导读

    现代大部分的登录系统都支持邮箱、手机号码登录两种方式,那么如何在邮箱或者手机号码这个字符串上建立索引才能保证性能最佳呢?

    今天这篇文章就来探讨一下在 Mysql 中如何给一个字符串加索引才能达到性能最佳。

    本文首发于作者的微信公众号【 码猿技术专栏 】,原创不易,喜欢的朋友支持一下,谢谢!!!

    陈某将会从 什么是前缀索引 、 前缀索引和普通索引的比较 、 如何建丽最佳性能的前缀索引 、 前缀索引对覆盖索引的影响 这几段来讲。

    前缀索引

    顾名思义,对于列值较长,比如 BLOB 、 TEXT 、 VARCHAR ,就 "必须" 使用 前缀索引 ,即将值的前一部分作为索引。因为索引的存储也是需要空间的,同样索引太长维护起来也比较困难。

    比如我们给 User 表中的邮箱添加前缀索引,如下:

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

    上述语句将email的前7个字符作为索引。

    前缀索引和普通索引比较

    我们分别将 email 的全部作为索引和前7个字符作为索引来看看在性能上有什么差异。建立索引的语句如下:

    alter table user add index index1(email);

    alter table user add index index2(email(7));

    假设有 user 表中有这样几条数据(id,name,email): (1,"陈某","chenmou1993@xxx") 、 (2,"张某","chenmou1994@xxx") 、 (3,"李某","chenmou1995@xxx") 、 。

    对应于index1和index2的索引树如下两张图:

    3471da708b0452dc640ec6155dcc48c9.png

    43a8250c23960dd025cfa06d68c87d63.png

    如果执行下面的查询语句,Mysql如何利用索引来查询呢?

    select * from user where email="chenmou1995@xxx";

    【1】普通索引的执行过程

    从index1索引树找到满足索引值是 chenmou1995@xxx 的这条记录,取得 id=2 的值;

    到主键上查到主键值是 id=2 的行,判断email的值是正确的,将这行记录加入结果集;

    取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email=chenmou1995@xxx 的条件了,循环结束。

    这个过程中,只需要回主键索引取一次数据,所以系统认为 只扫描了一行 。

    【2】前缀索引的执行过程

    从index2索引树找到满足索引值是 chenmou 的记录,找到的第一个是id=1;

    到主键上查到主键值是id=1的行,判断出email的值不是 chenmou1995@xxx ,这行记录丢弃;

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

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

    在这个过程中,要回主键索引取4次数据, 也就是扫描了4行。

    通过以上查询的对比,很容易就可以发现, 使用前缀索引后,可能会导致查询语句读数据的次数变多。

    但是对于这个查询语句来说,如果建立的前缀索引的长度为13呢?那么满足 chenmou1995 的记录只有一个,这样就可以直接定位到 id=2 ,此时不但空间缩小了,扫描的行数也减少了。

    于是结论就来了: 使用前缀索引,只要定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

    那么如何建立正确的前缀索引才能达到最佳的性能呢?接着往下看................

    如何建立最佳性能的前缀索引

    通过上述的比较,可以得出一个结论, 建立前缀索引的区分度越高越好,意味着重复的键值越少 。

    那么如何统计区分度,其实很简单,只需要判断 数据库 中重复的次数即可。sql如下:

    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;

    但是如果对于使用前缀区分度不太好的情况,比如,我们国家的身份证号,一共18位,其中前6位是地址码,所以同一个县的人的身份证号前6位一般会是相同的。 这时候如果对身份证号做长度为6的前缀索引的话,这个索引的区分度就非常低了。

    按照我们前面说的方法,可能你需要创建长度为12以上的前缀索引,才能够满足区分度要求。

    但是,索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也就会越低。

    那么,如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求,还有没有别的处理方法呢?这种方法,既可以占用更小的空间,也能达到相同的查询效率。现在简单的介绍一种解决此种问题的方式,当然方法肯定不止一种,如下:

    倒序存储

    如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写:

    select field_list from t where id_card = reverse('输入的身份证号');

    由于身份证号的 最后6位 没有地址码这样的重复逻辑,所以最后这6位很可能就提供了足够的区分度。当然了,实践中你不要忘记使用 count(distinct) 方法去做个验证。

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

    前缀索引会导致覆盖索引失效,查询语句如下:

    select id,name from user where email="chenmou1995@xxx";

    由于使用了前缀索引,因此必须会 回表 验证查询到的时候正确,此处使用了覆盖索引也是无效的。

    也就是说,使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。

    总结

    如何给字符串加索引是一个需要考量的问题,陈某在这里给出如下的建议:

    如果字符串长度很短,建议直接用全部作为索引。

    使用前缀索引注意分析区分度,区分度越高越好。

    使用前缀索引需要考虑覆盖索引失效的问题。

    展开全文
  • 1、前缀索引这里有这样一张用户表:mysql> create table SUser(ID bigint unsigned primary key,email varchar(64), //邮箱字段...)engine=innodb;mysql> alter table SUser add index index1(email);或mysql&...

    1、前缀索引

    这里有这样一张用户表:

    mysql> create table SUser(

    ID bigint unsigned primary key,

    email varchar(64), //邮箱字段

    ...

    )engine=innodb;

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

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

    上面的语句,假设是分别给SUser这张表的邮箱字段添加索引,一个邮箱的字符可能很长,使用第二种方式,占用的空间会更小。

    不过这里同时带来的损失是,可能会增加额外的记录扫描次数。

    使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。但是在建立前缀索引时,关注的时区分度。区分度越高,意味着重复的键值越少,查询时扫描B+搜索树的次数越少。因此,我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

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

    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。

    tips:前缀索引会导致覆盖索引不可用,因为虽然就算在非主键索引树上找到这个键,但是依然需要回表去主键索引上查找一次,系统并不能确定前缀索引的定义是否截断了完整信息。

    其他方式

    对于邮箱这样的字段来说,使用前缀索引的效果可能还不错,但是对于身份证这样前缀区分度不好的字段来说,如何建立索引呢?

    1、利用倒叙存储。

    查询时可以这么写:

    mysql> select * from t where id_card = reverse('身份证号码');

    2、使用hash字段。

    可以再创建一个整数字段,保存身份证的校验码,同时在该字段上创建索引。

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

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

    and id_card='input_id_card_string'

    不过这两种方式都不支持范围查询了。

    总结

    直接创建完整索引,这样可能比较占用空间;

    创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;

    倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;

    创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。

    展开全文
  • Mysql性能优化:如何给字符串加索引
    展开全文
  • 今天这篇文章就来探讨一下在Mysql如何给一个字符串加索引才能达到性能最佳。本文首发于作者的微信公众号【码猿技术专栏】,喜欢的读者关注一下,谢谢!!!Mysql性能优化:如何给字符串加索引?​mp.weixin.qq.com...

    导读现代大部分的登录系统都支持邮箱、手机号码登录两种方式,那么如何在邮箱或者手机号码这个字符串上建立索引才能保证性能最佳呢?

    今天这篇文章就来探讨一下在Mysql中如何给一个字符串加索引才能达到性能最佳。

    本文首发于作者的微信公众号【码猿技术专栏】,喜欢的读者关注一下,谢谢!!!Mysql性能优化:如何给字符串加索引?​mp.weixin.qq.comb80e372cf3a20574a03ed399f4714285.png陈某将会从「什么是前缀索引」、「前缀索引和普通索引的比较」、「如何建丽最佳性能的前缀索引」、「前缀索引对覆盖索引的影响」这几段来讲。

    前缀索引顾名思义,对于列值较长,比如BLOB、TEXT、VARCHAR,就 "必须" 使用「前缀索引」,即将值的前一部分作为索引。因为索引的存储也是需要空间的,同样索引太长维护起来也比较困难。

    比如我们给User表中的邮箱添加前缀索引,如下:

    alter table user add index index1(email(7));上述语句将email的前7个字符作为索引。

    前缀索引和普通索引比较我们分别将email的全部作为索引和前7个字符作为索引来看看在性能上有什么差异。建立索引的语句如下:

    alter table user add index index1(email);

    alter table user add index index2(email(7));假设有user表中有这样几条数据(id,name,email):(1,"陈某","chenmou1993@xxx")、(2,"张某","chenmou1994@xxx")、(3,"李某","chenmou1995@xxx")、(4,"王某","chenmou1996@xxx")。

    对应于index1和index2的索引树如下两张图:码猿技术专栏码猿技术专栏如果执行下面的查询语句,Mysql如何利用索引来查询呢?

    select * from user where email="chenmou1995@xxx";

    「【1】普通索引的执行过程」从index1索引树找到满足索引值是chenmou1995@xxx的这条记录,取得id=2的值;

    到主键上查到主键值是id=2的行,判断email的值是正确的,将这行记录加入结果集;

    取index1索引树上刚刚查到的位置的下一条记录,发现已经不满足email=chenmou1995@xxx的条件了,循环结束。

    这个过程中,只需要回主键索引取一次数据,所以系统认为「只扫描了一行」。

    「【2】前缀索引的执行过程」从index2索引树找到满足索引值是chenmou的记录,找到的第一个是id=1;

    到主键上查到主键值是id=1的行,判断出email的值不是chenmou1995@xxx,这行记录丢弃;

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

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

    在这个过程中,要回主键索引取4次数据,「也就是扫描了4行。」通过以上查询的对比,很容易就可以发现,「使用前缀索引后,可能会导致查询语句读数据的次数变多。」

    但是对于这个查询语句来说,如果建立的前缀索引的长度为13呢?那么满足chenmou1995的记录只有一个,这样就可以直接定位到id=2,此时不但空间缩小了,扫描的行数也减少了。

    于是结论就来了:「使用前缀索引,只要定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。」

    那么如何建立正确的前缀索引才能达到最佳的性能呢?接着往下看................

    如何建立最佳性能的前缀索引通过上述的比较,可以得出一个结论,「建立前缀索引的区分度越高越好,意味着重复的键值越少」。

    那么如何统计区分度,其实很简单,只需要判断数据库中重复的次数即可。sql如下:

    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;但是如果对于使用前缀区分度不太好的情况,比如,我们国家的身份证号,一共18位,其中前6位是地址码,所以同一个县的人的身份证号前6位一般会是相同的。 这时候如果对身份证号做长度为6的前缀索引的话,这个索引的区分度就非常低了。

    按照我们前面说的方法,可能你需要创建长度为12以上的前缀索引,才能够满足区分度要求。

    但是,索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也就会越低。

    那么,如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求,还有没有别的处理方法呢?这种方法,既可以占用更小的空间,也能达到相同的查询效率。现在简单的介绍一种解决此种问题的方式,当然方法肯定不止一种,如下:

    「【1】倒序存储」

    如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写:

    select field_list from t where id_card = reverse('输入的身份证号');

    由于身份证号的「最后6位」没有地址码这样的重复逻辑,所以最后这6位很可能就提供了足够的区分度。当然了,实践中你不要忘记使用count(distinct)方法去做个验证。

    前缀索引对覆盖索引的影响前缀索引会导致覆盖索引失效,查询语句如下:

    select id,name from user where email="chenmou1995@xxx";由于使用了前缀索引,因此必须会「回表」验证查询到的时候正确,此处使用了覆盖索引也是无效的。

    也就是说,使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。

    总结如何给字符串加索引是一个需要考量的问题,陈某在这里给出如下的建议:如果字符串长度很短,建议直接用全部作为索引。

    使用前缀索引注意分析区分度,区分度越高越好。

    使用前缀索引需要考虑覆盖索引失效的问题。

    展开全文
  • MySQL如何快速的加索引

    千次阅读 2018-01-26 18:17:06
    加索引时间久可能因为如下原因: 1:添加索引的字段表大 索引构建过程中需要操作的数据量较大 2:物理磁盘性能较差 索引结构构建的效率低 3:alter事务可能在等待其他锁释放 4:系统资源被其他服务占用,发生资源...
  • 如何给字符串加索引?索引的选择前缀索引对覆盖索引的影响其它方式总结 索引的选择 例如对于一个支持邮箱登录的系统,如何在这个字段上建立合理的索引? 在email字段上创建索引的语句如下: alter table SUser add ...
  • 现在主流网站都支持手机号登录,如何在手机号这样的字符串字段建立合适的索引呢? 假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的: create table SUser( ID bigint unsigned primary key, email ...
  • 今天这篇文章就来探讨一下在Mysql如何给一个字符串加索引才能达到性能最佳。本文首发于作者的微信公众号【码猿技术专栏】,喜欢的读者关注一下,谢谢!!!Mysql性能优化:如何给字符串加索引?​mp.weixin.qq.com...
  • [TOC]现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:mysql> create table SUser(ID ...
  • 声明:本文章内容是根据极客时间中林晓斌的课程《MYSQL45讲》,经过学习,加以自己的理解形成的笔记。具体原文可以到官网进行阅读。如有侵权请,告知删除。...例如:一个emali 字段添加索引的时候。可以这样。 mys...
  • 总结: 问题:
  • 一、Mysql大数据量问题与解决方案 Mysql单表适合的最大数据量是多少? 我们说Mysql单表适合存储的最大数据量,自然不是说能够存储的最大数据量,如果是说...影响Mysql单表的最优最大数量的一个重要因素其实是索引。 我
  • 闲扯很多时候我们面对很慢的查询的时候会一筹莫展,这个时候大部分人都会很自然的想到建索引这条路。...那么如何给一个表加索引,这其实是个很复杂又很简单的问题,首先我们需要了解索引。一个案例...
  • 前缀索引顾名思义,对于列值较长,比如BLOB...比如我们User表中的邮箱添加前缀索引,如下:alter table user add index index1(email(7));上述语句将email的前7个字符作为索引。前缀索引和普通索引比较我们分别将e...
  • 现在主流网站都支持手机号登录,如何在手机号这样的字符串字段建立合适的索引呢?假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:create table SUser(ID bigint unsigned primary key,email varchar...
  • 前缀索引对覆盖索引的影响其他来字符串加索引解决方案:倒序存储和hash存储的异同点小结 问题 现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题 普通索引到...
  • 2.3 Mysql怎么得到索引的基数的呢? 2.4 选错索引的原因? 2.4.1 怎么看预计扫描的行数? 2.4.2 为什么选错了索引? 2.4.3 如果表的统计数据不准确,使用analyze table t 3. 选错索引,处理方...

空空如也

空空如也

1 2 3 4 5 ... 10
收藏数 196
精华内容 78
关键字:

如何给mysql加索引

mysql 订阅