SQL总结

数据库的种类

常见的关系型数据库

SQLite、MySQL、Oracle等

常见的非关系型数据库

MongoDB：是最著名的NoSQL数据库。它是一个面向文档的开源数据库。MongoDB是一个可伸缩和可访问的数据库。它在c++中。MongoDB同样可以用作文件系统。在MongoDB中，JavaScript可以作为查询语言使用。通过使用sharding MongoDB水平伸缩。它在流行的JavaScript框架中非常有用。
Cassandra：是Facebook为收件箱搜索开发的。Cassandra是一个用于处理大量结构化数据的分布式数据存储系统。通常，这些数据分布在许多普通服务器上。您还可以添加数据存储容量，使您的服务保持在线，您可以轻松地完成这项任务。由于集群中的所有节点都是相同的，因此不需要处理复杂的配置。
Redis：是一个键值存储。此外，它是最著名的键值存储。Redis支持一些c++、PHP、Ruby、Python、Perl、Scala等等。Redis是用C语言编写的。此外，它是根据BSD授权的。
HBase：是一个分布式的、面向列的开源数据库，该技术来源于 Fay Chang 所撰写的Google论文“Bigtable：一个结构化数据的分布式存储系统”。就像Bigtable利用了Google文件系统（File System）所提供的分布式数据存储一样，HBase在Hadoop之上提供了类似于Bigtable的能力。
Neo4j：被称为原生图数据库，因为它有效地实现了属性图模型，一直到存储层。这意味着数据完全按照白板的方式存储，数据库使用指针导航和遍历图。Neo4j有数据库的社区版和企业版。企业版包括Community Edition必须提供的所有功能，以及额外的企业需求，如备份、集群和故障转移功能。

引擎

MyISAM

用途：访问的速度快，以 SELECT、INSERT 为主的应用
索引：B tree，FullText，R-tree
锁：表锁
事务：不支持事务
其他：不支持外键。每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为 .MYD (MYData)。索引文件的扩展名是 .MYI (MYIndex)。

InnoDB

用途：大部分情况下选择 InnoDB，除非需要用到某些 InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择 InnoDB 引擎。
索引：B+ tree，hash(引擎自适应，无法人为干预)，FullText(5.6开始)
锁：行锁
事务：支持
其他：对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。InnoDB 所有的表都保存在同一个数据文件中，InnoDB 表的大小只受限于操作系统文件的大小限制。MyISAM 只缓存索引，不缓存真实数据；InnoDB 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

特性

插入缓冲

插入缓冲 (Insert Buffer/Change Buffer) :提升插入性能，change buffering是insert buffer的加强insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效只对于非聚集索引(非唯一) 的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入;若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

预读

innoDB使用两种预读算法来提高I/O性能: 线性预读(linear read-ahead) 和随机预读 (randomread-ahead)
为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位线性预读着眼于将下一个extent提前读取到buffer pool中而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中

InnoDB和MyISAM的区别

事务支持：InnoDB支持事务处理，而MyISAM不支持。这意味着InnoDB可以保证数据的完整性，可以在一个事务中执行多个SQL语句，并且可以回滚事务以撤销之前的操作。

锁级别：InnoDB使用行级锁定，而MyISAM使用表级锁定。行级锁定可以避免锁定整个表，提高并发性能。

外键支持：InnoDB支持外键，而MyISAM不支持。外键可以用于维护表之间的引用完整性，防止数据丢失或不一致。

全文索引：MyISAM支持全文索引，而InnoDB在MySQL 5.6之前不支持全文索引（MySQL 5.6及以后版本支持全文索引）。

空间占用：MyISAM在处理大量数据时，比InnoDB更快，并且使用较少的存储空间。但是，在事务处理方面，InnoDB更加强大，因为它具有更多的数据完整性保障和更高的并发性能。

处理效率：当表中大量插入或更新操作时，MyISAM效率更高，因为它不需要为每次更新操作记录事务日志。而在高并发环境下，InnoDB比MyISAM更加适用，因为它可以提供更好的并发控制和事务管理。

索引

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

MySQL的索引实现

A

InnoDB 存储引擎支持以下几种常见的索引：

B+ 树索引
全文索引
哈希索引

B

mysql的存储引擎InnoDB采用的索引类型是B+Tree。MySQL 将所有的数据都放到了叶子节点中，而根节点和非叶子节点中放的都是冗余的索引 Key，树高度只有 3，就是说我们只需要三次磁盘的 io 就可以完成查找，还可以将根节点放到磁盘中，这样就只需要两次，速度也是相当于快。非叶子节点存储索引和下一个子节点的地址，叶子结点存储所有的索引和数据。

B+Tree索引

B+树索引虽然查询速度较慢，但是支持 范围查询和排序等操作，因此在实际应用中被广泛使用。

Hash索引

哈希索引不能按序访问，所以在许多情况下，它比B+树索引查询速度更快。然而，哈希索引只适用于等值查询，并且不支持范围查询和排序等操作。

索引的种类

聚焦索引（Clustered Index）：

聚焦索引是一种将数据表按照索引顺序进行存储的索引类型，因此，查询索引列时可以直接获取相邻的数据行，从而提高查询性能和效率。每个数据表只能有一个聚焦索引。

单列索引（Single Column Index）：单列索引是一种只针对单个列进行索引的索引类型。这种索引可以提供快速的等值查询和范围查询。

联合索引（Composite Index）：复合索引是一种针对多个列进行索引的索引类型。联合索引可以提供快速的多列等值查询和范围查询，但对于只查询部分列的情况，效果可能不如单列索引。
覆盖索引（Covering Index）：覆盖索引是一种特殊的联合索引，当联合索引的key值包含了所有索引条件时，联合索引成为覆盖索引。数据库可以直接从索引中获取查询结果，而无需查询数据表（不需要回表查询）。这种索引可以显著提高查询性能和效率。

唯一索引（Unique Index）：唯一索引是一种对索引列进行唯一性约束的索引类型。这种索引可以防止重复插入相同的索引值，从而保证数据表的数据完整性。

全文索引（Full-Text Index）：SQL 全文索引是指对 SQL 数据库中的文本类型数据建立索引，以便在文本数据集合中高效地进行关键词搜索和查询的操作。它可以在 SQL 数据库中存储和管理大量的文本数据，并提供基于文本内容的高效检索功能，可以极大地提高数据查询的效率和准确性。

