MySQL图解记忆

MySQL架构

ICP索引下推

MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。

开启ICP特性后，由于 nickname 的 like 条件可以通过索引筛选，存储引擎层通过索引与 where 条件的比较来去除不符合条件的记录，这个过程不需要读取记录，同时只返回给Server层筛选后的记录，减少不必要的IO开销。

Extra显示的索引扫描方式

• using where：查询使用索引的情况下，需要回表去查询所需的数据。索引全扫描
• using index condition：查询使用了索引，但是需要回表查询数据。
• using index：查询使用覆盖索引的时候会出现。不需要回表
• using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

模糊匹配优化

alter table users01 add index idx_nickname(nickname);

select * from users01 where nickname like '%SK%';

从执行计划看到 type=ALL，Extra=Using where 走的是全部扫描，没有利用到ICP特性。

辅助索引idx_nickname(nickname)内部是包含主键id的，等价于(id，nickname)的复合索引

尝试利用覆盖索引特性将SQL改写为 select Id from users01 where nickname like '%SK%' 。

从执行计划看到，type=index，Extra=Using where; Using index，索引全扫描，但是需要的数据都在索引列中能找到，不需要回表。

利用这个特点，将原始的SQL语句先获取主键id，然后通过id跟原表进行关联，分析其执行计划：

select * from users01 a , (select id from users01 where nickname like '%SK%') b where a.id = b.id;

从执行计划看，走了索引idx_nickname，不需要回表访问数据，执行时间从60ms降低为40ms，type = index 说明没有用到ICP特性

但是可以利用 Using where; Using index 这种索引扫描不回表的方式减少资源开销来提升性能。

全文索引

explain select * from users01 where match(nickname) against('看风');

使用了全文索引的方式查询，type=fulltext，同时命中全文索引 idx_full_nickname，从上面的分析可知，在MySQL中，对于完全模糊匹配%%查询的SQL可以通过全文索引提高效率。

MySQL调优

• 排除缓存干扰：第一次sql缓存失效，更新后第一次查询会查不到缓存。执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。8.0以上版本没有缓存干扰问题

• Explain：

• 采用统计：InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用analyze table tablename来处理。

• 覆盖索引：如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品id，id一般都是主键。select itemId from itemCenter where size between 1 and 6，因为商品id itemId一般都是主键，在size索引上肯定会有我们这个值，这个时候就不需要回主键表去查询id信息了。

• 联合索引：建立一个名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了

• 最左匹配原则： itemname like ’谢%‘

• 索引下推：select * from itemcenter where name like '敖%' and size=22 and age = 20;即在引擎层就通过条件帅选出数据

• 唯一索引普通索引选择：change buffer

• change buffer：

  • 当需要更新一个数据页时，如果数据页在内存中就直接更新（唯一索引），而如果这个数据页还没有在内存中的话（普通索引），在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。
  • merge过程：在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
  • change buffer在内存中有拷贝，也会被写入到磁盘上。
  • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。
  • 

• 前缀索引：区分度很高，可以使用倒序、REVERSE()反转、删减字符串、substring截取字符串等，但调用函数也需要开销，

• 条件字段函数的操作：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

• 隐式函数转换：select * from t where id = 1 ，如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引。MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，函数会导致走不上索引。

• 隐式字符编码转换：如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，一旦两个字符比较，就会转换为utf8mb4再比较。转换的过程相当于加了CONVERT(id USING utf8mb4)函数

• flush：redo log大家都知道，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了redo log就会立马返回结果，但是这个redo log总会找个时间去更新到磁盘，这个操作就是flush。

  • 内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。
  • InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
  • 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
  • 

InnoDB

• 分为两大块：InnoDB In-Memory Structures 和 InnoDB On-Disk Structures，即缓存和磁盘

• 缓存：

  • Buffer Pool 是a linked list of pages，一个以页为元素的链表。采用基于 LRU（least recently used） 的算法来管理内存：
  • change Buffer：如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge：
  • Adaptive Hash Index自适应哈希索引：MySQL 会自动评估使用自适应索引是否值得，如果观察到建立哈希索引可以提升速度，则建立
  • Log Buffer：Log Buffer 里的 redo log，会被刷到磁盘里：

