Mysql 详情

最新推荐文章于 2024-10-15 11:52:10 发布

ruhai-a

最新推荐文章于 2024-10-15 11:52:10 发布

阅读量164

点赞数

分类专栏：小知识 mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45620487/article/details/110631617

版权

小知识同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

mysql

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的六种存储引擎，包括InnoDB、MyISAM、Blackhole、MEMORY、CSV和ARCHIVE，重点对比了InnoDB和MyISAM的特性。接着，解释了数据库事务的ACID特性，四种事务隔离级别及其对脏读、不可重复读和幻读的影响。此外，还探讨了锁的类型和作用，以及MySQL查询和索引的基本原理。

摘要由CSDN通过智能技术生成

一、引擎

1、六大引擎

InnoDB、MyIsam、Blackhole、MEMORY、CSV、ARCHIVE

2、引擎的介绍

1、InnoDB：

（1）、提供事务安全表
（2）、行锁

储存方式：

2、MyIsam：

（1）、提供高速存储和检索，以及全文搜索能力
（2）、不支持事务，
（3）、Mysql默认引擎
（4）、表锁

储存方式：

3、Blackhole：

简介：Blackhole简称“黑洞”，顾名思义就是任何写入到此引擎的数据均会被丢弃掉，Select语句的内容永远是空。Blackhole虽然不存储数据，但是MySQL还是会正常的记录下Binlog，而且这些Binlog还会被正常的同步到Slave上，可以在Slave上对数据进行后续的处理。这样对于在Master上只需要Binlog而不需要数据的场合下，balckhole就有用了。

（1）、验证dump file语法的正确性
（2）、以使用blackhole引擎来检测binlog功能所需要的额外负载
（3）、由于blackhole性能损耗极小，可以用来检测除了存储引擎这个功能点之外的其他MySQL功能点的性能。

4、MEMORY：

（1）、提供"内存中"表，将数据储存在内存中
（2）、不支持事务处理

5、CSV：

（1）、把数据以逗号分隔的格式存储在文本文件中
（2）、不支持事务

6、ARCHIVE：

（1）、把数据存在远程数据库中
（2）、支持事务处理

InnoDB和MyIsam的一些细节区别

清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
InnoDB支持外键，MyISAM不支持
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引

二、脏读、不可重复读、幻读

参考大神：https://blog.csdn.net/qq_33591903/article/details/81672260

脏读：

简介：

读取了未提交到数据库的数据。

例子：比喻A事务提交了修改的数据，然后事务B也是在执行的，刚好读取了A提交的数据。然后A修改数据错误回滚了，而B事务什么都不知道，这样B事务读取了A的脏数据了。
比喻A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。

不可重复读：

简介：

前后多次读取，数据内容不一致。

比喻A事务读取一个用户的金额为20元，然后B事务取出10块，当A事务再次查询用户的的金额的时候是10块了，两次查询数据内容不一样，按正常走的话，数据应该一样的，称为不可重复读。

幻读：

简介：

前后多次读取，数据总量不一致。

比喻事务A第一次读取数据表有两百条数据，而事务B向表中添加多了一百条，表中就有三百条数据了，等事务A第二次去读取表的，时候就变成了三百条，读取的数据总量和之前统计的不一样，就像产生了幻觉一样，称为幻读。

三、事务

简介：
就看作一件事情，比喻一件事情里面有多个小事情，事情看作成事务，多个小事情看作成DML(insert、update、delete)语句。事务和DML语句有关，因为只有多个小事情才组成事情出来。DML语句的个数这个和业务逻辑有关。

1、四大特征(ACID)
（1）、原子性(A)：事务是最小单位，不可再分
（2）、一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
（3）、隔离性(I)：事务A和事务B之间具有隔离性
（4）、持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

2、原子性：

一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

3、一致性：

数据库总数从一个一致性的状态转换到另一个一致性的状态。

4、隔离性：

事务跟事务之间是有隔离性的，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

隔离性有隔离级别(4个):

读未提交：read uncommitted

这个已经很明显了。就是读取了未提交的数据，这里就产生了一个脏数据也叫“脏读”。
产生的脏数据，可以使用“读已提交”来解决。

读已提交：read committed

当对方事务提交数据后，我当前事务才能读取到。这里也出现一个事务的前后多次的查询中却返回了不同内容的数据的现象，也就是出现了不可重复读。
在这里隔离级下可以解决掉“脏读”的问题，但是这里会产生一个“不可重复读”
已提交可能会产生不可重复读的现象，我们可以使用可重复读。

注意：这是大多数数据库系统默认的隔离级别，例如Oracle和SQL Server，但mysql不是。

