mysql基础

p1

41

p2

13

支持全文索引

磁盘块3

非聚簇索引

存储引擎：不同的存放位置，不同的文件格式

75

树：多叉树二叉树AVL树红黑树

63

34

每秒调用fsync()刷到磁盘

内存

data

指针：存储节点地址信息

特点

磁盘块1

否

35

哈希表可以完成索引的存储，每次添加索引的时候需要计算哈希值，取模后计算出下标，将元素插入下标位置即可适用场景：       等值查询       表中数据是无序的（范围查找比较耗时，需要挨个遍历）    企业中多数查询是范围查询，且hash表在使用的时候会把数据加载到内存中，耗费内存空间，不是特别合适

连接器

是

磁盘块9

可以查看sql语句的执行计划，以找到对应的优化点，加快sql执行速度

45

0

RedoLog--InnoDB存储引擎的日志文件 - 当发生数据修改的时候，InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新操作就算是完成了，同时InnoDB引擎会在合适的时机将记录操作到磁盘中。[WAL(write ahead log)：预写日志，提高性能] - redolog是固定大小的，是循环写的过程 - 有了redolog之后，InnoDB就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe

p3

是（5.6以后）

                                                           MyISAM                                               InnoDB

8

数据：即记录表中除主键外的数据

索引下推：简称ICP，在Mysql5.6的版本上推出，用于优化查询。       如：id       name      age              1   zhangsan      10              2   zhangsan      20              3   zhangsan      40              4   zhangsan      10select * from table where name=\"zhangsan\" and age=\"10\";如果没有索引下推，这条sql会先找到4条数据，然后每条数据都会进行回表查询匹配年龄为10的数据，回表4次；如果有索引下推，则在拿到4条数据时就会判断后续年龄是否满足，只拿到2条进行回表查询拿回全部数据。

二叉树

索引的分类

红黑树是升级版的AVL树，损失了部分查询的性能，来提升插入的性能，最高子树和最低子树的高度差小于2倍即可，如最高子树高度为8最低子树为4。不需要进行N多次旋转，而且加入了变色的操作，来满足插入和查询性能的平衡。

70

10

每次提交时写入OS Buffer，并通过调用fsync()刷到磁盘

红黑树

UndoLog - Undo Log是为了实现事务的原子性，在InnoDB引擎中，还用Undo Log来实现多版本并发控制（简称：MVCC） - 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log的备份将数据恢复到事务开始之前的状态。 - 注意：undo log是逻辑日志，可以理解为：     - 当delete一条记录时，undo log中会记录一条对应的insert记录     - 当insert一条记录时，undo log中会记录一条对应的delete记录     - 当update一条记录时，它记录一条对应相反的update记录

B+树

支持事务

大量insert、delete、update

二叉树及其N多种变形都不能满足索引，原因是树的深度无法控制或者插入数据的性能比较低

sql语句的实际执行组件

不同的存储引擎，数据文件和索引文件存储的位置是不同的，因此有了分类：       聚簇索引：数据和文件放在一起 -> InnoDB                        .frm：存放的是表结构                        .ibd：存放的是数据和索引       注意：mysql的InnoDB存储引擎默认把所有数据文件放在表空间中，不会为每个单独的表保存一份数据文件，如果需要把每个表用一个文件保存，设置如下属性：       set global innodb_file_per_table=on;       非聚簇索引：数据和索引单独一个文件 -> MyISAM                        .frm：存放的是表结构                        .MYI：存放索引                        .MYD：存放数据

客户端：向数据库发送请求

B树

磁盘块5

MySQL执行计划

执行计划索引选择

30

Log files

9

缺点：1、每个节点都有key和data，而每个页存储空间是有限的，如果每个data比较大的化会导致存储key的数量比较少2、当存储的数据量比较大时会导致树的深度较大，增加查询是磁盘io速度，进而影响查询性能

哈希表：哈希冲突

40

磁盘块6

词法分析语法分析

优化器

25

20

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

commit

索引类型

5

执行器

磁盘块8

4

19

39

支持外键

6

Buffer Pool

88

分析器

多叉树

磁盘块2

31

磁盘块4

B+树是在B树上做了优化：1、B+树每个节点可以包含更多的节点，第一个原因是降低树的深度，第二个原因是把数据划分为更多的范围区间，区间越多，查询速度越快。2、非叶子节点只存储key，叶子节点存储key和data3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高。

36

