您好,登录后才能下订单哦!
# 怎么掌握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需要警惕)
联合索引(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='电子产品';
低效写法:
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;
关键服务器参数:
# 缓冲池大小(建议物理内存的50-70%)
innodb_buffer_pool_size = 8G
# 日志文件大小
innodb_log_file_size = 256M
# 连接数配置
max_connections = 500
thread_cache_size = 50
出现以下情况时应考虑分库分表: - 单表数据量超过500万行(SSD场景可放宽至1000万) - 磁盘IO利用率持续高于80% - 业务高峰期出现大量慢查询(>1s) - 备份恢复时间超过可接受范围
策略类型 | 优点 | 缺点 | 适用场景 |
---|---|---|---|
范围分片 | 易于扩展 | 可能产生热点 | 有时间序列特征的业务 |
哈希分片 | 数据分布均匀 | 难以范围查询 | 无明显热点的通用场景 |
一致性哈希 | 减少数据迁移量 | 实现复杂 | 需要频繁扩容的场景 |
目录分片 | 灵活性强 | 需要维护路由表 | 分片规则复杂的业务 |
原始用户表:
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
);
// 分片路由算法
public String determineTable(String userId) {
int hash = userId.hashCode();
int tableNum = Math.abs(hash % 16);
return "user_" + String.format("%02d", tableNum);
}
方案 | 优点 | 缺点 | TPS |
---|---|---|---|
UUID | 简单 | 无序,影响性能 | 10万+ |
数据库序列 | 易理解 | 有单点问题 | 1万左右 |
Snowflake | 趋势递增 | 时钟回拨问题 | 50万+ |
Leaf-segment | 高吞吐 | 依赖DB | 5万+ |
// 使用Seata的AT模式示例
@GlobalTransactional
public void crossDbTransaction() {
orderDao.insert(); // 操作DB1
accountDao.update(); // 操作DB2
}
场景:商品搜索页响应时间>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内
场景:订单表达到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二次检索实现
slow_query_log
+ pt-query-digestSHOW GLOBAL STATUS
监控QPS/TPSThreads_connected
vs max_connections
查询优化路径:
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字左右,可根据需要调整具体案例细节)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。