Mysql自己理解_mysql数据库工作集和常驻集的区别-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_43117466/article/details/108727389

理论相关

数据库的三范式是什么？

1NF：列不可再分，如果数据库表的所有字段值都是不可分解的原子值
2NF：属性完全依赖于主键。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
3NF：属性不依赖与其他非主属性，属性直接依赖于主键

说一下 ACID 是什么？

事务具有4个特征，分别是原子性、一致性、隔离性和持久性，简称事务的ACID特性。
原子性（atomicity)：
- 一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
一致性（consistency)
- 事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
- 如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态
隔离性（isolation）
- 事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。
- 不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间
- 即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。
持久性（durability）
- 一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。
- 即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

mysql 的内连接、左连接、右连接有什么区别？

内连接：关键字 inner join on 返回的是两个表的交集部分。左边右边共有的。
左连接：关键字 left join on/left outer join on 左外连接，是外连接的一种，左连接的话，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为Null。左边全部内容，右边没有的为null。以左表为主表，公共部分加右表没有的补为null
右连接：关键字 right join on/right outer join on 右外连接，是外连接的一种，与左连接相反，右连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为Null。右边全部内容，左边没有的为null。以右表为主表，公共部分加左表没有的补为null
全连接：关键字 union/union all 左连接和右连接的并集。
- 1.通过union连接的sql，它们分别单独取出的列数必须相同；
- 2.不要求合并的表列名称相同时，以第一个sql表列名为准；
- 3.使用union时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用unio进行合并，而是通常采用union all进行合并。
- 4.被union连接的sql子句，单个子句中不用写order by，因为不会有排序的效果，但可以对最终的结果集进行排序。
- 5.union会自动将完全重复的数据去除掉；而union all会保留那些重复的数据；

in 和exits的区别

in语句：只执行一次：确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，In的速度较快。
exists语句：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

in是把外表和内表作hash连接，而exits是对外表作loop循环，每次loop循环再对内表进行查询。

区别：如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in，相反如果外层的主查询记录较少，子查询中的表大，又有索引时使用exits。另外in时不对Null进行处理。
其实区分in和exits主要是造成了驱动顺序的改变。
如果是exits，那么以外层表为驱动表，先查询主查询再将结果和内表的子查询进行对比。详细执行如下：使用exits关键字进行查询的时候，首先，先查询的不是子查询的内容，而是查主查询的表。然后，根据表的每一条记录，再去子查询的内表中依次去判断where后面的条件是否成立；如果成立则返回t否则false。如果返回true的话，则该行结果保留，如果返回的是false，则删除该行，最后将得到的结果返回。
如果是In，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。另外，in时不对Null进行处理。详细执行步骤如下：首先，在数据库内部将先进行查询子查询。然后，将子查询得到的结果和原有的外表做一个笛卡尔积。最后，再根据in设置的条件，将结果进行筛选后返回。

说一下数据库的事务隔离

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

如果不考虑事务的隔离性，会发生脏读、不可重复读、幻读。
读未提交：最低级别，能够读取到其他事务未提交的数据，以上问题均无法解决。
读已提交：读已提交，能够读取到其他事务已经提交的数据，可避免脏读情况发生。
可重复读：确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。
串行化：最严格的事务隔离级别，要求所有事务被串行执行，不能并发执行，可避免脏读、不可重复读、幻读情况的发生。

SQL高级

解决mysql乱码问题

修改mysql配置文件，支持utf-8
修改库和表的字符集

alter database mydb character set'utf8'
alter table mytbl convert to character set'utf8'；

已经乱码的值删除重插或者更新

mysql用户授权、杂项配置（sql_mode）（作为了解）

mysql逻辑架构

查询：应用程序->连接池->缓存（读）和缓存（写）->SQL接口->解析器->优化器->存储引擎
返回：缓存（读）和缓存（写）->应用程序

说一下 mysql 常用的存储引擎

InnoDB ：
- InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 .idb 是数据文件。
- InnoDB 中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。
- InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读；
- 而在 Oracle 数据库中，只支持串行化级别和读已提交这两种级别，其中默认的为读已提交级别。
Myisam :
- Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。
- Myisam 只支持表锁，且不支持事务。Myisam 由于有单独的索引文件，在读取数据方面的性能很高。

对比	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，不适合高并发	行锁（可能死锁）适合高并发
缓存	只缓存索引，不缓存数据	缓存索引和数据，对内存要求高，内存对性能有决定性影响
关注点	节省资源、消耗少、简单业务	并发写、事务、更大资源
默认安装	是	是

