欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MySQL索引使用及优化

程序员文章站 2022-07-07 09:17:26
#3. 索引使用策略及优化##3.1 索引的好处##3.2 什么情况下可以用到B树索引(1) 定义有主键的列一定要建立索引 : 主键可以加速定位到表中的某行(2) 定义有外键的列一定要建立索引 : 外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接(3) 对于经常查询的数据列最好建立索引① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间② 经常用在 where子句中的数据列，将索引建立在where子句的...

1 索引的好处

  • 大大减少存储引擎需要扫描的数据量
  • 排序以避免使用临时表
  • 把随机I/O变为顺序I/O

2 实例

执行 select * from T where k between 3 and 5，需要几次树的搜索，扫描多少行？

  • 创建表
    MySQL索引使用及优化

  • 插入数据MySQL索引使用及优化

  • InnoDB索引组织结构
    MySQL索引使用及优化

SQL查询语句的执行流程：

  1. 在k索引树找到k=3，取得 ID 300
  2. 再到ID树查到ID 300对应的R3
  3. 在k树取下个值5，取得ID 500
  4. 再回到ID树查到ID 500对应R4
  5. 在k树取下个值6，不满足条件，循环结束

回到主键索引树搜索的过程，称为回表。
查询过程读了k索引树的3条记录（步骤135），回表两次（24）
由于查询结果所需数据只在主键索引有，不得不回表。那么，有无可能经过索引优化，避免回表？

3 覆盖索引

执行语句
select ID from T where k between 3 and 5

只需查ID值，而ID值已在k索引树，因此可直接提供结果，不需回表。即在该查询，索引k已“覆盖”我们的查询需求，称为覆盖索引。

覆盖索引可减少树的搜索次数，显著提升查询性能，使用覆盖索引是个常用性能优化手段。

使用覆盖索引在索引k上其实读了三个记录，R3~R5（对应的索引k上的记录项）
但对于Server层，就是找引擎拿到两条记录，因此MySQL认为扫描行数是2。

问题

在一个市民信息表，有必要将身份证号和名字建立联合索引？

假设这个市民表的定义：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

身份证号是市民唯一标识。有根据身份证号查询市民信息的，只要在身份证号字段建立索引即可。再建立一个（身份证号、姓名）联合索引，是不是浪费空间？

如果现在有一个高频请求，根据身份证号查询姓名，联合索引就有意义了。可在这个高频请求上用到覆盖索引，不再回表查整行记录，减少了执行时间。
当然索引字段的维护总是有代价。建立冗余索引支持覆盖索引就需权衡考虑。

2 何时用索引

(1) 定义有主键的列一定要建立索引 : 主键可以加速定位到表中的某行
(2) 定义有外键的列一定要建立索引 : 外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接
(3) 对于经常查询的数据列最好建立索引
① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间
② 经常用在 where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间

如果为每一种查询都设计个索引，索引是不是太多？
如果我现在要按身份证号去查家庭地址？虽然该需求概率不高，但总不能让它全表扫描？
但单独为一个不频繁请求创建（身份证号，地址）索引又有点浪费。怎么做？

B+树这种索引，可利用索引的“最左前缀”，来定位记录。

为了直观地说明这个概念，用（name，age）联合索引分析。
MySQL索引使用及优化

索引项按照索引定义出现的字段顺序排序。

当逻辑需求是查到所有名字“张三”的，可快速定位到ID4，然后向后遍历得到所有结果。
要查所有名字第一个字“张”的，条件"where name like ‘张%’"。也能够用上索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足。

不只是索引的全部定义，只要满足最左前缀，就可利用索引加速。
最左前缀可以是

  • 联合索引的最左N个字段
  • 字符串索引的最左M个字符

联合索引的内的字段顺序

  • 标准
    索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。
  • 原则
    如果调整顺序，可少维护一个索引，那么这顺序优先考虑。

