MySQL面经知识点汇总

最新推荐文章于 2023-10-03 21:57:14 发布

@小二，上酒

最新推荐文章于 2023-10-03 21:57:14 发布

阅读量309

点赞数

分类专栏：面试基础知识文章标签： mysql 面试

本文链接：https://blog.csdn.net/one23_man_show/article/details/106984481

版权

面试基础知识专栏收录该内容

3 篇文章 0 订阅

订阅专栏

数据库理论

事务

概念：
事务是指满足原子性、一致性、隔离性和持久性的一组操作，可以通过Commit提交一个事务，也可以通过Rollback进行回滚。

Q：事务的四个特性

ACID
原子性： 事务被视为最小分割单元，事务的所有操作要么全部提交成功；要么全部失败回滚。
一致性： 数据库在事务操作后使数据库从一个一致性状态转到另一个一致性状态（满足完整性约束）。
隔离性： 一个事务所做的修改在最终提交之前，对其他事务不可见。多个事务并发执行，可以保证互相不影响。
持久性： 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
在这里插入图片描述

Q：并发一致性问题

丢失修改
T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
脏读
T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读
T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。幻读和不可重复读的区别在于,不可重复读是针对确定的某一行数据而言,而幻读是针对不确定的多行数据。因而幻读通常出现在带有查询条件的范围查询中。

Q：隔离级别

未提交读
事务A和事务B，事务A未提交的数据，事务B可以读取，这里读取到的数据叫做“脏数据”，该级别最低，一般只是理论上存在，数据库的默认隔离级别都高于该级别。
事务的修改即使没有提交，对其他事务也是可见的。
提交读
事务A和事务B，事务A提交的数据，事务B才可读取到，换句话说：对方事务提交之后的数据，当前事务才可读取到，可以避免读取“脏数据”，但是该级别会有“不可重复读”的问题，事务B读取一条数据，当事务A修改这条数据并提交后，事务B再读取这条数据时，数据发生了变化，即事务B每次读取的数据有可能不一致，这种情况叫做“不可重复读”。
一个事务只能读取已经提交的事务所做的修改。
可重复读
MySQL默认的隔离级别是重复读，该级别可以达到“重复读”的效果，但是会有“幻读”的问题，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，发现有新的“幻影”行。
保证同一个事务在多次读取同样数据中结果一样。
可串行化
事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待，这样保证了同一个时间点上只有一个事务操作数据库，该级别可以解决“幻读”的问题。但是这种级别一般很少使用，因为吞吐量太低，用户体验不好。

存储引擎

Q：Innodb和MyISAM对比

1. 是否支持行级锁：
MyISAM只有表级锁（table-level locking），而InnoDB支持支持行级锁和表级锁，默认行级锁。

2.是否支持事务和崩溃后的安全恢复：
MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持事务，外部键等高级数据库功能。具有事务（commit）、回滚（rollback）和崩溃修复能力的事务安全型表。
3. 是否支持外键：
MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC：
仅InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在READCOMMITTED和REPEATABLE READ两个隔离级别下工作；MVCC可以使用乐观（optimistic）锁和悲观（pessimistic）锁来实现；各数据库中MVCC实现并不统一。

数据库索引

Q：为什么使用索引

索引是为了加速对表中的数据行的检索而创造的一种分散存储的数据结构。 更通俗的说，数据库索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
优点：
1）所有mysql字段都可以被索引
2）大大加快数据的查询速度
3）帮助服务器避免进行排序和分组（B+树支持排序和分组），以及避免创建临时表
4）将随机 I/O 变为顺序 I/O
缺点：
1）创建和维护索引需要耗费时间，而且数据量越大，越耗费时间。
2）索引需要占用物理空间
3）增删改操作数据时也需要对索引更新，降低了数据的维护速度
所以：

经常更新的表不宜用索引，经常查询的表最好用索引；
数据量小的表最好不用索引；
如果一个列上的值类别少，最好不要用索引（如：性别）

Q：索引类别有哪些

