数据库——额外补充

大表优化

当MySQL一个表中记录数过大时，数据库的CRUD性能会明显下降，常见的优化措施为：

限定数据范围

禁止不带任何数据范围条件的查询语句，比如在用户查询订单历史时，我们可以控制在一个月的范围内；

读/写分离

随着用户和数据的增多，单机的数据库支撑不了快速发展的业务，因此便有了数据库集群。而读写分离是数据库集群方式之一。

读写分离即一个主库，一个从库或一主多库。业务服务器把写的操作都写道主数据库上，读的操作都到从库查询。主库会同步数据到从库来保证数据库一致。这种集群方式的本质是把访问的夜里从主库中转移到从库，如果写操作很多，不适用于这种集群方式；这种方式只能分担访问的压力，不能分担存储量过多的压力。

在单机的情况下，一般都会加索引来优化数据库。加索引可以对查询操作有优化，但会影响更改操作，因为写操作可能需要更新下索引。在主从分离后，我们可以单独针对读库来做索引上的优化，而写库可以减少索引来提高写的效率。

主从同步延迟问题

主库有数据写入后，同时也写入binlog二进制日志文件中；从库则通过binlog文件来同步数据。这期间是有一定的时间延迟的，若同时有大量数据写入，则延迟时间更长。

以付款操作为例子，主库已经写入数据了，但查询的任务是到从库里查，在这段时间延迟里，从库没有你的付款记录，因此查询页面上可能显示你还没付款。因此类似付钱这种业务，读写都会到主库中，避免出现延迟问题。而其他不重要的问题，例如修改头像什么的，就可以读写分离，延迟一会也问题不大。即关键业务读写都由主库承担，非关键业务读写分离。

分配机制

我们需要考虑怎么制定写操作是到主库里写，而读操作是到从库中读。

（1）代码封装

抽出一个中间层，让这个中间层来实现读写分离和数据库连接。这样的实现简单，且可以根据业务定制来变化。

缺点

• 如果有个数据库宕机，发送主从切换。那么就得修改配置，重启。
• 一个业务可能包含若干个子系统，一个子系统是java写的一个子系统用go写的。这样就得分别为不同语言实现一套中间层，重复开发。

（2）数据库中间层

有一个独立的系统专门实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的SQL协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

它的优点是：因为是通过sql协议的所以可以兼容不同的语言不需要单独写一套并且有中间件来实现主从切换，业务服务器不需要关心这点。

缺点是：中间件实现很复杂，难度比代码封装高很多。不过有开源的数据库中间件，如Mysql Proxy，Mysql Route。

垂直分区

根据数据库里的数据表的相关性进行拆分，例如一个用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单的说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

优点：使得列数据变小，在查询时减少读取的页数，减少IO次数。此外还可以简化表的结构，易于维护。

缺点：键会出现冗余，需要管理冗余列。

水平分区

保持数据表结构不变，通过某种策略将存储数据分片。这样每片数据分散到不同的表或库中，达到了分布式的目的。举例来说：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量，如果只是分表的话，由于表的数据还是在同一台机器上，因此只能解决单一表数据过多的问题。水平拆分最好是分库。

 

---

一条SQL语句在MySQL中如何执行

MySQL 基本架构

连接器：身份认证和权限相关（登录MySQL）

查询缓存：执行查询语句时，会先去查询缓存（MySQL 8.0 版本后移除）

分析器：若没有命中缓存，SQL语句就会经过分析器，分析器首先做词法分析，再做语法分析。

优化器：按 MySQL 认为最优的方案去执行。

执行器:  执行语句，然后从存储引擎返回数据。

MySQL 主要分为 服务器层和存储引擎层：

• 服务器：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有垮存储引擎的功能都在此层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎：主要负责数据的存储和读取，支持 InnoDB、MyISAM、Memory 等

查询语句

以下面的语句为例

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

（1）检查该语句是否有权限，若没有则返回错误信息。

（2）通过分析器进行词法分析，提取sql语句的关键元素，如上面语句中的select，查询的表名是 tb_student，需要查询所有列，查询条件是该表的id = 1.然后判断sql语句是否有语法错误。

（3）优化器进行优化，如上面语句中有两种执行方案：

• 先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18
• 先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

优化器根据自己的优化算法选择效率最好的方案，然后开始准备执行。

（4）权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';

执行更新时需要记录日志，MySQL自带binlog归档日志，InnoDB 引擎还自带一个日志模块 redo log（重做日志），redo log使得InnoDB 引擎具有crash-safe 的能力，即使数据库发生异常重启，之前提交的记录都不会丢失。而binlog 日志只能用来存档。

此处我们以InnoDB 引擎为例：

（1）拿到查询语句后，将age修改为19，然后调用存储引擎InnoDB的API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo日志。此时redo日志进入准备状态。

（2）接收器记录binlog，然后调用引擎接口，提交redo日志为提交状态。

在redo日志处于预提交状态，binglog已经写完的时候，若发生异常重启，则MySQL会：

1. 判断redo日志是否完整，若是完整则提交
2. 若redo日志只是预提交而非提交状态，则去判断binlog是否完整，若完整则提交redo日志，否则回滚事务

 

---

一条SQL语句执行得很慢的原因有哪些

这种问题需要分两组情况：

• 大多情况下是正常的，偶尔出现很慢。
• 在数据量不变的情况下，这条SQL语句一直都很慢。

偶尔很慢

（1）数据库在刷新脏页

当内存数据页与磁盘数据页内容不一致时，这个内存页称为脏页。当我们往数据库添加/更新一条数据时，数据库会更新内存中中相应的数据，这些更新的字段不会立马同步持久化到磁盘中，而是将这些更新的记录写入redo日志中。等到空闲时间，在通过redo日志把最新数据同步到磁盘里。

（2）拿不到锁

我们执行的这条语句涉及到的表加锁了/某一行加锁了，而且有人在用，因此我们只能等别人释放锁。我们可以通过 show processlist 命令来查看当前状态。

一直都很慢

（1）没有以某个字段建立索引或者没有用到索引

如果没有建立索引，只能全盘扫描。详情的解释参考此处。

对于出现 没有用到索引的情况，可能是我们无意中的行为：

例1：

select * from t where c - 1 = 1000;

对于上述的例子，如果建立了c字段的索引，是不会使用到索引的。因为在字段的左边做了运算。正确查询如下：

select * from t where c = 1000 + 1;

例2：

select * from t where pow(c,2) = 1000;

在查询时对字段用了函数操作，也会导致没有用上索引。

例3：

select * from t where 100 < c and c < 100000

虽然我们可能建立了c列索引，但由于c列索引对应的B+树的叶子节点存放的不是完整用户记录，而此例我们又需要符合条件的完整用户记录，因此我们还需要回表。此例对应此处的《回表的代价》中的例子。

 

 

---

参考资料

https://juejin.im/post/5cbdaf80f265da038d0b444e#heading-2           Mysql数据库读写分离

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485097&idx=1&sn=84c89da477b1338bdf3e9fcd65514ac1&chksm=cea24962f9d5c074d8d3ff1ab04ee8f0d6486e3d015cfd783503685986485c11738ccb542ba7&token=79317275&lang=zh_CN#rd     一条SQL语句在MySQL中如何执行