Mysql表设计与大表优化

目录

一、单表优化

二、读写分离

三、引入缓存

四、表分区

五、垂直拆分

六、水平拆分

七、分库分表加深总结

---

在常规应用开发中，Mysql的单表性能都是够用的，从量级来看，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下Mysql都是可以的，但是如果随着数据量继续上升，超过千万级以后，大表的优化就必须要考虑了，那么千万级的大表优化方案如何来做呢，可以考虑参考下这个顺序：

• 单表优化
• 读写分离
• 缓存
• 表分区
• 垂直拆分
• 水平拆分
• 兼容MySQL且可水平扩展的数据库
• NoSQL
• NewSQL

一、单表优化

事实上很多时候MySQL单表的性能依然有不少优化空间，优化做得好也是能正常支撑千万级以上的数据量的，就从表字段、索引和查询优化来看吧。

1.字段

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
• VARCHAR的长度只分配真正需要的空间
• 使用枚举或整数代替字符串类型（这个待讨论）
• 尽量使用TIMESTAMP而非DATETIME，
• 单表不要有太多字段，建议在20以内
• 避免使用NULL字段，很难查询优化且占用额外索引空间
• 用整型来存IP

2.索引

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 字符字段只建前缀索引
• 字符字段最好不要做主键
• 不用外键，由程序保证约束
• 尽量不用UNIQUE，由程序保证约束
• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

3.查询SQL

• 可通过开启慢查询日志来找出较慢的SQL
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
• 不用SELECT *
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 不用函数和触发器，在应用程序实现
• 避免%xxx式查询
• 少用JOIN
• 使用同类型进行比较，比如用'123'和'123'比，123和123比
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

4.引擎

一般来说，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表，但是这个地方无脑选择InnoDB就好了，很少见过不用事务、不需奔溃恢复的系统。

二、读写分离

读写分离就是只在主服务器上写，只在从服务器上读。对应到数据库集群一般都是一主一从(一个主库，一个从库)或者一主多从(一个主库，多个从库)，业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。一般读写分离的实现方式有两种：

• 代码封装，抽出一个中间层，让这个中间层来实现读写分离和数据库连接。讲白点就是搞个provider封装了save,select等通常数据库操作，内部save操作的dataSource是主库的，select操作的dataSource是从库的。
• 数据库中间件，就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的SQL协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

1、主从同步复制是怎么做的

首先先了解mysql主从同步的原理

• master提交完事务后，写入binlog
• slave连接到master，获取binlog
• master创建dump线程，推送binglog到slave
• slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
• slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
• slave记录自己的binglog

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

• 全同步复制
  主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

• 半同步复制
  和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

三、引入缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。目前主要用Redis来实现，对于维护数据库与缓存的一致性也有两种方式：

• 直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。
• 回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

四、表分区

区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化：

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 |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1row in set (0.00 sec)

分区的好处：

• 分区表的数据还可以分布在不同的物理设备上，可以让单表存储更多的数据，分区表的数据更容易维护；
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快
• 可以备份和恢复单个分区

分区表的不足：

• 一个表最多只能有1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 分区表无法使用外键约束
• NULL值会使分区过滤无效
• 所有分区必须使用相同的存储引擎

分区的方式：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

分区最适合场景：

数据的时间序列性比较强，则可以按时间来分区，不然说下面这个，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

五、垂直拆分

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。再讲拆分之前先引入的讲讲数据库的性能瓶颈

1、IO瓶颈

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

2、CPU瓶颈

第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。

第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

1、垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。拆到了单独的库以后，可以形成服务化，提供api方式的接口调用。

场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。

其实基于现在微服务拆分来说，都是已经做到了垂直分库了，比如随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

2、垂直分表

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。类似于列表页和详情页的结构拆分。
结果：

• 每个表的结构都不一样；
• 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
• 所有表的并集是全量数据；

垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会将两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

六、水平拆分

水平分库和水平分表方法是差不多的，只是会按照拆分规则放库还是放表的区别，但是需要注意两者的场景，有不同的适用场景。

1、水平分库

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

结果：

• 每个库的结构都一样；
• 每个库的数据都不一样，没有交集；
• 所有库的并集是全量数据；

场景：如果系统的绝对并发量上来了，水平分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库，这时候就该分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

2、水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：

• 每个表的结构都一样；
• 每个表的数据都不一样，没有交集；
• 所有表的并集是全量数据；

场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：一次SQL查询优化原理分析

分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

七、分库分表加深总结

1.分库分表工具

目前主要流行的就是Mycat和sharding-sphere，TDDL是早期的一个方案。

• TDDL：jar，Taobao Distribute Data Layer；淘宝团队开发的，属于client层方案。不支持join、多表查询等语法，就是基本的crud语法是ok，但是支持读写分离。目前使用的也不多，因为还依赖淘宝的diamond配置管理系统。
• sharding-sphere：当当开源的，属于client层方案。确实之前用的还比较多一些，因为SQL语法支持也比较多，没有太多限制，而且目前推出到了2.0版本，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。client层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合sharding-jdbc的依赖；
• Mycat：中间件，属于proxy层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，proxy层方案的缺点在于需要部署，自己及运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

