MySQL总结

索引

1
2
3
MySQL 索引使⽤的数据结构主要有 BTree 索引和哈希索引。
对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；
其余⼤部分场景，建议选择 BTree 索引。

什么是索引

1
2
3
4
5
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B树、B+树和 Hash。

索引的作用就相当于目录的作用。
比如，我们在查字典的时候，如果没有目录，那我们就只能一页页的去找我们需要查的那个字，速度很慢。
如果有目录，我们只需要先去目录里查找字的位置，然后直接翻到那一页。

索引的优缺点

1
2
3
4
5
6
7
优点：
    可以大大加快数据的检索速度（大大减少的检索的数据量），这也是创建索引的最主要的原因。
    另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

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

B树和B+树区别

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

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

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

Hash索引和B+树索引的优缺点

1
2
3
4
5
6
7
Hash索引
    优点：Hash索引定位快
    缺点：Hash冲突问题、Hash索引不支持顺序和范围查询

B+树索引
    优点：单次请求涉及的磁盘IO次数少、查询效率稳定、遍历效率高
    缺点：会产生大量的随机IO，主要存在以下两种情况：主键不是有序递增的、即使主键是有序递增的，大量写请求的分布仍是随机的

索引的类型

1
2
3
4
5
6
7
主键索引(Primary Key)
    数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为null，不能重复。
    MySQL 的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

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

聚集索引

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

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

缺点：
    依赖于有序的数据（因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，插入或查找的速度比较慢）。
    更新代价大（如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的）

非聚集索引

1
2
3
4
5
6
非聚集索引：索引结构和数据分开存放的索引。二级索引属于非聚集索引。
MySQL 中，MYISAM引擎的表的.MYI文件包含了表的索引、.MYD文件的数据包含了表的数据。

优点：更新代价比聚集索引要小。非聚集索引的叶子节点是不存放数据的。

缺点：非聚集索引也依赖于有序的数据、可能会二次查询(回表，当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询)。

覆盖索引

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

在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

索引创建原则

1
2
3
4
5
6
单列原则：单列索引即由一列属性组成的索引。

联合索引：联合索引即由多列属性组成索引。

最左前缀原则：假设创建的联合索引由三个字段组成，在创建联合索引时，把查询最频繁的那个字段作为最左字段。
    可能由于版本原因(mysql版本为8.0.x)，创建的联合索引，相当于在联合索引的每个字段上都创建了相同的索引。

选择合适的字段

1
2
3
4
5
6
7
不为NULL的字段：对于数据为NULL的字段，数据库较难优化。如果避免不了为NULL，建议使用0,1,true,false这样语义清晰的短字符作为替代。

被频繁查询的字段：创建索引的字段应该是查询操作非常频繁的字段。

被作为条件查询的字段：被作为WHERE条件查询的字段，应该被考虑建立索引。

被经常频繁用于连接的字段：可以考虑建立索引，提高多表连接查询的效率。

不合适创建索引的字段

1
2
3
4
5
6
7
8
9
被频繁更新的字段应该慎重建立索引

不被经常查询的字段没有必要建立索引

尽可能的考虑建立联合索引而不是单列索引

注意避免冗余索引

字符串类型的字段上使用前缀索引代替普通索引：前缀索引仅限于字符串类型，较普通索引会占用更小的空间。

使用索引的查询性能

1
2
大多数情况下，索引查询都是比全表扫描要快的。
但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

事务

什么是事务

1
2
3
4
5
事务是由N步数据库操作组成的逻辑执行单元，要么都执行，要么都不执行。

最经典的例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。
万一在这两个操作之间突然出现错误，导致小明余额减少而小红的余额没有增加，这样就不对了。
事务就是保证这两个关键操作要么都成功，要么要失败。

事务的特性

1
2
3
4
原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性保证操作要么都执行，要么都不执行。
一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰。
持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。
并发虽然是必须的，但可能会导致以下的问题。

丢失修改：某一个事务的（回滚、提交），导致另一个事务修改的数据丢失。

脏读：某一个事务，读取了另一个事务（未提交）的数据。

不可重复读：在一个事务内两次读到的数据不一致。

幻读：与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据。在随后的查询中，第一个事务就会发现多了一些原来不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是修改，如多次读取一条记录发现其中某些列的值被修改；
幻读的重点在于新增或者删除，如多次读取一条记录发现记录数量增多或减少。

事务的隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以防止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以防止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读）。
可以通过 SELECT @@tx_isolation; 命令查看

注意：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重复读）事务隔离级别下使用的是 Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。
所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取已提交)，但是 InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

锁

MyISAM 和 InnoDB 使用的锁

1
2
MyISAM 支持表级锁(table-level locking)。
InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。

锁分类（锁的粒度）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Mysql为了解决并发、数据安全的问题，使用了锁机制。

可以按照锁的粒度把数据库锁分为表级锁和行级锁。

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

行级锁：Mysql中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。
其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 

InnoDB支持的行级锁，包括如下几种。
    Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
    Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁）。其他事务不能在锁范围内插入数据。
    Next-key Lock: 锁定索引项本身和索引范围。即 Record Lock 和 Gap Lock 的结合。可解决幻读问题。

使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：
    事务更新大表中的大部分数据，直接使用表级锁效率更高；
    事务比较复杂，使用行级锁很可能引起死锁导致回滚。

锁分类（是否可写）

1
2
3
4
5
表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）。

共享锁（Share Locks）：又称为读锁。如果事务T对数据对象A加上S锁，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。

排它锁（Exclusive lock）：又称为写锁。如果事务T对数据A加上排他锁后，其他事务不能再对A加任何类型的锁。获取排他锁的事务既能读数据，又能修改数据。

池化设计思想

数据库连接池

1
2
3
4
5
6
7
8
9
10
11
12
13
池化设计应该不是⼀个新名词。如java线程池、jdbc连接池、redis连接池等是这类设计的代表实现。
这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。

除了初始化资源，池化设计还包括如下这些特征：池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等，
这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息，所以占⽤了⼀些内存。

数据库连接池维护的数据库连接的缓存，将来需要对数据库的请求时可以重⽤这些连接。
为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的⽹站应⽤程序的请求，既昂贵⼜浪费资源。

在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此不必建⽴新的连接。
如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

参考资料

https://snailclimb.gitee.io/javaguide/

#总结
MySQL总结
https://lcf163.github.io/2020/07/09/MySQL总结/
作者
乘风的小站
发布于
2020年7月9日
许可协议