MEMORY:（现在使用Redis）
- Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。
- memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
- MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
MERGE:(场景：所有的分表数据)
- Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。
Archive：
- Archive档案存储引擎只支持INSERT和SELECT操作，在MysQL5.1之前不支持索引。
- Archive表适合 日志和数据采集 类应用。
- 根据英文的测试结论来看，Archive表比MySAM表要小大约75%，比支持事务处理的nnoDB表小大约83%
Blackhole：
- Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
CSV：
- CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
- CSV引擎可以作为一种数据交换的机制，非常有用。
Federated
- Federated引擎是访问其他MySQL服务器的一个代理

索引优化分析

1. 性能下降SQL慢、执行时间长、等待时间长

- 数据过多：分库分表
- 关联了太多的表，太多join：SQL优化
- 没有充分利用到索引：索引建立
- 服务器调优及各个参数设置：调整my.cnf

2.什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MysQL高效获取数据的数据结构。
- 索引的本质：索引是数据结构。
- 通俗的说，索引就相当于目录，为了方便查找书中的内容，通过对内容建立索引形成目录。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含对数据表里索引记录的引用指针。
数据库索引，是数据库管理系统中一个排序的数据结构，能够快速查询，更新数据库表中的数据，索引的实现通常使用b+

索引有哪些优缺点

优点：1.大大加快了数据检索速度，这也是创建索引的主要原因 2.使用索引，在查询过程中，可以使用优化隐藏，提高系统性能。
缺点：1.创建索引和维护索引需要花费一些时间，在进行增删改的时候，因为需要维护索引，增删改的效率会有所降低 2.索引会占用一些内存

索引结构

B-Tree
B+Tree

索引算法有哪些

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量， – 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like ‘jack%’;
**-- 如果一通配符开头，或者没有使用常量，则不会使用索引，**例如：
select * from user where name like ‘%jack’; Hash算法

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

Hash索引和B+数索引有什么区别

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相对应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子结点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

1.hash索引进行等值查询更快（一般情况下），但是却无法进行范围查询

因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

2.hash索引不支持使用索引进行排序，原理同上。
3.hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
4.hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
5.hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

B+树性质

1.有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接（叶子节点组成一个链表）。
3.所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
4.通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。
5.同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。

B树性质

一种二叉搜索树。
除根节点外的所有非叶节点至少含有（M/2（向上取整）-1）个关键字，每个节点最多有M-1个关键字，并且以升序排列。所以M阶B树的除根节点外的所有非叶节点的关键字取值区间为[M/2-1(向上取整),M-1]。
每个节点最多有M-1个关键字。

B树和B+树的区别

1.B树每个节点都存储数据，所有节点组成这棵树。B+树只有叶子节点存储数据（B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点），叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。

2.B树中叶节点包含的关键字和其他节点包含的关键字是不重复的，B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。

3.B树中每个节点（非根节点）关键字个数的范围为m/2(向上取整)-1,m-1，并且具有n个关键字的节点包含（n+1）棵子树。B+树中每个节点（非根节点）关键字个数的范围为m/2(向上取整),m，具有n个关键字的节点包含（n）棵子树。

4.B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

聚簇索引与非聚簇索引：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
聚簇索引的顺序就是数据的物理存储顺序。非聚簇索引顺序与数据物理排列顺序无关。
聚簇索引与非聚簇索引

一般来说索引就是如B-Tree这类可以来存储键值方便快速查找的数据结构。
聚簇索引是物理索引，数据表就是按顺序存储的，物理上是连续的。
一旦创建了聚簇索引，表中的所有列都根据构造聚簇索引的关键列来存储。
（我的理解，所有的记录行都根据聚簇索引顺序存储，如按照主键Id递增方式依次物理顺序存储）
因为聚簇索引是按该列的排序存储的，因此一个表只能有一个聚簇索引。

每个InnoDB表都需要一个聚簇索引。该聚簇索引可以帮助表优化增删改查操作。
如果你为表定义了一个主键，MySQL将使用主键作为聚簇索引。
如果你不为表指定一个主键，MySQL将第一个组成列都not null的唯一索引作为聚簇索引。
如果InnoBD表没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。
因此每个InnoDB表都有且仅有一个聚簇索引。

所有不是聚簇索引的索引都叫非聚簇索引或者辅助索引。
在InnDB存储引擎中，每个辅助索引的每条记录都包含主键，也包含非聚簇索引指定的列。
MySQL使用这个主键值来检索聚簇索引。
因此应该尽可能将主键缩短，否则辅助索引占用空间会更大。
一般来说用自增的整数型列作为主键列。

