关于java:myql相关

1.bin Log: 数据恢复主从复制

MySQL Server 层也有一个日志文件，叫做 binlog，它能够被所有的存储引擎应用。

bin log 以事件的模式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），能够用来做主从复制和数据恢复。凌晨 1 点钟全量备份   程序员  1 点 --- 9 点钟      10 点钟    数据文件全副删掉了   复原 1 点钟   复原到 9 点钟

数据恢复：区别于 Redo Log 的解体复原，数据恢复是基于业务数据的，比方删库跑路，而解体复原是断电重启的

磁盘读写，并不是按需读取，而是按页读取，一次至多读一页数据（个别是 4K）然而 Mysql 的数据页是 16K，如果将来要读取的数据就在页中，就可能省去后续的磁盘 IO，提高效率。

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，防止每次拜访都进行磁盘 IO，起到减速拜访的作用。

冷热分区的 LRU 策略

LRU 链表会被拆分成为两局部，一部分为热数据，一部分为冷数据。冷数据占比 3/8，热数据 5 /8。

数据页第一次加载进来，放在 LRU 链表的什么中央？

放在冷数据区域的头部

冷数据区域的缓存页什么时候放入热数据区域？

MySQL 设定了一个规定，在 innodb_old_blocks_time 参数中，默认值为 1000，也就是 1000 毫秒。

意味着，只有把数据页加载进缓存里，在通过 1s 之后再次对此缓存页进行拜访才会将缓存页放到 LRU 链表热数据区域的头部。

为什么是 1 秒？

因为通过预读机制和全表扫描加载进来的数据页通常是 1 秒内就加载了很多，而后对他们拜访一下，这些都是 1 秒内实现，他们会寄存在冷数据区域期待刷盘清空，基本上不太会有机会放入到热数据区域，除非在 1 秒后还有人拜访，阐明后续可能还会有人拜访，才会放入热数据区域的头部。

解体复原基本保障零碎主动做的

InnoDB 引入了一个日志文件，叫做 redo log（重做日志），咱们把所有对内存数据的批改操作写入日志文件，如果服务器出问题了，咱们就从这个日志文件外面读取数据，复原数据——用它来实现事务的持久性。

redo log 有什么特点？

1. 记录批改后的值，属于物理日志

2.redo log 的大小是固定的，后面的内容会被笼罩，所以不能用于数据回滚 / 数据恢复。

3.redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

A 500 B 800 C 1000

小表驱动大表

MySQL 外部自带了一个缓存模块。默认是敞开的。次要是因为 MySQL 自带的缓存的利用场景无限，第一个是它要求 SQL 语句必须截然不同。第二个是表外面任何一条数据发生变化的时候，这张表所有缓存都会生效。

在 MySQL 5.8 中，查问缓存曾经被移除了。

下一步咱们要做什么呢？

如果轻易执行一个字符串 fkdljasklf，服务器报了一个 1064 的错：

