Mysql基础知识

Mysql基础知识汇总，包含索引，事务，面试常见问题，底层原理等…

Mysql

什么是数据库

数据库是一种有效地管理大量的、安全的、并发的、关联的、一致的数据工具；而文件保存数据会存在安全问题，不利于查询和对数据的管理，也不利于存放海量数据；因此，引入了数据库的概念，数据库更便于我们去操纵和管理数据，做一些我们想要的改变。

什么是sql什么是mysql

SQL是一种用于操作数据库的查询语言，而MySQL是一种数据库软件。MySQL是一个DBMS（DataBaseManagementSystem）数据库管理系统，是一个用来管理数据库文件的软件；而SQL就是用于管理DBMS数据的，即访问，更新和操作数据库中的数据。

超键、候选键、主键、外键的概念

超键，候选键，主键，外键区别知识概述

  • 超键：只要在关系中含有能唯一标识的元组属性的集合就叫做超键

  • 候选键：不含有多余属性，只含有能唯一标识元组的属性就叫做候选键

  • 主键：用户从众多候选键中选出来的一个键就是主键

  • 外键：相对于两个表或多张表而言。如果表R中属性K是其他表的主键，那么K在表R中称为外键。

作用

  1. 保证实体的完整性
  2. 加快数据库的操作速度
  3. 在表中添加新记录时，表会自动检查新纪录的主键值，不允许该值与其他记录主键值重复
  4. 表自动按主键值的顺序显示表中的记录，如果没有定义主键，则按输入记录的顺序显示表中的记录

数据库的三大范式是什么

  1. 第一范式（1NF）：数据库表中的字段具有原子性,不可再分，同一列中不能有多个值。所有关系型数据库系统都满足第一范式。
  2. 第二范式（2NF）：在第一范式的基础上，要求实体的属性完全依赖于主关键字（表中要有一列属性将实体完全区分）。比如在员工表中，通过员工编号将员工区分。
  3. 第三范式
    ：在第二范式的基础上，要求非主键列必须直接依赖于主键，不能存在传递依赖（一张数据库表中不包含已在其他表中已包含的非主关键字信息）。比如一个表中有<
    学号，姓名，课程号，成绩>，其中成绩是由学号+课程号决定的，因此将表分为两个表<学号，姓名，课程号>，<学号，课程号，成绩>。

mysql常见的数据类型

  • 整数：smallint（2字节，0 ~ 2^16 -1）、int（4字节，0 ~ 2^32-1）;
  • 浮点数：float（4字节）、double（8字节）；
  • 定点数：decimal(m，d)（M+2字节）；
  • 日期类型：date（YYYY-MM-DD）、time（HH:MM:SS）、year（YYYY）、datetime（YYYY-MM-DD HH:MM:SS）、timestamp（YYYYMMDD HHMMSS）
  • 字符型：char（0 ~ 255）、varchar（0 ~ 255）、text（0 ~ 65535(2^16-1)）、longtext（0 ~ 2^32-1）
  • BLOB类: 存储二进制数据

MySQL中，BLOB是个类型系列，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大大小上不同。

MySQL的四种BLOB类型 : TinyBlob 最大 255 , Blob 最大 65K MediumBlob 最大 16M ,LongBlob 最大 4G

引擎

MyISAM 和 InnoDB的区别

  1. InnoDB（mysql5.5版本之后）支持事务处理、外键与行级锁，而MyISAM不支持；
  2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎，该引擎是为了处理大数据容量的数据库系统；
  3. MyISAM的读性能比InnoDB好，但修改性能比InnoDB差；
  4. MyISAM可被压缩，存储空间较小，InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引；

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键(
比如学号，姓名)，会使得数据随机插入，效率比较差。

InnoDB的四大特性

特性详细说明

  1. 插入缓冲（insert buffer)

    mysql对于非聚集索引的插入,先去判断要插入的索引页是否已经在内存中了，如果不在，暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert
    Buffer对象中，临时先放在这，然后等待情况，等待很多和现在情况一样的非聚集索引，再和要插入的非聚集索引页合并，比如说现在Insert
    Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。这样就提升了效率和插入性能，减少了随机IO带来性能损耗

    1
    2
    3
    insert Buffer 只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是减少了随机IO带来性能损耗

    插入缓冲的条件：非聚集索引，非唯一索引

  2. 双写机制(double write)

    首先会将（memcpy函数）Page刷到InnoDB tablespace的一个区域中。
    再将数据分别刷到一个共享空间和真正应该存在的位置。

  3. 自适应哈希索引(ahi)

    InnoDB存储引擎会监控对表上辅助索引页的查询。如果观察到建立hash索引可以提升性能，就会在缓冲池建立hash索引，

  4. 预读(read ahead)

    预料这些页会马上被读取到。预读请求的所有页集中在一个范围内

