面试笔记 - MySQL

最新推荐文章于 2024-08-28 17:47:28 发布

..十二

最新推荐文章于 2024-08-28 17:47:28 发布

阅读量131

点赞数

本文链接：https://blog.csdn.net/qq_42691486/article/details/108035162

版权

面试笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

笔记参考于：https://blog.csdn.net/ThinkWon/article/details/104778621

一、数据库基础

1.数据库三大范式

① 每一个列不可再拆分
② 在①基础上，非主键列完全依赖与主键，而不是只依赖于主键的一部分
③ 在②基础上，非主键只依赖与主键

2.MySQL权限表

MySQL服务器通过权限表控制用户对数据库的访问

user：记录允许连接到服务器的用户账户信息，全局权限
db：记录各个账户在各个数据库上的权限
table_priv：记录数据表级的操作权限
columns_priv：记录数据列级的操作权限
host：配合db表对给定主机上的数据库级操作权限更细致的控制

3.binlog的录入格式

statement：每一条会修改数据的sql都会记录在binlog中
row：保存每一条修改的的记录
mixed：以上两者的折中方案

二、存储引擎

1.MyISAM和Innodb

①常见存储引擎

Innodb:提供了对数据库ACID事务的支持，还提供了行级锁和外键的约束，它的设计目标就是处理大数据容量的数据库系统
MyISAM:不提供事务支持，不支持行级锁和外键
MEMORY：所有数据都在内存中，处理速度快，安全性低

②MyISAM和Innodb的区别

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序存储	按主键大小有序插入
锁支持	表级	表级、行级
优势	查	增删改

③MyISAM索引与Innodb索引的区别

Innodb是聚簇索引；MyISAM是非聚簇索引
Innodb的主键叶子节点存储的是行数据，因此主键索引效率很高
MyISAM索引的叶子节点存储的是行数据的地址，需要经过多一次的寻址
Innodb的非主键的叶子节点存储的是主键和其他带索引的列数据，因此覆盖索引很高效

④Innodb的四大特性

插入缓冲
二次写
自适应哈希索引
预读

⑤索引选择

MyISAM：适合读写插入
Innodb：适合更新（删除）操作多，并发量高，需要事务和外键支持

三、索引

1.索引

索引是一种特殊的文件，它包含着对数据库表里所有记录的引用指针

索引是一种为了高效操作数据库中数据的排好序了的数据结构

2.索引的优缺点

①优点

大大加快数据的检索速度
通过使用索引，在查询过程中，优化隐藏器，提高系统性能

②缺点

时间上：创建和维护索引需要耗费时间，在对数据库中数据进行增删改时也需要动态维护索引，会减低增删改的效率
空间上：索引是一个文件，会占用物理空间

3.索引使用场景

① where

可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引（alter table 表名 add index(字段名)），同样的SQL执行的效率，你会发现查询效率会有明显的提升（数据量越大越明显）

② order by

使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的

但是如果我们对该字段建立索引 alter table 表名 add index(字段名) ，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。

③join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖

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

4.索引类型

①主键索引

数据列不允许重复，不允许为NULL，一个表只能有一个主键

②唯一索引

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

③普通索引

基本的索引类型，没有唯一性的限制，允许为NULL值

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

④全文索引

是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

5.索引的数据结构

Innodb默认索引：B+树
MyISAM默认索引：B+树

① B树索引

查询方式：

主键索引区:PI(关联保存的时数据的地址)按主键查询,

普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快

B-tree：

每个节点可以放两个值
树高变低，查找速度变快
存在回旋查找的问题

B+tree：

非叶子节点存数据的key，非叶子节点既存key也存value（数据的地址）
除叶子节点的第一和最后一个节点比B-tree查找慢以外，其他节点查找速度相同（他们既是叶子节点，也是非叶子节点）
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接，由此解决了回旋查找的问题，范围查找的速度得到了提高，并且由于这些节点的数据是有序的，所以在按照索引排序的时候就不会产生文件的排序
所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字
数据对象的插入和删除仅在叶子节点上进行
B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点

②hash索引

不使用hash索引的原因：
1、只能进行等值查询，不能进行范围查询
2、hash值是无序的，不能进行排序
类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

