MySQL之三 引擎、视图、锁、事务

视图：VIEW  （是一个虚表，因为视图是存储下来的select语句,并且针对这个select结果再次查询和修改，意味着所做的操作不是针对一个事实上存在的物理表进行的，而是针对被select虚化以后的表实现的）

创建方法：（不建议使用）

注：视图中的数据事实上存储于“基表”中，因此其修改操作也会针对基表实现，其修改操作受基表约束限制

mysql> HELP CREATE VIEW

CREATE  VIEW view_name [(column_list)] AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]删除视图
mysql> HELP DROP VIEWSyntax:DROP VIEW [IF EXISTS] view_name [, view_name] ...    [RESTRICT | CASCADE]　
E.G   基表students
mysql> CREATE VIEW test AS SELECT sid,name,gender FROM students;Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;+------------------+| Tables_in_testdb |+------------------+| students         || tbl2             || test             |+------------------+3 rows in set (0.00 sec)
 
mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| testdb     |+------------+1 row in set (0.00 sec)
 
mysql> SHOW TABLE STATUS LIKE ‘test‘\G;*************************** 1. row ***************************           Name: test         Engine: NULL        Version: NULL     Row_format: NULL           Rows: NULL Avg_row_length: NULL    Data_length: NULLMax_data_length: NULL   Index_length: NULL      Data_free: NULL Auto_increment: NULL    Create_time: NULL    Update_time: NULL     Check_time: NULL      Collation: NULL       Checksum: NULL Create_options: NULLComment: VIEW1 row in set (0.00 sec)
分析虚表“”
mysql> EXPLAIN SELECT * FROM test WHERE name=‘zhaoming‘\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLEtable: students    #物理表的来源   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4     filtered: 25.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

 一、存储引擎
Mysql存储引擎（支持引擎）  表级别概念
　　　　表类型：CREATE TABLE  ... ENGINE=
　MariaDB（XtraDB 引擎(percona公司提供)）
　MyISAM：（不适合在线处理事务）



支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；但不支持事务，不支持行级别锁且为表级锁
崩溃后无法安全恢复
适用于场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
数据文件：每一张表都含有一下文件

　　表数据：tb_name.MYD
　　索引：tb_name.MYI
　　表结构定义文件：tb_name.frm








特性

　　加锁和并发：表级锁
　　修复：手工或自动修复、但可能丢失数据
　　索引：非聚集索引
　　延迟更新索引键
　　压缩表





　InnoDB特点：



处理大量的短期事务：
数据存储于“表空间” （table space）中：

所有InnoDB表的数据和索引放置于同一个表空间中

表空间文件：datadir定义的目录下

数据文件：idbdata1,idbdata2,...  （对上级表空间来说都是同一个表空间）


每个表单独使用一个表空间存储表的数据和索引

innodb_file_per_table=ON   （意味着创建表的话，每一张表都有单独的表空间，而不是统一的ibdata1）　　
表空间-------数据文件（存储数据和索引）：tb1_name.ibd
表格式定义文件：tb_name.frm

基于MVCC机制(多版本并发机制)：来支持较高并发，支持所有的四个隔离事务级别，默认级别为REPEATABLE READ; 间隙锁防止幻读
使用聚集索引
支持“自适应hash索引”、插入缓存区
支持热备（xtrabackup）




mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_file_%‘;

判断一张表是什么类型引擎：
　　　　SHOW TABLE STATUS;
 1.2、其它的存储引擎：
