MySQL（五）Mysql架构、数据库优化、主从复制

解梦者

已于 2024-01-26 22:59:18 修改

阅读量1.5k

点赞数

分类专栏：【MySQL】文章标签：数据库分布式

于 2021-03-30 14:24:13 首次发布

本文链接：https://blog.csdn.net/m0_37741420/article/details/115303692

版权

【MySQL】专栏收录该内容

7 篇文章 14 订阅

订阅专栏

文章目录

本系列文章：
MySQL（一）SQL语法、数据类型、常用函数、事务
 MySQL（二）MySQL SQL练习题
 MySQL（三）视图、存储过程、索引
 MySQL（四）存储引擎、锁
MySQL（五）Mysql架构、数据库优化、主从复制
MySQL（六）SQL语句优化
 MySQL（七）MySQL和Oracle、PostgreSQL的区别

一、MySQL架构

MySQL架构：

大体来说，MySQL可以分为Server层和存储引擎层两部分。

1、Server层
Server层包括连接器、查询缓存、分析器、优化器、执行器等，提供了Mysql Server 数据库所有逻辑功能，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等。
2、存储引擎层
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。
存储引擎是MySQL中具体与文件打交道的子系统，MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎，其根据文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎）。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。也就是说，执行create table建表的时候，如果不指定引擎类型，默认使用的就是InnoDB。

1.1 查询语句的执行过程

1.1.1 连接器

Mysql 服务器默认监听端口是3306。
连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令常规写法：

	mysql -h$ip -P$port -u$user -p

连接命令中的Mysql是客户端工具，用来跟服务端建立连接。在完成TCP握手后，连接器就要开始认证身份，这个时候用的就是输入的用户名和密码。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

1、长连接
MySQL是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。使用异步方式的话，服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量CPU资源）；另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。
MySQL既支持短连接，也支持长连接。短连接就是操作完毕以后，马上close掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。
客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以建议尽量使用长连接。

全部使用长连接后，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。两种解决方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2、半双工通信方式
MySQL支持哪些通信协议呢？第一种是Unix Socket，默认是使用该协议，如果指定-h参数，就会用第二种方式，TCP/IP协议：

	mysql -h192.168.8.211 -uroot -p123456

编程语言的连接模块都是用TCP协议连接到MySQL服务器的。

MySQL使用了半双工的通信方式，所以客户端发送SQL语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的SQL语句有多大，都是一次性发送。另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗。
所以，一定要在程序里面避免不带limit的这种操作，比如一次把所有满足条件的数据全部查出来，一定要先count 一下。如果数据量的话，可以分批查询。

1.1.2 查询缓存

MySQL的缓存默认是关闭的。
连接建立完成后，就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。
但是，查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此，对于更新压力大的数据库来说，查询缓存的命中率会非常低。因此，MySQL 8.0版本直接将查询缓存的整块功能删掉了。

1.1.3 分析器

如果没有命中查询缓存，就要开始真正执行语句了。分析器先会做“词法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。语法分析之后是语义解析，即检查表名、列名等是否存在，是否正确。
这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。词法分析就是把一个完整的SQL语句打碎成一个个的单词。接下来就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合，然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树：

在解析的时候报错，解析SQL的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

1.1.4 优化器

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。示例：

	select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

1.1.5 执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。示例：

	select * from T where ID=10

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

1.1.6 存储引擎

数据库的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。在MySQL里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。
MyISAM和InnoDB是我们用得最多的两个存储引擎，在MySQL 5.5版本之前，默认的存储引擎是MyISAM，5.5版本之后默认的存储引擎改成了InnoDB。

1、MyISAM（ 3 个文件）
应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。
特点：

1.支持表级别的锁（插入和更新会锁表）。不支持事务。
2.拥有较高的插入（insert）和查询（select）速度。
3.存储了表的行数（count 速度更快）。

2、InnoDB（ 2 个文件）
InnoDB是一个事务安全（与ACID兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。
特点：

支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）。
特殊的索引存放方式，可以减少IO，提升查询效率。

如何选择存储引擎？

如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
如果需要一个用于查询的临时表，可以选择Memory。

1.1.7 执行引擎，返回结果

1.1.8 示例

	select * from user where id > 1 and name = '张三';

