Mysql进阶详解

MySQL

mysql基础详解

事务
事务原则

原子性: 事务操作必须是原子的,对于一个事务中的所有操作,要么全部执行(commit),要么全部不执行(rollback);

一致性:一致性指的是数据的完整性,即执行事务的前后,数据整体应该是一致的;

隔离性:它指的是一个事务的执行不能被其他事务所干扰,这里又涉及到并发的概念;

持久性:一个事务一旦提交,它对数据库中数据的改变就是永久性的。任何操作甚至是系统故障都不应该对其产生影响;

产生的问题

脏读:事务 A 读取了事务 B 当前更新的数据,但是事务 B 出现了回滚或未提交修改,事务 A 读到的数据就被称为 “脏数据”;

不可重复读:务 A 在执行过程中多次读取同一数据,但是事务 B 在事务 A 的读取过程中对数据做了多次修改并提交,则会导致事务 A 多次读取的数据不一致;

幻读:事务 A 在执行过程中读取了一些数据,但是事务 B 随即插入或删除了一些数据,那么,事务 A 重新读取时,发现多了一些原本不存在的数据,就像是幻觉一样,称之为幻读;

不可重复读和幻读的区别: 不可重复读指的是对原来存在的数据做修改,而幻读指的是新增或者删除数据。

事务隔离

1.Read Uncommitted

读不提交,这个是最低级别的事务隔离,是指读到了其他事务未提交的数据,可能会导致了脏读的发生;比如A修改了count=2->count=4,但是并没有提交,然后B读取到了count=4,之后A又回滚了使count=2;

2.Read committed

Read committed解决了脏读的问题,指会在其他事务提交事务之后,在进行数据读取,但其可能会导致不可重复读的发生,比如A在事务期间第一次读取count=2,然后B事务也加入进来,B修改了count = 3,并提交了,A第二次读取为count=3,从而导致前后读取到的结果不一致;

3.Repeatable read

Repeatable read是MySQL的默认事务隔离级别,在一次事务过程中,当前事务多次读取,都会返回第一次读取到的结果可以有效的解决脏读,不可重复读的问题,但可能会出现幻读,那么什么是幻读呢?幻读是指,A事务在其事务期间不会读到B事务的操作,比如B提交了一条insert事务,A不会读到多了一条,但是A事务可以对B提交了一条数据进行操作,A再次查询时,sql返回的语句多了一条,导致了幻读的发生;

所以不可重复读和幻读的区别是不可重复读重点在于update,而幻读的重点在于insert和delete;

4.Serializable

Serializable是指串行化,即使每一个事务先后进行,可以避免脏读,不可重复读,幻读;但其执行效率太慢,基本上不会使用;

mysql可以通过 select @@transaction_isolation查询默认的事务隔离级别;通过**SET SESSION transaction_isolation=‘SERIALIZABLE’;**修改mysql的事务隔离级别;但要主要binlog_format的级别,binlog_format默认为statment,对于read committed及以下的隔离级别,要修改为binlog_format=row;

事务执行过程

mysql事务执行一般经过三个过程:server层,存储引擎,数据

连接器:判断用户登录以及用户权限;缓存命中,走缓存,直接返回查询结果;缓存没命中,到达分析器

缓存: MySQL的缓存主要的作用是为了提升查询的效率,缓存以key和value的哈希表形式存储,key是具体的SQL语句,value是结果的集合。

分析器: 对SQL语句进行分析,包括预处理与解析过程;词法分析与语法分析

优化器: 对SQL语句进行优化;

执行器: 调用存储引擎,执行具体的SQL操作;

存储引擎: undo log --> redo log --> binlog -->commit (存redo log --> 存binlog); (参考)

锁机制

在mysql各个引擎中,使用了三种锁机制,分别是行级锁定,页面锁定,表级锁定;三种表的细度依次增加,消耗的资源也依次减少;下面对三种锁机制进行说明:

1.行级锁定