为高频请求创建(身份证号，姓名）联合索引，并用这索引支持“身份证号查地址”需求。

如果既有联合查询，又有基于a、b各自的查询？
查询条件里只有b的，无法使用(a,b)联合索引，这时不得不维护另外一个索引，即需同时维护(a,b)、(b) 两个索引。

  • 这时要考虑原则就是空间
    比如市民表，name字段比age字段大 ，建议创建一个（name,age)的联合索引和一个(age)的单字段索引

3 索引优化

MySQL的优化主要分为

  • 结构优化（Scheme optimization）
  • 查询优化（Query optimization）

讨论的高性能索引策略主要属于结构优化。

为了讨论索引策略，需要一个数据量不算小的数据库作为示例
选用MySQL官方文档中提供的示例数据库之一：employees
这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

MySQL索引使用及优化

3.1 最左前缀原理与相关优化

要知道什么样的查询会用到索引，和B+Tree中的“最左前缀原理”有关。

联合索引

MySQL中的索引可以以一定顺序引用多列，这种索引叫做联合索引，是个有序元组<a1, a2, …, an>。
MySQL索引使用及优化

覆盖索引(Covering Indexes)

包含满足查询的所有列。

只需读索引而不用读数据，大大提高查询性能。

优点

(1)索引项通常比记录要小，使得MySQL访问更少的数据
(2)索引都按值排序存储，相对于随机访问记录，需要更少的I/O
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引
(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

覆盖索引只有B-TREE索引存储相应的值
并不是所有存储引擎都支持覆盖索引(Memory/Falcon)
MySQL索引使用及优化

对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra列中看到Using index

在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖
但是，InnoDB不限于此，InnoDB的二级索引在叶节点中存储了primary key的值
MySQL索引使用及优化
MySQL索引使用及优化
MySQL索引使用及优化
MySQL索引使用及优化
MySQL索引使用及优化

以employees.titles表为例，下面先查看其上都有哪些索引：
MySQL索引使用及优化
从结果中可以看到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>
为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），我们将辅助索引drop掉

ALTER TABLE employees.titles DROP INDEX emp_no;

这样就可以专心分析索引PRIMARY

情况一：全值匹配

MySQL索引使用及优化
很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。
这里有一点需要注意，理论上索引对顺序敏感，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引
例如我们将where中的条件顺序颠倒
MySQL索引使用及优化
效果是一样的

情况二：最左前缀匹配

MySQL索引使用及优化
当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀
上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

MySQL索引使用及优化
此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行过滤from_date（这里由于emp_no唯一，所以不存在扫描）
如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引
除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上

首先我们看下title一共有几种不同的值
MySQL索引使用及优化
只有7种
在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀
MySQL索引使用及优化
这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。看下两种查询的性能比较：
MySQL索引使用及优化
“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引

情况四：查询条件没有指定索引第一列

MySQL索引使用及优化
由于不是最左前缀，这样的查询显然用不到索引

情况五：匹配某列的前缀字符串

MySQL索引使用及优化
此时可以用到索引，通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

情况六：范围查询(由于B+树的顺序特点,尤其适合此类查询)

MySQL索引使用及优化

  • 范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引
  • 索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引
    MySQL索引使用及优化
  • 可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range
  • 用了“between”并不意味着就是范围查询，例如下面的查询：
    MySQL索引使用及优化

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。
MySQL索引使用及优化

情况七：查询条件中含有函数或表达式

如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）
MySQL索引使用及优化
虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。再如：
MySQL索引使用及优化
显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。
MySQL索引使用及优化

最左前缀可用于在索引中定位记录。那不符合最左前缀的部分，会怎么样？

以市民表的联合索引（name, age）为例。

  • 需求
    检索表中“名字第一个字是张，且年龄是10的所有男孩”

SQL：

select * from tuser where name like '张%' and age=10 and ismale=1;

语句在搜索索引树时，只能用 “张”，找到第一个满足条件记录ID3。还不错，总比全表扫好。然后判断其他条件。
MySQL5.6前，只能从ID3开始个个回表，到主键索引上找数据行，再对比字段值。
5.6引入索引下推优化（index condition pushdown)， 在索引遍历过程，对索引中包含的字段先做判断，直接过滤不满足条件的记录，减少回表。

