Mysql学习笔记一、理论篇

最新推荐文章于 2023-07-05 20:29:08 发布

皮卡猴

最新推荐文章于 2023-07-05 20:29:08 发布

阅读量197

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/flying_monkey_1/article/details/115897937

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Mysql学习笔记一、理论篇

从本篇文章开始总结Mysql相关的知识点，Mysql相关的内容可以分为两个部分，理论篇和实践篇。本篇文章记录理论篇相关的内容，包括索引、事务、主从复制三个部分。

1. 谈谈你对索引的理解

首先，mysql里存在一些对应的索引。索引底层的数据结构一般是通过B+树或hash表实现，对于不同的索引是和存储引擎相关的，存储引擎表示的是数据在磁盘上不同的组织形式，Innodb和MyIsam使用的是B+索引，MEMORY使用的是hash索引。
- 索引里面存储的一般是<key, value>类型的数据，通过key找到对应的value，key为某一列的值，value为一行记录。对k-v类型数据格式存储时，可以选择hash表、二叉树、AVL树、红黑树、B树、B+树，根本的点在于任意类型的二叉树都会导致整棵树变高，会增加IO次数，降低访问速率。B+树可以在某一个数据节点里，尽可能多的存储数据，让这棵树变低，减少IO次数，提高访问速率。
其次，mysql里有主键索引、唯一索引、普通索引、组合索引、全文索引等分类。工作中用的较多的是主键索引和组合索引，在主键索引和组合索引中会存在一系列回表、索引覆盖，最左匹配、索引下推等技术。
最后，使用sql语句时，执行sql的过程中，我们还可以通过索引的一些点进行优化，设计对应数据的访问形式。

1.1. 存储引擎

存储引擎表示的是数据在磁盘上的不同组织形式，常用的有Innodb和MyIsam。

Innodb和MyIsam有以下区别：

事务（Innodb支持事务，MyIsam不支持事务）
外键（Innodb支持外键，MyIsam不支持外键）
锁粒度（Innodb支持表锁和行锁，MyIsam只支持表锁）
索引存储方式（Innodb中数据和索引放在一个文件存储即.idb文件，MyIsam中数据和索引放在两个文件分开存储）

hash表是MEMORY存储引擎中索引的数据格式。Innodb支持自适应的hash：mysql索引是使用B+树还是使用hash表是由mysql服务来决定的，用户无法干扰（当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找）。

1.2. 数据结构

为什么不用hash表？

使用hash表必须要保证良好的hash算法，否则大量hash冲突会使hash表退化成为一个链表。
使用hash表的时候不支持范围查询，当需要范围匹配时，必须挨个对比，效率太低。
需要大量内存空间

为什么不用二叉树、AVL树、红黑树

树太深，IO次数过多，降低数据查询效率（局部性原理和磁盘预读）

B树和B+树

B树中间节点也会存储数据（某一行的记录），导致三层的B树映射的磁盘块个数很少（不到万条数据级别），想要达到百万，千万级别的数据存储量，必须增加树的高度。
B+树中间节点不存储数据，只存储Key值，大大提高了映射的磁盘块个数（3-4层B+树，千万条数据级别）。
B+树叶子节点存放全量数据，并且叶子节点是一个双向循环链表。B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点。因此可以对B+数进行两种查找运算：一种是从根节点开始，进行随机查找；一种是对于主键的范围查找和分页查找。

注意：

使用Innodb数据引擎时，一定要设置主键自增，在链表最后插入数据，可以防止在中间插入数据时叶分裂带来的时间消耗。

1.3. 聚簇索引和非聚簇索引

InnoDB存储引擎下数据和索引是放在同一个文件中的（.idb文件），在进行数据检索时，根据索引查找到数据后直接取出。因此在插入数据时，必须要将数据和某一个索引列绑定到一起（主键，唯一键，6字节的row_id）。

这样一来会有几个问题。

问题一：InnoDB在创建表时，一定要指定主键吗？

不是。
InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点存储记录，如果没有主键，会选择唯一键，如果没有唯一键，会生成一个6字节的row_id来作为主键（6字节的row_id可以存储亿级别条的数据，足够使用了，因为在6字节用完之前就需要进行分库分表了mycat和shardingsphere技术）。
mysql默认会为主键和唯一键创建索引。

问题二：一个表中可以包含多个索引列，那么数据文件会存储几份？