行级锁定在mysql各个引擎中,锁定对象颗粒度最小的,所以锁定资源的竞争发生的可能性也是十分小的,因此可以在最大可能下提高数据库服务器处理高并发的能力;但也存在着资源消耗大,锁定或释放的时间长的问题,也有可能会发生死锁;使用行级锁定的引擎主要是innoDB;

2.页面锁定

页面锁定是介于行级锁定和标记锁定之间的锁机制,也有可能会发生死锁,在数据库服务器中不是很常见;

3.表级锁定

表级锁定是锁定对象颗粒度最大的锁机制,所以其实现逻辑十分简单,会直接锁定整个表,因此,表级锁定的锁定和释放速度快,资源消耗也小,不会发生死锁;但出现锁定资源竞争发生的可能大,导致处理高并发的能力小;主要是一些非事务性的存储引擎,比如MyISAM,MEMORY,CSV等

表级锁定-MyISAM

现在,我们使用常用的MyISAM引擎对表级锁定的实现进行简单说明,在mysql中表级锁定有两种模式,分别为表共享读锁、表独占写锁;

1.表共享读锁

表共享读锁: 在MyISAM中,当一个表得到一个读锁时,其他进程事务也可以对该表进行读操作,但不能进行写操作;

2.表独占写锁

表独占写锁:在MyISAM中,当一个表得到写锁时,其他进程事务既不能对该表进行写操作,也不能进行读操作,直到该表释放写锁;那么在MyISAM读锁与写锁之间的优先级是怎么样的呢?

在MyISAM引擎中,如果读锁和写锁同时申请同一个表,写锁的优先级会更高,写锁会先于读锁对表进行加锁操作,即使读锁先于写锁申请,它们同时在等待队列中,写锁仍然会先于读锁对表进行加锁操作;

3.MyISAM-表锁的实现

MyISAM在执行查询语句前,会自动给所涉及到的表加读锁,在执行更新操作时,也会自动给所涉及到的表加写锁。

4.MyISAM中表锁的优化

在MyISAM中使用的表级锁定比页面锁定和行级锁定占用的资源都要少,但由于表级锁定的颗粒度比较大,所以导致了其并发性能较为不好,进程之间相互竞争系统资源的可能性较大;优化的关键为提到表级锁定的并发能力?那么应该怎么提高并发能力呢?在表级锁定的基础上,可以尽量使可以并发执行的进程尽可能的并发执行;

行级锁定-innoDB

在mysql中,mysql本身并没有实现行级锁定,行级锁定是其各种引擎自己实现的;行级锁是颗粒度最小的锁, 并发能力强,可以很好的处理并发事务,但也存在着容易出现死锁的问题,对系统资源的消耗比较高;在innoDB中,行级锁定是体现在对索引项的锁定上的,如果操作没有使用索引,锁定级别就会是表级锁定。当使用同一索引即使数据集不一样,行级锁定也会对其进行锁定;

行级锁定具体实现

在innnoDB中行级锁定分为共享锁和排他锁两种,同时也存在着意向锁的概念,使得行级锁定和表级锁定可以共存,一个事务需要获得自己所需资源时,如果所需资源被一个共享锁锁定,那么当前事务也可以在资源上加一把共享锁,但不能加排他锁;如果遇到的是排他锁时,就必须等待到排他锁被释放掉,自己才有机会对所需资源进行加锁操作;意向锁的作用就是当一个事务需要获取资源时,如果资源被排他锁锁定,就可以在表上加上意向锁,如果需要共享锁就在表上加上意向共享锁,否则,也可以加上意向排他锁;和共享锁、排他锁一样,意向共享锁可以多个共存,但意向排他锁最多只可以有一个;

间隙锁

间隙锁是指在执行一个范围条件查询时,在条件范围内但不存在的数据也会被锁定,比如a>100,101存在会被锁定,102不存在也会被锁定,因此间隙锁可以很好的解决幻读的问题;==注意:==如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

InnoDB使用间隙锁的目的:

