MySQL（2024.4.17）

1. 什么是MySQL的MVCC机制？

2. 如何理解InnoDB的Next-Key Lock机制？

3. 快照读和当前读的区别？

4. 如何在SQL语句中触发当前读？

5. MySQL默认的隔离级别是什么？

6. 如何避免在使用当前读时可能出现的死锁问题？

7.1 事务的四种隔离级别是什么？

7.2 不可重复读和幻读之间的区别是什么？

7.3 为什么InnoDB在可重复读隔离级别下仍然可能产生幻读？

7.4 解决幻读的方法★★★

8. MySQL的日志★★★★★

8.1 为什么需要使用redo log？它是何时写的？

8.2 redo log的刷盘策略

8.3 redo log的两阶段提交

8.4 binlog日志的记录格式、写入机制

9. EXPLAIN 分析执行计划★★★★★

10.1 MySQL索引失效的情形有哪些？★★★

10.2 如何避免索引失效的情况？★★★

10.3 针对“使用覆盖索引来实现ORDER BY和GROUP BY，或者将排序操作放到应用程序层面”具体应该怎么做呢？

11. MySQL高性能优化规范建议总结

一、数据库命名规范：

二、数据库基本设计规范

三、字段设计规范

四、索引设计规范

五、SQL 开发规范

六、必须阅读

12. MySQL索引详解★★★★★

一、最左前缀匹配原则：

二、索引下推

三、MySQL的索引结构？★★★★★

四、索引分类

13. MySQL查询缓存详解

1. 什么是MySQL的MVCC机制？

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

1、读操作（SELECT）：

当一个事务执行读操作时，它会使用快照读取。快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取其他事务尚未提交的修改。具体工作情况如下：

对于读取操作，事务会查找符合条件的数据行，并选择符合其事务开始时间的数据版本进行读取。

如果某个数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据。

事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作。

2、写操作（INSERT、UPDATE、DELETE）：

当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。具体工作情况如下：

对于写操作，事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本。

新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。

原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。

3、事务提交和回滚：

当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。

当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

4、版本的回收：

为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。

MVCC 通过创建数据的多个版本和使用快照读取来实现并发控制。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。这样，不同的事务可以在一定程度上并发执行，而不会相互干扰，从而提高了数据库的并发性能和数据一致性。

MySQL的多版本并发控制（Multiversion Concurrency Control，简称MVCC）是一种用于实现数据库并发操作的技术。它允许不同的事务在同一时间看到数据库的不同快照，从而提高系统的并行性能，同时减少锁定带来的冲突。

在InnoDB存储引擎中，MySQL使用了MVCC来支持事务的隔离级别，尤其是读已提交（Read Committed）和可重复读（Repeatable Read）级别。以下是MVCC的基本原理：

读取时创建快照：当一个事务开始时，它会得到一个系统版本号，这个版本号用于确定事务能看到哪些数据版本。

记录版本信息：每次更新记录时，并不立即覆盖原有记录，而是创建一个新的记录版本，并标记上当前系统版本号。

隐藏不可见的记录：对于一个事务，只有那些在其开始前就已经存在的记录版本才是可见的。如果记录是在当前事务开始后被修改的，那么这些记录对当前事务是不可见的。

快照读与Next-Key Lock：SELECT查询时，MVCC会基于事务的开始版本号选择可见的数据。此外，InnoDB还使用Next-Key Lock防止幻读，这在可重复读隔离级别下特别重要。

清理过期版本：事务提交时，旧版本的记录并不会立即删除，以供其他事务查询。垃圾收集器会在适当的时候自动清理不再需要的旧版本记录，即那些所有可能访问该版本的事务都已经结束的记录。

MySQL的MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于提高数据库并发性能的技术，尤其在InnoDB存储引擎中广泛使用。下面是MVCC的基本概念和工作原理：

事务可见性：每个事务看到的数据库状态是基于其开始时的一致性视图，而不是实时的数据库状态。这意味着不同事务看到的数据可能是不同的版本。

