香港服务器MySQL性能调优：利用虚拟列加速模糊查询的完整方案
在MySQL中，使用LIKE进行模糊查询时，在某些情况下是无法使用索引的。合理优化LIKE查询对于提升香港服务器的数据库性能至关重要，本文将详细介绍相关问题及优化方案。
1. LIKE查询无法使用索引的情况
在MySQL的LIKE查询中，索引的使用受到查询模式的影响，具体情况如下：
- 无法使用索引
- 当
LIKE值前后都有匹配符（%abc%） - 当
LIKE值前有匹配符（%abc）
- 当
- 可以使用索引
- 当
LIKE值后有匹配符（abc%）
- 当
这样一来，如果查询的是包含abc的字符串（%abc），就无法直接使用索引，从而导致查询性能下降。
2. LIKE %abc 真的无法优化吗？
在某些业务场景下，我们可能需要查询以abc结尾的字符串（即LIKE '%abc'）。而如果使用LIKE 'abc%'，则表示查询以abc开头的字符串。由于MySQL的索引只能优化后缀不带%的查询，我们可以使用以下方法来优化查询：
2.1 通过冗余列反转字符串
可以通过创建一个冗余列并存储字符串的反转值来优化查询。例如：
- 向表中插入数据时，反转存储在新的列中（例如
v_name）。 - 为
v_name列建立索引，使得查询可以利用索引进行优化。
示例：
ALTER TABLE test ADD COLUMN v_name VARCHAR(50);
UPDATE test SET v_name = REVERSE(name);
ALTER TABLE test ADD INDEX idx_v_name (v_name);
查询时即可使用索引：
SELECT * FROM test WHERE v_name LIKE 'cba%';
问题：
- 如果表中已有大量数据（百万级或千万级），执行
UPDATE操作反转所有数据需要较长时间。 - 这种方法会占用额外的存储空间。
3. MySQL 5.7.6 及以上版本的优化方案
MySQL 5.7.6 之后引入了虚拟列（Generated Columns），可以用于简化上述优化过程，而无需手动维护冗余数据列。
3.1 什么是虚拟列？
虚拟列的值是基于已有列计算得出的，可以分为两种：
- VIRTUAL：不会存储到磁盘，每次查询时动态计算。
- STORED：存储到磁盘，相当于物理冗余列，提高查询性能。
3.2 使用虚拟列优化LIKE查询
利用虚拟列，我们可以为字符串的反转版本创建索引：
ALTER TABLE test ADD COLUMN v_name VARCHAR(50) GENERATED ALWAYS AS (REVERSE(name)) VIRTUAL;
ALTER TABLE test ADD INDEX idx_v_name (v_name);
查询优化后：
SELECT * FROM test WHERE v_name LIKE 'cba%'
UNION
SELECT * FROM test WHERE name LIKE 'abc%';
这样，查询LIKE '%abc'就可以走索引了，大大提升查询效率。
4. 虚拟列的优势与劣势
4.1 优势
✅ 简化查询：避免手动维护冗余列
✅ 数据一致性：基础列变更时，虚拟列自动更新
✅ 减少冗余：VIRTUAL类型不会占用额外存储
✅ 索引支持：可以对虚拟列创建索引
✅ 灵活性：可以动态计算，不影响表结构
4.2 劣势
❌ 性能开销：VIRTUAL列需要每次查询计算，影响性能
❌ 存储空间：STORED列会增加磁盘占用
❌ 复杂性增加：可能增加表结构的复杂度
❌ 兼容性问题：仅支持MySQL 5.7.6及以上版本
❌ 索引限制：部分复杂表达式不支持索引
5. 性能测试：如何评估优化效果？
5.1 建表
CREATE TABLE test (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
INDEX idx_name (name)
) CHARACTER SET utf8;
5.2 插入200万条测试数据
DELIMITER //
CREATE PROCEDURE InsertTestData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 2000000 DO
IF i <= 200 THEN
SET @randomName1 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97), 'abc');
INSERT INTO test (name) VALUES (@randomName1);
ELSEIF i <= 400 THEN
SET @randomName2 = CONCAT('abc', CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97));
INSERT INTO test (name) VALUES (@randomName2);
ELSE
SET @randomName3 = CONCAT(CHAR(FLOOR(RAND() * 26) + 65), CHAR(FLOOR(RAND() * 26) + 97));
INSERT INTO test (name) VALUES (@randomName3);
END IF;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
调用存储过程插入数据：
CALL InsertTestData();
5.3 使用虚拟列优化查询
ALTER TABLE test ADD COLUMN v_name VARCHAR(50) GENERATED ALWAYS AS (REVERSE(name)) VIRTUAL;
ALTER TABLE test ADD INDEX idx_v_name (v_name);
SELECT * FROM test WHERE v_name LIKE 'cba%'
UNION
SELECT * FROM test WHERE name LIKE 'abc%';
5.4 传统方法（无优化）
SELECT * FROM test WHERE name LIKE 'abc%'
UNION
SELECT * FROM test WHERE name LIKE '%abc';
6. 总结
- 在香港服务器的MySQL数据库中，
LIKE '%abc'默认无法使用索引，查询性能较低。 - 传统方案是使用冗余列存储字符串的反转版本，然后基于该列建立索引。
- MySQL 5.7.6 及以上版本支持虚拟列，可以动态计算反转字符串，避免数据冗余，同时支持索引优化查询。
- 测试数据表明，使用虚拟列优化
LIKE '%abc'查询能够显著提升性能，使查询走索引，提高执行效率。 - 如果数据量较大且磁盘空间允许，建议使用
STORED虚拟列，否则使用VIRTUAL虚拟列。
如果你的香港服务器数据库查询LIKE '%abc'性能较低，可以尝试使用虚拟列+索引优化方案，大幅提升查询速度！