mysql数据库面试_面试如何讲解mysql数据库

1、mysql的引擎
最常见的MyISAM 和 InnoDB

2、线上的一些问题
主从延迟问题、模糊匹配问题、全表扫描问题

3、MyISAM 和 InnoDB 的区别
是否支持行级锁
MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务
MyISAM 不提供事务支持。InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。
InnoDB支持 MVCC
索引结构：MyISAM 和 InnoDB 都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址，而是保存该行的所有数据内容，而辅助索引的Data域保存的则是主索引的值。
由于InnoDB的辅助索引保存的是主键索引的值，所以使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这也是为什么不建议使用过长的字段作为主键的原因：由于辅助索引包含主键列，所以，如果主键使用过长的字段，将会导致其他辅助索变得更大，所以争取尽量把主键定义得小一些。
为什么推荐使用自增主键：
在插入数据情况下，不用移动数据修改索引，因为涉及磁盘数据，数据的存储是按照页来落入磁盘的，自增只需要新增加一个页，可以保证数据存储连续性。

4、记录锁、间隙锁、临键锁（https://www.cnblogs.com/Terry-Wu/p/12219019.html）
行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。
记录锁：
需要注意的是：id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁
间隙锁：
使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

5、Innodb事务的隔离级别
读未提交、读已提交、可重复读、可串行化。

6、Innodb事务的隔离级别是由MVCC和锁机制实现的
MVCC：
MVCC是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行的事务ID，一个保存了行的回滚段指针。每开始一个新的事务，都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中，而回滚段的指针有该行记录上的所有版本数据，在undo log回滚日志中通过链表形式组织，也就是说该值实际指向undo log中该行的历史记录链表。
在并发访问数据库时，对正在事务中的数据做MVCC多版本的管理，以避免写操作阻塞读操作，并且可以通过比较版本解决快照读方式的幻读问题，但对于当前读的幻读，MVCC并不能解决，需要通过临键锁来解决。
锁机制：
排它锁解决脏读
共享锁解决不可重复读
临键锁解决幻读

7、InnoDB存储引擎支持行级锁和表级锁，默认情况下使用行级锁，但只有通过索引进行查询数据，才使用行级锁，否就使用表级锁。MyISAM和MEMORY存储引擎采用的是表级锁

8、索引的优缺点：
1）索引的优点：
减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。
如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）索引的缺点：
当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。
索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的使用场景：

(1）在哪些列上面创建索引：
WHERE子句中经常出现的列上面创建索引，加快条件的判断速度。
按范围存取的列或者在group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间。
（2）不在哪些列建索引？
区分度不高的列。由于这些列的取值很少，例如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
在查询中很少的列不应该创建索引。由于这些列很少使用到，但增加了索引，反而降低了系统的维护速度和增大了空间需求。
当添加索引造成修改成本的提高 远远大于 检索性能的提高时，不应该创建索引。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
索引的分类:
普通索引：最基本的索引，没有任何限制
唯一索引：但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。
全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
组合索引：主要是为了提高mysql效率，创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

聚簇索引与非聚簇索引:
聚簇索引：表中数据存储的物理顺序与索引值的顺序一致，一个基本表最多只能有一个聚簇索引，更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引
非聚簇索引：表中数据的物理顺序与索引值的顺序不一致的索引组织，一个基本表可以有多个聚簇索引。

为什么使用B+数\ B+Tree索引的优点：
相比于B树，只有叶子节点有数据，链表形式存储，方便范围查询；数比b树要低，减少了索引的io次数。
页内节点不存储内容，每次IO可以读取更多的行，大大减少磁盘I/O读取次数
带顺序访问指针的B+Tree：B+Tree所有索引数据都存储在叶子结点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针，这样做是为了提高区间查询效率。

MySQL主从复制的原理：
Slave从Master获取binlog二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成：
（1）Slave SQL thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于slave端
（2）Slave I/O thread线程：读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中，位于slave端：
（3）Binlog dump thread线程(也称为IO线程)：将bin-log二进制日志中的内容发送到slave服务器，位于master端

优化查询的方法？
1.使用索引 应尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引。

他山之石：https://blog.csdn.net/a745233700/article/details/114242960

ACID特性，实现原理:
原子性：实现原理（undo log）
实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

持久性：实现原理（redo log）
InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中。
Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘（redo日志的刷盘策略1，2，3）。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

隔离性：锁机制和MVCC保证隔离性
(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性