数据库的数据太多了怎么办？特别大的访问量到数据库上怎么办？分库分表？| 大别山码将

た入场券 2022-09-13 04:28 30阅读 0赞

#### 数据库的数据太多了怎么办，一个表有一亿个数据（特别大的访问量到数据库上）？分库分表？Mysql的主从复制 ####

**1.使用优化查询的方法**

**1.使用索引**

应尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引。

**2.优化 SQL 语句**

2.1 **通过 explain(查询优化神器)用来查看 SQL 语句的执行效果**， 可以帮助选择更好的索引和优化查询语句， 写出更好的优化语句。 通 常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句， 把关 键字 EXPLAIN 加到前面， 查看执行计划。例如： explain select \* from news;  
     2.2 任何地方都不要使用 select \* from t ， 用具体的字段列表代 替“\*” ， 不要返回用不到的任何字段。  
     2.3 不在索引列做运算或者使用函数。  
     2.4 查询尽可能使用 limit 减少返回的行数， 减少数据传输时间和 带宽浪费。

**3.优化数据库对象**

3.1 **优化表的数据类型**  
     使用 procedure analyse()函数对表进行分析， 该函数可以对表 中列的数据类型提出优化建议。 能小就用小。 表数据类型第一个原 则是： **使用能正确的表示和存储数据的最短类型**。 这样可以减少对 磁盘空间、 内存、 cpu 缓存的使用。 使用方法： select \* from 表名 procedure analyse();  
3.2 **对表进行拆分**  
     通过拆分表可以提高表的访问效率。 有 2 种拆分方法：  
    **1.垂直拆分：**  
     **把主键和一些列放在一个表中， 然后把主键和另外的列放在另 一个表中。** 如果一个表中某些列常用， 而另外一些不常用， 则可以 采用垂直拆分。  
    2.**水平拆分：**  
    **根据一列或者多列数据的值把数据行放到二个独立的表中。**  
3.3 **使用中间表来提高查询速度**  
     创建中间表， 表结构和源表结构完全相同， 转移要统计的数据 到中间表， 然后在中间表上进行统计， 得出想要的结果。

\*\*4.硬件优化 \*\*

4.1 CPU 的优化  
     选择多核和主频高的 CPU。  
4.2 内存的优化  
     使用更大的内存。 将尽量多的内存分配给 MYSQL 做缓存。  
4.3 磁盘 I/O 的优化

4.3.1 使用磁盘阵列  
     RAID 0 没有数据冗余， 没有数据校验的磁盘陈列。 实现 RAID 0 至少需要两块以上的硬盘， 它将两块以上的硬盘合并成一块， 数据 连续地分割在每块盘上。  
     RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列， 其容量仅等于 一块硬盘的容量， 因为另一块只是当作数据“镜像”。      使用 RAID-0+1 磁盘阵列。 RAID 0+1 是 RAID 0 和 RAID 1 的组 合形式。 它在提供与 RAID 1 一样的数据安全保障的同时， 也提供了 与 RAID 0 近似的存储性能。

4.3.2 调整磁盘调度算法  
     选择合适的磁盘调度算法， 可以减少磁盘的寻道时间。

**5.MySQL 自身的优化**

对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数 进行优化调整。 如指定 MySQL 查询缓冲区的大小， 指定 MySQL 允 许的最大连接进程数等。

\*\*6.应用优化 \*\*

**6.1 使用数据库连接池  
6.2 使用查询缓存**  
     它的作用是存储 select 查询的文本及其相应结果。 如果随后收到 一个相同的查询， 服务器会从查询缓存中直接得到查询结果。 查询 缓存适用的对象是更新不频繁的表， 当表中数据更改后， 查询缓存 中的相关条目就会被清空。

**2.主从复制， 读写分离， 负载均衡**

目前，大部分的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台） 数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可 以利用数据库的这一功能，**实现数据库的读写分离，从而改善数据库的负载压力**。**一个系 统的读操作远远多于写操作，因此写操作发向 master，读操作发向 slaves 进行操作**（简 单的轮循算法来决定使用哪个 slave）。  
     利用数据库的读写分离，**Web 服务器在写数据的时候，访问主数据库（Master），主 数据库通过*主从复制机制*将数据更新同步到从数据库（Slave），这样当 Web 服务器读数 据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的 Web 应用可以轻 松地读取数据，而主数据库也只会承受少量的写入操作**，还可以实现数据热备份，可谓是 一举两得的方案。

**3.1 复制概述**

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

**3.2 复制原理**

MySQL 的主从复制原理如下。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Hsbynvh-1632738914954)(C:\\Users\\LENOVO-LX\\AppData\\Roaming\\Typora\\typora-user-images\\image-20210830095456063.png)\]][img-6Hsbynvh-1632738914954_C_Users_LENOVO-LX_AppData_Roaming_Typora_typora-user-images_image-20210830095456063.png]

从上层来看，复制分成三步：

*  Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。
 *  主库推送二进制日志文件 Binlog 到从库的中继日志 Relay Log 。
 *  slave重做中继日志中的事件，将 Master 上的改变反映到它自己的数据库中。所 以两端的数据是完全一样的。

从图中可以看出， Slave 服务器中有一个 SQL 线程(SQL Thread)从中继日志读 取事件， 并重做其中的事件， 从而更新 Slave 的数据， 使其与 Master 中的数据一致。 只要 该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。

**3.3 复制优势**

MySQL 复制的有点主要包含以下三个方面：

*  主库出现问题，可以快速切换到从库提供服务。
 *  可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
 *  可以在从库中执行备份，以避免备份期间影响主库的服务。

##### 主从复制的几种方式： #####

1.  同步复制  
        主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，必须等待验证*所 有的从服务器*的更新数据是否已经复制到其中，之后才可以自由处理其它进入的事务处理 请求。
2.  异步复制  
        主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，无需等待验证更 新数据是否已经复制到从服务器中，就可以自由处理其它进入的事务处理请求。
3.  半同步复制  
        主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，只需等待验证*其 中一台从服务器*的更新数据是否已经复制到其中，就可以自由处理其它进入的事务处理请 求，其他的从服务器不用管

**3. 数据库分表， 分区， 分库**

**分表：**

通过拆分表可以提高表的访问效率。 有 2 种拆分方法：  
    **1.垂直拆分：**  
     **把主键和一些列放在一个表中， 然后把主键和另外的列放在另 一个表中。** 如果一个表中某些列常用， 而另外一些不常用， 则可以 采用垂直拆分。  
    2.**水平拆分：**  
    **根据一列或者多列数据的值把数据行放到二个独立的表中。**

**分区**就是把一张表的数据分成多个区块，**这些区块可以在一个磁盘上，也可以在不同 的磁盘上**，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，**多块硬盘同 时处理不同的请求，从而提高磁盘 I/O 读写性能**，实现比较简单。 包括水平分区和垂直分 区。  
**分库**是**根据业务不同把相关的表切分到不同的数据库中**，比如 web、bbs、blog 等库。

![在这里插入图片描述][eaa2bf1d61704d1586e9891b482ab0d5.gif_pic_center]

[img-6Hsbynvh-1632738914954_C_Users_LENOVO-LX_AppData_Roaming_Typora_typora-user-images_image-20210830095456063.png]: /images/20220828/4255f115fb4f496dbbe87e97f169df85.png
[eaa2bf1d61704d1586e9891b482ab0d5.gif_pic_center]: /images/20220828/5c9ed9a083ea4811ae66fec431f5ce37.png