mysql 索引有哪几种类型？

MYSQL数据库索引类型包括普通索引，唯一索引，主键索引与组合索引
单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
- 这是最基本的MySQL数据库索引，它没有任何限制，允许为Null值。
唯一索引
- 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引
- 设定为主键后数据库会自动建立索引，innodb为聚簇索引
复合索引
- 一个索引包含多个列
数据库索引依赖的底层数据结构，B+树
- 每个节点中子节点的个数不能超过 m，也不能小于 m/2；
- 根节点的子节点个数可以不超过 m/2，这是一个例外；
- m 叉树只存储索引，并不真正存储数据，这个有点儿类似跳表；
- 通过链表将叶子节点串联在一起，这样可以方便的按区间查找；
- 一般情况，根节点会被存储在内存中，其他节点存储在磁盘中。

索引的使用场景

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高，高并发条件下倾向组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

什么时候不要使用索引

表记录太少不要建立索引。只有当数据库里已经有足够多的测试数据时，索引才有实际价值。
经常增删改的列不要建立索引
对于有大量重复的列和有空值的列不要建立索引

什么时候索引会失效

-1.在组合索引中不能有列的值为Null，如果有，那么这一列对组合索引就是无效的
-2.在一个select语句这，索引只能使用一次，如果在where中使用了，那么在order by中就不要用了
-3.like操作中，像比如’%aaa&'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引
-4.在索引的列上使用表达式或者函数会使索引失效，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
-5.在查询条件中使用不等于，包括<，>和!=会导致索引失效，特别是对主键索引使用！=不会使索引失效，如果读主键索引或者整数类型的索引使用<或者>不会使索引失效
-6.查询条件中使用is Null或者is not null会导致索引失效
-7.字符串不加单引号会导致索引失效，更准确的说是类型不一致会导致失效。
-8.如果是排序的字段使用了索引，那么select的字段也要是索引字段，否则索引会失效。特别是如果排序的是主键索引则select *也不会导致索引失效

什么是最左前缀原则

最左前缀原则是组合索引非常重要的原则。mysql会一直向右匹配直到遇到匹配查询才会停止匹配。mysql查询优化器会判断纠正这条sql语句以什么样的顺序执行效率最高，最后才能生成真正的执行计划。

比如说联合索引(a,b,c)，为什么b<10 and c<10没有用到索引，而a<10 and c<10用到了
当B+数的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左向右的顺序来建立搜索树的，比如当(张三,20,f)这样的数据来检索的时候，b+树会优先比较name来确定下一步的搜索方向，如果name相同再一次比较age和sex，最后得到检索的数据；但当(20,f)这样的就没有Name的数据的时候。b+树就不知道下一步该查哪一个节点了，因为建立搜索树的时候name是第一个比较因子，必须要先根据Name来搜索才知道下一步去哪里查询，比如当(张三，f)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段ge的缺少，所以只能把名字等于张三的数据都找到，然后再匹配性别是f的数据列，这个是非常重要的性质，即索引的最左前缀原则。

说一下乐观锁和悲观锁

乐观锁(Optimistic Lock), 顾名思义，就是想法很乐观，每次操作数据的时候都认为别人不会修改，所以不会上锁
但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
悲观锁(Pessimistic Lock), 顾名思义，就是想法很悲观，每次操作数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想操作数据就会block直到它自己拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

共享锁和排他锁

-共享锁，就是对多个不同的事务，对同一资源共享同一个锁。如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。通过在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。
-排它锁，就是对多个不同的事务，对同一资源只能有一把锁。如果事务T对数据A加上了排它锁，则其他事务就不能对A加上任何类型的锁。获取排它锁的事务既能读数据，又能修改数据源。在需要执行的语句后面加上for update就可以了

MVCC多版本并发控制

Multi-Version Concurrency Control，多版本并发控制。目的在于提高数据库高并发场景下的吞吐性能。
基本思想：MVCC的实现，通过保存数据在某个时间点的快照来实现。这意味着有一个事务无论运行多长时间，在一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。
–基本特征
—1.每行数据都存在一个版本，每次数据更新时都更新该版本
—2.修改时Copy出当前版本随意修改，各个事务之间无干扰
—3.保存时比较版本号，如果成功提交，则覆盖原纪录；失败则放弃copy，回滚