B+ Tree索引和Hash索引区别

  1. 哈希索引适合等值查询，但是不无法进行范围查询 哈希索引没办法利用索引完成排序,哈希索引不支持多列联合索引的最左匹配规则
    。如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题.
  2. 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

存储引擎的选择

  1. 若数据库平台读多写少，可以选择MyISAM引擎，如博客系统、学习网站等；
  2. 若数据库平台更新操作（增删改）较多，则选择InnoDB；

索引

一篇文章搞懂索引

索引的使用场景:

  1. 当表记录较少、经常增删改的表或数据重复且分布平均的表字段不需要使用索引，用了反而占存储空间；
  2. 对于索引的添加或删除操作，MySql数据库会先创建一张临时表，然后把数据导入临时表中，删除原表，再把临时表名改为原来的表名。所以，增加和删除索引有成本。

需要建立索引的情况

  1. 主键自动建立唯一索引；
  2. 频繁作为查询的条件的字段应该创建索引；
  3. 查询中与其他表关联的字段，使用外键关系建立索引；
  4. 查询中排序的字段，通过索引可以大大提高排序的速度；
  5. 查询中统计或者分组字段；

聚簇索引、非聚簇索引、覆盖索引

  • 聚簇索引: 索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。
  • 非聚簇索引:而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。
  • 覆盖索引 : 一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖.

索引有哪些类型:

  1. 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一
  2. 聚集索引：定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
  3. 非聚集索引：唯一索引、普通索引、主键索引、全文索引
  4. UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
  5. INDEX(普通索引)：允许出现相同的索引内容
  6. PROMARY KEY(主键索引)：不允许出现相同的值
  7. fulltext index(全文索引)：可以针对值中的某个单词，但效率不是很好

索引怎么优化查询?索引为什么要使用B+树:

  1. B+树更有利于对数据库的扫描 。B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题，而 B+
    树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的 range query，B+树有着更高的性能。
  2. B+树的磁盘读写代价更低 。B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B
    树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字 数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说
    I/O 读写次数也就降低了。
  3. B+树的查询效率更加稳定 。由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。
    所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引下推：

索引下推主要是用来优化SQL查询语句

1
　SELECT * from user where  name like '陈%' and age=20
  • 没有索引下推的时候，先匹配name，然后拿着取出来的多个结果再多次回表找age=20的；
  • 有索引下推的时候，匹配到name后，在索引内部就判断了age是否等于20，不等于直接t跳过。找到结果后拿着主键回表找到全部数据。

二叉查找树,平衡二叉树,B树,B+树

二叉查找树

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下： 1.
将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。 2.
继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。 3.
把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

劣势：不稳定，可能出现之在一边或者两边树的高度查太大

平衡二叉树

如果上面的二叉查找树是这样的构造：

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。
导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。 为了解决这个问题，我们需要*
*保证二叉查找树一直保持平衡**，就需要用到平衡二叉树了

平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树相差的高度不能超过1

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。

**劣势：时间复杂度与树的高度有关，树的高度直接决定了IO次数，性能会很差；不支持范围快速查找，找到一个还要从根节点再找下一个，查询效率低。
**

B树

因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量
减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。
如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。
B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。

注意： 图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。 –
图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)
，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。
基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。
假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下： 1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。 3.
将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

注意： B树的构造是有一些规定的，但这不是本文的关注点，有兴趣的同学可以令行了解。 – B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的。

B+树

根据上图我们来看下B+树和B树有什么不同。

  1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

  2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
    有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是
    说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

B+树的特点:

  1. 所有叶子节点中的关键字按大小顺序排序；
  2. 相邻的叶子节点按顺序链接（相当于构成一个顺序链表）；
  3. B+树的内部节点只存放键，不存放值，叶子节点同时存放键和值；

B树的特点:

  1. B树的内部节点和叶子节点都存放键和值；
  2. B树的叶子节点各自独立