注意：        在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的节点，而且所有叶子节点是一种链式循环结构，因此可以对B+树进行两种查找运算：一种是基于主            键的范围和分页查找，另一种是从根节点开始，进行随机查找。注意：1、InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点存储记录，如果没有主键，那么会选择唯一主键，如果没有唯一主键，那么会生成一个6位的row_id来作为主键。2、如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找对应的记录，叫做回表注意：        MyISAM中索引与数据文件是分开存放的，索引中存的是对应数据的地址，通过地址再找到对应的数据，不会产生回表。

磁盘块7

键值：即表中记录的主键

7

支持表锁

采用数据库连接池，减少频繁创建连接的开销

每秒写入OS Buffer，并调用fsync()刷到磁盘

聚簇索引

MyISAM表锁MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的。注意：MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作之前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁。

2

Log Buffer

1

15

管理连接权限控制

树

29

MySQL锁机制

DML

27

每次提交时写入OS Buffer

在树的结构中，左子树必须小于根节点，右子树必须大于根节点，如果是多叉树，从左右到必须有序

64

磁盘块10

22

28

11

3

大量select

数据更新的流程：1、执行器先从存储引擎中找到数据，如果在内存中则直接返回，如果不在内存中则查询后返回2、执行器拿到数据之后会先修改数据，然后调用引擎接口重新吸入数据3、引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作4、执行器生成这个操作的binlog5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成

日志

磁盘

       MySQL索引的五种类型：主键索引、唯一索引、组合索引、普通索引和全文索引。通过给字段添加索引可以提高数据的读取效率，提高项目的并发能力和抗压能力。1、主键索引：主键索引是一种唯一性索引，它必须指定为PRIMARY KEY，每个表只能有一个主键，叶子节点存储行记录。   ---主键自增使用了自增锁，保证ID不会乱。会导致页分裂，页合并（IO操作浪费性能）2、唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空。（不需要回表）3、普通索引：基本的索引类型，叶子节点存储主键值，值可以为空，没有唯一性的限制（会产生回表，可使用覆盖索引，把主键索引放在搜索条件中）。4、全文索引：全文索引的索引类型为FULLTEXT，全文索引可以在varchar、char、text类型的列上创建，类似ES、Solr、Lucene（MyISAM支持、InnoDB在5.6以后支持）。5、组合索引：多列值组成给一个索引，专门用于组合搜索（最左匹配原则）。     最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。建立组合索引时要考虑这点，还要考虑字段的长度，怎么建出来的索引文件最小，索引文件越小，效率越高。

AVL树

BinLog - Binlog是server层的日志，主要做mysql功能层面的事情 - 一般企业中数据建库会有备份系统，可以定期执行备份，备份的周期可以设置 - 恢复数据的过程：     - 找到最近一次的全量备份数据     - 从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻 - 与redolog的区别：     - redolog是InnoDB独有的，binlog是所有引擎都可以使用的     - redolog是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑     - redolog是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前的日志信息

OS Buffer

mysql server

1、所有键值分布在整棵树中2、搜索有可能在非叶子节点结束，在关键字全集内做一次查找，其性能逼近一次二分查找3、每个节点最多拥有M棵子树4、根节点最少拥有2棵子树5、分支节点至少拥有M/2棵子树（除根节点和叶子节点外都是分支节点）6、所有叶子节点都在同一层，每个节点最多有M-1个键，并且升序排列

14

idselect查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序1、id相同，执行顺序从上到下2、id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行3、id相同和不同的，同时存在，相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行select_type主要用来分辨查询的类型，是普通查询还是联合查询还是子查询--simple：简单的查询，不包含子查询和union--primary：查询中包含任何复杂的子查询，最外层被标记为primary--union：若第二个select出现在union之后，则被标记为union--dependent union：跟union类似，此处的dependent表示union或union all联合而成的结果会受外部表的影响--union result：从union表获取结果的select--subquery：在select或者where包含子查询--dependent subquery：subquery的子查询受到外部表查询的影响--derived：from子句中出现的子查询，也叫做派生类--uncacheable subquery：表示使用子查询的结果不能被缓存--uncacheable union：表示union的查询结果不能被缓存table对应正在访问哪个表，表名或者别名，可能是临时表或者union合并结果集1、如果是具体的表明，则表明从实际的物理表中获取数据，当然页可以是表的别名2、表名是derivedN的形式，表明使用了id为N的查询产生的衍生表span style=\"font-weight: normal;\

AVL树是一种严格意义上的平衡树，最高子树和最低子树的高度差不超过1，所以在插入操作的时候会进行N多次的旋转，严重影响插入的性能。

InnoDB：磁盘MyISAM：磁盘memory：内存

适合操作类型

17

16

支持行锁