超详细MySQL笔记

百補园的张和

一、事务

1、概念

事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。

• 将小明的余额减少 1000 元
  
• 将小红的余额增加 1000 元。
  

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。这样就不会出现小明余额减少而小红的余额却并没有增加的情况。
2、事务的基本特性（ACID）

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；由undo log日志保证
  
• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；一般由代码层面来保证
  
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；由MVCC和锁来保证
  
• 持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响；由内存+redo log来保证
  

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！
3、事务中并发一致性的问题

• 脏读（Dirty read）: T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
  

• 丢失修改（Lost to modify）:T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
  

• 不可重复读（Unrepeatable read）: T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
  

• 幻读（Phantom read）: T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
  

不可重复读和幻读有什么区别呢？

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
  
• 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。
  

4、事务的隔离级别

• 读取未提交(READ UNCOMMITTED) ：
  

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• 读取已提交(READ COMMITTED) ：
  

是Oracle的默认隔离级别。
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• 可重复读(REPEATABLE READ) ：
  

是MySQL的默认隔离级别。
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• 可串行化(SERIALIZABLE) ：
  

最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

二、MySQL的锁

1、按锁的粒度分类：

• 表级锁：
  

MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁：
  

MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。只有InnoDB 引擎支持行级锁，且默认为行级锁。

2、按锁的类型分类：

读写锁：
不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

• 共享锁（S 锁） ：
  

又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

• 排他锁（X 锁） ：
  

又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。
意向锁：
如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。
意向锁是表级锁，意向锁之间是互相兼容的。共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：
  

事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

• 意向排他锁（Intention Exclusive Lock，IX 锁）：
  

事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
3、InNoDB中行锁定的方式

• 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
  
• 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  
• 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。
  

三、MySQL的储存引擎

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。
MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。
MyISAM与InnoDB的对比：

• 是否支持行级锁：
  

MyISAM 只有表级锁
InnoDB 支持行级锁和表级锁，默认为行级锁。

• 是否支持事务
  

MyISAM 不提供事务支持。
InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。

• 是否支持外键
  

MyISAM 不支持，而 InnoDB 支持。
外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

• 是否支持数据库异常崩溃后的安全恢复
  

MyISAM 不支持，而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

• 是否支持 MVCC
  

MyISAM 不支持，而 InnoDB 支持。
MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

• 索引实现不一样
  

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
InnoDB：其数据文件本身就是索引文件，储存在 .idb 的文件中。
MyISAM：索引文件和数据文件是分离的，索引储存在 .myi 的文件中，数据储存在  .myd 的文件中

四、索引

1、索引的概念：

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。
2、索引的优缺点：

优点 ：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  
• 索引需要使用物理文件存储，也会耗费一定空间。
  

3、索引的底层数据结构

哈希表：
哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。
缺点：

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

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
B 树& B+树：
B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

• B 树的所有节点既存放键(key) 也存放 数据(data)； B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  
• B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了；B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  

为什么MySQL要使用B+树替代B树作为索引的存储结构？
因为B树的每个节点都存储了索引和数据；而B+树只有在叶子节点存储了索引和数据，在其他节点只存储了索引。相比而言，B+树存储数据的单元最小，可以减少磁盘中扇区切换所带来的时间消耗。
4、索引的类型

• 主键索引：
  

数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引

• 辅助索引（二级索引）：
  

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引(Unique Key) ：

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

普通索引(Index) ：

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

前缀索引(Prefix) ：

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

全文索引(Full Text) ：

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

联合索引：

是指对表上的多个列进行索引。联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。

5、 聚集索引与非聚集索引

• 聚集索引：
  

聚集索引即索引结构和数据一起存放的索引。InnoDB储存引擎中的主键索引属于聚集索引。在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
优点：

• 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
  

缺点：

• 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  
• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。
  
• 非聚集索引：
  

非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
优点：

• 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
  

缺点：

• 跟聚集索引一样，非聚集索引也依赖于有序的数据
  
