MySQL知识点（Leo）

MySQL

数据库相关概念

数据库(DataBase)：存储数据的仓库，数据是有组织的进行存储
数据库管理系统(DB Management System)：操纵和管理数据库的大型软件
SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准

MySQL介绍

启动

net start mysql
net stop mysql

客户端连接：

MySQL自带的客户端命令行

mysql [-h localhost] [-P 3306] -u root -p

数据模型：
数据库

表(存储数据)

SQL

sql分类：

数值类型

DML语法

DQL语法

DCL语法

函数

函数指一段可以直接被另一段程序调用的程序或代码

字符串函数

数值函数

日期函数

流程函数

约束

1、概念：约束是作用与表中字段的规则，用于限制存储在表中的数据

2、目的：保证数据库中数据的正确性、有效性和完整性

3、分类：

注意：约束是作用于字段上的，可以在创建表/修改表的时候添加约束

外键

一般主从表的划分：主表和从表的划分本质是 “谁是被依赖的一方”：

主表：独立存在，不依赖其他表。
从表：必须依赖主表才能存在，通过外键与主表关联。

外键添加语法：ALTER TABLE 表名 ADD CONSTRANT 外键名称 FOREIGN KEY (外键字段名) REFERNCES 主表(主表列名)；

多表查询

概述：从多张表中查询数据

笛卡尔积：两个集合A和集合B的所有组合情况(在多表查询时要消除无效笛卡尔积)

内连接

隐式内连接：select 字段列表 from 表1，表2 where 条件；

显示内连接：select 字段列表 form表1 [inner] join 表2 on 连接条件；

外连接

自连接查询

联合查询

注意：union all 会将查询的结果直接合并，union会对结果去重。对于联合查询的多张表的列数和字段类型也需要保持一致

子查询

事务

简介：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务操作

事务的基本特性（ACID）

原子性（Atomicity）

定义：事务中的所有操作要么全部成功，要么全部失败回滚。
实现机制：
数据库通过回滚日志（Undo Log）实现原子性。若事务执行过程中出错，系统会根据回滚日志撤销已执行的操作。

一致性（Consistency）

定义：事务执行前后，数据库状态保持合法（满足所有约束条件）。
例如：转账前后总金额不变，主键唯一性约束等。
实现依赖：
原子性、隔离性和持久性共同保证一致性，同时需应用层代码正确实现业务逻辑。

隔离性（Isolation）

定义：多个事务并发执行时，相互之间不可见，避免干扰。
例如：事务 A 读取数据时，事务 B 对同一数据的修改不可见。
隔离级别：
数据库提供不同的隔离级别（见下文），隔离性越高，并发性能越低。

持久性（Permanence）

定义：事务一旦提交，其结果永久保存在数据库中，即使系统崩溃也不丢失。
实现机制：
数据库通过重做日志（Redo Log） 实现持久性。事务提交前，先将变更写入日志，确保系统崩溃后可恢复。

并发事务问题

脏读：一个事务读取到另一个事务未提交的数据

不可重复读：一个事务先后读取同一条记录，但两次读取到的数据不同

幻读：一个事务按照条件查询时没有对应的数据行，但是在插入数据时，又发现该数据行已存在

事务的隔离级别

隔离级别定义了事务之间的可见性和干扰程度，由低到高分为四个级别：

READ UNCOMMITTED（读未提交）

特性：
允许一个事务读取另一个未提交事务的数据（脏读）。
问题：
可能读取到未提交的数据，这些数据可能最终被回滚，导致脏读（Dirty Read）。
示例：
事务 A 修改数据但未提交，事务 B 读取该数据；若 A 回滚，B 读取的数据即为脏数据。

READ COMMITTED（读已提交）

特性：
一个事务只能读取另一个已提交事务的数据（避免脏读）。
问题：
可能出现不可重复读（Non-Repeatable Read），即同一事务中多次读取同一数据结果不同。
示例：
事务 A 读取数据后，事务 B 修改并提交该数据；A 再次读取时，结果与第一次不同。

REPEATABLE READ（可重复读）

特性：
同一事务中多次读取同一数据结果相同（避免不可重复读）。
实现机制：
通过MVCC（多版本并发控制）*或*行锁实现。例如，MySQL InnoDB 使用 MVCC。
问题：
可能出现幻读（Phantom Read），即同一事务中多次查询同一范围数据，返回的记录数不同。
示例：
事务 A 查询符合条件的记录有 10 条，事务 B 插入一条新记录并提交；A 再次查询时，结果变为 11 条。

SERIALIZABLE（串行化）

特性：
最高隔离级别，强制事务串行执行（避免所有并发问题）。
实现机制：
通过表锁或强制事务排队实现。
缺点：
并发性能极低，适用于对数据一致性要求极高且并发量小的场景。