聚焦索引（Clustered Index）/聚簇索引/聚集索引

聚焦索引（Clustered Index）是一种在数据库表中创建的一种特殊索引类型。它决定了数据在硬盘上的物理存储顺序，而不是仅在逻辑上排列，因此也被称为“聚簇索引”或“聚集索引”。
当创建聚焦索引时，数据库系统将根据所选择的聚焦索引列对数据进行排序，并将它们存储在磁盘上相邻的位置。这种物理排序可以显著提高数据的读取速度，因为查询特定的聚焦索引列时，数据库可以直接读取相关数据页的连续块，而不必在磁盘上跳来跳去。
与聚焦索引相对应的是非聚焦索引（Non-Clustered Index），它不影响数据在硬盘上的物理存储位置，而是单独创建一个索引表，用于存储指向数据的指针。虽然非聚焦索引可以加速特定查询的速度，但它们需要额外的空间来存储索引表，并且对于频繁的插入、更新和删除操作可能会导致索引表的不断重新构建，从而影响性能。
因此，在选择创建索引时，需要根据表的特点和查询的需求来决定是否需要创建聚焦索引或非聚焦索引。一般来说，对于频繁进行范围查询或排序的列，使用聚焦索引效果更好，而对于更新和插入操作较频繁的列，则建议使用非聚焦索引。

覆盖索引

覆盖索引指索引中已经具备了所有要查的项，不再需要回表查询
当索引查询运用时，当在SQL的EXPLAIN结果的Extra列中看到 “Using index” 时，通常表示查询使用了覆盖索引（Covering Index）。

CURD

删除

Truncate和Delete和Drop

Truncate:
作用：删除表中所有的行。
速度：比Delete更快，因为它不会记录每一行的操作，而是直接删除整个表。
无法恢复：一旦执行Truncate操作，所有的行都将被删除，不可恢复。不删除trigger
注意事项：使用Truncate操作会重置表的自增长ID。
Delete:
作用：删除表中指定的行。
速度：比Truncate慢，因为它需要记录每一行的操作。
可恢复：Delete操作删除的行可以通过备份或者回滚操作进行恢复。
注意事项：Delete操作只是删除表中的行，而不会删除表本身。不删除trigger
Drop:
作用：删除整个表，包括表中所有的数据和表结构。
速度：比Delete和Truncate都慢，因为它需要删除整个表结构。
无法恢复：一旦执行Drop操作，整个表将被删除，不可恢复。
注意事项：使用Drop操作将永久删除表及其所有数据和结构，必须慎重使用。
综上所述，Truncate、Delete和Drop都是删除表中数据的命令，但它们的作用范围不同，使用时需要根据实际需求进行选择。如果只需要删除表中的部分数据，应该使用Delete；如果需要删除整个表，但又想保留表结构，应该使用Truncate；如果需要删除整个表及其结构，应该使用Drop。

查询

如何优化查询速度

1. 使用索引： 索引可以提高查询效率。确保在需要查询的字段上创建索引，并避免在大型表上使用不必要的索引。

2. 缩小查询范围：只查询需要的列，不要使用SELECT *语句，只查询需要的列，以避免不必要的查询和传输数据。

3. 优化WHERE子句： WHERE子句中使用正确的比较操作符可以提高查询效率。 例如，避免使用NOT IN，使用IN代替NOT IN。

4. 避免使用子查询：子查询可能会导致查询效率下降，尽可能避免使用它们。

5. 避免在查询中使用函数：使用函数可能会使查询变慢，尽量避免在查询中使用函数。

6. 尽量减少数据传输：尽量使用LIMIT语句限制返回的行数，并使用分页来减少数据传输。

7. 避免使用“SELECT DISTINCT”：使用“GROUP BY”代替“SELECT DISTINCT”，可以更快地执行查询。

8. 避免使用临时表：尽量避免使用临时表，因为它们会使查询变慢。

9. 优化表结构：如果可能，可以考虑重构表结构，以便更好地支持查询操作。

10. 定期清理无用数据：定期清理无用数据可以减少查询时间。

SQL Modify与Alter

SQL的MODIFY和ALTER是用于修改数据库表结构的两个关键字，但在使用上有稍微不同的情况。
ALTER用于修改现有表的结构。它可以用于添加或删除列，更改列的数据类型，重命名表或修改约束，如主键或外键约束。使用ALTER修改表的语法如下所示：

ALTER TABLE table_name 
ADD column_name datatype [NULL | NOT NULL],
DROP column_name,
ALTER COLUMN column_name datatype,
RENAME TO new_table_name;

而MODIFY是ALTER语句的一种特定类型，用于修改现有表中列的数据类型。使用MODIFY的语法如下所示：

ALTER TABLE table_name
MODIFY column_name datatype;

总之，ALTER可以用于修改表的各个方面，而MODIFY是用于修改列的数据类型的特定ALTER语句。

数据库的事务

事物的锁

共享锁(shared lock)，又叫做"读锁"，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。
排他锁(exclusive lock)，又叫做"写锁"，写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。

行锁，表锁

在InnoDB引擎下，按锁的粒度分类，可以简单分为行锁和表锁。
行锁实际上是作用在索引之上的（索引上次已经说过了，这里就不赘述了）。当我们的SQL命中了索引，那锁住的就是命中条件内的索引节点（这种就是行锁），如果没有命中索引，那我们锁的就是整个索引树（表锁）。
简单来说就是：锁住的是整棵树还是某几个节点，完全取决于SQL条件是否有命中到对应的索引节点。
而行锁又可以简单分为读锁（共享锁、S锁）和写锁（排它锁、X锁）。
读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。写锁是排他的，写锁会阻塞其他的写锁和读锁。

事务的四种隔离级别

事务隔离会有性能损失，所以数据库往往提供多种隔离级别来防止一部分而不是全部的并发问题，由程序员来进行权衡。

在数据库系统中，事务的隔离级别是指一个事务在操作过程中，与其他并发执行的事务之间所具有的隔离程度。SQL标准定义了四种隔离级别，分别是：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

具体来说，这四种隔离级别的特点如下：

读未提交（Read Uncommitted）：

