代码随想录每日一题：数据库

SQL的执行

一条SQL查询语句是如何执行的？

大体来说，MySQL 可以分为Server层和存储引擎层两部分。

1.连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

2.查询缓存：MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。

MySql8.0之后，删除了查询缓存。

3.分析器：你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

4.优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引;或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。

5.执行器：MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

事务隔离级别

说一说事务隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务特性

事务的四大特性有哪些

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性：指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

隔离性：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

持久性：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

索引的类型

索引有哪些种类？

一、从数据结构维度进行分类：

①B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。

②哈希索引：适合等值查询，检索效率高，一次到位。

③全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char，text，varchar类型上创建。

④R-Tree索引：用来对GIS数据类型创建SPATIAL索引。

二、从物理存储维度进行分类：

①聚集索引：数据存储与索引一起存放，叶子节点会存储一整行记录，找到索引也就找到了数据。

②非聚集索引：数据存储与索引分开存放，叶子节点不存储数据，存储的是数据行地址。

三、从逻辑维度进行分类：

①主键索引：一种特殊的唯一索引，不允许有空值。

②普通索引：MySQL中基本索引类型，允许空值和重复值。

③联合索引：多个字段创建的索引，使用时遵循最左前缀原则。

④唯一索引：索引列中的值必须是唯一的，但是允许为空值。

⑤空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

B+树作索引

MySQL什么使用B+树来作索引，它的优势什么？

1. 单点查询：B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到。从平均时间代价来看，会比 B+ 树稍快一些。但是 B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，数据量相同的情况下，B+树的非叶子节点可以存放更多的索引，查询底层节点的磁盘 I/O次数会更少。

2. 插入和删除效率：B+ 树有大量的冗余节点，删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，删除非常快。B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。B 树没有冗余节点，删除节点的时候非常复杂，可能涉及复杂的树的变形。

3. 范围查询：B+ 树所有叶子节点间有一个链表进行连接，而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，范围查询效率不如 B+ 树。B+ 树的插入和删除效率更高。存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如nosql的MongoDB。

何时建索引

什么时候需要创建索引，什么时候不需要创建索引？

何时需要创建索引：

表的主关键字：自动建立唯一索引。

直接条件查询的字段：经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度。

查询中与其它表关联的字段：例如字段建立了外键关系。

查询中排序的字段：排序的字段如果通过索引去访问将大大提高排序速度。

唯一性约束列： 如果某列具有唯一性约束，那么为了确保数据的唯一性，可以在这些列上创建唯一索引。

大表中的关键列： 在大表中，如果查询的效率变得很低，可以考虑在关键列上创建索引。

何时不需要创建索引：

小表： 对小表创建索引可能会带来额外的开销，因为在小数据集中扫描整个表可能比使用索引更快。

频繁的插入、更新和删除操作： 索引的维护成本会随着数据的插入、更新和删除操作而增加。如果表经常被修改，过多的索引可能会影响性能。

数据重复且分布平均的表字段：假如一个表有10万行记录，性别只有男和女两种值，且每个值的分布概率大约为50%，那么对这种字段建索引一般不会提高数据库的查询速度。

很少被查询的列： 如果某列很少被用于查询条件，那么为它创建索引可能没有明显的性能提升。

查询结果总行数较少的表： 如果查询的结果集总行数很少，使用索引可能不会有太大的性能提升。

MVCC机制

说下你了解的MVCC机制？并说明原理？

MVCC（Multi-Version Concurrency Control，多版本并发控制），用于管理多个事务同时访问和修改数据库的数据，而不会导致数据不一致或冲突。MVCC的核心思想是每个事务在数据库中看到的数据版本是事务开始时的一个快照，而不是实际的最新版本。这使得多个事务可以并发执行，而不会互相干扰。

索引失效

索引失效的场景有哪些？

OR条件：当查询中使用多个OR条件时，如果这些条件不涉及同一列，索引可能无法有效使用。数据库可能会选择全表扫描而不是使用多个索引。

对列进行类型转换：如果在查询中对列进行类型转换，例如将字符列转换为数字或日期，索引可能会失效。