可重复读：repeatable read

在这个隔离级下所有事务前后多次的读取到的数据内容是不变的，也就是某个事务在执行的过程中，不允许其他事务进行update操作，但允许其他事务进行add操作，造成某个事务前后多次读取到的数据总量不一致的现象，从而产生幻读。
可重复读依然会产生幻读的现象，此时我们可以使用串行化来解决。

注意：这才是mysql的默认事务隔离级别

串行化：serializable

在这种隔离级别下，所有的事务顺序执行，所以他们之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象。但是安全和效率不能兼得，这样事务隔离级别，会导致大量的操作超时和锁竞争，从而大大降低数据库的性能，一般不使用这样事务隔离级别。
事务A和事务B，事务A在操作数据库时，事务B只能排队等待
这种隔离级别很少使用，吞吐量太低，用户体验差
在这种隔离级别下，所有的事务顺序执行，所以他们之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象。但是安全和效率不能兼得，这样事务隔离级别，会导致大量的操作超时和锁竞争，从而大大降低数据库的性能，一般不使用这样事务隔离级别。

表格示例（如下）

隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	×（未解决）	×	×
读已提交（read committed）	√（解决）	×	×
可重复读（repeatable read）	√	√	×
串行化（serializable）	√	√	√

5、持久性：

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

四、锁

Mysql常见的锁，有表锁和行锁。
表锁：对一整张表进行加锁。行锁：针对于特定的行。
在Server层面，提供了表锁的实现。行锁则由存储引擎实现。

锁模式：
锁模式分为读锁、写锁、意向锁
读锁：
读锁，也称共享锁（Share Lock），可以简称为S锁。
某个事务对某行或某表加了读锁后，其他事务依然可以读取，但不能修改。
可以同时有多个事务对某行或某表加读锁。
写锁：
写锁，也称独占锁（Exclusive Lock）、排它锁等，可以简称为X锁。
某个事务对某行或某表加了写锁后，其他事务不可以读取或更改记录。
同一时刻，只能有一个事务对对某行或某表加写锁。
意向锁：
这里的意向锁也分为读意向锁（Intention Share Lock，简称IS锁）以及写意向锁（Intention Exclusive Lock，简称IX锁）。

当事务1对表中的某条记录加X锁后，事务2想对整张表加X锁，于是事务2需要遍历该表中的所有记录，判断是否有记录存在X锁。如果有一条记录被加了X锁，则事务2需要等待事务1完成。

这种遍历的方式非常低效，Mysql在后来引入了意向锁的概念，用来解决这种问题。当事务1对某条记录加X锁前，首先需要对表加IX锁。当事务2需要对表加X锁时，只需要判断表上是否含有IX锁，如果有，则进行等待。

当然，意向锁不会和行锁冲突，意向锁只会阻塞对表的S锁或X锁。事务1对表加IX锁，然后对记录a加X锁，事务2需要修改记录b时，并不需要判断是否存在意向锁。

五、其他

MySQL运行过程：

MySQL解析过程：客户端与数据库服务通信——>缓存——>SQL解析(解析器)——>预处理——>优化器——>调用存储引擎——>调用数据——>.返回客户端
过程图：https://zhuanlan.zhihu.com/p/63201304
简单来说可以分为：
1.客户端与数据库服务通信。
2.查询缓存
3.查询优化处理
4.调用存储引擎
5.返回客户端

客户端发送一条查询给服务器；
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

MySQL 索引：

1、什么是索引？
直接回答：加快你查询速度，辅助你的sql语句。
另类回答：数据库文件存在磁盘中，使用索引的话，可以直接找到想要的数据的位置。

2、索引在什么情况下会失效？
(1)、like条件中前面使用了%
(2)、使用!= 或者 <>和in 导致索引失效；
(3)、类型不一致会失效。比喻user表里面user_id类型是int，另外一个表的user_id是其他类型不是int的；
(4)、索引列上使用函数（replace substr concat sum count avg）表达式、计算等；
(5)、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,比喻where=123,123使用引号;

2、什么情况上建立索引？
(1)、在where、order、join上加索引
(2)、如果经常更新的字段，不要加索引。
(3)、索引不需要太多，会影响更新速度。

3、索引类型
(1)、UNIQUE唯一索引
不可以出现相同的值。
加速查询 + 列值唯一（可以有null）
(2)、INDEX普通索引
允许出现相同的索引内容。
仅加速查询。
(3)、PRIMARY KEY主键索引
不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。
(4)、fulltext index 全文索引
目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
对文本的内容进行分词，进行搜索