MySQL进阶

今天药忘吃喽~ 2023-09-27 17:24 146阅读 0赞

#### 文章目录 ####

*  一、存储引擎
 *   *  InnoDB
     *  MyISAM 与 Memory
     *  存储引擎对比
 *  二、索引
 *   *  索引结构
     *  Btree
     *  B+Tree
     *  Hash索引
     *  索引分类
     *  索引语法
     *  SQL性能分析

## 一、存储引擎 ##

MySQL体系结构：  
![在这里插入图片描述][4ec3205ca86c452aa7252ccae585307c.png]  
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称之为表类型。

我们来查询一下建表语句

show create table message;

![在这里插入图片描述][c51594fd47d8419fbd38d039b8694407.png]  
我们可以发现默认的存储引擎是InnoDB

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

show engines;

![在这里插入图片描述][f729f14c17184289bc1df26bbdb7b16f.png]  
创建表时，指定存储引擎  
![在这里插入图片描述][6949d26de78246bfa29ae986ae57db70.png]  
我们来试着创建张表，指定存储引擎为MyISAM  
![sh][]

### InnoDB ###

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎  
**特点：**  
1.DML操作遵循ACID模型，支持事务;  
2.行级锁，提高并发访问性能;  
3.支持外键FOREIGN KEY约束，保证数据的完整性和正确性

文件：  
xxx.ibd：xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表结构(frm,sdi)、数据和索引

逻辑存储结构：  
![在这里插入图片描述][8bc907a885604e2888d60f40f12d3771.png]  
一个区的大小为1M，一个页的大小为16K，一个区可以包含64个页

### MyISAM 与 Memory ###

MyISAM：是MySQL早期默认支持的存储引擎  
特点：  
1.不支持事务，不支持外键  
2.支持表锁，不支持行锁  
3.访问速度快  
文件：  
xxx.sdi：存储表结构信息  
xxx.MYD：存储数据  
xxx.MYI：存储索引

Memory：Memory引擎的表数据是在内存中的，由于收到硬件问题、断电问题的影响，这些表只能作为临时表或缓存使用  
特点：  
1.内存存放  
2.hash索引(默认)  
文件：  
xxx.sdi：存储表结构信息

### 存储引擎对比 ###

<table> 
 <thead> 
  <tr> 
   <th>特点</th> 
   <th>InnoDB</th> 
   <th>MyISAM</th> 
   <th>Memory</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储限制</td> 
   <td>64TB</td> 
   <td>有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>事务安全</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>锁机制</td> 
   <td>行锁</td> 
   <td>表锁</td> 
   <td>表锁</td> 
  </tr> 
  <tr> 
   <td>B+tree索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>Hash索引</td> 
   <td>-</td> 
   <td>-</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>5.6之后支持</td> 
   <td>支持</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>空间使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>N/A</td> 
  </tr> 
  <tr> 
   <td>内存使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>中等</td> 
  </tr> 
  <tr> 
   <td>批量插入</td> 
   <td>速度</td> 
   <td>低</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

存储引擎没有好坏之分，我们应该根据应用系统的特点选择合适的存储引擎，对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

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

**MyISAM：** 如果应用是以读和插入操作为主，很少的更新和删除，并且对事务的完整性、并发性要求不高，那么MyISAM比较合适

**Memory：** 将数据保存在内存中，访问速度快，常用于临时表及缓存。缺陷是对表的大小有限制，太大的表无法缓存在内存中，无法保障数据的安全性

## 二、索引 ##

索引(index)是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以实现高级查找算法，这种数据结构就是索引

select * from user where age = 45;

![在这里插入图片描述][baa190a6edce4b2eb0211614db38fd6e.png]  
我们无索引的情况下，虽然我们现在已经找到45的数据，但是还会向下扫描，因为不知道下面是否还有数据，这种我们称之为全表扫描  
![在这里插入图片描述][1cc6f7d1a6da453b9e39932720bd5e50.png]  
而我们有了索引之后，查询效率会大幅度提高，但我们实际并不是使用的这种数据结果

索引的优缺点：  
优点：  
1.提高数据检索的效率，降低数据库IO的成本  
2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：  
1.索引列要占用空间  
2.索引大大提高了查询效率，同时也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时效率降低

### 索引结构 ###

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要有以下几种：

<table> 
 <thead> 
  <tr> 
   <th>索引结构</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>B+Tree索引</td> 
   <td>最常见的索引类型，大部分存储引擎都支持B+树索引</td> 
  </tr> 
  <tr> 
   <td>Hash索引</td> 
   <td>底层使用哈希表实现，只有精确匹配索引列查询才有效，不知道范围查询</td> 
  </tr> 
  <tr> 
   <td>R-tree(空间索引)</td> 
   <td>空间索引是MyISAM引擎中的一个特殊索引类型，主要用于地理空间数据类型</td> 
  </tr> 
  <tr> 
   <td>Full-text(全文索引)</td> 
   <td>是一种建立倒排索引，快速匹配文档的方式</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>索引</th> 
   <th>InnoDB</th> 
   <th>MyISAM</th> 
   <th>Memory</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>B+Tree索引支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Hash索引</td> 
   <td>不支持</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>R-tree</td> 
   <td>不支持</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>Full-text</td> 
   <td>5.6后支持</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
 </tbody> 
