mysql（1）-基本结构，索引，日志

1. mysql 基本架构

先来个图看一下。大概mysql的工作架构图如下，客户端发送请求到mysql服务端，服务端进行一系列的处理。

接下来聊一下server端的各个部分。

• 客户端：指jdbc连接，命令行，可视化工具等

• 连接器
  a. 作用：管理连接，进行权限验证，用户名密码认证，查询权限等
  b. 利用show processlist 命令查看当前连接，如果长时间无动静会自动断开，可以通过wait_timeout控制，默认是8个小时
  c. 连接分两种
  长连接：推荐使用长连接，但是也要周期性的断开。
  短连接

• 分析器
  a. 作用： 词法分析，语法分析
  b. 词法分析，就是识别输入的字符串 。语法分析就是分析语法是否正确，如果不正确会报错：
  you have an error inyour sql synta。
  c. 抽象语法树AST，会根据一些关键字，where from 进行切分，验证表，列等是否存在。

• 优化器
  a. 作用：执行计划，选择索引
  b. 在真正执行sql语句执行会进行优化，当有多个索引时，决定使用那个索引；当要关联多个表时，决定表的连接顺序等。
  c. 不同的执行方式对SQL语句的执行效率影响大
  RBO 基于规则的优化
  CBO 基于成本的优化

• 执行器
  a. 操作引擎，返回结果。

• 数据缓存
  当执行查询语句的时候，会先去查询缓存中查看结果，之前执行 过的sql语句及其结果可能以key-value的形式存储在缓存中，如 果能找到则直接返回，如果找不到，就继续执行后续的阶段。
  但是，不推荐使用查询缓存: 查询缓存的失效比较频繁，只要表更新，缓存就会清空 ；缓存对应新更新的数据命中率比较低。
  我们知道这个思想就行了。

• 存储引擎
  存储数据，提供读写接口。
  不同的存储引擎的存储方式是不同的，数据文件和索引文件的存放位置也是不同的。
  i. 聚簇索引：数据和索引放在一起： innodb
  .frm文件存放的是表结构
  .ibd文件存放的是数据和索引

mysql的innodb存储引擎默认情况下会把所有的数据文件放到表空间中，不会为每个单独的表保存一份数据文件，如果需要每个表保存一份数据文件，设置如下属性
set global innodb_file_per_table = on;

ii. 非聚簇索引：数据和索引是单独的文件存储：mylsam
.frm 文件存放表结构
.myi 存放索引数据
.myd 存放实际数据

2. mysql 索引

• 索引是帮助 MySQL 高效获取数据的数据结构
• 索引是存储在索引结构中的
• 索引文件的存储形式和存储引擎有关

2.1 索引的结构

有多种数据结构能够进行索引的存储，比如hash表、二叉树、B树、B+树等。那么为什么最终选取来B+树数据结构呢。

2.1.1 hash表

2.1.1.1 哈希（hash）函数–又叫散列函数

1. hash表本质上可以认为是数组的形式，称为hash数组
2. hash存储主要是将数据散列的到hash表中3. 最简单的散列方式就是%求余，余数是那个数组的索引，就存储到哪个索引上，该方法叫除留余数法。散列函数，也称为hash函数。

直接定制法：
数字分析法：
平方取中法：key=1234 1234^2=1522756 取227作hash地址
折叠法：key=123456789，将数据分开相加，分为123=6、456=15、789=24，最终得61524，然后取末尾三位 524，存储到hash数组524的位置。
除留余数法：取余法4.

2.1.1.2 哈希冲突

我们取余法进行举例，假设hash表长度就是为8，那么数据8 和数据16求余结果都是0，我们不能打8和16都存在0的位置上，这就是hash冲突。无论使用什么算法都会产生hash冲突。

hash表长度只有8么，当然不是，你可以设置的很长，那么hash表到底应该是设置多长呢。这个不能一概而论，必须根据实际的情况来看。

2.1.1.3 如何设计一个hash表

如何设计一个hash表需要注意三个方面，1. 怎样控制hash表的长度 2. 怎样设计hash函数 3. 怎样解决hash冲突

