mysql阅读记录

一、了解🐟

1.架构🐟

mysql是服务端-客户端的sc模式，通过 tcp/socket 等进行通信连接。

2.连接过程🐟

3.配置🐟

配置文件一般路径：/etc/my.cnf、/etc/mysql/my.cnf、～/.my.cnf 其中可配置多个组内容：

[server]
具体启动项

[mysqld]
...

[client]
...

[mysql]
...

具体配置很多，之后再收集

4.变量🐟

也就是mysql的系统环境，比如最大连接数、默认引擎是什么等。有全局范围和会话范围就行，慢慢收集。

5.字符集🐟

字符与二进制之间的映射集。比如最早的 ascii。mysql直接utf8mp4就行，其用1～4个字节表示一个字符。

6.比较规则🐟

控制字符的大小、排序等。一般 utf8_xx_xx 格式。通用：utf8_general_ci：ci表示不区分大小写，genral表示通用，_cs就区分大小写。

注意点：mysql服务端和客户端都可以指定字符集、比较规则，使用时需要注意最好配置一致。

二、结构🐟

默认都是指 innodb 存储引擎下的结构

1.行结构🐟

P57

行结构也就是每条数据的格式（insert插入的内容在innodb中以怎样的格式存储），innodb中有四种行数据结构，这里只介绍 compact 一种。

2.页结构🐟

P85

innodb 中磁盘和内存间数据交换的基本单位为页，一页大小16KB。也就是说使用innodb引擎的mysql,一次最少会把内存中的16KB数据刷新到硬盘中。

有很多种类型的页，其中存放真实数据的页称为索引页，或者直接说数据页吧。

innodb 还会把 user records 中的行数据进行分组，把每组的最后一个地址偏移量保存到 page directory 中，查找数据时根据二分法确定到组，再遍历即可定位到主键数据。

3.文件🐟

P140

上面说的都是mysql的结构，数据还是要以文件格式保存der,这些文件保存在哪？有什么特有后缀提供识别？

show variables like 'datadir',文件就保存在这个目录下！

存储引擎	文件
Innodb	表结构(表名.frm)、表数据（表名.idb）
Myisam	表结构(表名.frm)、表数据(表名.myd)、表索引(表名.myi)、

上面是比较完整的数据结构图了，一堆结构，页里保存行，64个页组成区，不同区又可以分为碎片区、空闲区等，256个区又组成组等等。

通过这些骚结构，mysql实现了数据的高效处理（碎片管理、顺序io等等等）

4.索引🐟

P90

类似页中给行分配组偏移量一样，索引是给页建立一个目录项。具体：b+树算法。（注意b+树不超过4层）

聚簇索引：叶子节点都存储完整的行数据（innodb自动创建），非叶子节点都按照主键排序。二级索引：以非主键字段建立b+树，叶子节点存放主键，再根据主键去聚簇索引中找到完整数据。（回表）联合索引：以多个非主键字段建立b+树，...

ps：联合索引的列值在b+树中的排序规则：先按列1值排序，列1值相同的情况下按照列2值排序，列1列2值相同的情况下再按列3值排序，...。所以如果只查询列2条件的话，并不会走联合索引哟。而存在联合索引的情况下，要避免创建重复索引，也就是不用单独给列1再各索引了，只查列1也会走联合索引der。

b+树的每个节点都是数据页，也就是默认都会占有16KB空间，所以创建索引有一定的空间代价; 而增删等操作都会影响b+的稳定，b+树需要重新排序，带来一定的时间代价。

5.索引应用🐟

区间：=、in、not in、is null、is not null、>、<、!=、between、like等关键字产生的扫描区间，区间存在是为了减少遍历全部聚簇索引，根据条件确定区间即可减少查询次数。
排序：order by,对查询到的记录按照规则进行排序。如果是按照索引列排序，会减少回表操作，直接根据索引排序就行，但是要保证列排序规则要统一，asc-升序、desc-降序。如果是按联合索引排序，也要记得按索引中的列顺序哟。如果没用索引的情况下要怎么排序nie：把查到的记录放到内存中按规则一个个排呗，如果内存放不下就放磁盘，所以这效率是有点感人der
分组：group by,记录中相同的列合并成一个。如果分组字段多列的话，也要按联合索引顺序来写，因为联合索引保证了列1相同再到列2、列3,相同的列一眼丁真。如果不用索引的话，把记录取出来后先按列1进行分组，将相同的结果分组，再按列2进行分组，而且这些统计的结果都要保存到临时表中，所有分组操作结束后再把临时表的结果响应给到客户端，就问你麻不麻烦。