[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘fkdljasklf’ at line 1

服务器是怎么晓得我输出的内容是谬误的？

或者，当我输出了一个语法完全正确的 SQL，然而表名不存在，它是怎么发现的？

这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。

这一步次要做的事件是对 SQL 语句进行词法和语法分析和语义的解析。

词法剖析就是把一个残缺的 SQL 语句打碎成一个个的单词。

比方一个简略的 SQL 语句：

select name from user where id = 1;

它会打碎成 8 个符号，记录每个符号是什么类型，从哪里开始到哪里完结。

第二步就是语法分析，语法分析会对 SQL 做一些语法查看，比方单引号有没有闭合，而后依据 MySQL

定义的语法规定，依据 SQL 语句生成一个数据结构。这个数据结构咱们把它叫做解析树。

如果表名谬误，会在预处理器解决时报错。

它会查看生成的解析树，解决解析器无奈解析的语义。比方，它会检查表和列名是否存在，查看名字和别名，保障没有歧义。

问题：一条 SQL 语句是不是只有一种执行形式？或者说数据库最终执行的 SQL 是不是就是咱们发送的 SQL？

这个答案是否定的。一条 SQL 语句是能够有很多种执行形式的。然而如果有这么多种执行形式，这些执行形式怎么失去的？最终抉择哪一种去执行？依据什么判断规范去抉择？

这个就是 MySQL 的查问优化器的模块（Optimizer）。

查问优化器的目标就是依据解析树生成不同的 执行打算，而后抉择一种最优的执行打算，MySQL 外面应用的是基于开销（cost）的优化器，那种执行打算开销最小，就用哪种。

应用如下命令查看查问的开销：show status like 'Last_query_cost'; 
    -- 代表须要随机读取几个 4K 的数据页能力实现查找。

如果咱们想晓得优化器是怎么工作的，它生成了几种执行打算，每种执行打算的 cost 是多少，应该怎么做？

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

首先咱们要启用优化器的追踪（默认是敞开的）：

SHOW VARIABLES LIKE 'optimizer_trace'; 

set optimizer_trace="enabled=on";

留神开启这开关是会耗费性能的，因为它要把优化剖析的后果写到表外面，所以不要轻易开启，或者查看完之后敞开它（改成 off）。

接着咱们执行一个 SQL 语句，优化器会生成执行打算：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

这个时候优化器剖析的过程曾经记录到零碎表外面了，咱们能够查问：

select * from information_schema.optimizer_trace\G

expanded_query 是优化后的 SQL 语句。

considered_execution_plans 外面列出了所有的执行打算。

记得关掉它：

        set optimizer_trace="enabled=off"; 

•       SHOW VARIABLES LIKE 'optimizer_trace';

MySQL 的优化器能解决哪些优化类型呢？

比方：

1、当咱们对多张表进行关联查问的时候，以哪个表的数据作为基准表。2、select * from user where a=1 and b=2 and c=3，如果 c=3 的后果有 100 条，b=2 的后果有 200 条，a=1 的后果有 300 条，你感觉会先执行哪个过滤？3、如果条件外面存在一些恒等或者恒不等的等式，是不是能够移除。4、查问数据，是不是能间接从索引外面取到值。5、count()、min()、max()，比方是不是能从索引外面间接取到值。6、其余。

优化器最终会把解析树变成一个查问执行打算，查问执行打算是一个数据结构。

当然，这个执行打算是不是肯定是最优的执行打算呢？不肯定，因为 MySQL 也有可能笼罩不到所有的执行打算。

MySQL 提供了一个执行打算的工具。咱们在 SQL 语句后面加上 EXPLAIN，就能够看到执行打算的信息。

EXPLAIN select name from user where id=1;

B+ 树能显著缩小 IO 次数，提高效率
B+ 树的查问效率更加稳固，因为数据放在叶子节点
B+ 树能进步范畴查问的效率，因为叶子节点指向下一个叶子节点
B+ 树采取程序读

1. 盘片

2. 磁头

3. 主轴

4. 集成电路板

磁盘是如何实现单次 IO 的

单次的 IO 工夫 = 寻道工夫 + 旋转提早 + 传送工夫

咱们容易有一个误区，就是在常常应用的查问条件上都建设索引，索引越多越好，那到底是不是这样呢？

第一个叫做列的离散度，咱们先来看一下列的离散度的公式：

不同值得数量：总行数越靠近 1 那么离散度越高，越靠近 0，离散度越低

count(distinct(column_name)) : count(*)，列的全副不同值和所有数据行的比例。数据行数雷同的状况下，分子越大，列的离散度就越高。

后面咱们说的都是针对单列创立的索引，但有的时候咱们的多条件查问的时候，也会建设联结索引，举例：查问问题的时候必须同时输出身份证和考号。

联结索引在 B+Tree 中是复合的数据结构，它是依照从左到右的程序来建设搜寻树的（name 在右边，phone 在左边）。

从这张图能够看进去，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

这个时候咱们应用 where name= ‘jim’ and phone = ‘136xx ‘ 去查问数据的时候，B+Tree 会优先比拟 name 来确定下一步应该搜寻的方向，往左还是往右。如果 name 雷同的时候再比拟 phone。然而如果查问条件没有 name，就不晓得第一步应该查哪个节点，因为建设搜寻树的时候 name 是第一个比拟因子，所以用不到索引。

有一天咱们的 DBA 找到我，说咱们的我的项目外面有两个查问很慢，依照咱们的想法，一个查问创立一个索引，所以咱们针对这两条 SQL 创立了两个索引，这种做法感觉正确吗？

CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phone on user_innodb(name,phone);

当咱们创立一个联结索引的时候，依照最左匹配准则，用右边的字段 name 去查问的时候，也能用到索引，所以第一个索引齐全没必要。

相当于建设了两个联结索引(name),(name,phone)。

如果咱们创立三个字段的索引 index(a,b,c)，相当于创立三个索引：index(a) 

index(a,b) 

index(a,b,c) 

用 where b=? 和 where b=? and c=? 是不能应用到索引的。这里就是 MySQL 外面联结索引的最左匹配准则。

什么叫回表：不须要回表叫笼罩索引

汇集索引：id

二级索引：name

非主键索引，咱们先通过索引找到主键索引的键值，再通过主键值查出索引外面没

有的数据，它比基于主键索引的查问多扫描了一棵索引树，这个过程就叫回表。

在辅助索引外面，不论是单列索引还是联结索引，如果 select 的数据列只用从索引中就可能获得，不用从数据区中读取，这时候应用的索引就叫做笼罩索引，这样就防止了回表。

Extra 外面值为“Using index”代表应用了笼罩索引。

因为索引对于改善查问性能的作用是微小的，所以咱们的指标是尽量应用索引。

1、在用于 where 判断 order 排序和 join 的（on）字段上创立索引

2、索引的个数不要过多。

——节约空间，更新变慢。

3、区分度低的字段，例如性别，不要建索引。

——离散度太低，导致扫描行数过多。

4、频繁更新的值，不要作为主键或者索引。

——页决裂

5、随机无序的值，不倡议作为主键索引，例如身份证、UUID。

——无序，决裂

6、创立复合索引，而不是批改单列索引

1、索引列上应用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式

2、字符串不加引号，呈现隐式转换

3、like 条件中后面带 %

4、负向查问 NOT LIKE 不能

## MyiSAM 与 Innodb

myi index

myd data

咱们表内的数据是依照汇集索引的顺序排列的

本文由博客一文多发平台 OpenWrite 公布！

关于java:myql相关

1.Bin log 是什么，有什么用？（数据库被人干掉了怎么办？）

什么是预读？

什么是 Buffer Pool？性能优化的一个点

Buffer Pool 的内存淘汰策略

Redo Log 跟 Buffer Pool 的关系

3.Mysql 的体系结构是什么样子的（一条查问语句它到底是怎么执行的？）

查问缓存(Query Cache)

语法解析和预处理(Parser & Preprocessor)

词法解析

语法解析

预处理器（Preprocessor）

查问优化（Query Optimizer）与查问执行打算

什么优化器？

优化器是怎么失去执行打算的？

优化器能够做什么？

优化器失去的后果

4. 一条更新语句要经验那些流程

5. 为什么 Mysql 要应用 B + 树做为索引 B 树

6. 磁盘的程序读以及随机读有什么区别？（这个在面大厂的时候有可能会让你聊，问法有很多种）

7.索引应用准则（索引怎么应用才正当）

列的离散（sàn）度

联结索引最左匹配

如何创立联结索引

笼罩索引与回表

8. 索引的创立与应用

在什么字段上索引？

什么时候索引生效？

Hash 索引