-- 為經(jīng)常用于查詢條件的字段創(chuàng)建索引
CREATE INDEX idx_user_status_created ON users(status, created_at);
?
-- 為經(jīng)常用于ORDER BY的字段創(chuàng)建索引
CREATE INDEX idx_order_time ON orders(order_time DESC);
?
-- 復(fù)合索引遵循最左前綴原則
CREATE INDEX idx_user_age_city ON users(age, city);

-- 覆蓋索引示例
CREATE INDEX idx_user_info ON users(id, name, email, status);
-- 這樣查詢時就不需要回表了
SELECT id, name, email, status FROM users WHERE id = 1001;

-- 不推薦：沒有索引支持的模糊查詢
SELECT * FROM products WHERE product_name LIKE '%手機%';
?
-- 推薦：使用前綴匹配
SELECT * FROM products WHERE product_name LIKE '手機%';
?
-- 推薦：使用全文索引
ALTER TABLE products ADD FULLTEXT(product_name);
SELECT * FROM products WHERE MATCH(product_name) AGAINST('手機');

-- 不推薦：大表之間的JOIN
SELECT o.*, u.name FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.order_date > '2024-01-01';
?
-- 推薦：先過濾再JOIN
SELECT o.*, u.name FROM 
(SELECT * FROM orders WHERE order_date > '2024-01-01') o
JOIN users u ON o.user_id = u.id;

-- 不推薦：OFFSET太大時性能很差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
?
-- 推薦：使用游標(biāo)分頁
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 優(yōu)化前
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT,
    status VARCHAR(50),
    age INT,
    score DECIMAL(10,2)
);
?
-- 優(yōu)化后
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT,
    status TINYINT,  -- 用數(shù)字代替字符串
    age TINYINT UNSIGNED,  -- 年齡用TINYINT足夠
    score DECIMAL(5,2)  -- 根據(jù)實際需要調(diào)整精度
);

-- 按月分表
CREATE TABLE orders_202401 LIKE orders;
CREATE TABLE orders_202402 LIKE orders;
CREATE TABLE orders_202403 LIKE orders;
?
-- 創(chuàng)建分區(qū)表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    -- 其他字段
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)*100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404)
);

-- 按用戶ID哈希分表
CREATE TABLE users_0 LIKE users;
CREATE TABLE users_1 LIKE users;
CREATE TABLE users_2 LIKE users;
CREATE TABLE users_3 LIKE users;
?
-- 應(yīng)用層路由邏輯
-- user_table = "users_" + (user_id % 4)

-- 原始大表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    -- 基礎(chǔ)信息
    real_name VARCHAR(50),
    phone VARCHAR(20),
    -- 擴展信息（很少查詢）
    biography TEXT,
    preferences JSON,
    statistics JSON
);

-- 拆分后
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    real_name VARCHAR(50),
    phone VARCHAR(20)
);

CREATE TABLE user_extended (
    user_id INT PRIMARY KEY,
    biography TEXT,
    preferences JSON,
    statistics JSON,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 主庫配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

-- 從庫配置
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

# 偽代碼示例
class DatabaseRouter:
    def route_query(self, sql):
        if sql.startswith(('SELECT', 'SHOW')):
            return slave_connection
        else:
            return master_connection

-- 創(chuàng)建歸檔表
CREATE TABLE orders_archive LIKE orders;

-- 歸檔6個月前的數(shù)據(jù)
INSERT INTO orders_archive 
SELECT * FROM orders 
WHERE order_date < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- 刪除已歸檔的數(shù)據(jù)
DELETE FROM orders 
WHERE order_date < DATE_SUB(NOW(), INTERVAL 6 MONTH);

#!/bin/bash
# 每日凌晨執(zhí)行的清理腳本
mysql -u root -p database_name << EOF
DELETE FROM log_table 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) 
LIMIT 10000;
EOF

-- 開啟查詢緩存
SET GLOBAL query_cache_size = 268435456;  -- 256MB
SET GLOBAL query_cache_type = ON;

# 使用Redis緩存熱點數(shù)據(jù)
import redis

def get_user_info(user_id):
    cache_key = f"user:{user_id}"
    cached = redis_client.get(cache_key)
    
    if cached:
        return json.loads(cached)
    
    # 從數(shù)據(jù)庫查詢
    user = query_database(user_id)
    
    # 緩存結(jié)果
    redis_client.setex(cache_key, 3600, json.dumps(user))
    return user

[mysqld]
# InnoDB緩沖池大小，建議設(shè)置為物理內(nèi)存的70-80%
innodb_buffer_pool_size = 8G

# 日志文件大小
innodb_log_file_size = 1G

# 并發(fā)線程數(shù)
innodb_thread_concurrency = 16

# 查詢緩存
query_cache_size = 256M
query_cache_type = 1

# 臨時表大小
tmp_table_size = 256M
max_heap_table_size = 256M

-- 查看慢查詢
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看表大小
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

-- 分析表統(tǒng)計信息
ANALYZE TABLE your_large_table;

-- 優(yōu)化表（整理碎片）
OPTIMIZE TABLE your_large_table;

-- 檢查表完整性
CHECK TABLE your_large_table;

-- 添加復(fù)合索引
CREATE INDEX idx_user_status_date ON orders(user_id, status, order_date);

ALTER TABLE orders 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

# 查詢操作走從庫
orders = slave_db.query("SELECT * FROM orders WHERE user_id = ?", user_id)

# 寫入操作走主庫
master_db.execute("INSERT INTO orders (...) VALUES (...)")

-- 不推薦
DELETE FROM logs WHERE created_at < '2024-01-01';

-- 推薦
DELIMITER ;;
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    REPEAT
        DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
        SELECT SLEEP(0.1); -- 短暫休息避免影響其他操作
    UNTIL ROW_COUNT() = 0 END REPEAT;
END;;
DELIMITER ;