MySQL索引的优化之LIKE模糊查询功能实现

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

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

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

-- 创建覆盖索引
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 SELECT * FROM users WHERE username LIKE '%doe';
​
-- 优化后的查询（使用全文索引）
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

-- 创建分区表
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),
    jsPARTITION 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%';