mysql前缀索引 默认长度,mysql 索引长度、区分度和前缀索引

好的索引

1、查询频繁 2、 区分度高 3、长度小 4、尽量能覆盖常用查询字段

索引长度的重要性

索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度(占用内存多)

针对列的值，从左往右截取部分来来建立索引。即是使用前缀索引

①、截的越短，重复读越高，区分度就越小，索引效果并不好

②、截的越长，重复读越低，区分度越高。索引效果越好，但是需要更多的空间存储索引文件。增删改变慢。

所以我们需要在 区分度+长度 两者行取得一个平衡。我们可以截取不同的长度，并测试其区分度。

使用前缀索引

1、语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

实操的难度：在于前缀截取的长度。

2、在navicat中创建前缀索引

image.png

区分度计算

我们可以利用selectcount(distinct left(password,prefixLen))/count(*); 通过从调整prefixLen的值(从1自增)查看不同前缀长度的一个平均匹配度，接近1时就可以了(表示一个密码的前prefixLen个字符几乎能确定唯一一条记录)

下面对前缀索引进行下实践，建立一张表syfg。里面的password字段添加了一个 INDEX idx_password(password(10)) USING BTREE 索引

CREATE TABLE `test`.`syfg` (

`id` int(11) NOT NULL,

`a` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`c` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`d` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`password` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',

PRIMARY KEY (`id`) USING BTREE,

INDEX `idx_password`(`password`(10)) USING BTREE COMMENT '密码前缀索引'

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `test`.`syfg`(`id`, `a`, `b`, `c`, `d`, `passwrod`) VALUES (1, 'a', 'b', 'c', 'd', '202cb962ac59075b964b07152d234b70');

INSERT INTO `test`.`syfg`(`id`, `a`, `b`, `c`, `d`, `passwrod`) VALUES (2, 'a1', 'b1', 'c1', 'd1', 'caf1a3dfb505ffed0d024130f58c5cfa');

执行下列语句，数值越接近于1就越好。根据这个值调整前缀索引长度。

select count(distinct left(password,10))/count(*) FROM syfg

需要经过反复修改截取位数。进行反复的测试，直到这个区分度接近1。这样前缀索引的性能才是最好的时候。

关于前缀索引的特定情况优化设计

比如存储url的列，他们的前7个8个字符总是相同的。若对其设置前缀索引需要截取更大的空间才能保证一定的区分度。这样建立的索引效率非常低。

https://www.bilibili.com

https://www.baidu.com

1、所以我们可以将之倒过来存储并建立前缀索引，这样区分度会比较轻易提高

mysql中的字符串反转使用REVERSE()函数

SELECT REVERSE('https://www.baidu.com')

2、或者可以使用crc32()函数将url构造为一个伪hash列，转成整型。降低索引的长度，从而提高查询效率。

具体使用可以看看这个 https://www.jianshu.com/p/93d91f5192a0