第4节. mysql架构和存储引擎详解

权限类别

管理、程序、数据库、表、字段，这些都可以设置权限

管理类：create、shutdown、show databases、

结尾带上：with grant option就是被授权的用户拿到权限可以转赠给别人。

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

PS：授权，只能select col1只能查询col1这个字段，只能对col1和col2进行插入赋值，在mydb.mytbl这个库的这个表上，针对‘someuser’@'somehost'这个用户。

就OK啦；所以现在的新版本依然可以用grant去授权的时候直接创建用户。

取消授权REVOKE

上图就是ALL里面单单去掉了delete权限。

查看当前用户权限

一般授权可能需要结合flush privileges使之生效，有时候也不用补。当然忘记密码的步骤里需要改密码cli前flush一次。而授权这里通常是授权cli后补一个flush。

单单授权cli

就是

第一步，创建用户

create user user2@'127.0.0.1' identified by 'centos';

PS：identified by 'centos' 不管在create user还是在grant 授权里出现都是设定密码了。

第二步，授权

grant all on hellodb.* to 'user2'@'%';

mysql的架构

1、connector：连接器也就是API：

理解：各种API咯，JDBC就是jAVA调库的API，此外还有python和其他语言调用mysql的API。

2、connection pool：连接过来后要验证身份、分配线程、等等，这些处理模块就是connection pool连接池：

authentication，就是验证是不是合法的用户，验证完了以后，系统会分配一个为你服务的线程。

thread ，就是mysql是单进程多线程的服务。用户上来，验证过了后，分配的就是一个线程。

reuse，用户使用线程查询完后，断开了，线程不会销毁。系统会清理该线程的用户信息，然后重新使用，叫reuse重新使用。

thread，其实有一个pool，池子。从池子里取线程分配。如果池子里总共有1000个线程，来了1w个用户连接，此时就超量了，就有一个连接限制connection limit。这个默认连接数是100，我以前批量并发获取400台SW信息并发写道mysql里就遇到了这个问题，最后查出来就是连接数限制要调大些就好了。

生产中这个连接数的值肯定要调大的。

还有check Memory和cache都是在连接池子connection pool里

cache下文介绍

3、SQL接口

用户通过connection pool拿到线程服务了，就可以通过线程向mysql发送指令了。而client发送过去的cli，server就会检查cli的语法，检查语法就是靠SQL Interface接口来完成的。

SQL语法检查通过了以后就进入了下一阶段

4、Parser：解析器

把用户发来的cli解释成SQL自己身能理解的内容。

在做解析的时候，同时要做权限检查，应该是给到具体的权限吧。然后还要做优化

5、Optimizer,优化

DB在资源访问的时候，访问方式不是单一的，比如：用索引和不用索引。

到底用索引好还是不用索引号，就需要一个优化。

根据这个优化，最终判断出来这个最佳的路径，来进行查询。

6、Caches和Buffers

如果查询过了，则可以利用缓存。

如果缓存里也没有，就需要去磁盘找了那些数据了。

7、数据是放在文件系统上的db格式

数据库底层的数据，除了数据库本身--db文件，还有一些日志也很重要。日志是一个大的话题，后面会讲各种各样的日志。正常软件就一个日志，而数据库里的日志就很多：redo、undo、Binary、error、查询、慢查询。

这些db文件要对其进行访问，就需要存储引擎进行支持

8、存储引擎

存储引擎负责和磁盘打交道

数据库通过存储引擎连接到磁盘文件系统上来访问磁盘的文件里的数据。

历史上有上百种引擎，目前都是使用innoDB了。

9、数据库其他功能

备份、还原、集群等

存储引擎

历史上出现过上百个存储引擎，现在只需要了解MyISAM和InnoDB了，其实MyISAM都不用了已经。

mysql5.5之前的版本默认用的MyISAM。

1、存储数据大小，MyISAM是256TB，要远远大于InnoDB引擎的支持，但是没有用，因为mysql的整体定位就是中小型数据库。达到TB级别，就要考虑分库了，支持不了这么大的访问量了。

2、Transaction事务，MyISAM不支持，事务里有很多重要特性：稍后介绍CIDB特性，还有很重要的特性-原子性。