–InnoDB存储引擎MVCC的实现策略
—在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。
—每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。
–补充：
—1.MVCC手段只适用于mysql隔离级别中读已提交和可重复读
—2.不可重复读由于存在脏读，既能读到未提交事务的数据行，所以不适用MVCC.这是由于mvcc的创建版本和删除版本只要在事务提交后才会产生。
—3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

基于快照隔离的并发控制MVCC

快照隔离是多版本并发控制mvcc的一种实现方式。
–其核心思想是：数据库为每个数据项维护多个版本，每个事务只对属于自己的私有快照进行更新，在事务真正提交前进行有效性检查，使得事务正常提交更新或者失败回滚。
–由于快照隔离导致事务看不到其他事务对数据项的更新，为了避免出现丢失更新问题，可以采用以下两种方案避免：
—1.先提交者获胜：对于执行该检查的事务t，判断是否有其他事务已经将更新写入数据库，是则t回滚，否则t正常提交。
—2.先更新者获胜：通过锁机制保证第一个获得锁的事务提交其更新，之后试图更新的事务中止。
事务间可能冲突的操作通过数据项的不同版本的快照相互隔离，到真正要写入数据库时才进行冲突检测。所以是一种乐观并发控制。

基于时间戳的并发控制流程

核心思想:对于并发可能冲突的操作,基于时间戳排序规则选定某事务继续执行,其他事务回滚。

系统会在每个事务开始时赋予其一个时间戳,这个时间戳可以是系统时钟也可以是一个不断累加的计数器值,当事务回滚时会为其赋予一个新的时间戳,先开始的事务时间戳小于后开始事务的时间戳。

基于时间戳排序和基于锁实现的本质一样:对于可能冲突的并发操作,以串行的方式取代并发执行,因而它也是一种悲观并发控制。它们的区别主要有两点:

基于锁是让冲突的事务进行等待,而基于时间戳排序是让冲突的事务回滚。
基于锁冲突事务的执行次序是根据它们申请锁的顺序,先申请的先执行;而基于时间戳排序是根据特定的时间戳排序规则。

基于有效性检查的并发控制

核心思想:事务对数据的更新首先在自己的工作空间进行,等到要写回数据库时才进行有效性检查,对不符合要求的事务进行回滚。

基于有效性检查的事务执行过程会被分为三个阶段:

读阶段:数据项被读入并保存在事务的局部变量中。所有write操作都是对局部变量进行,并不对数据库进行真正的更新。
有效性检查阶段:对事务进行有效性检查,判断是否可以执行write操作而不违反可串行性。如果失败,则回滚该事务。
写阶段:事务已通过有效性检查,则将临时变量中的结果更新到数据库中。

有效性检查通常也是通过对事务的时间戳进行比较完成的,不过和基于时间戳排序的规则不一样。

该方法允许可能冲突的操作并发执行,因为每个事务操作的都是自己工作空间的局部变量,直到有效性检查阶段发现了冲突才回滚。因而这是一种乐观的并发策略。

说一下 mysql 的行锁和表锁

表锁：
- 特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定颗粒大，发生锁冲突的概率最高，并发度最低
加读锁（共享锁）
- 我们加读锁的这个进程可以读加读锁的表，但是不能读其他的表。
- 加读锁的这个进程不能update加读锁的表。
- 其他进程可以读加读锁的表（因为是共享锁），也可以读其他表
- 其他进程update加读锁的表会一直处于等待锁的状态，直到锁被释放后才会update成功。
加写锁（独占锁）
- 加锁进程可以对加锁的表做任何操作（CURD）。
- 其他进程则不能查询加锁的表，需等待锁释放
行锁：
- 特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定颗粒最小，发生锁冲突的概率最低，并发度最高
- 行为：当我们对一行进行更新但是不提交的时候，其他进程也对该行进行更新则需要进行等待；
- 如果我们对一行进行更新，其他进程更新别的行是不会受影响的。
行锁升级为表锁：
- 当我们的行锁涉及到索引失效的时候，会触发表锁的行为。
间隙锁：
- 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
- 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

Mysql数据库优化

