JavaWeb数据库篇之MySQL特性

MySQL Explain
EXPLAIN 命令的输出内容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
各列的含义如下:

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息(using filesort 需要排序，需优化；using temporary 使用了临时表，需优化 ；using index 使用了索引)

1
2
3
4
5
6
7
8
9
10
11
12
extra释义：

• Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
• Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
• Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
• Impossible where：这个值强调了where语句会导致没有符合条件的行。
• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

1
2
3
4
5
6
7
总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

参考：
MySQL Explain详解
MySQL 性能优化神器 Explain 使用分析

MySQL日志(binlog、redo、undo)
Redo与Undo日志
Redo：是先记录操作日志，虽然记录操作日志也是应用了缓存（innodb_log_buffer）但是它还是比数据更新之前更新redo日志到磁盘日志文件中，数据的更新会在后面的线程刷新操作过程中被更新，redo操作事务提交后只有日志被持久化数据暂时未被持久化。
Undo:undo操作也使用了缓存，只是它在事务提交的时候会同时将数据和日志更新到磁盘，这步操作就是和redo的主要区别，并且该操作对磁盘IO的消耗非常大，所以undo操作保证了事务的原子性，事务一旦提交数据也被持久化了。

InnoDB存储引擎的恢复机制：

必须要将Undo Log持久化，而且必须要在写Redo Log之前将对应的Undo Log写入磁盘。
Undo和Redo Log的这种关联，使得持久化变得复杂起来。为了降低复杂度，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来，而不用在redo log之前写入磁盘了。
参考：
MySQL redo与undo
MySQL-重做日志 redo log -原理

Redo、Undo、Binlog日志
Binlog
binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)，在mysql主从复制中就是依靠的binlog。在mysql中开启binlog需要设置my.cnf中的log_bin参数，另外也可以通过binlog_do_db
指定要记录binlog的数据库和binlog_ignore_db指定不记录binlog的数据库。对运行中的mysql要启用binlog可以通过命令SET SQL_LOG_BIN=1来设置。设置完成，我们就可以来测试binlog了。

区别
innodb引擎中的redo/undo log与mysql binlog是完全不同的日志，它们主要有以下几个区别：

a）层次不同。redo/undo log是innodb层维护的，而binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。innodb的redo/undo log更详细的说明可以参见姜承尧的《mysql技术内幕-innodb存储引擎》一书中相关章节。
b）记录内容不同。redo/undo日志记录的是每个页的修改情况，属于物理日志+逻辑日志结合的方式（redo log物理到页，页内采用逻辑日志，undo log采用的是逻辑日志），目的是保证数据的一致性。binlog记录的都是事务操作内容，比如一条语句DELETE FROM TABLE WHERE i > 1之类的，不管采用的是什么引擎，当然格式是二进制的，要解析日志内容可以用这个命令mysqlbinlog -vv BINLOG。
c）记录时机不同。redo/undo日志在事务执行过程中会不断的写入;而binlog仅仅在事务提交后才写入到日志，之前描述有误，binlog是在事务最终commit前写入的，多谢anti-semicolon 指出。当然，binlog什么时候刷新到磁盘跟参数sync_binlog相关。
显然，我们执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。
binlog刷新到磁盘的时机跟sync_binlog参数相关，如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新，而如果设置为不为0的值则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响，一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。
通过命令SHOW MASTER LOGS可以看到当前的binlog数目。如下面就是我机器上的mysql的binlog情况，第一列是binlog文件名，第二列是binlog文件大小。可以通过设置expire_logs_days来指定binlog保留时间，要手动清理binlog可以通过指定binlog名字或者指定保留的日期，命令分别是:purge master logs to BINLOGNAME;和purge master logs before DATE;。
......
| mysql-bin.000018 | 515 |
| mysql-bin.000019 | 504 |
| mysql-bin.000020 | 107 |
+------------------+-----------+
1
2
3
4
5
参考：
binlog、redo、undo日志对比：http://www.php.cn/mysql-tutorials-361643.html

数据库事务
概述
基本概念
是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

事务的四个特性：ACID
原子性：事务必须是原子工作单元，对于其数据修改，要么全部执行，要么全部不执行。MySQL通过 Redo日志保证原子性。
一致性：指一个事务在执行之前和之后数据库都必须处于一致性状态。而数据库的一致性，指的是数据库的状态满足所有的完整性约束。MySQL Redo Undo日志保证一致性。
隔离性：由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查看数据时数据所处的状态，到底是另一个事务执行之前的状态还是中间某个状态，相互之间存在什么影响，是可以通过隔离级别的设置来控制的。
持久性：事务结束后，事务处理的结果必须能够得到固化，即写入数据库文件中，即使机器宕机数据也不会丢失，它对于系统的影响是永久性的。
隔离的四种级别
读未提交(Read Uncommitted)：即使一个事务的更新语句没有提交，别的事务也可以读到，没有安全性，几乎不使用。
读已提交(Read Committed)：一个事务只能看到其他事务已经提交的更新(Oracle,Sqlserver等数据库的默认隔离级别)。可防止脏读(一个事务读取了另一个事务修改了但是未提交的数据。举个例子，事务一更新了count=101，但是没有提交，事务二此时读取count，值为101而不是100，然后事务一出于某种原因回滚了，然后第二个事务读取的这个值就是噩梦的开始)。(依赖MVCC实现)
可重复读(Repeatable Read)：一个事务中进行了两次或多次同样的对于数据结果的查询，得到的结果是一样的，但不保证对于数据条数的查询是一样的，只要存在读改行数据就禁止写，消除了不可重复读(MySQL数据库的默认隔离级别)。可防止不可重复读(一个事务对同一行数据执行了两次或更多次查询，但是却得到了不同的结果，也就是在一个事务里面你不能重复（即多次）读取一行数据，如果你这么做了，不能保证每次读取的结果是一样的，有可能一样有可能不一样。造成这个结果是在两次查询之间有别的事务对该行数据做了更新操作。举个例子，事务一先查询了count，值为100，此时事务二更新了count=101，事务一再次读取count,值就会变成101，两次读取结果不一样)。(依赖MVCC实现)
串行化(Serializable)：这个事务执行的时候不允许别的事务并发执行.完全串行化的读，只要存在读就禁止写,但可以同时读，消除了幻读。这是事务隔离的最高级别，虽然最安全最省心，但是效率太低，一般不会用。可防止幻读(幻读和不可重复读有点像，只是针对的不是数据的值而是数据的数量。此种异常是一个事务在两次查询的过程中数据的数量不同，让人以为发生幻觉，幻读大概就是这么得来的吧。举个例子，事务一查询order表有多少条记录，事务二新增了一条记录，然后事务一查了一下order表有多少记录，发现和第一次不一样，这就是幻读)。
数据库锁
一般可以分为两类，一个是悲观锁，一个是乐观锁，悲观锁一般就是我们通常说的数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制。

悲观锁，它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。
乐观锁，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现。既然都有数据库提供的悲观锁可以方便使用为什么要使用乐观锁呢？对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。
乐观锁实现方式大概包括：

版本号（记为version）：就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version=”old version”这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。
时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
待更新字段：和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。
所有字段：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。
---------------------