mysql
一、mysql文件分类
1、配置文件
/etc/my.cnf
2、日志文件
1）通用日志
一般情况下关闭的（{hostname}.log）
2）慢查询日志
做性能优化时使用，找出执行时间长的sql语句。
{hostname}-slow.log
默认关闭
3）错误日志
记录系统中发生的错误信息。可以开启。
4）二进制日志
bin-log
可以在mysql实现主从复制时使用。
3、数据文件
1)表结构定义文件
*.frm
2)表的数据存储文件
.ibd：InnoDB引擎存储数据的文件。其中包含数据和索引信息。
.MYD：MyIsam引擎数据存储文件。
.MYI：MyIsam引擎索引存储文件。
3)系统表空间文件
ibdata1：系统的元数据详细，系统的数据。
4)重做日志文件
ib_logfile0
ib_logfile1
保障数据不丢失的重要环节。
二、查看系统参数及状态
1、查看系统参数
show variables like '%xxxx%';
2、查看系统状态
show status like '%xxxx%';
三、mysql架构
1、server 层
1）连接器
维护和客户端的连接的连接池。
2）分析器
SQL语句。对SQL语句进行词法分析语法分析。
3）优化器
对分析结果进行优化。
4）执行器
把优化结果交给数据库引擎执行。
5）查询缓存
在内存中建立一个缓存系统。key-value形式
key：sql语句
value：对应结果集。
默认关闭状态，在msyql8中缓存模块已经删除了。
缓存的优点：
提高查询性能
缓存的缺点：
1.建立缓存，时间开销。
2.空间开销。
3.缓存维护的时间开销。
4.缓存的命中率可能不高。
当相同的sql语句反复执行时，可以使用缓存。
使用方法：
缓存是通过“query_cache_type”来控制。
mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)
开启缓存：
在/etc/my.cnf配置文件中设置“query_cache_type”的值
0：关闭缓存
1：开启缓存，默认缓存所有的select语句。“select sql_no_cache xxx”
2：可以有针对性的对SQL语句进行缓存。只缓存“select sql_cache xxx”
查看缓存的命中率：
show status like 'qcache_hits';
2、引擎层
查看mysql默认的引擎列表：
> show engines;
除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该选择InnoDB引擎。
引擎的选择：
不同的表可以使用不同的引擎，需要在创建表的时候指定使用何种引擎。
四、InnoDB引擎
1、磁盘结构
1）系统表空间
ibdata1文件
用户字典、双写缓冲区、修改缓冲区、回滚日志。
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
2）用户表空间
默认每个表都对应一个用户表空间，保存是表中的数据和索引信息。
innodb_file_per_table
on:默认
off：所有的数据都放到系统表空间中。
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
3）通用表空间
需要通过create tablespace语句创建的。一般不需要的。
4）临时表空间
当mysql使用到临时表时会自动创建。
5）回滚表空间
默认是在系统表空间中。
6）重做日志（redo log）
由一组文件组成：
ib_logfile0
ib_logfile1
采用循环写的模式使用这一组文件。
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2
2、用户表空间的存储格式
ibd文件->段->区->页（基础单位）->行（数据行）
MySQL中对数据的读写是以“页”为单位，每个数据页默认是16k。
行的大小取决于表结构的定义。
如果数据行定义超过16k，存储时需要使用多个数据页进行存储。（不建议）
3、内存结构
1）buffer poll 缓冲池
其中包含数据页和索引页。读写都是以页为单位。
2）修改缓冲区（插入缓冲区）
其中保存的是辅助索引（非主键索引）的缓冲区。当修改数据时，对应的辅助索引也修改对应的修改。
先对辅助索引的修改放到缓冲区，然后慢慢的处理。
3）自适应hash索引
由InnoDB自己来维护的一个hash索引，用户无法干预。
4）log buffer
redo log的缓冲区。
4、InnoDB数据更新流程
1）查看要更新的数据页是否在buffer pool中存在。
如果不存在从磁盘上读取数据页，放到buffer pool 中。
如果存在就直接修改对应的数据页。
2）在修改数据页之前，应该先生成redo log，把redo log的写入缓冲区。
3）当用户commit时，需要把redo log buffer中的数据写入 redo log 文件中。
4）如果redo log 写入成功代表commit成功。
5）如果 redo log 写入失败，需要rollback操作。
redo log 文件顺序写，效率比随机写效率高。
如果系统突然崩了，可以根据redo log 中的日志信息，恢复数据。redo log中记录的是数据页中的什么位置修改什么内容。
redo log 也就是保证数据完整性的重要环节。
脏页：修改buffer pool中的数据页后，造成内存中的数据页和磁盘上的数据页内容不一致，形成脏页。脏页指的是内存中数据页。
buffer pool 采用LRU算法进行数据页的调度。
5、脏页落盘的时机
采用CheckPoint检查点机制
1）sharp checkpoint：强制落盘。把内存中所有的脏页都执行落盘操作。关闭数据库之前执行。
2）fuzzy checkpoint：模糊落盘。把内存中一部分脏页执行落盘操作。
1、Master Thread Checkpoint
主线程定时将脏页写入磁盘。每秒或者每10秒一次的频率。
2、FLUSH_LRU_LIST Checkpoint
buffer pool有脏页换出，需要执行checkpoint。
3、Async/Sync Flush Checkpoint
redo log快写满的时候执行checkpoint。
当redo log 文件占用空间超过75%小于90%，会执行异步落盘操作。不会阻塞写操作。
当redo log 文件占用空间超过90%，会执行同步异步落盘操作。会阻塞写操作。
4、Dirty Page too much Checkpoint
buffer pool中脏页太多，如果脏页率超过75%执行checkpoint。
6、双写落盘
1、把写入的数据页放到内存的双写缓冲区（2m）
2、把内存中缓冲区的数据页写入系统表空间的双写缓冲区。
3、把内存中缓冲区的数据页写入用户表空间。
崩溃恢复：
1）如果系统表空间数据页写坏。可以根据用户表空间的原始数据页+redolog 恢复数据。
2）如果用户表空间数据页写坏。可以使用系统表空间中最新的数据页恢复数据。
7、redo log落盘控制参数
innodb_flush_log_at_trx_commit=1
当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入每秒写入一次；
当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；
当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。