读已提交（Read Committed）：在这种隔离级别下，每次读取都会看到最新的已提交事务的结果，相当于每次读都开启一个新的快照。

可重复读（Repeatable Read）：在同一事务内多次读取相同数据，结果始终一致，即看到的是事务开始时刻的数据视图，不受后续其他事务的影响。

回滚段（Rollback Segment）：保存了事务撤销时需要的信息，如旧的行版本。

行版本：每个被更新的行都有多个版本，每个版本与生成它的事务ID相关联。

删除标记：在InnoDB中，删除不是立即清除行，而是对其设置一个删除标志，这样其他事务仍然可以看到该行，但知道它已被删除。

快照读（Snapshot Read）与当前读（Current Read）：快照读忽略删除标记，并只查看未提交的更改；而当前读则能看到所有版本，包括被删除的行。

2. 如何理解InnoDB的Next-Key Lock机制？

InnoDB的Next-Key Lock机制是为了防止幻读（Phantom）现象，它是行级锁的一种扩展形式，结合了行锁和间隙锁（Gap Lock）。以下是Next-Key Lock的工作原理：

行锁：锁定特定的行，确保同一事务中的两个SELECT不会同时修改同一条记录。

间隙锁：锁定索引之间的间隙，不包括索引本身的数据项，目的是阻止其他事务在此范围内插入新的记录，这样就不会有新行出现在当前事务的视图中。

Next-Key Lock组合：在索引中，Next-Key Lock不仅锁定当前行，还会锁定到下一个唯一索引值之前的所有间隙。例如，如果锁定索引值为3的行，Next-Key Lock会锁定3及其之前的区间（包括2，但不包括4，因为4是紧接3后的唯一索引值）。

行选择性：Next-Key Lock的选择性取决于查询条件。如果查询条件能够精确匹配一行，它将表现为行锁；如果条件匹配索引范围，将表现为间隔锁。

避免幻读：在可重复读（Repeatable Read）隔离级别下，通过Next-Key Lock可以确保事务在整个事务期间看到的是相同的数据集，即使其他事务在此期间进行了插入操作。

3. 快照读和当前读的区别？

快照读（Snapshot Read）和当前读（Current Read）是数据库事务处理中的两种不同读取方式，主要区别在于锁行为和数据可见性：

快照读（Snapshot Read）：

在隔离级别为Read Uncommitted或Read Committed时，快照读通常会发生。
快照读不会对读取的数据加锁，因此它不会阻塞其他事务对该数据的读写操作。
数据的可见性基于事务开始时的数据版本，即每个事务看到的是数据库的一份快照，不考虑其他事务的未提交更改。
这种方式在高并发环境下能提高性能，因为它避免了锁竞争。

当前读（Current Read）：

包括锁定读（例如SELECT ... FOR UPDATE/LOCK IN SHARE MODE）、SERIALIZABLE隔离级别的读等。
当前读会对读取的数据加锁，确保其他事务在当前事务完成之前无法修改这些数据。
数据的可见性包括所有已提交的更改，即使在事务开始之后发生的更改也是可见的。
这提供了更高的数据一致性，但可能导致死锁并降低并发性能。

4. 如何在SQL语句中触发当前读？

在SQL中，触发当前读（也称为一致性读或者锁定读）通常涉及到锁操作或者使用特定的查询选项。以下是几种常见的方式：

