1.mysql整体框架

从上往下看，依次会分为网络连接层、系统服务层、存储引擎层、以及文件系统层，往往编写SQL后，都会遵守着MySQL的这个架构往下走。

连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作。
服务层：主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。
存储引擎层：这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等。
文件系统层：涵盖了所有的日志，以及数据、索引文件，位于系统硬盘上。

1.1、日志模块

在MySQL中主要存在七种常用的日志类型，如下：

①binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
②redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
③undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
④error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
⑤general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
⑥slow-log：慢查询日志，主要记录执行时间较长的SQL。
⑦relay-log：中继日志，主要用于主从复制做数据拷贝。

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

2. 一条SQL的历程

2.1 一条查询SQL的历程

2.1 一条写入SQL的历程

3. MySQL之库表设计篇：一、二、三、四、五范式

3.1 数据库三大范式

到这里就已经将库表设计的三范式做了直观阐述，总结如下：

第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。
第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

3.2 巴斯-科德范式（3.5范式）

第三范式只要求非主键字段之间，不能存在依赖关系，但没要求联合主键中的字段不能存在依赖，因此第三范式并未考虑完善，巴斯-科德范式修正的就是这点。

3.3 第四范式

一个表中至少需要有三个独立的字段才会出现多值依赖问题，多值依赖是指表中的字段之间存在一对多的关系，也就是一个字段的具体值会由多个字段来决定。第四范式的定义就是要消除表中的多值依赖关系。

3.4 第五范式

多值依赖也属于连接依赖的一种，而连接依赖也包含了多值依赖

4. mysql索引

索引本质上和表是一样的，都是磁盘中的文件，那也就代表着创建一个索引，并不像单纯的给一张表加个约束那么简单，而是会基于原有的表数据，重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据，那创建索引时，就需要将索引字段上的1000W个值全部拷贝到本地索引文件中，同时做好排序并与表数据产生映射关系。

4.1 索引分类

聚簇索引、非聚簇索引、唯一索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、普通索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引......

4.2 引入索引的优缺点

首先来聊聊索引机制带来的利害关系，有句古话曾说过： “凡事有利必有弊” ，而MySQL的索引机制也不例外，引入索引机制后，能够给数据库带来的优势很明显：

①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。
②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。
③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。
④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。
⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。
⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

看着上面一条又一条的好处，似乎感觉索引好处很大啊，对于这点确实毋庸置疑，但只有好处吗？No，同时也会带来一系列弊端，如：

①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。
②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。
③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

4.2.1 主键索引的缺陷

众所周知，一张表中大多数情况下，会将主键索引以聚簇的形式存在磁盘中，上篇文章也聊到过，聚簇索引在存储数据时，表数据和索引数据是一起存放的。同时，MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。为何不使用UUID呢？因为UUID是无序的，如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构。

4.2.2 联合索引存在的矛盾

为了多条件查询时的效率更高，一般都会同时对多个字段建立联合索引，但之前也聊到过，联合索引存在一个致命的问题，比如在用户表中，通过id、name、age三个字段建立一个联合索引，此时来了一条查询SQL，如下：

sql
复制代码
SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";

而这条SQL语句是无法使用联合索引的，为什么呢？因为查询条件中，未包含联合索引的第一个字段，想要使用联合索引，那么查询条件中必须包含索引的第一个字段。因此在建立索引时也需要考虑这个问题，确保建立出的联合索引能够命中率够高。

4.2.3 全文索引存在的硬伤

之前做模糊查询时，通常都会使用like%语法，不过这种方式虽然能够实现效果，但随着表越来越大，数据越来越多时，其性能会出现明显下降，而全文索引的推出则能够完美解决该问题，可以利用全文索引代替like%语法实现模糊查询，它的性能会比like%快上N倍。

4.2.4 唯一索引存在的快慢问题

SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX";

如果COLUMN_XX字段上建立的是唯一索引，当找到一条数据后就会立马停下检索，因此本身建立唯一索引的字段值就具备唯一性。因此唯一索引查询数据时，会比普通索引快上一截，但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。

