MySQL中的B+ Tree

在MySQL的InnoDB引擎创建的表，使用的B+ Tree的结构存储的数据，并对B+ Tree进行了优化。

B+ Tree只有单项指针， MySQL在这个基础上做了双向指针，这是支持范围查找的关键。

一般建表后，数据通过B+ Tree的数据结构存储，叶子节存储的是所有数据，那么非叶子节点中存储的是谁？

当设置主键后，非叶子节点存储的就是主键，通过这样的结构将数据存储在磁盘中，所以会自动生成主键索引。

为什么B+ Tree查询快？

MySQL默认B+ Tree的节点大小为16KB。

以主键索引举例，主键类型为bigint，默认占用8字节。

当做数据的key存储在非叶子节点中，因为要找到叶子节点中的数据，所以key需要自己下层索引的磁盘地址，这个占用6字节。

那么一个节点最大索引（key）数量为16KB/8+6=1170

一个h3（高度3）的数据，可以存储1170*1170*16=2KW+的数据。

因为高度为3，所以2KW数据最终只执行了3次磁盘IO交互就完成了查找。

在高版本的MySQL中，在MySQL启动时，会将非叶子节点放到内存中，这样通过一次磁盘交互就可以找到数据，所以查询很快。

索引的存储

索引会占用磁盘空间，因为所有的索引都是一个采用B+ Tree结构的数据文件，存储在磁盘中。

区别在于主键索引（聚簇索引）在B+ Tree的叶子节点上有数据。

其他索引（非聚簇索引）的叶子节点上存储的是主键。

当使用其他索引会先找到叶子节点上的主键，然后通过主键索引找到数据。

主键类型的选择

主键类型选择int，bigint与varchar。

bigint(推荐)：在B+ Tree结构的遍历查找中，会进行大小比较，整型比较大小快，支持的数据量更大。

int：与bigint优势大体相同，但是支持的数据量不够大，并且使用一些工具方法生成ID是位数也不够。

varchar（不推荐）：在B+ Tree结构的遍历查找中，比较大小会进行转hash码逐位比较，性能比整型差，占用空间也大。

主键属性可以选择自增，UUID，雪花等。

自增：在B+ Tree结构中是十分友好的增加数据方式，只会往树的右边插入数据，很少会出现触发树平衡的情况。

如果需要分表分库，也会有自增ID重复的情况。

同时也存在一定程度上的业务问题，比如用户知道ID为自增后，通过循环调用接口的方法获取所有数据。

UUID（不推荐）：UUID的值是随机的，可能会往B+ Tree的左边插入数据，如果左边节点中的key满了，那么会导致整个树进行分裂来重新平衡，这很消耗性能，而UUID可能会平凡触发这个操作。

雪花等其它方法（推荐）：以雪花算法为例，它是趋势递增，新的主键小于之前的概率很小，不会频繁触发树平衡机制。

聚簇索引与非聚簇索引

聚簇索引

  1. Mysql自动将采用了INNODB存储引擎表中主键建立索引，这个索引就是聚簇索引。

  2. 如果当前表中没有主键，MySql将会选择一个添加唯一性约束的字段作为聚簇索引。

  3. 如果当前表中既没有主键字段，也没有添加了唯一性约束字段MySql将随机选取一 个字段作为聚簇索引。

  4. 在采用INNODB存储引擎的表文件中，必然会存在一个聚簇索引。

  5. 在采用INNODB存储引擎的表文件中，只能有一个聚簇索引。

  6. 在表文件中其他字段上建立的索引都是非聚簇索引。

非聚簇索引

  1. 是由开发人员自行创建.

  2. 对于采用了INNODB存储引擎表，除了一个聚簇索引之外，其他字段上创建的索引都是非聚簇索引

  3. 在采用MyIsam存储引擎的表中，创建的所有索引都是非聚簇索引

区别

聚簇索引非叶子节点存储着主键，叶子节点存储着数据。