数据仅仅存储一份，不会造成多分数据的冗余。

问题三：数据和某一索引列是绑定在一起的，那么其他索引列如何检索数据？

将主键索引值放到其他索引的叶子节点。
这里的主键索引称为聚簇索引；其他索引称为非聚簇索引。

聚簇索引和非聚簇索引

数据和索引放在一起的叫作聚簇索引；数据和索引没有放在一起的叫作非聚簇索引。
InnoDB存储引擎中（.idb文件），既存在聚簇索引，又存在非聚簇索引；MyIsam存储引擎中（.MYI文件和.MYD文件），只有非聚簇索引。

1.4. MYSQL索引使用原则

MySQL有一些索引的设计或使用原则，如回表、索引覆盖、最左匹配、索引下堆。

1. 回表

从某一个索引的叶子节点获得聚簇索引的id值；根据id再去聚簇索引中获取全量记录，这个过程叫作回表。

表users：id, name, age, gender四列；
其中，id为主键，name为普通索引

	# 整张表包含2棵B+树，执行时分两步完成：
	# 1. 先去name索引的B+查找id值
	# 2. 再去id主键索引的B+树获取全量记录，该过程称为回表。
	SELECT * FROM users WHERE name = '张三';

2. 索引覆盖

从索引叶子节点中能获取到全量查询列，这样的过程叫作索引覆盖。

表users：id, name, age, gender四列；
其中，id为主键，name为普通索引

 	# 1. 直接从name索引的B+树，获取全量查询列
	SELECT id, name FROM users WHERE name = '张三';

3. 最左匹配

以最左边的为起点任何连续的索引都能匹配上，同时遇到范围查询(>、<、between、like)就会停止匹配，这种匹配方式称为最左匹配。
参考链接

# 用到了索引，where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序 
select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 

# 都从最左边开始连续匹配，用到了索引
select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

# 这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描 
select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

# 如果不连续时，只用到了a列的索引，b列和c列都没有用到 
select * from table_name where a = '1' and c = '3'

4. 索引下推

把原来在server层要做的数据处理操作，下推到存储引擎层处理的操作叫作索引下推。

表users：id, name, age, gender四列；
其中，id为主键，name,age为组合索引

	# 没有索引下推之前，数据库语句执行过程：先根据name去存储引擎拿到全量的数据，将数据读取到server层，
	# 然后在server层按照age做数据过滤
	
	# 引入索引下推后，根据name,age两个列去存储引擎筛选数据，将最终的结果返回给客户端
	SELECT * FROM users WHERE name = '张三' and age = 10

2. 谈谈你对事务的理解

事务中的一组SQL语句，要么全部执行成功，要么全部失败，是事务模型区分文件系统的重要特征之一。

2.1. 事务ACID

事务具有四种基本特性：原子性（Atomicity）、一致性(Consistency)、隔离性（Isolation）、持久性（Durability）。

原子性：事务中的所有操作作为一个整体像原子一样不可分割，其中操作要么全部成功，要么全部失败。

原子性实现原理：Undo log

Undo Log是为了实现事务的原子性而使用的一种方式，在InnoDB存储引擎中，还用Undo Log来实现多版本并发控制（MVCC）
在操作任何数据之前，首先将数据备份（这个数据备份称为Undo Log），便可以进行数据的修改，如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
undo log是逻辑日志，可以理解为
1. 当insert一条记录时，undo log中会记录一条对应的delete记录
2. 当delete一条记录时，undo log中会记录一条对应的insert记录
3. 当update一条记录时，undo log中会记录一条对应相反的update记录

一致性：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。

一致性实现原理：一致性是事务的根本追求。

一致性是通过原子性、隔离性、持久性共同保证的（事务并发执行或事务故障，系统故障）会对一致性造成破坏。
数据库系统通过并发控制技术和日志恢复技术来避免这种情况的发生
1. 并发控制技术（MVCC）保证了事务的隔离性。
2. 日志恢复技术保证了事务的原子性。

隔离性：并发执行的事务不会相互影响，其对数据库的影响和他们串行执行时一样。

完全的隔离性会使得并行执行的系统退化为串行执行，导致系统并发性能降低，因此实际上MySQL会对隔离性的要求有所放宽，这也会一定程度上造成数据库一致性要求的降低。
事务隔离级别
1. 读未提交(READ UNCOMMITED)：对事务处理的读取没有任何限制，不推荐。
2. 读已提交(READ COMMITTED)：
3. 可重复读(REPETABLE READ)：A事务新插入一条数据，B没有读到结果，但是B却没办法插入该条重复数据。
4. 串行化（SERIALIZABLE）：

