Linux:MYSQL（九）explain和事务隔离级别

EXPLAIN 解释

• 通过EXPLAIN来分析索引的有效性
• EXPLAIN SELECT clause
  获取查询执行计划信息，用来查看查询优化器如何执行查询
• 输出信息说明：
  参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
• id: 当前查询语句中，每个SELECT语句的编号
  复杂类型的查询有三种：
  简单子查询
  用于FROM中的子查询
  联合查询：UNION
  注意：UNION查询的分析结果会出现一个额外匿名临时表

EXPLAIN

• select_type：
  简单查询为SIMPLE
  复杂查询：
  SUBQUERY 简单子查询
  PRIMARY 最外面的SELECT
  DERIVED 用于FROM中的子查询
  UNION UNION语句的第一个之后的SELECT语句
  UNION RESULT 匿名临时表

MariaDB [hellodb]> explain select name,age from students where age > (select avg(age) from students);
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY     | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
|    2 | SUBQUERY    | students | ALL  | NULL          | NULL | NULL    | NULL |   25 |             |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

• table：SELECT语句关联到的表

EXPLAIN

• type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
  • ALL: 全表扫描
  • index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
  • range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
  • ref: 根据索引返回表中匹配某单个值的所有行
  • eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
  • const, system: 直接返回单个行
• possible_keys：查询可能会用到的索引
• key: 查询中使用到的索引
• key_len: 在索引使用的字节数

EXPLAIN

• ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
• rows：MySQL估计为找所有的目标行而需要读取的行数
• Extra：额外信息
  Using index：MySQL将会使用覆盖索引，以避免访问表
  Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
  Using temporary：MySQL对结果排序时会使用临时表
  Using filesort：对结果使用一个外部索引排序

并发控制

• 锁粒度：
  • 表级锁
  • 行级锁
• 锁：
  • 读锁：共享锁，只读不可写，多个读互不阻塞
  • 写锁：独占锁,排它锁，一个写锁会阻塞其它读和它锁
• 实现
  • 存储引擎：自行实现其锁策略和锁粒度
  • 服务器级：实现了锁，表级锁；用户可显式请求
• 分类：
  • 隐式锁：由存储引擎自动施加锁
  • 显式锁：用户手动请求

并发控制

• 锁策略：在锁粒度及数据安全性寻求的平衡机制
• 显式使用锁
  • LOCK TABLES 加锁(READ,WRITE)

MariaDB [hellodb]> lock table students read;

还能读可能有缓存，可修改服务器变量

show variables like 'query_cache_wlock_invalidate';
set query_cache_wlock_invalidate=ON;写到服务器选项里
vim /etc/my.cnf
[mysqld]
query_cache_wlock_invalidate=ON

- UNLOCK TABLES 解锁

MariaDB [hellodb]> unlock tables;

- FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

MariaDB [hellodb]> flush tables with read lock;

- SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查询时加写或读锁

备份

温：读，不可写
热：可读，可写
冷：不可读，不可写

事务

• 事务Transactions：一组原子性的SQL语句，或一个独立工作单元

• 事务日志：记录事务信息，实现undo,redo等故障恢复功能

• ACID特性：

  • A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
  • C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
  • I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
  • D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

事务

• 启动事务：
  BEGIN
  BEGIN WORK
  START TRANSACTION

• 结束事务：
  COMMIT：提交
  ROLLBACK: 回滚
  注意：只有事务型存储引擎中的DML语句方能支持此类操作

• 自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
  建议：显式请求和提交事务，而不要使用“自动提交”功能

• 事务支持保存点：savepoint
  SAVEPOINT identifier
  ROLLBACK [WORK] TO [SAVEPOINT] identifier
  RELEASE SAVEPOINT identifier 删除事务点

savepoint sp23 储存点( 可不写identifier)
savepoint sp24 
rollback to 24
release sp24 撤销sp24

事务隔离级别

• 事务隔离级别：从上至下更加严格

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

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

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

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

• MVCC: 多版本并发控制，和事务级别相关

事务 - 指定事务隔离级别：

• 服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置

SET tx_isolation=’’
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

查看设置：show variables like ‘tx_isolation’

• 服务器选项中指定
  vim /etc/my.cnf
  [mysqld]
  transaction-isolation=SERIALIZABLE

并发控制

• 死锁：
  两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

查看当前等待命令:show processlist\G
终止等待命令（慎用，可能修改确实占用时间长）：kill 2

• 事务日志：保证数据的安全性，完整性
  事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
  事务日志文件： ib_logfile0， ib_logfile1

优化思路：放在一个干净的分区中放事务日志。