这两个过程的执行流程图：

  • 无索引下推执行流程
    MySQL索引使用及优化
  • 索引下推执行流程
    MySQL索引使用及优化

两个图里面，每一个虚线箭头表示回表一次。
无索引下推执行流程，在(name,age)索引里特意去掉age的值，这过程InnoDB并不看age的值，只按顺序把“name第一个字是’张’”的记录一条条取出来回表，回表4次。

区别是，InnoDB在(name,age)索引内部就开始判断了age是否等于10，对不等10的记录，直接判断并跳过。这个例子中，只需对ID4、ID5这两条记录回表取数据判断，只需回表2次。

3.4 Btree索引的使用限制

MySQL索引使用及优化

3.4.1 以下情况下设置索引,但无法使用

① 以“%”开头的LIKE语句，模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

3.4.2 索引选择性与前缀索引

MySQL索引使用及优化

既然索引可以加快查询速度，那么是不是只要是查询语句，就建上索引呢?

答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间

  • 索引会加重插入、删除和修改记录时的负担，增加写操作的成本
  • 太多索引会增加查询优化器的分析选择时间
  • MySQL在运行时也要消耗资源维护索引
    ##索引并不是越多越好。下列情况下不建议建索引
  • 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引
    例如，在查询中很少使用的列，有索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间
    又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度
    对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引
  • 表记录比较少
    例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了
  • 索引的选择性较低
    所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值
    Index Selectivity = Cardinality / #T
    显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。
    例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性
    MySQL索引使用及优化
    title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

从图12可以看到employees表只有一个索引<emp_no>，那么如果我们 想按名字搜索一个人，就只能全表扫描了：
MySQL索引使用及优化

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：
MySQL索引使用及优化

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

MySQL索引使用及优化

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