（1）SELECT ... FOR UPDATE：这个语句会锁定所选的行，直到当前事务结束才释放。这确保了其他事务在此期间不能修改这些行。
START TRANSACTION;
SELECT * FROM table WHERE condition FOR UPDATE;
-- 执行其他操作...
COMMIT; -- 或者 ROLLBACK;
（2）SELECT ... LOCK IN SHARE MODE：这个语句也会锁定行，但允许其他事务进行读取，防止写入（共享锁）。
START TRANSACTION;
SELECT * FROM table WHERE condition LOCK IN SHARE MODE;
-- 执行其他操作...
COMMIT; -- 或者 ROLLBACK;
（3）高隔离级别的查询：在READ COMMITTED隔离级别上，你可以通过设置更高的事务隔离级别，如SERIALIZABLE，来强制当前读。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM table WHERE condition;
COMMIT;
（4）在子查询中使用NOT EXISTS或EXISTS：某些情况下，子查询中的NOT EXISTS或EXISTS可以导致当前读，因为它们检查行的存在性。
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.key = t1.key);

5. MySQL默认的隔离级别是什么？

可重复读。（Oracle默认的隔离级别是：读已提交）

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失。
InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。

6. 如何避免在使用当前读时可能出现的死锁问题？

避免在使用当前读（如SELECT ... FOR UPDATE）时出现死锁的方法主要包括以下几点：

事务顺序执行：确保不同的事务以相同顺序访问资源（行或表）。这减少了两个事务因尝试互锁对方正在使用的资源而发生死锁的可能性。
设置超时：MySQL允许配置事务超时时间（innodb_lock_wait_timeout），当事务等待锁超过这个时间限制，会自动回滚，从而打破死锁。
死锁检测与恢复：InnoDB存储引擎内置了死锁检测机制，当检测到死锁时，会选择一个事务进行回滚以解决死锁。
一次性获取所有需要的锁：事务尽可能在一开始时就获取所有需要的锁，而不是逐步获取。这样可以减少长时间持有部分锁而引起其他事务等待的情况。
应用程序逻辑：在应用程序设计时，考虑死锁可能发生的场景，并采用适当的算法来避免，比如按固定的顺序更新资源。
合理的数据模型设计：避免复杂的关联关系和依赖，尽量简化业务流程，减少可能导致死锁的操作路径。

7.1 事务的四种隔离级别是什么？

在关系型数据库中，事务的四种隔离级别是：

读未提交（Read Uncommitted）：允许事务读取其他未提交的更改，可能会出现脏读、不可重复读和幻读问题。

读已提交（Read Committed）：只允许事务读取其他已经提交的更改，可以避免脏读，但可能遇到不可重复读和幻读。

可重复读（Repeatable Read）：事务在整个过程中能多次读取同一数据块并保持一致性，不会发生脏读和不可重复读，但可能存在幻读。InnoDB存储引擎通过MVCC和next-key lock来实现可重复读。

串行化（Serializable）：最高的隔离级别，通过加锁的方式强制事务顺序执行，避免了所有并发问题，但可能导致较高的锁冲突和性能下降。

7.2 不可重复读和幻读之间的区别是什么？

不可重复读（Non-Repeatable Read）和幻读（Phantom Read）是事务隔离级别中的两种并发问题，主要区别在于它们涉及的数据范围不同：

不可重复读：

发生在一个事务内多次读取相同数据集时，第二次读取的结果包含了第一次读取时不存在的新记录或被修改的旧记录。
这通常是由于其他事务在两次读之间对数据进行了更新或删除导致的。
在可重复读隔离级别下，InnoDB通过MVCC避免了不可重复读，但在某些情况下仍可能发生幻读。

幻读：

当一个事务在进行两次查询时，第二次查询返回了一些在第一次查询时不复存在的新行。
幻读通常发生在范围查询或者添加新的满足条件的行时，即使查询的条件没有变化，也会因为其他事务插入的新行而产生不同的结果。
在可重复读隔离级别下，InnoDB使用Next-Key Lock防止幻读，但在某些特定条件下，例如索引间隙锁不适用时，还是可能发生幻读。

7.3 为什么InnoDB在可重复读隔离级别下仍然可能产生幻读？

InnoDB在可重复读隔离级别下使用多版本并发控制（MVCC）来确保事务内部的查询不会看到其他事务的未提交更改。然而，在某些情况下，这不足以防止幻读。幻读通常发生在以下场景：

