第十八周作业

1、总结mysql常见的存储引擎以及特点。

 MyISAM 引擎特点：

        不支持事务

        表级锁定

        读写相互阻塞，写入不能读，读时不能写

        只缓存索引

        不支持外键约束

        不支持聚簇索引

        读取数据较快，占用资源较少

        不支持MVCC（多版本并发控制机制）高并发

        崩溃恢复性较差

        MySQL5.5.5 前默认的数据库引擎

        MyISAM 存储引擎适用场景

        只读（或者写较少）

        表较小（可以接受长时间进行修复操作）

InnoDB引擎特点：

        行级锁

        支持事务，适合处理大量短期事务

        读写阻塞与事务隔离级别相关

        可缓存数据和索引

        支持聚簇索引

        崩溃恢复性更好

        支持MVCC高并发

        从MySQL5.5后支持全文索引

        从MySQL5.5.5开始为默认的数据库引擎

       数据文件：

                所有InnoDB表的数据和索引放置于同一个表空间中，

                        数据文件：ibdata1, ibdata2,存放在datadir定义的目录下

                        表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下

                每个表单独使用一个表空间存储表的数据和索引

                        两类文件放在对应每个数据库独立目录中

                        数据文件(存储数据和索引)：tb_name.ibd

                        表格式定义：tb_name.frm

 2、 总结MySQL查询缓存优化总结   

        1）查询缓存中内存块的最小分配单位query_cache_min_res_unit ：

        2）查询缓存命中率 ：

        3）查询缓存内存使用率：

[mysqld]
query_cache_type=ON
query_cache_size=200M

3、MySQL日志各类总结

        1）事务日志:

              事务日志：transaction log

                        redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log

                        undo log：保存与执行的操作相反的操作,用于实现rollback事务型存储引擎自行管

                                          理和使用，建议和数据文件分开存放  

show variables like '%innodb_log%';
innodb_log_file_size   50331648 #每个日志文件大小
innodb_log_files_in_group  2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径
innodb_flush_log_at_trx_commit #默认为1

        事务日志 性能优化：

        

innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供
更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系
统或停电可能导致最后一秒的交易丢失

        2）错误日志：

        mysqld启动和关闭过程中输出的事件信息

        mysqld运行中产生的错误信息

         event scheduler运行一个event时产生的日志信息

        在主从复制架构中的从服务器上启动从服务器线程时产生的信息

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error'; 
+---------------+------------------------------+
| Variable_name | Value                       |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+

        3） 通用日志

        通用日志：记录对数据库的通用操作，包括:错误的SQL语句

        通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

        通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

        4）慢查询日志

        慢查询日志：记录执行查询时长超出指定时长的操作

        慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

        5）二进制日志(备份) 重要

        基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少 基于“行”记录：

        row，记录数据，日志量较大，更加安全，建议使用的格式    #使用次格式

        混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上 ）

        

        二进制日志文件的构成

        有两类文件

                1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如：                                         on.000001,mariadbbin.000002

                 2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

        二进制日志相关的服务器变量：

sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开
启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除