该隔离级别最低，事务之间的隔离程度最弱。一个事务可以读取另一个事务未提交的数据。因此，读未提交的隔离级别可能导致脏读（Dirty Read）以及脏写。
什么是脏读、什么是脏写呢？
设想一个事务已经将一些数据写入数据库，但还没有提交。另一个事务可以看到未提交的数据吗？如果是的话，这就叫做脏读。
如果两个事务尝试更新数据库中的相同数据，会发生什么情况？通常后面的写入会覆盖前面的写入。但是，如果先前的写入尚未提交，后面的写入又覆盖了这个尚未提交的值，会怎么样呢？这就叫做脏写。

读已提交（Read Committed）：

该隔离级别在事务之间提供了更强的隔离程度。一个事务只能读取另一个事务已经提交的数据。这可以避免脏读、脏写。
为了防止脏读，每次写入前，数据库都会记住旧值。 当前事务尚未提交时，其他事务的读取都会拿到旧值。当前事务提交后，其他事务才能读取到新值。
为了防止脏写，数据库一般用行锁。当事务想要修改特定的行时，必须先获得该行的锁。一次只有一个事务可持有任何给定行的锁。如果另一个事务要写入同一行，就必须等到第一个事务提交或回滚后。
但可能导致不可重复读（Non-Repeatable Read），即在同一个事务中，读取同一行数据时，由于其他事务对该行数据进行了修改，因此读取的结果不同。幻读的出现是因为一个事务在执行两次相同的查询语句之间，其他事务插入或删除了符合该查询条件的数据行，导致第二次查询返回的结果集比第一次查询返回的结果集多了或少了数据行，这种情况下，即使使用锁机制，也不能完全解决幻读问题

读偏差

读已提交是最弱的隔离级别，使用了读已提交，仍然有很多地方可能会产生并发错误。

小明有两个账户，账户A和账户B，每个存有500元，一共1000元。现在发起一笔转账，从账户A转100元到账户B。

发起转账交易后，小明马上查询两个账户的余额。不幸的是，对账户B的查询发生在转账事务提交前（显示500元），对账户A的查询发生在转账事务提交后（显示400元）—— 似乎有100元不翼而飞！

这种异常叫做读偏差。尽管听上去有些吹毛求疵，因为这看起来不会长期持续。如果用户几秒钟后再次刷新页面，就会看到正确的帐户余额。但是有些情况下，仍不能容忍这种暂时的不一致，比如：

1、需要运行一个查询，扫描数据库中的所有账户。这个查询将会持续一段时间，在这个时间段又有多个转账事务被提交，那么我们始终无法读取到正确的数据。

2、备份操作需要复制整个数据库，这可能需要花费数小时才能完成。备份进行时，数据库依然接受写入操作。最终备份数据库中会包含一些旧的值和一些新的值。如果将来从这样的备份中恢复数据，那么不一致（比如消失的钱）就会变成永久的。

很明显，造成不可容忍的读偏差的原因是——只读操作持续的时间太长了，而这段时间又提交了新的写入。快照隔离是这个问题最常见的解决方案。

快照隔离

快照隔离是一种比读已提交更强的隔离级别，它对长时间运行的只读查询（如备份和分析）非常有用。做法是，每个事务都从数据库的一致快照中读取——也就是说，事务始终可以看到事务开始时数据库中的所有数据。即使这些数据随后被其他事务更改，这个事务也只能看到该特定时间点的旧数据。

数据库是如何实现快照隔离的呢？

快照隔离的实现通常使用写锁来防止脏写（与读已提交的行锁其实是一样的）：写入型事务会阻止另一个事务修改同一个对象，但是读取不需要任何锁定。从性能的角度来看，快照隔离做到了：读不阻塞写，写不阻塞读。

可是这与读已提交有啥子区别呢？

区别在于，读已提交为每个查询保留单独的快照，而快照隔离为每个事务保留相同的快照。在快照隔离级别下，如果我们发起多个长时间的查询请求，数据库系统往往需要为每个被查询的行保留N多个不同的版本，用一个唯一的、自增的事务ID来标识。因为正在进行的多个事务需要看到数据库在不同时刻的状态。这允许了数据库在处理长时间查询时，可以同时处理写入操作，且写入操作不会影响查询结果的正确性，且快照隔离也不会产生什么太大的额外开销。

针对快照隔离，推荐一篇文章：

丢失更新

两个用户同时编辑wiki页面，每个用户通过读取整个页面、编辑内容、然后发送整个页面到服务器的方式来保存其更改，覆写数据库中当前的任何内容。

到目前为止的读已提交和快照隔离级别，可以保证只读事务在并发写入时可以看到什么，却忽略了两个事务并发写入的问题。如果两个写入事务同时执行，则其中一个的修改可能会丢失，因为第二个写入的内容并没有包括第一个事务的修改——这就是丢失更新。

这是一个普遍的问题，目前的各种解决方案如下。

1、原子写：

UPDATE counter SET value = value + 1 WHERE id = 'whatever';

2、for update显式锁定：

BEGIN TRANSACTION;
# for update就是为这行数据加了锁，提交或回滚后释放
SELECT * FROM users WHERE id = 'Eddie' FOR UPDATE;
# 拿到数据后，应用程序做校验，然后...
UPDATE users SET money = '99999999' WHERE id = 'Eddie';
COMMIT;

3、比较并设置（CAS, Compare And Set）：

UPDATE wiki_pages SET content = '新内容' WHERE id = '007' AND content = '旧内容';
# 根据数据库的实现，这可能也不安全
# 如果数据库允许WHERE子句从旧快照中读取，则此语句也无法保证防止丢失更新

可重复读（Repeatable Read）：

该隔离级别进一步提高了事务之间的隔离程度。在一个事务中，多次读取同一行数据时，都能够读取到相同的结果。这是因为该隔离级别使用了锁定机制，防止其他事务对数据进行修改。但是，仍然存在幻读（Phantom Read）的问题，当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

写入偏差与幻读

并发写入可能导致的bug还没有完。

公司决定施行007班制，要你写一个轮班管理程序，需求是公司里可以同时有多位员工值班，但至少有一位员工在值班。员工可以申请不值班（例如，进了ICU），只要至少有一个同事正在值班，申请即可通过。

现在想象一下，小明和小红是两位值班员工。两人都感到不适，所以他们都决定请假，他们恰好在同一时间点击按钮下班。

这是两个事务，应用首先检查是否有两个或以上的员工正在值班。由于数据库使用快照隔离，两个事务的读取都返回当前值班员工数量为2 ，所以小明成功更新自己的记录休班了，而小红也做了一样的事情。两个事务都成功提交了，现在没有人值班了。