间隙锁（Gap Locks）：InnoDB默认使用Next-Key Locks，这是一种组合了行锁和间隙锁的机制。它不仅锁定所选的行，还锁定行之间的间隙。这防止了其他事务在选定范围内插入新行，从而防止幻读。但是，如果查询使用了索引的最值，如SELECT * FROM table WHERE id > some_value，间隙锁只会在该范围的开始处设置，允许在末尾插入新行，导致幻读。

索引选择性：当查询的WHERE子句使用的是非唯一索引或者部分索引键时，InnoDB可能会释放一些间隙锁，这也可能导致幻读。

因此，尽管InnoDB在可重复读级别下试图防止幻读，但在上述特定情况下，还是有可能出现幻读现象。

7.4 解决幻读的方法★★★

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

将事务隔离级别调整为 SERIALIZABLE 。

在可重复读的事务级别下，给事务操作的这张表添加表锁。

在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）。

在标准的 SQL 隔离级别定义里，REPEATABLE-READ(可重复读)是不可以防止幻读的。

但是！InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

快照读：由 MVCC 机制来保证不出现幻读。

当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

8. MySQL的日志★★★★★

MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）、事务日志 redo log（重做日志）、undo log（回滚日志）。

MySQL中的binlog（二进制日志），redo log（重做日志）和undo log（回滚日志）是用于实现数据持久性和事务恢复的关键组件：

Binlog (二进制日志):

Binlog记录了所有改变数据库状态的查询，用于数据备份和复制到从服务器。
它以事件的形式存储，可以有多种格式，如ROW, STATEMENT和MIXED。
Binlog是串行化的，意味着即使在并行交易中，日志也是按顺序写入的。

Redo Log (重做日志):

Redo log是InnoDB存储引擎用来保证事务原子性的工具。
当事务更新数据时，先将改动写入redo log缓冲区，并标记为待提交。
在事务提交时，这些更改会被写入实际的数据文件（称为双写缓冲区）。
如果系统崩溃，InnoDB可以通过redo log来恢复未完成的事务，确保已提交的变更被持久化。

Undo Log (回滚日志):

Undo log用于实现事务的回滚操作。
当事务开始时，InnoDB会为可能需要回滚的更改保存旧的值（undo信息）。
如果事务失败或执行ROLLBACK，InnoDB可以通过undo log恢复数据到事务开始前的状态。
同样，undo log也被用于MVCC（多版本并发控制），使读取操作可以查看一致的旧数据视图。

8.1 为什么需要使用redo log？它是何时写的？

为什么要使用redo log:

事务原子性：确保即使在系统故障的情况下，已经提交的事务对数据库的修改也能永久保存。
数据一致性：在系统崩溃后，通过redo log进行检查点或恢复操作，可以维护数据的一致性状态。
性能提升：redo log允许快速写入内存，而延迟磁盘I/O，提高系统的响应速度（即write-ahead logging原则）。

何时写redo log:

事务开始时，InnoDB会创建一个新的redo log记录。
执行每个影响数据的SQL语句时，相关的更改首先被写入redo log buffer。
事务提交时，redo log buffer的内容被刷入redo log文件，并标记这些记录为已提交（checkpoint过程）。
系统定期或者达到特定条件（如缓存满）时，也会将redo log buffer的内容刷新到磁盘。

8.2 redo log的刷盘策略

我们要注意设置正确的刷盘策略innodb_flush_log_at_trx_commit 。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。
刷盘策略innodb_flush_log_at_trx_commit 的默认值为 1，设置为 1 的时候才不会丢失任何数据。为了保证事务的持久性，我们必须将其设置为 1。
另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。

8.3 redo log的两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。
binlog（归档日志）保证了 MySQL 集群架构的数据一致性。
虽然它们都属于持久化的保证，但是侧重点不同。
在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。
为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。
原理很简单，将 redo log 的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

8.4 binlog日志的记录格式、写入机制

