mysql的基本介绍简书_一、mysql简介

好久没更新简书了，最近遇上一些事情拖慢了读书进度，望见谅！由于本书篇幅较大，所以我决定今后每看完一章就更新一章的笔记，我会一如既往的和大家一起共同走下去，我相信读书路上你我同在

激萌.jpg

mysql流程

客户端发起对mysql数据库服务器的连接操作，mysql服务器内部通过连接池维护客户端连接，每一个客户端的查询操作都建立在一个独立的mysql连接上

当客户端发起sql查询时，mysql服务器首先会在查询缓存中查找是否有已经存在的查询记录，有就返回，否则，开始解析客户端发来的sql查询语句并进行分析优化，之后通过API调用存储引擎获取查询结果，并将结果保存到缓存中，同时返回给客户端

下面是整个流程的简略图

mysql请求流程.png

并发控制

只要存在同一时刻多个查询修改同一数据的情况，就会有并发问题产生，在 mysql中可以有两个层面的并发控制：服务器层和存储引擎层

一种解决的方式是通过加锁机制来解决，但是加锁机制并不能支持并发的处理，因为在任意一个时刻只有一个进程可以进行操作，在大容量高并发系统中，这是一个瓶颈

读写锁

读取数据没有并发的问题产生，但是在读取数据的同时对数据进行操作比如更新，删除操作等，那么会导致未知的异常情况

可以通过两种类型的锁来解决上面的问题，它们是：共享锁与排他锁，也叫读锁与写锁

读锁：读锁是共享的，多个客户同一时刻读取同一资源，互相不干扰

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

锁粒度

提高共享资源并发性的方式就是让锁定的资源对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有资源

下面提供了mysql的两种最重要的锁策略

表锁

表锁是mysql最基本的锁策略，并且是开销最小的策略，它会锁定整张表

用户在对表进行写操作(增、删、改)前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作，没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的，表锁基于服务器层建立锁

行级锁

行级锁可以最大程度的支持并发处理，它是由存储引擎实现的，它一次只锁定事务需要访问的行记录

事务

mysql数据库的ACID特性

原子性(automicity):事务中的sql被当成是一个不可分割的单位，整个事务要么全部成功，要么全部失败

一致性(consistency):数据库总是从一个状态跳转到另一个状态，如果转账来说，在用户A转出200块钱但是用户B还没有收到钱的时候出现了问题，那么用户A的账户应该还是原来余额，转账成功后用户A才少了200块，B多了200块

隔离性(isolation): 不同事务之间应该是相互独立的，在A用户转出200块钱之前，用户查询的A用户余额与A用户转出之后但事务还没有结束查询到的A的余额通常来说应该是一致的

持久性(durability): 事务提交的修改操作将永久保存，即使数据库发生崩溃，但重启之后也会看到已经修改之后的结果，但这种持久性并不是说就一定是永久的，持久性也分为了很多个级别

隔离级别

未提交读(read uncommitted): 事务A读取到事务B修改但是还没有提交的数据，也称为脏读

提交读(read committed): 事务A不能读取到事务B还没有提交的数据，但是可以读取到已经提交的数据，这就导致事务A可能在事务B没开始之前读取的数据与事务B提交之后的数据结果不一致，因为事务B很有可能会修改事务A读取的数据，也称为不可重复读

可重复读(repeatable committed): 保证事务A在事务范围中读取到的一段范围中的记录结果一致的，但是这不可避免其他事务可能会在这个范围中插入新记录，这样就导致事务A读出来的结果会有多余的记录，在innodb中采用间隙锁来防止幻行的出现

序列读(serializable):多个事务串行读取

未提交度、提交度针对单条记录，重复读针对的是范围数据

死锁

多个事务(至少两个)在同一资源上相互引用，并请求对方已经锁定占用的资源，从而导致恶性循环的现象，这就是死锁

为了解决这个问题，数据库提供了各种死锁检测和死锁超时机制

一种是通过检测的方式检查是否存在死锁，如果存在就立即返回错误，另一种是当查询达到锁等待超时的设定后放弃请求，innodb目前处理死锁的方式是将持有最少行级排它锁的事务进行回滚

锁的行为和顺序和存储引擎相关，以同样的顺序执行语句，不同的存储引擎可能会产生死锁，有些又不会，当产生死锁之后，只能通过回滚其中一方事务才能打破死锁僵局

事务属性

自动提交设置

mysql默认情况是自动提交的，通过

show variables like ‘%commit%’;

Variable_name value

autocommit on

可以通过set autocommit = 1;设置开启或者关闭自动提交

当关闭了自动提交之后，mysql就必须通过rollback或者commit才能提交一个事务执行的结果

修改非事务类型表(myISAM或者内存表)不会有影响，相当于autocommit=on

隔离级别设置

通过select @@tx_isolation;或者

show variables like ‘%isolation%’;来查看当前mysql的隔离级别

通过set tx_isolation = ‘read uncommitted | read committed | repeatable read | serializable’或者

set [session/global] transaction isolation level [ read uncommitted | read committed | repeatable read | serializable ]

来设置当前事物隔离级别

session针对会话，global针对整个数据库

mysql隐式和显示锁定

在事务执行过程中，随时都可以锁定，这是由mysql根据不同的隔离级别自动加锁的，也可以通过显示的指定锁定

select ... lock in share mode # 乐观锁

select ... for update # 悲观锁

以上两种查询并不属于sql规范

mysql事务是在存储引擎层面实现的，所以不同的存储引擎可能效果会不太一样

mysql也支持lock tables和unlock tables但是它并不能代替事务处理，如果要使用事务，还是要选择支持事务处理的存储引擎

多版本并发控制(MVCC)

