MySQL 总结知识

一、 mysql 的执行引擎

•  1、innodb： 默认的存储引擎是 InnoDB ，并且也是最主流的选择 

支持事务。

支持崩溃后恢复。

• 2、MyISAM：在 MySQL5.1 以及之前的版本，默认的存储引擎是 MyISAM ，但是目前已经不再更新 

不支持事务。

二、索引

索引，都是实现在存储引擎层的。

1、主要有六种类型：

1、普通索引：最基本的索引，没有任何约束。

4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。 

5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。 

6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。（常用的全文索引引擎的解决方案有 Elasticsearch） 

2、MySQL 索引的“使用”注意事项？

- 0、 应尽量避免在 where 子句中对字段进行 null 值判断 ，否则将导致引擎放弃使用索引而进行全表扫描 。 

- 1、 应尽量避免在 WHERE 子句中使用 != 或 <>操作符 ， 否则引擎将放弃使用索引而进行全表扫描。 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。 注意，column IS NULL 也是不可以使用索引的。 

- 2、 应尽量避免在 where 子句中使用 or 来连接条件 ，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。 

- 3、 应尽量避免在 WHERE 子句中对字段进行表达式操作 ，这将导致引擎放弃使用索引而进行全表扫描。 

- 4、 应尽量避免在 WHERE 子句中对字段进行函数操作 ，这将导致引擎放弃使用索引而进行全表扫描。 

- 5、 不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算 ，否则系统将可能无法正确使用索引。 

- 6、 尽量使用数字型字段 ，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 

- 7、 列类型是字符串类型，查询时一定要给值加引号 ，否则索引失效。

- 8、 LIKE 查询，% 不能在前，因为无法使用索引 。如果需要模糊匹配，可以使用全文索引。   

- 9、 尽量的扩展索引，不要新建索引 。

- 10、 复合索引遵循前缀原则 。索引的最左匹配特性

3、索引结构

为了描述B-Tree，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。

InnoDB存储引擎就是用 B+Tree 实现其索引结构。

B+树结构中有：      

• 非叶子节点只存储键值信息，
• 数据全部冗余到了叶子节点，
• 而且所有叶子节点之间都有一个链指针，所以支持范围查找

1. 在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。
2. 通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。 因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
3. 在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

面试题1：为什么不用B树作为索引？

4、B-Tree 有哪些索引类型

在 B+Tree 中，根据叶子节点的内容，索引类型分为 主键索引 和 非主键索引 。

innodb中的主键索引和实际数据绑定在一起，也就是说Innodb的一个表一定要有主键索引。如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有，则选用唯一索引作为主键索引；如果连唯一索引也没有，则会默认建立一个隐蔽的主键索引，这个隐形字段长度为6个字节，类型为长整形（用户不可见）。

1）主键索引：

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）

2）. InnoDB的辅助索引

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

总结：

当通过辅助索引来查询数据时，需要进过两步：

首先，InnoDB 存储引擎会遍历辅助索引找到主键。 

然后，再通过主键在聚集索引中找到完整的行记录数据。

面试题2、为什么主键需要是自增 ID ，又或者为什么主键需要带有时间性关联？

三、事务

（一）、事务的特性  ：指的是 ACID

1. 原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
2. 一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
3. 隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4. 持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

（二）、事务的隔离级别

1、事务定义了四种事务隔离级别，不同数据库在实现时，产生的并发问题是不同的。

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

  会导致脏读。

• READ COMMITTED（读已提交）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

  会导致不可重复读。

  这个隔离级别，也可以叫做“不可重复读”。

• REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行。

  会导致幻读。

• SERIALIZABLE（可串行化）：强制事务串行执行。

2、MySQL InnoDB 采用 MVCC 来支持高并发，实现结果如下表所示：

• MySQL 默认的事务隔离级别为可重复读（repeatable-read） 。
• 上图的 <X> 处，MySQL 因为其间隙锁的特性，导致其在可重复读的隔离级别下，不存在幻读问题。也就是说，上图 <X> 处，需要改成“否”！！！！
• 有些资料说可重复读解决了幻读，实际是存在的，可以通过 SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式，获得到悲观锁，禁止其它事务操作对应的数据，从而解决幻读问题

四、mysql锁机制

1、MySQL 的共享锁和排他锁，就是读锁和写锁。

2、表锁与行锁：由执行引擎决定

使用行锁或者表锁都是使用的悲观锁： SELECT ...... FOR UPDATE;

当查询有明确主键时使用的是行锁；查询无明确主键时使用表锁

• 表锁：系统开销最小，会锁定整张表，MyIsam 使用表锁。
• 行锁：最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB 使用行锁。

3、悲观锁与乐观锁

• 乐观锁：可以读，不能写 
• 悲观锁：不能读写，就是上面看到的共享锁和排他锁

4、死锁

死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。简单的说，进程 A 等待进程 B 释放他的资源，B 又等待 A 释放他的资源，这样就互相等待就形成死锁。

下列方法有助于最大限度地降低死锁：

• 设置获得锁的超时时间。 通过超时，至少保证最差情况下，可以有退出的口子。
•  按同一顺序访问对象。 这个是最重要的方式。 
• 避免事务中的用户交互。 
• 保持事务简短并在一个批处理中。 
• 使用低隔离级别。
• 使用绑定连接。

面试题：MySQL 中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？

InnoDB 是基于索引来完成行锁。

例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE

五、MySQL 查询执行顺序

(1)     SELECT
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>复制代码

六、什么是 MVCC

多版本并发控制（MVCC），是一种用来 解决读-写冲突 的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

七、MySQL SQL 优化

参考：

1、https://www.cnblogs.com/nullzx/p/8729425.html

2、https://blog.csdn.net/bigtree_3721/article/details/73650601

3、http://svip.iocoder.cn/MySQL/Interview/