B树和B+树的区别:

  1. 内部节点中，关键字的个数与其子树的个数相同，不像 B 树种，子树的个数总比关键字个数多 1 个 ；
  2. 所有指向文件的关键字及其指针都在叶子节点中，不像 B 树，有的指向文件的关 键字是在内部节点中。换句话说，B+树中，内部节点仅仅起到索引的作用；
  3. B+在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支，B+为了找到这个关键字的指针；

什么是最左匹配原则:

  • 利用最左前缀：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
    如果建立(a,b,c,d)顺序的索引，那么后面的d是用不到索引的，如果建立(a,b,d,c)的索引就可以用到。
  • 所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

最左匹配原则原理

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL
语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select *
from t
where a = 1
  and b = 1
  and c = 1; #这样可以利用到定义的索引（a,b,c）
select *
from t
where a = 1
  and b = 1; #这样可以利用到定义的索引（a,b,c）
select *
from t
where a = 1; #这样也可以利用到定义的索引（a,b,c）
select *
from t
where b = 1
  and c = 1; #这样不可以利用到定义的索引（a,b,c）[优化器]
select *
from t
where a = 1
  and c = 1; #这样不可以利用到定义的索引（a,b,c）[优化器]

也就是说通过最左匹配原则你可以定义一个联合索引，但是使得多中查询条件都可以用到该索引。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配

也就是：

1
2
3
4
5
select *
from t
where a = 1
  and b > 1
  and c = 1; #这样a,b可以用到（a,b,c），c不可以

这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。

最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。

我们都知道索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的健值数量不是一个，而是多个。构建一颗 B+
树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。
例子：假如创建一个（a,b,c)的联合索引，那么它的索引树是这样的：

a ab abc

该图就是一个形如(a,b,c)联合索引的 b+ 树，其中的非叶子节点存储的是第一个关键字的索引 a，而叶子节点存储的是三个关键字的数据。这里可以看出
a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。

通过对联合索a,b可以用到（a,b,引的结构的了解，那么就可以很好的了解为什么最左匹配原则中如果遇到范围查询就会停止了。以
select * from t where a=5 and b>0 and c =1; #这样``c），c不可以 为例子，当查询到 b 的值以后（这是一个范围值），c
是无序的。所以就不能根据联合索引来确定到低该取哪一行。

总结

  • 在 InnoDB 中联合索引只有先确定了前一个（左侧的值）后，才能确定下一个值。范围查询的话，那么联合索引中使用范围查询的字段后的索引在该条
    SQL 中都不会起作用。****如果有

  • 值得注意的是，in 和 = 都可以乱序，比如有索引（a,b,c），语句 select * from t where c =1 and a=1 and b=1
    ，这样的语句也可以用到最左匹配，因为 MySQL 中有一个优化器，他会分析 SQL 语句，将其优化成索引可以匹配的形式，即
    select * from t where a =1 and a=1 and c=1

事务

  • 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，
    要么都执行，要么都不执行。

有一个经典的栗子就是转账，比如小明要给小方转100块钱，如果成功了，那么小明的账户余额信息和小方的账户余额信息都需要更新；但是如果转账过程中突然银行系统崩溃，导致小明的余额少了，小方的余额多了，这就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

事务的四大特性(ACID)

  1. **原子性(Atomic)**： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. **一致性(Consistent)**： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. **隔离性(Isolated)**： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. **持久性(Durable)**： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

什么是脏读?幻读?不可重复读?

  • 脏读：事务2读取时，读到了事务1未提交修改的数据，这个数据可能是废弃的数据，所以事务2就读到了脏数据。
1
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
  • 不可重复读：事务2在事务1第二次读取数据前修改了数据，导致事务1前后两次读取数据不一致。
1
在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不 可重复读
  • 幻读：事务2在事务1第二次读取数据前提交了数据，导致读到的数据条数不一致。
1
第一个事务查询一个User表id=100发现不存在该数据行，这时第二个事务又进来了，新增了一条id=100的数据行并且提交了事务。这时第一个事务新增一条id=100的数据行会报主键冲突，第一个事务再select一下，发现id=100数据行已经存在，这就是幻读。
  • 不可重复读指的是读到值的不同，幻读指的是数据条数的不同。