-1.首先可以使用explain或者describe命令分析一条查询语句的执行信息，其中会显示索引和查询数据读取数据条数等信息
-2.优化子查询 :在Mysql中，尽量使用join来替代子查询，因为子查询需要嵌套查询，嵌套查询时会建立一张临时表，临时表的简历和删除都会他较大的系统开销，而连接查询不会创建临时表，因此效率比嵌套查询高
-3.使用索引：索引是提高数据库查询速度最重要的方法之一，但也要符合一定的要求：
—1.对于那些经常被查询的字段需要创建索引
—2.经常出现在where，between and子句中的列要创建索引
—3.对于有大量重复值的数据段不应该创建索引
—4.对于组合索引必须要满足最左匹配原则，在Mysql中，会一直向右匹配，直到遇到符号查询为止。
—5.索引要尽量的短。对于那些长字符串，要设置前缀，这样有利于避免内存的浪费，同时有利于提高查询效率。
-4.分解表：对于字段比较多的表，如果某些字段使用频率较低，此时应该，将其分离出来从而形成新的表。
-5.中间表：对于将大量连接查询的表可以创建中间表，从而减少在查询时造成的连接耗时
-6.增加冗余度：类似于创建中间表，增加冗余也是为了减少连接查询
-7.分析表、检查表、优化表：分析表主要是分析表中的关键字的分布，检查表主要是检查表中是否存在错误，优化表主要是消除删除或更新造成的表空间浪费。

语句相关

常见命令

show databases：显示当前连接下所有数据库
show tables显示：当前库中所有表
show tables from库名：显示指定库中所有表
show columns from 表名：显示指定表中所有列
use 库名：打开/使用指定库
create 表名：创建表
drop 表名：删除表

语法规范

不区分大小写
每条命令结尾建议用分号
注释：
- #单行注释
- -单行注释
- /多行注释/

DQL: Data Query Language

1. 基础查询

语法：
- select 列名 from 表名;
特点：
- 查询结果集是一个虚拟表
- 查询列表可以是单个字段、多个字段、常量、表达式、函数，可以是以上的组合
引申1：起别名
- select 列名 as "别名" from 表名;
- select 列名 "别名" from 表名;
引申2：+的作用：加法运算
- 如果两个操作数都是数值型，则直接做加法运算
- 如果其中一个为非数值型，则将强值转换成数值型，如果转换失败，则当做0
- 如果其中一个为null，则结果直接为null
引申3：去重
- select distinct department_id from employees;
引申4：补充函数
- select database();显示当前数据库
- select version();显示当前版本
- select user();显示当前用户
- select ifnull(列名，表达式);指定列名为空时的值
- select concat(字符1，字符2，字符3，100);连接字符
- select length(字符/字段);获取字节长度

2. 条件查询

语法：

select 列名
from 表名
where 条件;

特点：
1. 条件表达式
  - 条件运算符：>、>=、 <、 <=、 =、 <>
2. 逻辑表达式
  - 逻辑运算符：and、or、not
3. 模糊查询（ 以下关键字都可以和not搭配使用 ）
  - like：搭配通配符，_表示任意单个字符，%表示任意多个字符
  - in：判断字段在某一列表
  - between and：判断字段在某一区间
  - is null：判断null值

3. 排序查询

语法：

select 列名
from 表名
where 条件
order by 排序列表;

特点：
- 排序列表可以是单个字段、多个字段、别名、表达式、函数、列的索引，可以是以上的组合
- 升序：asc (默认)
- 降序：desc

4. 常见函数

1.单行函数
- 字符函数
    - concat(str1,str2)：拼接字符串
    - substr(str,起始索引，字符长度)： **起始索引从1开始** 
    - instr(str,substr)：子串第一次出现的位置
    - length(str)：字节长度
    - char_length：字符个数
    - trim('x' from 'str')：去前后指定字符，默认去空格
    - strcmp(str1,str2)：大于：1，等于：0，小于：-1
    - upper/lower(str)：转为大小写
    - left/right(str,长度)：从左/右截取指定长度
    - lpad/rpad(str,长度,'x')：用指定字符在左/右边填充字符串，如果短则截取
- 数学函数
    - abs(num)：绝对值
    - ceil(num)：向上取整
    - floor(num)：向下取整
    - round(num,精读)：四舍五入
    - truncate(num,精读)：截断取整
    - mod(num)：取模 a%b=a/b*b
- 日期函数
    - now()：当前系统日期时间
    - curtime()：当期时间
    - curdate()：当前日期
    - datediff()：日期之差
    - date_format('1996-10-1','%Y年%M月%d日 %H时%i分%s秒')：
    - str_to_date('10/1 1996','%m/%d %Y')：解析字符串为日期
- 流程控制函数
    - if(表达式,'结果1','结果2')：表达式为真，返回结果1，否则结果2
    - case 
    ```mysql
      case 表达式                    case
      when 值1 then 结果1            when 条件1 then 结果1
      when 值2 then 结果2            when 条件2 then 结果1
      else 结果n                     else 结果n
      end                            end
    ```
