mysql主从复制面试题_MySQL面试知识复盘（一）

本文主要介绍MySQL基本语句的使用以及MySQL知识点的一些回顾。

1、MySQL连接语句（一）多表连接语句

UNION 内的语句必须具有相同的列数。列也必须具有类似的数据类型。另外，每个语句中的列顺序必须相同

(2) 基本命令语句

1、查看MySQL提供的所有存储引擎

mysql> 显示；

2.查看MySQL当前默认存储引擎

mysql> 显示类似“%%”；

3.查看表的存储引擎

mysql> 显示类似“”的表；

(3)in和in mysql的区别

in语句：外表和内表之间的哈希连接。

语句：对外表进行循环，每次循环都会查询内表。

如果查询的两个表大小相似，使用in和没有太大区别。

如果两个表其中一个较小，另一个是较大的表，则使用较大的子查询表，并使用较小的子查询表。

not in和not：如果查询语句使用not in，那么内外表都会进行全表扫描，不使用索引；而not的子查询仍然可以使用表上的索引。

所以无论哪个表很大，使用 not 都比 not in 快。

（四）与char的区别 2.与MySQL存储引擎相关 1.请介绍一下您对MySQL存储引擎的理解？

MySQL的存储引擎主要包括 和 。

2. 和 和 有什么区别？

（1）仅支持表级锁，不支持事务级锁和行级锁；支持行级锁和表级锁，默认为行级锁。

(2)强调绩效。每个查询都是原子的，其执行速度比类型快，但不提供事务支持。为事务、外键和其他高级数据库功能提供事务支持。具有事务、回滚和崩溃恢复功能的事务安全表。

(3) 不支持外键，但支持外键。

(4) 仅支持MVCC。对于处理高并发事务，MVCC比简单的加锁效率更高。

MVCC仅工作在READ和READ两种隔离级别下； MVCC可以使用乐观锁和悲观锁来实现；

(5)索引为聚集索引，索引为非聚集索引。主键索引的叶子节点存储行数据，因此主键索引非常高效。索引的叶子节点存储的是行数据地址，需要再次寻址才能获取数据。

插入知识点：

非聚集索引：索引检索时，首先按照B+Tree搜索算法来搜索索引。如果指定的Key存在，则取出其数据字段的值，然后以数据字段的值为地址读取对应的数据记录。

聚集索引：索引文件和数据文件分开。表数据文件本身就是一个由B+Tree组织的索引结构。树的叶子节点的数据字段保存了完整的数据记录。

基于主索引查找时，可以直接找到key所在的节点来检索数据；当基于辅助索引进行查找时，需要先检索主键的值，然后再查找主索引。因此，在设计表时，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，因为这会导致主索引频繁拆分。

《MySQL 高性能》：

不要轻易相信“比快还快”之类的经验说法。这个结论往往不是绝对的。在我们知道的许多场景中，速度是无与伦比的，特别是当使用聚集索引，或者需要访问的数据可以放在内存中的应用程序时。

3、指数相关（1）什么是指数？

索引是特殊文件，包含指向数据表中所有记录的引用指针并占用物理空间。

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

（2）MySQL索引使用的数据结构有哪些？

MySQL索引使用的数据结构主要包括BTree索引和哈希索引。

对于哈希索引，底层数据结构是哈希表。因此，当大部分需求是单记录查询时，可以选择哈希索引，其查询性能最快。在大多数其他场景中，建议选择BTree索引。

（3）指数的优缺点是什么？

优势：

缺点：

(4) 索引有哪些类型？

可以通过ALTER TABLE ADD()创建唯一索引；

可以通过ALTER TABLE ADD(,)创建唯一的复合索引；

普通索引可以通过ALTER TABLE ADD INDEX()创建；

可以通过 ALTER TABLE ADD INDEX (, , ) 创建组合索引；

可以通过ALTER TABLE ADD()创建全文索引；

（5）创建索引的原则是什么？ (6)索引创建和删除语句

创造：

--第一种：在执行CREATE TABLE时创建索引
CREATE TABLE user_index (
	id INT auto_increment PRIMARY KEY,
	name VARCHAR (16),
	id_card VARCHAR (18),
	UNIQUE KEY (id_card)
);
--第二种：使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
--第三种：使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);

删除：

alter table table_name drop KEY index_name;
--如果主键自增长，不能直接删除（自增长依赖于主键索引），需要取消自增长再行删除：
alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

（7）如何删除100万以上的数据？

（1）先删除索引（这个过程大约需要三分钟）

（2）然后删除无用的数据（这个过程不到两分钟）

（3）删除完成后重新创建索引（此时数据较少）并且索引创建也很快，大约十分钟左右。

（8）非聚集索引一定会返回表查询吗？

不一定，这涉及到查询语句所需的字段是否全部命中索引。如果所有字段都命中索引，则无需执行回表的查询。

（9）索引的使用场景有哪些？

(1) 其中

(2) 排序依据