(1)防止幻读,以满足相关隔离级别的要求;
(2)为了满足其恢复和复制的需要;

InnoDB_lock_wait_timeout
  1. 解决死锁问题;
  2. 解决并发高时,资源耗尽而导致数据库崩溃的问题;
悲观锁

当我们要对数据库中的一条数据进行修改的时候,为了避免同时被其他人修改,最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前锁定,再修改的方式被称为悲观并发控制(PCC);

悲观锁的实现原理:在mysql中必须关闭自动提交,通过for update字段对其进行加锁;但是,在使用悲观锁的时候,一定需要注意使用索引,否则行锁将会上升为表锁,引起系统问题;

乐观锁

乐观锁假设数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测;

乐观锁的实现原理: 乐观锁其实是对 CAS(compare-and-swap)的实现:在做修改时,检查当前的环境是否与预定义的一致,如果一致则可以提交;否则,重试或抛异常;

乐观锁存在的问题:

  1. ABA问题,解决方法是定义一个只增不减的字段;
  2. 当遇到高并发时,只可能会有一个线程修改成功,会造成大量的线程失败或重试;

悲观锁和乐观锁的适用场景:

  • 悲观锁的适用场景 :写入操作比较频繁的场景 ,如果有大量的读取操作,每次读取都需要加锁,会增加锁开销,降低系统的吞吐量;
  • 乐观锁的适用场景:读取操作比较频繁的场景,如果有大量的写入操作,冲突的可能性会剧增,降低系统的吞吐量;
MVCC

MVCC(Mutil-Version Concurrency Control),就是多版本并发控制,MVCC 是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问 ;在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程;这就使得别的事务可以修改这条记录,反正每次修改都会在版本链中记录。

版本链

在InnoDB引擎表中,它的聚簇索引记录中有两个必要的隐藏列:

  1. **trx_id:**用来存储的每次对某条聚簇索引记录进行修改的时候的事务id;
  2. roll_pointer:每次对哪条聚簇索引记录有修改的时候,都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针,它指向这条聚簇索引记录的上一个版本的位置;
ReadView

ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务,也就是开始了还未提交的事务,ReadView会记录一个事务id的范围,如果当前事务要访问的事务id<readview,则说明要访问的事务之前已经提交了,可以访问;如果当前事务要访问的id在readview的范围之内,则说明该事务还没有提交,则不可以访问;如果当前事务要访问的事务id>readview,则说明该事务是第一次读写时未开始的事务,不可以访问;

可重复读和已提交读ReadView的区别

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView;

而可重复读隔离级别则在第一次读的时候生成一个ReadView,之后的读都复用之前的ReadView;

索引

索引是存储引擎用于快速找到记录的一种数据结构;索引本身就是有序的;

索引的优缺点

优点: 1. 减少扫描的数据量,加速查询;2. 减少或完全消除数据库的排序操作(ORDER BY),因为索引是有序的;3. 将服务器的随机 IO 变为顺序 IO;

缺点:1. 索引会占据额外的存储空间(毕竟它是数据结构),包括磁盘和内存;2. 由于对数据需要排序,自然会影响到数据更新(插入、更新、删除)的速度

索引的实现原理

对于 MySQL 来说,服务器层并不会实现索引,而是交给了存储引擎。所以,你应该知道了,不同的存储引擎自然也就会有各自不同的实现。对于 InnoDB 而言,它的内部实现使用的是 B+ 树

B+树的优势:

  1. io次数少: b+树中间节点只存索引,不存在实际的数据(一页能够有更多索引)
  2. 性能稳定: b+树数据只存在于叶子节点,查询性能稳定,即每次都到达叶子结点
  3. 范围查询简单: b+树不需要中序遍历,遍历链表即可。