4.2.5 哈希索引的致命问题

哈希索引，也就是数据结构为Hash类型的索引，不过估计大家接触的比较少，毕竟创建索引时都默认用的B+树结构。但要比起查询速度，哈希索引绝对是MySQL中当之无愧的魁首！因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。

4.3 建立索引时需要遵守的原则

下面我们就一起来看看建立索引时，需要遵守的一些原则：

①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

对于索引机制，在建立时应当参考上述给出的意见，这每一条原则都是从实际经验中总结出来的，前面八条不一定要全面思考，但后面七条注意点，一定要牢记，如若你的索引符合后面七条中的描述，那一定要更改索引。

4.4 索引失效

查询中带有OR会导致索引失效：EXPLAIN SELECT * FROM zz_users WHERE user_id = 1 OR user_name = "熊猫";其中既包含了主键索引的字段，又包含了联合索引的第一个字段，虽然所有查询条件都包含了索引字段，但由于使用了OR，最终导致索引失效。
模糊查询中like以%开头导致索引失效.
字符类型查询时不带引号导致索引失效.
索引字段参与计算导致索引失效:EXPLAIN SELECT * FROM zz_users WHERE user_id - 1 = 1;
段被用于函数计算导致索引失效:EXPLAIN SELECT * FROM zz_users WHERE SUBSTRING(user_name,0,1) = "竹子";
违背最左前缀原则导致索引失效: SELECT * FROM zz_users WHERE user_sex = "男" AND password = "1234";应该改为SELECT user_name,user_sex,password FROM zz_users WHERE user_name = "竹子" AND user_sex = "男";因为此时所需的user_name、user_sex、password三个字段数据，在联合索引中完全包含，因此可以直接通过联合索引获取到数据。
不同字段值对比导致索引失效:SELECT * FROM zz_users WHERE user_name = user_sex;
反向范围操作导致索引失效 :一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时并不会走索引，索引此时失效了，但是做正向范围查询时，索引依旧有效。

4.5 使用索引的正确姿势

其实到这里，对于如何使用索引才是正确的呢？总结如下：

①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。
⑨....... 实际上无非就是根据前面给出的索引失效情况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上提升数据检索的效率。

4.6 索引为什么使用B+Tree

4.6.1 索引为何不选择二叉树？

①如果索引的字段值是按顺序增长的，二叉树会转变为链表结构。
②由于结构转变成了链表结构，因此检索的过程和全表扫描无异。
③由于树结构在磁盘中，各节点的数据并不连续，因此无法利用局部性原理。

4.6.2 索引为何不选择红黑树？

①虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
②每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理。

4.6.3 索引为何不选择B-Tree？

虽然对比之前的红黑树更矮，检索数据更快，也能够充分利用局部性原理减少IO次数，但对于大范围查询的需求，依旧需要通过多次磁盘IO来检索数据。

4.6.4 索引为何要选择B+Tree？

良好的平衡性：B+树是一种自平衡的搜索树，它具有良好的平衡性质。这意味着从根节点到叶节点的路径长度相等或相差很小，使得数据的查找时间相对稳定，不会因为数据量的增加而导致性能下降。
顺序访问性能：B+树的内部节点和叶节点都是按照键的顺序排列的。这种有序性使得范围查询、范围扫描和顺序访问变得更加高效，因为相邻的节点在磁盘上通常是连续存储的，从而减少了磁盘I/O的次数。
减少磁盘I/O次数：B+树的节点通常比内存页的大小小得多。当进行索引查找时，B+树可以通过较少的磁盘I/O次数定位到所需的数据页或叶节点，从而减少了对磁盘的访问次数，提高了查询性能。
支持范围查询：由于B+树的有序性，它能够高效地支持范围查询，如"大于某个值"、"小于某个值"、"介于某个范围内"等操作。
支持快速插入和删除：B+树的插入和删除操作相对高效。由于B+树的平衡性质，这些操作通常只涉及局部的调整，而不需要进行整个树的重构。
支持高并发和多用户访问。

5 mysql的事务