6.什么时候用索引🐟

只为用于搜索、排序、分组的列创建索引。（也就是上面提到的那些关键字）
列值大多都是不重复的情况下，因为二级索引是为了减少回表的，如果值都相同，那还不如直接聚簇索引，全表扫描，还少走一遍二级索引。
索引的列值类型尽量小，类型越小，索引占有空间就越少，一个数据页存放的记录就越多，越高效。
前缀索引，经典右模糊
覆盖索引，查找的数据就是索引列的值，回表都不用回了。

三、优化🐟

前面说过 sql解析后就到优化步骤，最后会生成一个执行过程。优化就需要知道语句的一些执行步骤，执行的成本计算，如何更哪些步骤的成本更优等。

1.访问方式🐟

mysql中通过不同方式执行语句的过程成为访问方式，比如执行一条语句，该语句是不是走主键、二级索引，还是全表扫描之类。

const：通过主键或者唯一索引来执行语句。这种执行效果是常数级别的。
ref：通过二级索引进行比较取值的方式。列1=xxx等。
ref_or_null：跟ref一样，但多了一个null值比较。
range：列值为某个区间的，比如：列1xxx等。
index：取值结果不用回表的，比如：select 列1,列2 from xxx where 列3=xxx; 列1、列2、列3都为联合索引的情况下。
all：全表扫描，聚簇索引从头到尾走一遍。

2.连接原理🐟

连接：多表中的列进行两两组合，比如两表，前者为驱动表，后者为被驱动表，从驱动表中每查出一条都需要跟被驱动表进行对比。

内连接：结果集中只存在符合条件的记录
外连接：以驱动表为结果集，合并被驱动表中复合条件的记录

驱动表只会被访问一次，而被驱动表则会根据驱动表中符合条件的记录，有多少记录就访问多少次。条件用索引就行，避免被驱动表走全表。另外查询结果也不用用 *，直接写需要的列，有个join buffer的缓存区可以在内存中进行快速比较条件，前提就是要给出具体的列。

3.成本🐟

mysql 选择访问方式时会根据IO成本和CPU成本来选择代价最低的方案执行查询。

找出所有执行语句的方案
- 全表扫描的代价：每张表都有维护统计信息：show table status like '表名'\G，统计信息存储的表：show tables from mysql like 'innodb%stats';
- 不同索引

4.优化器🐟

mysql会尽力对用户编写的sql语句进行重写优化。

内连接的驱动表、被驱动表顺序优化
一些条件、常数优化等
子查询转连接查询等

5.explain 详解🐟

可查看某个查询语句的具体执行计划。explain sql语句;

列名	描述
id	语句唯一id
select_type	查询类型
table	表名
partitions	分区信息
type	单表的访问方式
possible_keys	可能用到的索引
key	实际用到的索引
key_len	索引长度
ref	索引值匹配的对象信息
rows	预估需要读取的记录条数
filtered	预估读取记录数百分比
extra	额外信息

主要看type访问方式，再看下索引的字段满不满足。

6.缓存🐟

把查询的数据读取到内存，减少磁盘io。默认innodb的 bufferpool 只有128m.

[server] innodb_buffer_pool_size=xxxx（字节单位）

四、事务🐟

起源：映射现实业务

1.原则🐟

原子性：操作不可分割
隔离性：事务间顺序有规律
一致性：结果符合实际
持久性：结果落盘

mysql innodb、ndb才有事务机制，自动事务下mysql的每条语句都是一个独立的事务，事务开启后可以设置保存点方便回滚。

2.redo日志🐟

记录事务修改的内容，系统崩溃重启后读取修改内容重新执行一遍就能恢复数据。

系统后台单独线程，每秒一次的频率将redo日志刷盘、关闭服务时、checkpoint时

3.undo日志🐟

为了事务回滚而记录的内容，insert、delete、upate等

4.事务概念🐟

事务并发执行时出现的一系列不符合现实逻辑的现象：

脏写：A事务修改未提交的B事务中已经被B修改过的数据。
脏读：A事务读取到未提交的B事务中修改过的数据。
不可重复读/模糊读：A事务修改未提交的B事务读取的数据。
幻读：A事务在B事务读取数据后但未提交时修改数据（insert、delete、update）