事务隔离级别	脏读	不可重复读	幻读
读未提交 read-uncommitted	是	是	是
读已提交 read-committed	否	是	是
可重复读 repeatable-read (mysql)	否	否	是
串行化 serializable	否	否	否

隔离性实现原理：锁

事务的隔离性是通过MVCC机制和锁机制来实现的。

持久性：事务一旦提交，其对数据库的更新就是持久的，任何事务或系统故障都不会导致数据丢失。

持久性实现原理：Redo Log

当执行一个sql语句，并不是直接将数据持久化到磁盘中的，而是先写到内存缓存区中，再进行系统调用持久化到磁盘中。
Redo Log记录的是新数据的备份。在事务提交前，只需要将Redo Log持久化即可，不需要进行数据持久化。系统可以根据Redo Log中的内容，将数据恢复到最新的状态。

2.2. 多版本并发控制（MVCC）

多版本并发控制：读取数据时按照一种类似快照的方式将数据保存下来，不同事务的session会看到自己特定版本的数据和版本链。

版本链
对于使用InnoDB存储引擎的表来说，聚簇索引记录中包含两个必要的隐藏列：trx_id和roll_pointer
- trx_id: 每次对某条记录进行改动时，都会把对应的事务id赋值给tx_id隐藏列。
- roll_pointer:每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该条记录修改前的信息。
  -
ReadView

ReadView中维护当前活跃的tx_id。访问数据时，获取数据中的tx_id（版本链中最大的tx_id），如果比ReadView中所存放的tx_id都小，说明该事务已提交，可以访问；如果比ReadView都大或者在ReadView中，不可以访问，获取roll_pointer，取上一版本重新对比。

对于READ_UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了；对于SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要使用到版本链，核心问题是判断版本链中哪个版本是当前事务可见的。

readview中维护了一个数组m_ids，里面存储未提交的tx_id(也称为活跃的事务)，对于read_commited和 repeatable_read隔离级别，事务读数据的时候不会从活跃事务中读数据。

read_commited：如果一个事务提交了，会将其tx_id从m_ids中去除，这样另一个事务就可以读到提交后的结果。
repeatable_read：如果一个事务提交了，不会将其tx_id从m_ids中去除，这样另一个事务并不会读到提交后的结果。
rc和rr两个隔离级别下，以上效果是通过在不同时机创建ReadView来实现的。

多版本并发控制（MVCC）总结
MVCC（Multi-Version Concurrency Control,多版本并发控制）指处于read_committed、repeatable_read这两种隔离级别的事务执行普通的SELECT操作时，访问该条记录版本链的过程。
read_committed和repeatable_read=这两个隔离级别的一个很大不同是ReadView的生成策略不同（也就是tx_id在m_ids数组中去除效果的实现）。
- read_committed在每次执行普通SELECT操作前都会生成一个ReadView；
- repeatable_read只在第一次执行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好。

默认情况下，事务隔离级别为repeatable_read模式，InnoDB存储引擎的SELECT操作使用MVCC方式来保证事务的隔离性。某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性，这就需要用户显式的对数据库读取操作进行加锁。

2.3. 锁

MySQL支持一些对应的锁机制来解决并发数据处理所带来的问题，InnoDB存储引擎实现了行锁和表锁，MyIsam存储引擎只包含表锁。

InnoDB存储引擎实现了两种标准的行级锁：共享锁和排他锁。共享锁（S Lock）允许事务读一行数据；排他锁（X Lock)允许事务删除或更新一行数据，使用较多的是排他锁。

共享锁和排他锁
- 共享锁和排他锁都是行级锁，SELECT操作默认不加锁需要通过FOR UPDATE或LOCK IN SHARE MODE手动加锁，DELETE，INSERT，UPDATE操作时会先对要操作记录加锁。
- InnoDB存储引擎不同事务隔离级别，加锁方案不同。

	SELECT ... FOR UPDATE 			# 对读取的行记录加一个X锁，其它事务不能对已锁定的行加任何锁
	SELECT ... LOCK IN SHARE MODE	# 对读取的行记录加一个S锁，其它事务可以向被锁定的行加S锁

	# DELETE: 删除一条数据时，先对记录加X锁，再执行删除操作
	# INSERT: 插入一条数据时，先加“隐式锁”来保护这条新插入的记录，保证事务提交前不被别的事务访问到。
	# UPDATE: 更新一条数据时，先个记录加X锁

