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

索引失效的常见陷阱

  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 关键监控指标

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字左右,可根据需要调整具体案例细节)

推荐阅读:
  1. 12C环境下分库分表改造查询优化
  2. MySQL 查询优化

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:ZFS文件系统有哪些功能特点

下一篇:jquery如何获取tr里面有几个td

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》