MySQL数据库常见面试题总结

1、数据库的常用范式：

第一范式（1NF）：指表的列不可再分，数据库中表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
第二范式（2NF）：在 1NF 的基础上，还包含两部分的内容：一是表必须有一个主键；二是表中非主键列必须完全依赖于主键，不能只依赖于主键的一部分；
第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，非主键列必须直接依赖于主键。
BC范式（BCNF）：在 3NF 的基础上，消除主属性对于码部分的传递依赖

2、SQL语句的执行过程：

2.1、客户端的数据库驱动与数据库连接池：

（1）客户端与数据库进行通信前，通过数据库驱动与MySQL建立连接，建立完成之后，就发送SQL语句

（2）为了减少频繁创建和销毁连接造成系统性能的下降，通过数据库连接池维护一定数量的连接线程，当需要进行连接时，就直接从连接池中获取，使用完毕之后，再归还给连接池。常见的数据库连接池有 Druid、C3P0、DBCP

2.2、MySQL架构的Server层的执行过程：

（1）连接器：主要负责跟客户端建立连接、获取权限、维持和管理连接

（2）查询缓存：优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。

MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的：

先说下缓存中数据存储格式：key（sql语句）- value（数据值），所以如果SQL语句（key）只要存在一点不同之处就会直接进行数据库查询了；
由于表中的数据不是一成不变的，大多数是经常变化的，而当数据库中的数据变化了，那么相应的与此表相关的缓存数据就需要移除掉；

（3）解析器/分析器：分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。

（4）优化器：主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算，最终得出一个执行计划，包括选择使用哪个索引

在分析是否走索引查询时，是通过进行动态数据采样统计分析出来；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走索引时，也要考虑到这方面的因素

（5）执行器：根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

2.3、Innodb存储引擎的执行过程：

（1）首先MySQL执行器根据执行计划调用存储引擎的API查询数据
（2）存储引擎先从缓存池buffer pool中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中
（3）在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
（4）innodb 会在 Buffer Pool 中执行更新操作
（5）更新后的数据会记录在 redo log buffer 中
（6）提交事务在提交的同时会做以下三件事
（7）（第一件事）将redo log buffer中的数据刷入到redo log文件中
（8）（第二件事）将本次操作记录写入到 bin log文件中
（9）（第三件事）将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
（10）使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

详细内容请阅读这篇文章：https://blog.csdn.net/a745233700/article/details/113927318

3、常用的存储引擎？InnoDB与MyISAM的区别？

存储引擎是对底层物理数据执行实际操作的组件，为Server服务层提供各种操作数据的API。常用的存储引擎有InnoDB、MyISAM、Memory。这里我们主要介绍InnoDB 与 MyISAM 的区别：

（1）事务：MyISAM不支持事务，InnoDB支持事务

（2）锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是行锁只有通过索引查询数据才会使用，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况，但是表级锁不存在死锁

（3）主键和外键：MyISAM 允许没有任何索引和主键的表存在，不支持外键。InnoDB的主键不能为空且支持主键自增长，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键，支持外键完整性约束

（4）索引结构：MyISAM 和 InnoDB 都是使用B 树索引，MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址，而是保存该行的所有数据内容，而辅助索引的Data域保存的则是主索引的值。

由于InnoDB的辅助索引保存的是主键索引的值，所以使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这也是为什么不建议使用过长的字段作为主键的原因：由于辅助索引包含主键列，所以，如果主键使用过长的字段，将会导致其他辅助索变得更大，所以争取尽量把主键定义得小一些。

（5）全文索引：MyISAM支持全文索引，InnoDB在5.6版本之前不支持全文索引，5.6版本及之后的版本开始支持全文索引

（6）表的具体行数：

① MyISAM：保存有表的总行数，如果使用 select count() from table 会直接取出出该值，不需要进行全表扫描。
② InnoDB：没有保存表的总行数，如果使用 select count() from table 需要会遍历整个表，消耗相当大。

（7）存储结构：

① MyISAM会在磁盘上存储成三个文件：.frm文件存储表定义，.MYD文件存储数据，.MYI文件存储索引。
② InnoDB：把数据和索引存放在表空间里面，所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

（8）存储空间：

① MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
② InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

（9）适用场景：

