Mysql总结

Joey Liao

已于 2023-03-08 22:08:42 修改

阅读量445

点赞数

分类专栏：数据库面试总结文章标签： mysql 数据库 java

于 2022-12-21 14:54:49 首次发布

本文链接：https://blog.csdn.net/c630843901/article/details/128386259

版权

面试总结同时被 2 个专栏收录

13 篇文章 2 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

1 Mysql基础架构

在这里插入图片描述

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

2 Mysql存储引擎

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。InnoDB支持事务，其他都不支持

MyISAM何InnoDB的区别

是否支持行级锁：
MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务：
MyISAM 不提供事务支持。
InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。
是否支持外键：
MyISAM 不支持，而 InnoDB 支持。外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。
是否支持一场崩溃后的安全恢复：
MyISAM 不支持，而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
是否支持MVCC：
MyISAM 不支持，而 InnoDB 支持。
索引实现不一样：
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录

3 Mysql索引

简介:索引是一种用于快速查询和检索数据的数据结构

3.1 索引的分类

按照数据结构维度划分：

BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
哈希索引：类似键值对的形式，一次即可定位。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，InnoDB 中的二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。能极大提升查询效率。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

主键索引与二级索引

主键索引

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
在这里插入图片描述

可以看到主键索引叶子节点存储的是数据

二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

在这里插入图片描述

二级索引叶子节点存储的则是主键，要想获得数据，需要再次回表用主键进行查询

覆盖索引与联合索引

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

联合索引

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。

最左前缀匹配原则： 在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

前缀索引

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。

建立前缀索引的语法是：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

这里最关键的参数就是prefix_length，这个值需要根据实际表的内容，得到合适的索引选择性（Index Selectivity）。索引选择性就是不重复的个数与总个数的比值。

选择方法：
比如我们现在有个Employee表，其中有个FirstName字段，是varchar(50)的，我们查询该字段的索引选择性：

select 1.0*count(distinct FirstName)/count(*)
from Employee

得到结果0.7500，然后我们希望对FirstName建立前缀索引，希望前缀索引的选择性能够尽量贴近于对整个字段建立索引时的选择性。我们先看看3个字符，如何：

select 1.0*count(distinct left(FirstName,3))/count(*)
from Employee

得到的结果是0.58784，好像差距有点大，我们再试一试4，5，6个字符，直到贴近于0.75

使用索引小技巧

选择合适的字段创建索引，不为 NULL 的字段——被频繁查询的字段——被作为条件查询的字段——频繁需要排序的字段 ——被经常频繁用于连接的字段
被频繁更新的字段应该慎重建立索引
限制每张表上的索引数量，建议单张表索引不超过 5 个
尽可能的考虑建立联合索引而不是单列索引
注意避免冗余索引，冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。
字符串类型的字段使用前缀索引代替普通索引

Mysql事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。能极大提升效率
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

在这里插入图片描述
另外，关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发带来的问题

脏读

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并为提交到数据库， A 的值还是 20。
在这里插入图片描述

丢失修改（Lost to modify）

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
在这里插入图片描述

不可重复读（Unrepeatable read）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。
在这里插入图片描述

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 1 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。
在这里插入图片描述

SQL标准定义的事务隔离级别

READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

Mysql锁

表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：（排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。）

共享锁（S锁）：又称读锁，食物在读取记录的时候获取共享锁，允许多个事务同时获取共享锁
排它锁（X锁）：又称写锁，事务在修改记录的时候获取排它锁，不允许多个事务同时获取。（锁不兼容）

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁

意向锁是表级锁，共有两种：

**意向共享锁（Intention Shared Lock，IS 锁）：**事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
**意向排他锁（Intention Exclusive Lock，IX 锁）：**事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
在这里插入图片描述

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。
在这里插入图片描述

当前读和快照读

快照读（一致性非锁定读）： 由 MVCC 机制来保证不出现幻读。
当前读（一致性锁定读）： 使用 Next-Key Lock 进行加锁来保证不出现幻读。

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：SELECT ... FOR UPDATE SELECT ... LOCK IN SHARE MODE

快照即记录的历史版本，每行记录可能存在多个历史版本

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

MySQL 如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。MySQL 提供了两个方法来处理 ip 地址

INET_ATON() ： 把 ip 转为无符号整型 (4-8 位)
INET_NTOA() ： 把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

Joey Liao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql总结

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
复制链接

扫一扫