【MySQL】（九）数据库进阶——SQL执行原理、索引、explain、事务、统计日志

---

前言：
本文为在霍格沃兹测试开发学社中学习到的一些技术写出来分享给大家，希望有志同道合的小伙伴可以一起交流技术，一起进步~ 😘

1. Mysql中的SQL执行原理

1.1 SQL语句执行过程

mysql大体分为以下两部分：

1. Mysql server：涵盖mysql大多数核心服务功能，以及内置函数，还有所有跨库存储引擎的功能，比如存储过程、触发器、视图等。
2. 存储引擎：负责数据存储于提取，支持innodb、myisam等。最常用的存储引擎是innodb。

1.2 Server组件

1. 连接器：连接管理，权限验证

   注意：若管理员在用户连接过程中对用户进行权限更改，用户的权限会在下次连接时生效，本次连接不受影响。

2. 查询缓存：命中直接返回结果

   mysql拿到查询请求后会先去查询缓存中查看是否执行过此请求，如果查询在缓存中找到，则会将结果直接返回。

3. 分析器：语法分析

   如果请求没有命中查询缓存，则会开始进行请求分析。分析器去判断输入的SQL语句是否符合语法。

4. 优化器：生成执行计划，选择索引

   对语句进行执行优化。表中有多个索引时，决定用哪个索引；或者，当语句有多表关联时，决定多表的连接顺序

5. 执行器：操作引擎，返回结果

   首先判断用户是否有执行权限，若有，则打开表使用引擎执行语句，然后将结果返回；若没有，报错。

2 索引

2.1 索引定义

• 索引是存储的表中一个特定列的值数据结构。

  常用的索引数据结构：B-Tree，因为时间复杂度比较低，因此常用。

• 索引包含一个表中列的值，并且这些值存储在一个数据结构中。

• 索引本身很大，不会全部存储在内存中，一般以文件的形式存储在磁盘上。

2.2 索引分类

1. 单列索引：指索引中只包含一列，一个表中可包含多个单列索引。
   • 普通索引
   • 唯一索引：要求值都是唯一的，允许NULL值
   • 主键索引：不允许NULL值，主键建立的规则：int 优于 vchar
2. 组合索引：表中包含多个单列索引，那么它就是组合索引。
3. 全文索引：检索包含一个或多个给定单词的组合结构。

注意：一个表中创建的索引不要太多，最好不要大于五个。

2.3 索引优势

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

2.4 索引的劣势

• 占用空间。
• 降低更新表的速度。
• 需要花时间研究建立最优秀的索引，或者优化。

2.5 索引适合的场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 查询中排序的字段

3. explain

3.1 explain——执行计划

• 模拟优化器执行SQL查询语句
• 分析查询语句或是表结构的性能瓶颈

3.2 explain的使用

语法：explain +SQL语句

执行结果表：

• id：表示查询序列号，查询中操作表的顺序，id值越大，优先级越高；id相同则由上至下顺序执行。

• select_type：查询类型

• table：输出结果集

• type：访问类型，显示查询使用了哪种类型

• possible_key：mysql可以使用的索引，NULL说明无索引可用

• key：实际使用的索引

• key_len：使用索引的长度

-ref：索引中哪一列被使用

• rows：返回请求数据的行数

• Extra：其他额外参数

3.3 explain作用

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

4. 事务

4.1 事物的概念

• 数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。
• 事物由事务开始与事务结束之间执行的全部数据库操作组成。

注意：事务主要是用来处理操作量大，复杂度高的操作。mysql只有使用innodb数据库引擎才支持事务。

4.2 事务特点——ACID

• 原子性（Atomicity）

  一个事务中所有的操作要么全完成，要么不完成，不会出现处于中间某一个环节。事务执行过程发生错误则会回滚到初始状态。

• 一致性（Consistency）

  事务开始之前和事务结束之后，数据库的完整性不会被破坏。也就是说，写入的数据必须完全符合预设的规则，包含数据的精确度、串联性等。

• 隔离性（Isolation）

  • 因为数据库允许多个事务并发执行，隔离性是为了防止多事务并发执行时，由于交叉执行导致数据不一致的情况。
  • 事务隔离级别：
    • 读未提交
    • 读提交
    • 可重复读
    • 可序化

• 持久性（Durability）

  事务处理结束，对数据的修改结果是永久的，即时出现系统故障也不会丢失

4.3 事务操作

begin： 开始一个事务

rollback：事务回滚

commit：事务提交

5. 统计日志

5.1 日志分类

slow log：慢查询日志，超出预设的long_query_time阈值的SQL记录

general log：全局查询日志，所有SQL查询的记录

5.2 查看慢查询日志

1. 查看日志开关：show variables like '%query%';
2. 打开日志开关：set global show_query_log='ON';
3. 设置阈值：set long_query_time=0.01;
4. 执行SQL
5. 查看日志内容

注意：慢查询日志对数据库性能消耗较大，若非调优需要，一般不建议启用。

5.3 在表中查看慢查询日志

修改日志存放方式：set global log_output='table';

查看表中保存的日志内容：select * from mysql.slow_log;

5.4 查看全局查询日志

1. 查看变量开关：show variables like '%general%';
2. 打开日志开关：set global general_log='ON';
3. 执行SQL
4. 查看表中日志内容：select * from mysql.general_log;

---

文末说明：
接口测试中我们很容易混淆Session、cookie和token，你知道他们有什么区别吗？

快来跟我一起看，一篇文章让你了解三者的区别。😎
⬇⬇⬇⬇⬇⬇⬇
👍👍👍：接口测试经典面试题：Session、cookie、token有什么区别？