索引的分类
  1. 普通索引: 针对于单个列创建的索引,之所以说它普通是因为它对列值没有什么限制,允许被索引的列包含重复的值;
  2. 主键索引: 它是一种特殊的唯一索引,在一张表中只能定义一个(但不是必须)主键索引;
  3. 唯一索引: 正如它的关键字一样,它要求列值是唯一的,这个索引保证了数据记录的唯一性;
  4. 联合索引: 也被称为复合索引,它是将多个列值绑定在一起作为索引;
  5. 聚簇索引: 实际上并不是一种索引类型,而是一种存储数据的方式,且是将索引和数据存储在一起。InnoDB 规定一个表只能有一个聚簇索引,且会使用主键来创建;
  6. 前缀索引: 当表中的数据列是字符型,且大多数长度都比较长时,就可以考虑使用列值的一部分前缀作为索引,这也就被称作是前缀索引;
  7. 覆盖索引: 当一个索引包含需要查询的所有字段时,就称之为覆盖索引;
聚簇索引

既然数据表只有一个聚簇索引,那么,所有其他的索引就应该是 “非聚簇索引”,它们的区别又是什么呢 ?对于聚簇索引来说,索引即数据,所以,如果以主键去查询数据,那么只需要一次索引查找即可。对于非聚簇索引而言,实际存储的是记录主键,所以,还需要根据主键再做一次查询才可以获取到数据,这也就是我们通常所说的 “非主键的二次查询”;

联合索引

联合索引会遵循最左前缀匹配原则,也就是常说的 “最左优先原则”。它的表现形式是在检索数据时从联合索引的最左边开始匹配,在检索数据时从联合索引的最左边开始匹配;你需要充分理解它的 “最左原则”,这会让你避免创建冗余的索引;另外,你还需要知道一些索引失效的条件:

  1. 在索引列上执行计算、函数、类型转换等操作;

  2. 使用不等于(!= 或 <>);

  3. 使用 IS NULL,IS NOT NULL;可以使用exists关键字支持索引;

  4. LIKE 以通配符(%)开头;用全文索引支持索引;

最左前缀匹配原则:索引(A,B,C) --> 索引(A),(A,B),(A,B,C)

连接
等值连接/不等值连接

2个表会先进行笛卡尔乘积运算,生成一个新表格,占据在电脑内存里,当表的数据量很大时,很耗内存,这种方法效率比较低,尽量不用;

select * from A,B where A.id=B.nameid

自然连接

特殊的等值连接,自然连接要求两个关系中进行比较的必须是相同的属性组,值域相同;

select * from A natural join B on A.id=B.nameid

内连接

2个表根据共同ID进行逐条匹配,不会出现笛卡尔乘积的现象,效率比较高,优先使用这种方法;

select * from A inner join B on A.id=B.nameid

外连接

无论2个表之间属性值是否满足连接条件,一个主表的行都会被连接到;分为两种方式:

  1. 左外连接:以左边的表作为主表;
  2. 右外连接:以右边的表作为主表;

select * from A left/right join B on A.id=B.nameid

全连接

select * from A full JOIN B ON A.id=B.nameid

交叉连接(笛卡尔积)

select * from A cross JOIN B

mysql引擎
引擎的分类

innoDB,MyIsam,Memory,TokuDB;

InnoDB和MYISAM的区别
  1. innoDB提供事务支持,MyIsam不支持事务;
  2. InnoDB提供行锁和外健约束,myisam不支持;
  3. InnoDB没有保存表的行数
  4. 索引底层不同;
  5. innoDB适合用于更新和查询都相当的频繁,多重并发;要求事务,或者可靠性要求比较高;外键约束,MySQL支持外键的存储引擎只有InnoDB;myisam适合用于做很多count的计算;查询非常频繁

关于索引的不同:

innoDB:索引的结构为B+树,但是innoDB索引文件本身就是数据文件,即B+树的叶子节点存储的数据本身;而且innoDB辅助索引的B+树存放的是主键值,所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。也最好使用自增主键,便于建树;

myisam:索引结构为B+树,但B+树的叶子节点存储的内容为实际数据的地址

日志
Redo log

redo日志是一种基于磁盘的数据结构,用于在崩溃恢复期间纠正不完整事务写入的数据;redo日志(redo log)用来保证事务的持久性,即事务ACID中的D;记录mysql操作在恢复时会按照redo log文件执行;

