MySQL知识点整理

最新推荐文章于 2024-05-04 22:38:25 发布

Sweetheartman

最新推荐文章于 2024-05-04 22:38:25 发布

阅读量646

点赞数

分类专栏： Java MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/lidekun9132/article/details/117396073

版权

Java 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

MySQL索引

什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引的目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？因此，索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

索引设计原则

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的类，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

注意：

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

创建索引的原则

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

order by查询中排序的字段
group by查询中统计或者分组的字段
join 对join语句匹配关系（on）涉及的字段建立索引能够提高效率

创建索引的三种方式

在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list); 
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

索引的优点和缺点

优点

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
可以加快数据的检索速度。
可以加速表与表之间的连接。
在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间。

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占用物理空间，数据量越大，占用空间越大。
会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护。

索引的原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

磁盘IO和预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-reDWhP7G-1622344980531)(MySQL.assets/1618539553409.png)]

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引一般以文件形式存在磁盘中（也可以存于内存中），存储的索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索。

索引的类型

① 普通索引（单列索引）：单列索引是最基本的索引，它没有任何限制。

② 复合索引（组合索引）：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。

③ 唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值） 。

④ 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引，其实就是建表加的主键。

⑤ 全文索引：全文索引主要用来查找文本中的关键字，目前只有char、varchar，text 列上可以创建全文索引。

索引的方法

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

MySQL索引使⽤的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。
MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: InnoDB索引分为主索引和辅助索引，其表数据文件本身就是按B+Tree组织的⼀个主索引，树的叶节点data域保存了完整的数据记录，主索引的key是数据表的主键，这被称为“聚簇索引”。而其余的索引都作为辅助索引，辅助索引的data域存储主索引的Key而不是地址，这也是和MyISAM不同的地⽅。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主索引的key，再走⼀遍主索引。因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理⾃《Java⼯程师修炼之道》

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索引。

索引的最左匹配原则

索引最左匹配原则

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

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

前缀索引

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

实操的难度：在于前缀截取的长度。

我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

最左前缀匹配原则

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

索引失效情况

应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
不用函数和触发器，在应用程序实现
Like查询避免%xxx式查询，应使用xxx%
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

使用索引查询一定能提高查询的性能吗

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索

Hash索引和B+树索引有什么区别或者说优劣呢?

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

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序，原理同上。
hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

B+树在满足聚簇索引和覆盖索引的时候需不需要回表查询数据？

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

什么是聚簇索引？何时使用聚簇索引与非聚簇索引？

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询，如果还要查询name时，那么要回表查询数据行，获取name。

联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

MySQL事务

什么是数据库事务？

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

事务的四大特性

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

并发事务带来的问题

脏读（Dirty read）: 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交
到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没
有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是
不正确的。
丢失修改（Lost to modify）: 指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，
那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修
改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取
A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在⼀个事务内多次读同⼀数据。在这个事务还没有结束
时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修
改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不
⼀样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接
着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了
⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者
删除⽐如多次读取⼀条记录发现记录增多或减少了。

MySQL事务的隔离级别

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

隔离级别	脏读	不可重复度	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

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

MySQL存储引擎

MyISAM和InnoDB区别

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(row-
level locking)和表级锁,默认为⾏级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度
⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据
库功能。具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)
的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不⽀持，⽽InnoDB⽀持。
是否支持在线热备份：InnoDB 支持在线热备份
是否支持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效;MVCC只在
READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤乐观
(optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。

MySQL视图

为什么要使用视图？什么是视图？

为了提高复杂SQL语句得复用性和表操作的安全性， MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

视图有哪些特点？

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

重用SQL语句；
简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
使用表的组成部分而不是整个表；
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

查询简单化。视图能简化用户的操作
数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图的缺点

性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

存储过程与函数

什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

触发器

什么是触发器？触发器的使用场景有哪些？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

可以通过数据库中的相关表实现级联更改。
实时监控某张表中的某个字段的更改而需要做出相应的处理。
例如可以生成某些业务的编号。
注意不要滥用，否则会造成数据库及应用程序的维护困难。

MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

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

常用的SQL语句

SQL语句主要分为哪几类？

数据定义语言DDL(Data Ddefinition Language) ：CREATE，DROP，ALTER。对逻辑结构操作的，其中包括表结构，视图和索引
数据查询语言DQL（Data Query Language）：SELECT。
数据操纵语言DML（Data Manipulation Language)：INSERT，UPDATE，DELETE。
数据控制功能DCL（Data Control Language）：GRANT，REVOKE，COMMIT，ROLLBACK。对数据库安全性完整性操作的，可以简单理解为权限控制。

