一、考点

1、分区表的原理

1）工作原理

创建表时使用 partition by 子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区即可。

2）适用场景

① 表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；

② 分区表的数据更易维护，可以对独立的分区进行独立的操作；

③ 分区表的数据可以分布在不同的机器上，从而高效使用资源；

④ 可以使用分区表来避免某些特殊的瓶颈；

⑤ 可以备份和恢复独立的分区。

3）限制

① 一个表最多只能有 1024 个分区；

② 5.1版本中，分区表表达式必须是整数， 5.5可以使用列分区；

③ 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；

④ 分区表中无法使用外键约束；

⑤ 需要对现有表的结构进行修改；

⑥ 所有分区都必须使用相同的存储引擎；

⑦ 分区函数中可以使用的函数和表达式会有一些限制；

⑧ 某些存储引擎不支持分区；

⑨ 对于 MyISAM 的分区表，不能使用 load index into cache；

⑩ 对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。

2、分库分表的原理

1）工作原理

通过一些 HASH算法 或者工具实现将一张数据表垂直或者水平进行物理切分。

2）适用场景

① 单表记录条数达到百万到千万级别时；

② 解决表锁的问题。

3）分表方式

① 水平分割

表很大，分割后可以降低在查询时需要读取的数据和索引的页数，同时也降低了索引的层数，提高查询速度。

使用场景：

• 表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用；
• 需要把数据存放在多个介质上（最新的数据放到不同服务器上，或者做缓存）。

缺点：

• 给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需 UNION 操作；
• 在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数（从某种意义上来说，会降低效率）。

② 垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

使用场景：

• 如果一个表中某些列常用，而另外一些列不常用（可以把常用的列单独拆分出来，查询的时候只查询常用的列即可）；
• 可以使数据行变小，一个数据页能存储更多数据，查询时减少 I/O 次数。

缺点：

• 管理冗余列，查询所有数据需要 JOIN 操作；
• 整体缺点：有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差；
• 对于应用层来说，逻辑算法无疑增加开发成本。

3、延伸：MySQL的复制原理及负载均衡

1）MySQL主从复制工作原理

• 在 主库 上把 数据更改 记录到 二进制日志（binary log）；
• 从库 将 主库 的日志复制到自己的 中继日志；
• 从库 读取 中继日志 中的事件（增、删、改），将其重放到 从库 数据中。

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

• 数据分布：随意停止或开始复制，并在不同地理位置分布数据备份。
• 负载均衡：降低单个服务器的压力。
• 高可用和故障切换：帮助应用程序避免单点失败（如：现在突然有台服务器挂掉了，这时候也不用害怕，因为有其他的备份机器，有其他正在运行的从机，立马切换过来就可以了，服务时不会受到任何影响的）。
• 升级测试：可以使用更高版本的MySQL作为 从库（先看看查询方面有没有问题）。

二、解题方法

充分掌握分区和分表的工作原理和适用场景，在面试中，此类题通常比较灵活，会给一些现有公司遇到问题的场景，大家可以根据分区和分表以及MySQL复制、负载均衡的适用场景来根据情况进行回答。

三、真题

设定网址的用户数量在千万级，但是活跃用户的数量只有 1%，如何通过优化数据库提高活跃用户的访问速度？

分析：分区分表的适用场景（把常用的数据分出来，把不常用的数据分到另外一个地方去），因此无论使用分区，还是分库分表，都可以解答此题。

使用分区：

• 可以使用MySQL的分区，因为MySQL分区可以帮助我们按照一个规则（把活跃用户分在一个区，不活跃的y用户分到另一个区），在进行查询的时候，可以进行操作活跃用户的时候，只操作活跃用户的那个区。

使用分库分表：

• 通过 水平切分 的方式，把活跃用户的数据切分成一个表，不活跃用户的数据放到另外一张表中，查询的时候，只查询活跃用户的数据表即可。