记录格式：

binlog 日志有三种格式（statement、row、mixed），可以通过binlog_format参数指定。

写入机制：

binlog 的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到 binlog 文件中。
因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
binlog 日志刷盘流程如下

上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快；上图的 fsync，才是将数据持久化到磁盘的操作。

write和fsync的时机，可以由参数sync_binlog控制，默认是1。

为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。

虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。

为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。

最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

9. `EXPLAIN` 分析执行计划★★★★★

图灵第4期MySQL调优专题学习笔记_图灵学院索引优化资料下载-CSDN博客

type列所有值的顺序从最优到最差排序为：；
key列表示 MySQL 实际使用到的索引；
Extra列：包含 Using filesort 或 Using temporary 时，需要优化。

10.1 MySQL索引失效的情形有哪些？★★★

MySQL索引可能在以下情况下失效或效率降低：

全表扫描更优时：当查询涉及表中的大部分或全部记录时，MySQL可能会选择不使用索引而直接全表扫描。

条件覆盖不到索引列：如果WHERE子句中的条件没有用到索引的第一列或多列，则索引可能不会被使用。（最左特性原则）

索引列使用函数：如果索引列在查询中使用了函数，比如CONVERT()或DATE_FORMAT()，那么MySQL无法利用索引。

不等式操作符：对于索引列使用<, >, <=, >=, !=, BETWEEN, 或 NOT BETWEEN时，MySQL可能不会完全利用索引，特别是当范围较大时。

NULL值处理：大多数索引不包括NULL值，所以如果索引列包含NULL且查询中涉及到NULL，可能影响索引效果。

使用OR连接条件：如果WHERE子句中OR连接的条件只有一部分是索引字段，那么MySQL可能无法有效地使用索引，除非每个列都有独立的索引。

索引列排序/分组：当对索引列进行ORDER BY或GROUP BY操作，且没有使用相应的覆盖索引时，索引可能不起作用。

数据倾斜：如果数据分布非常不平衡，某些索引键值过于集中，可能导致索引效率下降。

索引过长：如果索引太长，MySQL可能不会使用它来优化查询。

索引统计信息过时：MySQL依赖于表的统计信息来决定是否使用索引，如果这些信息过时，可能导致决策错误。

强制类型转换：如果比较的两个列数据类型不一致，MySQL在比较之前可能需要转换列的类型，这可能导致无法使用索引。

使用通配符搜索：像LIKE 'abc%'这样的模糊查询可以从索引中受益，但如果模式以通配符开头，如LIKE '%abc'，则无法使用索引。

10.2 如何避免索引失效的情况？★★★

要避免索引失效或效率降低，可以遵循以下策略：

选择合适的索引类型：针对不同的查询模式（如全值匹配、前缀匹配或范围查询），选择最适合的索引类型，如主键索引、唯一索引或全文索引。

避免全表扫描：尽量优化查询以减少全表扫描，例如通过限制返回行数或使用LIMIT。

使用索引覆盖：确保查询中的所有字段都在索引中，这样可以通过索引直接获取数据，无需回表。

避免函数或表达式运算：确保索引列在查询中直接使用，避免函数、计算表达式或用户定义的函数。

正确使用等于和范围查询：对于多列索引，应先用等于条件的列，再用范围条件的列。

合理设计索引：考虑常用查询模式，为最频繁的查询创建索引，避免过度索引。

维护统计信息：定期更新表的统计信息，使MySQL能更准确地计划查询。

优化排序和分组：使用覆盖索引来实现ORDER BY和GROUP BY，或者将排序操作放到应用程序层面。

避免使用OR：尽可能拆分OR条件为多个独立的查询，然后使用UNION ALL或JOIN合并结果。

考虑NULL的影响：对于经常出现在查询条件中的字段，考虑避免使用NULL值。

10.3 针对“使用覆盖索引来实现ORDER BY和GROUP BY，或者将排序操作放到应用程序层面”具体应该怎么做呢？

