028-86922220

详解MySQL大表优化方案

 当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：

单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在***以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑***以上的数据量：

字段

索引

查询SQL

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

系统调优参数

可以使用下面几个工具来做基准测试：

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：

升级硬件

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

读写分离

也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

缓存

缓存可以发生在这些层次：

可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

表分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

 

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图5条记录落在两个分区上：

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);

+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

| 1 | SIMPLE | user_partition | p1,p4 | range | PRIMARY | PRIMARY | 8 | NULL | 5 | Using where; Using index |

+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

分区的好处是：

分区的限制和缺点：

分区的类型：

分区适合的场景有：

 
 
 
 
    
  
  
  
  
  1. CREATE TABLE members ( 
    2. 
  
  
  
  
  2.     firstname VARCHAR(25) NOT NULL, 
    3. 
  
  
  
  
  3.     lastname VARCHAR(25) NOT NULL, 
    4. 
  
  
  
  
  4.     username VARCHAR(16) NOT NULL, 
    5. 
  
  
  
  
  5.     email VARCHAR(35), 
    6. 
  
  
  
  
  6.     joined DATE NOT NULL 
    7. 
  
  
  
  
  7. ) 
    8. 
  
  
  
  
  8. PARTITION BY RANGE( YEAR(joined) ) ( 
    9. 
  
  
  
  
  9.     PARTITION p0 VALUES LESS THAN (1960), 
    10. 
  
  
  
  
  10.     PARTITION p1 VALUES LESS THAN (1970), 
    11. 
  
  
  
  
  11.     PARTITION p2 VALUES LESS THAN (1980), 
    12. 
  
  
  
  
  12.     PARTITION p3 VALUES LESS THAN (1990), 
    13. 
  
  
  
  
  13.     PARTITION p4 VALUES LESS THAN MAXVALUE 
    14. 
  
  
  
  
  14. ); 
    15.

 

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

另外MySQL有一种早期的简单的分区实现 - 合并表(merge table)，限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

比如原始的用户表是：

 

垂直拆分后是：

 

垂直拆分的优点是：

缺点是：

水平拆分

概述

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决

前面垂直拆分的用户表如果进行水平拆分，结果是：

 

实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

水平拆分的优点是:

缺点是：

分片原则

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

解决方案

由于水平拆分牵涉的逻辑比较复杂，当前也有了不少比较成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。

客户端架构

通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现

这是一个客户端架构的例子：

 

可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC层来实现

客户端架构的优点是：

缺点是：

代理架构

通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件

这是一个代理架构的例子：

 

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

代理架构的优点是：

缺点是：

各方案比较 

  出品方 架构模型 支持数据库 分库 分表 读写分离 外部依赖 是否开源 实现语言 支持语言 ***更新 Github星数
MySQL FabricMySQL官方代理架构MySQL有有有无是python***制4个月前35
Cobar阿里巴巴代理架构MySQL有无无无是Java***制两年前1287
Cobar Client阿里巴巴客户端架构MySQL有无无无是JavaJava三年前344
TDDL淘宝客户端架构***制有有有Diamond只开源部分JavaJava未知519
Atlas奇虎360代理架构MySQL有有有无是C***制10个月前1941
Heisenberg百度熊照代理架构MySQL有有有无是Java***制2个月前197
TribeDB个人代理架构MySQL有有有无是NodeJS***制3个月前126
ShardingJDBC当当客户端架构MySQL有有有无是JavaJava当天1144
Shark个人客户端架构MySQL有有无无是JavaJava两天前84
KingShard个人代理架构MySQL有有有无是Golang***制两天前1836
OneProxy平民软件代理架构MySQL有有有无否未知***制未知未知
MyCat社区代理架构MySQL有有有无是Java***制两天前1270
VitessYoutube代理架构MySQL有有有无是Golang***制当天3636
Mixer个人代理架构MySQL有有无无是Golang***制9个月前472
JetPantsTumblr客户端架构MySQL有有无无是RubyRuby10个月前957
HibernateShardHibernate客户端架构***制有有无无是JavaJava4年前57
MybatisShardMakerSoft客户端架构***制有有无无是JavaJava11个月前119
GizzardTwitter代理架构***制有有无无是Java***制3年前2087
 

如此多的方案，如何进行选择?可以按以下思路来考虑：

  1. 确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择客户端架构，复杂场景或大规模系统倾向选择代理架构
  2. 具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑
  3. 不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持
  4. ***按大公司->社区->小公司->个人这样的出品方顺序来选择
  5. 选择口碑较好的，比如github星数、使用者数量质量和使用者反馈
  6. 开源的优先，往往项目有特殊需求可能需要改动源代码

按照上述思路，推荐以下选择：

兼容MySQL且可水平扩展的数据库

目前也有一些开源数据库兼容MySQL协议，如：

但其工业品质和MySQL尚有差距，且需要较大的运维投入，如果想将原始的MySQL迁移到可水平扩展的新数据库中，可以考虑一些云数据库：

NoSQL

在MySQL上做Sharding是一种戴着镣铐的跳舞，事实上很多大表本身对MySQL这种RDBMS的需求并不大，并不要求ACID，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题，例如：


本文题目：详解MySQL大表优化方案
地址分享：http://www.turtgq.com/article/coeeeje.html

其他资讯