使用order by对查询结果按照某个字段进行排序时，如果该字段没有索引，则执行计划会对所有查询的数据使用外部排序（将数据从硬盘批量读取到内存，使用内部排序）排序，最后合并排序结果），这个操作对性能影响很大，因为查询涉及到的所有数据都需要从磁盘读取到内存（如果单个数据太大或者数据量很大）太大了，效率会降低），更不用说读取内存后进行排序了。 。

如果在字段alter table table name add index(字段名)上创建索引，那么由于索引本身是有序的，所以可以直接根据索引的顺序和映射关系将数据一一检索出来。而如果是分页的话，那么只需要取出索引表一定范围内的索引对应的数据，而不是像上面所说的把所有数据取出来，排序然后返回一定范围内的数据。 （从磁盘取数据对性能影响最大）

(3)加入：

对join语句匹配关系涉及的字段建立索引（上）可以提高效率

4. 交易相关 (1) 什么是交易？

事务是一组逻辑操作，要么全部执行，要么不执行。它是数据库并发控制的基本单位。

（2）本次交易有哪些特点？ （3）并发事务带来的问题？

不可重复读和幻读的区别：

不可重复读取的重点是修改，比如多次读取一条记录，发现某些列的值被修改了。幻读的重点是添加或删除。例如，多次读取一条记录发现记录条数增加或减少。

(4)事务隔离级别有哪些？ MySQL的默认隔离级别是多少？

SQL 标准定义了四种隔离级别：

MySQL存储引擎支持的默认隔离级别是-READ（可重读）。虽然支持RR级别，但不会出现幻读问题，因为使用了next-key lock算法来防止幻读的发生。

5、锁相关（1）你了解MySQL锁吗？

当数据库存在并发事务时，可能会出现数据不一致的情况。这时候就需要一些机制来保证访问的顺序。锁机制就是这样一种机制。

(2) 锁定机构

在关系数据库中，数据库锁按照锁粒度可以分为行级锁、表级锁和页级锁。

使用表级锁；支持行级锁和表级锁，默认为行级锁

Read级别，读取数据不需要共享锁，因此不会与修改数据的排它锁冲突；

Read级别，读操作需要共享锁，但执行完语句后共享锁被释放；

Read级别，读操作需要共享锁，但是在事务提交之前共享锁是不会释放的，即事务完成后必须释放共享锁；

是限制性最强的隔离级别，因为它锁定整个范围的键并保持锁定直到事务完成。

插入一个知识点：

共享锁（S锁）：也称为读锁，其他用户可以并发读取数据，但在所有共享锁被释放之前，任何事务都无法获取该数据的排它锁。如果事务T给数据A加了共享锁，其他事务只能给A加共享锁，不能加排它锁。获取共享锁的事务只能读取数据，而不能修改数据。

排它锁（X锁）：也称为写锁。如果事务T给数据对象A加了X锁，那么只有T可以读取和修改A。在T释放A上的锁之前，其他事务都不能给A加任何类型的锁。这会阻止任何其他事务获取该锁直到事务结束时释放资源上的原始锁定。在更新操作（ 、 或 ）期间始终应用独占锁。获取排他锁的事务既可以读取也可以修改数据。

(3) 锁算法

存储引擎的锁算法有以下三种：

（4）从锁类别来看，MySQL有哪些类型的锁？像上面那样加锁会不会影响并发效率？

从锁的类型来看，有共享锁和排它锁。

（5）MySQL中引擎行锁是如何实现的？

行锁定是基于索引完成的

示例：* from where id = 1 for ;

for可以根据条件完成行锁定，id是带有索引键的列。如果id不是索引键，就会完成锁表，无法并发。

(6)什么是死锁？怎么解决呢？

死锁是指两个或多个事务互相占用同一资源，并请求锁定对方的资源，造成恶性循环的现象。

解决死锁的常用方法：

(7)什么是数据库的乐观锁和悲观锁？它是如何实现的？

悲观锁：假设会发生并发冲突，并阻止所有可能违反数据完整性的操作。查询数据后，事务被锁定，直到事务提交。实现方法：利用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查数据完整性违规。修改数据时，锁定事务并锁定通过。

实现方法：乐观锁一般采用版本号机制或者CAS算法来实现。

两种锁的使用场景：

乐观锁适用于写入相对较少的情况（多读场景），即很少发生冲突的情况。这样可以节省锁的成本，提高系统的整体吞吐量。

悲观锁适用于写入较多的情况（多写场景）。当写入次数较多时往往会出现冲突，这会导致上层应用不断重试，实际上会降低性能。

6、MySQL优化（一）如何定位和优化SQL语句的性能问题？如何查看创建的索引是否被使用？

MySQL提供查看语句执行计划的命令

type关键字：访问类型

关键字：可能会用到的索引，请注意也可能不会用到。

如果查询涉及的字段有索引，则会列出该索引。当列为NULL时，需要考虑当前的SQL是否需要优化。

