MySQL学习笔记1

MySQL

1.MySQL架构与历史

1.1 MySQL逻辑架构

以下是MySQL逻辑架构图

• 最上层是基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理，授权认证，安全等等。
• 第二层架构是大多数MySQL核心服务功能所在的层，包括查询解析，分析优化，以及所有的内置函数（例如日期，时间，数学和加密函数），跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等
• 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取

读写锁

读锁：共享的，相互不阻塞的，多个用户在同一时刻可以同时读取同一个资源，而互不干扰。

写锁：排他的，一个写锁会阻塞其它的写锁和读锁

写锁比读锁具有更高的优先级

大多数时候，MySQL锁的内部管理都是透明的

事务内的语句，要么全部执行成功，要么全部执行失败

ACID

1. 原子性：
   • 事务中的所有操作是不可再分割的原子单位，要么执行，要么不执行
   • 一个事务中的操作，要么全部成功，要么全部失败
   • 如果全部失败，就回滚到事务开始前的状态
2. 隔离性：
   • 一个事务执行的过程中，不应该受到其它事务的干扰
   • 当多个用户并发的访问数据库中时，如果操作同一张表，数据库则为每一个用户都开启一个事务，且事务之间互不干扰，也就是说事务之间的并发是隔离的
   • 举个例子，现有两个并发的事务T1，T2，T1要么在T2开始前执行，要么在T2结束后执行，如果T1先执行，那T2就在T1结束后再执行
3. 一致性
   • 事务的执行的前后数据的完整性保持一致
   • 事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
   • 拿转账举例子，A账户和B账户相互转账，无论如何操作，A，B账户的总金额必须是不变的。
   • 同步更新
4. 持久性：
   • 一旦事务提交，对数据的改变是永久的，持久到数据库中
   • 即使断电或宕机情况下，也不会丢失提交事务的操作。

四种隔离级别

• 多个事务读可能会导致以下问题
  • 脏读：事务B读取事务A还没有提交的数据
  • 不可重复度：是指在数据库的访问中，一个事务范围内两个相同的查询返回了不同数据。这是由于在查询过程中，数据被另外一个事务修改并提交了。
  • 幻读：当在事务处理过程中执行两个相同的查询，并且第二个查询返回的行集合与第一个查询不同。这两个区别在于，不可重复读重点在一行，幻读的重点是返回的集合不一样。幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

隔离级别	脏读	不可重复度	幻读
READ UNCOMMITTED（读未提交）	可能	可能	可能
READ COMMITTED（读已提交）	不可能	可能	可能
REPEATABLE READ（可重复读）	不可能	不可能	可能
SERIALIZABLE（串行化）	不可能	不可能	不可能

行级锁

对一行或多行数据的锁机制，不是由MySQL服务器实现的，而是由其它存储引擎自己实现的

优点：锁粒度小，并发高

缺点：最大的锁开销，降低性能

表锁

MySQL中最基本的锁策略。锁定整张表

优点：开销最小，比行级锁更好的性能

缺点：锁粒度大，并发低，因为会锁住整张表造成更多的数据被锁占用

在事务中混合使用存储引擎

MySQL服务器层是不管理事务的，事务由下层的存储引擎实现，在同一个事务中，使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务性和非事务型的表（例如InnoDB表和MyISAM表），正常提交情况下不会有什么问题。

但是假如事务进行回滚的话，非事务型的表的变更是不能撤销的。会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确认。

隐式锁定和显示锁定

InnoDB采用的是两阶段锁定协议。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT和ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。以上是隐式锁定。

InnoDB会根据隔离级别在需要的时候自动加上锁。

InnoDB支持通过特定的语句进行显示加锁。

• SELECT ... LOCK IN SHARE MODE
• SELECT ... FOR UPDATE

注意：

MySQL支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，跟存储引擎没关系，不能代替事务处理，所以应用涉及到事务，还是要选择事务性存储引擎。

如果应用已经将表从MyISAM转换到InnoDB，但是还显式使用LOCK TABLE的话，这没必要，且严重影响性能，实际上InnoDB行级锁工作的更好

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，也是最重要、使用最广泛的存储引擎，被设计用来处理大量短期事务。

InnoDB概述

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。

InnoDB采用MVCC（多并发控制版本）来支持高并发，实现了四个标准的隔离级别。默认级别是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入

InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询由很高的性能。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

InnoDB通过一些机制和工具支持真正的热备份

MyISAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎，MyISAM提供了大量的特性，包括全文索引，压缩，空间函数等，但MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复

存储

MyISAM会将表存储在两个文件中：数据文件和索引文件，MyISAM表可以包含动态或者静态行

特性

1. 加锁与并发

   MyISAM对整张表加锁，而不是针对行，读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表里插入新的记录（并发插入）

2. 修复

   MyISAM可以手工或者自动执行检查和修复操作，但和崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable进行修复。

3. 索引特性

   对于MyISAM表，对于长字段也能基于前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。

4. 延迟更新索引键

   创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作