哈希索引（结构划分）
底层数据结构是哈希表，因此绝大多数的单数据查询，可以选择哈希索引。查询速度快O(1)，但是不能范围查询，无法避免数据的排序，不能利用部分索引查询，不能避免表扫描。
B+Tree索引（结构划分）
主流索引
主键索引
主键索引是一种唯一性索引，即不允许值重复并且值非空，并且每个表只能有一个主键。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
唯一索引
与普通索引类似，不同是：索引列的值必须唯一，但允许有null；如果构成组合索引，组合必须唯一。唯一索引的目的是提高访问速度的同时，避免数据重复。
CREATE UNIQUE INDEX indexName ON mytable(username(length))
ALTER table mytable ADD UNIQUE [indexName] (username(length))
普通索引
Mysql最基本的索引，没有任何限制，加快对数据的访问速度
CREATE INDEX index_name ON table_name (column_list)
联合索引
全文索引
用于查找文本中的关键词，而不是直接比较是否相等。
空间数据索引
可以用于地理数据存储。空间数据索引会从所有维度来索引数据，
可以有效地使用任意维度来进行组合查询。

Q：索引数据结构进化

二叉树
二叉树在增删的时候，可能会恶化树形结构，即变成线性结构，时间复杂度从O(logn)变成O(n)，大大降低效率。
平衡二叉树
平衡二叉树可以改善二叉树的结构，方式线性结构的出现。保证了时间复杂度。但是对于二叉树的每个节点存储的数据，都发生一次IO，二叉树每个节点最多有2个子节点，这样树的深度会大大增加，IO次数也会增加，查询很慢。
B树
在这里插入图片描述
B树增加了每个节点的子树，每个节点包含了更多的关键字，可以存储更的数据。大大降低了树的高度，从而降低IO次数优化了查询效率。
B+树

非叶子节点仅仅用来存储索引，所有的数据都存储在叶子节点中，这样就使得所有的查询都是从根节点开始到叶子节点结束，查询效率稳定。而且非叶子节点可以存储更多的关键字了。树的高度更矮，IO次数更少。
所有的叶子节点通过链指针连接，这样利用范围统计。

Q：聚集索引和非聚集索引的区别

MyISAM：
B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
MyISAM的主键索引、唯一键索引、普通索引都是非聚集索引。
InnoDB：
其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。
如果主键定义了，那么主键就是聚集索引；
如果主键没有定义，那么该表的第一个唯一非空索引是聚集索引；
如果都没有，那么内部会生成一个隐藏主键。
Innodb必须有且仅有一个聚集索引。

Q：最左匹配原则

最左匹配原则是针对联合索引而制定的原则。
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序（=和in都可以自动优化顺序）。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。将联合索引和B+树结合起来。例如创建了联合索引(a,b)，B+树如图：
可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

数据库优化等问题

Q：大表优化方法

MySQL单表记录数目过大的时候，数据库的CRUD性能下降，可采取如下方式：
限定数据的范围
合理使用where语句进行缩小范围精确查询
读写分离
主库负责读，从库负责写
垂直分区
将数据库表的列进行拆分，把一张列较多的表拆分为几个表
水平分区
保持数据的结构不变，将每一片数据分散到不同的表或者数据库中，达到分布式目的

Q：分库分表之后，id主键如何处理

分库分表之后需要一个全局id：
数据库自增id：
两个数据库分别设定不同自增步长，生成无重复的自增id。
利用Redis生成id：
性能好，灵活，不依赖数据库
UUID：
不适合作主键，太长。
还有一些算法如snowflake、leaf分布式ID生成系统等

Q：如何定位和优化慢查询

①使用慢日志优化SQL
show variables like ‘%slow_query%’;用于显示于慢日志相关的变量，如：slow_query_log；slow_query_log_file；long_query_time，这些可以通过set global语句进行更改；
show status like ‘%slow_quer%’;用于显示已执行的慢查询SQL条数；
通过定位到慢日志路径，查看相关慢查询信息然后进行优化。
②使用explain分析

https://www.cnblogs.com/tufujie/p/9413852.html

其中重点是：type对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
extra该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
③修改SQL语句或者尽量让SQL走索引
添加索引，或是减少不必要数据的访问（如不要用select *和添加limit限制等），或是切分大查询等。

Q：数据库在进行海量处理数据时候注意什么

思路是围绕着尽量避免全表扫描，应走索引查询

尽量避免在where子句中使用!=;<>;in / not in（between代替）;or（union代替）等字段
尽量避免在where子句中使用参数、函数和表达式操作等
最好不要使用select *查询
最好不要使用游标

数据库锁机制

日志

日志文件记录了影响MySQL数据库的各种类型的活动。这些日志文件可以帮助DBA对MySQL数据库的运行状态进行诊断，从而更好的进行数据库层的优化。常见的日志有：

错误日志
二进制日志
慢查询日志
查询日志
中继日志
事务日志

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确信息。

