数据库相关原理与MySQL并发机制

最新推荐文章于 2024-05-29 16:53:40 发布

牛逼的名字

最新推荐文章于 2024-05-29 16:53:40 发布

阅读量271

点赞数

文章标签：数据库 mysql 面试

本文链接：https://blog.csdn.net/advvvleo/article/details/115554395

版权

1. 主键与unique 有什么区别？

Primary key 在一个表中只有一个但在一张表中可有多unique
主键不允许null 而unique允许出现null
不可以既定义主键又定义unique

2. 外键的注意事项

外键一定是另外一张表的主键域内存在的东西或者为空值

3. check的注意事项

一个表可以定义多个check约束
每个字段智能定义一个check约束
在多个字段上定义的check约束必须定义为表约束

4. alter方式修改列的注意事项

不可以改变列名
不可以将由null值得列改为not null
若表中已有数据不可以减少该列的宽度
只能修改null/not null约束其余约束要先删除约束再添加约束

5. 条件查询

集合类型

select * from sc where CNO in(‘c1’,‘c2’)

另一种思维是将in 换成Not in，查询在集合外的元素
匹配查询

正则表达式：

%：代表0个或者多个字符

_：占位符，代表一个字符

[]：用来表示某一范围的数值

[^]：用来表示不在某一范围的数值
常见库函数
- AVG
- SUM
- MAX
- MIN
- COUNT 统计元祖个数
  - select sount (distinct x) as x from s //统计x列不重复的个数
  - count(*)用于统计所有元祖的个数不可以使用distinct关键字
分组查询group by

group by按照列的值查询，将所有列值相同的行分为一小组一小组，对每一个小组可以使用count函数进行统计

hanving子句每组按照特定条件筛选

！！！where>group by>having

为什么？

where作用于整个表视图，group by作用于组，having是在group by的基础上实现的

6. 多表查询

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

7. 子查询

select * from sc where prof = (select prof from t where tn =‘刘伟’)
话术：寻找xxx为yy的东西可以使用any、in。先返回一组集合然后用any、in展示出这组集合

某一，min

所有，max、all

exists
- where exists xxx：只要xxx里出现的条件为真，就输出这一行，循环输出，直到遍历完整张表。

8. 合并查询（合并成一个结果集）

合并查询是使用UNON操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。UNON操作会自动将重复的数据行剔除。必须注意的是，参加合并查询的各子查询的使用的表结构应该相同，即各子查询中的数据数目和对应的数据类型都必须相同。

特点：

① 合并的表中的列的个数、数据类型必须相同或向兼容。

② union默认去掉重复值，如果允许重复值需要用union all

③ 执行顺序可通过列的顺序或空格改变结果集的排列顺序

④ 可以与select into 一起使用，但是into 必须放在第一个select 语句中。

⑤ 可以合并的结果进行排序，单排序的order by 必须放在最后一个select的后面

所使用的列名也必须是第一个。

9. order by

ORDER BY 语句用于对结果集进行排序。

ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

10. 索引

逻辑结构到物理结构上的映射，避免在一个表中插入大量的索引，负责会影响插入、删除、更新数据的性能。

聚集索引

物理存储顺序与索引键的逻辑顺序是一致的，这就意味着改变表的结构也要改变每个记录的存储顺序。适用于“查多改少”的表。每张表仅有一组聚集索引。

非聚集索引

物理的存储顺序与逻辑可以不一致，也允许在一张表中建立多组非聚集索引。

唯一索引

保证索引键中不包含重复的值，只有当数据本身存在唯一性这个特征时，唯一索引才有意义。

主键和unqiue都可以生成唯一索引，建议同时创建主键和unique，使所以就目标明确。

视图索引

虚表，类似于缓存。如果很少更新基础表，使用视图索引是合适的；如果频繁更新基础表，视图索引开销较大。

全文索引

统计关键字的频率与位置，建立一张以词库为目录的索引。

xml索引

对XML数据类型创建XML索引。

数据库索引为什么采用B+树？

B+树的每个结点不包含下一个结点的指针，所以体积更小，这样每个磁盘块就能存放更多的索引结点，减少IO次数。
B+树更稳定：在B+树中，只有叶子结点存放关键信息，所以查询的时候路径是相当的，查找效率更为稳定。
B+树方便进行数据库扫描，因为每个叶子结点之间都有指针，因此B+树支持随机存取，而B树必须中序遍历才能得到相应的结点数据。

索引的优点

