...
| 代码块 |
|---|
|
CREATE TABLE orders (
user_id INT,
order_date DATE,
INDEX idx_user_date (user_id, order_date) -- 组合索引
);
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);
CREATE INDEX idx_user_date ON orders (user_id, order_date); |
其他特殊场景：
(1) 前缀索引：对字段的前 N 个字符创建索引（适用于长文本字段）
| 代码块 |
|---|
|
ALTER TABLE users ADD INDEX idx_name_prefix (name(10)); -- 仅索引前10个字符 |
...
| 代码块 |
|---|
|
ALTER TABLE users ADD INDEX idx_year (YEAR(create_time)); -- 按年份创建索引 |
(3) 隐藏索引（MySQL 8.0+）: 创建不可见索引（用于测试索引是否有效）
| 代码块 |
|---|
|
ALTER TABLE users ADD INDEX idx_age (age) INVISIBLE; |
删除索引：
| 代码块 |
|---|
|
ALTER TABLE users DROP INDEX idx_name;
DROP INDEX idx_name ON users; |
...
② 针对数据量特别巨大的情况下，Mysql查询都会走局部扫描或者全局扫描，极大地增加扫描时间，因此将大表拆分成小的分区表（基于时间或者id增加参数作为分区键等），可以极大的提升查询速度，减少不必要的扫描
1. RANGE 分区（按日期范围）
| 代码块 |
|---|
|
-- 按年份分区存储订单数据
CREATE TABLE sales (
id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
region VARCHAR(20)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION pmax VALUES LESS THAN MAXVALUE -- 处理未来数据
); |
2. RANGE COLUMNS 分区（多列范围）
| 代码块 |
|---|
|
-- 按日期和金额组合范围分区
CREATE TABLE sales (
id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
region VARCHAR(20)
)
PARTITION BY RANGE COLUMNS(order_date, amount) (
PARTITION p2023_low VALUES LESS THAN ('2024-01-01', 1000), -- 2023年且金额<1000
PARTITION p2024_high VALUES LESS THAN ('2025-01-01', 5000),
PARTITION pmax VALUES LESS THAN (MAXVALUE, MAXVALUE)
); |
3. LIST 分区（按地区编号）
| 代码块 |
|---|
|
-- 按预定义的地区编号分区
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50),
region_id INT
)
PARTITION BY LIST (region_id) (
PARTITION p_east VALUES IN (1, 2, 3), -- 东部地区
PARTITION p_west VALUES IN (4, 5, 6), -- 西部地区
PARTITION p_other VALUES IN (DEFAULT) -- 其他区域
); |
4. HASH 分区（均匀分布数据）
| 代码块 |
|---|
|
-- 按用户ID哈希分散到4个分区
CREATE TABLE user_logs (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
log_time DATETIME,
content TEXT
)
PARTITION BY HASH(user_id)
PARTITIONS 4; -- 指定分区数量 |
5. KEY 分区（简化哈希）
| 代码块 |
|---|
|
-- 按用户名的哈希值分区
CREATE TABLE messages (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
message TEXT
)
PARTITION BY KEY(username)
PARTITIONS 5; -- 分区数量 |
6. 复合分区（RANGE + HASH）
| 代码块 |
|---|
|
-- 先按年份范围分区，再按用户ID哈希子分区
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE NOT NULL,
user_id INT,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 ( -- 每个主分区下分4个子分区
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION pmax VALUES LESS THAN MAXVALUE
); |
添加新分区（RANGE 示例）
| 代码块 |
|---|
|
-- 为2023年新增分区（需确保分区连续）
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
); |
删除分区
| 代码块 |
|---|
|
-- 删除指定分区（数据会丢失！）
ALTER TABLE sales DROP PARTITION p2020; |
查询分区元数据
| 代码块 |
|---|
|
-- 查看表的分区信息
SELECT
PARTITION_NAME,
PARTITION_METHOD,
PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales'; |
使用存储过程定期自动根据实际创建分区表：
| 代码块 |
|---|
|
-- 创建存储过程，自动插入分区表
DELIMITER $$
CREATE PROCEDURE AddNextPartition()
BEGIN
DECLARE next_month VARCHAR(6);
-- 计算下个月，例如当前如果数据需要新增 '202401' 分区
SET next_month = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m');
SET @sql = CONCAT(
'ALTER TABLE revenue_detail_table REORGANIZE PARTITION pmax INTO (',
'PARTITION p', next_month, ' VALUES LESS THAN (\'',
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y%m'), '\'), ',
'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- 创建事件，每月检查并自动增加分区
CREATE EVENT IF NOT EXISTS AutoAddPartition
ON SCHEDULE EVERY 1 MONTH
DO CALL AddNextPartition(); |
...