首先检查权限，没有权限则返回错误；
MySQL以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
两种执行方案，先查 id > 1 还是 name = ‘张三’ ，优化器根据自己的优化算法选择执行效率最好的方案；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

1.2 更新语句的执行过程

更新语句执行流程：分析器、权限校验、执行器、引擎、 redo log （ prepare状态）、binlog 、 redo log （ commit状态）。
举个例子：

	update user set name = '张三' where id = 1;

1、先查询到id为1的记录，有缓存会使用缓存。
2、拿到查询结果，将name更新为张三，然后调用引擎接口，写入更新数据，innodb引擎将数据保存在内存中，同时记录redo log ，此时redo log进入prepare状态。
3、执行器收到通知后记录binlog ，然后调用引擎接口，提交redo log为commit状态。
4、更新完成。

为什么记录完redo log ，不直接提交，而是先进入prepare状态？假设先写redo log直接提交，然后写binlog ，写完redo log后，机器挂了， binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

1.3 MySQL数据存储文件

每张InnoDB的表有两个文件（.frm 和.ibd），MyISAM的表有三个文件（.frm、.MYD、.MYI）。

.frm是MySQL里面表结构定义的文件，选用任何一个存储引擎都会生成。

1.3.1 MyISAM

在MyISAM里面，另外有两个文件：一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录。一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引。
MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

1.3.2 InnoDB

在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

二、数据库优化

为什么要数据库优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上。
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢。
数据是存放在磁盘上的，读写速度无法和内存相比。

因此数据库的优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

2.1 从架构层面优化性能

说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多个环节组成的，每个环节都会消耗时间，要减少查询所消耗的时间，就要从每一个环节入手。

2.1.1 连接(配置优化)

第一个环节是客户端连接到服务端，在这个环节，可能服务端连接数不够导致应用程序获取不到连接。

1、从服务端来说，可以增加服务端的可用连接数
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，可以调两个参数：max_connections和wait_timeout。
1）修改配置参数增加可用连接数，修改max_connections的大小：

	-- 最大连接数
	show variables like 'max_connections';

2）或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小：

	--及时释放不活动的连接， 注意不要释放连接池还在使用的连接
	show global variables like 'wait_timeout';

2、从客户端来说，可以减少从服务端获取的连接数
即引入连接池，实现连接的重用。
在ORM层面，MyBatis 自带了一个连接池，或者使用专用的连接池工具（阿里的Druid、Spring Boot 2.x版本默认的连接池Hikari、老牌的DBCP和C3P0）。
连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。如：Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。
在Hikari的github文档中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式：它的建议是机器核数乘以2加1。也就是说，4核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。
3、其他MySQL参数
thread_pool_size：同时运行sql语句的Mysql的线程数。如果主引擎为InnoDB，thread_pool_size最佳设置可能在16和36之间，最常见的优化值倾向于24到36。
thread_pool_stall_limit：超时时间，线程在超过 thread_pool_size 时，会等待 thread_pool_stall_limit ms 后创建新线程，防止线程池瞬间扩展而还来不必要的线程开销。用处理被阻塞和长时间运行的语句，确保服务器不完全被阻塞。设置过长会导致线程被阻塞，引起性能问题。

2.1.2 缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。可以用第三方的缓存服务来解决这个问题，例如Redis。

2.1.3 主从复制/分库分表*

主从复制
如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。这个时候需要用到复制技术，被复制的节点称为master，复制的节点称为slave。slave本身也可以作为其他节点的数据来源，这个叫做级联复制。
做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。
读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要注意主从数据一致性的问题。
如果单个master节点或者单张表存储的数据过大的时候，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表。
分库分表
在单表的情况下，当业务正常时，我们使用单表即可。当业务出现了性能瓶颈时，我们首先考虑用分区的方式来优化，如果分区优化之后仍然存在后遗症，此时我们再来考虑分表分库。
如果在单表单库的情况下，当数据库表的数据量逐渐累积到一定的数量时（5000W行或100G以上），操作数据库的性能会出现明显下降，即使我们使用索引优化或读写库分离，性能依然存在瓶颈。此时，如果每日数据增长量非常大，我们就应该考虑分表，避免单表数据量过大，造成数据库操作性能下降。
分表分库分为垂直切分和水平切分两种。