1. hash表长度

哈希表的长度一般是定长的，在实际中，我们应该估算一下我们到底需要存储多大量的数据，设计的较合理一点，减少哈希表的扩容。
设计过大和过小都不可取，过大会浪费空间，过小hash冲突频繁。所以必须比较合理的设计，要做到尽可能地减小哈希冲突，并且也要尽可能地不浪费空间，选择合适的哈希表的大小是提升哈希表性能的关键。

负载因子：
这里引入一个概念就是表的装填因子（负载因子）

装填因子a=实际存储数据量/哈希表长

a越大，表示数据存储量大，那么越容易发生hash冲突。而且，插入效率变低，为什么会变低呢，如果长度为8，已经插入7个了，最后一个可能需要遍历整个表成才能插入。

a越小，可能会造成空间的浪费。比如a=0.1，哈希表总长为100，那么能够存储的数据只有10，严重的浪费了空间。
所以a取0.7～0.8比较合适，也就是哈希表长100，存入数据70～80正好。
有人问我们不能扩表么，当然能，我们在存入数据时，会实时监控这个比例，当a>0.7 0.8时，就会自动扩大数组，一般是2倍。

总结：如何设计hash表

1. 确保哈希表长度是一个素数，这样会产生最分散的余数，尽可能减少哈希冲突
2. 设计好哈希表装填因子，一般控制在0.7-0.8
3. 确认我们的数据规模，如果确认了数据规模，可以将数据规模除以装填因子，根据这个结果来寻找一个可行的哈希表大小
4. 当数据规模可能会动态变化，不确定的时候，这个时候我们也需要能够根据数据规模的变化来动态给我们的哈希表扩容，所以一开始需要自己确定一个哈希表的大小作为基数，然后在此基础上达到装填因子规模时对哈希表进行扩容。

2. hash函数设计

• hash函数不要太复杂，考虑时间
• hash函数足够的散列。
  -hash函数尽量减少冲突

3. hash冲突解决

• 开放定制法
  假设还是上述例子，8和16产生了hash冲突，即 8%8=0。16%8=0，在hash数组0位置冲突了

1）线性探测再散列
（16+di）%8 ，di=ci；例如di=21，就是（16+1）%8=1，如果1位置还是冲突，那么di = 2*2，就是（16+4）%8=4 ,di线性增加。直到不冲突为止。
2）平方探测再散列
di=i的2次方，第一次（16+1）%8. 第二次 （16+4）%8。第三次 （16+9）%8
3）随机探测在散列（双探测再散列）
di=随机数
（16+随机数）%8

• 链地址法

这中方式也是hashMap的解决hash碰撞的方式

蓝色的时哈希数组，而数组中的每个元素有作为链表的头节点，用来解决冲突，当1位置已经有数据时，当产生hash冲突，就将产生冲突的数据放在链表中。这就是hashMap的实现原理

• 公共溢出区法
  建立一个特殊存储空间，专门存放冲突的数据。此种方法适用于数据和冲突较少的情况。
• 再散列法
  准备若干个hash函数，如果使用第一个hash函数发生了冲突，就使用第二个hash函数，第二个也冲突，使用第三个……

2.1.1.4 hash表查找数据

以上都是说怎么存数据，那么取数据的时候怎么取呢？

给定一个key，通过hash算法H(key)算出存储位置index。
array(index)=key,则表示查找成功。
array(index)=null,表示hash表中无此数据
array(index)!=null 也 !=key，而是其他数据，就说明之前产生过hash冲突，所以就使用解决hash冲突的方法去查找index，直到array(index)=key或者array(index)=null 为止。

2.1.1.5 如果索引使用hash表

1. 存储，hash表可以完成索引的存储，但是每次需要计算hash值，计算出存储的位置，然后存储
2. 但是在查找的时候不适合。
   只适合精确查找，并不适合范围查找，所有索引都是散列的，无须的。范围查找不方便，而在实际的企业生产中范围查找更多。所以查找会很慢。
   而且hash表需要读取到内存中，很耗费资源。
   所以不合适