① 如果需要提供回滚、崩溃恢复能力的ACID事务能力，并要求实现行锁级别并发控制，InnoDB是一个好的选择；
② 如果数据表主要用来查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率；

备注：在mysql8.0版本中已经废弃了MyISAM存储引擎

4、事务的ACID与实现原理？

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

4.1、事务的ACID：

（1）原子性：事务是一个不可分割的工作单元，事务里的操作要么都成功，要么都失败，如果事务执行失败，则需要进行回滚。
（2）隔离性：事务的所操作的数据在提交之前，对其他事务的可见程度。
（3）持久性：一旦事务提交，它对数据库中数据的改变就是永久的。
（4）一致性：事务不能破坏数据的完整性和业务的一致性。例如在转账时，不管事务成功还是失败，双方钱的总额不变。

4.2、ACID的实现原理：

4.2.1、原子性：原子性是通过MySQL的回滚日志undo log来实现的：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

4.2.2、隔离性：

（1）事务的隔离级别：

为保证在并发环境下读取数据的完整性和一致性，数据库提供了四种事务隔离级别，隔离级别越高，越能保证数据的完整性和一致性，但对高并发性能影响也越大，执行效率越低。（四种隔离级别从上往下依次升高）

读未提交：允许事务在执行过程中，读取其他事务尚未提交的数据；
读已提交：允许事务在执行过程中读取其他事务已经提交的数据；
可重复读（默认级别）：在同一个事务内，任意时刻的查询结果都是一致的；
读序列化：所有事务逐个依次执行，每次读都需要获取表级共享锁，读写会相互阻塞。

（2）事务的并发问题：

如果不考虑事务的隔离性，在事务并发的环境下，可能存在问题有：

更新丢失：两个或多个事务操作相同的数据，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后的更新覆盖了其他事务所做的更新。
脏读：指事务A正在访问数据，并且对数据进行了修改（事务未提交），这时，事务B也使用这个数据，后来事务A撤销回滚，并把修改后的数据恢复原值，B读到的数据就与数据库中的数据不一致，即B读到的数据是脏数据。
不可重复读：在一个事务内，多次读取同一个数据，但是由于另一个事务在此期间对这个数据做了修改并提交，导致前后读取到的数据不一致；
幻读：在一个事务中，先后两次进行读取相同的数据（一般是范围查询），但由于另一个事务新增或者删除了数据，导致前后两次结果不一致。

不同的事务隔离级别，在并发环境会存在不同的并发问题：

（3）事务隔离性的实现原理：

Innodb事务的隔离级别是由MVVC和锁机制实现的：

① MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL 的 InnoDB 存储引擎实现事务隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行，无需使用 MVCC。读序列化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行的事务ID，一个保存了行的回滚段指针。每开始一个新的事务，都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中，而回滚段的指针有该行记录上的所有版本数据，在undo log回滚日志中通过链表形式组织，也就是说该值实际指向undo log中该行的历史记录链表。

在并发访问数据库时，对正在事务中的数据做MVCC多版本的管理，以避免写操作阻塞读操作，并且可以通过比较版本解决幻读。

② 锁机制：

MySQL锁机制的基本工作原理就是：事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

排它锁解决脏读
共享锁解决不可重复读
临键锁解决幻读

4.2.3、持久性：

持久性的依靠redo log日志实现，在执行SQL时会保存已执行的SQL语句到一个redo log文件，但是为了提高效率，将数据写入到redo log之前，会先写入到内存中的redo log buffer缓存区中。写入过程如下：当向数据库写入数据时，执行过程会首先写入redo log buffer，redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中，这一过程称为刷盘（即redo log buffer写日志到磁盘的redo log file中）。

redo log buffer的使用可以大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时redo log buffer中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。为了确保事务的持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘，刷新频率由 innodb_flush_log_at_trx_commit变量来控制的：

0：表示不刷入磁盘；
1：事务每次提交的时候，就把缓冲池中的数据刷新到磁盘中；
2：提交事务的时候，把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

4.2.4、一致性：

一致性指的是事务不能破坏数据的完整性和业务的一致性：

数据的完整性：实体完整性、列完整性（如字段的类型、大小、长度要符合要求）、外键约束等
业务的一致性：例如在银行转账时，不管事务成功还是失败，双方钱的总额不变。

该部分详情可参考这篇博客：https://blog.csdn.net/a745233700/article/details/84207186