什么是事务的隔离级别?Mysql的默认隔离级别是什么?

  • READ Uncommitted未提交读：事务中的修改，即使未提交，对其他事务也都是可见的；
  • Read Committed提交读：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的；大多数数据库默认采用此级别； 解决脏读
  • Repeatable Read可重复读：解决了脏读问题；该级别保证了在同一事务中多次读取同样记录的结果是一致的；MySQL默认为此级别；解决脏读和不可重复读
  • Serializable可串行化：最高的隔离级别。该级别会在读取的每一行数据上都加锁，导致大量的超时和锁争用的问题。但用的比较少，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑此级别。全部解决

锁

按照锁的粒度分数据库锁有哪些

Lock锁根据粒度主要分为表锁、行锁、页锁，不同的存储引擎拥有的锁粒度都不同。

  1. 表锁
    ：表级锁就是一次表进行会将整个锁定，是个存储引擎中最大颗粒度的锁机制；它的特点是实现逻辑简单，资源消耗较少，获取锁和释放锁的速度很快，很好的解决了死锁问题。使用表锁的主要是MyISAM、MEMORY，CSV等一些非事务性存储引擎。
  2. 行锁：MySQL中锁定粒度最小的锁机制，特点是发生锁定资源争用的概率小，能给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能，但资源消耗较大。使用行锁的主要是InnoDB引擎。
  3. 页锁：它的锁定颗粒度介于表锁和行锁之间，它的并发能力与资源开销也是介于两者之间，另外，它与行锁一样，会发生死锁。使用页锁的主要是BerkeleyDB存储引擎。

从锁的类别上分Mysql有哪些锁

从类别上锁分为：共享锁（S Lock）和排它锁（X Lock）。

  • 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
  • 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

锁的粒度具体取决于存储引擎，InnoDB实现了行锁，页锁，表锁；他们的资源开销从大到小，并发性能也从大到小。

Mysql中InnoDB引擎的行锁怎么实现的?

InnoDB是通过索引来实现行锁的。

例如：select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起。

innoDB存储引擎的锁算法有哪些?

  1. Record lock：单个行上记录的范围。它会锁住索引记录，如果InnoDB存储引擎建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定；
  2. Gap lock：间隙锁，锁定一个范围，但不包含记录本身；
  3. Next-Key lock：Record+Gap，锁定范围+记录本身；InnoDB对于行的查询都是采用这种锁定算法。

死锁如何解决?

  1. 对于事物之间由于资源访问顺序导致的死锁，则可以约定以相同的顺序访问表；
  2. 对于并发修改同一记录导致的死锁，可以使用乐观锁或悲观锁进行控制；
  3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
  4. 避免多个事务同时执行表级锁，对于有全表扫描的SQL语句，建立相应的索引进行优化。

悲观锁和乐观锁?

  • 乐观锁（Optimistic Lock）
    ：从名字上看，就是很乐观，每次去拿数据的时候认为别人不会改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新数据，乐观也严谨。乐观锁适用于读多写少的场景，这样可以提高吞吐量。乐观锁总是假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

    乐观锁通过使用数据版本（Version）记录机制（最常用）和时间戳（timestamp）来实现。

    前者是通过为数据库表增加一个数字类型“version”字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，version值加1.当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

    时间戳的方式与前者实现方式差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）,
    和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

  • 悲观锁（Pessimistic Lock）
    ：就是比较悲观，每次去拿数据的时候都认为别人会修改，所以每次拿数据的时候都会对数据上锁，这样别人想拿这个数据的时候就会先上锁，之后再对数据进行操作。总的来说就是悲观锁总是假定会发生并发冲突，然后屏蔽一切可能违反数据完整性的操作。

    悲观锁大多数情况下依靠数据库的锁机制实现，例如使用Select … for
    update语句，以保证操作最大程度的独占性。但如果采用悲观锁，则整个操作过程中，数据库始终处于加锁状态（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），而面对成百上千万的并发，就会导致灾难性的后果，因此采用悲观锁进行控制时要考虑清楚。

视图

视图（View）是从一个或多个基本表（或视图）导出的表，它与基本表不同，是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据；当基本表中的数据发生变化，从视图中查询出的数据也就随之改变；单表视图一般用于查询和修改，会改变基本表的数据；多表视图一般用于查询，不会改变基本表的数据。

视图的作用:

  1. 视图能够简化用户的操作；
  2. 视图使用户能以多种角度看待同一数据；
  3. 视图对重构数据库提供了一定程度的逻辑独立性；
  4. 视图能够对机密数据提供安全保护；

当需要多次重复输入相同的语句、需要不同的表字段聚合，进行信息重组、安全需要或需要兼容老的表等情况，就可以利用视图来简化sql查询，提高开发效率。