存储引擎

mysql体系结构：

连接层：最上层是一些客户端和连接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它具有的操作权限

服务层：第二层主要完成大多数的核心服务功能，如sql接口，并完成缓存的查询，sql的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等

引擎层：存储引擎真正的负责了mysql中数据的存储和提取，服务器通过api和存储引擎进行通信。不同的存储引擎具有不同的功能，和优势。

存储层：主要是将数据存储在文件系统上，并完成与存储引擎的交互。

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是库；所以存储引擎也被称为表类型

存储引擎特点：

InnoDB：一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5后是默认的存储引擎。

特点：1、DML遵循ACID模型，支持事务

2、行级锁，提高并发访问性能

3、支持外键foreign key约束，保证数据的完整性和一致性；

逻辑存储结构

表空间->段->区->页->行

MyISAM和Memory:

区别：

存储引擎的选择：

如今MyISAM被mongoDB替代；Memory被Redis替代

索引

概述：索引是帮助mysql高效获取数据的数据结构（有序）。

优缺点：

优：提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗

缺：索引列也是占用空间的，索引大大提高了查询效率，同时也降低了更新表的速度，如对表insert，delete，update时，效率降低

索引结构

问到索引结构默认是问B+tree

B-tree相比二叉树的区别是每个节点可以存储多个key

B+tree相对于B树而言：

1.所有的数据都会出现在叶子节点(当子节点向上分枝时，叶子节点会保持数据，并且该数据会形成链来连接数据)

2.叶子节点会形成一个单向链表

MySQL索引对数据结构对经典的B+tree进行了优化。在原B+tree的基础上，增加了指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+tree，提高了区间访问的性能，数据只存放在最下面的叶子节点，上面的节点只做索引作用

Hash索引特点

1、Hash索引只能用于对等比较(in，=)，不支持范围查询(between，>,<...)

2、无法利用索引完成排序操作

3、查询效率高，通常可以通过一次检索就可以(除非hash冲突)，效率通常高于B+tree索引

存储引擎支持

在MySQL中，支持hash索引的是memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+tree索引在指定条件下自动构造的

问：为什么InnoDB存储引擎选择使用B+tree索引结构？

答：1、相比于二叉树，层级更少，搜索效率更高

2、对于B-tree，无论叶子节点还是非叶子节点都会保持数据，这样导致一页中存储的键值减少，指针更会减少，要同时保存大量数据，只能通过增加树的高度，导致性能降低

3、相对hash索引，B+tree支持范围匹配及排序操作

索引分类

回表查询：先找二级索引拿主键值，再根据主键值到聚集索引中查到数据

覆盖索引：指查询使用了索引，并且需要返回的列，在该索引中已经全部能找到

索引语法

创建索引：CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

查看索引：SHOW INDEX FROM table_name;

删除索引：DROP INDEX index_name ON table_name;

性能分析

执行频率：通过show [global] status like 'com+7个下划线_'命令可以查看数据库中各个语句的访问频次

慢查询日志：记录了所有执行时间超过指定参数(long_query_tiome,单位：秒，默认10秒)的所有sql日志

profiling详情：能够帮助我们在做sql优化时帮助我们了解时间都耗时到哪里去了。通过have_profiling查看参数

explain执行计划：直接在sql语句前面加上explain，获取执行sql语句的信息

索引使用

最左前缀法则：如果索引了多列(联合索引)，要遵守最左前缀法则；最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳远某一列，索引将部分失效(后面的字段索引失效)
范围查询：复合索引使用中出现范围查询(>,<)，范围查询右侧的索引会失效。解决办法：sql尽可能用>=或<=
索引列运算：不要在索引列上进行运算操作，索引将失效
字符串不加单引号：字符串类型字段使用时，不加单引号，索引将失效
模糊查询：如果只是尾部模糊匹配，索引不会失效；如果头部使用模糊匹配，索引会失效
or连接的条件：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没索引，那么涉及的索引都不会被用到
数据分布影响：如果mysql评估使用索引比全表更慢，则不使用索引

SQL提示：是优化数据库的一个重要手段，就是在sql语句中加入一些人为的提示达到优化操作的目的

use index(idx_name)建议数据库用哪个索引还有:ignore index(忽略idx)/force index(强制使用idx)

覆盖索引：尽量使用覆盖索引(查询使用了索引，并且需要返回的列在该索引中已经全部能够找到)，减少select *(防止回表查询数据)

前缀索引：当字符类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，浪费磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以节约索引空间，提高效率

单例索引和联合索引：

单例索引：即一个索引只包含单个列

联合索引：即一个索引包含多个列

在业务场景中，如果存在多个查询条件考虑针对于查询字段建立索引时，建议建立联合索引，这样不用回表

