mysql底层架构和mysql优化

mysql底层架构和mysql优化

mysql底层架构主要分为以下几个重要节点

client:客户端，例如JDBC,navicat

server:服务端，mysql服务端，主要是提供mysql服务，服务端又分为几个重要的部分，

连接器：连接客户端和服务端，其中包括连接池，避免资源的浪费

分析器：分析sql数据有哪些组成部分

优化器：优化sql语句和执行顺序，CBO(基于成本的优化)，RBO（基于规则的优化）

执行器：用于执行基本操作，与存储引擎进行交互

存储引擎：用于存储数据的，主要有innodb和mysiam等常用的存储引擎

整体的执行过程：

用户使用客户端进行连接，mysql服务端连接器管理连接验证，连接器通过之后，分析器会分析sql语句拆解，分析器进行语法分析和词汇分析，接下来优化器会对sql进行优化，优化器优化sql，最后执行器执行相应的结果与存储引擎进行io数据交互。

接下来重点讲一下存储引擎这块

存储引擎目前用的比较多的就是innodb和myisam

他们主要区别如下

1.innodb索引是聚簇索引，叶子节点直接存放数据。myisam是非聚簇索引，叶子节点直接存放数据的存放地址（就是数据存储的时候会有个地址，这里存储的是地址）

聚簇索引：存储的时候数据是和主键一起存储的

聚簇索引：存储的时候数据是和主键不是一起存储的

2.innodb是支持事务的，myisam是不支持事务的

3.innodb是支持行级锁和表级锁的，myisam只支持表级别锁

4.innodb支持外键，mysiam不支持

5.innodb在5.6版本后支持全文索引

索引的存储结构是使用B+树的，为什么使用B+树

我们先了解一下什么是查找树，查找树的特性是，左边节点的值永远比右边节点的值要小

这样存储数据的好处在于方便我们查询数据，只要对比根节点的大小，就知道我们需要的数据是在根节点的左边还是右边，循环向下查找

但是这种结果会出现问题，例如下图

导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。

为了解决这个问题，出现了个平衡树

什么是平衡树？

平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1。 

但是平衡二叉树在数据越来越多的情况下，树的深度会越来越多深，那这时候查找效率会越来越慢

为了解决树深度的问题，出现了个B树的概念

什么是B树呢？

单个节点可以存储多个键值和数据的平衡树。也就是我接下来要说的 B 树

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低

mysql在这个基础上做了个更加复杂的操作，构建了个B+树

mysql的B+树有以下几个特别，

1.根节点之间的连接是双向链表，这一结构很方便不同根节点之前的数据查找，而叶子节点之前数据是单项链表，查找数据的时候，按顺序向下取数即可。

2.根节点只存储key（主键），为什么呢，因为mysql每次与磁盘进行IO操作的时候，是以块的形式去取数据的。每个块的固定大小是16KB，mysql在取节点数据的时候，应当尽量减少IO操作，这样就可以减少性能消耗。把尽可能多的主键存储在这个16KB块的时候，我们IO操作就少了，找到相应的根节点，然后就去叶子节点获取我们需要的数据了

3.B+树的根节点是主键(key)是有序存储的，这个主键是怎么生成的呢？有这么几个规则：

3.1 如果表里面有有主键，那么这个key就是主键

3.2如果这个表没有主键，那么这个key就是唯一索引

3.3如果这个表没有主键，没有唯一索引，那么key就是6字节的rowid

备注：建议不要使用uuid作为主键，为什么呢？因为是无序的，这样导致B+树底层存储结构有问题，建议使用雪花算法生成主键，自定义id生成有序主键

4.B+树索存储结构:主键索引：聚簇索引，其他索引：非聚簇索引（普通索引，复合索引，唯一索引，全文索引）

存储引擎的底层结构就写到这里了。

二接下来我会写mysql优化相关的东西

主要分为3点：索引优化，sql优化和表优化

1.索引优化

索引分为：普通索引,唯一索引,联合索引,主键索引,还有个全文索引，全文索引主要作用是在大字段的值里面，取某段字，fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用

优化方案

1.使用索引的不要为null

2.使用短索引

3.经常出现在where语句后面的字段加索引

4.使用where和order by的查询字段加联合索引

5.like %不要使用双%，会直接导致索引失效

6.尽量不要在列上使用函数操作

2.sql优化

开启sql慢查询监控

slow_query_log设置为on

这个配置开启后会记录查询语句的查询时间查过域值的sql语句

2.ong_query_time查询时间超过的域值，建议设置为1

3.slow_query_log_file慢查询sql记录文件

4.query_not_using_indexes记录没用使用索引的语句

优化方案

2.1查询的时候不要直接使用 select *,尽量把字段列出来

2.2大部分情况下连接查询会比子查询要快

2.3多使用explain分析查询语句

2.4优化上面开启配置记录的sql语句

2.5多表查询的时候，使用小表驱动大表，即小表join大表

2.6对于经常使用的sql语句开启缓存

3.表结构优化

3.1字段上尽量使用not null

3.2字段固定长度效率会更快

3.3大表拆分，分为垂直拆分和水平拆分

垂直拆分即把一个大字段拆分成多个字段

水平拆分即按数据的标识或时间戳把数据拆分存储多个表。表结构一模一样

下面有几个知识点，大家感兴趣的可以去了解一下

事务

隔离级别

事务传播机制

原子性：undo log,保存的是跟执行操作相反的操作

一致性

隔离性：锁

持久性:：redo log

mysql机制