mysql对应设置了不同隔离级别，不同隔离级别在事务并发过程中会发生不同的现象：

未提交读
已提交读
可重复读（mysql默认）
串行读

读未提交：在这个隔离级别下，事务A会读到事务B未提交的数据，在事务B回滚后，事务A读到的数据无意义，是脏数据，称为脏读

读已提交：在这个隔离级别下，只有在事务B已提交时，事务A才能读到。

可重复读：如果事务A先查询id为1的记录，之后事务B修改这条记录并提交，事务A再读取，两次结果会不一致，所以不可重复读。在这个隔离级别下，就算事务B的修改已经提交，事务A读到的数据依旧是一致的。

幻读：当事务B插入一条新数据并提交之后，事务A查询不到当前数据，查询不到就以为不存在，但是事务A却可以更新这条数据成功，并且更新后再次查询，数据出现了。一开始查询不到，但能修改，再次查询又出现了，跟幻觉一样，所以称为　幻读。

隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
可重复读	不可能	不可能	可能
串行读	不可能	不可能	不可能

无论哪个隔离级别都不允许脏写的情况发生。

五、MVCC与锁🐟

1.MVCC🐟

用于控制并发事务访问相同记录的行为，比如控制A事务读取某字段值时，要所有其他修改该字段的事务都执行结束，才能读到最新值，否则都读到修改前的值等等。

因为逻辑实现使用到undo的修改记录，多个记录会形成一条记录链，也成版本链，所以这种机制又称为：多版本并发控制。

版本链：对同一个字段值的修改记录，每次修改都生成一个undo记录，这些所有的修改形成的undo记录链。

比如读未提交，别的事务直接读取字段最新的undo记录即可。而对于读已提交和重复读，则需要判断版本链中哪个版本是当前事务可见才行。

readview：一致性视图或者快照，版本可见的规则。其中包含：生成事务时的事务id、系统所有活跃事务id列表、活跃事务最小事务id、下一个事务的id等等。
视图的使用规则（根据规则在undo记录链中一个个匹配，如果都不符就说明该字段的所有修改值都不可用，查询的话就没结果）
- undo记录的事务id与readview的事务生成id一致，说明是同一个事务内，当然是可见的
- undo记录的事务id小于最小的活跃id，说明该记录是生成readview前就提交的，也是可见的
- 如果undo记录的事务id >= readview中记录的下一个事务id,说明这条undo记录是生成readview后才开启的，就不可见
- 如果undo记录的事务id在readview的活跃列表中，也不可见，说明生成readview时，该undo记录还没结束事务。
读已提交：每次读取数据（也就是select）前都生成一个readview
可重复读：第一次读取数据时生成readview，之后的select都复用该readview

mvcc实质就是在读已提交和可重复读这两个事务下执行select操作时，访问undo记录链的过程。是一种提高数据库并发性能的手段，这种select称为一致性读/一致性无锁读。

2.锁🐟

前面mvcc主要说了并发事务下同一个记录读取的问题，那并发事务的修改则需要靠锁来保持数据的安全、一致。

锁与mvcc的关系：数据库的并发场景主要是读-读、读-写、写-写，事务并发时对每个操作都加锁可以保持数据的一致，但会导致性能的降低，因此在能不用锁的场景就别用锁，比如其中的读操作。

并发场景
- 读-读：无锁
- 读-写：mvcc+锁
- 写-写：锁

mysql中有表锁、行锁的概念，锁粒度越小，对性能的影响就越小，这里主要看行锁的相关操作。

innodb中的行级锁
- record lock：记录锁，对某条记录加锁
- gap lock：间隙锁，给某区间内记录加锁，不允许在该区间内操作记录，可以防止插入幻读记录
- next-key lock：记录锁+间隙锁，保证记录所在的某区间内无操作
- insert intention lock：等待gap锁释放的事务，要持有该锁，表示处于等待状态
锁的使用细则（既然定义了那么多锁，什么情况下使用呢？）
- https://www.cnblogs.com/lifegoeson/p/13675798.html
死锁：事务间需要等待另一方已经持有但未释放的锁，当检测到死锁时，innodb会选择一个较小的事务进行回滚，释放掉该事务持有的锁。

创建日期: October 14, 2024 17:02:20
最后更新: October 31, 2024 00:58:39