数据库调优

# MySQL错误日志
/var/log/mysql/error.log

# 慢查询日志
/var/log/mysql/slow.log

# 系统日志
/var/log/syslog

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 2;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

# CPU使用情况
top -p $(pidof mysqld)

# 内存使用情况
free -m

# 磁盘I/O
iostat -x 1

# 网络连接
netstat -anp | grep mysql

-- 查看当前活动会话
SHOW PROCESSLIST;

-- 查看完整进程列表
SHOW FULL PROCESSLIST;

-- 查看锁等待情况
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看支持的存储引擎
SHOW ENGINES;

-- InnoDB：支持事务、行级锁、外键（推荐）
-- MyISAM：不支持事务、表级锁、读性能好

1. 基础原则：遵循第三范式（3NF）
2. 查询优化：适度反范式化
3. 字段选择：合适的数据类型
4. 存储引擎：根据需求选择

-- 数值类型选择
-- 好的选择：使用数值类型
status TINYINT          -- 状态码
price DECIMAL(10,2)     -- 价格

-- 不好的选择：使用字符类型
status VARCHAR(10)      -- 存储'active'、'inactive'
price VARCHAR(20)       -- 字符串存储数值

-- 字符类型选择
-- 固定长度：CHAR
phone CHAR(11)          -- 手机号
id_card CHAR(18)        -- 身份证号

-- 可变长度：VARCHAR
name VARCHAR(50)        -- 姓名
address VARCHAR(200)    -- 地址

-- 根据实际需求设置合理长度
-- 好的选择
username VARCHAR(32)    -- 用户名通常不超过32字符
email VARCHAR(100)      -- 邮箱通常不超过100字符

-- 不好的选择
username VARCHAR(255)   -- 过长浪费存储
email TEXT              -- 大字段影响性能

-- 低效写法（索引失效）
SELECT comment_id, comment_text, comment_time 
FROM product_comment 
WHERE SUBSTRING(comment_text, 1, 3) = 'abc';

-- 高效写法（使用索引）
SELECT comment_id, comment_text, comment_time 
FROM product_comment 
WHERE comment_text LIKE 'abc%';

-- 低效写法：相关子查询
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customer c WHERE c.id = o.customer_id AND c.level = 'VIP');

-- 高效写法：JOIN查询
SELECT o.* 
FROM orders o
INNER JOIN customer c ON o.customer_id = c.id
WHERE c.level = 'VIP';

-- 当子查询结果集较大时
-- 低效写法
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customer WHERE level = 'VIP');

-- 高效写法
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customer c WHERE c.id = o.customer_id AND c.level = 'VIP');

-- 低效写法
SELECT * FROM product_comment WHERE product_id = 10001;

-- 高效写法：只查询需要的字段
SELECT comment_id, comment_text, comment_time 
FROM product_comment 
WHERE product_id = 10001;

-- 适合创建索引的字段
-- WHERE条件中频繁使用的字段
CREATE INDEX idx_product_id ON product_comment(product_id);

-- JOIN关联字段
CREATE INDEX idx_customer_id ON orders(customer_id);

-- ORDER BY排序字段
CREATE INDEX idx_create_time ON orders(create_time);

-- 不适合创建索引的字段
-- 性别字段（区分度低）
-- 状态字段（取值少）
-- 频繁更新的字段

-- 遵循最左前缀原则
-- 联合索引 (a, b, c)
CREATE INDEX idx_abc ON orders(a, b, c);

-- 可以使用索引的查询
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能使用索引的查询
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

-- 避免索引失效的情况

-- 1. 避免对索引字段进行运算
-- 索引失效
WHERE YEAR(create_time) = 2023
-- 索引有效
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

-- 2. 避免隐式类型转换
-- 索引失效（phone是VARCHAR类型）
WHERE phone = 13800138000
-- 索引有效
WHERE phone = '13800138000'