• 磁盘：平时创建的表的数据，可以存放到 The System Tablespace 、File-Per-Table Tablespaces、General Tablespace 三者中的任意一个地方

  • Doublewrite Buffer：Change Buffer 是提升性能，那么 Doublewrite Buffer 就是保证数据页的可靠性。MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer

• 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。
• InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。
• InnoDB 支持真正的在线热备份，即读取一致性视图不需要停止表的读写，而MySQL 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。
• 数据页：

MyISAM

• 提供了大量的特性，包括压缩表、空间数据索引等。不支持事务。

• 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。

• 在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

• InnoDB 和 MyISAM 的比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

索引

• B+ Tree：B+ Tree 是 B 树的一种变形，它是基于 B Tree 和叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。

• B+ 树有两种类型的节点：内部节点（也称索引节点）和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。内部节点中的 key 都按照从小到大的顺序排列，对于内部节点中的一个 key，左子树中的所有 key 都小于它，右子树中的 key 都大于等于它，叶子节点的记录也是按照从小到大排列的。

• 

• 查找：查找以典型的方式进行，类似于二叉查找树。起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部典型的使用是二分查找来确定这个位置。

• 删除：和插入类似，只不过是自下而上的合并操作。

• 红黑树：通过对从根节点到叶子节点路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的。所以相对于严格要求平衡的AVL树来说，它的旋转保持平衡次数较少。适合，查找少，插入/删除次数多的场景。

• AVL数：平衡二叉树，一般是用平衡因子差值决定并通过旋转来实现，左右子树树高差不超过1，那么和红黑树比较它是严格的平衡二叉树，平衡条件非常严格（树高差只有1），只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以 AVL 树适用于插入/删除次数比较少，但查找多的场景。

• B/B+数：多路查找树，出度高，磁盘IO低，一般用于数据库系统中。

• B+数和红黑树比较：（一）磁盘 IO 次数

  B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘 IO 次数更少。

  （二）磁盘预读特性

  为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

• 因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

• 因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

• 可以指定多个列作为索引列，多个索引列共同组成键。

• 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

• InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

• 哈希索引：哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找。

• 自哈希索引：当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

• 全文索引：查找条件使用 MATCH AGAINST，而不是普通的 WHERE。使用倒排索引实现，它记录着关键词到其所在文档的映射。

• 空间数据索引：MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。

索引优化

• 单列索引：在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引，如 SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

• 多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

• 索引顺序：让选择性（重复性小）最强的索引列放在前面。

• 前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

• 覆盖索引：索引包含所有需要查询的字段的值。

• 索引优点：帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

• 索引使用条件：对于中到大型的表，索引就非常有效；但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

explain

• select_type：常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。
• table：要查询的表
• possible_keys：可选择的索引
• key：实际使用的索引
• rows：扫描的行数
• type：索引查询类型，经常用到的索引查询类型：
  • system: 表只有一行，这是一个 const type 的特殊情况
  • const：使用主键或者唯一索引进行查询的时候只有一行匹配
  • eq_ref：在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录的时候
  • ref：使用非唯一索引
  • range：使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询
  • index：和all的区别是扫描的是索引树 。1）查询的字段是索引的一部分，覆盖索引。2）使用主键进行排序
  • all：扫描全表：

优化

• 减少请求的数据量：
  • 只返回必要的列：最好不要使用 SELECT * 语句。
  • 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
• 减少服务器端扫描的行数：最有效的方式是使用索引来覆盖查询，即覆盖索引

重构查询方式

• 切分大查询：一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
• 分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，

事务

原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。

一致性：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。

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

持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

隔离级别

未提交读（READ UNCOMMITTED）事务中的修改，即使没有提交，对其他事务也是可见的。

提交读（READ COMMITTED）一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。

可重复读（REPEATABLE READ）保证在同一个事务中多次读取同样数据的结果是一样的。

可串行化（SERIALIZABLE）强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。

锁

共享锁（S Lock）允许事务读一行数据

排他锁（X Lock）允许事务删除或者更新一行数据

意向共享锁（IS Lock）事务想要获得一张表中某几行的共享锁

意向排他锁：事务想要获得一张表中某几行的排他锁

MVCC

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

