hana数据库 字段长度_数据库中的引擎、事物、锁、MVCC（五）

其实本章主要讲一下mysql的日志、关联查询join的内部衍生的算法以及优化方案。

一、mysql的主从复制

master（主服务器）写入 binlog日志

1. 事件通知 slave（从服务器）
2. slave的 IO Thread 读取binlog并 写入relay log
3. SQL Thread 读取 relay log 同步到数据库

以上就是数据（日志）的同步过程。

二、binlog

以上暴露除了一个点 什么是 bin log？

1、定义

bin log是MySQL的二进制日志，可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

2、使用场景

• MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 （就是上面第一点主从复制）
• 自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

三、Join原理

咱们在做关联查询的时候，经常用到join，其内部使用的是NestedLoopJoin算法。

NestedLoopJoin算法有三种实现算法：

• Simple Nested-Loop Join（简单的嵌套循环连接）
• Index Nested-Loop Join（索引嵌套循环连接）
• Block Nested-Loop Join（缓存块嵌套循环连接）

1、Simple Nested-Loop Join

简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果;

例如：select * from user tb1 left join level tb2 on tb1.id=tb2.user_id;

“交叉相乘”得到的最后的数据就是算法返回的数据，也就是咱们进行管理查询，忘记加条件时查询出来的数据。

即“笛卡尔积”。

最后的数据量=表A数据量 × 表B数据量 ×…… 。可见最后的数据量是多么的庞大。

2、Index Nested-Loop Join

索引嵌套循环连接其优化的思路 主要是为了减少内层表数据的匹配次数;简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数;

从原来的匹配次数=外层表行数 * 内层表行数 ==变成了==> 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。

例如： 两张表 user[id pk，……] level[user_id，……]

select * from user tb1 left join level tb2 on tb1.id=tb2.user_id

原先的是 循环次数=user数据量 * level数据量

变成了 循环次数=user数据量 * level索引高度

3、Block Nested-Loop Join

缓存块嵌套循环连接其优化思路是减少外层表的循环次数;

Block Nested-Loop Join 通过一次性缓存多条数据，把参与查询的列缓存到join buffer 里，,然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了外层循环的次数;

当我们不使用Index Nested-Loop Join的时候，默认使用的是Block Nested-Loop Join。

解释：使用缓存区一次缓存多少数据作为一次的匹配（缓存区中的数据库一次匹配完毕）。

匹配次数 变成 总匹配次数= 缓存次数 * 表2数据量。

那么缓存块缓存的数据量越多，则最后循环的次数越少。

四、数据库优化方案

一般来说，咱们优化有很多种的方案或者方向，现在总结一下：

1. 业务优化
2. 表结构优化（数据库优化）
3. 表索引优化（数据库优化）
4. SQL优化

1、业务优化

一把来说，跟业务相关的优化，都是业务优化。业务上的问题一般需要产品修改需求。

主要优化方面：减少不必要的业务需求，优化设计方案。

2、表结构优化

• 选择合适的字段类型
• 精确字段长度
• 适当利用冗余字段（减少连表查询、减少计算）;
• 把字段定义为NOT NULL并且提供默认值
• 禁止存储大文件或者大照片
• 禁止使用外键

3、表索引优化

• 在合适的字段上建索引（离散性不高、查询频率不高、更新频率高的字段不适合建索引）；
• 组合索引区分度高的字段放在前面（最左匹配原则）
• 单表索引不能过多(在5个以内)

注意：什么是离散型？

离散性指的就是数据重复（相似）情况，数据字段的数据重复情况越少，离散型高；重复情况越多，离散性越低。

例如：性别，离散性基本上是最低的，因为只有两种情况，以后可能出现第三种情况。

4、SQL优化

SQL优化的情况很多，我这是简单介绍几种优化方案：

• 只取需要的列，避免SELECT *
• 禁止使用属性隐式转换
• 禁止在WHERE条件的属性上使用函数或者表达式
• 避免使用or查询
• 联表查询用小结果集驱动大结果集
• 禁止负向查询(NOT、!=、<>、!<、!>、NOT IN、NOT LIKE)，以及%开头的模糊查询

注意：为什么不提倡 使用 *

• 不能使用索引（查询索引字段）
• 数据量大，网络传输会变慢（查询少量字段的时候）
• 需要进行SQL解析
• join buffer缓存的数据量会减少（查询少量字段的时候）
• 出现覆盖索引情况

参考网站：

mysql的binlog文件：https://www.cnblogs.com/martinzhang/p/3454358.html