不过事物皆有两面性，视图也存在它的缺点。视图的性能差，数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间；视图也存在着修改限制，当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

存储过程和函数的区别:

存储过程是用户定义的一系列SQL语句的集合，涉及特定表或其他对象（表、视图、索引、序列、目录、同义词、数据库用户、存储过程、函数、触发器等）的任务，用户可以调用存储过程；而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

  1. 存储过程功能复杂强大，可以执行包括修改表等一系列数据库操作；而函数实现的功能针对性比较强；

  2. 对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类；

  3. 存储过程可以使用非确定函数，而不允许用户在定义函数非确定函数；

  4. 存储过程一般作为一个独立的部分来执行（EXECUTE语句执行），而函数可以作为查询语句的一部分来调用（SELECT调用）；

触发器

例如在数据库中有两个表，一个用户信息表，一个账号表，账号表用来账号的注册，密码的修改等操作，信息表用来保存用户

的信息（姓名、年龄等）；当账号在账号表中注册成功后，如何在用户表中同时添加用户的相关信息以保证数据的准确性和实时性呢。通常我们在注册成功时可以insert用户的信息到用户信息表中，但如果用户数量过多，SQL语句则过于繁琐，怎么办。这时我们就可以使用触发器。

触发器（trigger）是一种特殊的存储过程，它可以在对一个表上进行INSERT、UPDATE和DELETE操作中的任一种或几种操作时被自动调用执行。它SQL
server 提供给程序员和数据分析员来保证数据完整性的一种方法。

当需要实现多个表的级联更改或实时监控表中字段的更改作出处理时就可以使用触发器来完成。

mysql中的触发器:

Before Insert、After Insert、Before Update、After Update、Before Delete、After Delete

MVCC

MVCC讲解

什么是MVCC

MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)
是一种基于多版本的并发控制协议，只有在InnoDB引擎下存在，用于实现提交读和可重复读这两种隔离级别。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别未提交锁和已提交锁时才会生效。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

MVCC的实现机制

InnoDB在每行数据都增加两个隐藏字段，一个记录创建的版本号，一个记录删除的版本号。

在多版本并发控制中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的，对于数据的更新（包括增删改）操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

MVCC下的CRUD

  • SELECT：
    当隔离级别是REPEATABLE READ时select操作，InnoDB必须每行数据来保证它符合两个条件：

    1. InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)
      。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
    2. 这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。
      符合这两个条件的行可能会被当作查询结果而返回。

  • INSERT：

InnoDB为这个新行记录当前的系统版本号。

  • DELETE：

InnoDB将当前的系统版本号设置为这一行的删除ID。

  • UPDATE：

InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。

优点:

这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保只选择符合条件的行。保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量

缺点：

在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ
UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

数据库优化

mysql性能优化:

  1. 当只有一行数据时使用limit 1。查询时如果已知只会得到一条数据，则加上limit
    1会增加性能。因为mysql数据库引擎会在找到一条结果后停止搜索，而不是继续查询下一条或直至所有记录查询完毕。
  2. 选择正确的数据库引擎。MySQL中都MyISAM和InnoDB都各有利弊，因此选择正确的引擎很重要。MyISAM适用于读多写少的应用，而且它对于select
    count(*)类似操作速度非常快；InnoDB是一个复杂的存储引擎，它在写多的应用中占优势，并且它支持很多高级应用（事务等）。
  3. 用not exists代替not in。Not exists用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。Not
    in是最慢的方式要同每条记录比较，在数据量比较大的操作中不建议使用这种方式。
  4. 对操作符的优化，尽量不采用不利于索引的操作符。如：in、not in、is null、is not null、<> 等；；这种运算符会检索标中的全部数据

数据库结构优化

  1. 将字段很多的表分解成多个表。因为字段很多的表中存在一些使用频率低的字段，这些字段的存在就会大大影响访问速度；
  2. 增加一些中间表。对于需要经常联合多个表进行查询时，可以建立一个中间表，将需要通过联合查询的数据插入到中间表，来提高效率；
  3. 读写分离。在数据库并发大的情况下，最好的做法就是进行横向扩展，增加机器，以提升抗并发能力，而且还兼有数据备份功能。
Mysql
#知识点 #Mysql
Mysql基础知识
https://zhyyao.me/2022/12/01/technology/mysql/mysql_basic/
作者
zhyyao
发布于
2022年12月1日
许可协议