key关键字：显示MySQL在查询中实际使用的索引。如果没有使用索引，则显示为NULL。

ref关键字：表示上表的连接匹配条件，即使用哪些列或者常量来查找索引列上的值

额外的关键字：

(2) SQL的生命周期？

(1)建立应用服务器和数据库服务器的连接

(2)数据库进程获取请求sql

(3)解析并生成执行计划，执行

(4) 将数据读入内存并进行逻辑处理

(5)通过步骤1中的连接将结果发送给客户端

(6)关闭连接并释放资源

（3）大表如何优化？

垂直拆分的优点：可以使列数据更小，减少查询时读取的块数，减少I/O次数。另外，垂直分区可以简化表的结构，更容易维护。

垂直拆分的缺点：主键会冗余，冗余列需要管理，会引起Join操作，可以通过在应用层进行Join来解决。

此外，垂直分区使事情变得更加复杂；

分表只是解决单表数据过大的问题，但由于表数据还在同一台机器上，所以对于提高MySQL的并发能力实际上没有任何意义，所以水平拆分是最好的数据库拆分方式。

水平拆分可以支持非常大量的数据存储，并且应用端修改很少。但分片交易难以解决，跨节点连接性能较差，逻辑复杂。尽量不要对数据进行分片，因为分片会带来逻辑、部署、运维的各种复杂性

我们添加两种常见的数据库分片解决方案：

（4）您关心过业务系统中的SQL耗时问题吗？查询统计是否太慢？您如何优化慢速查询？

业务系统中，除了使用主键的查询外，我会在测试库上测试其他查询的时间消耗。慢查询的统计主要是运维来做的，业务中的慢查询会定期反馈给我们。

要优化慢查询，我们首先要了解慢查询的原因是什么？是不是查询条件没有命中索引？您是否加载了不必要的数据列？或者数据量太大？

因此，优化也是针对这三个方向：

（5）为什么不建议使用UUID作为主键？

因为在存储引擎中，主键索引是以聚集索引的形式存在的，也就是说主键索引和所有数据都存储在主键索引的B+树叶子节点上。如果使用UUID，由于传入的ID与原始大小不同，肯定会引起大量的数据插入和数据移动，从而产生大量的内存碎片，从而导致插入性能下降。

主键是聚集索引。如果没有主键，则会选择一个唯一键作为聚集索引。如果没有唯一键，则会生成隐式主键。

（6）MySQL数据库CPU突然飙升到500%怎么办？

当CPU激增到500%时，首先使用操作系统命令top命令观察是否是占用造成的。如果不是，则找到占用率高的进程并进行相关处理。

如果是它造成的，用show看看里面运行了什么，看看是否有消耗资源的SQL在运行。找到消耗大的SQL，看看执行计划是否准确，索引是否缺失，数据量是否过大。

一般来说，必须杀死这些线程（并观察CPU使用率是否下降），进行相应的调整，例如添加索引、更改SQL、更改内存参数，然后重新运行这些SQL。

也有可能每条SQL语句消耗的资源并不多，但是突然大量的连接进来，导致CPU激增。这种情况就需要和应用程序一起分析连接数激增的原因，然后做出相应的调整，比如谈谈限制连接数等。

(7)如何优化LIMIT分页？ （八）MySQL复制原理及流程

主从复制：将主库中的DDL、DML操作通过二进制日志()传输到从库，然后重新执行(redo)这些日志；从而使从库数据与主库数据保持一致。

(1)MySQL主从复制工作原理：

Main：——记录所有改变数据库数据的语句，放在上面；

Slave：io线程——使用start Slave后，负责从上面拉取内容，放入自己的relay log中；

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

(2)复制过程：

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

中继日志：来自服务器的中继日志

(3)主从复制的作用：

(9)优化SQL语句的一些方法：

（1）优化查询时，应尽量避免全表扫描，并首先考虑在where、order by涉及的列上建立索引；

（2）尽量避免对where子句中的字段进行空值判断，否则引擎将放弃使用索引而进行全表扫描；

(3) 尽量避免在where子句中使用!=或运算符，否则引擎将放弃使用索引而进行全表扫描；

（4）尽量避免在where子句中使用or来连接条件，否则引擎会放弃使用索引而进行全表扫描；

(5)in和not in也应谨慎使用，否则会导致全表扫描。对于连续值，能用就不要用in；

(6)如果where子句中使用了参数，也会引起全表扫描。因为 SQL 仅在运行时解析局部变量，所以优化器不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。但是，如果在编译时构建访问计划，则变量的值仍然未知，因此不能用作索引选择的输入；

（7）尽量避免对where子句中的字段进行表达式操作，这会导致引擎放弃使用索引而进行全表扫描；

（8）尽量避免对where子句中的字段进行函数操作，这会导致引擎放弃使用索引而进行全表扫描；

(9) 不要在where子句中“=”左侧进行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引。

本文参考文章：

MySQL数据库面试题（2020最新版）