MySQL杂谈

好记性，不如烂笔头！！！ 统一记录日常了解到的关于MySQL性能问题

explain详情

每次用explain的时候，总要搜一下各个字段的含义，烦死.

explain来分析sql语句的执行行为，主要输出几个字段:

1. id: 选择标识符
2. select_type: 查询类型，你这个查询时简单查询SIMPLE? 还是联合查询Union?
   • Simple：简单查询
   • Primary: 子查询中最外层查询的类型为Primary
   • Union: Union语句中后一个select
   • Subquery: 子查询
3. table: 表
4. type: 访问表的方式 比较重要
   • ALL: 全表扫描
   • index: 也是全表扫描，但是只扫描索引树
   • range: 使用索引，检索某些范围的行
   • ref: 哪些列或常量用于查找索引列上的值 （不太懂）
   • eq_ref
   • const: 查询转换为常量（不太懂，大致意思就是当使用主键索引在where条件中时，使用CONST）
   • system: 和const一样，是表中只有一行数据时的特例
   • NULL
5. possible keys: 可能用到的索引
6. key: 实际用到的索引
7. key length: 索引长度（索引中各个字段的最大可能长度）
8. ref: 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 (不太懂)
9. rows: 扫描的行数（估算值）比较重要
10. extra: 其他的说明信息
    • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
    • Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
    • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
    • Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
    • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

Mysql执行过程

连接器

连接器负责与客户端建立连接、获取权限、维持和管理连接

1. 验证身份。在客户端与服务端TCP握手之后，连接器进行用户名和密码验证，验证失败报错Access denied for user
2. 权限判断。身份验证后，连接器到权限表中查看用户权限，此后该连接里执行的sql语句的权限判断，都依赖此时读到的权限
3. 断开连接。MySQL通过wait_timeout管理连接存在的空闲时长(show processlist中的sleep状态)，到达这些空闲时间的连接会自动断开，此时客户端如果仍然使用此连接，会报错MySQL has gone away

查询缓存

MySQL将已经执行过的语句放入内存缓存（可配置开关）。缓存方式为key-value， key为整条执行的语句，value为该语句的执行结果

1. 如果查询直接命中缓存，则直接返回结果，不走后面的流程
2. 如果未命中缓存，则需要再走后面的流程，然后更新内存缓存

值得注意的是，MySQL对内存缓存的管理，是以表为维度的，也就是，只要这张表存在更新操作，那么这个表的所有缓存都会失效。

因此，如果存在大量更新操作的数据表，命中缓存的概率很低，而且会增加系统开销（建立缓存）。此时，应该关闭缓存。

MySQL 8.0之后取消了缓存功能

分析器

分析器主要进行SQL语句的词法分析，校验SQL语句是否合法

优化器

在分析完SQL语法之后，优化器会选择合适的查询方案，如:

1. 多个索引时，选用哪个索引
2. 使用连表查询时，哪个表先查，哪个表后查

等等。

执行器

通过分析器，MySQL知道要做什么；通过优化器，知道要怎么做；接下来，就由执行器具体执行“怎么做”；

执行器通过一次又一次调用引擎接口，获取数据，判断是否满足，再下一次。

慢日志中的row_examined就是执行器调用引擎接口的次数

Change Buffer

change buffer是什么？

change buffer是一段内存缓存区，当执行insert/update操作时，如果涉及到的数据page不在内存中，那么MySQL会把这条语句存到change buffer中即返回操作成功。

等到下一个查询语句需要访问这个数据page时，再把这个数据页加载到内存，执行change buffer中和该page相关的变更操作(称为**merge**)。另外，MySQL也会异步定时执行merge

优势

通过change buffer机制，可以在变更操作时，减少一次数据磁盘的查询和加载，提高更新操作的性能

同时，通过merge功能，可以保证数据查询时的准确性

使用场景

对于写多读少的应用场景，使用change buffer时可以提高写性能，而读操作相对少，merge操作不会很频繁，因此此时比较适合change buffer的场景

相反，对于写少读多的应用场景，如果每次写操作之后都有对应的读，那么数据页的定位和加载次数并没有显著提高，然而维护change buffer也需要额外的代价，这种情况可能就不是那么合适了

以上只是两个极端的场景，真正使用中可以根据实际情况判断如何使用

change buffer和redo log

redo log是MySQL数据更新操作优化的核心特性之一，通过WAL(先写日志，再写数据)的机制，将数据更新中的随机写磁盘的IO变更为顺序写

change buffer是减少了数据更新中的随机读磁盘的IO