事务是指由一组数据库操作组成的逻辑单位，这组操作要么全部成功执行，要么全部失败回滚。

5.1 事务的ACID原则

ACID主要涵盖四条原则，即：

A/Atomicity：原子性
C/Consistency：一致性
I/Isolation：独立性/隔离性
D/Durability：持久性

5.2 ysql事务隔离机制

事务隔离机制分为了四个级别：

①Read uncommitted/RU：读未提交
②Read committed/RC：读已提交
③Repeatable read/RR：可重复读
④Serializable：序列化/串行化上述四个级别，越靠后并发控制度越高，也就是在多线程并发操作的情况下，出现问题的几率越小，但对应的也性能越差，MySQL的事务隔离级别，默认为第三级别：Repeatable read可重复读。

5.2.1 数据库脏读问题

脏读的意思是指一个事务读到了其他事务还未提交的数据，也就是当前事务读到的数据，由于还未提交，因此有可能会回滚。

5.2.2 数据库的不可重复读问题

不可重复读问题是指在一个事务中，多次读取同一数据，先后读取到的数据不一致。

5.2.3 数据库的幻读问题

幻读问题的原因是在于：另外一个事务在第一个事务要处理的目标数据范围之内新增了数据，然后先于第一个事务提交造成的问题。

5.3 事务的四大隔离级别

在上面连续讲了脏读、不可重复读以及幻读三个问题，那这些问题该怎么解决呢？其实四个事务隔离级别，解决的实际问题就是这三个，因此一起来看看各级别分别解决了什么问题：

①读未提交：处于该隔离级别的数据库，脏读、不可重复读、幻读问题都有可能发生。
②读已提交：处于该隔离级别的数据库，解决了脏读问题，不可重复读、幻读问题依旧存在。
③可重复读：处于该隔离级别的数据库，解决了脏读、不可重复读问题，幻读问题依旧存在。
④序列化/串行化：处于该隔离级别的数据库，解决了脏读、不可重复读、幻读问题都不存在。

5.4 MySQL的事务实现原理

5.4.1 正常SQL的事务机制

任意一条写SQL的执行都会记录三个日志：undo-log、redo-log、bin-log。

undo-log：主要记录SQL的撤销日志，比如目前是insert语句，就记录一条delete日志。
redo-log：记录当前SQL归属事务的状态，以及记录修改内容和修改页的位置。
bin-log：记录每条SQL操作日志，主要是用于数据的主从复制与数据恢复/备份。

在写SQL执行记录的三个日志中，bin-log暂且不需要关心，这个跟事务机制没关系，重点是undo-log、redo-log这两个日志，其中最重要的是redo-log这个日志。但要注意：MySQL是基于磁盘的，但磁盘的写入速度相较内存而言会较慢，因此MySQL-InnoDB引擎中不会直接将数据写入到磁盘文件中，而是会先写到BufferPool缓冲区中，当SQL被成功写入到缓冲区后，紧接着会将redo-log日志中相应的记录改为commit状态，然后再由MySQL刷盘机制去做具体的落盘操作。

5.4.2 多条SQL的事务机制

先把前面的案例搬下来，如下：

sql
复制代码
-- 开启事务
start transaction;
-- 修改 ID=4 的姓名为：黑熊（原本user_name = 1111）
update `zz_users` set `user_name` = "黑熊" where `user_id` = 4;
-- 删除 ID=1 的行数据
delete from `zz_users` where `user_id` = 1;
-- 提交事务
COMMIT;

①当MySQL执行时，碰到start transaction;的命令时，会将后续所有写操作全部先关闭自动提交机制，也就是后续的所有写操作，不管有没有成功都不会将日志记录修改为commit状态。

②先在redo-log中为第一条SQL语句，记录一条prepare状态的日志，然后再生成对应的撤销日志并记录到undo-log中，然后执行SQL，将要写入的数据先更新到缓冲区。

③再对第二条SQL语句做相同处理，如果有更多条SQL则逐条依次做相同处理..... ，这里简单的说一下撤销日志长啥样，大致如下： ④直到碰到了rollback、commit命令时，再对前面的所有写SQL做相应处理：

