-- 創(chuàng)建時間范圍分區(qū)表
CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) 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 p_max VALUES LESS THAN MAXVALUE
);

-- 查詢指定分區(qū)
SELECT * FROM orders PARTITION (p2022) WHERE user_id=123;

-- 創(chuàng)建分片表（user_id取模分64張表）
CREATE TABLE user_00 LIKE users;
CREATE TABLE user_01 LIKE users;
...
CREATE TABLE user_63 LIKE users;

-- 分片查詢路由
SELECT * FROM user_${user_id % 64} WHERE user_id=123456;

-- 創(chuàng)建覆蓋索引
ALTER TABLE orders ADD INDEX idx_cover (user_id, order_date, amount);

-- 強制索引使用
SELECT /*+ INDEX(orders idx_cover) */ order_date, amount 
FROM orders 
WHERE user_id=123 AND order_date > '2024-01-01';

-- 歸檔歷史數(shù)據(jù)
CREATE TABLE orders_archive LIKE orders;
INSERT INTO orders_archive SELECT * FROM orders 
WHERE order_date < '2023-01-01';
DELETE FROM orders WHERE order_date < '2023-01-01';

-- 創(chuàng)建聯(lián)合視圖
CREATE VIEW orders_union AS
SELECT * FROM orders 
UNION ALL
SELECT * FROM orders_archive;

-- 低效分頁改造
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;

-- 避免全表掃描
SELECT user_id FROM orders FORCE INDEX(primary) 
WHERE create_time BETWEEN '2024-01-01' AND '2024-03-01';

-- 創(chuàng)建列式存儲表
CREATE TABLE logs (
    id BIGINT,
    log_time DATETIME,
    content TEXT
) ENGINE=Columnstore;

# my.cnf核心參數(shù)
[mysqld]
innodb_buffer_pool_size=128G
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=20000
innodb_read_io_threads=16
query_cache_type=0

-- 批量插入優(yōu)化
INSERT INTO orders (user_id,amount) 
VALUES (1,100),(2,200),(3,300);

-- 延遲更新
SET GLOBAL innodb_flush_log_at_trx_commit=2;
START TRANSACTION;
...批量操作...
COMMIT;

-- TiDB分布式查詢
SELECT /*+ READ_FROM_STORAGE(tikv[orders]) */ *
FROM orders 
WHERE user_id IN (SELECT user_id FROM vip_users);

-- IP地址存儲優(yōu)化
ALTER TABLE access_log 
MODIFY ip INT UNSIGNED,
ADD INDEX idx_ip (ip);

-- 枚舉類型改造
ALTER TABLE users 
MODIFY gender ENUM('M','F') NOT NULL DEFAULT 'M';