通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的，但是如果 master 节点挂了，怎么办？此时就要用集群方案了。

2.2 从数据库设计层面优化性能*

数据库结构优化需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1、将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
2、增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
3、增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
冗余字段：在设计数据库时，某一字段属于一个表，但它又同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段。
冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。
冗余字段会导致一些问题，比如，冗余字段的值在一个表中被修改了，就要同步关联的表，否则会导致数据不一致。这要根据实际情况，平衡数据库性能，进行冗余字段的设计。
4、字段设计尽量合理

所有字段均定义为NOT NULL，除非真的需要存储NULL。
尽可能减少定义字段宽度。
如’省份’、'性别’等固定值，最好使用ENUM。ENUM类型是非常快和紧凑的，实际上，其保存的是TINYINT，但其外表上显示为字符串。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
把IP地址存成UNSIGNEDINT。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IPbetweenip1andip2。们必需要使用UNSIGNEDINT，因为IP地址会使用整个32位的无符号整形。
越小的列会越快。如果一个表只会有几列（比如字典表、配置表），那么，我们就没有理由使用INT来做主键，使用MEDIUMINT,SMALLINT或是更小的TINYINT会更经济一些。

5、提前做好数据量的预估，进行分表设计
不要等需要拆分时再拆，一般把表的数据量控制在千万级别。当单表数据量达到一定程度时（MySQL5.x时代的性能拐点则为1000W - 2000W行级别，具体需根据实际情况测试），为了提升性能，最为常用的方法就是分表。分表的策略可以是垂直拆分（比如：不同订单状态的订单拆分到不同的表），也可以是水平拆分（比如：按月将订单拆分到不同表）。如果在业务层分表，会将逻辑变得复杂，而且分散。可以引入分表的中间件屏蔽分表后的细节，让业务层像查询单表一样查询分表后的数据。比如Mycat。（访问量不大，但是表数据很多的表，我们可以采取分区表，实现起来也比较简单）
6、合理的设置主键和索引
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
提一下JOIN子句，被用来JOIN的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
7、使用合适的主键
主键分自增主键和业务主键：

自增主键：写入、查询效率和磁盘利用率都高，但每次查询都需要两级索引，因为线上业务不会有直接使用主键列的查询。
业务主键：写入、查询效率和磁盘利用率都低，但可以使用一级索引，依赖覆盖索引的特性，某些情况下在非主键索引上也可以实现1次索引完成查询。

8、有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键

2.3 从查询sql层面优化性能*

通过explain和show profiles进行执行计划分析，找出问题，进行针对性的优化。其中创建高效索引是最有效的一个手段。

1、多列索引和索引顺序
出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。
在选择性高的字段上建立索引，可以让MySQL在查询时过滤掉更多的行。对于多列索引，哪个索引字段在前面，取决于索引的选择性的高低。选择性高的索引排在前面，有利于提高查询效率。例如联合索引（user_group_id，trade_amount）用户的群组肯定比订单的交易金额的选择性高。
2、覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

优化关联查询：以小表驱动大表。
子查询尽量换成join。这是因为join，MySQL不需要在内存中创建临时表来完成这个逻辑上的需求。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

3、优化LIMIT分页
一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列，然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。
考虑下面的查询，修改前：

	SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

修改后：

	SELECT film.film_id,film.description
		FROM film INNER JOIN (
			SELECT film_id FROM film ORDER BY title LIMIT 50,5
		) AS tmp USING(film_id);

4、优化UNION
除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。
UNION ALL要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同：

对重复结果的处理：UNION在进行表连接后会筛选掉重复的记录，Union All不会去除重复记录。
对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

5、使用连接(JOIN)来代替子查询，当然尽量避免JOIN查询
6、避免导致索引失效

负向条件查询不能使用索引（not in/not exists都不是好习惯）
前导模糊查询不能使用索引（应使用like’‘XX%’'）
数据区分度不大的字段不宜使用索引
在属性上进行计算不能命中索引
复合索引最左前缀不满足

2.4 分库分表

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。
无论分库还是分表，数据库中间件都是可以支持的。就是基本上那些中间件可以做到你分库分表之后，中间件可以根据你指定的某个字段值，比如说 userid，自动路由到对应的库上去，然后再自动路由到对应的表里去。