如果是commit提交事务的命令，则先将当前事务中，所有的SQL的redo-log日志改为commit状态，然后由MySQL后台线程做刷盘，将缓冲区中的数据落入磁盘存储。
如果是rollback回滚事务的命令，则在undo-log日志中找到对应的撤销SQL执行，将缓冲区内更新过的数据全部还原，由于缓冲区的数据被还原了，因此后台线程在刷盘时，依旧不会改变磁盘文件中存储的数据。

5.4.3 SQL事务恢复机制

现在再来思考一个问题，有没有这么一种可能呢？也就是当SQL执行时，数据还没被刷写到磁盘中，结果数据库宕机了，那数据是不是就丢了啊？毕竟本地磁盘中的数据，在MySQL重启后依旧存在，但缓冲区中还未被刷到磁盘的数据呢？因为缓冲区位于内存中，所以里面的数据重启是不会存在的撒？

对于这个问题呢实际上并不需要担心，因为前面聊到过redo-log是一种预写式日志，会先记录日志再去更新缓冲区中的数据，所以就算缓冲区的数据未被刷写到磁盘，在MySQL重启时，依旧可以通过redo-log日志重新恢复未落盘的数据，从而确保数据的持久化特性。

当然，有人或许又会问：那如果在记录redo-log日志时，MySQL芭比Q了咋整？如果遇到了这个问题呢？

但如果在记录日志的时候MySQL宕机了，这代表着SQL都没执行成功，SQL没执行成功的话，MySQL也不会向客户端返回任何信息，因为MySQL一直没返回执行结果，因此会导致客户端连接超时，而一般客户端都会有超时补偿机制的，比如会超时后重试，如果MySQL做了热备/灾备，这个重试的时间足够MySQL重启完成了，因此用户的操作依旧不会丢失（对于超时补偿机制，在各大数据库连接池中是有实现的）。

6 高并发场景下该如何保证数据读写的安全性？

事务是基于数据库连接的，而每个数据库连接在MySQL中，又会用一条工作线程来维护，也意味着一个事务的执行，本质上就是一条工作线程在执行，当出现多个事务同时执行时，这种情况则被称之为并发事务，所谓的并发事务也就是指多条线程并发执行。

6.1 共享锁

共享锁的意思很简单，也就是不同事务之间不会排斥，可以同时获取锁并执行，这就类似于之前聊过的《AQS-共享模式》，但这里所谓的不会排斥，仅仅只是指不会排斥其他事务来读数据，但其他事务尝试写数据时，就会出现排斥性。在MySQL中，我们可以在SQL语句后加上相关的关键字来使用共享锁，语法如下： SELECT ... LOCK IN SHARE MODE; -- MySQL8.0之后也优化了写法，如下： SELECT ... FOR SHARE;

6.2 独占锁

当一个线程获取到独占锁后，会排斥其他线程，如若其他线程也想对共享资源/同一数据进行操作，必须等到当前线程释放锁并竞争到锁资源才行。

在MySQL中，可以通过如下方式显式获取独占锁： SELECT ... FOR UPTATE;

6.2 表锁

表锁的意思也就以表作为锁的基础，将锁加在表上，一张表只能存在一个同一类型的表锁。

-- MyISAM引擎中获取读锁（具备读-读可共享特性） LOCK TABLES table_name READ;

-- MyISAM引擎中获取写锁（具备写-读、写-写排他特性） LOCK TABLES table_name WRITE;

6.2 行锁

通常而言，为了尽可能提升数据库的整体性能，所以每次在加锁时，锁的范围自然是越小越好，举个例子：假设此时有1000个请求，要操作zz_users表中的数据，如果以表粒度来加锁，假设第一个请求获取到的是排他锁，也就意味着其他999个请求都需要阻塞等待，其效率可想而知。

7 编写SQL时的注意点

7.1 查询时尽量不要使用*

写*的确能让程序员更省力，但对机器就不太友好了，因此在写查询语句时一律不要使用*代替所有字段.其实主要有如下几方面的原因：