　　　CSV：将普通的CSV(字段通过逗号分隔)作为MySQL表使用；不支持索引
　　　MRG_MYISAM ：将多个MyISAM表合并成一个虚拟表
　　　BLACKHOLE：类似于/dev/null，不真正存储任何数据，多级级联复制时有用（复制中继）
　　　MEMORY：通常适用于临时表-----所有数据都保存于内存中，内存表，支持hash索引：表级锁
　　　　　　避免临时表在内存上占用过大空间会定义：临时表的大小------默认16M（超过16M后必然会临时存储在磁盘上）
　　　PERFORMANCE_SCHEMA ：伪存储引擎------随着MySQL启用才会存在，关闭后消失（类似于/proc文件）
　　    ARCHIVE : 支持SELECT和INSERT操作-----支持行级锁和专用缓存区（适用于数据仓库）
　　　FEDERATED ：用于访问其它远程Mysql服务器一个代理，它通过创建一个到远程Mysql服务器的客户端连接，
　　　　　　　　　　并将查询传输到远程服务器执行，而后完成数据存取。
　　　　　　　　　在MariaDB上实现时FederatedX
 
 二、并发控制：
锁：

　读锁：共享锁
　写锁：独占锁






锁粒度：

　　表级锁
　　行级锁






锁策略：在锁粒度及数据安全性寻求的平衡机制
　　　　每种存储引擎都可以自行实现其锁策略和锁粒度
　　　
　MySQL在服务器级也实现了锁，表级锁：用户可显式请求

LOCK TABLES  tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES  ：解锁E.G
mysql> LOCK TABLES students READ;
mysql> UNLOCK TABLES;
mysql> UPDATE students SET Age=15 WHERE name=‘zhaoming‘;
mysql> LOCK TABLES students WRITE;
注：先如果是读锁，查询后会记录缓存中，需要更新表数据后，会触发使其缓存失效，再去写锁就能看到效果（直到解锁或者超时才结束）
FLUSH TABLES tbl_name [, tbl_name，....][WITH READ LOCK]　　把表的所有数据从内存清到硬盘上，而后关闭最后再打开表
SELECT clase [FOR UPDATE] [WITH READ LOCAK]

mysql> SHOW GLOBAL VARIABLES LIKE ‘%ssl%‘;

三、事务

3.1、事务：一组原子性的SQL查询，或者说一个独立工作单元。 

 　　　事务日志：

3.2、ACID测试：

A：autoicity 原子性：整个事务中的所有操作要么全部成功执行，要么全部失败后回滚回去。

 　　　 如何执行回滚：一个关系型数据库应该具有撤销日志（redo log）功能（用于回滚此前特定的操作），

　　　　　　还具备重做日志(undo log)（把此前已经执行的操作再运行一次）

C：consistency 一致性：数据库总是从一个一致性状态转换成另一个一致状态；

I：Isolation  隔离性:一个事务所做出的操作在提交之前，是不能为其它所见；

D：durability 持久性： 一旦事务提交，其所做的修改会永久保存于数据库中；

3.3、事务：

启动事务：START TRANSACTION;

结束事务：

• • COMMIT：提交
  • ROLLBACK：回滚

注意：只有事务型存储引擎方能支持此类操作

3.4、默认自动提交：

显式提交和提交事务，而不是使用“自动提交”功能

mysql> SHOW GLOBAL VARIABLES LIKE ‘%AUTO%‘;

 关闭自动提交：

mysql> set session autocommit=0;

mysql> show session variables like ‘autocommit‘;

3.5、事务支持savepoint  

SAVEPOINT identifier    #设定保存点
ROLLBACK [WORK] TO SAVEPOINT identifier  #回滚到指定保存点
RELEASE SAVEPOINT identifier  #删除保存点E.G
mysql> UPDATE students SET Age=27 WHERE name=‘yangguo‘;mysql> SAVEPOINT sq2;
 
mysql> INSERT students (sid,name,Age,gender) VALUES (‘5‘,‘guoxiang‘,‘25‘,‘F‘);
mysql> SAVEPOINT sq3;
回滚sq2;
mysql> ROLLBACK TO SAVEPOINT sq2;

3.6、事务隔离级别：