数据切分根据其切分类型，可以分为两种方式：垂直切分和水平切分。

2.4.1 垂直切分*

垂直切分常见有垂直分库和垂直分表两种。
一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。
在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

垂直切分的优点：

解决业务系统层面的耦合，业务清晰。
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等。
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈。

垂直切分的缺点：

部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度。
分布式事务处理复杂。
依然存在单表数据量过大的问题（需要水平切分）。

2.4.2 水平切分*

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。
水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。
水平切分分为库内分表和分库分表。

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
水平切分的优点：

不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。
应用端改造较小，不需要拆分业务模块。

水平切分的缺点：

跨分片的事务一致性难以保证。
跨库的join关联查询性能较差。
数据多次扩展难度和维护量极大。

以下为典型的数据分片规则。

1、根据数值范围
按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1 ~ 9999的记录分到第一个库，10000 ~ 20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

优点：

单表大小可控。
天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移。
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询。

2、根据数值取模
一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

优点：

数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。

缺点：

后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）。
容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

2.4.3 分库分表带来的问题*

1、事务一致性问题
当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，没有简单的方案，一般可使用"XA协议"和"两阶段提交"处理。
分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。
对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。
2、跨节点关联查询 join 问题
切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。解决这个问题的一些方法：
1）全局表。全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。
2）字段冗余。一种典型的反范式设计，利用空间换时间，为了性能而避免join查询。
3）数据组装。在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。
4）ER分片。关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题。在1:1或1:n的情况下，通常按照主表的ID主键切分。如下图所示：

这样一来，Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了。
3、跨节点分页、排序、函数问题
跨节点多库进行查询时，会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。

如果取得页数很大，情况则变得复杂很多，因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体的排序，这样的操作时很耗费CPU和内存资源的，所以页数越大，系统的性能也会越差。
在使用Max、Min、Sum、Count之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。
4、全局主键避重问题
在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

UUID。
结合数据库维护主键ID表。
雪花算法。

5、数据迁移和扩容问题
当业务高速发展，面临性能和存储的瓶颈时，才会考虑分片设计，此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据，然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和QPS，以及业务发展的速度，进行容量规划，推算出大概需要多少分片（一般建议单个分片上的单表数据量不超过1000W）。
如果采用数值范围分片，只需要添加节点就可以进行扩容了，不需要对分片数据迁移。如果采用的是数值取模分片，则考虑后期的扩容问题就相对比较麻烦。

2.4.4 什么时候考虑切分*

1、能不切分尽量不要切分
不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。
2、数据量过大，正常运维影响业务访问
对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。
大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力。
3、随着业务发展，需要对某些字段垂直拆分
4、数据量快速增长
单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。
5、安全性和可用性
在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。

2.4.5 分库分表中间件

目前分库分表已经有一些较为成熟的开源解决方案：Cobar、TDDL、Atlas、Sharding-jdbc、Mycat。

Cobar
阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 Cobar 集群，Cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用，但是最近几年都没更新了，基本没啥人用，而且不支持读写分离、存储过程、跨库 join 和分页等操作。
TDDL
淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。
Atlas
360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。
Sharding-jdbc
当当开源的，属于 client 层方案，是 ShardingSphere 的 client 层方案， ShardingSphere还提供 proxy 层的方案 Sharding-Proxy。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且截至 2019.4，已经推出到了 4.0.0-RC1 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，算是一个现在也可以选择的方案。
Mycat
基于 Cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。
总结
现在其实建议考量的，就是 Sharding-jdbc 和 Mycat，这两个都可以去考虑使用。
Sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 Sharding-jdbc 的依赖。
Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的。
建议中小型公司选用 Sharding-jdbc，client层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；但是中大型公司最好还是选用 Mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 Mycat，然后大量项目直接透明使用即可。

2.4.6 如何设计才可以让系统从未分库分表动态切换到分库分表上*