①分析成本变高。
②网络开销变大。
③内存占用变高。
④维护性变差。

7.2 表查询时尽量不要关联太多表

对于这点的原因其实很简单，一旦关联太多的表，就会导致执行效率变慢，执行时间变长，原因如下：

数据量会随表数量呈直线性增长，数据量越大检索效率越低。
当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在16~18张表之内（阿里开发规范中要求控制在3张表以内）。

7.3 多表查询时一定要以小驱大

所谓的以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据 MySQL优化器选择驱动表的逻辑如下：

①如果指定了连接条件，满足查询条件的小数据表作为驱动表。
②如果未指定连接条件，数据总行数少的表作为驱动表。

7.4 不要使用like左模糊和全模糊查询

如若like关键字以%号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免%xxx、%xxx%这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

7.5 查询时尽量不要对字段做空值判断

select * from xxx where yyy is null; select * from xxx where yyy not is null; 当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况，一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替.

7.6 不要在条件查询`=`前对字段做任何运算

select * from zz_users where user_id * 2 = 8; select * from zz_users where trim(user_name) = "熊猫"; 但上述这类情况都不会走索引，因为MySQL优化器在生成执行计划时，发现这些=前面涉及到了逻辑运算，因此就不会继续往下走了，会将具体的运算工作留到执行时完成，也正是由于优化器没有继续往下走，因此不会为运算完成后的字段选择索引，最终导致索引失效走全表查询。

7.7 !=、!<>、not in、not like、or...要慎用

简单来说就是这类写法也可能导致索引失效，因此在实际过程中可以使用其他的一些语法代替，比如or可以使用union all来代替：

7.8 必要情况下可以强制指定索引

有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过force index，如下： select * from zz_users force index(unite_index) where user_name = "熊猫";

7.9 从业务设计层面减少大量数据返回的情况

之前在做项目开发时碰到过一些奇葩需求，就是要求一次性将所有数据全部返回，而后在前端去做筛选展现，这样做虽然也可以，但如果一次性返回的数据量过于巨大时，就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。

7.10 尽量避免深分页的情况出现

前面刚刚聊过分页，分页虽然比较好，但也依旧存在问题，也就是深分页问题，如下： select xx,xx,xx from yyy limit 100000,10; 上述这条SQL相当于查询第1W页数据，在MySQL的实际执行过程中，首先会查询出100010条数据，然后丢弃掉前面的10W条数据，将最后的10条数据返回，这个过程无异极其浪费资源。如果查询出的结果集，存在递增且连续的字段，可以基于有序字段来进一步做筛选后再获取分页数据，如下：

csharp
复制代码
select xx,xx,xx from yyy where 有序字段 >= nnn limit 10;

8 MySQL之高并发大流量情况下海量数据分库分表的正确姿势

8.1 为什么需要分库分表

请求数太高：并发量急剧增加、吞吐量严重下降、连接出现异常、数据库时常宕机、系统经常崩溃一系列后患问题。
数据查询慢：单表或单库数据量过大，导致数据检索的效率直线降低。
数据量太大：当一个库的数据存储量太大时，就算每张表的并发数不多，但是因为是海量数据，单库中存在大量的数据表，每张表都有一部分并发请求，导致最终单库的连接数阈值成为数据库的瓶颈。
mysql数据库瓶颈：一般程序的性能瓶颈都源自于硬件问题，而问题归根到底都属于IO、CPU瓶颈，接下来聊一聊IO、CPU瓶颈。

8.2 分库分表正确的拆分手段

分库分表的拆分规则也可分为：水平、垂直 两个维度。分库、分表是两个概念，两者并不是同一个名词，所以这里需要牢记！按拆分的粒度来排序，共计可分为四种方案：垂直分表、水平分表、垂直分库、水平分库。

8.3 不同场景下的分表方案

分表大多是在单表字段过多或数据过多的场景下，会选择的一种优化方案，当一个表字段过多时，应当考虑垂直分表方案，将多余的字段拆分到不同的表中存储。当一个表的数据过多时，或者数据增长速率过快时，应当考虑通过水平分表方案，来降低单表的数据行数。

