DML transaction ISOLATION Level storage engine comparison

DML transaction ISOLATION level storage engine vs. DML

INSERT INTO

  第一种：    insert into tb_name [(col1,col2,....)]{values|value}(val1,val2,...)[,(val21,val22,....),....]  第二种：     insert into tb_name set col_name=val1,col2=val2,....  第三种(将一个表中的数据插入到另外一张表中)：     insert into tb_name select clause             <!--replace的工作机制： 与Insert相同，除了在新插入的数据与表中的主键或唯一索引定义的数据相同会替换老的行；-->

Update updates data

UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET col_name1=val1 [, col_name2={val2] ...[WHERE where_condition][ORDER BY ...][LIMIT row_count]  update通常情况下，必须要使用where字句，或者使用limit限制要修改的行数--safe-updates：启动时应该带选项  

Delete: Deleting data

MySQL Basic architecture

?

Connection pooling connects to user requests

连接连接池用到的协议：文本或者二进制二进制的协议高效一些

Core functional layer Query parsing, analysis, optimization, built-in functions
Features across the storage engine

 query cache 缓存，只保存select查询

Storage Engine layer data deposit and extraction

Explain: Parsing statements, using indexes

MySQL Lock

The mode of the lock applied when performing the operation

   1.读锁：用户在读的时候施加的锁，为防止别人修改，但是用户可以读，还被称为共享锁   2.写锁：独占锁，排它锁。其他用户不能读，不能写

Lock granularity: (amount of data)

      表锁：table lock           锁定了整张表        行锁：row lock           锁定了需要的行 粒度越小，开销越大，但并发性越好： 粒度越大，开销越小，但并非性越差；

Where to implement the lock:

   MySQL锁：可以手动使用，可以使用显示锁   存储引擎锁：自动进行的（隐式锁）   显示锁：      lock tables：施加锁      LOCK TABLES      tbl_name lock_type      [, tbl_name lock_type] ...      锁的类型 lock_type      (READ | WRITE)      unlock tables：解锁     InnoDB存储引擎也支持另外一种显示锁（锁定挑选出的部分行,行级锁）     select .... lock in share mode     select .... for update        <!--做备份时要手动施加读锁-->

Transaction: Transaction

A transaction is a set of atomic query statements, and multiple queries are treated as a separate unit of work

Acid testing: the ability to meet acid tests means that their support transactions, or compatible transactions

A：Atomicity，原子性，都执行或者都不执行C：Consistency，一致性，从一个一致性状态转到另外一个一致性状态I：Isolaction，隔离性。一个事务的所有修改操作在提交前对其他事务时不可见的D: Durability, 持久性，一旦事务得到提交，其所做的修改会永久有效    安全性越高，并发性越低

Isolation level:

       READ UNCOMMITTEND（读未提交），脏读，不可重复读，幻读       READ COMMITTEND（读提交）  不可重读       REPEATABLE READ (可重读)          SERIALIZABLE（可串行化）强制事务的串行执行避免了幻读；性能极低

Start a transaction

   start transaction

Transaction commit

      commit

Business Review

      rollback

SavePoint control where the rollback is

   SAVEPOINT identifier   ROLLBACK [WORK] TO [SAVEPOINT] identifier

* * Do not explicitly start a transaction, each statement will be treated as a default transaction, and its execution will be automatically committed
1.select @ @global. autocommit
Set Global autocommit = 0
2.cimmication: Note Turn off autocommit, remember to manually start the transaction, you should remember to manually submit * *

View the transaction isolation level for MySQL

      show global  variables like ‘tx_isolation‘      select @@global.tx_isolation  

Set the transaction isolation level for MySQL

set global tx_isolation= 级别  （READ UNCOMMITTEND| READ COMMITTEND| REPEATABLE READ | SERIALIZABLE

）

Recommendation: In scenarios where things are not particularly demanding, you can use read submissions

MVCC: Multi-version concurrency control

每个事务启动时，InnoDB会为每个启动的事务提供一个当下时刻的快照  为实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间，（其实里面存储的是系统版本号 system version number）   旨在两个隔离级别下有效：read committed和repeatable read

MySQL's storage Engine

Definition: The storage engine is also commonly referred to as the "table type"

Viewing the storage engine

   mysql> show engines;   mysql> show table status  [{FROM | IN} db_name] [LIKE ‘pattern‘ | WHERE expr]  比如 :   mysql> show table status in hellodb;           mysql> show table status in hellodb where Name=‘class‘\G    ***************************************        Name 表名        Engine 存储引擎        Version：版本        Row_format:行格式           {DEFAULT|DYNAMIC|FIXED|COMMPRESSED|REDUNDANT|COMPACT}        Rows：表中的行数        Avg_row_length:平均每行包含的字节数        Data_length:表中数据总体大小，单位为字节        Max_data_length:表能够占用的最大空间，单位为字节，0表示么有上限        Index_length:索引的大小，单位为字节        Data_free:对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间        Auto_increment:下一个AUTO_INCREMENT的值        Create_time：表的创建时间        Update_time: 表数据的最后一次修改时间        Check_time: 使用CHECK TABLE或myisamchk最近一次检查表的时间        Collation：排序规则        Checksum：如果启动，则为表的checksum        Create_options:创建表时指定使用的其他选项        Comment：表的注释信息

MySQL data file

InnoDB

      1.innodb_file_per_table=OFF,即是用共享表空间         每个表一个独有的格式定义文件：tb_name.frm         还有一个默认位于数据目录下的共享的表空间文件：ibdata#      2.innodb_file_per_table=ON，即是用独立表空间        每个表在数据库目录下存储两个文件          tb_name.frm          tb_name.ibd    <!--表空间： table space ，由InnoDB管理的特有格式数据文件，内部可同时存储数据和索引-->

MyISAM

   每个表都在数据库目录下存储三个文件       tb_name.frm       tb_name.MYD       tb_name.MYI

Modifying the default storage engine: implemented by Default_storage_engine service variables

Features of each storage engine

InnoDB:

  支持事务，有事务日志  ib_logfile0  ib_logfile1  支持外键约束  支持MVCC(多版本并发控制)     支持聚簇索引       聚簇索引之外的其他索引，通常称之为辅助索引   行级锁：间隙锁   支持使用辅助索引   支持自使用hash索引   

MyISAM:

    全文索引      支持表压缩存放：做数据仓库，能节约存储空间并提升性能      支持空间索引      表级锁      延迟更新索引     不支持事务、外键和行级锁     崩溃后无法安全恢复数据  使用场景：只读数据，表较小，能够忍受崩溃后的修复操作和数据丢失

ARCHIVE

    仅支持INSERT和SELECT，支持很好压缩功能    

Csv:

Blackhole:

    没有存储机制，任何发往次引擎的数据都会丢弃，其会记录二进制日志，因此，常用于多级复制架构中作中转服务器

MEMORY:

    保存数据在内存中，内存表；常用于保存中间数据，如周期性的聚合数据等，也用于实现临时表    支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型

Mrg_myisam:

   是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表

NDB:

   是MySQL CLUSTER中专用的存储引擎、          

Third-party storage engines

XtraDB: Enhanced InnoDB, Percona available

         编译安装时，下载XtraDB的源码替代MySQL存储引擎中的InnoDB的源码

PBXT:MARIADB comes with this storage engine

    支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持   支持事务、MVCC

TOKUDB:

   使用 Fractal Trees索引，适用存储大数据，拥有很好的压缩比，已经被引入MariaDB  

Column data storage Engine:

 Infobright:目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计 InfiniDB MonetDB LucidDB   

Open Source Community Storage Engine:

 Aria：前身为Maria，是增强版的MyISAM（支持崩溃后安全恢复，支持数据缓存） Groona：全文索引引擎 Mroonga：是基于Groona的二次开发版 OQGraph：由open query研发，支持图（网状 ）结构的存储引擎 SphinxSE：为Sphinx全文搜索服务器提供了SQL接口 Spider：能将数据切分成不同的分片，比较高效透明的实现了分片（shared），并支持在分片上支持并行查询   

* How to select the storage engine:
Whether a transaction is required
Support for type of backup
Post-crash recovery
Unique Features *

Index Type:

   聚簇索引    辅助索引   B树索引   R树索引   hash索引   全文索引

DML transaction ISOLATION Level storage engine comparison