mysql事务与引擎

一 事务

事务的概念
●事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
●事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
●事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
●事务通过事务的整体性以保证数据的一致性。
●事务能够提高在向表中更新和插入信息期间的可靠性。

1.1 acid的特性

  原子性 整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
  一致性 数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律
  隔离性 一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
  持久性  一旦事务提交，其所做的修改会永久保存于数据库中

1.2 隔离级别

脏读 ：一个事务读取了其他事务未提交的数据。
不可重复读：一个事务每次查询的数据不同。
幻读: 一个事务多次查询的结果一致，即使其他事务修改了数据，也只能读取最初的事务。
串读：一个事务数据未提交影响了其他事务的读取。

二 存储引擎

2.1 MyISAM引擎特点

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差

2.2 innodb引擎

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

2.3管理存储引擎

      查看mysql支持的存储引擎
      show engines;
      show table status from mysql\G ;

2.4 查看当前默认的存储引擎

  show variables like '%storage_engine%';

2.5 设置默认的存储引擎

   vim /etc/my.cnf
  [mysqld]
  default_storage_engine = InnoDB

2.6查看库中所有表使用的存储引擎

show table status from db_name;

2.7 查看库中指定表的存储引擎

 show table status like  'tb_name';
    show create table tb_name;

2.8 设置表的存储引擎

   CREATE TABLE tb_name(... ) ENGINE=InnoDB;
      ALTER TABLE tb_name ENGINE=InnoDB;

三 日志

3.1 事务日志：transaction log

• redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
• undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

3.2 错误日志

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

 SHOW GLOBAL VARIABLES LIKE 'log_error' ;
 yum  安装
 cat /var/log/mysqld.log

3.3 通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置

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

3.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 版后已删除


set  global slow_query_log=1;
#开启
set long_query_time=1;


select sleep(10)

3.5 二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放

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

sql_log_bin=ON|OFF：
#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=mysql-bin      默认是关闭
#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：
#二进制日志记录的格式，mariadb5.5默认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，即不自动删除

3.51 在线查看 二进制

 SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
 例子：
 show binlog events in 'mysql-bin.000001' from 6516 limit 2,3