• 可能会二次查询（回表）：这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询
  

非聚集索引一定回表查询吗?



6、 覆盖索引、回表

• 覆盖索引：
  

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

• 回表：
  

如果一个查询语句的执行，需要检索两遍索引，这个过程称为回表。（首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录）。
7、联合索引、最左前缀匹配原则

• 联合索引：
  

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

• 最左前缀匹配原则：
  

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询。如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。
所以，我们在使用联合索引时，可以将区分度高的字段或场用于查询的字段放在索引的最左边，这也可以过滤更多数据。
8、索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
9、创建索引的注意事项：

1.选择合适的字段创建索引：

• 不为 NULL 的字段
  
• 被频繁查询的字段
  
• 被作为条件查询的字段
  
• 频繁需要排序的字段
  
• 被经常频繁用于连接的字段
  

2.被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
3.尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.注意避免冗余索引 。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
10、使用索引的一些建议

• 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  
• 应尽量避免在 where 子句中对字段进行 null 值判断。
  
• 应尽量避免在 where 子句中使用 != 或 <> 操作符。
  
• 应尽量避免在 where 子句中使用 or 来连接条件，尽量使用union all代替
  
• in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了。如果是子查询，可以用exists代替in。
  
• 应尽量避免在where子句中对字段进行函数操作
  
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算
  

11、MySQL 如何为表字段添加索引

1.添加 PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )2.添加 UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )3.添加 INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )4.添加 FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )五、数据类型

1、整型