MySQL索引使用及优化

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引 建上：

  1. ALTER TABLE employees.employees
  2. ADD INDEX first_name_last_name4 (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：
MySQL索引使用及优化
性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

3.5 InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。
如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

MySQL索引使用及优化
这样就会形成一个紧凑的索引结构，近似顺序填满
由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
MySQL索引使用及优化
此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

与排序（ORDER BY）相关的索引优化及覆盖索引（Covering index）的话题本文并未涉及，
全文索引等等本文也并未涉及

4 Hash索引

MySQL其实提供了四种索引

  • B-Tree 索引:最常见的的索引，大部分引擎支持B树索引
  • Hash索引:只有Memory引擎支持，场景简单
  • R-Tree索引:空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • Full-text：全文索引也是MyISAM的一个特殊索引，主要用于全文索引，InnoDb从MySql5.6开始提供支持全文索引

MySql目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符索引，这样的特性大大缩小了索引文件的大小，但前缀索引也有缺点，在排序order by和分组group by操作的时候无法使用

create index idx_title on film(title(10));
索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree索引 支持 支持 支持
HASH索引 不支持 不支持 支持
R-Tree索引 支持 不支持 不支持
Full-text索引 支持 暂不支持 不支持

常用的索引就是B-tree索引和Hash索引。
只有Memory引擎支持HASH索引，Hash索引适用于key-value查询，通过Hash索引比B-tree索引查询更加迅速。
但是Hash索引不支持范围查找例如<><==,>==等操作，如果使用memory引擎并且where不使用=进行 索引列，就不会用的索引。

Memory只有在"="的条件下才会使用索引

4.0 特点

值放在数组
用一个哈希函数把key换算成一个确定位置，然后把value放在数组的这个位置。
不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

Hash索弓|基于Hash表实现，只有查询条件精确匹配Hash索引中所有列时,才能够使用到hash索引
对于Hash索引中的所有列,存储引擎会为每一行计算一个Hash码, Hash索引中存储的就是Hash码。

假设维护一个身份证信息和姓名的表，根据身份证号查找对应的名字，对应的哈希索引如下：
MySQL索引使用及优化

User2和User4根据身份证号算出值都N，没关系，跟个链表。
假设，要查ID_card_n2对应的名字

  • 首先，将ID_card_n2通过哈希函数算出N
  • 然后，按顺序遍历，找到User2

四个ID_card_n值并不是递增，好处是增加新的User时速度快，只需往后追加。
缺点不是有序，所以哈希索引做区间查询速度慢。要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描。

4.1 Hash索引的限制

  • 必须进行二次查找
  • 无法用于排序
  • 不支持部分索引查找、范围查找
  • Hash码的计算可能存在Hash冲突，碰撞很多的话，性能也会变差
  • 索引存放的是hash值,所以仅支持 < = > 以及 IN
  • 无法通过操作索引来排序，因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序
  • 不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值MySQL索引使用及优化MySQL索引使用及优化
    哈希索引只有Memory,NDB两种引擎支持，Memory引擎默认支持哈希索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储

但是，Memory引擎表只对能够适合机器的内存切实有限的数据集。

要使InnoDB或MyISAM支持哈希索引，可以通过伪哈希索引来实现，叫自适应哈希索引。

主要通过增加一个字段，存储hash值，将hash值建立索引，在插入和更新的时候，建立触发器，自动添加计算后的hash到表里。

所以，哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。

直接索引

假如有一个非常非常大的表，如下：

CREATE TABLE IF NOT EXISTS `User` (
  `id` int(10) NOT NULL COMMENT '自增id',
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `email` varchar(128) NOT NULL DEFAULT '' COMMENT '用户邮箱',
  `pass` varchar(64) NOT NULL DEFAULT '' COMMENT '用户密码',
  `last` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后登录时间',
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这个时候，比如说，用户登陆，我需要通过email检索出用户，通过explain得到如下：

mysql> explain SELECT id FROM User WHERE email = ‘ooxx@gmail.com’ LIMIT 1;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | User  | ALL  | NULL          | NULL | NULL    | NULL | 384742 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

发现 rows = 384742 也就是要在384742里面进行比对email这个字段的字符串。

这条记录运行的时间是：Query took 0.1744 seconds，数据库的大小是40万。

从上面可以说明，如果直接在email上面建立索引，除了索引区间匹配，还要进行字符串匹配比对，email短还好，如果长的话这个查询代价就比较大。

如果这个时候，在email上建立哈希索引，查询以int查询，性能就比字符串比对查询快多了。

Hash 算法

建立哈希索引，先选定哈希算法，这里选用CRC32。

《高性能MySQL》说到的方法CRC32算法，建立SHA或MD5算法是划算的，本身位数都有可能比email段长了。

INSERT UPDATE SELECT 操作

在表中添加hash值的字段：

mysql> ALTER TABLE User ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0;

接下来就是在UPDATE和INSERT的时候，自动更新 email_hash 字段，通过MySQL触发器实现：

DELIMITER |
CREATE TRIGGER user_hash_insert BEFORE INSERT ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
CREATE TRIGGER user_hash_update BEFORE UPDATE ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
DELIMITER ;

这样的话，我们的SELECT请求就会变成这样：

mysql> SELECT email, email_hash FROM User WHERE email_hash = CRC32(“F2dgTSWRBXSZ1d3O@gmail.com”) AND email= “F2dgTSWRBXSZ1d3O@gmail.com”;

+----------------------------+------------+
| email                      | email_hash |
+----------------------------+------------+
| F2dgTSWRBXSZ1d3O@gmail.com | 2765311122 |
+----------------------------+------------+

在没建立hash索引时候，请求时间是 0.2374 seconds，建立完索引后，请求时间直接变成 0.0003 seconds。

AND email = "F2dgTSWRBXSZ1d3O@gmail.com" 是为了防止哈希碰撞导致数据不准确。

本文地址：https://blog.csdn.net/qq_33589510/article/details/107398177

相关标签： MySQL

上一篇： 存储raid5两块盘掉线导致数据丢失的恢复全过程

下一篇： SSD固态硬盘变FDD格式怎么办？SSD固态硬盘变FDD格式处理方法

推荐阅读