2.1.2 树

2.1.2.1 二叉树

1. 二叉树以及它的变种不能够支撑索引的存储，主要有两方面：
   第一. 树的深度无法控制，随着数据的增多，使用二叉树树的深度会越来越长。
   第二，树的插入效率比较低
2. 如果二叉树不满足，考虑一下多叉树- B树。

2.1.2.2 B树

B-树就是B树
英文名字叫做B-tree，中间的短线是英文连接符，只是翻译的时候将短线翻译成了减号。
全称Balance-tree(平衡多路查找树)，平衡的意思是左边和右边分布均匀。多路的意思是相对于二叉树而言的，二叉树就是二路查找树，查找时只有两条路，而B-tree有多条路，即父节点有多个子节点。

使用B树来存储索引

1. 看图
   
   是不是看不懂，没有关系，我们解释一下。
2. 假设，现在有一张表，t_user用户表，字段有 user_id用户id（主键） user_name用户名称 user_age用户年龄
   现在存入来100条数据，主键是1…100。
3. 主键也是索引，此时的索引结构如果是B树。
   这100主键索引会一B树的形式存储，以图说明：
   将100个索引拆分， 磁盘块1 存储p1,16,p2,34,p3。其中p1代表0～15,指向磁盘2，而在磁盘2中继续拆分，分为p1,7p2,11,p3,此时的p1又指向磁盘5，而磁盘5中存储的是主键为1，2，3，4，5，6的值（图中并没画全）
4. 当查询的时候，将磁盘读取内存中，就能通过一层一层遍历进行查找，查询速度就会增快。
   又个基础知识，cup在读取磁盘的时候，不是一下子将所有的磁盘内容读取到内存中的，而是按页去读，一页是4k，所以一次只会对取4k的数据，

例如要查找id=4的数据。cup先读取磁盘1，发现4在p1区，二p1指向磁盘2，cup在读取磁盘2，读取后发现在磁盘2的p1区，磁盘2的p1区指向磁盘5，然后读取磁盘5，找到id=4的数据，返回。
如果要查找id=16，cup读取磁盘1，发现id=16就在磁盘1，直接将数据返回。

5. 但是有两个问题
   a. 磁盘块中是数据和索引一起存储的，假设一条数据为1k，那么cup在读取磁盘的时候，一次只能读取到的索引数就很少， 因为读取的4k里，大部分都被数据占了，那么这就导致我们可能要多读几次，增加了io频率，降低了查询的效率。
   b. 由于磁盘中大部分空间是被数据占着，那么索引就存储的少来，就会导致树的深度增加。也会降低查找效率
6. 所以在B树不能满足索引存储，所以在Bshu的基础上做了升级，B+树。

2.1.2.3 B+树

B+树是在B树的基础上做了升级，升级的地方在哪里呢，那就是所有的非叶子节点都不在存储数据，只有叶子节点才会存储数据。

1. 就是在拆分索引进行存储的时候，不在非叶子节点中存储数据，而是将这个索引依次拆分下去，直到叶子节点。
2. B+Tree每个节点可以包含更多的节点，这个做的原因有两个
   第一个原因是为了降低树的高度
   第二个原因是将数据范围变为多个区间，区间越多，数据检索越快2、非叶子节点存储key，叶子节点存储key和数据
3. 叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高
4. 注意：在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
5. 以上都是主键索引存储和查找的方式，一个主键对应一条数据或者记录。如果我们建立一个普通的索引是怎么存储的，又是怎么找到的呢？

普通索引

主键索引user_id 的存储结构如下：

如果我们给user_name设置为索引，存储结构为:

此时叶子节点存储的就不是整个数据了，而是满足条件的数据的id。这里是user_id。

回表

在使用普通索引进行查询时，会通过普通索引查找到满足该索引的主键，然后根据查找的重新遍历主键的索引树，然后找到真正的数据。
这个过程就叫做回表。

所以使用普通索引会进行两次索引树的查找，第一次，先找满足主键，第二次根据这些主键找数据。这个过程叫回表。

