1 事务

事务内的语句，要么全部执行成功，要么全部执行失败回滚。

银行应用事务必要性的的一个经典例子。假设银行的数据库有2张表：支票表（checking）表和储蓄表（savings）。现在要从用户jane的支票账户转义200元到她的储蓄帐户，那么需要2个步骤：

加查支票账户余额是否高于200元
从支票账户中减去200元
储蓄账户余额中增加200元

以上3个操作必须打包在一个事务中，任何一个步骤是白，必须同时会滚所有的操作。

事务满足ACID。

原子性(Atomictiy): 整个事务中的所有操作要么全部提交成功，要么全部失败回滚。不可能执行其中的一部分操作
一致性(consitency): 数据库总是从一个一致性的状态转换到另一个一致性的状态。如前面的例子，执行完第2步和第3步之间的时候系统崩溃了，支票余额不会被扣除200元。
隔离性(isolation): 一个事物所做的修改在最终提交以前，对其他事务“通常来说”是不可见的。执行完第2步和第3步之间的时候，另一个账户汇款程序看到的支票余额不会减去200元。
持久性(durability): 一旦事务提交，其所做的修改就会永久保存在数据库中。

1.1 隔离级别

SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITED(未提交读)：事务中的修改，即使没有提交，对其他事物也都是可见的。事务可以读取未提交的数据，会导致脏读，而且性能没有好太多，所以实际应用中一般很少使用。
READ COMMITED(提交读)：一个事物提交前，只能“看见”已经提交事务的的修改。但是A事务读取数据后，B事务更新了数据，A事务再次读到的数据和第一次可能不一样，所以也叫“不可重复读”。
REPEATABLE READ(可重复读)：解决了脏读和事务中多次读取同一个记录的结果是一致的。但是读取某个范围内的数据，另一个事务插入了新的记录，之前事务再次读取会产生“幻读”。InnoDB通过MVCC（多本版并发控制）解决了幻读问题。可重复读是Mysql的默认事务隔离级别。
SERIALIZABLE（可串行化）：最高的隔离级别, 它通过强制事务串行执行，避免了前面的幻读问题。这样就会在每一行数据上加锁，导致性能低下。实际中也很少用到这个隔离级别。

1.2 多版本并发控制(MVCC)

可以认为MVCC是行锁的一个变种，但是它在很多场景下避免了加锁操作，因此开销较低。实现了非阻塞的读操作，锁定必要行的写操作。

MVCC的实现是通过保存数据在某个时间点的快照实现的，也就是说不管多长时间，每个事务看到的数据是一致的。InnoDB的MVCC是通过在每行记录后面保存2个隐藏的列来实现的。这两个列一个保存了行创建时间，一个保存了过期时间。不是具体的时间值而是系统版本号，没开始一个新的事物，系统版本号都会自动递增。事务开始的系统版本号会作为事务的版本号用来和每行记录的版本号进行比较。

2 查询性能优化

2.1 重构查询的方式

2.1.1 复杂查询还是简单查询

在优化查询的时候，目标是找到一个更有的方法获得实际需要的结果。

设计查询的时候需要考虑的一个重要问题是：是否需要将一个复杂的查询分成多个简单的查询。

在一个通用服务器上，某些mysql的版本也能运行每秒超过10万的查询
即使一个千兆网卡也能轻松满足每秒超过2000次的查询。
如果将一个大查询分解为多个小查询能够减少工作量，这么做时可取的。

mysql内部每秒能扫描上百万上数据，相比之下，MySQL响应数据给客户端就慢的多。

其它条件相同的条件下，尽可能少的查询当然是更好的。
如果一个查询能够胜任时还写成多个独立的查询时不明智的。

2.1.2 拆分查询

如删除旧数据，清理旧数据的时候，每次delete 10000条数据，然后sleep一会，可以大大降低对服务器的影响。

2.1.3 分解关联查询

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效。比如应用层应用缓存的的场景，应用方便的缓存了单个查询的结果，可以直接试用IN或者ID直接查询，提高性能。

通过缓存ID，然后根据ID进行更新是一个很常用的方法。

2.2 Limit分页

SELECT film_id, description FROM FILE order by title Limit 50, 5

提升性能的最好方法是采用索引覆盖扫描

如果表非常大，那就需要使用“延迟关联”的方法，让mysql扫描尽可能少的页面。

SELECT film_id, description FROM film INNER JOIN(
    SELECT film_id FROM film ORDER BY title LIMIT 50， 5
    )AS LIM USING(film_id)

这里的关键在于：通过使用覆盖索引查询先返回需要的键（一般为主键），再根据取到的键关联原表获得需要的行，这可以减少MYSQL扫描那些不符合条件的行。但不是所有情况第2中写法都会提升性能，应用不对反而会下降，具体问题需要具体分析。

大概可以分三种情况：

总数据为4万行，符合条件的为3万行。改写后提升不是特别明显；
总数据为4万行，符合条件的为300行。改写后性能提升很明显；
总数据为50行，符合条件的为40行，改写后反而性能下降。

一般分页时，如limit n，m，当n特别大时，通常需要用延迟关联来解决性能问题。

2.3 其他

on/using/where的字段上加索引
group/order by 中的表达式只针对一张表
不去重，使用union All

2.4 优化命令

2.4.1 查看 MySQL 服务器运行的状态值

show status

主要关注 “Queries”、“Threads_connected” 和 “Threads_running” 的值，即查询次数、线程连接数和线程运行数。

2.4.2 查看运行的线程

show processlist

如果state出现如下值，需要进行优化：

Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重
Create tmp table #创建临时表，严重
Copying to tmp table on disk #把内存临时表复制到磁盘，严重
locked #被其他查询锁住，严重
loggin slow query #记录慢查询
Sorting result #排序

2.4.3 开启慢日志查询

show variables like "%long%";          // 查看一下默认为慢查询的时间10秒
show variables like "%slow%";          // 查看一下慢查询是不是已经开启
set slow_query_log='ON';               // 开启慢记录查询，加上global，不然会报错的
show variables like "%slow%";          // 查看是否已经开启

2.4.4 通过explain分析

筛选出有问题的 SQL，我们可以使用 MySQL 提供的 explain 查看 SQL 执行计划情况（关联表，表查询顺序、索引使用情况等）。

mysql> explain select * from category;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | category | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

type的类型：

system：表只有一行记录，相当于系统表
const：通过索引一次就找到，只匹配一行数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
index：只遍历索引树
ALL：全表扫描，性能最差

Extra的类型：

using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
using where：where 子句用于限制哪一行
using join buffer：使用连接缓存
distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行

Mysql基础

Mysql的基础知识和一些实现机制