MySQL数据库面试题

MySQL数据库面试题

---

目录（重点部分用🚩标注）：

• MySQL常用的存储引擎有什么区别？(🚩🚩🚩）

• 为什么要用索引？

• 索引算法有哪些？(🚩）

• Hash索引和B+树的区别？(🚩🚩🚩）

• B树和B+树的区别？(🚩🚩🚩）

• 数据库为什么使用B+树而不是B树？(🚩🚩🚩）

• 索引的种类有哪些？

• 创建索引的原则有哪些？（🚩🚩）

• 什么是最左匹配原则？(🚩🚩🚩）

• 什么是聚簇索引，什么是非聚簇索引？(🚩🚩🚩）

• 数据库的三大范式是什么？(🚩🚩🚩）

• 索引在什么情况下会失效？(🚩🚩🚩）

• 什么是数据库的事务？

• 事务的四大特性(ACID)是什么？(🚩）

• 数据库的并发一致性问题(🚩🚩🚩）

• 数据库的隔离级别有哪些？(🚩🚩🚩）

• 什么是MVCC？(🚩🚩🚩）

• MySQL中都有哪些触发器？

• SQL语句主要分为哪几类

• 超键、候选键、主键、外键分别是什么？

• 表之间的关联有哪些？

• UNION与UNION ALL的区别？

• type访问类型有哪些？(🚩🚩🚩）

• 一条SQL查询语句的执行流程?(🚩🚩🚩）

• SQL 约束有哪几种？

• drop、delete与truncate的区别？

• 什么是数据库的锁？

• 数据库的锁与隔离级别的关系？(🚩）

• 数据库锁的类型有哪些？(🚩）

• 什么是数据库的乐观锁和悲观锁，如何实现？(🚩🚩🚩）

• 什么是死锁？如何避免？(🚩🚩🚩）

• drop、delete和truncate的区别？

• UNION和UNION ALL的区别？

• 大表数据查询如何进行优化？

• 如何对慢查询进行优化？(🚩🚩🚩）

• 主键一般用自增ID还是UUID？(🚩🚩🚩）

• 如何优化查询过程中的数据访问？　(🚩）

• 如何优化WHERE子句

• 执行顺序(🚩）

• MySQL的复制原理及流程？如何实现主从复制？(🚩🚩🚩）

• 了解读写分离吗？(🚩）

• 数据库开窗函数(🚩🚩🚩）

---

MySQL常用的存储引擎有什么区别？(🚩🚩🚩）

常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB区别

	MyISAM	Innodb
存储结构	每张表被存放在三个文件： frm表格定义、MYD(MYData)数据文件、 MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中.ibd  frm表格定义、（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

总结

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

为什么要用索引？

索引是一种数据结构。数据库索引，是对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

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

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

1. 把创建了索引的列的内容进行排序

2. 对排序结果生成倒排表

3. 在倒排表内容上拼上数据地址链

4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

优点：

• 大大加快数据检索的速度。

• 将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)

• 加速表与表之间的连接

缺点：

• 从空间角度考虑，建立索引需要占用物理空间

• 从时间角度 考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

索引算法有哪些？(🚩）

索引算法有 BTree算法和Hash算法

• B+树索引

熟悉数据结构的同学都知道，B+树、平衡二叉树、红黑树都是经典的数据结构。在B+树中，所有的记录节点都是按照键值大小的顺序放在叶子节点上，如下图。

B+树的索引又可以分为主索引和辅助索引。其中主索引为聚簇索引，辅助索引为非聚簇索引。聚簇索引是以主键作为B+ 树索引的键值所构成的B+树索引，聚簇索引的叶子节点存储着完整的数据记录；非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引，非聚簇索引的叶子节点存储着主键值。所以使用非聚簇索引进行查询时，会先找到主键值，然后到根据聚簇索引找到主键对应的数据域。上图中叶子节点存储的是数据记录，为聚簇索引的结构图，非聚簇索引的结构图如下：

Mysql 聚簇索引和非聚簇索引的区别_坚持，

• 哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。

Hash索引和B+树的区别？(🚩🚩🚩）

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。

B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

因为两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下，会选择使用B+树索引。

• 哈希索引不支持排序，因为哈希表是无序的。

• 哈希索引不支持范围查找。

• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。

• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

B树和B+树的区别？(🚩🚩🚩）

B树和B+树最主要的区别主要有两点：

• B树中的内部节点和叶子节点均存放键和值，

• B+树的内部节点只有键没有值，叶子节点存放所有的键和值。

• B＋树的叶子节点是通过相连在一起的，方便顺序检索。

  两者的结构图如下。

B树和B+树的区别

数据库为什么使用B+树而不是B树？(🚩🚩🚩）

• B树适用于随机检索，而B+树适用于随机检索和顺序检索

• B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。

• B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便

• B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。

那在什么情况适合使用B树呢，因为B树的内部节点也可以存储值，所以可以把一些频繁访问的值放在距离根节点比较近的地方，这样就可以提高查询效率。综上所述，B+树的性能更加适合作为数据库的索引。

索引的种类有哪些？

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

• 组合索引：由多个列值组成的索引。

• 唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。

• 全文索引：对文本的内容进行搜索。

• 普通索引：基本的索引类型，可以为NULL

创建索引的原则有哪些？（🚩🚩）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。（下面有详细说明）

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

什么是最左匹配原则？(🚩🚩🚩）

最左匹配原则：从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。

例如建立索引(a,b,c)，大家可以猜测以下几种情况是否用到了索引。

• 第一种

  select * from table_name where a = 1 and b = 2 and c = 3 
  select * from table_name where b = 2 and a = 1 and c = 3
  

  上面两次查询过程中所有值都用到了索引，where后面字段调换不会影响查询结果，因为MySQL中的优化器会自动优化查询顺序。

• 第二种

  select * from table_name where a = 1
  select * from table_name where a = 1 and b = 2  
  select * from table_name where a = 1 and b = 2 and c = 3
  

  答案是三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。

• 第三种

  select * from table_name where  b = 1 
  select * from table_name where  b = 1 and c = 2 
  

  答案是这两个查询语句都没有用到索引，因为不是从最左边开始匹配的

• 第四种

  select * from table_name where a = 1 and c = 2 
  

  这个查询语句只有a列用到了索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的。

• 第五种

  select * from table_name where  a = 1 and b < 3 and c < 1
  

  这个查询中只有a列和b列使用到了索引，而c列没有使用索引，因为根据最左匹配查询原则，遇到范围查询会停止。

• 第六种

  select * from table_name where a like 'ab%'; 
  select * from table_name where  a like '%ab'
  select * from table_name where  a like '%ab%'
  

  对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。

什么是聚簇索引，什么是非聚簇索引？(🚩🚩🚩）

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

• 聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。

• 非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

可以从非常经典的两张图看看它们的区别(图片来源于网络)：

数据库的三大范式是什么？(🚩🚩🚩）

第一范式（1NF）：字段不可分；

第二范式（2NF）：有主键，非主键字段依赖主键；

第三范式（3NF）：非主键字段不能相互依赖。

再通俗的说：

1NF：原子性。 字段不可再分,否则就不是关系数据库;

2NF：唯一性 。一个表只说明一个事物；

3NF：每列都与主键有直接关系，不存在传递依赖。

索引在什么情况下会失效？(🚩🚩🚩）

在上面介绍了几种不符合最左匹配原则的情况会导致索引失效，除此之外，以下这几种情况也会导致索引失效。

• 条件中有or，例如select * from table_name where a = 1 or b = 3

• 在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2

• 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。

• 在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1

• 在使用like查询时以%开头会导致索引失效

• 索引上使用！、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1

• 索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

什么是数据库的事务？

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

事务的四大特性(ACID)是什么？(🚩）

• 原子性：原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。【基本】

• 一致性：一致性指事务在执行前后状态是一致的。

• 隔离性：一个事务所进行的修改在最终提交之前，对其他事务是不可见的。

• 持久性：数据一旦提交，其所作的修改将永久地保存到数据库中。【磁盘】

数据库的并发一致性问题(🚩🚩🚩）

当多个事务并发执行时，可能会出现以下问题：

• 脏读：事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后【但是没有提交】的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了。

• 不可重复读：事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致。

• 幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致。

• 丢失修改：事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改覆盖了事务A的修改。

不可重复度和幻读看起来比较像，它们主要的区别是：

在不可重复读中，发现数据不一致主要是数据被更新了。

在幻读中，发现数据不一致主要是数据增多或者减少了。

数据库的隔离级别有哪些？(🚩🚩🚩）

• 未提交读：一个事务在提交前，它的修改对其他事务也是可见的。

• 提交读：一个事务提交之后，它的修改才能被其他事务看到。

• 可重复读：在同一个事务中多次读取到的数据是一致的。

• 串行化：需要加锁实现，会强制事务串行执行。

数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题。

隔离级别	脏读	不可重复读	幻读
未提交读	允许	允许	允许
提交读	不允许	允许	允许
可重复读	不允许	不允许	允许
串行化	不允许	不允许	不允许

MySQL的默认隔离级别是可重复读。

事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的，

提交读和可重复读可以通过MVCC实现，串行化可以通过锁机制实现。

什么是MVCC？(🚩🚩🚩）

MVCC(multiple version concurrent control)是一种控制并发的方法，主要用来提高数据库的并发性能。

在了解MVCC时应该先了解当前读和快照读。

• 当前读：读取的是数据库的最新版本[加锁读]，并且在读取时要保证其他事务不会修改当前记录，所以会对读取的记录加锁。

• 快照读：不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗。

可以看到MVCC的作用就是在不加锁的情况下【快照读】，解决数据库读写冲突问题，并且解决脏读、幻读、不可重复读等问题，但是不能解决丢失修改问题。

MVCC的实现原理：

• 版本号

  系统版本号：是一个自增的ID，每开启一个事务，系统版本号都会递增。

  事务版本号：事务版本号就是事务开始时的系统版本号，可以通过事务版本号的大小判断事务的时间顺序。

• 行记录隐藏的列

  DB_ROW_ID：所需空间6byte，隐含的自增的行ID，用来生成聚簇索引，如果数据表没有指定聚簇索引，InnoDB会利用这个隐藏ID创建聚簇索引。

  DB_TRX_ID：所需空间6byte，最近修改的事务ID，记录创建这条记录或最后一次修改这条记录的事务ID。

  DB_ROLL_PTR：所需空间7byte，回滚指针，指向这条记录的上一个版本。

  它们大致长这样，省略了具体字段的值。·

• undo日志

  MVCC做使用到的快照会存储在Undo日志中，该日志通过回滚指针将一个一个数据行的所有快照连接起来。它们大致长这样。

img

数据库中的多版本并发控制（MVCC） - 起风了

从上面的分析可以看出，事务对同一记录的修改，记录的各个会在Undo日志中连接成一个线性表，在表头的就是最新的旧纪录。

在重复读的隔离级别下，InnoDB的工作流程：

• SELECT

  作为查询的结果要满足两个条件：

• 1. 当前事务所要查询的数据行快照的创建版本号必须小于当前事务的版本号，这样做的目的是保证当前事务读取的数据行的快照要么是在当前事务开始前就已经存在的，要么就是当前事务自身插入或者修改过的。

  2. 当前事务所要读取的数据行快照的删除版本号必须是大于当前事务的版本号，如果是小于等于的话，表示该数据行快照已经被删除，不能读取。

  3. 总结就是要大于创建时间，小于删除时间

• INSERT

  将当前系统版本号作为数据行快照的创建版本号。

• DELETE

  将当前系统版本号作为数据行快照的删除版本号。

• UPDATE

  保存当前系统版本号为更新前的数据行快照创建行版本号，并保存当前系统版本号为更新后的数据行快照的删除版本号，其实就是，先删除再插入即为更新。

总结一下，MVCC的作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题，它可以实现提交读和可重复度两个隔离级。

隔离级别	脏读	不可重复读	幻读
未提交读	允许	允许	允许
提交读	不允许	允许	允许
可重复读	不允许	不允许	允许
串行化	不允许	不允许	不允许

MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

• Before Insert

• After Insert

• Before Update

• After Update

• Before Delete

• After Delete

SQL语句主要分为哪几类

• 数据定义语言DDL（Data Ddefinition Language） CREATE，DROP，ALTER

  主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

• 数据查询语言DQL（Data Query Language） SELECT

  这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

• 数据操纵语言DML（Data Manipulation Language） INSERT，UPDATE，DELET

  主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

• 候选键：是最小超键，即没有冗余元素的超键。

• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。

  一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

• 外键：在一个表中存在的另一个表的主键称此表的外键。

表之间的关联有哪些？

• 内连接（INNER JOIN）

• 外连接（LEFT JOIN/RIGHT JOIN）

• 联合查询（UNION与UNION ALL）

• 全连接（FULL JOIN）

• 交叉连接（CROSS JOIN）

full join 一定需要 on 条件的匹配，一条匹配全部输出

cross join 是笛卡尔积，没有匹配都能输出

详情见 ： https://hiszm.blog.csdn.net/article/details/119736109

UNION与UNION ALL的区别？

• 如果使用UNION ALL，不会合并重复的记录行

• 效率 UNION 高于 UNION ALL

type访问类型有哪些？(🚩🚩🚩）

type 表示 MySQL 决定如何查找表中的行，从最差到最优有如下几种取值:

1. ALL:

   • 全表扫描，通常意味着MySQL必须扫描整张表，从头到尾，去找到需要的行

   • 有例外，如在查询里使用了LIMIT，或者在Extra列中显示“Using distinct/not exists”

2. index:

   • 这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行，主要优点是避免了排序

   • 缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大

   • 如果在Extra列中看到“Using index”，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行

3. range：

   • 范围扫描就是一个有限制的索引扫描，比全索引扫描好一些，因为它用不着遍历全部索引

   • 当MySQL使用索引去查找一系列值时，例如IN()和OR列表，也会显示为范围扫描。然而，这两者其实是相当不同的访问类型，在性能上有重要的差异

4. ref:

   • 一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行

   • 只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生

   • ref_or_null 是ref之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目

5. eq_ref

   • 一种索引访问，MySQL知道最多只返回一条符合条件的记录

   • 在MySQL使用主键或者唯一性索引查找时发生

6. const, system

   • 当MySQL能对查询的某部分进行优化并将其转换成一个常量时，就会使用这些访问类型

   • 例如，如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键，MySQL 就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。

7. NULL

   • 这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引

   • 例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表

一条SQL查询语句的执行流程?(🚩🚩🚩）

流程描述：

1. 客户端 发起请求；

2. 请求到达 连接器（验证用户身份，给予权限）；

3. 查询缓存（存在缓存则直接返回，不存在则执行后续操作）；

4. 请求到达 分析器（对SQL进行词法分析和语法分析）；

5. 请求到达 优化器（主要对执行的SQL优化，选择最优的执行方案）；

6. 请求到达 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）；

7. 去引擎层 获取数据并返回（如果开启查询缓存，则会将查询的结果缓存起来）。

SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

• CHECK: 用于控制字段的值范围。

drop、delete与truncate的区别？

三者都表示删除，但是三者有一些差别：

	Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

总结

在不再需要一张表的时候，用drop；

在想删除部分数据行时候，用delete；

在保留表而删除所有数据的时候用truncate。

什么是数据库的锁？

当数据库有并发事务的时候，保证数据访问顺序的机制称为锁机制。

数据库的锁与隔离级别的关系？(🚩）

隔离级别	实现方式
未提交读	总是读取最新的数据，无需加锁
提交读	读取数据时加共享锁，读取数据后释放共享锁
可重复读	读取数据时加共享锁，事务结束后释放共享锁
串行化	锁定整个范围的键，一直持有锁直到事务结束

数据库锁的类型有哪些？(🚩）

按照锁的粒度可以将MySQL锁分为三种：

MySQL锁类别	资源开销	加锁速度	是否会出现死锁	锁的粒度	并发度
表级锁	小	快	不会	大	低
行级锁	大	慢	会	小	高
页面锁	一般	一般	不会	一般	一般

MyISAM默认采用表级锁，InnoDB默认采用行级锁。

从锁的类别上区别可以分为共享锁和排他锁

• 共享锁：共享锁又称读锁，简写为S锁，一个事务对一个数据对象加了S锁，可以对这个数据对象进行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加S锁，不能加X锁。共享锁可以同时加上多个。

• 排他锁：排他锁又称为写锁，简写为X锁，一个事务对一个数据对象加了X锁，可以对这个对象进行读取和更新操作，加锁期间，其他事务不能对该数据对象进行加X锁或S锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

什么是数据库的乐观锁和悲观锁，如何实现？(🚩🚩🚩）

乐观锁：系统假设数据的更新在大多数时候是不会产生冲突的，所以数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。

乐观锁实现方式：一般通过版本号和CAS算法实现。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁。

悲观锁的实现方式：通过数据库的锁机制实现，对查询语句添加for updata。

select * from table where id=1 for update

CAS，即 Compare And Swap(比较与交换)，是一种无锁算法，基于硬件原语实现，能够在不使用锁的情况下实现多线程之间的变量同步。jdk中的java.util.concurrent.atomic包中的原子类就是通过CAS来实现了乐观锁。

CAS算法过程(🚩）

算法涉及到三个操作数：

• 需要读写的内存位置V

• 需要进行比较的预期值A

• 需要写入的新值U

CAS算法解析：

CAS具体执行时，当且仅当预期值A符合内存地址V中存储的值时，就用新值U替换掉旧值，并写入到内存地址V中。否则不做更新。

CAS算法的运行原理如下如所示：

CAS会有如下三个方面的问题：

1.ABA问题，一个线程将内存值从A改为B，另一个线程又从B改回到A。

2.循环时间长开销大：CAS算法需要不断地自旋来读取最新的内存值，长时间读取不到就会造成不必要的CPU开销。

3.只能保证一个共享变量的原子操作（jdk的AtomicReference来保证应用对象之间的原子性，可以把多个变量放在一个对象里来进行CAS操作，解决了这一问题）。

ABA问题图解：

ABA问题解决方案：在变量前面添加版本号，每次变量更新的时候都将版本号加1，比如juc的原子包中的AtomicStampedReference类。

什么是死锁？如何避免？(🚩🚩🚩）

死锁是指两个或者两个以上进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。

在MySQL中，MyISAM是一次获得所需的全部锁，要么全部满足，要么等待，所以不会出现死锁。

在InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题。

如何避免MySQL发生死锁或锁冲突：

• 如果不同的程序并发存取多个表，尽量以相同的顺序访问表。

• 在程序以批量方式处理数据的时候，如果已经对数据排序，尽量保证每个线程按照固定的顺序来处理记录。

• 在事务中，如果需要更新记录，应直接申请足够级别的排他锁，而不应该先申请共享锁，更新时在申请排他锁，因为在当前用户申请排他锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁冲突或者死锁。

• 尽量使用较低的隔离级别

• 尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会

• 合理选择事务的大小，小事务发生锁冲突的概率更低

• 尽量用相等的条件访问数据，可以避免Next-Key锁对并发插入的影响。

• 不要申请超过实际需要的锁级别，查询时尽量不要显示加锁

• 对于一些特定的事务，可以表锁来提高处理速度或减少死锁的概率。

drop、delete和truncate的区别？

	drop	delete	truncate
速度	快	逐行删除，慢	较快
类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	删除整个表，数据行、索引都会被删除	表结构还在，删除表的一部分或全部数据	表结构还在，删除表的全部数据

一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。

UNION和UNION ALL的区别？

union和union all的作用都是将两个结果集合并到一起。

• union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。

• union all的性能比union性能好。

大表数据查询如何进行优化？

• 索引优化

• SQL语句优化

• 水平拆分

• 垂直拆分

• 建立中间表

• 使用缓存技术

• 固定长度的表访问起来更快

• 越小的列访问越快

如何对慢查询进行优化？(🚩🚩🚩）

慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。

相关参数：

• slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭。

• slow_query_log_file：MySQL数据库慢查询日志存储路径。

• long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上。

• log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中。

• log_output：日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。

方法：

1. 开启慢查询日志

配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，

果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，

它会在datadir下产生一个xxx-slow.log的文件。

1. 设置临界时间

配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

1. 查看日志，

一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 分析语句的执行计划，查看SQL语句的索引是否命中

• 优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表。

• 优化LIMIT分页。

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，

第一个参数指定第一个返回记录行的偏移量，

第二个参数指定返回记录行的最大数目。

初始记录行的偏移量是 0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10; 
// 检索记录行 6-15
mysql> SELECT * FROM table LIMIT 5; 
//检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。

主键一般用自增ID还是UUID？(🚩🚩🚩）

使用自增ID的好处：

• 字段长度较uuid会小很多。

• 数据库自动编号，按顺序存放，利于检索

• 无需担心主键重复问题

使用自增ID的缺点：

• 因为是自增，在某些业务场景下，容易被其他人查到业务量。

• 发生数据迁移时，或者表合并时会非常麻烦

• 在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

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

使用UUID的优点：

• 唯一标识，不会考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。

• 可以在应用层生成，提高数据库的吞吐能力。

• 无需担心业务量泄露的问题。

使用UUID的缺点：

• 因为UUID是随机生成的，所以会发生**随机IO，影响插入速度，**并且会造成硬盘的使用率较低。

• UUID占用空**间较大，建立的索引越多，**造成的影响越大。

• UUID之间比较大小较自增ID慢不少，影响查询速度。

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

如何优化查询过程中的数据访问？　(🚩）

从减少数据访问方面考虑：

• 正确使用索引，尽量做到索引覆盖

• 优化SQL执行计划

从返回更少的数据方面考虑：

• 数据分页处理

• 只返回需要的字段

从减少服务器CPU开销方面考虑：

• 合理使用排序

• 减少比较的操作

• 复杂运算在客户端处理

从增加资源方面考虑：

• 客户端多进程并行访问

• 数据库并行处理

如何优化WHERE子句

• 不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。

• 不要在where子句中使用null或空值判断，尽量设置字段为not null。

• 尽量使用union all代替or

• 在where和order by涉及的列建立索引

• 尽量减少使用in或者not in，会进行全表扫描

• 在where子句中使用参数会导致全表扫描

• 避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描

执行顺序(🚩）

from -> on -> join -> where -> group by -> having -> select ->  distinct -> order by 

• FROM：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1。虚表就是视图，数据会来自多张表的执行结果。

• ON：对FROM连接的结果进行ON过滤,并创建虚表V2

• JOIN：将ON过滤后的左表添加进来，并创建新的虚拟表V3

• WHERE：对虚拟表V3进行WHERE筛选，创建虚拟表V4

• GROUP BY：对V4中的记录进行分组操作，创建虚拟表V5

• HAVING：对V5进行过滤，创建虚拟表V6

• SELECT：将V6中的结果按照SELECT进行筛选，创建虚拟表V7

• DISTINCT：对V7表中的结果进行去重操作，创建虚拟表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都h是不同的。

• ORDER BY：对V8表中的结果进行排序。

MySQL的复制原理及流程？如何实现主从复制？(🚩🚩🚩）

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

MySQL主从复制工作原理

• 在主库上把数据更高记录到二进制日志

• 从库将主库的日志复制到自己的中继日志

• 从库    读取中继日志的事件，将其重放到从库数据中

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

• 【主】binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。

• 【从】I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。

• 【从】SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放

MySQL主从复制详解

主从复制的作用：

• 高可用和故障转移

• 负载均衡

• 数据备份

• 升级测试

了解读写分离吗？(🚩）

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

• 主服务器负责写，从服务器负责读，缓解了锁的竞争

• 从服务器可以使用MyISAM，提升查询性能及节约系统开销

• 增加冗余，提高可用性

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。

因为主从复制要求slave不能写只能读

如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave。

方案一

使用mysql-proxy代理

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用

缺点：降低性能， 不支持事务

方案二

使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。 如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select都访问salve库，这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。

方案三

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务.

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

数据库开窗函数(🚩🚩🚩）

目的

我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等, 这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚 集前的行数的。

但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,

这时我们便引入了窗口函数。

开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

应用

1. 用于分区排序

2. 动态GROUP BY

3. TOP N

4. 累计计算

5. 层次查询

格式

• 分区（partition by）

• 排序（order by）

• 范围（rows between或range between）

over(partition by col1 order by col2 rows between col3)

开窗函数=分析函数+Over();

row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)

开窗函数详细见：https://hiszm.blog.csdn.net/article/details/119824742

引用

https://hiszm.cn
https://hiszm.blog.csdn.net/article/details/119540143
https://mp.weixin.qq.com/s/REzOiTTNKstR1JHlu3thzQ
https://blog.csdn.net/jankin6/category_11191625.html
https://mp.weixin.qq.com/s_biz=MzU3MzgwNTU2Mg==&mid=2247487449&idx=1&sn=2cc97bf6669416267c25a7a46192b70