5、数据库中的锁机制？

当数据库中多个事务并发存取同一数据的时候，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。MySQL锁机制的基本工作原理就是，事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照不同的分类方式，锁的种类可以分为以下几种：

按锁的粒度划分：表级锁、行级锁、页级锁；
按锁的类型划分：共享（锁S锁）、排他锁（X锁）；
按锁的使用策略划分：乐观锁、悲观锁；

5.1、表级锁、行级锁、页级锁：

表级锁：最大粒度的锁级别，发生锁冲突的概率最高，并发度最低，但开销小，加锁快，不会出现死锁；
行级锁：最小粒度的所级别，发生锁冲突的概率最小，并发度最高，但开销大，加锁慢，会发生死锁；
页级锁：锁粒度界于表级锁和行级锁之间，对表级锁和行级锁的折中，并发度一般。开销和加锁时间也界于表锁和行锁之间，会出现死锁；

不同的存储引擎支持不同的锁机制：

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

5.2、InnoDB的行锁：

InnoDB的行锁有两种类型：

共享锁（S锁、读锁）：多个事务可以对同一数据行共享一把S锁，但只能进行读不能修改；
排它锁（X锁、写锁）：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。

对于update，delete，insert 操作，InnoDB会自动给涉及的数据行加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。

5.3、InnoDB的表锁与意向锁：

因为InnoDB引擎允许行锁和表锁共存，实现多粒度的锁机制，但是表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）。当事务要在记录上加上行锁时，则先在表上加上对应的意向锁。之后事务如果想进行锁表，只要先判断是否有意向锁存在，存在时则可快速返回该表不能启用表锁，否则就需要等待，提高效率。

5.4、InnoDB行锁的实现与临键锁：

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据，才能使用行锁，否则将使用表锁。

在InnoDB中，为了解决幻读的现象，引入了临键锁(next-key)。根据索引，划分为一个个左开右闭的区间。当进行范围查询的时候，若命中索引且能够检索到数据，则锁住记录所在的区间和它的下一个区间。其实，临键锁(Next-Key) = 记录锁(Record Locks) 间隙锁(Gap Locks)

间隙锁：当使用范围查询而不是精准查询进行检索数据，并请求共享或排它锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)。
记录锁：当使用唯一索引，且记录存在的精准查询时，使用记录锁

5.5、利用锁机制解决并发问题：

X锁解决脏读
S锁解决不可重复读
临键锁解决幻读

InnoDB存储引擎锁机制的详细内容和MyISAM存储引擎的锁机制的详细内容可以阅读这篇文章：https://blog.csdn.net/a745233700/article/details/84504209

6、MySQL索引的实现原理：

索引本质上就是一种通过减少查询需要遍历行数，加快查询性能的数据结构，避免数据库进行全表扫描，好比书的目录，让你更快的找到内容。（一个表最多16个索引）

6.1、索引的优缺点：

（1）索引的优点：

减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。
如果索引的数据结构是B 树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（2）索引的缺点：

当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。
索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

6.2、索引的使用场景：

（1）在哪些列上面创建索引：

WHERE子句中经常出现的列上面创建索引，加快条件的判断速度。
按范围存取的列或者在group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间。
经常用于连接的列上，这些列主要是一些外键，可以加快连接的速度；
作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（2）不在哪些列建索引？

区分度不高的列。由于这些列的取值很少，例如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
在查询中很少的列不应该创建索引。由于这些列很少使用到，但增加了索引，反而降低了系统的维护速度和增大了空间需求。
当添加索引造成修改成本的提高远远大于检索性能的提高时，不应该创建索引。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。

6.3、索引的分类：

（1）普通索引、唯一索引、主键索引、全文索引、组合索引。

普通索引：最基本的索引，没有任何限制
唯一索引：但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。
全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
组合索引：主要是为了提高mysql效率，创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（2）聚簇索引与非聚簇索引：

如果按数据存储的物理顺序与索引值的顺序分类，可以将索引分为聚簇索引与非聚簇索引两类：

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

6.4、索引的数据结构：

常见的索引的数据结构有：B Tree、Hash索引。

（1）Hash索引：MySQL中只有Memory存储引擎支持hash索引，是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来，因此查询效率非常高，可以一次定位。

hash索引的缺点：

