mysql高级部分

超、凢脫俗 2022-03-08 13:36 149阅读 0赞

## mysql逻辑架构 ##

![图片描述][3205009103-5c554c439b4a8_articlex]

### 连接层 ###

> 最上层是一些客户端和连接服务，包含本地scoket同学和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信，主要完成一些类似连接处理，授权认证，及相关的安全方案，在该层引入线程池的概念，为通过认证安全接入的客户端提供线程，同样在该层上可以实现基于SSL的安全链接，服务器也会为安全接入的每个客户端验证它所具备的操作权限。

### 服务层 ###

> 第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存查询，SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程，函数等，在该层，服务层会解析查询并创建相应的内部解析树，并对其完成响应的优化确认查询表的顺序，是否利用索引等，最后生成相应的执行操作，如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样就解决大量读操作的环境中能够很好的提供系统性能。

### 存储引擎层 ###

> 存储引擎层，存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取，例如：MYISAM和InnoDB。

### 数据存储层 ###

> 主要将数据存储在运行于裸设备的文件系统之上，并完成存储引擎的交互(文件系统)。

## MyISAM和InnoDB对比 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">功能点</th> 
   <th align="center">MyISAM</th> 
   <th align="center">InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">主外键</td> 
   <td align="center">不支持</td> 
   <td align="center">支持</td> 
  </tr> 
  <tr> 
   <td align="center">事务</td> 
   <td align="center">不支持</td> 
   <td align="center">支持</td> 
  </tr> 
  <tr> 
   <td align="center">行表锁</td> 
   <td align="center">表锁，操作一条记录也会锁住整个表，不支持高并发</td> 
   <td align="center">行锁，操作一条记录只会锁住一行，适合高并发</td> 
  </tr> 
  <tr> 
   <td align="center">缓存</td> 
   <td align="center">只缓存索引，不缓存数据</td> 
   <td align="center">缓存索引也缓存数据，对内存要求高，内存大小直接影响性能</td> 
  </tr> 
  <tr> 
   <td align="center">表空间</td> 
   <td align="center">小</td> 
   <td align="center">大</td> 
  </tr> 
  <tr> 
   <td align="center">关注点</td> 
   <td align="center">性能</td> 
   <td align="center">事务</td> 
  </tr> 
  <tr> 
   <td align="center">默认安装</td> 
   <td align="center">是</td> 
   <td align="center">是</td> 
  </tr> 
 </tbody> 
</table>

## sql慢的原因 ##

1.  查询语句效率低
2.  索引失效或者没建索引
3.  join太多
4.  服务器参数设置

## 索引 ##

### 什么是索引 ###

> 索引是一种数据结构，提高查询的效率  
> 索引会将数据排序，从而能够快速查找  
> 数据库除了有数据以外，还维护着一个满足特定查找算法的数据结构，这种数据结构以某种方式指向数据，这样就可以利用这种数据结构高效地查找数据，这种数据结构就是索引。

### 索引的优点 ###

1.  提高查询效率，降低IO成本
2.  对列进行排序，降低排序成本

### 索引的缺点 ###

1.  索引也是一张表，保存了键值对，并指向实际数据，索引列也需要占用空间。
2.  虽然索引能够提升查询的效率，但是会降低更新的效率，比如insert，update，delete，因为更新表时，mysql不仅要保存新的数据，还要更新索引列的信息。

### 索引分类 ###

1.  单值索引
2.  唯一索引
3.  复合索引

### 哪些情况可以建索引 ###

1.  主键自动建立唯一索引
2.  频繁作为查询条件的字段要建立索引
3.  与其他表关联的字段，外键字段建立索引
4.  查询中的排序字段
5.  查询中统计或分组的字段

### 哪些情况不要建索引 ###

1.  表数据很少（300万一下的）
2.  经常增删改的字段
3.  数据重复且平均的字段，这种字段建索引没有意义

## explain ##

> explain可以模拟优化器执行sql语句，从而可以了解sql语句执行的内部原理，以此来分析性能瓶颈。

### 使用方法 ###

mysql> explain sql_sentence;

### 作用 ###

1.  表的读取顺序
2.  数据库读取的操作类型
3.  哪些索引可以被使用
4.  哪些索引实际被使用
5.  表之间的引用
6.  每张表有多少行被优化器查询

### 输出信息 ###

#### id ####

> 表的读取顺序

1.  id相同：自上而下执行语句
2.  id不同：id越大，优先级越高，越先执行
3.  id有相同的也有不同的：先执行id最大的第一个，然后平级的顺序执行，再执行小一级id的语句

#### select\_type ####

> 查询的类型

1.  simple：简单的select查询，查询中不含子查询或union
2.  primary：查询中包含任何复杂的子查询，则最外层的查询是此类型
3.  subquery：查询中包含子查询
4.  derived：临时衍生表
5.  union：第二个select出现在union之后
6.  union result：union之后的结果

#### table ####

> 数据对应的表

#### type ####

> 显示查询使用了哪种类型

1.  system：表中只有一行数据，是const类型的特例
2.  const：通过索引一次就找到了，通过主键或唯一索引与常量值比较只匹配一行数据，用于单表查询
3.  eq\_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，用于多表联查
4.  ref：非唯一索引扫描，返回所有匹配的行
5.  range：检索给定范围的行，比如where中的in、between、>、<
6.  index：只遍历索引树，用于获取表的所有索引字段的数据，select id from table;
7.  all：全表扫描，从磁盘中读取数据，百万级数据查询出现all则需要优化

#### possible\_key ####

> 显示可能用到的索引值，但不一定会被实际用到

#### key ####

> 实际使用到的索引，如果为空则没有使用或者根本没有索引

#### key\_len ####

> 索引中使用的字节数，长度越短越好  
> 它表示索引字段的最大可能长度，而不是实际长度

#### ref ####

> 显示那一列索引被用到了，可能是const或者某一列

#### rows ####

> 大致估算找到所需信息需要读取的行数

#### extra ####

> 额外信息

1.  using filesort：mysql对数据进行重新的排序，无法利用索引进行的排序叫文件排序
2.  using temporary：排序时创建了临时表
3.  using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据，如果出现了using where表示索引列被用来查找特定行，如果没出现则说明索引列只用来提取数据

## 索引优化 ##

### 防止索引失效 ###

1.  最佳左前缀法则：如果索引了多个列，查询需要从索引的最前列开始，且不能跳过索引中的列
2.  不要在索引列上做任何操作，比如各种函数操作，不然会导致索引失效并转向全表扫描
3.  在where中使用了范围，则范围右边的列失效
4.  尽量使用覆盖索引（索引列和查询列顺序一致），少用select \*
5.  在使用不等于（!=或者<>）时，无法使用索引，使用的是全表扫描
6.  is null和is not null无法使用索引
7.  like以通配符开头的（like '%xyz'），索引会失效，变成全表扫描
8.  隐式类型转换会导致索引失效
9.  少用or

## 慢查询 ##

> 在/var/lib/mysql有log文件  
> 使用mysqldumpslow查看日志

[3205009103-5c554c439b4a8_articlex]: /images/20220308/d9f89a68dde74c809adf7d2f134b9c3b.png