2. 分组函数/聚合函数/统计函数
- sum(字段名)：求和
- avg(字段名)：求平均
- max(字段名)：求最大
- min(字段名)：求最小
- count(字段名)：求非空字段个数
    - count(*)：统计行数
    - count(distinct  department_id)：去重统计
- 分组函数用于分组查询使用

5. 分组查询

语法：

select count(字段名1),字段名2
from 表名
where 条件
group by 字段名2;
having count(*)>5

where 不支持分组函数，要是用having

实例

每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job id，MAX（salary）
FROM emplovees 
WHERE commission_pct Is NOT NULL 
GROUP BY job id 
HAVING MAX（salary）>12000；

SQL语句定义的顺序

(1) SELECT (2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5) ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP}
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

LIMIT：限制输出指定条数的字段（索引从零开始）

LIMIT offset,size//从offset开始显示size条数据
LIMIT 5,10; // 检索记录行 6-15
LIMIT 95,-1; // 检索记录行 96-last
LIMIT 5; //检索前 5 个记录行

SQL语句执行顺序

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

6. 连接查询

(一)sql92语法

等值连接（判断条件为等式）

特点：
- 多表等值连接的结果为多表的交集部分
- n表连接，至少需要n-1个连接条件
- 多表的顺序没有要求
- 一般需要为表起别名，起了别名就不能使用原表名
- 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

#案例：查询哪个部门的员工个数>5，并按员工个数进行降序
SELECT department_name，COUNT（*）
FROM employees e，departments d 
WHERE e.department_id = d.department_id 
GROUP BY e.department_id 
HAVING 个数>5
ORDER BY 个数 DESC；

非等值连接（判断条件为非等式）

#案例：查询哪个部门的员工个数>5，并按员工个数进行降序
SELECT salary, grade_level 
FROM employees e, job_grades g 
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接（自己的表查询两遍）

#案例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name, m.employee_id, m.last_name 
FROM employees e, employees m 
WHERE e. manager_id'=m.' employee_id';

(二)sql99语法

SELECT 查询列表
FROM 表1 别名 
【连接类型】join 表2 别名
on 连接条件
where 筛选条件
group by
havaing
order by;

【连接类型】

内连接
- inner
外连接
- left outer
- right outer
- full outer
交叉连接
- cross

内连接（查询交集）

SELECT 查询列表
FROM 表1 别名 
inner join 表2 别名
on 连接条件

等值

#3.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city ,COUNT（*）部门个数
FROM departments d 
INNER JOIN locations l
ON d.location_id=l.location_id GROUP BY city 
HAVING COUNT（*）>3；

非等值

#案例：查询哪个部门的员工个数>5，并按员工个数进行降序
SELECT salary, grade_level 
FROM employees e
inner join job_grades g 
ON salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接

#案例：查询员工名和上级的名称
SELECT e.last_name, m.last_name 
FROM employees e
inner join employees m 
WHERE e. manager_id =m.employee_id;

外连接（查询差集）

应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=左外连接与右外连接的并集
5、交叉连接：两个表的笛卡尔乘积（两个表的字段进行全匹配）

#案例1：查询哪个部门没有员工(左外)
SELECT d.*，e.employee_id 
FROM departments d 
LEFT OUTER JOIN employees e 
ON d.department id=e.department_id 
WHERE e.employee_id IS NULL；

查询哪个部门没有员工(右外)
SELECT d.*，e.employee_id 
FROM  employees e 
RIGHT OUTER JOIN departments d
ON d.department id=e.department_id 
WHERE e.employee_id IS NULL；

查询哪个部门没有员工(全外)
SELECT d.*，e.employee_id 
FROM  employees e 
FULL OUTER JOIN departments d
ON d.department id=e.department_id ；

(三)总结

SQL92和SQL99 pk
- 功能：SQL99支持的较多
- 可读性：SQL99实现连接条件和筛选条件的分离，可读性较高
使用场景：

A表与B表公有内容

SELECT <select list>
FROM A 
INNERJOIN B 
ON A.key=B.key ;

A表全部内容，B表补null

SELECT <select list>
FROM A 
LEFT JOIN B 
ON A.key=B.key ;

B表全部内容，A表补null

SELECT <select list>
FROM A 
RIGHT JOIN B 
ON A.key=B.key ;

只有A表的内容

SELECT <select list>
FROM A 
LEFT JOIN B 
ON A.key=B.key 
WHERE B.key is null;

只有B表的内容

SELECT <select list>
FROM A 
RIGHT JOIN B 
ON A.key=B.key 
WHERE A.key is null;