Hash索引仅能满足等值的查询，不能满足范围查询、排序。因为数据在经过Hash算法后，其大小关系就可能发生变化。
当创建组合索引时，不能只使用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。
当发生Hash碰撞时，Hash索引不能避免表数据的扫描。因为仅仅比较Hash值是不够的，需要比较实际的值以判定是否符合要求。

（2）B Tree索引：B Tree是mysql使用最频繁的一个索引数据结构，是Innodb和Myisam存储引擎模式的索引类型。B Tree索引在查找时需要从根节点到叶节点进行多次IO操作，在查询速度比不上Hash索引，但是更适合排序等操作。

B Tree索引的优点：

页内节点不存储内容，每次IO可以读取更多的行，大大减少磁盘I/O读取次数
带顺序访问指针的B Tree：B Tree所有索引数据都存储在叶子结点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针，这样做是为了提高区间查询效率。

6.5、为什么使用B Tree作为索引：

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，磁盘I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。

（1）局部性原理与程序预读：

由于磁盘本身存取就比主存慢很多，再加上机械运动耗费，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页的整倍数。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

（2）B Tree索引的性能分析：

上文说过一般使用磁盘I/O次数评价索引结构的优劣。我们先从B树分析，B树检索一次最多需要访问h个节点，同时，数据库巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，即每次新建节点时，直接申请一个页的空间，这样就保证一个节点在物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个节点只需要一次I/O就可以完全载入。B树中一次检索最多需要h-1次I/O（根节点常驻内存），时间复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小。综上所述，用B树作为索引结构效率是非常高的。

而红黑树这种结构，虽然时间复杂度也为O(h)，但是h明显要深的多，并且由于逻辑上很近的节点，在物理上可能很远，无法利用局部性，所以IO效率明显比B树差很多。

另外，B Tree更适合作为索引的数据结构，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度d的上限取决于节点内key和data的大小，由于B Tree内节点去掉了data域，因此可以拥有更大的出度，磁盘IO的次数也就更少了。

（3）B 树索引和 B树索引的对比？

根据B-Tree 和 B Tree的结构，我们可以发现B 树相比于B树，在文件系统或者数据库系统当中，更有优势，原因如下：

（1）B 树有利于对数据库的扫描：B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而B 树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以范围查询、排序等操作，B 树有着更高的性能。
（2）B 树的磁盘IO代价更低：B 树的内部结点的data域并没有存储数据，因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说I/O读写次数也就降低了。
（3）B 树的查询效率更加稳定：由于B 树的内部结点只是叶子结点中关键字的索引，并不存储数据。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

（4）MySQL的 InnoDB 和 MyISAM 存储引擎中B Tree索引的实现？

MyISAM和InnoDB都是使用B 树索引，MyISAM的主键索引和辅助索引的Data域都是保存行的地址，但是InnoDB的主键索引保存的不是行的地址，而是保存该行的所有所有数据，而辅助索引的Data域保存的则是主索引的值。

索引的长度限制：

对于 Innodb 的组合索引，如果各个列中的长度超过767字节的，则会对超过767字节的列取前缀索引；对于 Innodb 的单列索引，如果列的长度超过767的，则取前缀索引（取前255字符）
对于 MyISAM 的组合索引，所创建的索引长度和不能超过1000 bytes，否则会报错，创建失败；对于 MyISAM 的单列索引，最大长度也不能超过1000，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）

索引的实现原理详细阅读：https://blog.csdn.net/a745233700/article/details/80798181

7、SQL优化和索引优化、表结构优化：

（1）MySQL的SQL优化和索引优化：https://blog.csdn.net/a745233700/article/details/84455241

（2）MySQL的表结构优化：https://blog.csdn.net/a745233700/article/details/84405087

8、数据库参数优化：

MySQL属于 IO 密集型的应用程序，主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。所以对于MySQL数据库的参数优化上，主要针对减少磁盘IO的参数做优化：比如使用 query_cache_size 调整查询缓存的大小，使用 innodb_buffer_pool_size 调整缓冲区的大小；

MySQL的参数优化：https://blog.csdn.net/a745233700/article/details/114506553

9、explain的执行计划：

执行计划是SQL语句经过查询分析器后得到的抽象语法树和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果，所以可能会存在分析错误的情况，也就是存在执行计划并不是最优的情况。通过explain关键字知道MySQL是如何执行SQL查询语句的，分析select 语句的性能瓶颈，从而改进我们的查询，explain的结果如下：