超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

SQL约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

左连接、右连接、内连接

左连接：左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右连接：与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
内连接：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

limit 分页是物理还是逻辑？

物理分页

varchar与char的区别

char的特点

char表示定长字符串，长度是固定的；
如果插入数据的长度小于char的固定长度时，则用空格填充；
因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

varchar表示可变长字符串，长度是可变的；
插入的数据是多长，就按照多长来存储；
varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
对于varchar来说，最多能存放的字符个数为65532

varchar(50)中50的含义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

int(20)中20的含义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

int(10)和char(10)以及varchar(10)的区别

int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

int(10) 10位的数据长度 9999999999，占32个字节，int型4位
char(10) 10位固定字符串，不足补空格最多10个字符
varchar(10) 10位可变字符串，不足补空格最多10个字符
char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

FLOAT和DOUBLE的区别是什么？

FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

Drop、Delete与Truncate的区别

Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

UNION与UNION ALL的区别？

如果使用UNION ALL，不会合并重复的记录行
效率 UNION 高于 UNION ALL

SQL优化

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。

Explain查询语句分析

SQL的生命周期

应用服务器与数据库服务器建立一个连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

大表数据查询，怎么优化?

优化shema、sql语句+索引；
第二加缓存，redis；
主从复制，读写分离；
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

超大分页怎么处理？

超大的分页一般从两个方向上来解决.

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.
解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

开启慢查询日志

配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间

配置项：long_query_time

查看：show VARIABLES like ‘long_query_time’，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

慢查询优化

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

字段为什么要求定义为not null?

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

数据库优化

复制

主从复制

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理

在主库上把数据更高记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中

基本原理流程，3个线程以及之间的关联

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

复制过程

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jy5OsQI4-1622344980534)(MySQL.assets/1618554276321.png)]

切分

水平切分

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以⽀持非常大的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过大的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

Sharding 策略

哈希取模：hash(key) % N；
范围：可以是 ID 范围也可以是时间范围；
映射表：使用单独的一个数据库来存储映射关系。

水平拆分的优点是:

不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力

缺点是：

分片事务一致性难以解决
跨节点Join性能差，逻辑复杂：解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。
跨节点的count,order by, group by以及聚合函数问题：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。
数据多次扩展难度跟维护量极大
主键ID问题，无法保证全局唯一

数据库分库分表的常见方案：

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当
当网的 Sharding-JDBC 、阿里的TDDL是两种比较常⽤的实现。
中间件代理：在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。我
们现在谈的 Mycat 、360的Atlas、⽹易的DDB等等都是这种架构的实现。

垂直切分

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

详解B树、B+树、红黑树

B树、B+树、红黑树

二叉查找树、AVL树、B树、B+树、红黑树的区别与联系

AVL树和红黑树的区别

B树和B+树的区别

在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F1ZZyX7W-1622344980535)(MySQL.assets/1621643743292.png)]

使用B树的好处

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

索引为什么用B+树而不用B树或者红黑树?

B+树做索引而不用B-树

B-树/B+树的特点就是每层节点数目非常多，层数很少，目的就是为了减少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。

优点一： B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。

优点二： B+树所有的Data域在叶子节点，并且所有叶子节点之间都有一个链指针。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B+树做索引而不用红黑树

在大规模数据存储的时候，根据磁盘查找存取的次数往往由树的高度所决定，红黑树是平衡二叉查找树，存储相同数据，与B+树相比其高度往往很高，进而造成磁盘IO读写过于频繁，导致效率低下的情况。

B+树定义

B+ 树是一种树数据结构，是一个n叉树，通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。每个节点通常有多个孩子，一颗B+树包含根节点、内部节点和叶子节点。

一棵m阶的B+树需要满足下列条件：

每个分支节点最多有m棵子树。
非叶根节点至少有两棵子树，其他每个分支节点至少有ceil(m/2)棵子树。
节点的子树个数与关键字个数相等。
所有叶节点包含全部关键字及指向相应记录的指针，而且叶节点中将关键字按大小顺序排列，并且相邻叶节点按大小顺序相互连接起来。
所有分支节点中仅包含它的各个子节点中关键字的最大值及指向其子节点的指针。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ALjX82Ax-1622344980537)(MySQL.assets/1618540084595.png)]

如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

B+树查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+树性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

数据库范式

第一范式(1NF)

第一范式强调的是原子性，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即每一个属性都是原子的，不能再分，也可以理解为不能表中套表。

如下人员基本信息表：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6I7yWolM-1622344980538)(MySQL.assets/1618559452600.png)]

