MySQL（下）

四、事务

一、概念

       对数据库的一次执行中有多条sql语句执行。这多条sql在一次执行中，要么都成功执行，要么都不执行。保证了数据完整性。MySQL中只有innodb引擎支持事务。

二、特性       

        事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

        原子性：一个事务中多条sql要么都执行，要么都不执行，不执行的回滚到事务执行前状态。

        隔离性：事务可以多个同时执行，要对多个事务进行隔离。
        持久性：事务正常提交后，可以保证数据持久保存，即使宕机也不丢失。

        一致性：原子性，持久性，隔离性都是为了保证一致性，保证数据是完整可靠的。

三、设置

        默认情况下, MySQL 启用自动提交模式（变量 autocommit 为 ON）。

MYSQL 事务处理主要有两种方法：

        1、用 BEGIN, ROLLBACK, COMMIT 来实现

                        BEGIN 开始一个事务

                        ROLLBACK 事务回滚

                        COMMIT 事务确认

        2、直接用 SET 来改变 MySQL 的自动提交模式:

                        SET GLOBAL / SESSION autocommit=0; 禁止自动提交

                        SET GLOBAL / SESSION autocommit=1;开启自动提交

查看 autocommit 模式

                SHOW GLOBAL / SESSION VARIABLES LIKE 'autocommit';

四、事务隔离级别

一、读未提交(read uncommitted)：

        一个事务可以读取到另一个事务未提交的修改。这会带来脏读，幻读，不可重复读问题。

        

        SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

二、读已提交(read committed)：

        一个事务只能读取另一个事务已经提交的修改。其避免了脏读，仍然存在不可以重复读和幻读问题。

        SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

三、可重复读(repeatable read MySQL 默认隔离级别)：

        同一个事务中多次读取相同的数据返回的是一样的。其避免了脏读和不可重复读问题，普通查询解决了幻读问题,如果在查询中添加 for update 语句, 会出现幻读问题.

幻读: 同一个事物中多次读取读到数据行数不同.

        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

四、串行化(serializable)：

        事务串行执行,避免了以上所有问题。

        SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

五、事务实现原理     

        InnoDB 存储引擎还提供了两种事务日志：redolog(重做日志)和undolog(回滚日志)。其中 redolog 用于保证事务持久性；undolog 则是事务原子性和隔离性实现的基础。

原子性实现：

        MySQL用undolog 日志文件来记录增删改操作的反向操作。当出现异常事务回流时，执行对应操作的反向操作即可还原。

持久性实现(mysql 保证数据不丢失)：

        当数据库事务提交后，保证数据是不能撤销的。当sql发送到MySQL后，事务还未提交前，如果发现断电或宕机，先将sql保存到redolog日志文件中，在MySQL更新启动时，执行redolog中sql.

六、隔离级别实现原理MVCC

MVCC是什么：

        MVCC（多版本并发控制 Multi-Version Concurrent Control），是 MySQL 提高性能的一种方式，配合 Undo log 和版本链,让不同事务的读-写、写-读操作同时进行，提高并发访问，每次事务对某条记录操作时生成一个操作的版本链。

        如果隔离是读已提交，那么在同一个事务中，每次读取时，都会从版本链上生成一个快照（read view），每次读到的时当前查询时最新的数据，也称当前读。

        如果事务隔离是可重复读，在事务第一次读取数据时，会从版本链上生成一个快照，之后再次读取时，仍从上次版本快照中读取，也可实现重复读，也称快照读。

五、锁机制

一、概述        

        事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

二、行锁、表锁、间隙锁

        行锁：

        一个事务对某行数据进行操作时，其他事务不能对本行进行操作。分为共享锁，排他锁。

        特点：粒度小，开销大，加锁慢，并发度高。

        表锁：

        表级锁操作时，会给整张表加锁，MYISAM 与 INNODB 都支持表级锁定。

        特点：锁定粒度最大，实现简单，资源消耗较少，开销小，加锁快，开发度最低。

        间隙锁：

        满足某条件，获取某区间。