原子性：一个事务里面由很多小步骤组成，要么这些所有的小步骤都完成，要么都不做，不能只完成一部分，必须作为一个整体，这就是原子性。

举例：转账，从A转1W给B，A扣除1W和B增加1W，这两个事务就是一个原子了吧，这才是一个完成的操作，如果用MyISAM这种不支持事务的引擎，就有可能这边扣除1W，然后突然停电了，那头钱也没收到，钱就丢了。而InnoDB不会出现这种情况，如果同样出现上面的A转出钱突然的停掉，当A扣除1W的时候，就会记录在事务日志里了，然后那边加了1W也会记录到事务日志，由于一头扣除1W和一头增加1W是一个完整的事务。结果你只是扣了做了一半，结果数据库突然停掉或宕机了，等你后面启动的时候，就会检查发现里面只有扣钱的一半动作，这就是一个不完整的事务，此时就会果断采用一个undo撤销操作--扣除扣钱的动作。

所以如果DB不支持事务，就极度不安全。

3、locking granuarity锁的颗粒度

MyISAM的锁表，会影响并发

InnoDB是锁行也就是锁一条记录的，可以多人同时修改不同的行。并发好。

4、MVCC(多版本并发控制机制)，并发用

InnoDB支持MVCC

数据中的表不是看到的那么简单，你表里由2个字段，比如id和value，innodb引擎的数据就会给你自动添加两列-字段，分别是create和delete

这两个字段分别存入的是，insert 一行-记录的时候，当时创建记录的时间点记录下来，其实不是时间点，而是事务ID，而事务ID只会递增不会减的，就跟时间一样只会不断增加，所以你也可以理解成时间。

如果第二条记录，删掉了，那么delete字段里就会记录删除的时间点，也就是事务ID。

假设一个人1200的时候(事务ID具有时间特性的，可以理解成时间点的)执行了select查询

问1，此人能看到上表的哪些记录？

1000的可以看到，其他大于1200的事务ID的记录都看不到了

这个人是1200的时候开始操作的，然后持续到了3000才结束。

他看到的是1200以前的记录。

问2，下图用户1200的时候select能否看到第一行记录？

答：看不到了，因为1100的时候已经删掉了

以上就是MVCC机制👆

所以在有MVCC机制的数据库中，所谓的删除记录并没有真正的删除。

再问：此时t2这个查询事务能否看到id1的记录

答：可以，因为1050这个事务点再id1记录删除的事务点1100之前，并在创建事务1000之后。

总结：MVCC表里，有create和delete事务ID，而查询有查询的事务ID，所谓事务ID就是时间节点去理解好了。不同的事务查看的的结果是不同的。

所以，MVCC的DB是你访问呢你的，我访问我的，大家不打扰的。原来是这种并发性哦，我TM服了... 这应该叫你看到我看不到的，是吧

然后好像现在mysql这个默认地库还是MyISAM引擎的库文件

xxx.frm 存放表定义，也就是表结构、几列、数据类型

MYD数据本身、MYI存放的是索引，索引可以压缩。

压缩，MyISAM压缩有前置条件和使用限制；而InnoDB应该是数据和索引都支持的。

现在高版本的mariadb的mysql库的引擎好像不是MyISAM，但也不是InnoDB。其实是Aria--事务部的MyISAM。

可见MAD的后缀其实对应的引擎是Aria，而Aria就是MyISAM的事务版

其他的库默认就是InnoDB

但是新版本的mariadb是既有ibdata1合并文件，也有各自的库文件的

上图👆frm就是表结构文件，ibd就是数据库文件和索引文件共用的一个文件。

其实老版本也可以分开来，当然是分开来好的，因为所有库文件和索引合在一起，单个文件很大，你也不知道哪个库变大了。

新版都是默认开启的

performance_schema引擎专用于performance_schema数据库的

Memory引擎，内存里的，临时存放。

Archive引擎：不支持删除，存档用的。

csv引擎，是excel那种？

blackhole，主从复制的时候，可以用来加快复制。就是A--复制到B.C.D，不是直接复制多份过去，这样会加重机器负担，所以A-复制给Z，Z再分发到BCD行，而Z作为中间人，如果使用InnoDB引擎就会存到本地，而使用blackhole就不会存到本地而是内存中放着的，直接分发出去，减少了磁盘I/O。