mysql数据库事务日志_MYSQL（数据库索引、事务及各种日志）

一、索引

索引类型：

B+ TREE、HASH、B-TREE

聚簇(集)索引、非聚簇索引：数据和索引是否存储在一起

主键索引、二级(辅助)索引

稠密索引、稀疏索引：是否索引了每一个数据项

简单索引、组合索引

左前缀索引：取前面的字符做索引

覆盖索引：从索引中即可取出要查询的数据，性能高

二、并发控制

1、 加锁：  LOCK TABLES  tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...lock_type: READ ， WRITE

2、解锁：UNLOCK TABLES

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]关闭正在打开的表(清除查询缓存)，通常在备份前加全局读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]  查询时加写或读锁

show processlist 显示进程        kill ID 可关闭进程

三、事务(只针对DML语言)

1、启动事务：

BEGIN

BEGIN WORK

START TRANSACTION

2、 结束事务：

COMMIT：提交

ROLLBACK: 回滚

注意：只有事务型存储引擎中的DML语句方能支持此类操作

自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交

建议：显式请求和提交事务，而不要使用“自动提交”功能

事务支持保存点：savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

{面}事务的四个特性：

ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

{面}事务的隔离级别：从上至下更加严格(select @@tx_isolation  查询当前事务隔离级别 服务器端修改配置文件transaction-isolation=SERIALIZABLE)

READ UNCOMMITTED 可读取到未提交数据，产生脏读

READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致

REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置

SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

四、日志

1、事务日志

innodb_log_file_size 5242880 每个日志文件大小

innodb_log_files_in_group 2 日志组成员个数

innodb_log_group_home_dir ./ 事务文件路径

innodb_flush_log_at_trx_commit

说明：设置为1，同时sync_binlog = 1表示最高级别的容错     innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量

1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守            ACID特性

0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但      服务器崩溃可能丢失最后一秒的事务

2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或        停电可能导致最后一秒的交易丢失

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

slow_query_log=ON|OFF 开启或关闭慢查询

long_query_time=N 慢查询的阀值，单位秒

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 记录内容

profile(默认没有用)可显示指令运行慢的详细过程

3、二进制日志

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

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两      项都开启才可

binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT

max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为 1G

说明：文件达到上限时的大小未必为指定的精确值

sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

(2)二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

查看使用中的二进制日志文件

SHOW MASTER STATUS

查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3

(3)清除二进制日志

清除指定二进制日志：

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_expr }

示例：purge binary logs to 'mysql-bin.000016';清除16之前的二进制日志。

reset master ；二进制日志从头开始计数。flush logs ；可额外生成二进制日志。

注：optimize table testlog 对testlog表进行数据库整理，释放空白数据库占据的空间。