MySQL知识点总结_1

1. MySQL架构分为几层

连接层:先建立TCP连接(如果三次握手成功之后),就校验客户端传过来的
	   用户名和密码，不正确的话直接返回给客户端错误信息，连接结
	   束,若验证成功的话就根据客户端传过来的账户到对应的权限表里
	   面查找出该用户对所拥有的权限有哪些，以后该用户所有的操作都
	   将基于本次查询出来的权限
		
		SQL接口层:接收用户传来的SQL语句,并且给用户返回结果
服务层	  解析器 :对传过来的SQL进行语法语义上的检查,并建立语法树,查看用户
				是否有对应的操作(增删查改)权限
		查询优化器:在解析器解析之后，真正的查询之前，MySQL会为这个查询语句生成对应的执行				     计划,主要就是给这个查询语句选择合适的索引(全表检索？索引检索？)，并且
				 会判断多个表关联时join的顺序，执行计划完成之后，MySQL就会按照这个执行				 计划到存储引擎去找相关的方法并将查询结果通过SQL接口返回给客户端
		(Caches/Buffers)查询缓存组件(5.7.20有,8.0之后就删除了):可以在不同的客户端之间						共享
引擎层

2.不同存储引擎存储表结构的形式

存储引擎是:InnoDB，在dada\a 下

5.7的时候，该文件夹下 .frm 存储表结构信息，表字段信息

如果模式是系统表空间，那么数据信息和索引信息存储在 ibdata1 里面

如果存储形式是独立表空间，那么存储信息和索引信息存储在data\a的 .ibd 里面

如果是5.7则没有.ibd只有.frm，并且还会在data\a里面存储db.opt保存数据库的相关信息

如果是8.0，则没有.frm, .opt 只有.ibd(存储数据信息和索引信息)

存储引擎是MyISAM，在data\a下会存储三种形式的文件

5.7 .frm描述表结构文件，字段长度

8.0 .xxx.sdi 描述表结构文件，字段长度

同时还会有两个文件

.MYD 存储数据库相关信息

.MYI 存储索引相关信息

3.InnoDB相较MyISAM优点

1. 支持外键

2. 支持事务

3. 除了查询增加，如果还需要修改和删除(这两个操作在多线程的情况下也需要事务的支持)，也建议使用InnoDB

4. InnoDB支持行级锁，MyISAM只能支持表级锁(并发访问差，性能低)

   

4. InnoDB的缺点

1. 写数据的时候效率相较MyISAM低

2. 对内存的依赖较高

   这和InnoDB底层存储数据的结构有关

   因为InnoDB将数据和索引都存在一张表中,但是MyISAM是将索引信息和数据信息分开存储的，所以MyISAM可以只缓存索引信息，但是InnoDB必须缓存索引信息和数据信息，这就导致InnoDB对内存的要求较高，而且内存大小对性能有决定性的作用

5.其他的存储引擎

1. Archive :数据归档
2. CSV:存储数据时，以逗号分割数据项，每个字段必须 not null
3. Memory：数据存储在内存，.frm(存储表信息)文件存储在磁盘

6.根页面位置万年不动

每当为某个表创建一个B+树索引时，都会为这个索引创建一个根节点页面，最开始表中没有数据时，这个根节点既没有用户记录，也没有目录项记录。

随后向表中插入用户记录时，先将用户记录存储到这个根节点中。

当根节点可用空间用完时继续插入记录，此时会将根节点中所有记录复制到一个新分配的页，比如页a,然后对这个新页进行页分裂操作，得到另一个新页，比如页b，这是新插入的记录会根据键值的大小被分配到页a或者页b，而根节点便升级为存储目录项记录的页

一个B+树索引的根节点自诞生之日起便不会再移动。

这样只要我们对某个表建立索引，那么这个索引对应根节点的页号便会被记录到某个地方，以后凡是Innodb引擎需要用到这个索引，都会从那个固定的地方取出根节点的页号，从而来访问这个索引

7.内节点中目录项记录的唯一性

这个是针对二级索引来说的，比如一个表中有一个二级索引 c2 int类型的，这个表中已有一个 c1int 类型的主键索引，并且这个表中已有,假设每个页面只能存放三条用户记录 (1,1) (2,2) (3,3) (5,5)

那此时一共有两个页面，一个目录项页面了，假设内节点目录项记录不唯一，那此时B+树的目录页里面的存储结构如下：

假设此时我们又添加了一个 (6,6) ，先到目录项页面曲面找 1和5都满足条件，那此时肯定是不行的。

所以我们要确保内节点中目录项记录的唯一性的话，我们还要添加一个主键也作为记录，此时B+树的目录页面就变成这样了：
那么，此时我们添加(6,6)的时候就可以精准的定位到要存储在哪个页面了

8.一个页面至少存储2条记录

假设只存储一条记录，那么目录层级将非常非常多，并且最后存放真实数据的目录也只存放了一条记录，这样我们会多次的通过IO查询目录层级，太浪费时间了

9. MyISAM存储引擎的B+树索引结构

可以认为myisam没有聚集索引，全部都是二级索引，这也和MyISAM的表存储结构有关，Myisam引擎创建的一个表在磁盘中对应三个文件

5.7 .frm/8.0 .ibd [存储表结构信息]

.MYD 存储用户真实数据

.MYI 存储索引信息

如果是用myisam引擎创建的索引叶子节点除了保存键值外，其余的都是保存该键值对应真实数据在磁盘中的地址

10.MyISAM和InnoDB对比

11.对于InnoDB主键的建议

1. 不建议使用过长的字段作为主键，因为所有的二级索引都需要使用主键索引，否则会使二级索引变得过大
2. 使用单调的字段作为主键(自增最好)，如果是非单调的字段作为主键，那么在新插入数据的时候，数据文件为了维持B+树的特性，必须要频分的分页调整，性能十分低效