使用覆盖索引来优化ORDER BY和GROUP BY查询，主要目的是避免进行额外的数据检索。以下是具体做法：

1）覆盖索引：创建一个包含查询所需所有字段的复合索引。例如，如果你有这样一个查询：
SELECT col1, col2 FROM table ORDER BY col1, col2;
你可以创建一个索引 (col1, col2)，这样MySQL可以直接从索引中获取排序后的数据，而不需要回表。

2）GROUP BY：对于GROUP BY查询，确保索引包含分组字段以及用于聚合的任何其他字段。比如：
SELECT col1, COUNT(*) FROM table GROUP BY col1;
在这种情况下，创建一个(col1)索引可以提高性能，因为MySQL只需要对索引进行遍历就能完成分组。

3）将排序操作移到应用程序：当数据库无法有效利用索引时，可以考虑在应用程序中进行排序。这通常发生在处理大量数据并希望节省数据库资源时。首先，请求未排序的数据（通常是通过限制返回的行数来实现）：
SELECT * FROM table LIMIT 1000;
然后，在应用程序中对这些数据进行排序。这种方法适用于结果集较小的情况，因为大结果集可能会消耗大量内存。

11. MySQL高性能优化规范建议总结

一、数据库命名规范：

临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀

所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

二、数据库基本设计规范

所有表必须使用 InnoDB 存储引擎

没有特殊要求（即 InnoDB 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 InnoDB 存储引擎（MySQL5.5 之前默认使用 Myisam，5.6 以后默认的为InnoDB）。
InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

数据库和表的字符集统一使用 UTF8：兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

所有表和字段都需要添加注释：使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护。

尽量控制单表数据量的大小，建议控制在 500 万以内：500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。

谨慎使用 MySQL 分区表：分区表在物理上表现为多个文件，在逻辑上表现为一个表；谨慎选择分区键，跨分区查询效率可能更低；建议采用物理分表的方式管理大数据。

经常一起使用的列放到一个表中：避免更多的关联操作。

禁止在表中建立预留字段：

- 预留字段的命名很难做到见名识义。
- 预留字段无法确认存储的数据类型，所以无法选择合适的类型。
- 对预留字段类型的修改，会对表进行锁定。

禁止在数据库中存储文件（比如图片）这类大的二进制数据

在数据库中存储文件会严重影响数据库性能，消耗过多存储空间。

文件（比如图片）这类大的二进制数据通常存储于文件服务器，数据库只存储文件地址信息。

不要被数据库范式所束缚：一般来说，设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。

禁止在线上做数据库压力测试

禁止从开发环境,测试环境直接连接生产环境数据库：安全隐患极大，要对生产环境抱有敬畏之心！

三、字段设计规范

先选择符合存储需要的最小的数据类型

a.某些字符串可以转换成数字类型存储比如可以将 IP 地址转换成整型数据（MySQL提供了两个方法来处理ip地址）。数字是连续的，性能更好，占用空间也更小。
b.对于非负型的数据 (如自增 ID,整型 IP，年龄) 来说,要优先使用无符号整型来存储。无符号相对于有符号可以多出一倍的存储空间。
c.小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。

避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。
b、TEXT 或 BLOB 类型只能使用前缀索引

避免使用 ENUM 类型

尽可能把所有列定义为 NOT NULL：技术分享 | MySQL默认值选型（是空，还是 NULL）

一定不要用字符串存储日期：MySQL日期类型选择建议 | JavaGuide

同财务相关的金额类数据必须使用 decimal 类型
单表不要包含过多字段：如果一个表包含过多字段的话，可以考虑将其分解成多个表，必要时增加中间表进行关联。

四、索引设计规范

建议单张表索引不超过5个
禁止使用全文索引：全文索引不适用于OLTP场景。
禁止给表中的每一列都建立单独的索引：5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。
每个InnoDB表必须有个主键

（1）InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

