Mysql的几个灵魂拷问（五）

这一篇继续讲SQL的优化问题，在常规应用开发中，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协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

一文带你了解 Mysql 读写分离

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

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

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确实是需要做一些库表迁移的工作，但是总比自己写代码，抽数据导数据来的效率高得多了。

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

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

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

snowflake

第一个部分是 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 就出来了

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

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

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

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

NoSQL

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

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

参考引用

1、MySQL 大表优化方案
2、MySQL 优化提高笔记整理
3、《我想进大厂》之mysql夺命连环13问
4、MySQL:互联网公司常用分库分表方案汇总