这种异常它既不是脏写，也不是丢失更新，而是写入偏差。如果这两个事务一个接一个地运行，那么第二个员工就不能休班了，这种异常行为只有在事务并发进行时才有可能。

你可能会想到，for update显式锁定难道不能解决这个问题嘛？

BEGIN TRANSACTION;
SELECT COUNT(*) FROM employee WHERE on_call = TRUE FOR UPDATE;

#应用程序检查返回值是否 >= 2，如果是，则继续执行
UPDATE employee SET on_call = FALSE WHERE name = '小明';  #小红可并发执行一样的操作，换掉名字即可
COMMIT;

对于这个业务场景，完全可以，但是仍然没有彻底解决问题，想一下这个例子：

一个会议室预订系统，一个会议室在同一时间段只能被预定一次。当有人想要预订8点到10点的A5会议室，需要首先检查在这个时间段内，A5会议室是否已被其他会议占用。如果没有找到，则创建会议。

这里我们的“for update”就无效了，因为返回的结果可能为空，即没有会议冲突，那我们怎么加锁呢？select for update 锁不到任何东西。

这种情况，即一个事务的写入改变了另一个事务的查询结果的正确性，被称为幻读。快照隔离可以避免只读查询中的幻读，但是像刚刚这个例子，两个事务同时写入，幻读问题仍然存在。

物化冲突

如果幻读的问题是没有对象可以加锁，也许可以人为地在数据库中引入一个锁对象？

例如，在会议室预订系统中，我们可以创建一个关于房间和时间段的表。此表中的每一行对应于特定房间的特定时间段，并提前插入房间和时间段的所有可能组合。

现在，要创建预订的事务可以锁定这个新创建的表了。在锁定之后，可以检查重叠的预订并像以前一样插入新的预订。这个表不用来存储任何与预订相关的信息，它完全就是一排锁，用于防止并发写入时的幻读。

实践中，想要弄清楚如何物化冲突可能很难也很容易出错，这种做法也不优雅。如果没有其他办法，物化冲突应被视为最后的手段。

下面即将进入最后一个话题——两阶段锁定。它是解决幻读问题的好方法。

两阶段锁定、共享锁、互斥锁

两阶段锁定实现了最强的隔离级别，即串行化——无论怎么并发读写，最终与一个一个顺序发生的结果都是一样的。

你可能听说过两阶段提交（2PC），但两阶段锁定（2PL）与它是完全不同的东西。
两阶段锁定要求：只要没有写入，就允许多个事务同时读取同一行。但只要有写入（修改或删除），就独占访问权限。换句话说，写入不仅会阻塞其他写入，也会阻塞读——这是和快照隔离之间的关键区别。

为什么叫“两阶段”锁定呢？

第一阶段**（只加锁）：事务开始前，进程尝试对所有此事务需要的行进行加锁，按顺序一次锁一行，查询就加共享锁，修改就加互斥锁。若第一阶段全部加锁成功**，就开始第二阶段**（只解锁）**：执行更新然后释放所有锁。若在第一阶段某个进程加锁时发生冲突，则该进程释放它所有加锁的行，然后重试第一阶段。

两阶段提交用到了两种锁，共享锁（也称为读锁，允许多个事务同时读取同一行）或互斥锁（也称为写锁，只要有写入就独占访问权限），这是它们的经典使用场景。

两阶段锁定真正实现了串行化性质，它可以防止之前讨论的所有并发问题，也是性能损耗最大的选择，尤其是它可能会更频繁地导致死锁出现。事务由于死锁而被中止后只能重试，这意味着巨大的开销。

参考

串行化（Serializable）：

该隔离级别提供了最强的隔离程度。它使用了严格的锁定机制，确保所有并发事务以串行方式执行。这可以避免脏读、不可重复读和幻读等问题，但是由于事务串行执行，可能会导致性能下降。

注：幻读 小林coding

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
这两个解决方案是很大程度上解决了幻读现象，但是还是有个别的情况造成的幻读现象是无法解决的。

需要注意的是，不同的数据库管理系统对于隔离级别的实现可能略有不同。在实际应用中，需要根据具体场景选择合适的隔离级别，以平衡数据一致性和性能之间的关系。

最左侧前缀原则/最左隔离原则

最左侧前缀原则（Leftmost-First Rule，也称为“最左匹配原则”、“左前缀匹配原则”）是关系型数据库中一种事务隔离级别的实现原则。它指出，在实现“可重复读”或更高级别的事务隔离级别时，事务必须按照索引键的顺序访问表中的数据，并锁定这些数据，直到事务结束。具体来说，如果事务需要访问一个多列索引（例如，包含两个或更多列的索引），那么它必须按照索引键的顺序逐一访问这些列，直到找到第一个不满足查询条件的列为止。在此过程中，事务需要对访问过的所有数据行进行共享锁或排它锁的加锁操作，以保证事务的隔离性。

最左侧前缀原则可以帮助避免由于并发事务之间的数据访问冲突而导致的不一致问题。例如，当两个事务需要修改同一行数据时，如果它们按照相同的索引键顺序访问表中的数据，那么它们将会获取相同的锁，从而避免了死锁的发生。此外，最左侧前缀原则也可以帮助优化查询性能，因为按照索引键顺序访问数据可以减少锁的竞争，从而提高了并发访问的效率。

MVCC

MVCC是什么

MVCC（Multi-Version Concurrency Control）是一种多版本并发控制技术，常用于数据库系统中实现事务的隔离性。在MVCC中，每个事务都能看到一致性视图（consistent view）的数据，而该视图由数据库中的所有活跃事务的快照版本组成。不同版本的数据可以同时存在于数据库中，因此MVCC可以允许多个事务并发地读取和修改不同版本的数据，从而提高了数据库系统的并发性能。

在MVCC中，每个事务都可以看到一个时间戳（timestamp）的数据版本，而时间戳通过一个全局递增的序列来生成。当事务读取数据时，只能访问早于该事务时间戳的数据版本*，而对于写操作，则需要在数据库中创建一个新版本的数据，并分配一个新的时间戳。如果两个事务尝试修改同一条数据，那么只有一个事务能成功地提交，而另一个事务将被回滚。因此，MVCC可以保证数据库系统的隔离性，防止数据的不一致性和并发冲突。