停机迁移方案
大家伙儿凌晨 12 点开始运维，网站或者 app 挂个公告，说 0 点到早上 6 点进行运维，无法访问。接着到 0 点停机，系统停掉，没有流量写入了，此时老的单库单表数据库静止了。然后你之前得写好一个导数的一次性工具，此时直接跑起来，然后将单库单表的数读出来，写到分库分表里面去。导数完了之后，就 ok 了，修改系统的数据库连接配置啥的，包括可能代码和 SQL 也许有修改，那你就用最新的代码，然后直接启动连到新的分库分表上去。
双写迁移方案
不用停机，简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改，这就是所谓的双写，同时写俩库，老库和新库。
然后系统部署之后，新库数据差太远，用之前说的导数工具，跑起来读老库数据写新库，写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。
导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。
接着当数据完全一致了，就 ok 了，基于仅仅使用分库分表的最新代码，重新部署一次，不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本玩儿数据迁移之类的，都是这么干的。

2.4.7 分库分表之后，id 主键如何处理*

数据库自增 id
适合的场景：分库分表就俩原因，要不就是单库并发太高，要不就是单库数据量太大。如果是并发不高、数据量太大导致的分库分表扩容，可以用这个方案，因为可能每秒最高并发最多就几百，那么就走单独的一个库和表生成自增主键即可。
设置数据库 sequence 或者表自增字段步长
可以通过设置数据库 sequence 或者表的自增字段步长来进行水平伸缩。比如说，现在有 8 个服务节点，每个服务节点使用一个 sequence 功能来产生 ID，每个sequence 的起始 ID 不同，并且依次递增，步长都是 8。

适合的场景：在用户防止产生的 ID 重复时，这种方案实现起来比较简单，也能达到性能目标。但是服务节点固定，步长也固定，将来如果还要增加服务节点。
UUID
好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，作为主键性能太差了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），还有，由于在写的时候不能产生有顺序的append 操作，而需要进行 insert 操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。
适合的场景：如果你是要随机生成个什么文件名、编号之类的，你可以用 UUID，但是作为主键是不能用 UUID 的。
获取系统当前时间
这个就是获取当前时间即可，但是问题是，并发很高的时候，比如一秒并发几千，会有重复的情况，这个是肯定不合适的。基本就不用考虑了。
适合的场景：一般如果用这个方案，是将当前时间跟很多其他的业务字段拼接起来，作为一个id，如果业务上你觉得可以接受，那么也是可以的。你可以将别的业务字段值跟当前时间拼接起来，组成一个全局唯一的编号。
snowflake 算法
利用这个 snowflake 算法，你可以开发自己公司的服务，甚至对于机房 id 和机器 id，反正给你预留了 5 bit + 5 bit，你换成别的有业务含义的东西也可以的。
这个 snowflake 算法相对来说还是比较靠谱的，所以你要真是搞分布式 id 生成，如果是高并发啥的，那么用这个应该性能比较好，一般每秒几万并发的场景，也足够你用了。

2.5 数据库优化的相关问题

2.5.1 MySQL数据库cpu飙升到500%的话怎么处理

当cpu飙升到500%时，先用操作系统命令 top 命令（实时显示系统中各个进程的资源占用状况）观察是不是mysqld占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是mysqld造成的， show processlist（显示用户正在运行的线程），看看里面跑的session情况，是不是有消耗资源的sql在运行。找出消耗高的sql，看看执行计划是否准确， index是否缺失，或者实在是数据量太大造成。
show processlist示例：

这些字段的含义：

id：线程ID，可以用： kill id杀死一个线程。
db：数据库。
user：用户。
host：连库的主机IP。
command：当前执行的命令，比如最常见的：Sleep，Query，Connect等。
time：消耗时间，单位秒。
state：执行状态。sleep表示线程正在等待客户端发送新的请求；query表示线程正在查询或者正在将结果发送到客户端；Sorting result表示线程正在对结果集进行排序；Locked表示线程正在等待锁。
info：执行的SQL语句。

一般来说，肯定要kill掉这些线程(同时观察cpu使用率是否下降)，等进行相应的调整(比如说加索引、改sql、改内存参数)之后，再重新跑这些SQL。
也有可能是每个 sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

2.5.2 表中有大字段X（例如：text 类型），且字段X不会经常更新，以读为主，将该字段拆成子表好处是什么

