MySQL--索引的优化--LIKE模糊查询

在使用MySQL进行模糊查询时，LIKE语句的性能可能会受到较大影响，尤其是在数据量较大的情况下。

但本质上，用like进行模糊查询，只有以下三种情况：

• 前缀匹配：如果模糊查询是前缀匹配（如 LIKE '%abc' ）,MySQL可以使用索引来加速查询。确保在相关列上创建了索引

• 后缀匹配：对于后缀匹配（如LIKE 'abc%'），MySQL无法使用普通的B-tree索引。可以考虑使用反向索引（Reverse Index）或全文索引（Full-Text Index）

• 中间匹配：对于中间匹配（如LIKE '%abc%'）,MySQL也无法使用普通的B-tree索引。全文索引或搜索引擎（如Elasticsearcha）可能是更好的选择。

  一、前缀匹配优化

  前缀匹配（如LIKE 'abc%'）可以使用B-tree索引，因此性能较好。确保在相关列上创建索引

  示例：

  -- 创建表
  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(255) NOT NULL
  );
  ​
  -- 插入数据
  INSERT INTO users (username) VALUES ('john_doe'), ('jane_doe'), ('alice'), ('bob'), ('john_smith');
  ​
  -- 创建索引
  CREATE INDEX idx_username ON users(username);
  ​
  -- 前缀匹配查询
  EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

  • 执行计划分析

    • 如果使用了索引，EXPLAIN结果中的key列会显示idx_username,表明查询使用了索引

    • type会显示range，表示使用了索引范围扫描

      二、后缀匹配优化

      后缀匹配（如LIKE '%abc'），无法直接使用B-tree索引，可以通过反转字符串并创建索引来优化

      示例：

      -- 添加反转列
      ALTER TABLE users ADD COLUMN reversed_username VARCHAR(255);
      ​
      -- 更新反转列数据
      UPDATE users SET reversed_username = REVERSE(username);
      -- REVERSE('hello') 的结果是 'olleh'
      ​
      -- 创建反转列索引
      CREATE INDEX idx_reversed_username ON users(reversed_username);
      ​
      -- 后缀匹配查询（转换为前缀匹配）
      EXPLAIN SELECT * FROM users WHERE reversed_username LIKE REVERSE('doe') + '%';

      • 执行计划分析

        • 查询反转后的列时，EXPLAIN结果中的key列会显示idx_reversed_username，表明使用了索引

        • type列会显示range，表示使用了索引范围扫描

          
          （图片来源网络，侵删）

          三、中间匹配优化

          中间匹配（如LIKE '%abc%'）无法使用B-tree索引。可以考虑使用全文索引或外部搜索引擎

          示例（使用全文索引）

          
          （图片来源网络，侵删）

          -- 创建全文索引
          CREATE FULLTEXT INDEX idx_username_fulltext ON users(username);
          ​
          -- 全文索引查询
          EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

          • 执行计划分析：

            • EXPLAIN结果中的key列会显示idx_username_fulltext，表明使用了全文索引

              
              （图片来源网络，侵删）

            • type列会显示fulltext，表示使用了全文索引

              四、覆盖索引优化

              如果查询只需要返回索引列，可以使用覆盖索引（Covering index），避免回表操作

              示例：

              -- 创建覆盖索引
              CREATE INDEX idx_username_covering ON users(username, id);
              ​
              -- 覆盖索引查询
              EXPLAIN SELECT username FROM users WHERE username LIKE 'john%';

              五、减少查询范围

              通过其他条件缩小查询范围，减少模糊查询的数据量

              示例：

              -- 假设有一个注册时间列
              ALTER TABLE users ADD COLUMN registered_at DATETIME;
              ​
              -- 插入数据
              UPDATE users SET registered_at = NOW() - INTERVAL FLOOR(RAND() * 365) DAY;
              ​
              -- 缩小查询范围
              EXPLAIN SELECT * FROM users 
              WHERE registered_at > '2023-01-01' 
              AND username LIKE 'john%';

              • 执行计划分析

                • EXPLAIN结果中的key列会显示idx_username,表明使用了索引

                • rows列的值会减少，表明查询范围缩小

                  六、避免通配符开头

                  尽量避免在LIKE语句中使用通配符开头（如%abc），因为这种查询无法使用索引

                  示例：

                  -- 不推荐的查询
                  EXPLAIN SELECT * FROM users WHERE username LIKE '%doe';
                  ​
                  -- 优化后的查询（使用全文索引）
                  EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

                  • 执行计划分析：

                    • 不推荐的查询中，type列会显示ALL，表示全表扫描。

                    • 优化后的查询中，type列会显示fulltext，表示使用了全文索引。

                      七、使用外部搜索引擎

                      对于复杂的模糊查询需求，尤其是大数据量场景，可以使用外部搜索引擎（如Elatsticsearch）

                      示例

                      1. 将数据同步到Elasticsearch。

                      2. 使用Elasticsearch进行模糊查询。

                      八、分区表优化

                      如果数据量非常大，可以使用分区表（Partitioning），来较少每次查询需要扫描的数据量

                      示例：

                      -- 创建分区表
                      CREATE TABLE users_partitioned (
                          id INT PRIMARY KEY AUTO_INCREMENT,
                          username VARCHAR(255) NOT NULL,
                          registered_at DATETIME
                      ) PARTITION BY RANGE (YEAR(registered_at)) (
                          PARTITION p0 VALUES LESS THAN (2020),
                          PARTITION p1 VALUES LESS THAN (2021),
                          PARTITION p2 VALUES LESS THAN (2022),
                          PARTITION p3 VALUES LESS THAN (2023),
                          PARTITION p4 VALUES LESS THAN MAXVALUE
                      );
                      ​
                      -- 插入数据
                      INSERT INTO users_partitioned (username, registered_at) 
                      SELECT username, registered_at FROM users;
                      ​
                      -- 分区表查询
                      EXPLAIN SELECT * FROM users_partitioned 
                      WHERE registered_at > '2023-01-01' 
                      AND username LIKE 'john%';

                      • 执行计划分析：

                        • EXPLAIN结果中的partitions列会显示查询涉及的分区，表明查询只扫描了部分数据。

                          九、缓存结果

                          如果模糊查询的结果不经常变化，可以将查询结果缓存起来，减少数据库的查询压力

                          示例：

                          • 使用redis缓存查询结果

                          • 设置缓存的过期时间，确保数据的时效性

                            总结

                            通过以上方法，可以显著优化MySQL中LIKE模糊查询的性能。根据具体的业务需求和数据特点，选择合适的优化策略：

                            • 前缀匹配：使用普通索引。

                            • 后缀匹配：使用反转索引。

                            • 中间匹配：使用全文索引或外部搜索引擎。

                            • 大数据量：使用分区表或外部搜索引擎。

                            • 高频查询：使用缓存。

                              注：了解MySQL-MATCH ... AGAINST工具参考MySQL-MATCH ... AGAINST工具