MVCC是许多主流数据库系统的核心技术之一，如PostgreSQL、Oracle、MySQL等。

主从复制中MVCC作用

在主从复制中，MVCC（多版本并发控制）是一种用于实现事务隔离的技术。主从复制是一种数据复制技术，其中一个数据库服务器（主服务器）将其更改记录复制到另一个或多个数据库服务器（从服务器）上。由于主服务器和从服务器可能会处理并发事务，因此需要确保事务之间的隔离性，以避免数据冲突和数据一致性问题。

MVCC技术允许数据库在事务并发执行时保持一致性，并提供事务隔离级别。它通过在数据库中存储多个版本的数据来实现这一点。在MVCC中，每个事务都可以看到数据库的一个特定版本。当一个事务需要读取数据时，它会查找数据库中与其隔离级别相对应的版本，而不是当前版本。当事务需要更新数据时，它会创建一个新版本，而不是直接更新当前版本。

在主从复制中，MVCC可以保证从服务器能够看到与主服务器相同的版本，即使在主服务器和从服务器之间存在一定的延迟和并发访问。这可以确保从服务器始终与主服务器保持一致，并避免数据冲突和一致性问题。因此，MVCC是实现高可用性和数据复制的重要技术之一。

GAP

MySQL 的 gap 锁是一种锁定机制，用于在多个事务同时访问一个范围查询时，保证数据的一致性和隔离性。gap 锁主要用于解决幻读问题，它锁定的是一个范围而不是某一行或某个表，因此也被称为间隙锁。

具体来说，当一个事务执行一个范围查询时，MySQL 会对查询范围内的间隙（不存在的行）进行 gap 锁定，以防止其他事务在该范围内插入新的数据。例如，对于以下查询：

SELECT * FROM table WHERE id BETWEEN 10 AND 20 FOR UPDATE;

MySQL 会对 id 值为 10 到 20 之间的间隙进行 gap 锁定，以防止其他事务在这个范围内插入新的数据。

需要注意的是，gap 锁只在事务隔离级别为可重复读或更高级别时才会生效。在读提交隔离级别下，MySQL 会使用 next-key 锁来代替 gap 锁，以解决幻读问题。

另外，由于 gap 锁会锁定整个范围，因此在高并发情况下，可能会导致锁冲突和性能问题。因此，在使用 gap 锁时，需要根据具体情况和需求来进行调整和优化。

数据库连接池（Connection Pooling）

什么是连接池

数据库连接是一个很关键的有限的昂贵的资源，也容易对数据库造成安全隐患。因此，在程序初始化时，预先创建一定数量的数据库连接，并对其进行集中管理，就构成了数据库连接池，由程序动态地对池中的连接进行申请、使用和释放，既保证了较快的数据库读写速度，又提高了安全可靠性。

数据库连接池运行机制
从连接池获取/创建可用连接
使用完后，把连接归还给连接池
在系统关闭前，断开所有连接并释放占用的系统资源
如下图，各线程并不直接同数据库相连，而是从数据库连接池中申请连接，使用完毕后归还给数据库连接池。

为什么要使用数据库连接池，有什么好处？

如果不使用数据库连接池，则操作流程如下：

1. TCP三次握手建立连接
2. MySQL认证的三次握手
3. SQL执行
4. MySQL关闭
5. TCP四次挥手关闭连接
   可见，为执行一条SQL语句，多了很多我们并不关心的网络交互，如下图：
   
   使用数据库连接池，其流程如下：

只有第一次访问需要建立连接，之后的访问，复用之前创建的连接，直接执行SQL语句。

使用数据库连接池好处：

资源重用：避免了频繁的创建、释放连接引起的性能开销，在减少系统消耗的基础上，也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）
更快的系统响应速度：数据库连接池在初始化过程中，往往已创建了若干数据库连接于池中备用，此时连接的初始化工作均已完成，对于业务请求处理而言，直接利用现有可用连接，避免了从数据库连接初始化和释放过程的开销，从而缩减了系统整体响应时间
统一的连接管理，避免连接数据库连接泄漏：在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。

连接池和线程池的区别？

连接池：被动分配，用完放回。

线程池：主动干活，有任务到来，线程不断取出任务执行。

视图

数据库视图是一种虚拟表，它是根据特定的查询语句从一个或多个基本表中派生出来的结果集。视图不是实际的表，而是基于表的查询结果的逻辑表。视图可以隐藏底层表的复杂性，提供更简单、更易于理解的数据表示方式。通过视图，用户可以方便地查询多个表中的数据，而不必了解底层表的结构和关系。视图可以用来实现数据安全性和数据完整性等方面的要求。在实际应用中，视图是一种非常有用的工具，可以提高数据的可用性和可靠性。1

范式

Anomalies in DBMS

Example: A manufacturing company stores the employee details in a table Employee that has four attributes: Emp_Id for storing employee’s id, Emp_Name for storing employee’s name, Emp_Address for storing employee’s address and Emp_Dept for storing the department details in which the employee works. At some point of time the table looks like this:

Emp_Id	Emp_Name	Emp_Address	Emp_Dept
101	Rick	Delhi	D001
101	Rick	Delhi	D002
123	Maggie	Agra	D890
16	6 Glenn	Chennai	D900
166	Glenn	Chennai	D004

This table is not normalized. We will see the problems that we face when a table in database is not normalized.

Update anomaly: In the above table we have two rows for employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data.

Insert anomaly: Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if Emp_Dept field doesn’t allow null.

Delete anomaly: Let’s say in future, company closes the department D890 then deleting the rows that are having Emp_Dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.

First normal form (1NF)

A relation is said to be in 1NF (first normal form), if it doesn’t contain any multi-valued attribute. In other words you can say that a relation is in 1NF if each attribute contains only atomic(single) value only.

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold:

• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.
• An attribute that is not part of any candidate key is known as non-prime attribute.

In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency $X\to Y$ at least one of the following conditions hold:

• X is a super key of table
• Y is a prime attribute of table

Example: Let’s say a company wants to store the complete address of each employee, they create a table named Employee_Details that looks like this:

Emp_Id	Emp_Name	Emp_Zip	Emp_State	Emp_City	Emp_District
1001	John	282005	UP	Agra	Dayal Bagh
1002	Ajeet	222008	TN	Chennai	M-City
1006	Lora	282007	TN	Chennai	Urrapakkam
1101	Lilly	292008	UK	Pauri	Bhagwan
1201	Steve	222999	MP	Gwalior	Ratan