非聚簇索引非叶子节点存储key，叶子节点存储着主键。找到主键后要通过聚簇索引才可以找到数据。

MySQL事务的四大特性ACID及其原理

ACID四大特性

  1. 原子性(Atomicity)：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么不执行。

  2. 一致性(Consistency)：在事务开始之前和事务结束之后，数据不被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。

  3. 隔离性(Isolation)：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其他事务运行效果。

  4. 持久性(Durability)：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

ACID实现原理

事务的隔离性是通过数据库锁以及MVCC机制实现的。

事务的一致性由undo log来保证：undo log是逻辑日志，记录了事务的Insert、update、delete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。

事务的原子性和持久性由redo log来保证：redo log被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

事务的各个隔离级别都是如何实现的

读未提交

采用的是读不加锁原理

事务读不加所，不阻塞其他事务的读和写

事务写阻塞其他事务写，单不阻塞其他事务读

读已提交&可重复读

READ COMMITTED: 每次读取数据千都生成一个ReadView

REPEATABLE READ: 在事务里每次读取数据时生成一个ReadView

串行化

串行化的实现采用的时读写都加锁的原理

串行化的情况下，对于同一行事务，写会家写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等待前一个事务执行完成，才能继续执行。

MySQL查询语句中七个查询命令的特性

查询的优先级（谁先执行）

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

FROM

将硬盘上的表文件加载到内存中，生成一个全新的临时表。

WHERE

遍历FROM生成的临时表，根据条件进行过滤，并保存到一个新的临时表中，因为是逐行判断，所以无法使用聚合函数

GROUP BY

对临时表的数据先排序后分组，如果是多字段，那么第二字段开始操作的是上一个字段生成的临时表。

如果SELECT操作的临时表的GROUP BY提供的，那么会遍历所有分组字段的临时表的第一行内容。

HAVING

将GROUP BY生成的临时表中不满足条件的数据进行删除，并不会生成新的临时表。

这是操作的是一整个临时表，所以可以使用聚合函数。

SELECT

SELECT操作的临时表由FROM或WHERE提供的，那么会根据指定字段读取内容生成新的临时表。

如果由GROUP BY或HAVING提供的，那么会遍历GROUP BY生成的每一个临时，只读取第一行内容。

ORDER BY

专门针对SELECT生成的临时表中的数据进行排序，排序后的数据组成一个新的临时表。

LIMIT

对临时表的数据进行截取，并生成新的临时表。

七个命令中，只有HAVING不会生成临时表，其它都会。

七个命令中，只有FROM是从表文件从加载到内存，其他命令操作的都是上一个命令生成的临时表。

在当前命令执行完后，MySQL会销毁上一个命令生成的临时表。

多字段分组时，从第二个字段开始，操作的是上一个字段生成的临时表，所以多字段分组会生成多张临时表。

七个查询命令，那些命令执行的效率过慢

执行最慢的的是GROUP BY

原因：group by执行时间首先将临时表中的数据进行排序（排序等于全表遍历），然后再进行分组

order by

原因：需要对select生成的临时表中的数据进行一次排序（全表遍历），然后将拍好顺序的数据行组成一个全新的临时表

where

原因：需要对表中所有的数据行进行遍历，如果当前的临时表中的数据行过多，就会导致where执行效率非常慢

limit

原因：如果指定行数过大，就会导致查询速度慢。比如指定截取到20000行，它会从第0行开始找到20000行，指定的太大会遍历的数据过多，导致查询慢。

多表查询

多表查询没有指定表文件的加载顺序，也会导致查询速度过慢，也就是常说的小表驱动大表

MySQL查询慢的原因

  1. 用户量访问激增，访问量过大，使用mysql集群+负载均衡来处理

  2. 某些表数据过大，针对某些表查询慢，解决方案为查询语句优化

 

判断查询语句是否需要优化依赖工具

  1. 慢查询记录

  2. explain执行计划

  3. 相关日志文件（查看用户频繁执行的SQL语句是什么，方便创建多字段索引）