如果字段里面有大字段（text,blob)类型的，而且这些字段的访问并不多，这时候放在一起就变成缺点了。 MYSQL数据库的记录存储是按行存储的，数据块大小又是固定的（16K），每条记录越小，相同的块存储的记录就越多。此时应该把大字段拆走，这样应付大部分小字段的查询时，就能提高效率。
相应地，当需要查询大字段时，此时的关联查询是不可避免的。并且，拆分开后，对字段的UPDAE就要UPDATE多个表了。

2.5.3 一些数据库设计的注意事项

1、表名一般以【模块名称_具体表名】来实现
同一个模块的前缀是一样的。
2、表名称不应该取得太长
一般不超过三个英文单词，不推荐使用中文拼音，总的长度不要超过30个字符。
3、不使用tab或tb作为表前缀
4、一些作为多对多连接的表，可以使用两个表的前缀作为表名
如：用户登录表User_Login，用户分组表User_GroupInfo，这两个表建立多对多关系的表名为：User_Group_Relation（关系统一用Relation）。注意一点，主键在做其他表的外键时，或者在被其他表引用时，字段说明和字段名尽量保持一致，比如发帖表BBS_Topic里的用户字段写成UI_ID，这样跟用户信息表User_Info的主键UI_ID保持一致，容易维护。
5、当系统中有一些少量的、重复出现的值时，使用字典表来节约存储空间和优化查询
如地区、系统中用户类型的代号等。这类值不会在程序的运行期变化，但是需要存储在数据库中。一般数据库中，都有一个数据字典表，用来保存系统所用到的基础数据，大型的字段表如省份城市区域的字典表，统一以Dictionary_作为前缀。
6、默认的一些特殊字段
很多表中，比如一些业务处理表中，除了添加生成的自动编号ID（一般作为主键用），还有：

该记录创建的时间CreateDate(创建时间),该记录的创建人CreatBy，最后修改人LastEditBy，最后修改时间LastEditDate。（这些可以直接使用中文字符，而不使用编码，提高查询的效率）
同时有的时候需要注意，删除的时候并不真的删除该记录，而是添加一个标识位，比如XX_DeleteStaus删除状态，用来逻辑删除。1是有效的，0则是无效的。

7、每个表都应该有一个主键，这个主键最好是数字，而且是递增的
有很多表的主键用32位字符编码，这样做的目的更多的是从安全考虑的。因为字符多时索引时效率低，而使用自增列也不是很少，比如添加主表和从表操作时，主表的主键是从表的外键，这个时候还有取返回值，然后再添加，不可以同时添加。
8、基本表及其字段之间的关系,应尽量满足第三范式
但是满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
9、字段允许适当冗余
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

1）不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是varchar超长字段，更不能是text字段。

10、若两个实体之间存在多对多的关系，则应消除这种关系
消除的办法是，在两者之间增加第三个实体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。

2.5.4 一个耗时长的sql优化思路

慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?优化也是针对这三个方向：

1、首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列。如果是这种情况，就要对语句进行分析以及重写。
2、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
3、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

2.5.5 大表如何优化

1、限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句。
2、读/写分离
经典的数据库拆分方案，主库负责写，从库负责读。
3、垂直分区
根据数据库里面数据表的相关性进行拆分。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。
4、水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。
水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。
水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。
数据库分片的两种常见方案：

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

千万级的大表优化顺序
第一优化你的sql和索引。
第二加缓存，如：redis。
第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高。也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护。
第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区。
第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统。
第六才是水平切分，针对数据量大的表，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。
Mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高。

三、主从复制

在了解主从复制之前，可以先了解一下Mysql的日志。

3.1 Redo日志、Undo日志和Binlog日志

Redo日志和Undo日志是存储引擎层面的日志。

3.1.1 Redo日志（操作记录）*

Redo日志，是Innodb存储引擎的日志文件。Redolog用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB 存储引擎会使用Redolog恢复到发生故障前的时刻，以此来保证数据的完整性。
当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中。
Redolog是固定大小的，是循环写的过程。
有了redolog之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失。
Redo保证了持久性（隔离性是通过锁来实现）。

3.1.2 Undo日志（数据备份）*

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制。
在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
除了记录Redolog外，当进行数据修改时还会记录undo log， undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

注意：undo log是逻辑日志，可以理解为：

当delete一条记录时，undo log中会记录一条对应的insert记录；
当insert一条记录时，undo log中会记录一条对应的delete记录；
当update一条记录时，它记录一条对应相反的update记录。

