MySQL笔记

豆豆豆豆变
312 阅读11分钟

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括

www.bilibili.com/video/BV1Kr…

MySQL进阶

MySQL存储引擎

MySQL体系架构

image.png

●连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 ●服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。 ●存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

在MySQL中主要存在七种常用的日志类型，如下：

  • ①binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
  • ②redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
  • ③undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
  • ④error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
  • ⑤general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
  • ⑥slow-log：慢查询日志，主要记录执行时间较长的SQL。
  • ⑦relay-log：中继日志，主要用于主从复制做数据拷贝。

MySQL存储引擎

存储引擎就是存储数据、建索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

查询当前数据库支持的存储引擎

show ENGINES;

image.png

image.png

InnoDB

支持事务、行级锁、外键；每一张表对应一个.ibd文件（8.0以后），之前会额外有.frm文件存储表结构，8.0以后都融入到了.ibd文件中

image.png

image.png

一个Extent去一般大小为1M，包含64个Page Page页包含索引页和数据页，一页大小一般是16K，磁盘操作的最小单元

MyISAM

不支持事务、不行级锁、不支持外键 支持表锁 访问速度快 每一张myisam表对应三个文件：.MYD（数据）、.MYI（索引）、.sdi（表结构）

由于MyISAM引擎的非聚簇索引，关联的是行数据的指针，而InnoDB引擎关联的是聚簇索引的索引键，所以InnoDB的非聚簇索引在查询时需要回表，再查一次聚簇索引才能得到数据。而MyISAM每个非聚簇索引都能直接获取到行数据的地址，可以直接根据指针获取数据，从整体而言，MyISAM检索数据的效率会比InnoDB快上不少。

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

内存存放 hash索引（默认）

.sdi:存储表结构信息

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。(被MongoDB替代)

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。(被Redis替代)

MySQL索引分类

1 image.png

2

image.png

聚集索引选取规则：

  1. 如果存在主键，主键索引就是聚集索引。
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引I作为聚集索引。
  3. 如果表没有主键，或没有合适的唯一索引l，则innoDB会自动生成一个rowid作为隐藏的聚集索引。

image.png

MySQL索引高级

参考：juejin.cn/post/714907…

在建立联合索引的时候如何安排索引内的字段顺序：多考虑复用

  • 覆盖索引：在一棵索引树上就能查询到我们想要的数据，减少回表次数（就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖）
  • 最左前缀原则：这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
  • 索引下推：在MySQL 5.6之前，只能从ID=3开始一个个回表。到主键索引上找出数据行，再对比字段值。而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数从而减少磁盘IO（将Server层筛选数据的工作，下推到引擎层处理）
  • MRR多范围读取：使用缓冲区对需要回表的记录根据主键值进行排序，将随机IO优化为顺序IO。MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据
  • Index Skip Scan索引跳跃式扫描：没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来
千万级别的表B+Tree会有多高？

搞清楚B+Tree的一些疑惑后，此时来倒推一个问题，MySQL中一张千万级别的数据表，如果基于自增ID的主键字段建立B+树索引，那此时树会有多高呢？有人或许会认为，虽然B+Tree结构很优异，但千万级别的表至少有1000W条数据，再怎么样应该也有几十、几百的树高吧？但实际上答案会让你大吃一惊。

想要科学的弄懂这个问题，那必须建立在实际的依据上来计算，想要计算出树高，首先得有三个值：
①索引字段值的大小。
②MySQL中B+Tree单个节点的大小。
③MySQL中单个指针的大小。

如何计算索引字段值的大小呢？

这点要依据字段所使用的数据类型来决定。假设此时表的自增ID，创建表时使用的int类型，int类型在计算机中占4Bytes，那此时基于ID字段建立主键索引时，B+Tree每个节点的索引键大小就为4Bytes。

如何得知MySQL中B+树单个节点的大小呢？

对于索引单个节点的容量是多少呢？在MySQL中默认使用引擎的一页大小作为单节点的容量，假设此时表的存储引擎为InnoDB，就可以通过下述这条命令查询：

sql
 代码解读
复制代码
SHOW GLOBAL STATUS LIKE "Innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

从上述查询结果来看，InnoDB引擎的一页大小为16384Bytes，也就是16KB，此时也就代表着B+Tree的每个节点容量为16KB。

MySQL中的指针是多大呢？

一般来说，操作系统的指针为了方便寻址，一般都与当前的操作系统位数对应，例如32位的系统，指针就是32bit/4Bytes，64位的操作系统指针则为64bit/8Bytes，但由于64bit的指针寻址范围太大，目前的计算机根本用不上这么大的寻址范围，因此在MySQL-InnoDB引擎的源码中，单个指针被缩小到6Bytes大小。

千万级别的索引树高计算

从上述三条可得知：单个索引节点容量为16KB，主键字段值为4B，指针大小为6B，一个完整的索引信息是由主键字段值+指针组成的，也就是4+6=10B，那此时先来计算一下单个节点中可存储多少个索引信息呢？

16KB / 10B ≈ 1638个。

那此时来计算一下，对于一颗高度为2的B+树，根节点可存储1638个叶子节点指针，也就代表着B+Tree的第二层有1638个叶子节点，因为叶子节点要存储实际的行数据，假设表中每行数据为1KB，这也就是代表着一个叶子节点中可存储16条行数据，那么一颗高度为2的B+树可存储的索引信息为：1638 * 16 = 26208条数据。

再来算算树高为3的B+树可以存多少呢？因为最下面一排才是叶子节点，此时树高为3，也就代表着中间一排是叶节点，只存储指针并不存储数据，而每个节点可容纳1638个索引键+指针信息，因此计算过程是：1638 * 1638 * 16 = 42928704条。

是不是很令你惊讶？树高为3的B+Tree，竟然可以存储四千多万条数据，也就代表着千万级别的表，走索引查询的情况下，大致只需要发生三次磁盘IO即可获取数据。

当然，上述的这个数据是基于主键为int类型、表的一行数据为1KB来计算的，实际情况中会不一样，因为主键有可能是bigint类型或其他类型，而一行数据也可能不仅仅只有1KB。因此对于一张实际的千万级别表，它的主键索引实际树高有多少，你结合主键的数据类型以及一行数据的大小，也可以计算出来，它同时不会太高。
对实际的千万表索引树高感兴趣的，我提供一个计算公式：索引键大小=索引字段类型所占的空间、一行表数据大小=所有表字段的类型+隐藏字段（20Bytes）所占大小总和，得到这两个值之后，再套入前面的例子中既可得知。

看到这里，对于索引凭啥那么快？为啥能够提升查询性能？相信大家也有了答案，毕竟索引树高才是个位数，发生的磁盘IO次数也那么少，检索数据的速度不快才来了个鬼~

不过B+Tree中的每个索引页中，还会存储页头（页号、指针、伪记录等）、页目录、页尾等信息，大概一共占用128Bytes左右，因此想要真正的计算出来接近实际情况的索引树高，还需要把这点考虑在内~

作者：竹子爱熊猫
链接：juejin.cn/post/715127…
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

avatar
文章
阅读
粉丝