版本号

• 系统版本号：是一个递增的数字（全局），每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号：事务开始时的系统版本号。

隐藏的列

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

• 创建版本号：指示创建一个数据行的快照时的系统版本号；
• 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

Undo 日志

MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

• 在读提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的，在这个隔离级别下，事务在每次查询开始时都会生成一个独立的ReadView

• 可重复读，在第一次读取数据时生成一个ReadView，对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

• 总结：在使用读已提交（READ COMMITTD）、可重复读（REPEATABLE READ）这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

  这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，数据的可重复读其实就是ReadView的重复使用。

MVCC实现可重复读

当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增

因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

SELECT

多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。

把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于等于 T 的版本号。因为如果大于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。

除此之外，T 所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。

INSERT

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

DELETE

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

UPDATE

将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

快照读和当前读

在可重复读级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读：MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

当前读：MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

- select * from table where ? lock in share mode;
- select * from table where ? for update;

事务的隔离级别实际上都是定义的当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”的隔离性，就需要通过加锁来实现了。

锁算法

Record Lock：锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Lock：锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Lock：它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]  (10, 11]   (11, 13]    (13, 20]    (20, +∞)

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

Phantom Proble幻影读： 是指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。

幻影读是一种特殊的不可重复读问题。 Next-Key Lock解决

分库分表数据切分

• 水平切分

• 垂直切分

• Sharding 策略

  • 哈希取模：hash(key)%N
  • 范围：可以是 ID 范围也可以是时间范围
  • 映射表：使用单独的一个数据库来存储映射关系

• Sharding问题：

  • 事务问题：使用分布式事务来解决，比如 XA 接口

    连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。

    唯一性

    • 使用全局唯一 ID （GUID）
    • 为每个分片指定一个 ID 范围
    • 分布式 ID 生成器（如 Twitter 的 Snowflake 算法）

复制

主从复制

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

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

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

读写分离

• 主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
• 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

JSON

1. 在 where 条件中有通过 json 中的某个字段去过滤返回结果的需求
2. 查询 json 字段中的部分字段作为返回结果（减少内存占用）

• JSON_CONTAINS(target, candidate [, path]):如果在 json 字段 target 指定的位置 path，找到了目标值 condidate，返回 1，否则返回 0

  如果只是检查在指定的路径是否存在数据，使用JSON_CONTAINS_PATH()

• JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...): 如果在指定的路径存在数据返回 1，否则返回 0

范式

函数依赖：记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

第一范式 (1NF)：属性不可分。

第二范式 (2NF)：每个非主属性完全函数依赖于唯一一个键码。可以通过分解来满足。

第三范式 (3NF)：非主属性不传递函数依赖于键码。

ER图：有三个组成部分：实体、属性、联系。用来进行关系型数据库系统的概念设计。

磁盘IO和索引

• 预读： 当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

• 每一次IO读取的数据我们称之为一页(page)，具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO。

• MySQL中常用的索引在物理上分两类，B-树索引和哈希索引。

• B-Tree索引：这是一个3叉（只是举例，真实会有很多叉）的BTree结构图，每一个方框块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，紫色代表的是磁盘块中的数据key，黄色代表的是数据data，蓝色代表的是指针p，指向下一个磁盘块的位置。

• B+Tree索引：B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。每个Leaf Node是三部分组成的，即前驱指针p_prev，数据data以及后继指针p_next，同时数据data是有序的，默认是升序ASC，分布在B+tree右边的键值总是大于左边的，同时从root到每个Leaf的距离是相等的，也就是访问任何一个Leaf Node需要的IO是一样的，即索引树的高度Level + 1次IO操作。

• B+Tree非叶子节点只存储键值信息， 数据记录都存放在叶子节点中， 将B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，所以B+Tree的高度可以被压缩到特别的低。

• 聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据，辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。

• 索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：

  • 在经常需要搜索的列上建立索引，可以加快搜索的速度。
  • 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
  • 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
  • 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
  • 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

• 虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

• 叶子节点是一个递增的数组，可以用二分法搜索

int和byte

int占4个字节，即表示int类型的存储大小为4个字节。
如果转成十进制来说就是“-2147483648 ~2147483647”
即：int只能存放这么大的数字。。。超出范围则溢出。。。

