大纲介绍
一、前缀索引
1.1普通索引和前缀索引的区别
1.1.1数据结构和存储上的区别
1.1.2两种索引执行sql对比
1.2 如何确定前缀索引的长度
1.3 前缀索引对覆盖索引的影响
二、倒序存储
三、hash字段
一、前缀索引
普通索引和前缀索引的区别
1、语法上的区别
mysql> alter table users add index index1(email); //普通索引
mysql> alter table users add index index2(email(6)); //前缀索引
2、数据结构和存储上的区别
图 1 email 索引结构
图 2 email(6) 索引结构
| 索引 | 字符长度 |
|---|---|
| 取users中email字段长度的平均值是21 | |
| email(6) | 6 |
3、两种索引执行以下sql对比
select id,name,email from users where email='zhangssxyz@xxx.com';
执行过程
如何确定前缀的长度？
step1、算出这个列上有多少不同的值L
select count(distinct email) as L from users;
step2、依次算出不同长度前缀的值。我们重点分析4-9个字节的前缀索引
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,
count(distinct left(email,8))as L8,
count(distinct left(email,9))as L9,
from users;
| L值(users表email字段) | 1669090 |
|---|---|
| 接受损失 | 5% |
| 实际记录数 | 1585636 |
| 截取长度 | L4 | L5 | L6 | L7 | L8 | L9 |
|---|---|---|---|---|---|---|
| 截取长度记录数 | 229133 | 551980 | 886492 | 1184094 | 1419519 | 1559094 |
| 占比 | 14.45% | 34.81% | 55.91% | 74.68% | 89.52% | 98.33% |
前缀索引对覆盖索引的影响
select id,email from users where email='zhangssxyz@xxx.com';
select id,name,email from users where email='zhangssxyz@xxx.com';
| 查询字段 | 索引 | 覆盖索引 |
|---|---|---|
| id,email | 是 | |
| id,email | email(6)或者email(30) | 否 |
| id,name,email | email或者email(6)或者email(30) | 否 |
二、倒序存储
mysql> select field_list from t where id_card = reverse('input_id_card_string');
适用场景：身份证
由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度
三、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'；
概念：CRC全称为Cyclic Redundancy Check，又叫循环冗余校验。CRC32是CRC算法的一种，常用于校验网络上传输的文件。
CRC32函数值范围：是0-4294967296（2的32次方减1)
int 类型占用4个字节
而char(18)，18指的是字符而不是字节（4.0版本以上，以下指的是字节）；
那么字符和字节的转换要看字符集，utf8下，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节；gbk下，一个英文等于两个字节，一个中文（含繁体）等于两个字节。
总结
今天讲了通过前缀索引来优化email字段，他的优势是节省空间。他的缺点是可能会增加查询次数。同时我们学习了如果确认前缀索引的长度来增加索引的区分度，从而减少查询次数。
对于身份证这样的字段。由于前面几位的区分度不高，所以我们介绍了倒序存储的方法，同时可以结合前缀索引一起使用。
如果我们不想使用倒序存储，我们提供了另外一种方法——hash字段。我们可以通过hash字段做快速的查找。