这样的表是不能创建的，我们可以把这个表拆为基本信息表和地址表，通过人员编号来建立联系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tVT60toN-1622344980539)(MySQL.assets/1618559475022.png)]

第二范式(2NF)

第二范式是在第一范式基础上面提出来的，也就是说满足第二范式意味同时满足了第一范式。

第二范式：一张表中每个属性都是原子的，并且不存在对主键的部分函数依赖。第二范式要求每个非主属性完全依赖于主键，而不是仅依赖于其中一部分属性。

下面是学生信息表，最后两项为课程号和该门课成绩

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q9cwFnvm-1622344980540)(MySQL.assets/1618559617258.png)]

在这张表里面学号是主键吗？学号不是主键，学号能决定姓名，年龄，地址，但是凭学号这一项，是决定不了成绩这一项，所以这里学号不是主键。 **所以这张表的主键是（学号，课程号）**凭（学号，课程号）可以决定成绩；但是凭学号就可以决定姓名，年龄和地址，不需要课程号。所以在这里尽管主键是（学号，课程号），但是姓名，年龄和地址只要学号就可以决定的，也就是说这三个属性对主键存在部分函数依赖，它只依赖于主键里面的学号这一项，只依赖于主键的一部分，所以不是第二范式。

问题：

插入异常

如计划开新课，由于没人选修，没有课程号信息，那么因为（学号，课程号）是主键，所以连学生基本信息也不能插入，只有学生选课之后，才能插入信息。
删除异常

若学生申请病假休学一学期，从当前数据库删除选修记录，那么学生的基本信息也就丢了。
数据冗余

假设一个学生选修50门课，那么学生基本信息就重复了很多次，造成数据冗余。
更新异常

难以维护一致性，因为存在数据冗余，可能更改的时候漏掉了哪条记录

解决方法：

拆分成两个表，学生基本信息表和选课成绩表

总的来说，一张表只管一件事情，就不会出现这种问题。

第三范式(3NF)

第三范式是指在2NF基础上，不存在非属性对主键的传递依赖。

有一员工信息表，属性分别是员工号，工资级别，工资

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SD9IL3lv-1622344980541)(MySQL.assets/1618559856116.png)] 这张表的主键是员工号，一旦员工号确定了，工资级别也确定了，那么工资也就确定了。

但是工资对员工号存在传递依赖

具体看下图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AuxKZYw4-1622344980542)(MySQL.assets/1618559879559.png)]

问题：

插入异常

当工资级别还没有确定的时候，工资就无法确定。
删除异常

假设只有一个员工拿三级工资，当这个员工不在这个公司的时候，数据被删掉，那么这个工资级别与工资的对照关系就不存在了。
数据冗余

同2NF，工资级别与工资信息大量重复
更新异常

同2NF，难以维护一致性

解决方法：

同2NF，拆表，一张表只维护一件事

BC范式(BCNF)

满足BC范式的关系将消除任何属性（主属性和非主属性）对关系键的部分函数依赖和传递函数依赖。

MySQL的MVCC实现

MVCC详解

MVCC全称多版本并发控制指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

《高性能MySQL》中对MVCC的部分介绍

MySQL的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL, 包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同, 因为MVCC没有一个统一的实现标准。
可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现方式有多种, 典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

从书中可以了解到:

MVCC是被Mysql中 事务型存储引擎InnoDB 所支持的;
应对高并发事务, MVCC比单纯的加锁更高效;
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;
各数据库中MVCC实现并不统一
但是书中提到 “InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的”(网上也有很多此类观点), 但其实并不准确, 可以参考MySQL官方文档, 可以看到, InnoDB存储引擎在数据库每行数据的后面添加了三个字段, 不是两个!!

字符集及校对规则

字符集指的是⼀种从⼆进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每⼀种字符集都会对应⼀系列的校对规则。

MySQL采⽤的是类似继承的⽅式指定字符集的默认值，每个数据库以及每张数据表都有⾃⼰的默认值，他们逐层继承。⽐如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采⽤默认字符集）

锁机制

锁分类

表锁
- 共享锁：允许一个事务去读一行，其他事务可以获取共享锁，但是不能获取排他锁。
- 排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。
- 意向共享锁(IS)：事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁(IX)：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
Mysql中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

关于意向锁的理解：

在加行锁的时候需要获取对应的意向锁,其他行锁进来操作不同的行是互不影响的,但是对那些没有利用索引检索加锁的语句就会进行表意向锁校验,简单一点就是,大家都用索引修改,修改不同的行互不影响,但是没用索引,并不知道修改的是哪一行数据,默认加表锁都不让修改。这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