使用通配符前缀搜索：在使用通配符前缀（如LIKE ‘prefix%’）进行搜索时，大多数索引无法使用，因为索引通常是按照列的完整值进行排序的。

不等号条件：当查询中包含不等号条件（如>，<，>=，<=）时，索引可能会失效。通常情况下，索引只能用于等值比较。

表连接中的列类型不匹配：如果在连接操作中涉及的两个表的列类型不匹配，索引可能会失效。例如，一个表的列是整数，另一个表的列是字符，连接时可能会导致索引失效。

在索引列上进行计算、函数调用或类型转换等操作，会导致索引失效。

执行引擎

MySQL的执行引擎有哪些？

主要有MyISAM、InnoDB、Memery等引擎：

InnoDB引擎提供了对事务ACID的支持，还提供了行级锁和外键的约束。

MyISAM引擎不支持事务，也不支持行级锁和外键约束。

Memery就是将数据放在内存中，数据处理速度很快，但是安全性不高。

MySQL日志类型

MySQL日志文件有哪几种？详细说一说每种日志

MySQL日志文件主要有以下几种：

1. 错误日志（Error Log）：

   • 文件名： 默认情况下，错误日志文件名为主机名后跟.err扩展名。例如，如果主机名是 “myhost”，则错误日志文件为 “myhost.err”。
   • 作用： 记录MySQL服务器在启动和运行过程中的错误和警告信息，以便进行故障排查。

2. 查询日志（Query Log）：

   • 文件名： 默认情况下，查询日志是禁用的。如果启用，文件名为主机名后跟-slow.log扩展名。
   • 作用： 记录执行时间超过阈值的慢查询语句，有助于性能优化和定位问题。

3. 二进制日志（Binary Log）：

   • 文件名： 默认情况下，二进制日志文件以主机名开头，后跟一个唯一的序列号，例如 “hostname-bin.000001”。
   • 作用： 记录对数据库执行的所有更改操作，包括插入、更新、删除等，用于数据恢复、主从复制和点播日志等场景。

4. 慢查询日志（Slow Query Log）：

   • 文件名： 默认情况下，慢查询日志是禁用的。如果启用，文件名为主机名后跟-slow.log扩展名。
   • 作用： 记录执行时间超过阈值的慢查询语句，有助于性能优化和定位问题。

5. 中继日志（Relay Log）：

   • 文件名： 中继日志文件以主机名开头，后跟一个唯一的序列号，例如 “hostname-relay-bin.000001”。
   • 作用： 在主从复制中，从服务器上的中继日志用于存储从主服务器接收到的二进制日志事件，以便在从服务器上重新执行这些事件。

MySQL锁类型

MySQL 有哪些锁？作用分别是什么？

全局锁：主要是全库逻辑备份，在数据库备份时，防止数据或表结构的修改，造成与预期不一致的情况，全局锁后，数据变为只读。

表级锁：

元数据锁(MDL)：对数据库表进行操作时，会自动为表生成元数据锁，保证当前用户对记录进行CRUD操作，其他线程可以修改表的结构，只有事务提交之后，才会解锁。

意向锁：加共享之前，需要在表级别上加入共享意向锁，加独占锁之前，需要在表级别上加入独占意向锁。普通的select不需要加入行级锁，使用MVCC实现一致性读。

AUTO-INC锁：主要用于确保主键是自增的，在插入是，会加入表级别的AUTO-INC锁，等语句执行完成后，才会释放。其他事务要进行插入才做会发生阻塞，数据库的主键递增是由其实现的。

行级锁：

记录锁：用于锁住一条记录，有排他锁和共享锁。

间隙锁：只存在于可重复读隔离级别，用于解决幻读问题，间隙锁是可兼容的，可以多个事务同时持有包含共同间隙范围的间隙锁，不发生互斥。

Next-Key Lock：是记录锁和间隙锁的结合，锁定了一定的时间范围，同时锁定记录，即保护了数据，也防止其他事务在此间隙插入新的记录。

插入意向锁：插入一个记录时，会先判断该位置是否有间隙锁，如果有，就阻塞等待，此时可以发出插入意向锁，告知其有事务想在某个间隙插入新记录，但是现在处于等待状态。