连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销魂线程。

优化与执行

MySQL会解析查询，并 创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示优化器，影响它的决策过程。也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

读写锁

读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取统一资源，而互不干扰。

写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

表锁

MySQL最基本的锁策略，并且是开销最小的策略。

一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。

行级锁

最大程度地支持并发处理，同时也带来了最大的锁开销。

死锁

InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

死锁发生以后，只有部分或者完全回滚其中一个事务。

事务日志

使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘上的一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方法相对来说要快得多。

事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到硬盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

MySQL的事务

MySQL默认采用自动提交模式。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。

多版本并发控制

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容。

虚拟机把描述类的数据从Class文件加载到内存，并堆数据进行校验、转换解析和初始化，最终形成可以被虚拟机直接使用的Java类型，这就是虚拟机的类加载机制。

类加载的时机

类从被加载到虚拟机内存中开始，到卸载出内存为止，它的整个生命周期包括了：加载、验证、准备、解析、初始化、使用和卸载七个阶段。其中验证、准备和解析三个部分统称为连接。

虚拟机规范严格规定了有且只有四种情况必须立即对类进行初始化：

• 遇到new、getstatic、putstatic和invokestatic这4条字节码指令时，如果类没有进行过初始化，则需要先触发其初始化。生成这4条指令的最常见的Java代码场景是：使用new关键字实例化对象的时候、读取或设置一个类的静态字段的时候，以及调用一个类的静态方法的时候
• 使用java.lang.reflect包的方法对类进行反射调用的时候，如果类没有进行过初始化，则需要先触发其初始化

194/412

The serializable mode of transaction behavior tries to ensure that transactions run in such a way that they appear to be executed one at a time, or serially, rather than concurrently.

• Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet
• Nonrepeatable reads: A transaction re-reads data it has previously read and finds that another committed transaction has modified or deleted the data
• Phantom reads: A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

Oracle offers the read committed and serializable isolation levels, as well as a read-only mode. Read committed is the default.

How Oracle Manages Data Concurrency and Consistency

Multiversion Concurrency Control

Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Orable can also provide read consistency to all of the queries in a transaction (transaction-level read consistency).

Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.

Statement-Level Read Consistency

Oracle always enforces statement-level read consistency. This gurantees that all the data returned by a single query comes from a single point in time - the time that the query begin.

As query execution proceesds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.

Transaction-Level Read Consistency

Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began.

This mean that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries mde by a serializable transaction do see changes mode by the transaction itself.

Transactional-level read consistency produces repeatable reads and does not expose a query to phantoms.

Comparison of Read Committed and Seriablizable Isolation

Row-Level Locking

Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or undo and release its lock. If that other transaction rolls back, the waiting transaction, regardless of its isolation mode, can proceed to change the previously locked row as if the other transaction had not existed.

Referential Integrity

Because Orable does not use read locks in either read-consistent or seriablizable transactions. data read by one transaction can be overwritten by another. Transaction that perform database consistency checks at the application level cannot assume that the data they read will remain unchanged during the execution of the transaction even though such changes are not visible to the transaction.

实验一

• 两次读写操作

for (int i = 0; i < 2; i++)
{
    try
    {
        Thread.sleep(5000);
    }
    catch (InterruptedException e)
    {
        e.printStackTrace();
    }
    List<WechatUser> wechatUserList = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUserList.size());
    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");
    try
    {
        Thread.sleep(1000);
    }
    catch (InterruptedException e)
    {
        e.printStackTrace();
    }

}   

结果：

2016-03-20 22:13:30 [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource]-[DEBUG] Adding transactional method 'WechatRegistServiceImpl.insertUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE; ''
2016-03-20 22:13:30 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Acquired Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction
2016-03-20 22:13:30 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Changing isolation level of JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] to 8
2016-03-20 22:13:30 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[WARN] satart
2016-03-20 22:13:31 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /insert.html
2016-03-20 22:13:31 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Acquired Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction
2016-03-20 22:13:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Creating a new SqlSession
2016-03-20 22:13:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:35 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER
2016-03-20 22:13:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:35 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：0
2016-03-20 22:13:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36] from current transaction
2016-03-20 22:13:35 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())
2016-03-20 22:13:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:35 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 0 条
2016-03-20 22:13:36 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Creating a new SqlSession
2016-03-20 22:13:36 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32358420]
2016-03-20 22:13:36 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER
2016-03-20 22:13:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36] from current transaction
2016-03-20 22:13:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER
2016-03-20 22:13:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:41 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：1 用户数量：1
2016-03-20 22:13:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36] from current transaction
2016-03-20 22:13:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())
2016-03-20 22:13:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:41 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 1 条
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb26a36]
2016-03-20 22:13:42 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Initiating transaction commit
2016-03-20 22:13:42 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Committing JDBC transaction on Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver]
2016-03-20 22:13:42 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] <== Total: 2
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32358420]
2016-03-20 22:13:42 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：2
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32358420] from current transaction
2016-03-20 22:13:42 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())
2016-03-20 22:13:42 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32358420]
2016-03-20 22:13:42 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 0 条
2016-03-20 22:13:42 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Resetting isolation level of JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] to 4
2016-03-20 22:13:42 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Releasing JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction
2016-03-20 22:13:42 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Null ModelAndView returned to DispatcherServlet with name 'spring': assuming HandlerAdapter completed request handling
2016-03-20 22:13:42 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Successfully completed request
2016-03-20 22:13:48 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32358420] from current transaction

...

2016-03-20 22:13:49 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Committing JDBC transaction on Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver]
2016-03-20 22:13:49 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Resetting isolation level of JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] to 4
2016-03-20 22:13:49 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Releasing JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction

free

                   1      2        3       4      5         6
1              total     used     free  shared  buffers  cached
2 Mem:      24677460 23276064  1401396       0  870540 12084008
3 -/+ buffers/cache: 10321516  14355944
4 Swap:     25151484    224188  24927296

buffers是用于存放要输出到disk（块设备）的数据的，
cached是存放从disk上读出的数据

-buffers/cache，表示一个应用程序认为系统被用掉多少内存；
+buffers/cache，表示一个应用程序认为系统还有多少内存；

在linux中有这么一种思想，内存不用白不用，因此它尽可能的cached和buffer一些数据，以方便下次使用。但实际上这些内存也是可以立刻拿来使用的。
因为被系统cached和buffer占用的内存可以被快速回收，所以通常FO[3][3]比FO[2][3]会大很多

FO[2][1] = FO[2][2] + FO[2][3]
FO[3][2] = FO[2][2] - FO[2][5] - FO[2][6]
FO[3][3] = FO[2][3] + FO[2][5] + FO[2][6]

du -h --max-depth=1

查看硬盘占用情况

--max-depth=1下一级目录

df -h

查看整机硬盘使用情况

df -hl

查看整机硬盘使用情况（G, M, K为单位）