错误日志包含了数据库启动和关闭的次数；
包含了错误,警告,和注释的相关诊断信息；
在主从复制架构中的从服务器上启动从服务器线程时产生的信息；
event scheduler 运行一个event时产生的日志信息。

通过下面的命令定位该文件。

show variables like 'log_error';

二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。

二进制日志的作用在于：

恢复，可以用于恢复某些数据。
复制，通过复制和执行二进制日志，可以使另一台MySQL数据库（slave）和master保持一致。
审计，可以用来判断是否有对数据库进行注入的攻击。

使用方式如下：

log_bin = OFF | ON
log_bin_basename = /var/lib/mysql/mysql-bin 
sql_log_bin=1|0    #是否启用二进制日志
log_bin_index=PATH    #二进制日志索引位置
sync_binlog=1|0     #设定是否启动二进制日志同步功能
max_binlog_size=SIZE      #单个二进制文件最大体积，默认为1G
expire_logs_days=0       #超过多少天就清除二进制日志，默认为0,代表不启用此功能
binlog_format=STATEMENT|ROW|MIXED       
#二进制记录格式(STATEMENT：基于“语句”记录; ROW：基于“行”记录 ;MIXED：让系统自行判定该基于哪种方式进行)

查询日志

查询日志记录了所有对MySQL数据库请求的信息，日志记录了每一条sql语句，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。

开启方式如下：

vim /etc/my.cnf.d/server.cnf 
    general_log = ON| OFF            #查询日志开关
    general_log_file localhost.log            #查询日志的文件名字（/var/lib/mysql）
    log_output TABLE | FILE | NONE       #查询日志的存储形式

慢查询日志

它用来记录在某个数据库中响应时间超过阀值的语句。具体指运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。
慢查询日志是做数据优化可查的分析项。

第一步：查看默认设置

show variables like '%slow_query%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_query_log      | OFF                |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------+

查看默认等待时长

 show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

第二步：编辑配置文件开启慢查询日志

╭─root@localhost.localdomain ~  
╰─➤  vim /etc/my.cnf.d/server.cnf 
 
[server]
slow_query_log=1
long_query_time=3

第三步：显示符合条件已执行的慢查询语句条数

show status like '%slow_quer%';

中继日志

中继日志：复制架构中，备用服务器用于保存主服务器的二进制日志中读取到的事件

主从同步

①操作步骤：
在这里插入图片描述

(1) master将改变记录到二进制日志(binary log)中；
(2) slave将master的binary log events拷贝到它的中继日志；
(3) slave重做中继日志中的事件，同步数据

详细数据库主从同步请看：

https://www.cnblogs.com/atcloud/p/10773855.html

②存在问题：

1）主库宕机后，数据可能丢失

解决方案：半同步复制。确保事务提交后binlog至少传输到一个从库，不保证从库应用完这个事务的binlog。性能有一定的降低，响应时间会更长。
在这里插入图片描述
半同步复制和异步复制请看：

https://www.cnblogs.com/kevingrace/p/10228694.html

2）同步延迟
当主库的TPS并发较高时，产生的DDL数量超过slave 一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

解决方案：使用并行复制。并行是指从库多线程 apply binlog 库级别并行应用binlog，同一个库数据更改还是串行的。设置set global slave_parallel_workers=10;设置sql线程数为10。

事务日志

事务日志用于记录所有事务以及每个事务对数据库所做的修改。事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。

事务日志一般分成Undo与Redo：Undo一般用于事务的取消与回滚，记录的是数据被修改前的值，Redo一般用于恢复已确认但未写入数据库的数据，记录的是数据修改后的值，例如：数据库忽然断电重启，数据库启动时一般要做一致性检查，会把已写到Redo的数据但未写入数据库的数据重做一遍。

数据库系统如何来确认哪些数据需要redo或undo呢？那就需要一个检查点（checkpoint），在系统中一般有一个表或一个控制文件来记录检查点，日志是按顺序一直写下去的，检查点设置后，只需要比对检查点之后的数据就可以了。

@小二，上酒

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL面经知识点汇总

数据库#数据库索引哈希索引底层数据结构是哈希表，因此绝大多数的单数据查询，可以选择哈希索引。B±Tree索引可以分析以下从二叉树到B+树索引结构的变化历程。##聚集索引和非聚集索引的区别**MyISAM：**B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。**InnoDB：**其数据文件本身就是索引文件。相比MyIS
复制链接

扫一扫