SQL优化

插入优化:使用批量插入、手动提交事务和主键顺序插入；如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入

主键优化：

数据组织方式：在innoDB存储引擎中，表数据都是根据主键顺序存放的，这种存储方式的表称为索引组织表

页分裂:页可以为空，也可以填充一半和100%。每个页包含2-N行数据(如果一行数据过大，会行溢出)，根据主键排列；当主键乱序插入时，容易发生页分裂

页合并：当删除一行记录，实际上记录并没有被物理删除，只是记录被标记为删除且它的空间允许被其它记录声明使用。当页的记录达到MERGE_THRESHOLD(默认为50%)，innoDB会开始寻找最靠近的页(前或后)看看是否可以将两页合并以优化空间使用

order by优化：

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区soft buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫fileSort排序。

Using Index:通过有序索引顺序直接返回有序数据，不需要额外排序，效率高

group by优化

在分组操作时，可以通过索引来挺高效率

在分组操作时，索引的使用也是满足最左前缀法则

limit优化

一个常见头疼的问题是，越往后性能越低limit 2000000,10，此时mysql需要排序前面的记录，仅仅返回后10条记录，其它记录丢弃，查询排序的代价太大

优化思路：通过创建覆盖索引加子查询的形式来优化，

子查询在索引层快速筛选 ID + 主查询通过 ID 精准获取整行

count优化

count()是一个聚合函数，对于返回结果集，一行行地判断，如果count函数的参数不是null，累计值加1，否则不加，最后返回累计值

update语句优化

InnoDB引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁变成表锁，降低并发性能；update的where最好指想索引

Join优化

能用inner join就不用left join/right join，如果必须使用，要以小表为驱动

内连接会对两个表进行优化，优先把小表放在外面，大表放里面

视图/存储过程/触发器

视图：是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来定义视图查询中的表，并且是在使用视图时动态生成的。

通俗来讲，视图只保存了sql的逻辑，不保存查询结果

视图的检查选项：

当使用with check option子句创建视图时，mysql会通过视图检查正在更改的每个行，例如更新，插入，删除以符合视图的定义

mysql允许基于另一个视图去创建一个视图，它还会检查依赖视图中的规则以保持一致性

为了确定检查范围，mysql提供了两个选项：

CASCADED和LOCAL,默认为CASCADED

CASCADED会往下检查依赖视图的规则，以保持规则一致性
LOCAL只会检查当前创建视图的规则

视图的更新：视图中的与基础表中的行之间必须一对一关系，如果包含使用聚合函数，则不可更新

视图的作用：

1.简化查询操作，那些被经常使用的查询可以被定义为视图，使得用户不必为以后的操作每次指定全部的条件

2.数据库可以授权，但不能授权到数据库的特定行特定列中，视图可以规定用户查询修改特定行，列，更安全

3.视图可以帮助用户屏蔽真实表结构变化带来的影响，数据独立

存储过程

存储过程是事先编译并存储在数据库中的一段sql语句集合，调用存储过程可以简化开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，提高数据处理效率

特点：封装，复用；可以接受参数，也可以返回数据；减少网络交互，效率提升

触发器

是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器里面定义好的sql语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其它的数据库是相似的。现在触发器还只支持行级触发(影响多少行就触发多少次)，不支持语句级触发

Mysql锁有哪些，如何理解

概述：

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU,RAM,I/O)的争用外，数据也是一种提供许多用户共享的资源。如果保证数据并发访问时的一致性、有效性是所有数据库必须要解决的一个问题，锁冲突也是影响数据库并发访问的一个重要因素

按锁颗粒度分类：

1.行锁：锁某行数据，锁颗粒度最小，发生锁冲突的的概率最低，并发度高；应用在innoDB存储引擎中；

innoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁实现的，而不是对记录加锁。

常用sql语句加的锁：

默认情况下，innoDB在REPEATABLE READ事务隔离级别运行时，innoDB使用next-key锁进行搜索和索引扫描，以防止幻读

1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，会自动优化为行锁；

2.innoDB的行锁是针对索引加的锁，不通过索引条件检索数据时，innoDB会将表所有数据加锁（行锁升级为表锁）

3.索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁，目的防止其他事务插入间隙，间隙锁可以共存

2.表锁：锁整张表，锁颗粒度最大，并发度低；

对与表锁，分两类：表共享读锁(read lock)，表独占写锁(write lock)

语法：加锁：lock table表名...read/write；释放锁：unlock tables /客户端断开连接

读锁：也就是共享锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写

写锁：也就是排他锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写

元数据锁(MDL)：加锁过程是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上MDL锁主要作用是维护表数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。避免DML与DDL冲突，保证读写的正确性

意向锁：为了避免DML在执行时，加的行锁与表锁的冲突，在innoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