重要的有id、type、key、key_len、rows、extra：

（1）id：id列可以理解为SQL执行顺序的标识，有几个select 就有几个id。

id值不同：id值越大优先级越高，越先被执行；
id值相同：从上往下依次执行；
id列为null：表示这是一个结果集，不需要使用它来进行查询。

（2）select_type：查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询；

（3）table：表示 explain 的一行正在访问哪个表

（4）type：访问类型，即MySQL决定如何查找表中的行。依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type 类型都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一个索引。一般要求type为 ref 级别，范围查找需要达到 range 级别。

system：表中只有一条数据匹配（等于系统表），可以看成 const 类型的特例
const：通过索引一次就找到了，表示使用主键索引或者唯一索引
eq_ref：主键或者唯一索引中的字段被用于连接使用，只会返回一行匹配的数据
ref：普通索引扫描，可能返回多个符合查询条件的行。
fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
ref_or_null：与ref方法类似，只是增加了null值的比较。
index_merge：表示查询使用了两个以上的索引，索引合并的优化方法，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值；
index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。
index：索引全表扫描，把索引树从头到尾扫描一遍；
all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

（5）possible_keys：查询时可能使用到的索引

（6）key：实际使用哪个索引来优化对该表的访问

（7）key_len：实际上用于优化查询的索引长度，即索引中使用的字节数。通过这个值，可以计算出一个多列索引里实际使用了索引的哪写字段。

（8）ref：显示哪个字段或者常量与key一起被使用

（9）rows：根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。

（10）extra：其他的一些额外信息

using index：使用覆盖索引
using index condition：查询的列未被索引覆盖，where筛选条件使用了索引
using temporary：用临时表保存中间结果，常用于 group by 和 order by 操作中，通常是因为 group by 的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了
using filesort：MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能

对explain执行计划详请感兴趣的读者可以阅读这篇文章：https://blog.csdn.net/a745233700/article/details/84335453

10、MySQL的主从复制：

10.1、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端

注意：如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程；

10.2、主从复制流程：

（1）master服务器在执行SQL语句之后，记录在binlog二进制文件中；
（2）slave端的IO线程连接上master端，并请求从指定bin log日志文件的指定pos节点位置（或者从最开始的日志）开始复制之后的日志内容。
（3）master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
（4）slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中（该文件存slave端），以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步；
（5）slave端的SQL线程在检测到relay log文件中新增内容后，就马上解析该relay log文件中的内容，然后还原成在master端真实执行的那些SQL语句，再按顺序依次执行这些SQL语句，从而到达master端和slave端的数据一致性；

10.3、主从复制的好处：

（1）读写分离，通过动态增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上执行读功能。
（2）提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。
（3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

10.4、MySQL支持的复制类型及其优缺点：

binlog日志文件有两种格式，一种是Statement-Based（基于语句的复制），另一种是Row-Based（基于行的复制）。默认格式为Statement-Based，如果想改变其格式在开启服务的时候使用 -binlog-format 选项，其具体命令如下：

mysqld_safe –user=msyql –binlog-format=格式 &

（1）基于语句的复制（Statement-Based）：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。

优点：

① 因为记录的SQL语句，所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
② binlog日志文件记录了所有的改变数据库的语句，所以此文件可以作为数据库的审核依据。

缺点：

① 不安全，不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如：对于 delete 或者 update 语句，如果使用了 limit 但是并没有 order by ，这就属于非确定性的语句，就不会被记录。
② 对于没有索引条件的update，insert……select 语句，必须锁定更多的数据，降低了数据库的性能。

（2）基于行的复制（Row-Based）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从mysql5.0开始支持；

优点：

① 所有的改变都会被复制，这是最安全的复制方式；
② 对于 update、insert……select等语句锁定更少的行；

缺点：

① 不能通过binlog日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到什么数据改变。
② 因为记录的是数据，所以说binlog日志文件占用的存储空间要比Statement-based大。
③ 对于数据量大的操作其花费的时间有更长。

（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

有关主从复制更详细的内容，请阅读这篇文章：https://blog.csdn.net/a745233700/article/details/85256818

11、读写分离：

11.1、实现原理：

