mysql之初识

基础部分

mysql架构

大体为：Server 层和存储引擎层两部分
server：连接器、查询缓存、分析器、优化器、执行器等，内置函数，及跨存储引擎的功能都在这里实现，例如存储过程、触发器、视图等。 binlog 属于server层备份日志。

存储引擎：架构模式为插件模式，支持InnoDB、MyISAM、Memory 等多个存储引擎。常用引擎为InnoDB，mysql5.5.5后成为默认存储引擎。显式指定：create table 语句中使用engine=memory。redo log 属于innoDB的。作用崩溃恢复

表类型

InnoDB

InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。

不同之处
1：InnoDB 表的数据总是有序存放的
2：当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值
3：数据位置发生变化的时候，InnoDB 表只需要修改主键索引
4：InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找
5：nnoDB 支持变长数据类型，不同记录的长度可能不同

Memory

Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
主键索引默认是 哈希索引。范围查询则会走全表扫描
可以给某个字段在设置其他的索引类型：

alter table t1 add index a_btree_index using btree (id);

不同之处
1：内存表的数据就是按照写入顺序存放的
2：当数据文件有空洞的时候，内存表找到空位就可以插入新值
3：数据位置发生变化的时候，存表需要修改所有索引
4：内存表所有索引的“地位”都相同
5：内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

内存表

内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
想创建内存表就必须指定格式为Memory 。

临时表

临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。
特点:
建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。
session A 创建的临时表 t，对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。show tables 命令不显示临时表。
多数使用场景：
join优化时建立一个临时表，增加临时表索引用于BKA算法。
分库分表场景下，查询条件字段不是分库键，在每张库上执行查询语句，然后汇总到某个从库上创建一个临时表，对这个临时表进行查询操作。

内存临时表默认为：MEMORY
MEMORY 引擎不是索引组织表。你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

磁盘临时表

tmp_table_size 参数配置 默认16MB
如果临时表大小超过 > tmp_table_size ，则使用磁盘临时表。
磁盘临时表的默认是InnoDB：internal_tmp_disk_storage_engine参数控制

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
cmd命令模式链接：mysql -h$ip-P$port -u$user -p （类似登录流程）
TCP握手，密码验证，链接成功后：查询当前账号拥有的所有权限赋予，在此链接内都将依赖于此时读到的拥有权限。（相当于链接成功后，管理员修改权限需等到下次链接后生效）
链接活跃时长默认为8H，可以通过参数wait_timeout设置。
建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
重点：这个链接在mysql执行中使用的临时内存是由当前链接管理，这些资源只有在链接断开时才会释放，长链接的长次累计导致内存占用越大，就会被系统强行杀掉，现象属于mysql异常重启。
解决方案：
1定期断开长连接，判断使用大查询后断开链接，之后查询重新链接。
2mysql5.7以后：通过执行mysql_reset_connection初始化链接资源，不需要做重连和权限校验操作，但是会将链接恢复到刚刚创建完成状态。
会产生如下影响：

1、会回滚所有活动事务，并重置自动提交模式；
2、会释放所有的锁表；
3、所有的临时表会被关闭并清除；
4、Session 系统变量会被重新初始化为相应的全局系统变量的值；
5、用户自定义变量会丢失；
6、会释放 Prepared statements；
7、HANDLER 变量会被关闭；
8、LAST_INSERT_ID() 函数的值会被重置为 0；
9、通过 GET_LOCK() 函数获得的锁会被释放；

查询缓存

判断查询缓存中是否存储， 缓存存储格式以 key-value格式存储，key是语句 value是查询结果。key与查询语句进行匹配。

建议：大多数情况下不使用，
原因：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
对于更新频繁的数据库来说：查询缓存的命中率非常低；
一张静态表或者系统配置表（大都更新频率低）可以适合使用查询缓存。
按需使用：query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。SQL_CACHE 显式指定

mysql> select SQL_CACHE * from T where ID=10；

8.0版本直接去除 缓存模块。

分析器

1、词法分析：对应的字符表名列名 翻译成对应的表名列名
2、语法分析：根据语法规则判断当前sql是否符合语法规范
根据语法树来解析当前执行语句。

优化器

根据条件关系，返回的字段，及条件的索引来选择最优的方式。

执行器

通过优化器后的语句开始执行，
查询任何表数据都会做权限验证是否有这张表的查询权限，查询缓存也一样
（有的语句只有在执行阶段才能确定时那张表，所以对表权限的校验是在执行器）

1、存储引擎层的各种数据获取方法都是已经定义好了的，是静态的方法；
2、优化器 生成的执行计划，决定了 执行器会选择 存储引擎的哪个方法去获取数据，而这也决定了一个操作是否能快速执行完成；
3、InnoDB 存储引擎层的优化措施还有好多，对执行器来说只是一个黑箱

查询语句执行过程

客户端—》链接器–》查询缓存–》分析器–》优化器–》执行器–》（存储引擎）

更新语句执行过程

客户端—》链接器–》分析器–》优化器–》执行器–》
（存储引擎）
{
写入内存
写入redo log
写入binlog
}

结构

Buffer Pool

change buffer

简述下change buffer：

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果不存在，不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

数据在内存中直接更新，不在内存的话change buffer记录更新操作，然后定时刷新（merge）到磁盘或者读取数据的时候更新读入内存的数据并刷新（merge），数据库正常shutdown时也会执行merge操作。
change buffer也是可以持久化的数据。

唯一索引更新：更新操作首先判断是否违反了唯一性约束，这个判断是需要将数据页读取到内存中才能判断，如果读取到内存中了直接更新内存更快反而没有必要使用change buffer了。
那么唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

change buffer 的使用场景

适用于普通索引的场景， 对于写多读少的场景使用效果最佳。 例如：日志等，记录。
对于写入后马上查询的：数据写入change buffer，然后查询触发merge操作，增加了 change buffer的维护代价。

analyze table 表名 重新统计索引信息

redo log

介绍：记录了innodb所有可能发生脏页的数据变动，例如：change buffer的变动， 内存数据的变动等。
因为自身落盘为顺序写，速度很快，所以被放入事务提交成功的先决条件。
作用：用户Innodb的崩溃恢复，意思为innodb非正常重启崩溃不可用时，在重新启动恢复后使数据能保持完整性不丢失。

binlog

statement 格式的 binlog
row 格式的 binlog
结合两种模式使用的格式。