六、数据库（MySQL）

Github地址

CSDN地址

6、数据库（MySQL）

6.1 MySQL 索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表的特定信息，就像是一本书中的目录，加快查询速度。InnoDB存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在B+树中的。

6.1.1 B树和B+树

B+树是B树的变种，B+树的非叶子节点只用来保持索引，不存储数据，所有的数据都保存在叶子节点。而B树的非叶子节点也会保存数据。相比B+树的查询效率更稳定，均为根节点到叶子节点的路径
B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，同样的空间可以读入更多节点，所以B+树的磁盘读写代价更低。

6.1.2 聚簇索引和非聚簇索引

聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。
非聚簇索引（普通索引）的叶子节点内容是主键的值，在InnoDB里，非主键索引也被称为二级索引（secondary index）。

查找时：根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。

扩展1. Mysql回表
如果查询语句的where条件是普通索引，则先通过查询普通索引树，得到主键的值，再到主键索引树搜索一次。这个过程称为回表。

6.1.3 索引的最左前缀原则

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可利用索引加快查询速度。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。 最左前缀原则可以很明显的提高查询效率，是常见的MySQL性能优化手段。

6.1.4 索引下推

在MySQL5.6之前，只能从根据最左前缀查询到的ID开始一个个回表，到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

6.1.5 哪些列上适合创建索引？创建索引有哪些开销？

1. 不为 NULL 的字段
2. 被频繁查询的字段
3. 被作为条件查询的字段 （被作为 WHERE 条件查询的字段，应该被考虑建立索引）
4. 被经常频繁用于连接的字段

创建索引需要维护，再插入数据的时候会重新维护各个索引树（数据页的分裂和合并），并对性能造成影响。

扩展1. 自增主键
为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序。

6.1.6 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

eg. 覆盖索引使用实例
现在我创建了索引(username,age)，我们执行下面的 sql 语句

select username , age from user where username = 'Java' and age = 22
Copy to clipboardErrorCopied

在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表。

6.2 MySQL常见的存储引擎*

MySQL中常见的存储引擎有InnoDB和MyISAM，区别简单总结为：MyISAM表不支持事务、不支持行级锁、不支持外键； InnoDB支持事务、行级锁、外键如下：

• MyISAM不支持事务；InnoDB是事务类型的存储引擎
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁
• MyISAM引擎不支持外键；InnoDB支持外键
• 对于count(*)查询MyISAM更有优势，因为其保存了行数
• InnoDB是为了处理巨大数据量时的最大性能设计的存储引擎
• MyISAM支持全文索引（FULLTEXT）；InnoDB不支持

目前InnoDB应用最多，此外常见存储引擎还有Memory，使用Memory作为存储引擎的表称为内存表，将数据存在了内存中，适合作为临时表，在索引结构上支持B+树索引和Hash索引。

6.2.1 MySQL逻辑架构

由上图我们可以看出，逻辑架构 = Server层 + 存储引擎层。其中Server层包含连接层、分析器、优化器、以及执行器；存储引擎层包括多种支持的存储引擎，各个逻辑的 作用如下：

• 连接器：验证客户端权限，建立和断开MySQL连接
• 分析器：进行SQL语句的语法分析
• 优化器：选择索引，生成具体的SQL语句执行计划
• 执行器：操作存储引擎，执行SQL，返回结果
• 存储引擎层：各个不同的储存引擎提供了一些读写接口操作数据库

6.3 MySQL中where、group by、having关键字

where子句用来筛选from子句中指定的操作所产生的行
group by子句用来分组where子句的输出
having子句用来从分组的结果中筛选行

（1）having 和 where 的区别

• 语法类似，where搜索条件在进行分组操作之前应用；having搜索条件在进行分组操作之后应用
• having可以包含聚合函数sum、avg、max等
• having子句限制组，不限制行

（2）当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下

• ①执行where
• 使用group by对①进行分组
• ②对group by子句形成的组进行聚集函数计算每一组值
• ③having去掉②中不符合条件的组

6.4 MySQL的日志模块

在使用MySQL中，更新操作也是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录成本会很高。所以出现日志模块就是解决这个问题：update更新操作是先写日志，在合适的时间写磁盘，日志更新完毕就将执行结果返回客户端。
MySQL中的日志模块主要有redo log（重做日志）和binlog（归档日志）