A表+B表全部内容

SELECT <select list>
FROM A 
FULLJOIN B 
ON A.key=B.key;

A表+B表全部内容，去掉交集

SELECT <select list>
FROM A 
FULLJOIN B 
ON A.key=B.key 
WHERE A.key is null
OR B.key is null;

7. 子查询

说明：当一个查询语句中又嵌套了另一个充整的select语句，则被嵌套的select语句称为子查询或内查询，外面的select语句称为主查询或外查询.
子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

#示例：
select first name from employees where department_id>(
    select department id from departments 
    where location id=1700
)

按子查询出现的位置进行分类：
1. select后面
  - 要求：子查询的结果为单行单列（标量子查询）
2. from后面
  - 要求：子查询的结果可以为多行多列（表子查询）
3. where或having后面 ★
  - 要求：子查询的结果必须为单列（标量子查询、行子查询、列子查询）
4. exists后面
  - 要求：子查询结果必须为单列（相关子查询）
按结果集的行列数不同：
1. 标量子查询（结果集只有一行一列）
2. 列子查询（结果集只有一列多行）
3. 行子查询（结果集有一行多列）
4. 表子查询（结果集一般为多行多列）
特点：
1. 子查询放在条件中，要求必须放在条件的右侧
2. 子查询一般放在小括号中
3. 子查询的执行优先于主查询
4. 行子查询对应了单行操作符：> < >= <= = <>
  列子查询对应了多行操作符：any/some all in

（一）放在where后面

标量子查询，子查询必须为标量值，如果为多个或者不存在则出错

#案例1：谁的工资比Abel高？
#①查询Abel的工资
SELECT salary 
FROM employees 
WHERE last_name ='Abel'
#②查询员工的信息，满足salary>①结 果
SELECT FROM employees WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name ='Abel'
)；

列子查询，使用多行比较符
IN/NOT IN：等于列表中的任意一个
ANY|SOME：和子查询返回的某一个值比较
ALL：和子查询返回的所有值比较

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id 
FROM departments 
WHERE 1ocation_id IN（1400，1700）
#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name 
FROM employees 
WHERE department_id IN(
    SELECT DISTINCT department_id 
    FROM departments 
    WHERE location_id IN(1400，1700)
);

行子查询

#案例：查询员工编号最小并且工资最高的员工信息
SELECT*
FROM employees 
WHERE（employee_id，salary）=（
    SELECT MIN（employee_id），MAX（salary）
    FROM employees
）

（二）放在select后面，仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*，（
    SELECT COUNT（*）
    FROM employees e 
    WHERE e.department_id = d.department_id
）个数
FROM departments d；

（三）放在select后面，子查询充当一张表，必须起别名

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG（salary），department_id 
FROM employees 
GROUP BY department_id 

SELECT ag_dep.*,g. grade_level 
FROM (
    SELECT AVG (salary) ag, department_id 
    FROM employees 
    GROUP BY department_id 
) ag_dep 
INNER JOIN job_grades g 
ON ag_dep. ag 
BETWEEN lowest_sal AND highest_sal;

（四）放在exists后面，先执行主查询，exists语句为筛选结果

exists(语句)，语句结果存在为1，否则为0

SELECT department_name 
FROM departments d 
WHERE EXISTS (
    SELECT *
    FROM employees e 
    WHERE d.department_id=e.department_id
);

8. 分页查询

LIMIT：限制输出指定条数的字段（ 索引从零开始 ）
- LIMIT offset,size //从offset开始显示size条数据
- LIMIT 5,10; // 检索记录行 6-15
- LIMIT 95,-1; // 检索记录行 96-last
- LIMIT 5; //检索前 5 个记录行
要显示的页数page，每页的条目数size
- LIMIT (page-1)*size,size

9. 联合查询

union 联合合并：多条语句的结果合并为一个结果

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90；
SELECT * FROM employees WHERE email LIKE'%a%' 
UNION 
SELECT * FROM employees WHERE department_id>90

查询对象为多个没有直接关系的表，但查询对象一致时，可以用联合查询
特点：
- 多条查询语句的列数一致
- 要求查询语句的类型好顺序最好一致
- union默认去重，union all显示全部

DDL: Data Define Language

(一)库的管理

#1.创建数据库
CREATE DATABASE stuDB；
CREATE DATABASE IF NOT EXISTS stuDB；
#2.删除数据库
DROP DATABASE stuDB；
DROP DATABASE IF EXISTS stuDB；

(二)表的管理

