Tiaoooo
27 天前
试一下分区后加索引呢
以下内容来自 ai：
-- 假设我们有一个名为 'user_activities' 的表
CREATE TABLE user_activities (
id INT AUTO_INCREMENT,
user_id INT,
activity_type VARCHAR(50),
status ENUM('是', '否'),
created_at TIMESTAMP,
PRIMARY KEY (id, status)
) ENGINE=InnoDB;
-- 按 status 列进行分区
ALTER TABLE user_activities
PARTITION BY LIST COLUMNS(status) (
PARTITION p_yes VALUES IN ('是'),
PARTITION p_no VALUES IN ('否')
);
-- 插入一些示例数据
INSERT INTO user_activities (user_id, activity_type, status, created_at) VALUES
(1, '登录', '是', NOW()),
(2, '购买', '否', NOW()),
(3, '评论', '是', NOW()),
(4, '浏览', '否', NOW());
-- 查询 status 为 '是' 的记录
EXPLAIN SELECT * FROM user_activities WHERE status = '是';
-- 添加索引以进一步优化查询
CREATE INDEX idx_status_created_at ON user_activities(status, created_at);
-- 再次解释查询计划
EXPLAIN SELECT * FROM user_activities WHERE status = '是' ORDER BY created_at DESC LIMIT 10;
-- 查看分区信息
SELECT PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_activities';