Super keys: {Emp_Id}, {Emp_Id, Emp_Name}, {Emp_Id, Emp_Name, Emp_Zip}…so on
Candidate Keys: {Emp_Id}
Non-prime attributes: all attributes except Emp_Id are non-prime as they are not part of any candidate keys.

Here, Emp_State, Emp_City & Emp_District dependent on Emp_Zip. Further Emp_zip is dependent on Emp_Id that makes non-prime attributes (Emp_State, Emp_City & Emp_District) transitively dependent on super key (Emp_Id). This violates the rule of 3NF.

To make this table complies with 3NF we have to disintegrate the table into two tables to remove the transitive dependency:

触发器

触发器是一种特殊的数据库对象，它是与表相关联的一组SQL语句。当表上的特定操作（例如INSERT、UPDATE或DELETE）发生时，触发器会自动执行相应的SQL语句，而不需要显式调用。触发器通常用于实现复杂的业务规则和约束，例如强制实施数据完整性和安全性等方面。

存储过程

存储过程是一组预先编译好的SQL语句，它们可以接收参数并执行特定的任务。存储过程可以封装复杂的业务逻辑，例如数据计算、数据更新等操作。存储过程可以被调用，就像普通的函数一样，而不需要编写相同的SQL语句。

范式

关系型数据库中的第一范式（1NF）、第二范式（2NF）和第三范式（3NF）是用来规范化数据库设计的三种标准。它们的目的是为了确保数据库表中的数据不冗余、不重复，从而避免数据更新时的异常，提高数据的完整性和一致性。

第一范式（1NF）要求每个属性都是原子性的，不可再分解。简单来说，就是每个属性都只有一个值。例如，一个学生表中的“姓名”属性不能再拆分为“姓”和“名”两个属性。如果一个表中的属性有多个值，就需要对该表进行拆分，使每个属性只有一个值。例如，一个订单表中的“商品列表”属性可以拆分为一个新的商品表，从而遵循1NF。

第二范式（2NF）要求表中的所有非主键属性必须完全依赖于主键，而不能依赖于主键的一部分。这意味着如果一个表中有多个主键，那么每个非主键属性必须与所有主键有关，而不仅仅是其中的一部分。例如，一个订单表中的“商品名称”属性应该与订单编号和商品编号相关联，而不应该只与订单编号相关联，因为一个订单中可能有多个商品，每个商品都有自己的商品编号。

第三范式（3NF）要求表中的每个非主键属性都不依赖于其他非主键属性。也就是说，每个非主键属性只与主键相关，而不与其他非主键属性相关。例如，一个员工表中的“工资”属性应该只与员工编号相关，而不应该与员工的职位、工龄等其他属性相关联。如果一个表不符合3NF，则需要对该表进行进一步拆分，使每个属性都只与主键相关。

举个例子，假设我们有一个学生选课表，包含以下字段：学生编号、课程编号、课程名称、教师名称、成绩。这个表不符合第二范式，因为“课程名称”和“教师名称”属性依赖于课程编号，而不是学生编号和课程编号的组合。为了符合第二范式，我们需要将该表拆分为两个表，一个学生选课表和一个课程表。学生选课表包含学生编号和课程编号，以及成绩信息；课程表包含课程编号、课程名称和教师名称等信息。这样，每个表都符合第二范式和第三范式，从而避免了数据冗余和更新异常的问题。

面试其他问题

1. 单张表数据量太大问题怎么解决？Source

删、换、分、拆、异、热

2. Consecutive Days User LoginSource

user_id	date	Action	day_in_row	total_actions
1123	21/06/2018	Pageview	1	1
3321	20/06/2018	Pageview	1	6
3321	20/06/2018	Click	1	2
2122	19/05/2018	Pageview	1	1
2212	21/06/2018	Pageview	2	1
2212	20/06/2018	Pageview	1	1
2212	24/06/2018	Pageview	1	4
2212	24/06/2018	click	1	2
3321	21/06/2018	pageview	2	4
3321	21/06/2018	click	2	3
3321	17/06/2018	pageview	1	1

解法1

select userid, max(days)
from
(
  select userid, groupday, count(*) as days
  from
  (
    select
      userid, logindate - row_number() over (partition by userid order by logindate) as groupday
    from mytable
  )
  group by userid, groupday
)
group by userid
--having max(days) >= 3

3. mysql表设计时间列用datetime还是timestamp?

DateTime

Datetime固定为8个字节，时间格式为:
YYYY-MM-DD HH:MM:SS
可以设置datetime的长度，datetime(n)n代表我们要存储的毫秒值的位数假如n设置3。那我们存下来的是这样的2022-6-14 22:30:00:153datetime支持每次更新记录时，自动更新当前时间：

 DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

在实际工作中，这个属性比较有用，可以帮助我们找到记录变更的最后时间，不需要手动去设置。

Timestamp

timestamp是mysql的另一种时间戳形式，存储的内容是从1970-01-0100:00:00到现在的毫秒数。timestamp默认占用4个字节，所以按照毫秒数来计算，最大只能到2038-01-19 03:14:07。所以可能会出现类似千年虫的问题。如果说timestamp想要支持毫秒，就会占用7个字节。那么timestamp既然有最大上限，他的优点是什么呢? 就是跨国时区的自适应，以及夏令时的自适应。因为timestamp表示的毫秒数，我们只要设置不同的时区，就可以自动转化出适应时区的时间。

总结

有人认为timestamp的性能更好，因为是四字节，但是一旦存储毫秒就变成7字节，与datetime的8字节相差无几。有人认为timestamp可以自动解决时区问题，但是我的建议是通过前端或后端来做转换。还有就是timestamp的性能问题，如果我们的mysql没有显示的指定时区timestamp就会调用系统的时区。就会与操作系统底层函数__tz_convert()做交互。这个函数每次交互的时候，他会加锁，那么高并发情况下，会有性能抖动问题。而且性能不如datetime。

4. 如何保证缓存一致性

缓存的意义

常见解决方案

1. 更新数据库后，更新缓存.
   如果数据库更新成功，缓存失败，那么我们在缓存失效之前，读取到的一直都是老数据，而数据库里面是新数据
2. 更新缓存后，更新数据库
   缓存更新成功，而数据库失败，导致我们读取到的值一直都是错误的缓存值。
   这两种方案都不可取，无法原子性。不要选择更新! ! !