#1.创建表

CREATE TABLE 【IF NOT EXISTS】 表名（
    字段名 字段类型 【字段约束】，
    字段名 字段类型 【字段约束】，
    字段名 字段类型 【字段约束】
）；

字段类型参考

1、整型
TINYINT SMALLINT INT BIGINT
2、浮点型
FLOAT（m，n）
DOUBLE（m，n）
DECIMAL（m，n）
m和n可选
3、字符型
CHAR（n）：n可选
VARCHAR（n）：n 必选，n表示最多字符个数
TEXT
4、日期型
DATA TIME DATETIME TIMESTAMP
5、二进制型
BLOB 存储图像

字段约束参考

类型	非空约束	默认约束	主键约束	外键约束	唯一约束	检查约束	自增约束
关键字	not null	default	primary key	foregin key	unique	check	auto_increment
说明	非空	有默认值	不能重复且非空，且仅有一个（可以是组合主键）	用于从表参照，要求列为主键或唯一	唯一	检查条件	自增且为数值型，至多存在一个自增长列

#2.修改表

#语法
ALTER TABLE 表名 ADD/MODIEY/CHANGE/DROP COLUMN 字段名 字段类型 字段约束；
#修改表名
ALTER TABLE 表名 RENAME TO 表名

#3.删除表

DROP DATABASE IF EXISTS 表名；

#4.复制表

#仅复制结构，不复制内容
CREATE TABLE 表名1 LIKE 表名2；
#仅复制结构和内容
CREATE TABLE 表名1 SELECT 字段 FROM 库名.表名；

#案例：复制employees表中的last_name，department_id，salary字段到新表emp表，但不复制数据
CREATE TABLE emp 
SELECT last_name，department_id，salary 
FROM myemployees.employees 
WHERE 1=2；

DML: Data Manipulation Language

(一)数据的插入

insert into 表名（字段名1，字段名2...） values（值1，值2...）;
字段满足约束时值可选 null，default

字段和值列表一一对应，包含类型、约束等必须匹配
数值型的值，不用单引号。非数值型的值，必须使用单引号
字段顺序无要求

(二)数据的更新

1.修改单表的记录
语法：
update 表名
set 列=新值，列=新值，..
where 筛选条件；
2.修改单表的记录
sq192语法：
update 表1别名，表2别名
set 列=值，...
where 连接条件
and 筛选条件；
sql99语法：
update 表1 别名
【连接类型】 join 表2 别名
on 连接条件
set 列=值，..
where 筛选条件；

(三)数据的删除

delete语句（条件删除）
语法：delete from 表名 where 筛选条件；
truncate语句(删除所有)
语法：truncate table 表名；

#【面试题】delete和truncate的区别  
1.delete可以添加WHERE条件TRUNCATE不能添加WHERE条件，一次性清除所有数据  
2.truncate的效率较高  
3.如果删除带自增长列的表，使用DELETE删除后，重新插入数据，记录从断点处开始   
    使用TRUNCATE删除后，重新插入数据，记录从1开始  
4.delete删除数据，会返回受影响的行数TRUNCATE删除数据，不返回受影响的行数  
5.delete删除数据，可以支持事务回滚TRUNCATE删除数据，不支持事务回滚

演示事务的使用步骤

#1、取消事务自动开启
SET autocommit = 0；
#2、开启事务
START TRANSACTION；
#3、编写事务的sql语句
#4、结束事务
#提交
COMMIT；
【#回滚
ROLLBACK；】

实际相关

怎么验证 mysql 的索引是否满足需求？

在select语句前加上explain

mysql 问题排查都有哪些手段

使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。

如何做 MySQL 的性能优化？

为搜索字段创建索引。
避免使用 select *，列出需要查询的字段。
垂直分割分表。
选择正确的存储引擎。

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

一般情况下，我们创建的表是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启mysql的话，这条记录的id是6。
- 因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
如果使用的是MyISAM，那么这条记录的id就是8。
- 因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。
如果在这7条记录里面删除的是中间的几个记录（比如删除的是3,4两条记录），重启MySQL数据库后，insert一条记录后，ID都是8。
- 因为内存或者数据库文件存储都是自增主键最大ID

如何获取当前数据库版本？

select version();
mysql -V

char 和 varchar 的区别是什么？

char类型的长度是固定的，varchar的长度是可变的。
这就表示，存储字符串’abc’，
- 使用char(10)，表示存储的字符将占10个字节（包括7个空字符）
- 使用varchar2(10)，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。
char类型的效率比varchar的效率稍高