全称multiply version consistency control

MVCC可以理解为行级锁的变种，但是它并不需要加锁控制，所以更高效，虽然与行级锁实现不同，但是他们都实现了非阻塞式读操作，同时也只是锁定了必要的行

MVCC是通过保存在某一个时间点上的快照来实现的，每个事务从不同的时刻开始对同一张表的看到的数据可能是不一样的

MVCC根据不同的存储引擎，实现方式有所不同，分两种：乐观与悲观

innoDB的多版本并发控制原理：

innoDB的多版本是在行的后面保存两个隐藏列实现的，一个记录行的创建时间，一个记录行的删除时间，当然这两个列并不是记录真正的时间，而是记录了系统版本号，每开始一个事务，系统版本号都会自动增加，而事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行的记录版本号做比较

结合上面的两个列分析CRUD操作的具体实现

select

innoDB会根据以下两个条件查询结果记录

1.只查找行创建版本早于(小于等于)当前事务版本号的数据行，这样可以确保事务读取的记录要么是在事务之前就已经存在，要么就是在事务中创建的

2.行的删除版本要么没有定义，要么大于当前版本号，这可以确保事务读取到的行，在事务开始之前未被删除

insert

为每条插入的记录保存当前系统版本号为行版本号

delete

为每条删除的记录保存当前系统版本号为行删除版本号

update

innoDB插入一条新行，并保存当前系统版本号为行版本号，同时保存系统版本号到原来行的删除记录，作为行删除标识，update = insert + delete;

mysql存储引擎

mysql将每一个数据库保存为数据目录下的一个子目录，创建表时，会在该子目录下创建一个和表同名的.frm文件，保存表的定义与结构

查看user表状态

show table status like ‘user’ \G

查看user表具体字段信息

desc user

查看创建表时的语句结构

show create table user;

查看表状态.png

这里需要说明的一项是Data_free,对于myISAM引擎的数据库来说，删除并不会真正释放原来占用的空间，所以这里表示的是已经删除的行与后续可以被insert利用的空间

innoDB存储引擎

innoDB作为mysql的默认存储引擎，它将表数据存放在单独的表空间里面

(在windows中，这里以test/user表为例)

E:\MySQL\ProgramData\MySQL Server 5.5\data

在data数据目录中存放有test数据库子文件夹，里面用于存放test数据库中的各个表的结构定义.frm以及索引信息.ibd，在上层目录中ibdata1为所有表共享的表空间

innodb表空间

当innodb引擎中表的数据发生巨大变化时，该文件的大小也将随之发生变化，这一步是自动产生的。

innoDB采用MVCC(多版本并发控制)策略来支持高并发，支持四个隔离级别,READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ(default) | SERIALIZABLE ,并通过间隙锁防止幻读，间隙锁不仅锁定查询中的行，同时也会对索引中的间隙进行锁定，不允许幻行插入进来。

innoDB索引通过聚簇索引创建，聚簇索引对主键的查询性能很高，但是二级索引(也就是非主键索引)中必须包含主键列，如果表中的索引比较多的话，主键索引应该尽可能的小

innoDB通过一些机制和工具支持热备份，其他引擎是不支持的，其他引擎如果要备份数据，就必须先停止对表的写入操作

innoDB支持锁粒度更小的行级锁(存储引擎实现)

myISAM存储引擎

myISAM不支持行级锁及事务，崩溃后无法修复

myISAM会将表存储在三个文件中: .frm，.MYD，.MYI，.frm用于存储表结构，.myd用于存储数据，.myi用于存储索引

myisam表结构存储

myISAM只会将数据写入到内存中，然后等待os定期将数据刷到磁盘上，myISAM删除记录并不会马上更新数据文件.myd的大小，如果想要立马看到效果，可以通过使用optimize table来手动更新才能看到效果

加锁与并发

myISAM支持加锁机制，但是加锁是在整张表上进行的。读取时，对所有读到的表加共享锁，写入时对写入的表加排他锁，但是在读取查询时，也允许向表中插入新的记录

修复

对于myISAM引擎表，可以进行手工或者自动检查和修复，与事务支持的表修复有所区别，执行表的修复可能会丢失一部分数据，通过check table tablename查看表的错误，通过repair table tablename 修复错误的表，但是这个过程通常很漫长，即使mysql关闭也可以通过myisamchk命令检查

转换表的引擎

有三种方式可以修改表的引擎

Alter Table

alter table mytable ENGINE=InnoDB #注意大小写

该方法适用于任何引擎，但是执行很慢，因为它将原表中的数据复制到新表中，并加上读锁，一个替代方案是采用导入导出方法手动进行表复制

在转换表的过程中可能会丢失一些表的特性，比如讲InnoDB表转化为myISAM再转化为InnoDB,那么原来表中的外键会消失

导入导出(mysqldump)

使用mysqldump工具将数据导出到文件，然后修改文件中create table语句的存储引擎选项，注意同时修改表名，同一个数据库中不允许出现多个相同表名的数据表，即使是不同的存储引擎，另外，mysqldump会在每一个create table前面添加drop table语句

创建与查询(create insert)

综合第一种的高效和第二种的安全，不需要导出整个表的数据，首先要建立一个新的存储引擎表，然后利用insert .... select ...的语法来导数据

mysql> create table innodb_table like user;

mysql> alter table innodb_table set ENGINE=InnoDB

mysql> insert into innodb_table select * from user;

这样就把user表从myisam改变成innodb引擎的innodb_table表了

如果表中的数据量比较大，那么可能需要分批进行处理

mysql> start transaction;

mysql> insert into innodb_table select * from user where id between x and y;

mysql> commit;

还可以通过pt-online-schema-change的工具来完成上述操作