行锁（排他锁）的3种算法
- 记录锁（Record Lock）：单个行记录上的锁。
- 间隙锁（Gap Lock）：锁定一个范围，但不包含记录本身。
- Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock一般仅仅会锁定那些SELECT的记录，即便FOR UPDATE查询的字段不走索引；Next-Key Lock会对一个范围来加锁（repeatable_read插入数据时经常会触发）

	# a上建立了索引
	# RR隔离级别下，不仅仅对查询出的记录加了锁，因为加了间隙锁
	# RC隔离级别下，仅仅对查询出的记录加了锁，没有加间隙锁
	SELECT * FROM t1 WHERE a > 1 FOR UPDATE;		
	
	# c上没有建立索引，走全表扫描
	# RR隔离级别下，会对所有记录和间隙加锁，但并不是表锁（事实上，表锁很少用到）
	# RC隔离级别下，仅仅对查出的记录加锁
	SELECT * FROM t1 WHERE c = '1' FOR UPDATE;

RR隔离级别下，事务的隔离性是通过MVCC和锁机制实现的。

表锁
表锁使用的很少，在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB不会为这个表添加表级别锁的。
在对某个表执行ALTER TABLE、DROP TABLE这些DDL语句时，其它事务对该表执行SELECT、INSERT、DELETE、UPDATE语句时会发生阻塞；或在对某个表执行SELECT、INSERT、DELETE、UPDATE语句，执行ALTER TABLE、DROP TABLE这些DDL语句时会发生阻塞，这个过程会添加表级别的S锁和X锁。

3. 主从复制

复制是指将主服务器的DDL和DML操作通过二进制日志的形式，发送到从服务器，然后在从服务器上对这些日志重新执行，从而使得从服务器和主服务器的数据保持同步。

3.1. 三个线程

MySQL的主从复制主要有三个线程：master(bin log dump thread)，slave(I/O thread、SQL thread)。

主服务器binlog：主从复制的基础是主库发生的所有变更都记录到bin log，bin log是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点log dump线程：当bin log有变动时，log dump线程读取其内容，并发送给从节点。
从节点IO线程接收bin log内容，并将其写入relay log文件中。
从节点SQL线程读取relay log文件内容，并对数据更新进行重放，最终保证主从数据库的一致性。

主从节点使用bin log 文件 + position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后并不关心从库是否已处理，这样会产生一个问题，就是假设主库挂了，从库处理失败了，此时从库升为主库后，日志就丢失了。于是，产生了全同步复制和半同步复制。

全同步复制：主库写入binlog后，强制同步日志到从库，所有从库都执行完成后才返回给客户端。
半同步复制：从库写入日志成功后，返回ACK确认给主库，主库收到至少一个从库确认就认为写操作完成。

3.2. 企业常用的mysql主从架构

一主一从
通常用来热备份，但不能代替mysql数据的备份（快照技术）
一主多从
读写分离使用最多的架构，架构中任务分发是关键（Atlas Proxy）
双主
多应用在写场景较多的情况下（使用很少）
环形多主
级联同步
分担Master的读写压力

注意！mysql一般不会存放到docker中。

容器如果重新创建，其中的数据就会丢失。（安全）
容器内的文件驱动和原生操作系统中的文件驱动不同，中间加载了虚拟的over layer2，要比原生操作系统文件在读写时慢很多。（性能）
考虑到上述安全和性能两方面，要把mysql放到docker外。

皮卡猴

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习笔记一、理论篇

Mysql面试知识点总结1. 谈谈你对索引的理解首先，mysql里存在一些对应的索引。索引底层的数据结构一般是通过B+树或hash表实现，对于不同的索引是和存储引擎相关的，存储引擎表示的是数据在磁盘上不同的组织形式，Innodb和MyIsam使用的是B+索引，MEMORY使用的是hash索引。索引里面存储的一般是<key, value>类型的数据，通过key找到对应的value，key为某一列的值，value为一行记录。对k-v类型数据格式存储时，可以选择hash表、二叉树、AVL树、
复制链接

扫一扫