6.4.1 redo log（重做日志）

redo log 是InnoDB引擎特有的日志模块，redo log是物理日志，记录某个数据页上做了哪些修改。InnoDB的redo log是固定大小的，比如可以配置一组4个文件。每个文件的大小是1GB，那么redo log总共就可以记录4GB的操作。从头开始写，写到末尾就回到开头循环写。
InnoDB的redo log保证了数据库发生异常重启后，之前的提交记录不会丢失，这个能力成为crash-safe

6.4.2 binlog（归档日志）

binlog是Server层自带的日志模块，binlog是逻辑模块日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。
binlog日志文件格式：statement、row、mixed；

• statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确性。
• row格式的binlog中记录的是数据行的实际数据变更，优点是数据记录准确，缺点是日志文件较大
• mixed格式的binlog是前两者的混合模式

目前推荐使用row模式，因为在生产环境下对准确性的要求排在第一位。

总结：

1. 在更新数据库的时候，通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正确恢复。
2. 在对数据库误操作之后，可以通过备份库+binlog可以将数据库状态恢复到“任意“时刻。

6.5 MySQL事务 及其 特点

事务是逻辑上的一组操作，要么都执行，要么都不执行。满足如下四个特性ACID：

• 原子性（Atomicity）：事务作为一个整体被执行，要么全部执行，要么全部不执行；
• 一致性（Consistency）：保证数据状态从一个一致状态状态转变为另一个状态；
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不影响其他事务的执行；
• 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存；

6.6 多个事务并发操作数据库会产生哪些问题？

• 脏读：事务A读取了事务B未提交的数据，由于事务B回滚，导致事务A的数据不一致，结果事务A出现了脏读；
• 不可重复读；一个事务内同一个查询操作执行两次或多次得到的结果数值不同，因为在两次查询过程中别的事务更新了该数据，并且提交了事务。
• 丢失更新：两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务就会被后提交的事务覆盖，导致事务A的数据更新丢失。
• 幻读：事务A读的时候都出了N条记录，事务B在事务A执行的过程中增加了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。

注意： 幻读指一种结构上的改变，比如说条数发生了改变；不可重复读是指读出的数值发生了变化。

6.7 MySQL数据库事务的隔离级别

为了避免数据库事务操作中的问题，MySQL定义了4个事务隔离级别，不同的隔离级别对事物的处理不同。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

• 读未提交（Read Uncommitted）：允许脏读取。如果一个事务已经开始写数据，则另一个数据不允许同时进行写操作，但允许其他事务读此行数据。
• 读已提交（Read Committed）：允许不可重复读，但不允许脏读。读取数据的事务允许其他事务继续访问该行数据，但未提交的写事务将会禁止其他事物访问该行。
• 可重复读（Repeated Read）：禁止不可重复读取和脏读取，但有时会出现幻读。读取数据的事物将会禁止写事务，但允许读事务，写事务则禁止任何其他事务。
• 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务之恶能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但对并发操作影响最大。MySQL事务默认隔离级别时可重复读。