3.1.3 Binlog日志*

Binlog是Server层面的日志，主要做Mysql功能层面的事情。
Binlog中会记录所有的逻辑，并且采用追加写的方式。一般在企业中数据库会有备份系统（用于应付数据丢失等情况），可以定期执行备份，备份的周期可以自己设置。
Binlog主要用于恢复数据库和同步数据库。
恢复数据的过程：

找到最近一次的全量备份数据。
从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻。

3.1.4 binlog和redolog的区别*

binlog会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redolog只记录innoDB自身的事务日志。
binlog只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redolog不断写入磁盘。
binlog是逻辑日志，记录的是SQL语句的原始逻辑；redolog是物理日志，记录的是在某个数据页上做了什么修改。
redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前的日志信息。

3.1.5 数据更新的流程

执行流程：

执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回；
执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据；
引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作；
执行器生成这个操作的Binlog；
执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成。

Redo log为什么两阶段提交？

1、如果先写redo log后写binlog
假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
2、如果先写binlog后写redo log
如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是 0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

3.2 MySQL的复制原理以及流程*

为什么需要主从复制？

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
做数据的热备。
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制：将主数据库中的DDL和DML操作通过二进制日志（Binlog）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

1、主从复制的作用

主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。

2、MySQL主从复制解决的问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份。
负载均衡：降低单个服务器的压力。
高可用和故障切换：帮助应用程序避免单点失败。
升级测试：可以用更高版本的MySQL作为从库。

3、MySQL主从复制工作原理

在主库上把数据更高记录到二进制日志。
从库将主库的日志复制到自己的中继日志。
从库读取中继日志的事件，将其重放到从库数据中。

在主从复制时，涉及到3个线程：
Binlog线程：主服务器上的线程，该县城记录下所有改变了数据库数据的语句，放进主服务器上的Binlog中；
IO线程：从服务器上的线程，在使用start slave命令之后，负责从主服务器上拉取Binlog内容，放进从服务器上的relay log中；
Sql执行线程：从服务器上的线程，执行relay log中的语句。
上面三个线程涉及了2个线程：

Binary log：主数据库的二进制日志；
Relay log：从服务器的中继日志。

主从复制流程：

1、master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
2、slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件；
3、同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志（relay log）中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

3.3 MySQL支持的复制类型

这个问题也可以换个说法：MySQL的binlog的格式。Binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。Binlog是Server层的日志。
MySQL的Binlog有三种格式：statment、row和mixed。

3.3.1 基于语句的复制（statment）*

每一条会修改数据的sql都会记录在binlog中。
在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。Mysql默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。

优点
不需要记录每一行的变化，减少了Binlog日志量，节约了IO，提高性能。
缺点
由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

3.3.2 基于行的复制（row）*

不记录sql语句上下文相关信息，仅保存哪条记录被修改。
把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从Mysql5.0 开始支持。

优点
binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row级别的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
缺点
所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

新版的Mysql中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

3.3.3 混合类型的复制（mixed）*

是以上两种方式的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

3.3.4 Binlog基本配置与格式设定

1、基本配置
Mysql BInlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。示例：

binlog_format        = MIXED                 //binlog日志格式
log_bin              = 目录/mysql-bin.log    //binlog日志名
expire_logs_days     = 7                    //binlog过期清理时间
max_binlog_size      = 100m                 //binlog每个日志文件大小

2、Binlog日志格式选择
Mysql默认是使用Statement日志格式，推荐使用mixed。
由于一些特殊使用，可以考虑使用row，如自己通过Binlog日志来同步数据的修改，这样会节省很多相关操作。对于Binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

3.4 主从复制的几个问题

3.4.1 master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。

3.4.2 主从复制中，可以有N个slave,可是这些slave又不能进行写操作，他们的作用是什么？

主要用于实现分担负载,可以将读的任务分散到slaves上。数据备份，从而实现高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。

3.4.3 当主服务器的二进制日志每产生一个事件，都需要发往从服务器，如果有N个从服务器, 那是发N次，还是只发一次？

应该发N次。实际上，在MySQL的master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。
可以这样：slave-1是master的从服务器，slave-1又是slave-2、slave-3,…的主服务器。slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。