Explain执行计划详解

id属性：

【作用】: 通过id属性支出当前查询语句中相关表文件的加载顺序

【含义】:

情况1:如果被加载的表文件的ID属性内容是相同的，则根据自上而下的顺序进行加载 的.

情况2: 如果被加载的表文件的ID属性内容是不相同的。则ID较大的表，就先被加载。 ID较小的表，则后被加载.

情况3:如果被夹在的表文件的ID属性内容既有相同有存在不同时。则ID较大的表，就 现被加载。ID相同的表则按照自上而下顺序进行加载。

 

select_type属性：

【作用】: 对当前查询语句中的查询类型进行判断

【含义】:

1) Simple

表示当前查询语句是一个简单查询语句，不包含子查询，不包含联合查询，不半酣连接查询

2) Primary

如果执行的是一个包含子查询或者联合查询

Primary 指向的外部查询语句或者是联合查询的第一个子查询语句

3) DEPENDENT SUBQUERY

表示当前查询语句是一个子查询，并且执行条件依赖与外部查询提供的条件

4) SUBQUERY

表示当前查询是一个子查询，并且这个子查询再执行时不需要得到外部查询的帮助

 

type属性(重要)：

【作用】: 表示当前查询语句是否用到索引，索引的使用方式，当前查询语句的评价级别

【含义】:

以下按照最差级别到最好级别的顺序排列

1）all：全表扫遍，没有任何索引可以使用时，这是最差的情况，应该避免

2）index：定位数据行时，没有使用到索引（全表扫描），但是在已经定位的数据行中 读取字段时，却是使用索引，也就是说select命令前的其他命令没有触发索引， select却命中索引，这时才根据索引读取数据，执行效率低。

3）range：定位数据行，使用到索引。使用区间定位方式（where age < 23），这种状态 实际上是一种不稳定的状态，如果定位的数据行操作了表中总行数的30% 以上，就会导致mysql放弃使用索引定位。（开发中至少要达到这个级别）

4）eq_ref：关联查询时，根据唯一非空索引进行查询的情况

5）ref：定位数据行，使用到索引，采用直等方式（where name = zhangsan）

同时满足定位的条件的数据行只有一行。

6）const：定位数据行，使用到索引，采用主键索引来进行直等的方式（where id = 3）

7）system：操作表只有一行数据，并且数据内容是固定不变的。（实际开发中不存在）

8）null：无需访问表或者索引，比如获取一个索引列的最大值或最小值

******** SQL优化时，索引级别最低保证达到range级别

索引级别最高达到ref

用户查询用，一般不会使用主键字段如库表中的ID来进行查询，而是非主键字段，所以达到ref即可，而不是const级别

 

possible_keys属性：

【作用】: 表示当前查询语句执行时可能使用到的索引有那些。

 

key属性：

【作用】: 表示当前查询语句执行时真实使用到的索引，如果这个字段为null，则有两种可能，一是当前表中没有索引，二是当前表中有索引但是失效了。

possible_keys显示可能使用的索引有多个，mysql会选择性能最高的索引。

key_len属性：

【作用】: 如果本次查询使用了索引，则key_len内容不为空。

这里的数字表示当前采用索引字段的数据长度，这个值越大，定位数据更加精准。

 

索引那些情况下会失效

  1. 查询条件包含or，可能导致索引失效

  2. 如果字段类型时字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效

  3. 索引字段模糊搜索，只支持前置条件模糊搜索

  4. 联合索引，查询的条件列不是联合索引的第一列，索引失效

  5. 在索引列上使用Mysql内置函数，索引失效

  6. 对索引列运算，如 +、-、*、/ ，索引失效

  7. 索引字段上使用 !=或者 < >,not in时，可能会导致索引失效

  8. 索引上字段使用is null ,is not null可能会导致索引失效

  9. 左联机查询或右连接查询查询关键的字段编码格式不一样，可能导致索引失效

  10. Mysql优化器估计使用全表扫描比使用索引快，则不使用索引。