MySql面试题（索引、分库分表、高可用等）

一时失言乱红尘 2023-07-24 03:14 19阅读 0赞

## 前言 ##

MySql基础：[https://blog.csdn.net/yhl\_jxy/category\_5622941.html][https_blog.csdn.net_yhl_jxy_category_5622941.html]

MySql进阶：[https://blog.csdn.net/yhl\_jxy/category\_6390753.html][https_blog.csdn.net_yhl_jxy_category_6390753.html]

# 一 为什么要对数据库进行优化？ #

## 1、超大容量问题 ##

1）海量数据单表，数据库处理能力将达到上限；

2）硬盘空间达到极限，将无空间可用；

## 2、性能问题 ##

服务器处理能力有限，单库单表TPS有上限，无论怎么优化，随着数据量的增长最后都会面临无法增长的局面，

影响业务处理能力和用户体验。

## 3、升级扩展问题 ##

单一主库无法灵活的进行升级和扩展，无法满足公司快速发展要求，所有的数据都放在同一库里面，存在单点故障的风险；

# 二 数据库有哪些常用的优化手段？ #

1、优化字段，索引，Sql，数据归档

2、加缓存（MongoDB，Redis，Memcached，本地缓存等等）

3、读写分离（属于业务相关优化）

4、分库分表（基于业务分库分表）

5、分区（纯物理级别处理）

# 三 分库分表拆分方式？ #

分库分表的基本思想就是把一个数据库拆分成多个部分放到不同的数据库(Server)上，从而缓解单一数据库的性能。

主要是围绕垂直拆分和水平拆分展开。

## 1、垂直拆分 ##

**从数据库表的数量层面分库：**

对于海量数据的数据库，如果是因为表太多，我们可以按模块，把关系紧密的拆分出来，单独运行Server。

如果一个数据库表太多，所有应用都请求都打到一台实例上，数据库压力很大，会成为性能的瓶颈。

**从数据库表的字段数量层面分库：**

如果一个表字段特别多，意为着一般索引也会增多，索引文件会增大，查找时从磁盘读取索引文件速度

会降低，同时根据索引去读取数据时，单条数据的值比较大，从磁盘读到内存的速度也会降低。

所以，一个表字段特别多的时候，就要考虑按业务拆分成多个表，然后再按业务拆分到多个数据库Server。

## 2、水平拆分 ##

对于表不多，但是表的数据都是海量数据，这种情况比较适合做水平拆分，按照表数据的规则（比如按ID散列，用户ID等）

拆分到多个Server。

需要注意：分库分表不是一个事情，是两个维度的概念，你可以只分库，不分表，或者只分表，不分库，

或者分库分表同时做都可以。

# 四 分库分表会带来哪些问题？ #

## 1）Id主键 ##

一旦数据库被拆分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。

一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；

另一方面，应用程序在插入数据之前需要先获得ID，以便进行SQL路由；

**UUID：**

使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，

最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

**结合数据库维护一个Sequence表：**

在数据库中建立一个Sequence表，表的结构类似于：

CREATE TABLE \`SEQUENCE\` (

\`table\_name\` varchar(18) NOT NULL,

\`nextid\` bigint(20) NOT NULL,

PRIMARY KEY (\`table\_name\`)

) ENGINE=InnoDB；

每当需要为某个表的新纪录生成ID时就从Sequence表中取出对应表的nextid，并将nextid的值加1后更新到数据库中

以备下次使用。此方案也较简单，但缺点同样明显：

由于所有插入任何都需要访问该表，该表很容易成为系统性能瓶颈，同时它也存在单点问题，一旦该表数据库失效，

整个应用程序将无法工作。有人提出使用Master-Slave进行主从同步，但这也只能解决单点问题，并不能解决读写比

为1:1的访问压力问题。

**Twitter的分布式自增ID算法Snowflake：**

在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很

简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

\* 10---0000000000 0000000000 0000000000 0000000000 0 --- 00000 ---00000 ---000000000000

在上面的字符串中，第一位为未使用（实际上也可作为long的符号位），接下来的41位为毫秒级时间，

然后5位datacenter标识位，5位机器ID（并不算标识符，实际是为线程标识），然后12位该毫秒内的当前

毫秒内的计数，加起来刚好64位，为一个Long型。

这样的好处是，整体上按照时间自增排序，并且整个分布式系统内不会产生ID碰撞（由datacenter和机器ID作区分），

并且效率较高，经测试，snowflake每秒能够产生26万ID左右，完全满足需要。

## 2）join ##

只要是进行拆分，跨节点Join的问题是不可避免的。但是良好的设计和拆分却可以减少此类情况的发生。

解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id，根据这些id发起

第二次请求得到关联数据。

## 3）count，max等聚合函数，以及order by，group by等 ##

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。

解决方案：

与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。

和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。

但如果结果集很大，对应用程序内存的消耗是一个问题。

## 4）排序分页 ##

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容

易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，

我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，

最后再返回给用户。

## 5）数据迁移，容量规划，扩容等 ##

通常利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）

来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。

总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

## 6）分库数量 ##

分库数量首先和单库能处理的记录数有关，一般来说，Mysql 单库超过5000万条记录，Oracle单库超过1亿

条记录，DB压力就很大(当然处理能力和字段数量/访问模式/记录长度有进一步关系)。

在满足上述前提下，如果分库数量少，达不到分散存储和减轻DB性能压力的目的；如果分库的数量多，

好处是每个库记录少，单库访问性能好，但对于跨多个库的访问，应用程序需要访问多个库，如果是并发模式，

要消耗宝贵的线程资源；如果是串行模式，执行时间会急剧增加。

最后分库数量还直接影响硬件的投入，一般每个分库跑在单独物理机上，多一个库意味多一台设备。

所以具体分多少个库，要综合评估，一般初次分库建议分4-8个库，但是一般都是2的幂次方，对数据库扩展有好处。

## 7）分库策略 ##

分库维度确定后，如何把记录分到各个库里呢?

一般有两种方式

根据数值范围，比如用户Id为1-9999的记录分到第一个库，10000-20000的分到第二个库，以此类推。

根据数值取模，比如用户Id mod n，余数为0的记录放到第一个库，余数为1的放到第二个库，以此类推。

我们做过订单表分库分表，按照数据用户id取模分库，但是不是简简单单的取模，需要考虑分库以及分表情况。

## 8）路由透明 ##

分库从某种意义上来说，意味着DB schema改变了，必然影响应用，但这种改变和业务无关，所以要尽量保证分库

对应用代码透明，分库逻辑尽量在数据访问层处理。当然完全做到这一点很困难，具体哪些应该由DAL负责，

哪些由应用负责，这里有一些建议：

对于单库访问，比如查询条件指定用户Id，则该SQL只需访问特定库。此时应该由DAL层自动路由到特定库，

当库二次分裂时，也只要修改mod 因子，应用代码不受影响。

对于简单的多库查询，DAL负责汇总各个数据库返回的记录，此时仍对上层应用透明。

## 9）分库分表中间件 ##

从网上找了些组件：

**轻量级**

当当sharding-jdbc：https://github.com/dangdangdotcom/sharding-jdbc（现在改名叫sharding-sphere）

蘑菇街TSharding：https://github.com/baihui212/tsharding

**重量级**

sharding：https://github.com/go-pg/sharding

TDDL Smart Client的方式（淘宝）：https://github.com/alibaba/tb\_tddl

Atlas（Qihoo 360）：https://github.com/Qihoo360/Atlas

alibaba.cobar(（是阿里巴巴（B2B）部门开发）：https://github.com/alibaba/cobar

MyCAT（基于阿里开源的Cobar产品而研发）：http://www.mycat.org.cn/

Oceanus（58同城数据库中间件）：https://github.com/58code/Oceanus

OneProxy（支付宝首席架构师楼方鑫开发）：https://www.cnblogs.com/youge-OneSQL/articles/4208583.html

vitess（谷歌开发的数据库中间件）：https://github.com/youtube/vitess

**用哪个好？**

网上有很多比较，用sharding-sphere或mycat的比较多，sharding-jdbc作为一个组件集成在应用内，

而mycat则作为一个独立的应用需要单独部署；

分库分表中间件的一些比较：[https://www.cnblogs.com/iceggboom/p/10134853.html][https_www.cnblogs.com_iceggboom_p_10134853.html]

持续更新完善中......

[https_blog.csdn.net_yhl_jxy_category_5622941.html]: https://blog.csdn.net/yhl_jxy/category_5622941.html
[https_blog.csdn.net_yhl_jxy_category_6390753.html]: https://blog.csdn.net/yhl_jxy/category_6390753.html
[https_www.cnblogs.com_iceggboom_p_10134853.html]: https://www.cnblogs.com/iceggboom/p/10134853.html