mysql记录
备注:来源于掘金小册(花了30大洋)
散碎小点
- mysql服务器程序被称为mysql数据库实例,简称数据库实例
- mysql服务进程默认名称为mysqld,客户端默认名称为mysql
- 输入命令返回结果过程的本质就是一个进程间通信的过程,一般支持三种通信方式:TCP/IP / 命名管道和共享内存 / Unix域套接字文件(了解)
- 服务端处理来自客户端的请求大致分为三部分:连接管理 -> 解析优化 -> 存储引擎
- 连接管理可采用上述的三种方式建立连接。当客户端发起连接请求的时候,需要携带主机信息,用户名,密码,服务器对这些信息进行验证,认证失败则拒绝连接。可使用SSL来保证数据传输安全性
- 解析优化分为:查询缓存,语法解析,查询优化
- 查询缓存:从5.7开始不推荐查询缓存,并在8.0移除,只做了解即可。移除原因:虽然可提高性能,但需要维护缓存造成的开销,每次需要更新缓存,维护该缓存的内存区域
- 语法解析:sql语句如何执行?服务端需要对该语句进行判断与校验
- 查询优化:服务端会将sql语句进行与具有划来保证执行效率
- 存储引擎:常见的有MyISAM与InnoDB。负责对表中数据进行提取与写入
- 并不是所有系统变量都有GLOBAL与SESSION的作用范围,比如max_connections,最多连接数只有GLOBAL,insert_id表示有自增列的表进行插入时,该列的初始值,就只有SESSION的作用范围
- 有些变量只读,不可以设置。比如version
- 字符集_ci结尾的表示不区分大小写
- utf8是utf8mb3的别名,utf8mb4可存储一些表情
- 字符集举例utf8,比较规则举例utf8_general_ci
- 字符集一般由三个系统变量控制,为了避免乱码,通常会将字符集统一(一般为utf8),语句为
SET NAMES 字符集名;
等同于SET character_set_client = 字符集名;SET character_set_connection = 字符集名;SET character_set_results = 字符集名;
- 比较规则一般在排序中使用
InnoDB
将表中数据存储到磁盘的存储引擎。处理数据的过程发生在内存里。磁盘的读取速度是没法和内存比的,InnoDB的方式为:将数据划分为若干页,以页作为磁盘到内存交互的基本单位,一般页大小为16KB,也就是一次最少从磁盘中读取16KB的内容到内存中,将内存的16KB刷新到磁盘中
平时存储的一条条记录的存放方式被称为行格式。行格式有以下四种格式
COMPACT行格式
一条完整记录分为两段,一段能看见的,一段隐藏的。又可以说分为额外信息
与真实数据
。
额外信息
分为三类:变长字段长度列表、NULL值列表、记录头。
变长字段长度列表
在mysql中有一些变长字段比如varchar,存储的字节数据不固定,所以在存储真实数据的时候需要顺表把这些数据占用的字节数也存起来,变长字段占用的空间分为两部分:真正的数据内容,占用的字节数
内容中存的是字符串的长度十六进制表示形式
varchar(M)此时M表示的是字符数而不是字节数,字节数=M*一个字符所使用的字节数(看字符集utf8为3,gbk为2,ascii为1)设字节数为L
- 若L<=255,那么使用一个字节表示真正的字符串占用的字节数
- 若127<=L<255,用两个字节来表示真正占用的字节数
- 若L<=127,则用1个字节来表示真正字符串占用的字符数
InnoDB在读记录的变长字段列表先查看表结构,如果某个变长字段允许最大字节数为255时,如何区分一个字段长度还是半个字段长度?设计师使用该字节的第一个二进制位作为标志位,如果该字节第一位为0,那么该字节就是一个单独的字段长度(使用一个字节表示不大于127的二进制第一位都为0(不懂?)),如果该字节的第一位是1,那么该字节就是半个字段长度
假如某个字段长度超过了16KB,那么如果该记录在单个页面无法存储时,InnoDB会把一部分数据存储到所谓的溢出页中,该变长字段长度列表值存储留在本页面的长度,所以两个字节也可以存放下来
总结:L超过255字节并真实存储字节数超过127字节,则使用2个字节,否则1个字节
NULL值列表
某些列可能会存储null值,放到真实数据中会占用地方,所以compact将null列统一管理,存储到null值列表中,过程如下
- 统计存储null的列有哪些
系统计算时候不会统计not null的列,因为不可能存储null值 - 如果表中全为not null,则null值列表也就不存在了,否则按二进制顺序
逆序排列
,1为null。0不为null(可以记为0为false,1为true) - null值列表必须用整个字节为表示,如果使用二进制个数不是整数,则在高位补0,8bit为1字节,所以最多补8个0
记录头信息
由5个字节组成,也就是40个bit位,40个二进制位,不同位表示意义不同
- 预留位:没有使用
- delete_mask(1):标记该记录是否被删除
- min_rec_mask(1):B+每层飞叶子节点的最小记录添加该标记
- n_owned(4):拥有的记录数
- heap_no(13):在记录堆中的位置
- record_type(3):当前记录类型,0普通,1非叶子节点记录,2最小记录,3最大记录
- next_record(16):下一条记录的相对位置
mysql会为每条记录默认添加一些列(隐藏列)
row_id 行ID,唯一标识
transactionid
b树与b+
- b所有节点都会存储行数据,一个节点容量为16k是有限的。而b+非叶子节点主存储主键,可以容纳更多数据
- 由于可以容纳更多数据所以,同一个节点可以指向更多的节点,所以相同数据量,b+更矮,IO次数更少
- b查询效率不稳定,有可能在非叶子,有可能在叶子。b+稳定
- b+叶子有序,便于范围查询
为什么不用hash索引
精确匹配速度是O(1),但不适合范围查找。hash本身就是散列的
建造索引的时间
刚建表或者修改表结构
好处
加快查询速度,加快排序速度,加快分组速度
创建索引四大原则
- 联合索优于单列索引
- 索引应该建立在区分度高的字段上(区分度指,该字段值筛选出的不具备普适应,比如性别只有男女,查出的区分度就低,不适合建立索引)
- 为查询频繁的创建索引,修改频繁的避免创建
- 避免创建重复索引(单列a索引,和联合(a,b,c)索引就是重复索引,是两颗独立的b+树)
辅助索引只存储索引列 + 主键,也就是说查到该值之后,其实查到的是一个主键,然后回表,根据该主键去找到对应的数据
MyISAM表数据和索引是分离的,索引很小,有必要可以加载到内存中,磁盘IO降到最低,但需在最后执行一次回表
InnoDB数据与索引挂着,不分离,无法直接全部加载到内存中,需要使用磁盘IO分次读取节点,最差的情况就是回表,重新走一次主键索引
所以MyISAM特点就是查询快,性能高一点。InnoDB稍逊一点,但支持事务