-- 3. 避免使用NOT IN、<>、!=
-- 索引可能失效
WHERE status != 1
-- 改写为
WHERE status IN (0, 2, 3)

-- 连接方式选择

-- 1. 嵌套循环连接（Nested Loop）
-- 适合：小表驱动大表
SELECT * FROM small_table s JOIN large_table l ON s.id = l.sid;

-- 2. 哈希连接（Hash Join）
-- 适合：大表等值连接
SELECT * FROM table_a a JOIN table_b b ON a.id = b.aid;

-- 3. 排序合并连接（Sort Merge）
-- 适合：已排序的数据
SELECT * FROM sorted_table_a a JOIN sorted_table_b b ON a.id = b.id;

缓存使用流程：
1. 查询请求先访问缓存
2. 缓存命中：直接返回
3. 缓存未命中：查询数据库，结果写入缓存

# 方案1：缓存空值
def get_data(key):
    data = cache.get(key)
    if data is not None:
        return data if data != 'NULL' else None
    
    data = db.query(key)
    if data is None:
        cache.set(key, 'NULL', timeout=60)  # 缓存空值
    else:
        cache.set(key, data, timeout=3600)
    return data

# 方案2：布隆过滤器
def get_data_with_bloom(key):
    if not bloom_filter.might_contain(key):
        return None  # 确定不存在
    # 继续正常查询流程

架构示意：
┌─────────┐     写请求     ┌─────────┐
│         │ ──────────────>│  Master │
│ 应用服务 │               └─────────┘
│         │                     │
└─────────┘               主从同步
     │                         │
     │ 读请求              ┌────┴────┐
     └────────────────────>│  Slave  │
                           └─────────┘

// Spring配置读写分离
@Configuration
public class DataSourceConfig {
    
    @Bean
    @Primary
    public DataSource masterDataSource() {
        return DataSourceBuilder.create()
            .url("jdbc:mysql://master:3306/db")
            .build();
    }
    
    @Bean
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create()
            .url("jdbc:mysql://slave:3306/db")
            .build();
    }
}

单库结构：
┌─────────────────────────┐
│      单一数据库          │
│  ┌─────┐ ┌─────┐ ┌─────┐│
│  │用户表│ │订单表│ │商品表││
│  └─────┘ └─────┘ └─────┘│
└─────────────────────────┘

垂直分库后：
┌─────────┐  ┌─────────┐  ┌─────────┐
│ 用户库   │  │ 订单库   │  │ 商品库   │
│┌───────┐│  │┌───────┐│  │┌───────┐│
││用户表  ││  ││订单表  ││  ││商品表  ││
│└───────┘│  │└───────┘│  │└───────┘│
└─────────┘  └─────────┘  └─────────┘

-- 按ID范围分表
orders_0: id 1-1000000
orders_1: id 1000001-2000000
orders_2: id 2000001-3000000

-- 按Hash分表
orders_0: id % 4 = 0
orders_1: id % 4 = 1
orders_2: id % 4 = 2
orders_3: id % 4 = 3

-- 原始查询
SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-06';

EXPLAIN SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-06';

-- 结果：type=ALL，全表扫描，索引失效

-- 优化后的查询
SELECT * FROM orders 
WHERE create_time >= '2023-06-01' AND create_time < '2023-07-01';

-- 添加索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 创建历史表
CREATE TABLE orders_history LIKE orders;

-- 迁移历史数据
INSERT INTO orders_history 
SELECT * FROM orders WHERE create_time < '2022-01-01';

-- 删除已迁移数据
DELETE FROM orders WHERE create_time < '2022-01-01';

-- 按月分区
ALTER TABLE orders PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

调优优先级：
1. SQL优化和索引优化（成本最低，效果最明显）
2. 缓存策略（有效减少数据库压力）
3. 表结构优化（需要评估影响）
4. 读写分离（架构调整）
5. 分库分表（最后的手段）