3. 先删除缓存，再更新数据库
   实际中有一定的使用量，即使更新数据库失败也没有问题，数据都是老的。不过在高并发下会有问题，比如A线程删除缓存后，正在更新数据库，这个时候B线程查询了缓存，发现没有数据，于是就从数据库读取后，放入进来。导致缓存中存在的一直是老数据。针对上面这种问题，我们可以选择双删。先删除缓存，再更新数据库再删除缓存。
4. 先更新数据库、再删除缓存
   这种的话，是比较流行的，支持高并发。如果更新数据库成功，删除缓存失败，那么缓存失效前读取到的也是老数据。这种概率极小。可能存在的问题: A线程查询数据库，得一个旧值，B先将新值写入数据库，B线程删除缓存，A线程将查到的旧值写入缓存。这种情况下会存在脏数据。

如何应对删除失败

1. 消息队列补偿
   删除失败的消息打入队列，监听队列，再次重试执行、
2. 用canal监听binlog
   当mysql的数据发生变化后，canal会受到一条消息，这个时候进行删除。好处是可以代码解耦。减少业务复杂度。并且中间件保证了我们的高可用性

Mysql分表、分库、分片和分区的区别?

分片(Data Sharding)

水平分区如果我们将表拆分为多个表，其中每个表都具有与原始表相同的架构。随后，我们可以将不同的行移动到每个表中。这称为水平分区。例如，我们可以将 ID < 4 的所有用户移动到表 1 中，而所有其他用户都可以移动到表 2 中。需要注意的关键是表 1 和表 2 应该具有相同的架构，如下所示：

垂直分区
如果我们将一个表拆分为多个表，其中所有列的并集创建原始表，那么它被称为垂直分区。

例如，让我们考虑 99% 的请求是关于检索用户的姓名和重要性列。

我们可以通过创建两个表并将数据跨两个表移动来垂直拆分用户表，如下所示：

分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的，分区实现比较简单，数据库mysql、oracle等很容易就可支持。

分库分表后引入的问题

1、分布式事务问题如果我们做了垂直分库或者水平分库以后，就必然会涉及到跨库执行SQL的问题，这样就引发了互联网界的老大难问题-“分布式事务”。那要如何解决这个问题呢？

1. 使用分布式事务中间件
2. 使用MySQL自带的针对跨库的事务一致性方案(XA)，不过性能要比单库的慢10倍左右。3.能否避免掉跨库操作(比如将用户和商品放在同一个库中)

2、跨库join的问题分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。
3、横向扩容的问题当我们使用HASH取模做分表的时候，针对数据量的递增，可能需要动态的增加表，此时就需要考虑因为reHash导致数据迁移的问题。
4、结果集合并、排序的问题因为我们是将数据分散存储到不同的库、表里的，当我们查询指定数据列表时，数据来源于不同的子库或者子表，就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作，性能肯定会受非常大的影响。走缓存可能一条路!

如何解决

redis的配置文件中，存在两个参数

min-slaves-to-write 3
min-slaves-max-lag 10

第一个参数表示连接到master的最少slave数量，第二个参数表示slave连接到master的最大延迟时间。
按照上面的配置，要求至少3个slave节点，且数据复制和同步的延迟不能超过10秒，否则的话master就会拒绝写请求，配置了这两个参数之后，如果发生集群脑裂，原先的master节点接收到客户端的写入请求会拒绝，这样所有的写操作，就会在新的master上面，数据就可以保持一致，减少数据同步之后的数据丢失。

5. 一条完整的SQL语句的执行过程是怎样的？

前言

天天和数据库打交道，一天能写上几十条 SQL 语句，但你知道我们的系统是如何和数据库交互的吗？MySQL 如何帮我们存储数据、又是如何帮我们管理事务？…是不是感觉真的除了写几个 「select * from dual」外基本脑子一片空白？ 这篇文章就将带你走进 MySQL 的世界，让你彻底了解系统到底是如何和 MySQL 交互的，MySQL 在接受到我们发送的 SQL 语句时又分别做了哪些事情。

MySQL 驱动

我们的系统在和 MySQL 数据库进行通信的时候，总不可能是平白无故的就能接收和发送请求，就算是你没有做什么操作，那总该是有其他的“人”帮我们做了一些事情，基本上使用过 MySQL 数据库的程序员多多少少都会知道 MySQL 驱动这个概念的。就是这个 MySQL 驱动在底层帮我们做了对数据库的连接，只有建立了连接了，才能够有后面的交互。看下图表示:

这样的话，在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接，那么问题来了，我们系统肯定不是一个人在使用的，换句话说肯定是存在多个请求同时去争抢连接的情况。我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭，这样会有什么问题呢？如下图:

java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的。必然会大大降低我们系统的性能，但是如果给你提供一些固定的用来连接的线程，这样是不是不需要反复的创建和销毁连接了呢？相信懂行的朋友会会心一笑，没错，说的就是数据库连接池。

数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。

常见的数据库连接池有 Druid、C3P0、DBCP，连接池实现原理在这里就不深入讨论了，采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的「池化」思想，不管是线程池还是 HTTP 连接池，都能看到它的身影。

数据库连接池

到这里，我们已经知道的是我们的系统在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。不过这里有个小问题，业务系统是并发的，而 MySQL 接受请求的线程呢，只有一个？

其实 MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。

至此系统和 MySQL 数据库之间的连接问题已经说明清楚了。那么 MySQL 数据库中的这些连接是怎么来处理的，又是谁来处理呢？

网络连接必须由线程来处理

对计算基础稍微有一点了解的的同学都是知道的，网络中的连接都是由线程来处理的，所谓网络连接说白了就是一次请求，每次请求都会有相应的线程去处理的。也就是说对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。

SQL 接口
MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。

查询解析器

假如现在有这样的一个 SQL

SELECT stuName,age,sex FROM students WHERE id=1

但是这个 SQL 是写给我们人看的，机器哪里知道你在说什么？这个时候解析器就上场了。他会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言，至于怎么解析的就不需要再深究了，无非是自己一套相关的规则。

现在 SQL 已经被解析成 MySQL 认识的样子的，那下一步是不是就是执行吗？理论上是这样子的，但是 MySQL 的强大远不止于此，他还会帮我们选择最优的查询路径。