6.8 独占锁 (X锁）和 共享锁（S锁）

MySQL数据库的锁分为表级锁和行级锁。行级锁又可以分为独占锁和共享锁。
InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

1. 独占锁（Exclusive Lock，排他锁）也称 X 锁
   独占锁锁定的资源只允许进行锁定操作的程序使用，其他任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE或DELETE命令时，MySQL会自动使用独占锁。但当对象上有其他锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放

2. 共享锁（Share Lock）也称 S 锁
   共享锁顾名思义，那就是其锁定的资源可以被其他用户读取，但其它用户不能修改。如果select查询语句中要手动加入共享锁，那对应的SQL语句：select ... lock in share mode

一个事务在一行数据上加入了独占锁，那么其余的事务不可以在该数据行上加入任何锁，也就是说加过排他锁的数据行在其他事务中是不能修改数据的，也不能for update和lock in share mode锁的方式查询数据，但可以直接通过select … from … 查询数据，普通查询没有任何锁机制

6.9 MySQL 死锁

MySQL中死锁主要是多个事务使用行级锁对某行数据加锁造成的，MyISAM不支持行级锁，所以说死锁一般指InnoDB中的死锁。

死锁解决方案：

• 指定锁的获取顺序
• 大小事务拆分为小事务
• 在同一个事物中，一次尽量锁定更多的资源，减少死锁概率
• 给表建立合适的索引以及降低事务的隔离级别等
• 数据库的设置来解决死锁：
  ①通过参数innodb_lock_wait_timeout根据实际业务场景来设置超时时间，InnoDB引擎默认值为50s。②发生死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

6.10 悲观锁与乐观锁

从开发人员看，数据库中的锁又可以分为悲观锁和乐观锁。

• 悲观锁：利用数据锁的机制实现，在整个数据处理过程中都加入了锁，以保持排他性
• 乐观锁：乐观锁可以利用CAS实现，在操作数据时进行一个比较，按照当前事务中数据和数据库表中的该数据是否一致来决定是否要执行本次操作。

6.10.1 乐观锁的ABA问题

1. 问题描述
   ABA问题指当前事务读取该行数据时值为 A，经过别的事务修改成 B，当时当前事务要更新数据时，又被别的事务修改为 A，事实上数据是发生过改变的，存在并发问题。
2. 解决方法
   增加数据版本记录机制。比如电商网站的所有表中都会增加两个时间字段：创建时间字段和最后一次更新时间字段。当然也可以简单的就增加一个版本字段，每更新一次版本+1。

6.11 SQL优化或者MySQL故障排查

1. 故障
   一般情况下，遇到SQL异常，如超时，可通过explain 查看当前SQL语句的执行情况。explain + SQL 可查看当前SQL语句使用的索引以及扫描了多少行数据。

2. 优化

• 根据慢日志定位慢查询sql
• 使用explain等工具分析sql
• 修改sql或者尽量让sql走索引

6.12 MYSQL 约束

MySQL建表是一般有以下5个常用的约束：

主键约束	唯一性，非空性
唯一约束	唯一性，可以为空
检查约束	对该列数据的范围、格式进行限制
默认约束	该数据默认值
外键约束	需建立两表之间的关系并引用主表的列

6.13 锁划分

• 按锁的粒度划分，可分为表级锁、行级锁、页级锁
• 按锁级别划分，可分为共享锁、排它锁
• 按加锁方式划分，可分为自动锁、显式锁
• 按操作划分，可分为DML锁、DDL锁按使用方式划分，可分为乐观锁、悲观锁

6.14 MVCC回滚，两阶段提交

6.15 大表优化

1. 限定数据的范围
   务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离
   经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区(待完善)
   根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

4. 水平分区
   当表的行数很大时，保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

6.16 什么是数据库连接池?为什么需要数据库连接池?

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。

6.17 MySQL高性能优化规范建议

MySQL高性能优化规范建议

6.18 为什么是需要两阶段呢？

这里它的2阶段是对应于不同类型的日志，所以两阶段为的就是让这个2个不同的日志做好处理与准备。

1. 假设是先写Redo Log，后写binlog。如果这个时候MySQL发生了进程的异常重启，由于Redo Log已经写完，MySQL崩溃之后通过crash_safe能力，能够把数据恢复回来。但是由于binlog还没写完就crash了，所以binlog里面并没有记录该SQL语句，所以使用binlog回档数据的时候，恢复出来的数据其实是少了一次更新操作的，这样就造成了灾难恢复出来的库和原库数据不一致；

2. 假设是先写binlog，后写Redo Log。Binlog写完之后发生了crash，由于Redo Log还没有写，崩溃恢复之后这个事务的更新是无效的。但是binlog里面记录了这条更新的语句，所以使用binlog回档的时候就多了一条事务的更新。造成回档出来的数据和原库的数据不一致。

综上两阶段提交就是：

• 1、prepare阶段，写redo log；
• 2、commit阶段，写binlog并且将redo log的状态改成commit状态；

mysql发生崩溃恢复的过程中，会根据redo log日志，结合 binlog 记录来做事务回滚：
1、如果redo log 和 binlog都存在，逻辑上一致，那么提交事务；
2、如果redo log存在而binlog不存在，逻辑上不一致，那么回滚事务；

6.19 explain字段

explain字段详解

-------------------------------未完待补充-----------------------------------