读写分离解决的是，数据库的写操作，影响了查询的效率，适用于读远大于写的场景。读写分离的实现基础是主从复制，主数据库利用主从复制将自身数据的改变同步到从数据库集群中，然后主数据库负责处理写操作（当然也可以执行读操作），从数据库负责处理读操作，不能执行写操作。并可以根据压力情况，部署多个从数据库提高读操作的速度，减少主数据库的压力，提高系统总体的性能。

11.2、读写分离提高性能的原因：

（1）增加物理服务器，负荷分摊；
（2）主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；
（3）从库可配置MyISAM引擎，提升查询性能以及节约系统开销；
（4）主从复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务。

11.3、Mysql读写分写的实现方式：

（1）基于程序代码内部实现：在代码中根据select 、insert进行路由分类。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
（2）基于中间代理层实现：代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库，有以下代表性的代理层。

12、分库分表：垂直分表、垂直分库、水平分表、水平分库

读写分离解决的是数据库读写操作的压力，但是没有分散数据库的存储压力，利用分库分表可以解决数据库的储存瓶颈，并提升数据库的查询效率。

12.1、垂直拆分：

（1）垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

优点：

（1）避免IO竞争减少锁表的概率。因为大的字段效率更低，第一，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多；第二数据量大，需要的读取时间长。
（2）可以更好地提升热门数据的查询效率。

（2）垂直分库：按照业务模块的不同，将表拆分到不同的数据库中，适合业务之间的耦合度非常低、业务逻辑清晰的系统。

优点：

降低业务中的耦合，方便对不同的业务进行分级管理
可以提升IO、数据库连接数、解决单机硬件存储资源的瓶颈问题

（3）垂直拆分（分库、分表）的缺点：

主键出现冗余，需要管理冗余列
事务的处理变得复杂
仍然存在单表数据量过大的问题

12.2、水平拆分：

（1）水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

优点：

解决了单表数据量过大的问题
避免IO竞争并减少锁表的概率

（2）水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

优点：

解决了单库大数据量的瓶颈问题
IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库，提高了系统的稳定性和可用性

（3）水平拆分（分表、分库）的缺点：

分片事务一致性难以解决
跨节点JOIN性能差，逻辑会变得复杂
数据扩展难度大，不易维护

12.3、分库分表存在的问题的解决：

（1）事务的问题：

① 方案一：使用分布式事务：

优点：由数据库管理，简单有效。
缺点：性能代价高，特别是shard越来越多。

② 方案二：程序与数据库共同控制实现，原理就是将一个跨多个数据库的分布式事务分解成多个仅存在于单一数据库上面的小事务，并交由应用程序来总体控制各个小事务。

优点：性能上有优势；
缺点：需要在应用程序在事务上做灵活控制。如果使用了spring的事务管理，改动起来会面临一定的困难。

（2）跨节点 Join 的问题：

解决该问题的普遍做法是分两次查询实现：在第一次查询的结果集中找出关联数据的id，根据这些id发起第二次请求得到关联数据。

（3）跨节点count，order by，group by，分页和聚合函数问题：

由于这类问题都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作，解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个结点的查询可以并行执行，因此速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

12.4、分库分表后，ID键如何处理？

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

（1）UUID：

优点：本地生成ID，不需要远程调用，全局唯一不重复。
缺点：占用空间大，不适合作为索引。

（2）数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。

优点：简单易实现。
缺点：在高并发下存在瓶颈。

（3）Redis生成ID：

优点：不依赖数据库，性能比较好。
缺点：引入新的组件会使得系统复杂度增加

（4）Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。

1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数.
41bit：表示的是时间戳，单位是毫秒。
10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。
12bit：用来记录同一毫秒内产生的不同ID。

（5）美团的Leaf分布式ID生成系统，美团点评分布式ID生成系统：

13、分区：

分区就是将表的数据按照特定规则存放在不同的区域，也就是将表的数据文件分割成多个小块，在查询数据的时候，只要知道数据数据存储在哪些区域，然后直接在对应的区域进行查询，不需要对表数据进行全部的查询，提高查询的性能。同时，如果表数据特别大，一个磁盘磁盘放不下时，我们也可以将数据分配到不同的磁盘去，解决存储瓶颈的问题，利用多个磁盘，也能够提高磁盘的IO效率，提高数据库的性能。在使用分区表时，需要注意分区字段必须放在主键或者唯一索引中、每个表最大分区数为1024；常见的分区类型有：Range分区、List分区、Hash分区、Key分区，