8.3.1 垂直分表：结构不同，数据不同（表级别）

当一张表由于字段过多时，会导致表中每行数据的体积变大，而之前不仅一次聊到过：单行数据过大带来的后患，一方面会导致磁盘IO次数增多，影响数据的读写效率；同时另一方面结果集响应时还会占用大量网络带宽，影响数据的传输效率；再从内存维度来看，单行数据越大，缓冲区中能放下的热点数据页会越少，当读写操作无法在内存中定位到相应的数据页，从而又会产生大量的磁盘IO。

8.3.1 水平分表：结构相同，数据不同（表级别）

虽然数据库中有索引机制，能够确保单表在海量数据的基础上，检索数据的效率依旧可观，但随着数据不断增长，当达到千万级别时，就会出现明显的查询效率下降的问题。水平拆分之后的两张或多张表，每张表的表、索引等结构完全相同，各表之间不同的地方在于数据，每张表中会存储不同范围的数据。不过拆分之后的水平表究竟会存储哪个范围的数据，这要根据水平分表的策略来决定，你可以按ID来以数据行分表，也可以按日期来以周、月、季、年.......分表。

8.4 不同场景下的分库方案

经过前面的分表总结后可以得知：如果是因为库级别的压力较大，这时就需要考虑分库方案。分库和分表一样，也可以按垂直和水平两个维度来分，垂直分库本质上就是按业务分库，也就是现在分布式/微服务架构中，业务独享库的概念，而水平分库则是对同一个节点作横向拓展，也就是高可用集群的概念。

8.4.1 垂直分库：结构不同，数据不同（库级别）

当数据库使用单机的结构部署，在大流量/高并发情况下遇到瓶颈时，此时就可以考虑分库方案了，首先来聊聊垂直分库。般为了方便团队分工合作和后续管理维护，通常都会对单个项目划分模块，按照业务属性的不同，会将一个大的项目拆分为不同的模块，同时每个业务模块也会在数据库中创建对应的表。

垂直分库后，每个库中存储的数据都不相同，因为是按照业务特性去将对应的表抽出去了组成新库，所以库结构也是不同的，用户库是由用户相关的表组成、信审库是由心生相关的表组成。

8.4.2 水平分库：结构相同，数据不同（库级别）

经过前面的垂直分库后，根据不同的业务类型，将访问压力分发到不同的库处理后，虽然在极大程度上提升了数据层的负荷能力，但如果某类业务的并发数依旧很高，比如经过前面的业务分流后，假设平台库需要承载5000的并发、信审库依旧需要承载1W的并发，这也远超出了单个数据库节点的处理瓶颈，最终可能还是会能把对应的数据库节点打宕机，所以此时可通过水平分库的方案，来提升某类业务库的抗并发吞吐量

水平分库是基于一个节点，然后直接横向拓展，这也就意味着同一业务的数据库，各节点之间的库结构完全相同，但每个节点中的数据是否相同，这就要看你自己去决定了，一般情况下都是不同的，也就是不同节点的库会存储不同范围的数据。

8.4.3 另类的分库方案

前面聊清楚了分库分表中经典的垂直分库和水平分库方案，但除开这两种之外，还有一些另类的分库方案，也就是指一些数据库的高可用方案，例如主从复制、读写分离、双主热备等方案。

主从方案：一般会搭建读写分离，写请求发往主节点处理，读请求发往从节点处理，从节点会完全同步主节点的数据，从而实现读写请求分开处理的效果，能够再一定程度上提升数据存储层整体的并发处理能力。同时当主机挂掉时，从机也能够在很快的时间内替换成主机，以此确保数据层的高可用。

多主方案：一般是双主方案，两台数据库节点之间互为主从，相互同步各自的数据，两台节点中都具备完整的数据，读写请求可以发给任意节点处理。相较于前面的主从读写分离架构，这种双主双写架构的灾备能力更强，因为当其中某个节点宕机时，另一个节点可以完全接替对方的流量，不存在从机切换成主机的时间开销，因此能够保证数据100%不丢失。

Mysql数据库整理