关于MySQL的学习及相关调优

记录一下MySQL相关的学习历程

MySQL简单看分成三层，client、server、存储引擎

• 用户从client端发送一个请求到server端，

• server端会创建一个连接信息【验证用户名密码等信息】

• 然后将用户发送的sql请求进行分析【经过分析器】，将sql的关键字等信息进行提取分析，

• 然后将sql语句进行优化【优化器，MySQL自带sql优化】

• 后面经过执行器，执行器将和存储引擎进行交互（经过存储引擎从磁盘读取数据，即IO）

优化分两种：RBO、CBO

• RBO：基于规则的优化

• CBO：基于成本的优化（大部分采用这种）

索引面试技术名词

回表：当查询数据时用到普通索引，第一次是在普通索引树上查找并返回主键值，再根据主键值去索引树中查找对应行的数据

覆盖索引：当根据索引树查找数据时，返回的结果就是需要查找的值，不需要回表操作，叫做覆盖索引

最左匹配：where条件中开始的列应该是组合索引最左开始的列

索引下推：当通过组合索引中某个列查找数据时，会自动将组合索引另外的相关列信息一起查找并返回，以满足可能被使用的情况，叫做索引下推

 

聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

非聚簇索引：数据文件跟索引文件分开存放

 

索引采用的数据结构

哈希：memory引擎，哈希存储在内存中，刚好满足memory引擎，不能持久化

缺点：

1、利用hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间

2、如果所有的查询都是等值查询，那么hash确实很快，但是在企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此hash就不太适合了

 

B+树：innodb引擎，采用B+树的存储结构

B+Tree是在BTree的基础之上做的一种优化，变化如下：

    1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快

    2、非叶子节点存储key，叶子节点存储key和数据

    3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高

注意：

1、InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键

2、如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录,叫做回表

注意：

在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

 

MyISAM存储引擎

采用B+树，但是叶子节点存放的不是行数据，因为MyISAM在磁盘存储时有三个文件，分别是表结构文件、索引文件、数据文件；索引和数据是分别存储的，所以在使用MyISAM存储引擎时，叶子节点存放的是指向数据文件在磁盘存放的物理位置，而非实际的数据库存储的行数据

 

红黑(Red-black)树        是一种自平衡二叉查找树，1972年由Rudolf Bayer发明，它与AVL树类似，都在插入和删除操作时能通过旋转操作保持二叉查找树的平衡，以便能获得高效的查找性能。它可以在 O(logn) 时间内做查找，插入和删除等操作。红黑树是2-3-4树的一种等同，但有些红黑树设定只能左边是红树，这种情况就是2-3树的一种等同了。对于AVL树来说，红黑树牺牲了部分平衡性以换取插入/删除操作时少量的旋转操作，整体来说性能要优于AVL树。 特点：        节点是红色或黑色。        根节点是黑色。        每个叶节点（NIL节点）是黑色的。        每个红色节点的两个子节点都为黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)        从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。        最长路径不超过最短路径的2倍

 

 

 

 

 

左旋： 逆时针旋转，父节点被自己的右孩子取代，而自己成为自己的左孩子

右旋：顺时针旋转，父节点被做孩子取代，而自己成为自己的右孩子

行专列：

 

分区表：

 

日志存储及作用

Redo log：innodb存储引擎的日志文件

    当发送数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新算是完成了，同时innodb引擎会在合适的时候将记录写入到磁盘中

    Redo log是固定大小的，循环写的过程

    有了Redo log，innodb保证了即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe

 

Undo log：innodb存储引擎日志

    

 

binlog：服务端的日志文件

    

数据更新的流程

  

 

两阶段提交：写入redo，prepare阶段；写入binlog，commit阶段；数据和日志分离

 

 

意向共享锁、意向排他锁、间隙锁、自增锁

innodb 读写锁

select lock in share mode 

select for update

相关点要多使用多练习