（1）Range分区：按照连续的区间范围进行分区
（2）List分区：按照给定的集合中的值进行选择分区。
（3）Hash分区：基于用户定义的表达式的返回值进行分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
（4）Key分区：类似于按照HASH分区，区别在于Key分区只支持计算一列或多列，且key分区的哈希函数是由 MySQL 服务器提供。

（1）表分区的优点：

① 可伸缩性：

将分区分在不同磁盘，可以解决单磁盘容量瓶颈问题，存储更多的数据，也能解决单磁盘的IO瓶颈问题。

② 提升数据库的性能：

减少数据库检索时需要遍历的数据量，在查询时只需要在数据对应的分区进行查询。
避免Innodb的单个索引的互斥访问限制
对于聚合函数，例如sum()和count()，可以在每个分区进行并行处理，最终只需要统计所有分区得到的结果

③ 方便对数据进行运维管理：

方便管理，对于失去保存意义的数据，通过删除对应的分区，达到快速删除的作用。比如删除某一时间的历史数据，直接执行truncate，或者直接drop整个分区，这比detele删除效率更高；
在某些场景下，单个分区表的备份很恢复会更有效率。

14、主键一般用自增ID还是UUID？

（1）自增ID：

使用自增ID的好处：

字段长度较 UUID 会小很多。
数据库自动编号，按顺序存放，利于检索
无需担心主键重复问题

使用自增ID的缺点：

因为是自增，在某些业务场景下，容易被其他人查到业务量。
发生数据迁移时，或者表合并时会非常麻烦
在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

（2）UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

使用UUID的优点：

唯一标识，不用考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。
可以在应用层生成，提高数据库的吞吐能力。
无需担心业务量泄露的问题。

使用UUID的缺点：

因为UUID是随机生成的，所以会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低。
UUID占用空间较大，建立的索引越多，造成的影响越大。
UUID之间比较大小较自增ID慢不少，影响查询速度。

一般情况下，MySQL推荐使用自增ID，因为在MySQL的 InnoDB 存储引擎中，主键索引是聚簇索引，主键索引的B 树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

15、视图View：

视图是从一个或者多个表（或视图）导出的表，其内容由查询定义。视图是一个虚拟表，数据库中只存储视图的定义，不存储视图对应的数据，在对视图的数据进行操作时，系统根据视图的定义去操作相应的基本表。可以说，视图是在基本表之上建立的表，它的结构和内容都来自基本表，依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

（1）视图的优点：

简化了操作，把经常使用的数据定义为视图
安全性，用户只能查询和修改能看到的数据
逻辑上的独立性，屏蔽了真实表的结构带来的影响

（2）视图的缺点：

性能差，数据库必须把对视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

16、存储过程Procedure：

SQL语句需要先编译然后执行，而存储过程就是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过制定存储过程的名字并给定参数来调用它。

用程序也可以实现操作数据库的复杂逻辑，那为什么需要存储过程呢？主要是因为使用程序调用API执行，其效率相对较慢，应用程序需通过引擎把SQL语句交给MYSQL引擎来执行，那还不如直接让MySQL负责它最精通最能够完成的工作。

存储过程的优点：

（1）标准组件式编程：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且DBA可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（2）更快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
（3）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判杂的断和较复运算。
（4）减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
（5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

17、触发器Trigger：

触发器是与表有关的数据库对象，当触发器所在表上出现指定事件并满足定义条件的时候，将执行触发器中定义的语句集合。触发器的特性可以应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程，不同的是存储过程要用call来调用，而触发器不需要使用call，也不需要手工调用，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

18、游标Cursor：

游标，就是游动的标识，可以充当指针的作用，使用游标可以遍历查询数据库返回的结果集中的所有记录，但是每次只能提取一条记录，即每次只能指向并取出一行的数据，以便进行相应的操作。当你没有使用游标的时候，相当于别人一下给你所有的东西让你拿走；用了游标之后，相当于别人一件一件的给你，这时你可以先看看这个东西好不好，再自己进行选择。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100019.html原文链接：https://javaforall.cn

全文检索数据结构数据库 sql 云数据库SQLServer

0 人点赞