Undo log

undo log主要记录的是数据的逻辑变化,为了在发生错误时回滚之前的操作,需要将之前的操作都记录下来,然后在发生错误时才可以回滚。undo日志用于事务的回滚操作进而保障了事务的原子性。只将数据库逻辑地恢复到原来的样子;

MySQL优化
sql语句优化
  1. 使用limit语句对查询的数量进行限制;
  2. 使用join连接字段代替子查询;
  3. 拆分复杂的delete或insert语句;
  4. 避免使用select * 语句进行查找;
选择合适的数据类型
  1. 优先使用可存下数据的最小的数据类型,int < date,time < char < varchar < blob;
  2. 数值型的开销小于文本型;
  3. 使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar;
  4. 优先将字段定义为not null;
  5. 尽量少用text,非用不可最好分表;
选择合适的索引项
  1. 查询频繁的列;
  2. 长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好;
  3. 离散度大(不同的值多)的列,放在联合索引前面;
主从复制(参考文档)
MySQL复制

为了减轻主库的压力,应该在系统应用层面做读写分离,写操作走主库,读操作走从库;分散了数据库的访问压力,提升整个系统的性能和可用性,降低了大访问量引发数据库宕机的故障率。

复制策略

MySQL复制支持多种不同的复制策略,包括同步、半同步、异步和延迟策略等:

  1. 同步策略: aster要等待所有Slave应答之后才会提交
  2. 半同步策略: Master等待至少一个Slave应答就可以提交。
  3. 异步策略: Master不需要等待Slave应答就可以提交。
  4. 延迟策略: Slave要至少落后Master指定的时间。
复制模式

根据binlog日志格式的不同,MySQL复制同时支持多种不同的复制模式:

  1. 基于语句的复制:记录每一条更改数据的sql,binlog文件较小,节约IO,性能较高,但是可能存在一致性的问题;
  2. 基于行的复制:记录每一行数据的更改细节,binlog文件较大,性能较低,会造成复制的延迟;
  3. 混合复制:
实现主从复制

mysql主从复制需要三个线程,master(binlog dump thread)、slave(I/O thread 、SQL thread):

**binlog dump线程: **

当主库中有数据更新时,那么主库就会根据按照设置的binlog格式,将此次更新的事件类型写入到主库的binlog文件中,此时主库会创建log dump线程通知slave有数据更新,当I/O线程请求日志内容时,会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程;

I/O线程:

该线程会连接到master,向log dump线程请求一份指定binlog文件位置的副本,并将请求回来的binlog保存到本地的relay log中,relay log和binlog日志一样也是记录了数据更新的事件,它也是按照递增后缀名的方式,产生多个relay log( host_name-relay-bin.000001)文件,slave会使用一个index文件( host_name-relay-bin.index)来追踪当前正在使用的relay log文件;

SQL线程:

该线程检测到relay log有更新后,会读取并在本地做redo操作,将发生在主库的事件在本地重新执行一遍,来保证主从数据同步。此外,如果一个relay log文件中的全部事件都执行完毕,那么SQL线程会自动将该relay log 文件删除掉。

其他mysql问题
删除一个表中的所有数据关键字:Truncate

Truncate删除表中的所有数据,这个操作不能回滚,也不会触发这个表上的触发器,TRUNCATE比delete更快,占用的空间更小;

创建视图

create view viewName as select * from A;

  • 逻辑上的独立性,屏蔽了真实表的结构带来的影响。
  • 安全性,用户只能查询和修改能看到的数据。
  • 简化了操作,把经常使用的数据定义为视图。
关键字的优先级

from > on > join > where > group by > having > order by > limit

联合查询

使用关键字union将多个查询连接在一起,注意的是所有select语句中的字段数目要相同 ,返回的数据会被追加到第一条select语句的属性值(字段)下;

select * from B UNION select * from A

  • 0
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值