大大加快数据的检索速度，这也是创建索引的最主要的原因；
加速表和表之间的连接；
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

索引注意事项

非空、取离散值大的点、字段越小越好.

11. 范式

第一范式

所有属性不可拆分（一张二维表）

第二范式

每个非主键都依赖于主键（每行都要标识）

第三范式

每个非主键都不传递主键（不能传递什么意思？如果一张表中含另一张表的主键：部门编号，那么这张表也不需要再把部门人数、部门名称等非主键给包含进去，因为这太啰嗦）

BCNF属于第三范式

12. 事务

四个性质：

原子性

事务必须是一个原子操作，不然容易出现安全问题
一致性

一个事务可能会存在多个操作，这些操作有可能对不同的表进行，需要保证每个表都存在数据的一致
隔离性

多个线程对DB进行并发操作时，应该保证每个线程之间的事务是隔离的，一个事务的执行不能被另一个事务干扰。
持久性

事务一旦提交，在DB中的改变就是永久的，即使故障，也要保证数据的持久。

丢失更新

在两个事务并发对某一行修改时，事务A更新后，时间片完，事务B更新，此时事务B时间片完，事务A读取数据，但读的是事务B更新的数据，这就意味着事务A的更新被事务B 覆盖掉了。

脏读

事务A修改数据，但未提交给数据库；此时事务B读取改数据，但A回滚，可以理解为事务B读了一个错误的数据（因为事务A反悔了）

不可重复读（修改行数据问题）

指一个事务在同一时间间隔内读取到了不一致的数据：事务B在时间点1读取数据，与此同时事务A提交事务，此时事务B在时间点2又一次读取数据库，也就是在一次事务内读取到了不同的数据。（理解为程序的互斥问题：事务的读取必须是连续性的，要上锁）

幻读（插入数据问题）

事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。为什么会产生幻读？由于行锁仅能锁住已有的行，对新插入的行时无法控制的。

如何解决幻读？在两行数据的间隙处，加入锁，阻止新记录的插入；这个锁称为间隙锁。

事务隔离级别

Read uncommitted(读未提交)：解决丢失更新

如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据。

如何理解避免丢失更新？

丢失更新是出现在两个事务并发修改数据的过程，既然不会出现两个事务并发修改。也就不可能出现丢失更新。

如何理解出现脏读？

事务A是具有读写权限的，但是其他事务只有读权限，并发执行时，若事务B读取表中数据，那么很有可能读取到事务A回滚后的数据。

Read committed(读提交)：解决脏读

如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据（也就是说在这个级别下，读锁是允许其他事务进行表的读写的），该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

Repeatable read(可重复读取)：解决不可重读

可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，**读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，**这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

Serializable(可序化)：解决幻读

提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

事务的四种隔离级别示意图

13. 锁

什么是锁？

**锁是计算机协调多个进程或纯线程并发访问某一资源的机制。**在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

表锁（MyISAM)

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
MyISAM表的读操作会阻塞其他用户的写操作，但不阻塞其他用户的读操作（读优先于写）
MyISAM表的写操作会阻塞其他用户的所有操作，（写容易导致务安全问题）

如何加表锁

MyISAM在读之前（select语句之前）会给涉及到的表自动加上读锁，在执行更新操作之前，会给涉及到的表自动加写锁。

并发锁

在一定条件下，MyISAM也支持查询和操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。
当concurrent_insert设置为1时，MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。

MyISAM的锁调度

q:当不同线程同时对MySQL进行锁申请时，如何调度？

A:写线程将优先获得锁。为什么？想象一下，写的过程是从CPU寄存器到物理磁盘的过程，如果在这个过程中发生了阻塞，那么写线程将会被挂起，寄存器的内容很有可能被冲洗掉，因此写线程应当获得高的优先级。

note:因为MyISAM对写操作有较高的优先级，所以MyISAM表不太适合于有大量更新操作的应用（大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞）。

q:如何解决？

可以通过一些设置来调节MyISAM的调度行为。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

InnoDB锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

InnoDB按照不同的分类可以分为七种锁：

共享/排它锁(Shared and Exclusive Locks)

共享锁（S）：又称读锁。事务A对数据对象进行S锁操作，其他事务可以读取这个数据对象，但是不能修改这个数据对象；另外，其他事务只能对这个数据对象再进行S锁，不能进行X锁。
排他锁（X）：又称写锁。只有获得排他锁的对象可以对数据进行更新数据，其他事务不能再对这个对象加任何锁。

