字符串索引、幻读的解决方法

1. 给字符串加索引

1.1. 前缀索引vs完整索引

假设有一个支持邮箱登录的系统，用户表定义如下：

CREATE TABLE SUser(
  ID BIGINT UNSIGNED PRIMARY KEY,
  email VARCHAR(64),
  ...
) ENGINE=InnoDB;

为了根据邮箱查询用户信息，常用的查询语句如下：

SELECT f1, f2 FROM SUser WHERE email='xxx';

• 如果 email 字段上没有索引，查询将执行全表扫描，这会极大影响查询性能。

  前缀索引 vs 完整索引

  MySQL 支持前缀索引，即在创建索引时只使用字段的一部分。创建前缀索引的方法如下：

  ALTER TABLE SUser ADD INDEX index1(email);     -- 完整索引
  ALTER TABLE SUser ADD INDEX index2(email(6));  -- 前缀索引（前6个字节）

  • index1 索引包含完整的 email 字段。
  • index2 索引只包含 email 字段的前 6 个字节。

    前缀索引的优势：

    • 占用更少的存储空间。
    • 查询时扫描的数据量较小。

      但缺点是：

      • 增加了额外的记录扫描次数，因为前缀索引的区分度较低。

        查询执行过程对比

        假设我们要执行以下查询：

        SELECT id, name, email FROM SUser WHERE email='zhangssxyz@xxx.com';

        • 使用完整索引 (index1)：
          1. 从 index1 中找到匹配的 email，返回对应 ID。
          2. 查主键索引获取完整记录。
          3. 一次性返回数据，查询效率较高。
          • 使用前缀索引 (index2)：
            1. 从 index2 找到匹配前缀 zhangs 的记录。
            2. 返回多个记录，需要多次回主键索引确认完整匹配。
            3. 增加了额外的查询次数，查询效率较低。

            选择前缀索引长度

            选择前缀索引时，我们关注的是“区分度”——区分度越高，索引的效果越好。可以通过以下步骤判断前缀长度：

            1. 计算不同值的个数：

            SELECT COUNT(DISTINCT email) AS L FROM SUser;

            2. 检查不同前缀长度的区分度：

            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;

            3. 选择合适的前缀长度：

            例如，设定区分度损失在 5% 内，选择满足条件的前缀长度。

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

            
            （图片来源网络，侵删）

            使用前缀索引可能无法利用覆盖索引的优势。覆盖索引可以在不回表的情况下直接返回查询结果，但前缀索引无法直接满足这一点。

            • 覆盖索引：如果查询的字段都包含在索引中，MySQL 可以直接从索引中获取结果，避免回表查询。
            • 前缀索引：如果使用前缀索引，MySQL 仍然需要回表获取完整的字段值，影响查询性能。

              1.2. 其他优化方法

              对于一些具有低区分度的字段（如身份证号），前缀索引可能不够有效。此时可以采用以下两种优化方法：

              
              （图片来源网络，侵删）

              1. 倒序存储：

              对于区分度不够的字段（如身份证号），可以将字段倒序存储，这样最后几位通常会有足够的区分度。

              
              （图片来源网络，侵删）

              SELECT field_list FROM t WHERE id_card = REVERSE('input_id_card_string');

              2. 哈希字段：

              可以创建一个哈希字段，通过计算哈希值存储字段的 CRC32 值，并在该字段上建立索引。

              ALTER TABLE t ADD id_card_crc INT UNSIGNED, ADD INDEX(id_card_crc);
              SELECT field_list FROM t WHERE id_card_crc = CRC32('input_id_card_string') AND id_card = 'input_id_card_string';

              总结：

              在 MySQL 中，字符串字段的索引策略应根据业务需求和数据特性来选择。以下是几种常用策略：

              • 完整索引：占用较多空间，适用于较短或较高区分度的字段。
              • 前缀索引：节省空间，适用于长字段，但可能增加查询扫描行数，且不能使用覆盖索引。
              • 倒序存储：适用于有明显前缀区分度的字段，可以提高查询效率。
              • 哈希字段索引：适用于等值查询，减少存储空间消耗，但不支持范围查询。

                选择合适的索引策略，可以显著提升查询性能，并在空间和速度之间取得平衡。

                2. 幻读及解决方法

                幻读的概念：

                幻读（Phantom Read） 是数据库事务中一种特殊的并发问题，指的是在 同一个事务中执行两次相同条件的查询操作时，第二次查询返回了第一次查询未曾出现过的新记录。

                幻读出现的形式：

                幻读主要发生在使用 当前读（select ... for update / lock in share mode） 时，而不是快照读。即便在默认的 可重复读（REPEATABLE READ） 隔离级别下，普通的快照读不会出现幻读，但 当前读操作 由于要读取最新数据，是可能产生幻读的。

                幻读的危害：

                1. 破坏语义：Session A 认为它锁住了所有 d=5 的行，但事实上 Session B 和 C 仍可以修改或插入符合条件的行，违背了加锁的初衷。
                2. 破坏一致性：会导致 binlog 重放/主备同步产生不一致。比如 A 的更新语句是：

                UPDATE t SET d=100 WHERE d=5;

                • 如果 A 在执行前并不知道 B 和 C 已经插入或更新了符合条件的行，那么在 binlog 重放中，这些“幽灵记录”也会被更新，导致和主库数据不一致。

                  幻读的应对措施：

                  • InnoDB 为了解决幻读问题，在当前读时会 加“间隙锁”（gap lock） 和 临键锁（next-key lock），不仅锁住已存在的行，还锁住可能插入的位置。
                  • 对于范围查询加 for update，InnoDB 会在扫描过程中对所有满足条件的记录和可能插入新记录的间隙都加锁，从而防止其他事务插入“幻影行”。