** 那么有人问了，如果表没有设置主键怎么办？**
不用怕，如果没有设置主键，1. 先去找唯一索引，如果连唯一索引都没有 2 mysql会自动生成row_id作为主键。

总结：所以索引的存储结构是B+树

2.1.3 存储引擎的影响

以上所讲的都是Innodb的存储引擎，索引和数据是放在同一个文件的。所以mysql InnoDB–B+Tree,叶子节点直接放置数据。
而mysam的存储引擎，索引和数据是分开两个文件存储的，所以mysql MyISAM的–B+Tree，叶子节点存放的是数据的地址。

2.3 索引的分类

2.3.1 索引的分类

mysql的索引分5类：主键索引，唯一索引，普通索引，全文索引，联合索引。通过给字段添加索引来加快数据的读取效率，提供项目的并发能力和抗压能力。

• 主键索引，主键是一种特殊的唯一索引，指定primary key，非空，每个表只能一个主键，主键唯一指向一条记录。
• 唯一索引，唯一索引的列数据不能重复，必须唯一，列可为空。
• 普通索引，基础的索引，不必唯一，列可为空，（索引覆盖）
• 全文索引，全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建
• 联合索引，多列值可以组合在一起作为索引，专门用于组合搜索（最左匹配原则）

2.3.2 索引的几个名词

1. 回表
   先遍历普通索引的索引树找到id，然后根据找到的id遍历主键索引树，找到对应的数据，遍历两次索引树，这个过程叫做回表。
   select * from t_user where user_name=‘张三’；先遍历user_name的索引树，找到user_id，然后根据user_id遍历主键的索引树找到数据。

2. 索引覆盖
   将回表的语句 select * from t_user where user_name=‘张三’ 修改为 select user_id from t_user where user_name=‘张三’ ;
   此时只需要遍历一次user_name的索引就能够获取到user_id,不需要第二步，这个就是索引覆盖。
   在生产中推荐使用索引覆盖。

3. 最左匹配
   这个主要针对于组合索引。
   eg： 给t_user设置一个组合索引 ,user_name,user_age做为组合索引。
   select * from t_user where user_name=’’ and user_age=’’; select * from t_user where user_name=’’ 这个就能使用组合索引。
   而select * from t_user where user_age=’’ and user_name=’’; select * from t_user where user_age=’’; 就不能使用索引。
   所以最左匹配强调组合索引的顺序，只有按照组合索引的第一个列开始的才能使用到组合索引。所有以user_name开头的条件才能使用到索引。这就是最左匹配。

4. 索引下推
   也是针对联合索引的，
   eg： select * from t_user where user_name=‘张三’ and user_age=‘10’;

理论上是先遍历user_name的索引树，然后根据结果再去遍历user_age的索引树。
索引下推就是在遍历user_name的索引树的时候，就会讲user_age的条件加上，在遍历完user_name的索引树后，直接遍历主键索引树返回数据既可以了。

2.4 索引的维护

以上我们知道了索引的存储结构，但是如果我们增加数据或者删除数据都会对索引的结构有影响。
我们以主键索引树来树：

1. 当增加比较大的索引来说，我们直接向后增加就可以了
2. 但是如果增加中间的索引，就需要在逻辑上移动后面的位置，空出位置让该索引插入。如果增加索引后磁盘块满了（页满了），那就需要申请单独的页，然后移动一部分数据过去，这就是页分裂。而此时，移动的那个页就不满了，这样就会造成空间的浪费，进而造成io的次数增多影响效率。
3. 同理删除数据，会使的每个页的数据不满，这就设计到页合并。
4. 所以，尽量使用自增主键作为索引，这样可以在插入的时候保证是索引是一只增大的，避免频繁的移动。

2.5 不同存储引擎索引的比

	mylSam	innodb
索引类型	聚簇索引	非聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是（5.6版本支持）
适合操作的类型	大量select	大量insert delete update

3. mysql 日志

3.1 mysql日志介绍

mysql 日志分为三种，binlog , undo ,redo
binlog：二进制日志，归档日志，是server端日志。
redo：存储引擎日志，存储引擎为innodb，前滚日志，用于新数据存储
undo: 存储引擎日志，存储引擎为innodb，回滚日志，用于旧数据备份。

