GU𝐆𝐫𝐨𝐰 𝐔𝐩
返回文章列表
高并发项目
MySQLSQL优化分库分表索引

10MySQL优化

1.有哪些方向可以优化

1.1分库分表

我们可以将一个数据库/表进行拆分，将其放到多台服务器上，来缓解数据量过大、高并发状态下数据库频繁查询导致效率很低的问题。

分库分表分为垂直拆分和水平拆分两种方式：

拆分方式 拆分依据 说明
垂直分库 按业务模块拆分 将不同业务的表分配到不同的数据库中，例如电商系统拆为商品库、订单库、用户库
垂直分表 按字段拆分 将一张表中不常访问的大字段（如 text、blob）拆分到扩展表中，缩小主表体积
水平分库 按数据行的路由规则拆分 将同一张表的数据按规则（如用户 ID 取模）分散到多个结构完全相同的数据库中
水平分表 按数据行的路由规则拆分 在同一个数据库中，将数据按规则分散到多张结构完全相同的表中（如 order_1、order_2）

垂直拆分的特点是每个库/表的结构不同，拆分后业务边界清晰；水平拆分的特点是每个库/表的结构相同、数据不同，解决的是单表数据量过大的问题。

比如我们的项目之前使用的就是垂直分表（准确地说是垂直分库），将原来的数据库拆分为 5 个数据库，但是条件受限，我们无法让这 5 个数据库运行在 5 台不同的服务器上，这 5 个数据库仍然共同竞争同一台服务器的 CPU、内存、磁盘 I/O 和网络带宽，性能提升有限。

1.2读写分离

对于数据库来说，读的速度要远快于写的速度，而且绝大多数操作都是读操作（比如用户管理系统，绝大多数操作都是读取用户信息，只有创建、修改、删除用户时才会用到写操作）。

我们可以将原来的一个数据库复制出一个一模一样的副本：

  • 主库（master）：负责写操作（增、删、改）；
  • 从库（slave）：负责读操作（查询）；
  • 主库通过 binlog 将数据变更同步到从库，保持两边数据一致。

这样写压力集中在主库，读压力被多个从库分摊，从而提升整体性能。

1.3索引与SQL优化

除了架构层面的拆分，单库优化同样重要：

  • 为经常出现在 WHERE、ORDER BY、GROUP BY 中的字段建立合适的索引，遵循最左前缀原则；
  • 避免 select *、避免在索引字段上使用函数或进行运算，防止索引失效；
  • 通过 EXPLAIN 分析执行计划，关注 type、key、rows 等字段，排查全表扫描；
  • 对慢查询开启慢查询日志进行定位。

2.MyCat

如果让我们自己实现分库分表、读写分离，会很麻烦，而且中间会有很多错误，所以我们需要借助外部工具，MyCat 就是这样一个帮助实现数据库分库分表、读写分离的分布式数据库中间件。

应用程序不再直接连接物理数据库，而是连接 MyCat。MyCat 对应用层透明，根据配置将逻辑库/逻辑表的请求路由到后端真正的物理库表，并汇总返回结果。

可以到 MyCat 官网进行下载和学习：http://www.mycat.org.cn/

3.使用MyCat实现分库分表

MyCat 的核心配置文件是 schema.xml，其中定义逻辑库（schema）、逻辑表（table）、分片节点（dataNode）与物理数据源（dataHost）之间的映射关系。

  • 垂直分库分表：将不同业务的逻辑表映射到不同的 dataNode，每个 dataNode 对应一个独立的物理数据库。例如商品表路由到商品库、订单表路由到订单库。
  • 水平分库分表：在逻辑表的配置上通过 rule 指定分片规则，把同一张表的数据行分散到多个分片。常见的分片规则有：
    • 取模（如按 id % 分片数 路由）；
    • 范围约定（按 ID 区间路由）；
    • 一致性哈希 / 枚举等。

4.使用MyCat实现读写分离

读写分离的核心在于配置时标明主从关系：在 dataHost 中将负责写的数据库配置为 writeHost，将负责读的数据库配置为其下挂的 readHost。

应用发来的 SQL 会被 MyCat 自动判别：写请求（INSERT/UPDATE/DELETE）路由到 writeHost 主库，读请求（SELECT）负载均衡到 readHost 从库。主从库之间则依靠 MySQL 自带的主从复制保持数据同步。

由于本项目条件受限，没有多台服务器，就算在一台服务器上实现了分库分表、读写分离，也是共同消耗一台服务器的 CPU、磁盘 I/O，不能真正提高性能，所以本项目并未实际部署上述内容。