MySQL 调优

MySQL 调优基础知识

MySQL 架构

客户端---> 连接器----> 分析器 ---> 优化器 ---> 执行器 --->存储引擎

在 8.0 以前，连接器也会去 查询缓存 查询数据，但查询缓存里面的数据都是 key-value 的，命中率比较低，所以从 MySQL 8.0 开始，就取消了 查询缓存.

• 连接器: 负责客户端的连接，用户名密码验证，权限验证等

• 分析器：对输入的字符串进行识别，每个部分代表什么意思。识别表名，识别关键字，做语法分析。比如根据语法规则判断sql语句是否满足sql语法。

• 优化器：在具体执行sql之前，会在mysql内部创建数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，有多个索引的时候，使用哪个索引，当sql语句与多个表关联时，决定表的连接顺序。

• 执行器：按照sql优化器生成的执行计划，调用各个模块和存储引擎通信，并将结果返回。

• 存储引擎：数据如何鵆，用什么文件存储，存储什么格式。如MyISAM,InnoDB,Memory 等.

事务

ACID的特性:

• Atomicity 原子性: 一个事务里面可能会包含多个SQL语句，这些SQL语句提交上去，要么都成功，要么都失败。 试想下，银行转账的场景，A转给B 30 元，A的账号少了30，但B的记录因为某些原因没有更新上去，那这就出现了数据不一致情况。

• Consistency 一致性：

• Isolation 隔离性：

• Durability 持久性：

隔离级别

• Read uncommitted 读未提交

• Read committed 读提交

• Repeatable read 重复读

• Serialization 序列化

锁

读写锁

• 读锁/共享锁：多个线程可以同时读数据，互不影响，不会阻塞。

• 写锁/排他锁：有线程需要写数据的时候使用，写锁会阻塞其他进程。

锁的作用范围

• 表级锁: 表锁是MySQL基本的锁策略

• 行级锁: 行级锁不是直接锁记录，而是锁索引。如果sql语句用到了主键索引，那么就会锁住主键索引，如果sql语句操作的是非主键索引，那么mysql会先锁住非主键索引，然后再锁住主键索引。比如一个table中没有任何索引，那么当一个事务没有commit，另外一个事务是无法对整张表任何地方进行操作的。也就是说：没有索引的表，真正生效的是表锁，而不是行锁

• 死锁: 当多个事务试图以不同的顺序锁定资源，就有可能会出现死锁。InnoDB处理死锁的方法是将 最少行级别排他锁的事务进行回滚 (死锁回滚法)，也就是说，InnoDB引擎下，如果产生了锁，但影响的行数少，就会将这个事务进行回滚。 锁问题排查

• 查看哪些表被锁了: show OPEN TABLES where In_use > 0;

• 查看是哪些语句被锁了: show processlist;

• 找到哪个语句被锁了，并找到进程id，然后在mysql客户端中执行 kill $ID 即可杀掉进程

存储引擎

MySQL 语句

从 a 表里fileld_1 读出来，插入到b 表的 fileld_1里，同时 b 表有fileld_2是当前的时间，field_3为固定的"test"字符串

insert into b(field_1, field_2， field_3) select field_1, now(), "test" from a;