什么叫最优查询路径？就是 MySQL 会按照自己认为的效率最高的方式去执行查询
具体是怎么做到的呢？这就要说到 MySQL 的查询优化器了

MySQL 查询优化器

查询优化器内部具体怎么实现的我们不需要是关心，我需要知道的是 MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本

IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关

CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行

优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句

存储引擎

查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的（存储引擎是一个非常重要的组件，后面会详细介绍）

执行器

执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行

初识存储引擎

我们以一个更新的SQL语句来说明，SQL 如下

UPDATE students SET stuName = '小强' WHERE id = 1

当我们系统发出这样的查询去交给 MySQL 的时候，MySQL 会按照我们上面介绍的一系列的流程最终通过执行器调用存储引擎去执行，流程图就是上面那个。在执行这个 SQL 的时候 SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机IO读写的速度肯定让人无法接受的，所以每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池 Buffer Pool

Buffer Pool

Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后再放到 Buffer Pool 中，如下图

按照上面的那幅图，这条 SQL 语句的执行步骤大致是这样子的

1. innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在
2. 发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中
3. 该条记录会被加上一个独占锁（总不能你在修改的时候别人也在修改吧，这个机制本篇文章不重点介绍，以后会专门写文章来详细讲解）

undo 日志文件：记录数据被修改前的样子

undo 顾名思义，就是没有做，没发生的意思。undo log 就是没有发生事情（原本事情是什么）的一些日志

我们刚刚已经说了，在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

这样做的目的是什么？

Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响，看图说话

这里说句额外话，其实 MySQL 也是一个系统，就好比我们平时开发的 java 的功能系统一样，MySQL 使用的是自己相应的语言开发出来的一套系统而已，它根据自己需要的功能去设计对应的功能，它即然能做到哪些事情，那么必然是设计者们当初这么定义或者是根据实际的场景变更演化而来的。所以大家放平心态，把 MySQL 当作一个系统去了解熟悉他。

到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的，那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是小强 数据库中的记录是旺财 ，这种情况 MySQL是怎么处理的呢，继续往下看

redo 日志文件：记录数据被修改后的样子

除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了

画外音：redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的

redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来，例如，此时将要做的是update students set stuName=‘小强’ where id=1; 那么这条操作就会被记录到 redo log buffer 中，啥？怎么又出来一个 redo log buffer ,很简单，MySQL 为了提高效率，所以将这些操作都先放在内存中去完成，然后会在
某个时机将其持久化到磁盘中。
截至目前，我们应该都熟悉了 MySQL 的执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4. innodb 会在 Buffer Pool 中执行更新操作
5. 更新后的数据会记录在 redo log buffer 中

上面说的步骤都是在正常情况下的操作，但是程序的设计和优化并不仅是为了这些正常情况而去做的，也是为了那些临界区和极端情况下出现的问题去优化设计的

这个时候如果服务器宕机了，那么缓存中的数据还是丢失了。真烦，竟然数据总是丢失，那能不能不要放在内存中，直接保存到磁盘呢？很显然不行，因为在上面也已经介绍了，在内存中的操作目的是为了提高效率。

此时，如果 MySQL 真的宕机了，那么没关系的，因为 MySQL 会认为本次事务是失败的，所以数据依旧是更新前的样子，并不会有任何的影响。

好了，语句也更新好了那么需要将更新的值提交啊，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作

将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘**（具体策略情况在下面小总结出会详细介绍）,上图

如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？此时数据是在内存中，数据岂不是丢失了？不，这次数据就不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中（这边我的理解是和 Redis 的持久化机制是差不多的，在 Redis 启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中**）

到此为止，从执行器开始调用存储引擎接口做了哪些事情呢？

1. 准备更新一条 SQL 语句

2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中

3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中

4. innodb 会在 Buffer Pool 中执行更新操作

5. 更新后的数据会记录在 redo log buffer 中
   —到此是前面已经总结过的—

6. MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中 刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置

值为 0 表示不刷入磁盘

值为 1 表示立即刷入磁盘

值为 2 表示先刷到 os cache

7. myslq 重启的时候会将 redo 日志恢复到缓冲池中

截止到目前为止，MySQL 的执行器调用存储引擎的接口去执行【执行计划】提供的 SQL 的时候 InnoDB 做了哪些事情也就基本差不多了，但是这还没完。下面还需要介绍下 MySQL 级别的日志文件 bin log

bin log 日志文件：记录整个操作过程

上面介绍到的redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:

6 Explain

explain 中你通常关注哪些字段，为什么

在分析查询性能时，通常关注的字段包括 type、key、rows 和 Extra。其中，type 表示查询时所使用的访问方式，是影响性能的重要因素之一；key 表示实际使用的索引，也是查询性能的重要因素；rows 表示扫描的行数，是评估查询性能的重要指标；Extra 表示一些额外的信息，可以帮助我们更好地理解查询的执行过程。

explain 中的 type 字段有哪些常见的值

ALL：全表扫描，表示查询时需要扫描整张表。

INDEX：索引扫描，表示查询时使用了索引扫描。

RANGE：范围扫描，表示查询时使用了范围扫描，例如 BETWEEN、IN 等。

REF：索引引用，表示查询时使用了索引引用，通常是在多表连接时使用。

EQ_REF：唯一索引引用，表示查询时使用了唯一索引引用。

const/system：常量/系统查询，表示查询时使用了常量或系统函数。

大数据部分

数据倾斜

SQL数据倾斜（Data Skew）是指在数据处理过程中，部分数据量远大于其他数据的情况。这种情况下，数据的分布不均匀，会导致某些计算节点负载过高，从而影响整个数据处理的效率和性能。

数据倾斜在数据处理领域中非常常见，尤其是在大规模分布式数据处理中更为明显。例如，在使用 MapReduce 进行数据处理时，如果输入数据中某些键的出现频率过高，那么在 Map 阶段中，处理这些键的任务将会集中在少数的节点上，而其他节点则闲置，这就会导致处理时间过长，甚至任务失败。

数据倾斜的原因可能有很多，例如数据分布不均匀、查询条件不合适、数据分区策略不当等。为了解决数据倾斜问题，通常需要采取一些数据预处理、调整分区策略、优化查询语句等技术手段。例如，在 MapReduce 中可以采用 Combiner、Partitioner、Secondary Sort 等技术来优化数据处理过程，从而提高数据处理的效率和性能。