mysql的基本介绍简书_⼀、mysql简介
好久没更新简书了，最近遇上⼀些事情拖慢了读书进度，望见谅！由于本书篇幅较⼤，所以我决定今后每看完⼀章就更新⼀章的笔记，我会⼀如既往的和⼤家⼀起共同⾛下去，我相信读书路上你我同在
激萌.jpg
mysql流程
客户端发起对mysql数据库服务器的连接操作，mysql服务器内部通过连接池维护客户端连接，每⼀个客户端的查询操作都建⽴在⼀个独⽴的mysql连接上
当客户端发起sql查询时，mysql服务器⾸先会在查询缓存中查是否有已经存在的查询记录，有就返回，否则，开始解析客户端发来的sql 查询语句并进⾏分析优化，之后通过API调⽤存储引擎获取查询结果，并将结果保存到缓存中，同时返回给客户端
下⾯是整个流程的简略图
mysql请求流程.png
并发控制
只要存在同⼀时刻多个查询修改同⼀数据的情况，就会有并发问题产⽣，在 mysql中可以有两个层⾯的并发控制：服务器层和存储引擎层
⼀种解决的⽅式是通过加锁机制来解决，但是加锁机制并不能⽀持并发的处理，因为在任意⼀个时刻只有⼀个进程可以进⾏操作，在⼤容量⾼并发系统中，这是⼀个瓶颈
读写锁
读取数据没有并发的问题产⽣，但是在读取数据的同时对数据进⾏操作⽐如更新，删除操作等，那么会导致未知的异常情况
可以通过两种类型的锁来解决上⾯的问题，它们是：共享锁与排他锁，也叫读锁与写锁
读锁：读锁是共享的，多个客户同⼀时刻读取同⼀资源，互相不⼲扰
写锁：排他的，⼀个写锁会阻塞其他写锁和读锁的操作
锁粒度
提⾼共享资源并发性的⽅式就是让锁定的资源对象更有选择性，尽量只锁定需要修改的部分数据，⽽不是所有资源
下⾯提供了mysql的两种最重要的锁策略
表锁
表锁是mysql最基本的锁策略，并且是开销最⼩的策略，它会锁定整张表
⽤户在对表进⾏写操作(增、删、改)前需要先获得写锁，这会阻塞其他⽤户对该表的所有读写操作，没有写锁时，其他读取的⽤户才能获得读锁，读锁之间是不相互阻塞的，表锁基于服务器层建⽴锁
⾏级锁
⾏级锁可以最⼤程度的⽀持并发处理，它是由存储引擎实现的，它⼀次只锁定事务需要访问的⾏记录
sharepoint网站事务
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 ]
redis大key是什么来设置当前事物隔离级别
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根据不同的存储引擎，实现⽅式有所不同，分两种：乐观与悲观
mysql无法连接到服务器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;
numpy库是干嘛的mysql> insert into innodb_table select * from user where id between x and y;
mysql> commit;
还可以通过pt-online-schema-change的⼯具来完成上述操作
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论