mysql之字符串字段添加索引

mysql之字符串字段添加索引

字符串创建索引方式： 1、直接创建完整索引，比较占用空间。 2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。

3、倒序存储，在创建前缀索引，用于绕过字符串本身前缀的却分度不够的问题。

4、创建hash字段索引，查询性能稳定，有额外的存储和计算消耗。 倒序存储和hash字段索引都不支持范围查询。倒序存储的字段上创建的所有是按照倒序字符串的方式排序的。hash字段的方式也只能支持等值查询。

mysql> alter table SUser add index index1(email); :包含了每个记录的整个字符串 或 mysql> alter table SUser add index index2(email(6)); :-对于每个记录只取前6个字节 全字段索引操作流程 使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的： 1、从 index1 索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录，取得 ID2 的值； 2、到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集； 3、取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email=' zhangssxyz@xxx.com ’的条件了，循环结束。 前缀字段索引操作流程 如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的： 1、从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1； 2、到主键上查到主键值是 ID1 的行，判断出 email 的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃； 3、取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集； 4、重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。 倒序查询和hash字段的区别 它们的区别，主要体现在以下三个方面： 1、从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。

当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。 2、在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

3、从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

如何利用mysql索引优化检索

索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录。

表越大，资源消耗越大。

如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。大部分MySQL索引(PRIMARYKEY,UNIQUE,INDEX和FULLTEXT)都是以B树方式存储。

只有空间类型的字段使用R树存储，MEMORY(HEAP)表支持哈希索引。字符串默认都是自动压缩前缀和后缀中的空格。通常，如下所述几种情况下可以使用索引。

哈希索引(用于MEMORY表)的独特之处在后面会讨论到。想要尽快找到匹配WHERE子句的记录。根据条件排除记录。

如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。做表连接查询时从其他表中检索记录。想要在指定的索引字段key_col上找到它的MIN()或MAX()值。

优化程序会在检查索引的key_col字段前就先检查其他索引部分是否使用了WHEREkey_part_#=constant子句。这样的话，MySQL会为MIN()或MAX()表达式分别单独做一次索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：SELECTMIN(key_part2),MAX(key_part2)FROMtbl_nameWHEREkey_part1=10;对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如ORDERBYkey_part1,key_part2)。

如果所有的索引部分都按照DESC排序，索引就按倒序排序。有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：SELECTkey_part3FROMtbl_nameWHEREkey_part1=1假设有如下SELECT语句：如果在col1和col2上有一个多字段索引的话，就能直接取得对应的记录了。希望能解决您的问题。

MySQL前缀索引

前缀索引顾名思义，定义字符串的一部分当做索引，而不是把整个字符串当做索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

假设一张表有 id,name,email 2个字段 1.创建email列的普通索引应该是： alter table T add index idx_email1( email ) 2.前缀索引的创建规则为： alter table table T add index idx_email2( email(6) ) 当然第一索引包含是的整个字符串，第二个是该字段前6个字节（注意是字节） 对于这2中索引，B+树怎么存储呢？ INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('剑圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韩信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com'); 【谁还不是个野王啊】 普通索引存储为： 是的你没看错，前缀索引那颗树上的存储的是email的前6位字节，也就是你创建前缀索引时指定的前缀字节长度。

2种树相比，前缀索引存储了更少的数据，那么他所耗费的空间也就相比较少，这正是他的一个优点。同样的也就相对的增加了扫描行数。 什么增加了扫描行数？？？？ 这是为什么呢？ 那么小朋友咱们一起来看下吧。 假设SQL如此这般： select id,name,email from T where email = 'zhangsh1234@163.com' 那么这2个SQL，应该怎么操作呢。