这里有个地方需要明白，对于普通的select查询，默认是不加任何锁的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁，**但是仍然可以进行普通无锁查询。**mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
另外还需要注意的是：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

意向锁(Intention Locks)

InnoDB为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁是一个表级别的锁(table-level locking)；

意向锁又分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁；
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁；

加锁的语法为：

select … lock in share mode;　　//要设置IS锁

select … for update;　　　　　　//要设置IX锁

note：update,delete,insert涉及到的都是排他锁

以上是按照兼容性所划分的锁，按照实现算法来看nnodb的三种行锁分别是：间隙锁、记录锁和临键锁。

间隙锁(Gap Locks)

对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。

举例来说，假如lock_example表中只有101条记录，其id的值分别是1,2,…,100,101，下面的SQL：

Select * from lock_example where id > 100 for update;

InnoDB 不仅会对符合条件的 id值为 101 的记录加锁;

也会对 id大于101（这些记录并不存在）的“间隙”加锁。

间隙锁的目的

防止幻读,以满足相关隔离级别的要求

对于上例,若不使用间隙锁,如果其他事务插入 id大于 100 的任何记录,那么本事务如果再次执行上述语句,就会发生幻读。

满足其恢复和复制的需要

在使用范围条件检索并锁定记录时，InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待，因此,在实际开发中,尤其是并发插入较多的应用，我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

记录锁(Record Locks)

顾名思义，记录锁就是为某行记录加锁，它封锁该行的索引记录：

– id 列为主键列或唯一索引列

SELECT * FROM lock_example WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。

需要注意的是：id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。

同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

在通过主键索引与唯一索引对数据行进行UPDATE 操作时，也会对该行数据加记录锁：

– id 列为主键列或唯一索引列

UPDATE lock_example SET age = 50 WHERE id = 1;

临键锁(Next-key Locks)

临键锁是一段左开右闭的区间。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁,会降级为记录锁，即仅锁住索引本身，不是范围。

例如，在这样的一个表中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e1wkhRf1-1617958110845)(C:\Users\Hasee\AppData\Roaming\Typora\typora-user-images\image-20210409153222006.png)]

该表中 age 列潜在的临键锁有：

(-∞, 10],

(10, 24],

(24, 32],

(32, 45],

(45, +∞],

– 根据非唯一索引列 UPDATE 某条记录

UPDATE lock_example SET name = Vladimir WHERE age = 24;

– 或根据非唯一索引列锁住某条记录

SELECT * FROM lock_example WHERE age = 24 FOR UPDATE;

此时，事务A不仅将对age 为 24的那行记录进行临键锁，也获取了 (10, 24] 这个区间内的临键锁。

因此，加入一个事务B进行INSERT INTO table VALUES(100, 16, ‘Ezreal’);那么该事务将被阻塞。

不仅如此，临键锁非常霸道！！！在获取了具体行左边的区间的锁的同时还获取了右边区间的锁，什么意思？

事务CINSERT INTO table VALUES(100, 30, ‘Tom’);也会被阻塞！！！因为事务A在获取age=24的那行临键锁的时候，同时也获取了(24, 32]这段区间的临键锁！！！

即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为 (10, 32]。

插入意向锁(Insert Intention Locks)

对已有数据行的修改与删除，必须加强互斥锁(X锁)，但想一想，对插入操作其实是没有那么严格的。于是，插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

举个例子：

(表依然是如上的例子lock_example，数据依然是如上)，事务A先执行，在10与24两条记录中插入了一行，还未提交：

insert into lock_example values(11,23, ‘Jim’);

事务B后执行，也在10与24两条记录中插入了一行：

insert into lock_example values(12,24, ‘Bob’);

因为是插入操作，虽然是插入同一个区间，但是插入的记录并不冲突，所以使用的是插入意向锁，此处A事务并不会阻塞B事务。

自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

举个例子(表依然是如上的例子lock_example)，但是id为AUTO_INCREMENT，如果A事务执行如下语句：

insert into lock_example values(23, ‘Jim’);

B事务执行的语句如下：

insert into lock_example values(24, ‘Bob’);

此时事务B插入操作会阻塞，直到事务A提交。

锁总结：什么时候使用表锁？

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。

但在个别特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

在InnoDB下，使用表锁要注意以下两点。

（１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

（２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

死锁

ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。

InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

常见的避免死锁的方法：

如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能。
如果事务中存在更新记录的语句，应该直接申请排他锁，而不是先申请共享锁，等到要更新的时候再使用排他锁。

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。