</table>

我们日常所提及的索引，如果没有特殊指明，一般都是指B+树组织的索引

### Btree ###

![在这里插入图片描述][6d6d5d3e8b5b4e04973eb31f71f3b3df.png]  
二叉树：当我们的元素是顺序时，回形成一个链表，查询速度大大降低。数据量特别大的情况下，层级太深，检索速度慢  
红黑树：虽然不会出现链表的情况，但是数据量大的情况下，层级较深，检索速度慢

B-Tree(多路平衡查找树)：  
![在这里插入图片描述][86f48756724b4d0cbbfa2c2af0b831a0.png]

### B+Tree ###

![在这里插入图片描述][2b843db5887f4ac78903002d4aa66248.png]  
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能  
B+Tree与B-Tree的区别：  
1.非叶子节点只保存索引值，叶子节点保存了完整的索引和数据，因此它的查询时间固定为log(n)  
2.叶子节点有指向下一个叶子节点的指针，叶子节点类似有一个链表  
3.叶子节点保存了完整的数据以及有指针连接，B+树增加了区间访问性，提高了范围查询，而B树范围查询相对较差  
4.B+树更适合外部存储。因为非叶子不存储数据只存索引

对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，同样保存大量数据，只能增加树的高度，性能下降

### Hash索引 ###

哈希索引就是采用一定的哈希算法，将键值换算成新的hash值，映射到对应的位置，然后存储在hash表上  
![在这里插入图片描述][d8e376527894447faacabb03c3f41e9f.png]  
如果两个或多个键值，映射到了同一位置，他们就产生了hash冲突，也称之为hash碰撞，可用链表来解决

特点：  
1.Hash索引只能用于对等比较(=,in)，不支持范围查询(between，>，<…)  
2.无法利用索引进行排序  
3.查询效率高，通常只需要一次检索即可，效率高于B+Tree索引

存储引擎支持：  
在MySQL中，支持hash索引的是Memory，而InnoDB具有适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下构建的

### 索引分类 ###

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>含义</th> 
   <th>特点</th> 
   <th>关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键索引</td> 
   <td>针对表中主键创建的索引</td> 
   <td>默认自动创建，只有一个</td> 
   <td>PRIMARY</td> 
  </tr> 
  <tr> 
   <td>唯一索引</td> 
   <td>避免同一个表中某数据列中值重复</td> 
   <td>可以有多个</td> 
   <td>UNIQUE</td> 
  </tr> 
  <tr> 
   <td>常规索引</td> 
   <td>快速定位特定元素</td> 
   <td>可以有多个</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>全文索引查找的是文本中的关键词，而不是比较索引中的值</td> 
   <td>可以有多个</td> 
   <td>FULLTEXT</td> 
  </tr> 
 </tbody> 
</table>

在InnoDB存储引擎中，根据索引存储类型，可以分为以下两类：

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>含义</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>聚集索引</td> 
   <td>将索引与数据放一块，索引结构的叶子节点保存了行数据</td> 
   <td>必须有，而且只包含一个</td> 
  </tr> 
  <tr> 
   <td>二级索引</td> 
   <td>将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键</td> 
   <td>可以包含多个</td> 
  </tr> 
 </tbody> 
</table>

聚集索引规则：  
1.如果存在主键，主键索引就是聚集索引  
2.如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引  
3.如果没有主键，也没用合适的唯一索引，InnoDB会默认生成一个rowid作为隐藏的聚集索引

### 索引语法 ###

创建索引：

create [UNIQUE|FULLTEXT] index index_name on table_name(index_col_name,...);

我们的索引是可以加在单列或者多列的，也就是单列索引和多列索引

查看索引：

show index from table_name;

删除索引：

Drop index index_name on table_name;

### SQL性能分析 ###

**SQL执行频率**：MySQL客户端连接成功后，可执行show \[session|global\] status命令查看服务器状态信息，通过下述指令，可以查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次：

show [session|global] status 'Com_______';

![在这里插入图片描述][4f9b66c4426b4d6e9f4060ff2ed197b4.png]  
我们通过上述命令可以确定那些操作执行的次数比较多，但我们并不知道是那些具体的语句比较慢，我们就可以使用以下操作。