③平衡二叉树

不使用平衡二叉树的原因：
1、树越高，查找速度越慢
2、范围查找的时候，回旋查找数据的速度就会变慢（如：有1-10值的10个节点平衡二叉树，查找 >5 的数，需要先找到5，再从右边一个个查找比它大的数）

索引的基本原理

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

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

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

6.索引设计的原则

适合索引的列是出现在where子句中的列，或者连接子句指定的列
基数较小的类，索引效果较差，没必要建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，可以节省大量索引空间
不要过度使用索引

7.创建索引的原则

最左前缀匹配原则
频繁作为查询条件的字段才去创建索引
更新频繁的字段不适合创建索引
不能有效区分数据的列不社会创建索引
尽量扩展索引而不是新建索引
定义有外键的列一定要创建索引
很少使用，重复值比较多的列不适合创建索引
定义为text、image、bit的列不适合创建索引

8.创建索引的三种方式

直接创建索引

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

CREATE TABLE `table` (
	INDEX index_name (title(length))
)

9.删除索引

删除普通索引、唯一索引、全文索引

alter table 表名 drop KEY 索引名

删除主键索引

alter table 表名 drop primary key

在主键自增时，不能执行此操作

10.创建索引的注意事项

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

11.删除百万基本数据

先删除索引，再删除想要删除的数据，删完之后再建立索引

12.前缀索引

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

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

实操的难度：在于前缀截取的长度。
我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了

13.索引数据结构（补充）

①B-tree和B+tree的区别

B树可以将键和值存放在内部节点和叶子节点；B+树内部节点只能存放键，叶子节点存放键和值
B+树的叶子节点之间有一条链相连

②使用B树的好处

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

③使用B+树的好处

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

④hash索引与B+树的区别

hash索引在等值查询时更快，但是不能进行范围查询
hash索引不支持使用索引进行排序
hash索引不支持模糊查询和最左前缀原创，因为hash函数具有不可预测性
hash索引不能避免回表查询
hash索引虽然在等值查询上速度快，但是不稳定，发生hash碰撞的时候效率可能极差

⑤数据库为什么使用B+树而不是B树

B树只适合随机检索，而B+树可以随机检索也可以顺序检索
B+树的空间利用率更高，它是一直自平衡的二叉树，树的高度更低，B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作
B+树增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率

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

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询

⑦聚簇索引和非聚簇索引

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

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

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

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

14.索引失效

不遵循最佳左前缀法则
大于号右边的索引失效，因为虽然节点左边的字段是有序的，但是右边此时是无序的
like，%在左边时是不走索引的

四、事务

1.事务

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

2.ACID

原子性：事务是最小执行单位，不可再分割
一致性：执行事务前后，数据保持一致，多个事务对同一数据读取的结果是一致的
隔离性：并发访问数据库时，一个事务不受其他事务影响，各并发事务之间数据库是独立的
持久性：一个事务被提交后，它对数据库的修改是持久的

3.脏读、不可重复读、幻读

脏读:事务A修改了一个数据，此时事务B读取了这个数据，但是后面事务A进行了Rollback操作，那么B读到的数据就是不正确的
不可重复读：事务A读取了一个数据，但是后面事务B修改了这个数据，等A再回来读取的时候，就会发现这个数据不同了
幻读：事务A读取了一个数据，此时事务B往这个数据中插入了新的数据，那A再次读取这个数据的时候就会发现多了一些之前自己读的时候没有的东西

4.事务的隔离级别

READ-UNCOMMITTED (读未提交) ：最低隔离级别，允许读取未提交的数据，以上三个问题都不可以避免
READ-COMMOITTED（读已提交）：允许读取已经提交的数据，可以阻止脏读
REPEATBLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

五、锁

1.锁

当数据库有并发事务时，需要锁机制来保证访问的次序，保证数据一致

2.隔离级别与锁的关系

在read-uncommitted下，读取数据不需要加锁
在read-committed下，读取数据需要加共享锁，语句执行结束后释放锁
repeattable read下，在事务提交前不释放锁
seriallzable下，锁住整个范围的键，事务提交前不释放锁

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

行级锁（Innodb）：行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
表级锁（MyISAM）：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低
页级锁（BDB）：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般