分库分表的具体拆分做法，和前面Mysql分区的range/hash一致，可以参考。

2.现在有一个未分库分表的系统，未来要分库分表，如何设计才可以让系统从未分库分表动态切换到分库分表上？

主要两种方案，一种是停机迁移，一种是双写迁移方案。

首先来看停机迁移方案，先等到深夜将系统挺掉，没有流量写入了，此时老的单库单表数据库静止了。然后把写好一个导数的一次性工具，此时直接跑起来，然后将单库单表的数据批量读取并写到分库分表里面去。导数完了之后，就可以了，此时再修改系统的数据库连接配置，包括可能代码和SQL也许有修改，那你就用最新的代码，然后直接启动连到新的分库分表上去。

再来看双写迁移方案，这个不需要停应用，流程如下：
第一步：修改应用配置和代码，加上双写，部署；
第二步：将老库中的老数据复制到新库中，根据gmt_modified等字段判断这条数据最后修改的时间、或者只有老库里才有的数据才写入到新库中去；
第三步：以老库为准校对新库中的老数据，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止；
第四步：修改应用配置和代码，去掉双写，部署；

3.扩容

一种方案还是停机扩容，现有库表的数据抽出来慢慢倒入到新的库和表里去，但是耗时和方案不可控。

另外一种就是提前预估好，直接上32个库，每个库32个表，1024张表，这个方案，第一，基本上国内的互联网肯定都是够用了，第二，无论是并发支撑还是数据量支撑都没问题：

每个库正常承载的写入并发量是1000，那么32个库就可以承载32 * 1000 = 32000的写并发，如果每个库承载1500的写并发，32 * 1500 = 48000的写并发，接近5万/s的写入并发，前面再加一个MQ，削峰，每秒写入MQ 8万条数据，每秒消费5万条数据。1024张表，假设每个表放500万数据，在MySQL里可以放50亿条数据。每秒的5万写并发，总共50亿条数据，对于国内大部分的互联网公司来说，其实一般来说都够了！

刚开始的时候，这个库可能就是逻辑库，建在一个数据库上的，就是一个mysql服务器可能建了n个库，比如16个库。后面如果要拆分，就是不断在库和mysql服务器之间做迁移就可以了。然后系统配合改一下配置即可。比如说最多可以扩展到32个数据库服务器，每个数据库服务器是一个库。如果还是不够？最多可以扩展到1024个数据库服务器，每个数据库服务器上面一个库一个表。因为最多是1024个表。

这么搞，是不用自己写代码做数据迁移的，都交给dba来搞好了，但是dba确实是需要做一些库表迁移的工作，但是总比自己写代码，抽数据导数据来的效率高得多了。

4.那分表后的ID怎么保证唯一性的呢？

分布式环境下的ID生成有两个基本的要求：
1， 全局唯一，在分布式集群下，不同的节点并发生成的分布式id要唯一；
2， 顺序性，分布式id是有序生成

这里可以了解一下雪花算法：

 

• 第一个部分是 1 个 bit：0，这个是无意义的。
• 第二个部分是 41 个 bit：表示的是时间戳，Java里面用System.currentTimeMillis()即可。
• 第三个部分是 5 个 bit：表示的是机房 id，10001。
• 第四个部分是 5 个 bit：表示的是机器 id，11001（工作位意味着最多部署1024 台机器）。
• 第五个部分是 12 个 bit：表示的序号，就是某个机房某台机器上这一毫秒内同时生成的 id 的序号，0000 00000000。（2^12次方意味着同一个机器同一个毫秒内可以生成 4096 个不同的 id。）

雪花算法的工作流程是，这个 SnowFlake 算法系统首先肯定是知道自己所在的机房和机器的，比如机房 id = 17，机器 id = 12。接着 SnowFlake 算法系统接收到这个请求之后，首先就会用二进制位运算的方式生成一个 64 bit 的 long 型 id，64 个 bit 中的第一个 bit 是无意义的。接着 41 个 bit，就可以用当前时间戳（单位到毫秒），然后接着 5 个 bit 设置上这个机房 id，还有 5 个 bit 设置上机器 id。最后再判断一下，当前这台机房的这台机器上这一毫秒内，这是第几个请求，给这次生成 id 的请求累加一个序号，作为最后的 12 个 bit。最终一个 64 个 bit 的 id 就出来了

5.分表后非sharding_key的查询怎么处理呢？

• 可以做一个mapping表，比如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过user_id去查询。
• 大宽表，一般而言，商户端对数据实时性要求并不是很高，比如查询订单列表，可以把订单表同步到离线（实时）数仓譬如Hive，再基于数仓去做成一张宽表，再基于其他如es提供查询服务。
• 数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的

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

目前也有一些开源数据库兼容MySQL协议，如：TiDB，在一个 TiDB 的数据库上，所有业务场景不需要做分库分表，所有的分布式工作都由数据库层完成。TiDB 兼容 MySQL 协议，所以可以直接替换 MySQL，而且基本做到了开箱即用。

NoSQL

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

• 日志类、监控类、统计类数据
• 非结构化或弱结构化数据
• 对事务要求不强，且无太多关联操作的数据