**慢查询日志**： 慢查询日志记录了所有执行时间超过指定参数(long\_query\_time,单位: 秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置以下信息：

#开启MySQL慢日志查询开关
    slow_query_log=1
    #设置慢日志时间为2s,SQL语句执行时间超过2s，就会被视为慢查询，进行记录
    slow_query_time=2

配置完毕以后需要重启MySQL服务器  
![在这里插入图片描述][d676f916e96a4b958ebee67298b82c16.png]  
**profile详情**: show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have\_profiling参数，能够看到当前MySQL是否支持profile操作:

select @@have_profiling;

![在这里插入图片描述][e6ed43f5f1cd42f0af385d23358c235a.png]  
默认我们的profiling是关闭的，我们通过set语句开启profiling  
![在这里插入图片描述][fd62d37e17954691b92c8b9251ca8e7e.png]

set [session|global] profiling = 1;

![在这里插入图片描述][dfba5496b0c648aca7381188539d5ad6.png]  
可以通过以下执行查看SQL的执行耗时

#查看每一条SQL的耗时基本情况
    show profiles;
    
    #查看指定query_id的SQL语句各阶段的耗时情况
    show profile for query query_id;
    
    #查看指定query_id的SQL语句CPU的使用情况
    show profile cpu for query query_id;

![在这里插入图片描述][fd48e6dc53b54524b3eb1566df1606b2.png]  
然后我们可以指定query\_id去查看详细信息

**explain执行计划**  
explain或者desc命令获取MySQL如何执行select语句的信息，包括select执行过程中表如何连接以及连接的顺序

#直接在select之前加上关键字explain/desc
    explain select 字段 from 表名 where 条件;

![在这里插入图片描述][bd41d3d60d694a2ea5144103256c1950.png]  
各字段含义：  
id：select查询的序列号，表示查询中执行select语句操作表的顺序(id不同值越大先执行，id相同，从上到下执行)

select\_type：表示select的类型，常见的有simple(简单表，不适应表连接或者子查询)、primary(主查询，外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(select/where之后包含了子查询)

type：表示连接类型，性能由好到差的连接类型：NULL、system、const、eq\_ref、ref、range、index、all

possible\_key：显示可能应用在这张表上的索引，一个或多个

key：实际用到的索引，如果为NULL，则没有用索引

key\_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，再不损失精度下，越短越好

rows：MySQL认为必须要执行查询的行数，在InnoDB引擎中，是一个估计值，可能不是准确的

filtered：表示返回结果的行数占需读取行数的百分比，fitered的值越大越好

[4ec3205ca86c452aa7252ccae585307c.png]: https://img-blog.csdnimg.cn/4ec3205ca86c452aa7252ccae585307c.png
[c51594fd47d8419fbd38d039b8694407.png]: https://img-blog.csdnimg.cn/c51594fd47d8419fbd38d039b8694407.png
[f729f14c17184289bc1df26bbdb7b16f.png]: https://img-blog.csdnimg.cn/f729f14c17184289bc1df26bbdb7b16f.png
[6949d26de78246bfa29ae986ae57db70.png]: https://img-blog.csdnimg.cn/6949d26de78246bfa29ae986ae57db70.png
[sh]: https://img-blog.csdnimg.cn/724cd3718fb3480287400e37f0f7afa1.png
[8bc907a885604e2888d60f40f12d3771.png]: https://img-blog.csdnimg.cn/8bc907a885604e2888d60f40f12d3771.png
[baa190a6edce4b2eb0211614db38fd6e.png]: https://img-blog.csdnimg.cn/baa190a6edce4b2eb0211614db38fd6e.png
[1cc6f7d1a6da453b9e39932720bd5e50.png]: https://img-blog.csdnimg.cn/1cc6f7d1a6da453b9e39932720bd5e50.png
[6d6d5d3e8b5b4e04973eb31f71f3b3df.png]: https://img-blog.csdnimg.cn/6d6d5d3e8b5b4e04973eb31f71f3b3df.png
[86f48756724b4d0cbbfa2c2af0b831a0.png]: https://img-blog.csdnimg.cn/86f48756724b4d0cbbfa2c2af0b831a0.png
[2b843db5887f4ac78903002d4aa66248.png]: https://img-blog.csdnimg.cn/2b843db5887f4ac78903002d4aa66248.png
[d8e376527894447faacabb03c3f41e9f.png]: https://img-blog.csdnimg.cn/d8e376527894447faacabb03c3f41e9f.png
[4f9b66c4426b4d6e9f4060ff2ed197b4.png]: https://img-blog.csdnimg.cn/4f9b66c4426b4d6e9f4060ff2ed197b4.png
[d676f916e96a4b958ebee67298b82c16.png]: https://img-blog.csdnimg.cn/d676f916e96a4b958ebee67298b82c16.png
[e6ed43f5f1cd42f0af385d23358c235a.png]: https://img-blog.csdnimg.cn/e6ed43f5f1cd42f0af385d23358c235a.png
[fd62d37e17954691b92c8b9251ca8e7e.png]: https://img-blog.csdnimg.cn/fd62d37e17954691b92c8b9251ca8e7e.png
[dfba5496b0c648aca7381188539d5ad6.png]: https://img-blog.csdnimg.cn/dfba5496b0c648aca7381188539d5ad6.png
[fd48e6dc53b54524b3eb1566df1606b2.png]: https://img-blog.csdnimg.cn/fd48e6dc53b54524b3eb1566df1606b2.png
[bd41d3d60d694a2ea5144103256c1950.png]: https://img-blog.csdnimg.cn/bd41d3d60d694a2ea5144103256c1950.png