行锁
- Record Lock: 它是会锁住索引记录，比如 update table where id = 1， id 是主键，然后在聚簇索引上对 id=1 的个索引记录进行加锁；
- Gap Lock
  
  实质上是对索引前后的间隙上锁，不对索引本身上锁,目的是为了防止幻读。
  
  当使用范围条件查询而不是等值条件检索数据，并请求排他锁、或共享锁时，对于该范围内不存在的记录，不允许其修改插入。
  
  举个例子：当表中只有一条id=101的记录，一个事务执行select * from user where user_id > 100 for update；此时另一个事务执行插入一条id=102的数据是会阻塞的，必须等待第一个事务提交后才能完成。
  
  间隙锁是针对事务隔离级别为可重复读或以上级别的。
- Next-key Lock：锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。
Mysql中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

死锁

死锁：指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

MySQL死锁：多个事务都持有同一种不互斥(互斥排他锁是不能同时存在的)锁，还都在等待对方释放锁，继续执行，这样就造成了数据库的死锁。不过好在，Mysql自己实现一种死锁检测机制，如果监测到有死锁的存在时，mysql会主动中断一个事务，这个事务失败退出后，打破了死锁形成的必要条件，那么另外的事务就会继续执行下去。

MySQL的四种隔离级别，隔离级别不同，锁也是不一样的。

MySQL死锁案例分析：

行锁，锁的是索引的值。如果没有条件行没有索引的话，那么就会走隐藏的聚簇索引。有个特殊的就是，如果没有走索引条件的话，就会进行全局扫描，这时候就相当于加了表锁。
下面就分析一下真实遇到的并发死锁案例分析

CREATE TABLE `lock_test` (
  `id` int NOT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_id_c` (`id`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

遇到的这种情况，是由于程序并发执行，插入相同的一条sql语句,示意图如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ErvJ38Q7-1622344980543)(MySQL.assets/1618552055281.png)]

下面就分析一下为什么会出现死锁：
T1时刻，会话A插入一条记录，插入之后在加上记录锁（相当于排他锁（X锁）），T2时刻，因为存在唯一键冲突，会加上读锁（S锁），在插入的时候，发现会话A持有X锁，获取锁阻塞，同理T3时刻，会话C也出现阻塞等待X锁的情况。T4时刻，会话A发生回滚，释放X锁，此时会话B想获得X锁，但发现会话C有S锁存在，俩者冲突，同理会话C也在等待会话B的S锁，所以俩者产生了死锁。
由于Mysql有自己的死锁检测机制和解决机制，会话C失败推出，会话B正常执行插入操作，至此，死锁分析结束。

出现锁问题的排查命令

show open tables: 查看哪些表被锁了
show status like ‘table%’:查看表锁分析
show status like ‘innodb_row_lock%’:查看行锁分析
information_schema: MySQL专门记录性能信息的库

死锁解决方案

缓存去重

因为是并发请求，所以打到后端的请求参数都一样，为了防止并发请求造成并发数据库请求，我们可以在service层加一层缓存，把重复的请求给过滤掉，确保只放入一个请求进入，伪代码如下：
```
String key = id+c+d;
if(redisClient.exits(key)){
//并发请求,直接返回
return;
}
//唯一请求,过期时间根据实际情况而定
redisClient.sexEx(key,value,expireTime);
//数据库插入操作
dao.insert
```
异常捕捉

因为Mysql自己有死锁检测和恢复机制，所以即使发生了死锁，肯定有一条插入请求会落库成功，所以这时候只需要把死锁异常给catch住，让程序继续运行下去，不崩即可，伪代码就不写了，加个try、catch就行了。

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

数据类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

在进行存储和检索时，会保留 VARCHAR 末尾的空格，而会删除 CHAR 末尾的空格。

时间和日期

MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

DATETIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

一条SQL语句在MySQL的执行过程

简单来说 MySQL 主要分为 Server 层和存储引擎层

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

Server层基本组件介绍

连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。
分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

语句分析

查询语句
```
select * from tb_student  A where A.age='18' and A.name=' 张三 ';
```
- 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
- 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
- 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：
```
a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。 
b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。
```
  那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
- 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
更新语句
```
update tb_student A set A.age='19' where A.name=' 张三 ';
```
MySQL 自带的日志模块式 binlog（归档日志,用于主从集群复制数据） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志,用于本地机器崩溃恢复数据），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：
- 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
- 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
- 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
- 更新完成。

参考资料

Sweetheartman

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL知识点整理

MySQL索引什么是索引索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。索引的目的索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定
复制链接

扫一扫

专栏目录