MySQL学习笔记（一）

基本信息
1.bin目录下命令
1.mysqld
启动一个mysql服务端进程.但该命令不常用
2.mysqld_safe
间接调用mysqld,同时启动了另外一个监控进程. 监控进程在服务端进程挂了的时候,可以重启进程.
同时将服务器程序的出错信息和其他诊断信息生成日志
3.mysql.server start/stop
间接的调用mysqld_safe
建立客户端连接
mysql -h主机名 -u用户名 -P端口 -p密码
断开客户端连接
quit/exit/\q
2.客户端进程和服务器进程的通信方式
1.TCP/IP
2.命名管道和共享内存(windows系统)
1.命名管道
1.启动服务器程序的命令中加上–enable-named-pipe参数
2.启动客户端程序的命令中加入–pipe或者–protocol=pipe参数
2.共享内存
1.启动服务器程序的命令中加上–shared-memory参数
2.启动客户端程序的命令中加入–protocol=memory参数来显式的指定使用共享内存进行通信
3.服务端和客户端必须在同一台Windows主机中
3.Unix域套接字文件
1.MySQL服务器程序默认监听的Unix域套接字文件路径为/tmp/mysql.sock
2.启动客户端的命令指定的主机名为localhost或指定了–protocol=socket的启动参数
逻辑架构图
1.MySQL server
1.连接管理
1.客户端程序发起连接，需要携带主机信息、用户名、密码，服务器程序会进行认证
2.服务端会缓存连接线程,节省开销
3.可进行设置连接线程数量
4.可以使用SSL（安全套接字）的网络连接进行通信，来保证数据传输的安全性。
2.解析与优化
1.查询缓存
1.查询缓存可以在不同客户端之间共享
2.如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中
3.如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存
4.MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除
5.查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销, 从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除
2.语法解析
1.文本分析，判断请求的语法是否正确
3.查询优化
1.对语句做一些优化,如外连接转换为内连接、表达式简化、子查询转为连接
2.生成执行计划,表明了应该使用哪些索引进行查询，表之间的连接顺序
3.可以使用EXPLAIN语句来查看某个语句的执行计划
MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端
2.存储引擎
1.存超引擎类型
1.InnoDB
1.MySQL默认的存储引擎
2.具备外键支持功能的事务存储引擎
2.MyISAM
1.主要的非事务处理存储引擎
3.Memory
置于内存的表
2.可以为不同的表设置不同的存储引擎
3.创建表时可以指定存储引擎,不显式声明则采用默认存储引擎
4.可以修改表的存储引擎
启动配置
1.如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准
系统变量
1.如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION
2.SHOW VARIABLES语句默认查看的是SESSION作用范围的系统变量
3.并不是所有系统变量都具有GLOBAL和SESSION的作用范围
4.有些系统变量是只读的，并不能设置值
状态变量
1.状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，如Threads_connected表示当前有多少客户端与服务器建立了连接
字符集
1.utf8字符集表示一个字符需要使用1～4个字节, mysql中使用的utf8是阉割过的utf8mb3,使用1～3个字节表示字符
2.4个级别的字符集和比较规则
1.服务器级别
2.数据库级别
3.表级别
4.列级别
3.查看MySQL中查看支持的字符集和比较规则的语句
1.SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
2.SHOW COLLATION [LIKE 匹配的模式];
引擎
InnoDB
1.将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB 也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
2.InnoDB行格式
3.InnoDB数据页结构
1.结构示意图
2.记录头信息
3.Page Directory（页目录）
1.页面目录就是由槽(Slot)组成的
1.将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组
对于最小记录所在的分组只能有 1 条记录， 最大记录所在的分组拥有的记录条数只能在 1~8 条之间，
剩下的分组中记录的条数范围只能在是 4~8 条之间
2.每个组的最后一条记录的地址偏移量(next_record属性)单独提取出来存储到槽中
2.一个数据页中查找指定主键值的记录的过程分为两步
1.通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录
2.通过记录的next_record属性遍历该槽所在的组中的各个记录。
4.Page Header（页面头部）
Segment Header 结构的运用
PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP都占用10个字节， 它们其实对应一个叫Segment Header的结构
PAGE_BTR_SEG_LEAF记录着叶子节点段对应的INODE Entry结构的地址是哪个表空间的哪个页面的哪个偏移量， PAGE_BTR_SEG_TOP记录着非叶子节点段对应的INODE Entry结构的地址是哪个表空间的哪个页面的哪个偏移量
这样子索引和其对应的段的关系就建立起来了
因为一个索引只对应两个段，所以只需要在索引的根页面中记录这两个结构即可
占用固定的56个字节，专门存储各种状态信息
5.File Header（文件头部）
占用固定的38个字节,描述了一些页的通用信息
FIL_PAGE_TYPE
FIL_PAGE_PREV和FIL_PAGE_NEXT
通过建立一个双向链表把许许多多的页就都串联起来了，而无需这些页在物理上真正连着
并不是所有类型的页都有上一个和下一个页的属性
6.File Trailer
由8个字节组成,用于校验页面的完整性
File Trailer与File Header类似，都是所有类型的页通用的
前4个字节与File Header中的校验和对应
同步时会先同步File Header的校验和
如果页面从内存同步回磁盘中成功,页的首部和尾部的校验和应该是一致的
后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
为了校验页的完整性的
7.Infimum + Supremum
两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
4.B+树索引
1.下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
会导致进行页分裂操作
2.页内的记录是按照主键的大小顺序排成一个单向链表。
3.各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
4.存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
聚簇索引
1.B+树的叶子节点存储的是完整的用户记录
2.InnoDB存储引擎会自动的为我们创建聚簇索引
3.在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点）， 也就是所谓的索引即数据，数据即索引。
二级索引
使用非主键字段排序建立的B+树
叶子节点中除了索引字段外,还包含主键字段
因为需要保证在B+树的同一层内节点的 目录项记录除页号这个字段以外是唯一的
联合索引
同时以多个列的大小作为排序规则建立的B+树
排序是先以前序索引字段排序,再根据后续索引字段排序
sql查询语句时,如果未利用前序索引字段,将无法使用该联合索引
回表
利用二级索引的B+树查询后,根据主键去聚簇索引中快速定位其他字段信息
节点
叶子节点
实际用户记录其实都存放在B+树的最底层的节点上
非叶子节点
根节点
一个B+树索引的根节点自诞生之日起，便不会再移动
为了避免层级过多,mysql规定了一个页面最少存储2条记录
5.索引的代价
1.空间上的代价
每建立一个索引,需要占用许多的页空间
2.时间上的代价
增、删、改操作会对可能会对节点和记录的排序造成破坏, 每个索引对应的B+树都要进行相关的维护操作
6.索引的使用
MyISAM
二者区别
1.InnoDB索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了 2.MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储
1.InnoDB是mysql默认的事务性存储引擎 2.MyISAM是非事务型的存储引擎
1.InnoDB支持表级锁,行级锁 2.MyISAM是表级锁
1.InnoDB支持外键
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
执行select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行， 但是MyISAM只要简单的读出保存好的行数即可
2.MyISAM插入不频繁，查询非常频繁