tinyint、smallint、mediumint、int、bigint 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。
int(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。
2、浮点数

float 和 double为浮点类型，decimal为高精度小数类型。都可以指定列宽，例如 double(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。
3、字符串

主要有 char和 varchar 两种类型。

• varchar：是定长的，会保留字符串末尾的空格；适用于存储多字节的字符、经常不更新的字符串、长度波动大的字符串。
  
• char：是变长的，会保留字符串末尾的空格；适用于存储短的字符串、经常更新的字符串、长度波动不大的数据。
  

4、时间和日期

MySQL 提供了两种相似的日期时间类型: datetime和 timestamp。

• datetime：能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。它与时区无关。
  
• timestamp：和 UNIX 时间戳相同，只能表示从 1970 年 到 2038 年，使用 4 个字节，它和时区有关。默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。
  

六、MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
1、当前读和快照读

• 当前读
  

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 快照读
  

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
2、MVCC解决的问题

数据库并发场景有三种, 分别为：

• 读-读：不存在任何问题，也不需要并发控制
  
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
  
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
  

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
3、InnoDB 对 MVCC 的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性。如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

• 隐藏字段：
  

在内部，InnoDB 存储引擎为每行数据添加了三个 隐藏那个字段：

• DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
  
• DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
  
• DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引
  
• ReadView：
  

主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

• undo-log：
  

undo log 主要有两个作用：

• 当事务回滚时用于将数据恢复到修改前的样子
  
• 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读
  

在 InnoDB 存储引擎中 undo log 分为两种：

• insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
  
• update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除
  

七、性能优化

1、使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
比较重要的字段有:

• select_type : 查询类型，有简单查询、联合查询、子查询等
  
• key : 使用的索引
  
• rows : 扫描的行数
  

2、数据库命令规范

• 所有数据库对象名称必须使用小写字母并用下划线分割
  
• 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
  
• 数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
  
• 临时库表必须以 tmp_为前缀并以时间戳为后缀，备份表必须以 bak_为前缀并以时间戳为后缀
  
• 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）
  

3、数据库基本设计规范

• 没有特殊要求的情况下，所偶有的包必须使用InnoDB的储存引擎
  
• 数据库和表的字符集统一使用 UTF8
  
• 所有表和字段都需要添加注释
  
• 尽量控制单表数据量的大小,建议控制在 500 万以内。
  
• 谨慎使用 MySQL 分区表
  
• 尽量做到冷热数据分离,减小表的宽度
  
• 禁止在表中建立预留字段
  
• 禁止在数据库中存储图片,文件等大的二进制数据
  
• 禁止在线上做数据库压力测试
  
• 禁止从开发环境,测试环境直接连接生产环境数据库
  

4、数据库字段设计规范

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

原因：
列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。
a.将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据
MySQL 提供了两个方法来处理 ip 地址：

• inet_aton 把 ip 转为无符号整型 (4-8 位)
  
• inet_ntoa 把整型的 ip 转为地址
  

b.对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储
无符号相对于有符号可以多出一倍的存储空间

• 同财务相关的金额类数据必须使用 decimal 类型
  

Decimal 类型为精准浮点数，在计算时不会丢失精度，占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节，可用于存储比 bigint 更大的整型数据。

• 非精准浮点：float,double
  
• 精准浮点：decimal
  
• 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
  

5、索引设计规范

• 限制每张表上的索引数量,建议单张表索引不超过 5 个
  
• 禁止给表中的每一列都建立单独的索引
  
• 每个 Innodb 表必须有个主键
  
• 如何选择索引列的顺序
  
• 区分度最高的放在联合索引的最左侧
  
• 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
  
• 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
  
• 对于频繁的查询优先考虑使用覆盖索引，覆盖索引的好处：
  
• 避免 Innodb 表进行索引的二次查询
  
• 可以把随机 IO 变成顺序 IO 加快查询效率
  

八、MySQL 基本架构

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  
• 优化器： 按照 MySQL 认为最优的方案去执行。
  
• 执行器： 执行语句，然后从存储引擎返回数据。
  

总结：

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
  
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  
• 查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎
  
• 更新语句执行流程如下：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit状态)
  

九、MySQL的日志

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

Buffer Pool 是 InnoDB 存储引擎中的一个内存组件，里面缓存了磁盘上的真实数据，Java系统对数据库的增删改操作，主要是在这个缓存中执行的操作。
Buffer Pool的默认大小是128M。在实际的生产环境中可以通过参数innodb_buffer_pool_size对 buffer pool进行调整。
2、数据页

数据库的核心数据模型是表 + 字段 + 行的概念。 数据库对数据抽象出一个数据页的概念，将很多行的数据放到一个数据页中。即磁盘中有很多数据页，每个数据页存放很多行数据。
当要更新一行数据的时候，数据库会找到这行数据所在的数据页，然后从磁盘文件中把这行数据所在的数据页加载到 Buffer Pool 中。即Buffer Pool中存放的是一个一个的数据页。


PS:关于Mysql的体系构架、存储引擎和索引结构，可以阅读这篇文章：

3、缓存页

默认情况下，磁盘中存放的数据页的大小是16KB，即一页数据包含16KB的内容。
Buffer Pool中存放的一个一个的数据页，通常叫做缓存页。（Buffer Pool是一个缓冲池，里面的数据是从磁盘缓存到内存中的）
Buffer Pool中默认情况下，一个缓存页的大小和磁盘上一个数据页的大小是一一对应的，都是16KB。



4、 redo log（重做日志/事务日志）

• 它是物理日志，是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
  
• 它保证事务的持久性
  
• 它记录的是数据修改之后的值，不管事务是否提交都会记录下来。
  

5、undo log（回滚日志）

• 它记录的是数据被修改前的样子，主要用于事务在发生异常时，可以回滚事务。
  
• 它保证事务的原子性。
  
• 每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log，并将undo log保存到磁盘。
  

6、binlog（归档日志）

• 它会记录对这张表的整个操作，并且是顺序写；为主从复制和数据恢复提供了保证。
  
• 它保证了数据的一致性。
  
• 它是逻辑日志，记录内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1。属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。
  

十、常见的问题

1、一个完整SQL查询语句的书写顺序

-- "mysql语句编写顺序"
1 select distinct *
2 from 表(或结果集)
3 where …
4 group by …having…
5 order by …
6 limit start,count
-- 注：1、2属于最基本语句，必须含有。
-- 注：1、2可以与3、4、5、6中任一搭配，也可以与3、4、5、6中多个同时搭配。2、一个完整的SQL语句执行顺序





3、自增、UUID、雪花算法ID对比

• 自增 ID：有序 ID
  

• 优势：性能好、存储内容少、不会有页裂变问题、易读
  
• 劣势：分布式存储比较麻烦、mysql 有自增的 id 锁会有一定性能损耗（相对的）、容易被猜测数据被爬虫爬取
  

要支持分布式也可以，就是分表分库的时候设置不同的起始递增的位置
比如：表 A1 起始 101000000000，表 A2 起始 102000000000，这样 ID 就不会相同，预留 10 亿的数据，前面三位作为区分不通表或者机器

• UUID：无序随机 ID，时间戳 + 随机数 + 机器 MAC 等信息
  

• 优势：全网唯一、适合分布式分库分表存储、ID 没有规律不会被恶意猜测
  
• 劣势：性能 / 性能 / 性能、随机导致数据库存储大量的页 (插入性能会有比较大的影响，数据量非常大的时候查询也会有影响)、存储内容也较大（多了一倍多的存储空间）
  

关于 UUID 还有一种有序 UUID，有序 UUID 会有更好的性能，Hibernate 依赖包中有关于有序 UUID 的实现类：org.hibernate.id.UUIDHexGenerator。
如果项目中一定要使用 UUID 的话建议使用有序 UUID。

• 雪花算法：Snowflake，时间戳 + 随机数 + 服务器标记
  

• 优势：性能较好、有序、全局唯一适合分布式场景
  
• 劣势：存储内容较长、前端 Long 转换有精度丢失问题（需要实现 Json 转换解决精度问题）、服务器时钟回拨问题导致重复 ID (概率较低)、插入性能没有自增 ID 好
  

mybatisPlus的  IdWorker.getId()  就是使用雪花算法
4、mysql 插入数据后返回自增主键

• select LAST_INSERT_ID() as id;
  
• 使用max(id)；不适用于高并发场景
  

5、MySQL的主键ID为啥要自增

• 前提是InnoDB 引擎，避免页分裂。
  
• 底层的数据结构是B+Tree，左节点<根节点<右节点，一个数据页存满了，mysql 就会去申请一个新的数据页来存储数据。
  

另外，这几个也是常见的面试题

• 创建索引时需要注意什么？
  
• MySQL千万级别大表，你要如何优化？
  
• 索引是什么？MySQL为什么使用B+树，而不是使用其他？B+树的特点
  
• CHAR和VARCHAR的区别？
  
• 各种索引的概念：索引，主键，唯一索引，联合索引，索引分类
  
• Myql中的事务回滚机制,持久性，隔离级别的实现
  
• 说一说drop、delete与truncate的区别
  
• 什么叫视图？游标是什么？
  
• 什么是存储过程？用什么来调用？
  
• MySql索引是如何实现的
  
• 聚集索引与非聚集索引的区别
  
• NOW（）和CURRENT_DATE（）有什么区别？
  
• 事务四大特性（ACID）原子性、一致性、隔离性、持久性？
  
• 事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？
  
• MySQL常见的存储引擎InnoDB、MyISAM的区别？
  
• 数据库三范式，根据某个场景设计数据表？优缺点
  
• Mysql中的order by 是到底该怎么优化？
  
• MySQL 索引使用的注意事项
  
• 几千万数据，怎么快速查询？
  
• 数据库悲观锁和乐观锁的原理和应用场景？
  
• 内连接、自连接、外连接（左、右、全）、交叉连接的区别
  

其实很多大概率不会问，平常用的的很少，很多没啥真的技术含量，就理论知识硬背，所以小于哥就给大家整理了一下~
不会有人刷到这还想白嫖吧？不会吧？点赞对真的我很重要！要不加个关注？

作者：怀揣着梦想出发的博客
链接：https://blog.csdn.net/m0_59347746/article/details/125872973
来源：csdn

海森林

写的太好了，通俗易懂[赞同]