再来说byte
byte最大能够存放 -128～127 的数值。

那为什么是 -128～127
这个跟字节编码有关
首先知道byte是一个字节保存的，有8个位，也就是8个0、1。
8个位的第一个位是符号位，
也就是说0000 0001代表的是数字1
1000 0000代表的就是-1
所以正数最大位0111 1111，也就是数字127
负数最大为1111 1111，也就是数字-128

索引设计

• MySQL中的索引可以看成一张小表，占用磁盘空间，创建索引的过程其实就是按照索引列排序的过程，先在sort_buffer_size进行排序，如果排序的数据量大，sort_buffer_size容量不下，就需要通过临时文件来排序，最重要的是通过索引可以避免排序操作（distinct，group by，order by）。

• 对于OLAP(联机分析处理)的业务场景，需要扫描返回大量数据，这时候全表扫描的顺序IO效率更高。

• 全表扫描是顺序IO，索引扫描是随机IO，MySQL对此做了优化，增加了change buffer特性来提高IO性能。

• 分页查询优化：利用辅助索引的覆盖扫描进行优化，先获取id，这一步就是索引覆盖扫描，不需要回表，然后通过id跟原表trade_info进行关联

  select * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;
  // 优化为：
  select * from trade_info a ,
  (select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   //这一步走的是索引覆盖扫描，不需要回表
   where a.id = b.id;
  

• 前缀索引：-- 创建前缀索引，前缀长度为30 create index idx_nickname_part on users(nickname(30));

• 复合索引：唯一值多选择性好的列作为复合索引的前导列。但只适合于等值条件过滤，不适合有范围条件过滤的情况，如：elect * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';

  • 原则1：将范围查询的列放在复合索引的最后面，例如idx_status_create_time。
  • 原则2：列过滤的频繁越高，选择性越好，应该作为复合索引的前导列，适用于等值查找，例如idx_user_id_status。

• 跳跃索引：适合复合索引前导列唯一值少，后导列唯一值多的情况，如果前导列唯一值变多了，则MySQL CBO不会选择索引跳跃扫描，取决于索引列的数据分表情况。

• 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。

• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

• 索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time=UNIX_TIMESTAMP('2016-06-06')。

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

• 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。

• “合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来

主键

• 自增ID达到上限用完了之后，分为两种情况：

  1. 如果设置了主键，那么将会报错主键冲突。
  2. 如果没有设置主键，数据库则会帮我们自动生成一个全局的row_id，新数据会覆盖老数据

• 解决方案：

  表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的

DDL

• CREATE，ALTER，DROP，RENAME，TRUNCATE。这些操作都是隐式提交且原子性，要么成功，要么失败，在MySQL 8.0之前DDL操作是不记录日志的。

• 

• MetaData元数据：描述的对象的结构信息，一般都是静态化，只有表上发生了DDL操作才会实时更新。

• MySQL利用MetaData Lock来管理对象的访问，保证数据的一致性，对于一些核心业务表，表上DML操作比较频繁，这个时候添加字段可能会触发MetaData Lock。

• DDL执行方式: ALTER TABLE的选项很多，跟性能相关的选项主要有ALGORITHM和LOCK。

  • copy：MySQL早期的变更方式，需要创建修改后的临时表，然后按数据行拷贝原表数据到临时表，做rename重命名来完成创建，在此期间不允许并发DML操作，原表是可读的，不可写，同时需要额外一倍的磁盘空间。
  • INPLACE：直接在原表上进行修改，不需创建临时表拷贝数据及重命名，原表会持有Exclusive Metadata Lock，通常是允许并发DML操作。
  • INSTANT：MySQL 5.8开始支持，只修改数据字典中的元数据，表数据不受影响，执行期间没有Exclusive Metadata Lock，允许并发的DML操作。

• LOCK：无锁、共享锁、排它锁

• MySQL 8.0推出了INSTANT方式，真正的只修改MetaData，不影响表数据，所以它的执行效率跟表大小几乎没有关系。既解决主从同步，又解决rename数仓不同步的问题

• 监控DDL执行进度：在MySQL 8.0可以通过开启performance_schema，打开events_stages_current事件进行监控。

log

• binlog：数据库的变更，搜索引擎的数据也需要变更。搜索引擎监听binlog的变更，如果binlog有变更了，那我们就需要将变更写到对应的数据源。即记录了数据库表结构和表数据变更，不会记录select。存储着每条变更的SQL语句

  • 作用：复制和恢复数据，实现主从复制
  • 主从服务器需要保持数据的一致性，通过binlog来同步数据。
  • 如果整个数据库的数据都被删除了，binlog存储着所有的数据变更情况，那么可以通过binlog来对数据进行恢复。

• redo log：

  

  redo log的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快。

  redo log 记录的是数据的物理变化，binlog 记录的是数据的逻辑变化。binlog记载的是update/delete/insert这样的SQL语句，而redo log记载的是物理修改的内容（xxxx页修改了xxx）。

  redo log是MySQL的InnoDB引擎所产生的。binlog无论MySQL用什么引擎，都会有的。

  持久性就是靠redo log来实现的

• undo log：

  • 作用：回滚和多版本控制(MVCC)，实现事务的原子性
  • 主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。
  • undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了。

Text类型

由于MySQL是单进程多线程模型，一个SQL语句无法利用多个cpu core去执行，这也就决定了MySQL比较适合OLTP（特点：大量用户访问、逻辑读，索引扫描，返回少量数据，SQL简单）业务系统，同时要针对MySQL去制定一些建模规范和开发规范，尽量避免使用Text类型，它不但消耗大量的网络和IO带宽，同时在该表上的DML操作都会变得很慢。

另外建议将复杂的统计分析类的SQL，建议迁移到实时数仓OLAP中，例如目前使用比较多的clickhouse，里云的ADB，AWS的Redshift都可以，做到OLTP和OLAP类业务SQL分离，保证业务系统的稳定性。

• 使用es存储：在MySQL中，一般log表会存储text类型保存request或response类的数据，用于接口调用失败时去手动排查问题，使用频繁的很低。可以考虑写入本地log file，通过filebeat抽取到es中，按天索引，根据数据保留策略进行清理。

• 使用对象存储：有些业务场景表用到TEXT，BLOB类型，存储的一些图片信息，比如商品的图片，更新频率比较低，可以考虑使用对象存储，例如阿里云的OSS，AWS的S3都可以，能够方便且高效的实现这类需求。

InnoDB表空间

• 系统表空间： 主要存储MySQL内部的数据字典数据，如information_schema下的数据。

  用户表空间： 当开启innodb_file_per_table=1时，数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中，结构信息存储在table_name.frm文件中。

  Undo表空间： 存储Undo信息，如快照一致读和flashback都是利用undo信息。

• delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；

• 在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。

• 在MySQL数据库建模规范中有4个公共字段: id、is_deleted、create_time、update_time，基本上每个表必须有的，同时在create_time列要创建索引，有两方面的好处：

  1. 一些查询业务场景都会有一个默认的时间段，比如7天或者一个月，都是通过create_time去过滤，走索引扫描更快。
  2. 一些核心的业务表需要以T +1的方式抽取数据仓库中，比如每天晚上00:30抽取前一天的数据，都是通过create_time过滤的。

• 采用Clickhouse，对有生命周期的数据表可以使用Clickhouse存储，利用其TTL特性实现无效数据自动清理。

SQL优化

• UDF用户自定义函数：SQL语句的select后面使用了自定义函数UDF，SQL返回多少行，那么UDF函数就会被调用多少次，这是非常影响性能的：select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction

• text类型；如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错

• group_concat：gorup_concat是一个字符串聚合函数，会影响SQL的响应时间，如果返回的值过大超过了max_allowed_packet设置会导致程序报错。

• 内联子查询：在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info

• 表的链接方式：在MySQL中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，SQL性能变得很差，同时要清楚ON和Where的区别。

• 子查询：由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。

  select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
  and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;
  

• 索引列被运算：当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效。

• 类型转换：对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。

• 列字符集：从MySQL 5.6开始建议所有对象字符集应该使用用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询Join时字段字符集不匹配导致索引失效，同时目前只有utf8mb4支持emoji表情存储。

• 前缀索引：group/order by后面的列有索引，索引可以消除排序带来的CPU开销，如果是group by条件是前缀索引，是不能消除排序的。

• 函数运算：假设需要统计某月每天的新增用户量，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date date类型来解决这种问题。

• limit m,n要慎重: 对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。

• 字段顺序：排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。

• 时间列索引：对于默认字段created_at(create_time)、updated_at(update_time)这种默认就应该创建索引，这一般来说是默认的规则。

• 复合索引顺序：MySQL遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询（>=, >,<, <=, between ….. and ….）就停止扫描

• 索引属性；索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的count(*)说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键id的选择率是100%，在MySQL中尽量所有的update都使用主键id去更新，因为id是聚集索引存储着整行数据，不需要回表，性能是最高的。即先查出主键再更新数据

• comment属性：字段的备注要能明确该字段的作用，尤其是某些表示状态的字段，要显式的写出该字段所有可能的状态数值以及该数值的含义。

• default属性：在创建表的时候，建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充响应的默认值。

• not null属性：根据业务含义，尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。

• AUTO_INCREMENT属性: 在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数，如果id的值达到了最大值，下一个值就从0开始继续循环递增，在代码中禁止指定主键id值插入。

• 表&列名关键字: 在数据库设计建模阶段，对表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。同时建议设置lower_case_table_names = 1表名不区分大小写。

安全

• 撞库:”撞库”是黑客通过收集互联网已泄露的用户和密码信息，生成对应的字典表，尝试批量登陆其他网站后，得到一系列可以登录的用户。

• 脱库：指从数据库中导出数据。它被用来指网站遭到入侵后，黑客窃取其数据库。

• 洗库：“洗库”，属于黑客入侵的一种，就是黑客入侵网站，通过技术手段将有价值的用户数据归纳分析，售卖变现。

设计数据库

• 根据业务模块的需求去分析，抽取成CDM中的E-R图，转换成LDM，经过数据库选型及生成PDM，最终生成数据库表，然后才能开始coding，测试、发布上线以及版本迭代，为了保证线上业务的安全稳定高效，就需要对数据库进行精细化管理和维护。

• select count(*):

  • 针对无 where_clause 的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，其实反而性能最高
  • 不管是 COUNT(1)，还是 COUNT()，MySQL 都会用成本最小的辅助索引查询方式来计数，也就是使用 COUNT() 由于 MySQL 的优化已经保证了它的查询性能是最好的！随带提一句，COUNT()是 SQL92 定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT(*)查询表的行数！

• 数据库连接池大小：连接数 = ((核心数 * 2) + 有效磁盘数) 。4核i7数据库服务器的连接池大小应该为((4 * 2) + 1) = 9

分库分表

• 垂直分表：常规的方案是冷热分离（将使用频率高字段放到一张表里，剩下使用频繁低的字段放到另一张表里）。

• 水平分表：按照表中的记录进行分片。 单表不建议超过 500w，1亿数据分20个子表就够了。

• 按月分表：根据业务的特性，可以按月创建表

• 分区表：保证 SQL 正确的路由，执行并返回结果。常规的 hash 也是基于分区个数取模（%）运算的

• 按业务分库：例如将电商中，将数据库分为订单数据库、价格数据库、库存数据库

• 按表分库：分库其实是拆分 RDS 实例，是将拆分后的子表存储在不同的 RDS 实例中。扩容一台同配置的RDS实例，将订单表 orders 拆分20个子表，每个 RDS 实例10个。查询的时候要先通过分区键 user_id 定位是哪个 RDS 实例，再定位到具体的子表，然后做 DML操作

• 分布式数据库：代码改造（数据库中间件mycat，sharding-sphere）和分布式数据库（实际业务中使用比较多的有 PingCAP TiDB，阿里云 DRDS）

• 分库分表主要有两种方案：代码改造（数据库中间件mycat，sharding-sphere）和分布式数据库（实际业务中使用比较多的有 PingCAP TiDB，阿里云 DRDS），可以优先使用分布式数据库方案，虽然成本会有所增加，但对应用程序没有侵入性，同时也可以比较好的支撑业务增长和系统快速迭代

主从复制

• MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中；
• 主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性；
• MySql通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上；
• 当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示。