idx_email1: 2.到主键上查到主键为ID1的，判断email值是否正确【为什么判断呢，其实我理解是为了二次判断保证数据一致性吧，比较官方的解释尚未找到】，正确放入结果集 3.取 idx_email1 索引树上刚刚查到的位置的下一条记录，如此往复。 循环过程中，需要回主键取1次数据，所以系统可以认为只扫描了一行【1次是数第一棵树数出来的】 idx_email2: 1.从 索引数上找到满足索引值为 'zhangs'的该记录，取得 ID1的值 2.到主键上查到主键值是 ID1 的行，判断出 email 的值是’ zhangsh1234@xxx.com ’，这行记录放入结果集【不是要的值，丢弃，进行下一步】 3.取 idx_email2 上刚刚查到的位置的下一条记录,重复以上步骤 在这个过程中，要回主键索引取 3 次数据，也就是扫描了 3 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是，对于这个查询语句来说，如果你定义的 idx_email2 不是 email(6) 而是 email(8），也就是说取 email 字段的前 8 个字节来构建索引的话，即满足前缀’zhangsh’的记录只有一个，也能够直接查到 ID1，只扫描一行就结束了。也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 那么问题来了，到底定义多长才算是合理呢？ 一般的定义原则是 count(distinct(columnName))/count(*) ,当前缀索引【count(distinct(columnName(length)))，length是你想要创建列的前缀字节长度】越接近此值越好，当有多个前缀字节都一样且都等于这个值时怎么选择呢，当然是 字节越少越好了哈，字节越少越省空间。

索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。 count(distinct(columnName(length))) 翻译到SQL 为： count(dictinct(left(colunmName, length))) 前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景。

来呀，上SQL： select id,email from T where email='zhangsh1234@163.com' 如果按照email全字段索引，那么此SQL 是不需要回表的【为什么不需要回表？兄嘚，这个相当于覆盖索引了哈】 那么如果按照前缀索引是否需要回表呢？答案是的。 因为当判断前6个字节相等后，需要拿到id 回表拿到email的全部内容进行比较，如果不相同，丢弃这行，否则加入结果集。 那么有人会问了，我把长度放大点，包含所有字节不就好了吗？ 那么此时会有如下问题。 1.当你此时的长度是囊括了全字段，但是系统是不知道的，他还是需要回表再次判断的，去确定前缀索引的定义是否截断了完整信息。

2.此时长度是够了，那么能肯定因为业务日后不会增加长度吗？ 3.尽可能的加长长度，还不如直接建立全字段索引呢 综上，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。 前面说到的是，可以根据字段前面几个字节进行查询的，那么对于身份证这种，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。 或许你会说，多弄几个字节不就好吗？那么请问下自己为什么使用前缀索引呢，不就是为了节省空间吗？ 那么这么做合适吗？ 不合适对吗？ 乖~，快去反省下吧 那么采用前缀索引显示是不行的，那么如果用前缀索引怎么办呢，聪明的你应该已经猜到了，采用倒叙存储，然后建立前缀索引。 放到SQL 中就应该是这样的： select field_list from t where id_card = reverse('id_card_string'); 当然了，这种逻辑建议放到业务逻辑中实现，而不是放到SQL 中。

按照上述第4节的内容，有人或许会有另一个想法，还倒叙建立前缀索引复杂不，hash索引或者hash字段不香吗？ 有人会问了，为什么要在创建一个值来存储hash值呢，如果不存储你知道原值是什么吗？ 同时hash算法是有一定重复可能的（hash值碰撞） 【可以了解下partition算法哦:[ https://selfboot.cn/2016/09/01/lost_partition 】。如果重复了，不存储原值，你是无法判断出正确数据的。 注：【hash字段不代表hash索引，hash索引原理正在快马加鞭】，简单说下hash索引，hash索引不需要创建一个值来存储hash值，而是有hasn表来存储【hash值碰撞时，由一个链表来搞定了】，存储的内容为 hash值和每行的行指针 。 说回来啊，跑题了 查询时： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string' 不过有个问题相信你也想到了，不管是hash存储值还是hash索引都是不支持范围查询的。

来总结下这2个优缺点吧 1.从占用空间来看呢，倒叙索引不需要额外开辟存储空间，而hash字段需要额外的一个字段，所以从这点上看倒叙索引更胜一筹，NO!并不准确,如果前缀长度过长，那么这2个情况额外的空间也就相差无几了 3.从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数 1.全字段完整索引比较占空间，但是而走覆盖索引 2.前缀索引，节省空间，但会增加扫描 次数 并且不能使用覆盖索引【每次都需回表校验】 3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。

Mysql索引

https://blog.csdn.net/weixin_43935927/article/details/109491334建立索引，要使用离散度（选择度）更高的字段。 我们先来看一个重要的属性列的 离散度， count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数 数据行数相同的情况下，分子越大，列的离散度就越高。

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引 create table shop(address varchar(120) not null); alter table shop add key(address(12));  // 截取12个字符作为前缀索引是最优的吗？ 问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？ 先看一下字段在全部数据中的选择度计算公式： select count(distinct address) / count(*) from shop; select count(distinct left(address, n)) / count(*) as subn from shop; count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。

举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address，2) 所以，截取的长度越长就会越接近字段在全部数据中的选择度 ==>所以，我们要权衡索引大小和查询速度。 举个例子，通过不同长度去计算，与全表的选择性对比：     SELECT  COUNT(DISTINCT(address))/COUNT(*) sub,            -- 字段在全部数据中的选择度     COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,  -- 截取前5个字符的选择度     COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,      COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,     COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,  -- 截取前10个字符的选择度     COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,     COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,     COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,     COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15 FROM shop;+--------+--------+--------+--------+--------+--------+--------+--------+--------+ | sub    | sub5  | sub7  | sub9  | sub10  | sub11  | sub12  | sub13  | sub15  | +--------+--------+--------+--------+--------+--------+--------+--------+--------+ | 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 | +--------+--------+--------+--------+--------+--------+--------+--------+--------+ 可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了，而且长度也相较后面更短一些， 综合考虑比较合适。

mysql索引类型和索引方式