mysql学习笔记--基础篇

mysql逻辑架构 （三层

     最上层为客户端  负责进行链接，认证等

     第二层 负责 解析 优化（解析树） 缓存 分析 以及所有的内置函数（日期 时间 数学和加密）

      第三层为存储引擎  数据存储和提取 只是相应上层服务器请求

每个客户端链接会在服务器进程中拥有一个线程 这个链接的查询只会在这个线程中执行

服务器缓存线程 不需要为每个新建的连接创建或销毁进程

隔离级别

未提交读  （读取到为提交数据 如若回滚 出现脏读

        提交读      （执行两次查询可能因为其他事务的提交而导致两次读取的数据不一致 不可重复读

        可重复读   （解决了上面 两个问题 执行两次查询可能因为其他事务提交的插入导致读取行数不一样 （幻行）InnoDB 依靠MVCC解决

                  （mysql默认级别

         可串行化

（针对数据库）

脏读：读未提交-》回滚  （写时不加任和锁）

不可重复读：读-》修改-》读 （锁定只在游标位于该行上时保留）

幻行：读-》插入-》读 （锁定所有检索用到的行）（1000行里读10行 10锁）

可串行化：锁定该事物引用的所有行 （1000行里读10行 1000全锁）

死锁   InooDB之类的引擎有死锁检测 将持有最少行级锁的事务回滚 （返回-1)

show variables like 'autocommit';

不显示的开启一个事务 mysql每个查询都会被当作一个事务执行提交（可修改查看设置 autocommit

set autocommit=

你可以用下列语句查询全局和会话事务隔离级别：

 SELECT @@global.tx_isolation; 

SELECT @@session.tx_isolation; 

SELECT @@tx_isolation;

隔离级别

 tx_isolation                                      | REPEATABLE-READ （默认）

修改当前会话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

 tx_isolation                                      | READ-COMMITTED    

退出再次登入之后就还是 tx_isolation                                      | REPEATABLE-READ （默认）

SET[SESSION | GLOBAL]TRANSACTIONISOLATIONLEVEL {READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}

默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。

如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。

锁定

锁分为读锁（共享锁）和写锁（排他锁）

共享锁(S锁)--读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

       和写锁（排他锁）若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

锁定分为隐式和显式

引擎（InnoDB）会根据隔离级别在需要的时候自动加锁

也支持通过特定语句显示锁定

select.... lock in share mode

锁只有在commit 和 roll back时回滚

InnoDB还进行显示的锁定非常的不好 不推荐使用

如今的事务型存储引擎实现的都不是简单的行级锁 而是实现MVCC

通过快照实现 但是不同的数据库实现机制不同 没有一个统一标准

InnoDB实现MVCC是通过每行记录后面保存两个列实现的 （创建时间 和 过期（删除）时间）

每开始一个事务 系统版本号都会递增 事务开始时刻的系统版本号作为事务的版本号

select

只查找版本号早于当前事务的行

行的删除版本未定义 或大于当前事务版本

insert 新插入一行 版本号为当前系统版本号

delete 当前系统版本号为删除标识

update  新插入一行 版本号为当前系统版本号 当前系统版本号为原来的行的删除标识

分引擎描述 

show table status like 'user' \G   

得到表信息  (\G) 设置显示格式

*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
 Row_format: Compact
           Rows: 33
Avg_row_length: 496
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 50
    Create_time: 2015-06-15 18:27:01
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

InnoDB 使用MVCC支持高并发

聚簇索引

MyISAM 不支持事务和行级锁 崩溃后无法安全恢复

MyISAM 会将表数据存储在两个文件中 .MYD .MYI

MyISAM可以存储的记录行数受限与存储空间和单个文件限制大小

指针长度 决定了表的最大大小 

加锁与并发   实现的是表锁 读取时读锁 写入时写锁 但是读取时可以插入新记录

修复            （和事务恢复之类概念不同） check table xx检查错误 repair table xxx修复 还有其他工具帮助恢复

索引             支持全文索引 blod text也可基于前500字符创建索引

延迟更新索引 创建表时 指定delay_key_wirte时 在每次修改完成 不会将立刻将索引写入磁盘 而是写入内存 只有清空缓存关闭表时才写入

压缩表（若表不会被修改时可使用）

myisampack打包MyISAM表 

可减少占用空间 ->减少IO 

每次读取需要解压 但是因为记录是独立压缩的 所以读取行时不需要解压整个表

frm、MYI、MYD 分别是 表的表结构\索引\数据文件

myisam是使用简单的锁（表锁）来实现事务隔离级别的，读加share locak 写加 Exclusive lock

所以对于写操作比较多的表不适合用myisam 会发现一直被锁，并且myisam不支持错误修复，需要repair 还不支持事务

Repeatable Read（可重读） 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致幻读

  InnoDB通过多版本并发控制（MVCC，Multiversion Concurrency Control 间隙锁）机制解决了该问题。注：MVCC解决不可重复读问题 加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题。

MVCC 可以保证不阻塞地读到一致的数据    MVCC是为了减少加锁而引入了，从而来提高并发性。MVCC只工作在repeatable read和read commit两个隔离级别。

因为是mvcc解决不可重复读 所以一个未提交的事务删除一行 另一个事务还是可以读取这行 而不是因为锁而发生冲突

综上innodb 实现隔离级别不是单纯通过锁 而是锁+MVCC 并且的锁粒度为行级锁