事务的四个特点：acid
a：原子性
c：一致性
i：隔离性
d：持久性

wal，预写日志。write ahead log。

3.2 mysql数据更新机制

mysql进行数据更新的机制是：innodb引擎会先将记录写到redo log中， 并更新内存，此时更新就算是完成了，同时innodb引擎会在合适 的时机将记录操作到磁盘中。

总结：
如果需要更新一个数据到数据库需要经过一下几个步骤

1. 记录redo log日志文件
2. 更新内存中的数据，此时就算是更新成功了。
3. 在innodb引擎方便的时候，才将数据同步到数据文件中。即使此时数据库挂了，由于已经将redo log日志记录完成了，所以在数据库重启的时候，会根据redo log文件，将数据更新至数据库中（磁盘数据文件中），能够保证数据不丢失。

3.3 redo log日志

redo log日志的记录方式：redo log是怎么记录的磁盘中的日志文件中呢。
在操作系统中，分为内核空间和用户空间，我们能够操作的就是用户空间，而我们从磁盘中读取数据其实是经过了内核然后才能到用户空间让我们操作的。

所以，比如说一次更新，肯定是在一个进程中的内存空间有个日志缓存（log buffer）区进行日志书写，然后经过OS内核（os buffer），然后os写入磁盘的日志文件中。

而这个过程有三种：：

第一种：当执行commit语句后，将日志写到log buffer，每1s写入到os buffer中（相当于有1s的间隔时间，在这1s时间可能会后多个commit提交），然后同步到磁盘redo log文件中。好多次的commit，一次批量写入日志文件。
第二种：每提交一次commit，就会写一次os buffer，并同步到磁盘文件中。一次commit记一次日志文件。
第三种：每提交一次commit，就会写一次os buffer，但是需要1s后才写入日志文件中，这里也有1s的间隔。

相比较而言：第二种更安全，第一和第三，都有1s的间隔，如果在个期间宕机就会造成内存中数据丢失。但是第一和第三是批量操作，速度更快。需要谨慎选择。

说白了，redo log日志存储的是即将要变成的新数据。

redo log日志文件是固定大小的，就是大小是有限的，当达到上限后就会将之前的日志删除，空出空间，让新日志插入，是可以不断的循环写的

3.4 undo log 日志

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中， 还用Undo Log来实现多版本并发控制(简称:MVCC)
Undo log是为了实现数据回滚的，在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方 称为Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之 前的状态。
undo log是逻辑日志，可以理解为:
当delete一条记录时，undo log中会记录一条对应的insert记录
当insert一条记录时，undo log中会记录一条对应的delete记录
当update一条记录时，它记录一条对应相反的update记录

3.5 binlog 日志

binlog日志是server端的日志文件，主要做mysql功能层面的事情。
binglog日志是逻辑日志，记录的是这个语句的原始逻辑。
所以生产中经常会备份binlog日志，可以周期性的进行备份。如果要恢复数据，可以找到最新的备份，将binlog日志取出来，重新恢复数据。
binlog和redo 日志的区别别

1. redo是innodb独有的，binlog是所有引擎都可以使用的
2. redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻
   辑日志，记录的是这个语句的原始逻辑
3. redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前
   的日志信息

3.6 数据更新流程

重点：为什么redo 日志这里分成两步了呢，一个是prepare状态，一个commit状态
这是为了保证redo 日志 和binlog日志的一致性。意味着redo log记录成功，那么binlog 日志也记录成功。
分析：
如果先完成redo log，后完成binlog，那么当中间过程宕机后，redo log有新数据，但是binlong没有新数据，在恢复数据时会缺失数据。
同理如果先完成bin log，后完成redo log，那么当中间过程宕机后，binlog有新数据，但是redo log没有新数据,那么当使用binlog恢复数据后就会多出一条数据，和原数据库数据不一致了。
综上：所以redo拆分成两段，prepare和commit，将binlog放在中间，保证一致性。