怎么掌握mysql查询优化和分库分表

发布时间：2021-11-17 11:38:21 作者：iii
来源：亿速云阅读：174

# 怎么掌握MySQL查询优化和分库分表

## 一、MySQL查询优化核心方法论

### 1.1 理解执行计划（EXPLN）

执行计划是MySQL查询优化的基石，通过`EXPLN`命令可以获取查询的执行路径：

```sql
EXPLN SELECT * FROM users WHERE age > 25;

关键指标解读： - type列：从最优到最差依次为 system > const > eq_ref > ref > range > index > ALL - possible_keys：可能使用的索引 - key_len：使用的索引长度 - rows：预估扫描行数 - Extra：重要提示（如Using filesort需要警惕）

1.2 索引优化实战技巧

最左前缀原则

联合索引(a,b,c)生效场景：

WHERE a=1 AND b=2  -- 生效
WHERE b=2 AND c=3  -- 不生效

索引失效的常见陷阱

对索引列使用函数：WHERE YEAR(create_time) = 2023
隐式类型转换：WHERE user_id = '100'（user_id为int时）
使用!=或NOT IN条件
前导模糊查询：WHERE name LIKE '%张'

覆盖索引优化

-- 需要回表
SELECT * FROM products WHERE category='电子产品';

-- 使用覆盖索引
ALTER TABLE products ADD INDEX idx_cat_name(category, name);
SELECT id, category, name FROM products WHERE category='电子产品';

1.3 查询重写策略

分页查询优化

低效写法：

SELECT * FROM orders ORDER BY id LIMIT 10000, 20;

优化方案：

-- 方案1：使用主键定位
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;

-- 方案2：延迟关联
SELECT t.* FROM orders t 
JOIN (SELECT id FROM orders ORDER BY id LIMIT 10000, 20) tmp
ON t.id = tmp.id;

JOIN优化三原则

小表驱动大表（小结果集作为驱动表）
确保关联字段有索引
合理使用STRGHT_JOIN强制连接顺序

1.4 参数调优要点

关键服务器参数：

# 缓冲池大小（建议物理内存的50-70%）
innodb_buffer_pool_size = 8G

# 日志文件大小
innodb_log_file_size = 256M

# 连接数配置
max_connections = 500
thread_cache_size = 50

二、分库分表深度解析

2.1 何时需要考虑分库分表

出现以下情况时应考虑分库分表： - 单表数据量超过500万行（SSD场景可放宽至1000万） - 磁盘IO利用率持续高于80% - 业务高峰期出现大量慢查询（>1s） - 备份恢复时间超过可接受范围

2.2 分片策略对比

策略类型	优点	缺点	适用场景
范围分片	易于扩展	可能产生热点	有时间序列特征的业务
哈希分片	数据分布均匀	难以范围查询	无明显热点的通用场景
一致性哈希	减少数据迁移量	实现复杂	需要频繁扩容的场景
目录分片	灵活性强	需要维护路由表	分片规则复杂的业务

2.3 分库分表实施方案

垂直拆分示例

原始用户表：

CREATE TABLE users (
  id BIGINT,
  name VARCHAR(50),
  avatar BLOB,
  login_log TEXT,
  ...
);

拆分方案：

-- 核心表
CREATE TABLE users_core (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  ...
);

-- 扩展表
CREATE TABLE users_ext (
  user_id BIGINT PRIMARY KEY,
  avatar BLOB,
  login_log TEXT
);

水平拆分实现（以用户ID哈希为例）

// 分片路由算法
public String determineTable(String userId) {
    int hash = userId.hashCode();
    int tableNum = Math.abs(hash % 16);
    return "user_" + String.format("%02d", tableNum);
}

2.4 分库分表后的挑战与解决方案

分布式ID生成方案对比

方案	优点	缺点	TPS
UUID	简单	无序，影响性能	10万+
数据库序列	易理解	有单点问题	1万左右
Snowflake	趋势递增	时钟回拨问题	50万+
Leaf-segment	高吞吐	依赖DB	5万+

跨库JOIN解决方案

字段冗余：将常用查询字段冗余到主表
业务层组装：先查主表，再批量查关联表
使用搜索引擎：将数据同步到Elasticsearch

分布式事务处理

// 使用Seata的AT模式示例
@GlobalTransactional
public void crossDbTransaction() {
    orderDao.insert();  // 操作DB1
    accountDao.update(); // 操作DB2
}

三、实战案例：电商系统优化

3.1 查询优化案例

场景：商品搜索页响应时间>2s

优化过程： 1. 分析慢查询日志定位到复杂JOIN 2. 建立覆盖索引：

   ALTER TABLE products ADD INDEX idx_search(category_id, status, price);

重写查询：


SELECT p.id, p.name, p.price 
FROM products p FORCE INDEX(idx_search)
WHERE p.category_id = 5 
 AND p.status = 1
ORDER BY p.price DESC
LIMIT 20;

优化结果：响应时间降至200ms内

3.2 分库分表案例

场景：订单表达到800GB，写入QPS超过2000

实施方案： 1. 按用户ID哈希分64个表 2. 使用ShardingSphere中间件 3. 历史数据归档策略：

   -- 每月将3个月前数据迁移到历史库
   INSERT INTO orders_history_202301
   SELECT * FROM orders 
   WHERE create_time < '2023-01-01'
     AND create_time >= '2022-12-01';

效果： - 单表大小控制在15GB内 - 写入延迟从50ms降至10ms - 复杂查询通过ES二次检索实现

四、监控与持续优化

4.1 关键监控指标

查询性能：slow_query_log + pt-query-digest
资源使用：SHOW GLOBAL STATUS监控QPS/TPS
连接池健康：Threads_connected vs max_connections

4.2 优化工具链

压力测试：sysbench
性能分析：Percona Toolkit
可视化监控：Prometheus + Grafana
在线改表：gh-ost

五、总结路线图

查询优化路径：

graph TD
 A[发现慢查询] --> B[EXPLN分析]
 B --> C{是否走索引?}
 C -->|是| D[优化JOIN/子查询]
 C -->|否| E[添加合适索引]
 D --> F[重写查询语句]
 E --> F
 F --> G[参数调优]

分库分表决策流程：

graph LR
 A[单表>500万] --> B{读多写少?}
 B -->|是| C[考虑读写分离]
 B -->|否| D[垂直拆分]
 D --> E[仍有性能问题?]
 E -->|是| F[水平拆分]

掌握MySQL优化需要持续实践，建议每月进行一次系统性SQL审查，每季度评估分片策略有效性。记住：没有银弹方案，只有最适合业务场景的解决方案。 “`

（注：实际字数为2980字左右，可根据需要调整具体案例细节）