（2）InnoDB 是按照主键索引的顺序来组织表的

-不要使用更新频繁的列作为主键，不使用多列主键（相当于联合索引）

-不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）

-主键建议使用自增 ID 值

常见索引列建议

            -出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
            -包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
            -并不要将符合 1 和 2 中的字段的列都建立一个索引，通常将 1、2 中的字段建立联合索引效果更好
            -多表 join 的关联列

如何选择索引列的顺序
避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
对于频繁的查询优先考虑使用覆盖索引

五、SQL 开发规范

1）尽量不在数据库做运算，复杂运算需移到业务应用里完成；

2）优化对性能影响较大的 SQL 语句；

        要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询 MySQL 的慢查询日志来发现需要进行优化的 SQL 语句。
3）充分利用表上已经存在的索引；

避免使用双%号的查询条件。如：`a like '%123%'`，（如果无前置%,只有后置%，是可以用到列上的索引的）
一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。
在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

4）避免数据类型的隐式转换；
5）避免使用子查询，可以把子查询优化为 join 操作；
6）避免使用 JOIN 关联太多的表，建议不超过5个；
7）对应同一列进行 or 判断时，使用 in 代替 or；
         in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。
8）WHERE 从句中禁止对列进行函数转换和计算

        对列进行函数转换或计算时会导致无法使用索引。

        不推荐：where date(create_time)='20190101'

        推荐：where create_time >= '20190101' and create_time < '20190102'

9）在明显不会有重复值时使用 UNION ALL 而不是 UNION
10）拆分复杂的大 SQL 为多个小 SQL

六、必须阅读

技术同学必会的MySQL设计规约，都是惨痛的教训

聊聊数据库建表的15个小技巧

12. MySQL索引详解★★★★★

一、最左前缀匹配原则：

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配（相关阅读：联合索引的最左匹配原则全网都在说的一个错误结论）。

二、索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。
MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。
索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

总结一下索引下推应用范围：

适用于 InnoDB 引擎和 MyISAM 引擎的查询。

适用于执行计划是 range, ref, eq_ref, ref_or_null 的范围查询。

对于 InnoDB 表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用索引下推不会减少 I/O。

子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的。

存储过程不能使用索引下推，因为存储引擎无法调用存储函数。

三、MySQL的索引结构？★★★★★

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树，Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

左子树所有节点的值均小于根节点的值。

右子树所有节点的值均大于根节点的值。

左右子树也分别为二叉查找树。

二叉查找树的性能非常依赖于它的平衡程度。为了解决这个问题，并提高查询效率，人们发明了多种在二叉查找树基础上的改进型数据结构，如平衡二叉树、B-Tree、B+Tree 等。

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；

根节点总是黑色的；

每个叶子节点都是黑色的空节点（NIL 节点）；

如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；

从任意节点到它的叶子节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

B 树& B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

为什么MySQL的索引结构不使用Hash索引、二叉查找树(BST)、红黑树？

Hash索引：不支持顺序和范围查询。
二叉查找树(BST)：二叉查找树的性能非常依赖于它的平衡程度。
红黑树：平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。（下面的内容整理自《Java 工程师修炼之道》）

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

四、索引分类

按照数据结构维度划分：BTree 索引、哈希索引、RTree 索引、全文索引。
按照底层存储方式角度划分：聚簇索引、非聚簇索引。

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。

普通索引：仅加速查询。

唯一索引：加速查询 + 列值唯一（可以有 NULL）。

覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。

联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

主键索引 VS 二级索引

二级索引（Secondary Index）的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

唯一索引，普通索引，前缀索引等索引都属于二级索引。

唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index):普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

覆盖索引和联合索引：略。

聚簇索引与非聚簇索引：

聚簇索引：

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

非聚簇索引：

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

聚簇索引优点：查询速度非常快；对排序查找和范围查找优化。
非聚簇索引优点：更新代价比聚簇索引要小。缺点是可能会二次查询(回表)。