三、共享锁、排他锁 

共享锁：

        又称读锁。为一个查询语句添加共享锁后，其他事务读取，但不能再添加排他锁。

排他锁：

        又称写锁。为一个查询语句添加排他锁后，其他事务就不能为加锁的数据添加其他锁了，共享锁也不可以。

六、SQL优化

一、为什么要优化 

        随业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化就很有必要。

二、优化方法   

1.查询 SQL 尽量不要使用 select *，而是具体字段

        节省资源、减少开销。

2.避免在 where 子句中使用 or 来连接条件

        反例:SELECT * FROM user WHERE id=1 OR salary=5000

        正例:使用 union all 把两个两个 SQL 结果合并

        使用 or 可能会使索引失效，从而全表扫描；对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary 查询条件时，它还得全表扫描；

3. 尽量使用数值替代字符串类型

        正例

                主键（id）：primary key 优先使用数值类型 int

                性别（gender）：0 代表女，1 代表男；数据库没有布尔类型，mysql

        推荐使用 tinyint

        因为引擎在处理查询和连接时会逐个比较字符串中每一个字符； 而对于数字型而言只需要比较一次就够了；字符会降低查询和连接的性能，并会增加存储开销；

4. 使用 varchar 代替 char

varchar 变长字段按数据内容实际长度存储，可以节省存储空间；

char 按声明大小存储，不足补空格；

其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

5. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by group by 涉及的列上建立索引

6. 应尽量避免索引失效

6.1 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引

而进行全表扫描，如:select id from t where num=10 or num=20。

6.2 in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num

in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where

num between 1 and 3

6.3 模糊查询也将导致全表扫描

select id from t where name like '%abc%'

6.4 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进

行全表扫描。如: select id from t where substring(name,1,3)='abc'

7. 提高 group by 语句的效率

反例:先分组，再过滤

正例:先过滤，后分组

8. 清空表时优先使用 truncate

truncate table 比 delete 速度快，且使用的系统和事务日志资源少.

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

truncate table 通过释放存储表数据所用的数据页来删除数据.

9. 表连接不宜太多，索引不宜太多，一般 5 个以内

        联的表个数越多，编译的时间和开销也就越大，每次关联内存中都生成一个临时表应该把连接表拆开成较小的几个执行，可读性更高。

10. 深度分页问题

        反例 ：select id,name from account limit 100000,10;

        正例 ：select id,name FROM account where id > 100000 order by id limit 10;

11. 使用 explain 分析 SQL 执行计划

三、执行计划

EXPLAIN

        使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL

是如何处理SQL 语句的。

EXPLAIN 作用

        表的读取顺序

        数据读取操作的操作类型

        哪些索引可以使用

        哪些索引被实际使用

        表之间的引用

EXPLAIN 使用

        在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息，

而不是执行 SQL。

例如：EXPLAIN SELECT * FROM USER WHERE id = 1

expain 出来的信息有 12 列，分别是:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

其中：

id是选择标识符。是 SELECT 的查询序列号,id 如果相同，可以认为是一组，从上往下顺序执行,

在所有组中，id 值越大，优先级越高，越先执行。

select_type表示查询的类型。

        

        1.SIMPLE(简单 SELECT，不使用 UNION 或子查询等)

        2.PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的 select 被标记为 PRIMARY)

        3.SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)

        4.DERIVED(派生表的 SELECT, FROM 子句的子查询)

        5.UNION(UNION 中的第二个或后面的 SELECT 语句)

table是输出结果集的表。

type表示表的连接类型。又称“访问类型”。常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，性能从好到差）.

possible_keys表示查询时，可能使用的索引。显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

system: 表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不计。

const: 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique索引。

eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描.

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，但它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range: 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index: Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）。

All: Full Table Scan，将遍历全表以找到匹配的行。一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

key表示实际使用的索引。如果为 NULL，则没有使用索引,或者索引失效。

key_len是索引字段的长度。在不损失精确性的情况下，长度越短越好。

rows是扫描出的行数(估算的行数)。