READ  UNCOMMITTED：读未提交----别人只要操作，我这边立马能看见
　　隔离级别最低，彼此间的干扰最大
READ  COMMITTED : 读提交---- 不可重复读和幻读
　　别人只有提交之后，对方才能看到
REPATABLE  READ : 可重读的  （默认级别）---幻读和加锁读    
　　无论别人提不提交，我这里第一次看到什么样数据，事务结束后也还是什么样数据，直到我方commit后，数据才会同步
SERIALIZABLE：可串行没有幻读-----加锁读　　无论别人提不提交，在我方第一次查询时会被阻塞掉，直到别人commit后，我方才可以再次查询。
　　隔离级别越高，并发能力也就越差；并发能力越低也就意味着服务器性能也就越差

3.6.1、 可能存在问题：

• 　　脏读：
• 　　不可重复读：
•  　  幻读：
• 　　加锁读：

tx_isolation定义隔离级别--为全局变量；默认为REPEATABLE-READ,可在session级别修改3.6.2、服务器变量 ：

• 全局变量 　　修改后不影响当前会话，只对新建的会话有效；
• 会话变量： 　　仅对当前会话有效，而且是立即生效； 以上服务器重启，都会失效。要永久有效需修改配置文件

 查看数据库隔离级别　　　　mysql> SHOW GLOBAL VARIABLES LIKE "%ISO%";
　　修改当前会话隔离级别：
　　　　　mysql>SET tx_isolation=‘READ-UNCOMMITTED‘;　　
　　查看当前事务的隔离级别：　
　　　　mysql> SELECT @@tx_isolation;　
　　
 

Mysql ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 解决方法：

 mysql>SELECT * FROM information_schema.INNODB_TRX\G;
 mysql>KILL pid（SELECT * FROM information_schema.INNODB_TRX）
 
查看packet上限
 mysql>show global variables like ‘max_allowed_packet‘;
临时生效：
 mysql> set global max_allowed_packet=1024*1024*16;
编辑/etc/my.cnf，将max_allowed_packet = 16M

 　　MVCC：多版本并发访问控制  --------多个事务看不到不同数据内容的原因
 
3.6.4、死锁：
　　　　两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
3.6.5 事务日志分两类：
　　事务日志的写入类型为“追加”，因此其操作为“顺序I/O” （一段连续的磁盘IO）
　　此日志通常也称为“预写式日志(write ahead logging)”
 

日志大小及其文件路径　　安装目录/data 目录下　　-rw-r----- 1 mysql mysql 50331648 Jun 25 21:41 ib_logfile0
　　-rw-r----- 1 mysql mysql 50331648 Jul 16  2019 ib_logfile1
mysql> show global variables like ‘%innodb%‘;

只能通过修改配置文件实现（关闭mysql）

| innodb_log_file_size                     | 50331648               |

| innodb_log_files_in_group                | 2                      |

| innodb_log_group_home_dir                | ./

重做日志：redo  log
在写到数据库里面之前，先写到日志里面，就算服务器崩溃了，可以根据日志重新走一遍。

撤销日志：undo  log
每次一操作之前，把原有的状态保留下来，万一需要还原回去以便撤销回去


在事务引擎上执行写操作都要执行两遍： 事务日志一次，数据文件一次
写在日志中的速度快的多，因为日志中仅记录操作过程，而不是操作数据本身

注：当一个事务提交了就代表它完成了。
日志文件有两个，轮流使用 
日志文件大小要根据事务需求


假如磁盘坏了怎么？
日志和磁盘放在同一个磁盘上，需要做raid10；或者日志文件和数据文件放在不同设备上；还需要备份才能恢复。

3.6.6、事务的状态

活动的：active
　　事务在执行时的状态叫活动状态。
部分提交的：
　　最后一条语句执行后（该状态是非常危险）
失败的：
　　事务不能正常执行的状态叫失败状态。
中止的：
　　事务回滚并且数据库已经恢复到事务开始执行前的状态叫中止状态。
提交的:
　　事务在部分提交后，将往硬盘上写入数据，当最后一条信息写入后的状态叫提交状态。进入提交状态的事务就成功完成了。

注： 事务一旦提交，就无法撤销了；要想回到原来状态，只能通过补偿事务来完成