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

# 怎么掌握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 -- 不生效 

索引失效的常见陷阱

1. 对索引列使用函数：WHERE YEAR(create_time) = 2023
2. 隐式类型转换：WHERE user_id = '100'（user_id为int时）
3. 使用!=或NOT IN条件
4. 前导模糊查询：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优化三原则

1. 小表驱动大表（小结果集作为驱动表）
2. 确保关联字段有索引
3. 合理使用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解决方案

1. 字段冗余：将常用查询字段冗余到主表
2. 业务层组装：先查主表，再批量查关联表
3. 使用搜索引擎：将数据同步到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); 

1. 重写查询：

    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 优化工具链

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

五、总结路线图

1. 查询优化路径：

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

2. 分库分表决策流程：

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

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

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