意向共享锁(IS)：与表锁(read)兼容，与表锁排他锁互斥

意向排他锁(IX)；与表锁的共享锁和排它锁都互斥。意向锁直接不会互斥

3.全局锁：锁定数据库中的所有表；全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续DDL,DML已更新操作的事务提交语句都将被堵塞。

其典型的使用场景是全库的逻辑备份，对所有的表进行锁定，从而获取一致性的视图，保证数据的完整性

特点：1.如果在主库上备份，那么备份期间都不能执行更新，业务处于停摆

2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，导致主从延迟

在innoDB中，我们可以在备份时加参数 --single-transaction参数来完成不加锁的一致性数据备份

锁思想还分为：

乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现

悲观锁：上面的行锁，表锁等都是悲观锁

InnoDB存储引擎

架构

从mysql5.5开始，默认使用innoDB存储引擎，它擅长事务处理，具有崩溃恢复特性；

Change Buffer: 更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据修改变更缓存存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。—— 靠 “延迟读 + 批量合并” 减少随机读。

Change Buffer的意义是什么？

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

Adaptive Hash Index: 自适应 hash 索引，用于优化对 Buffer Pool 数据的查询。InnoDB 存储引擎会监控对表上各索引页的查询，如果观察到 hash 索引可以提升速度，则建立 hash 索引，称之为自适应 hash 索引。自适应哈希索引，无需人工干预，是系统根据情况自动完成。

Log Buffer: 日志缓冲区，用来保存要写入到磁盘中的 log 日志数据（redo log、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

General Tablespaces: 通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

Undo Tablespaces（撤销表空间）：MySQL 实例在初始化时会自动创建两个默认的 undo 表空间（初始大小 16M），用于存储 undo log 日志。

Temporary Tablespaces（临时表空间）：InnoDB 使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据。

Doublewrite Buffer Files: 双写缓冲区，innoDB 引擎将数据页从 Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

Redo Log: 重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用。

架构-后台线程

Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo 页的回收。
IO Thread
在 InnoDB 存储引擎中大量使用了 AIO 来处理 IO 请求，这样可以极大地提高数据库的性能，而 IO Thread 主要负责这些 IO 请求的回调。

线程类型	默认个数	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

Purge Thread
主要用于回收事务已经提交了的 undo log，在事务提交之后，undo log 可能不用了，就用它来回收。
Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

事务原理

事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作视为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

redo log----重做日志

Redo Log File：记录的是 “数据页的物理变更”（如 “数据页 100 的偏移量 0x123 从 A 变 B”），且按 “事务执行顺序” 依次追加写入磁盘（类似日志文件的 “尾插”），磁盘只需按 “连续地址” 依次写入，无需移动磁头，是 “顺序 I/O”；
Buffer Pool 脏页刷盘：写入的是 “完整的数据页”，且这些数据页在磁盘上的位置是分散的，磁盘需要频繁定位不同数据页的地址，是 “随机 I/O”。
批量写入减少 I/O 次数：通过先将 Redo Log 记录暂存到 Redo Log Buffer 中，数据库可以将多个修改操作产生的 Redo Log 进行批量处理，然后一次性写入 Redo Log File。这大大减少了磁盘 I/O 的次数，提高了写入效率。

undo log----回滚日志

MVCC

基本概念

MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能。MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志、readView。
当前读----阻塞读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode (共享锁)，select … for update、update、insert、delete (排他锁) 都是一种当前读。
快照读----非阻塞读

简单的 select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed（读已提交）：每次 select，都生成一个快照读。
- Repeatable Read（可重复读）：开启事务后第一个 select 语句才是快照读的地方。
- Serializable：快照读会退化为当前读。

三个隐式字段

undo日志----版本链

readView

ReadView（读视图）是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
不同的隔离级别，生成 ReadView 的时机不同：
READ COMMITTED(读已提交)：在事务中每一次执行快照读时生成 ReadView。
REPEATABLE READ(可重复读)：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView。
ReadView 中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务 ID 集合
min_trx_id	最小活跃事务 ID
max_trx_id	预分配事务 ID，当前最大事务 ID +1（因为事务 ID 是自增的）
creator_trx_id	ReadView 创建者的事务 ID

trx_id：代表是当前事务 ID。
版本链数据访问规则
①. trx_id == creator_trx_id -> 可以访问该版本，成立，说明数据是当前这个事务更改的。
②. trx_id < min_trx_id -> 可以访问该版本，成立，说明数据已经提交了。
③. trx_id > max_trx_id -> 不可以访问该版本，成立，说明该事务是在 ReadView 生成后才开启。
④. min_trx_id <= trx_id <= max_trx_id -> 如果 trx_id 不在 m_ids 中是可以访问该版本的，成立，说明数据已经提交。