深入了解MySQL,一篇简短的总结

2019/04/10 10:10
阅读数 17

MySQL的基本语法

这里作为MySQL部分模块的深入了解,大部分都是理论方面的笔记,不会写具体用法。

具体用法会记录在下面这个随笔分类下,不过暂时还没更新完,等过段时间会更新下事务、存储过程、索引等用法,虽然都很简单,就当做个完整的笔记。

https://www.cnblogs.com/lbhym/category/1493919.html

 

一个关系型数据库的基本模块

以下模块也不一定是各大数据库实际的模块,但是都差不多,只是大概了解一下数据库的架构。

除了硬件,也就是存储部分,是由磁盘组成,在软件部分主要分为一下几个模块:

存储管理:用于管理数据格式,把物理数据通过逻辑的形式组织表现出来,即数据实际都是存在物理的磁盘当中,需要在软件层面做一个逻辑上的组织管理。

缓存机制:影响数据库性能的一大问题就是IO,所以会将取出来的数据放入内存当中,使用时直接从内存返回。即使目前非常快的固态硬盘也远远比不上内存的速度。

SQL解析:将SQL解析成机器可读的语言。

日志管理:记录用户对数据库的操作。

权限划分:字面意思,非常常见的一个功能,将不同的用户分为不同的角色,操作权限也不同。

容灾机制:这个部分较为复杂,大概作用就是当数据库发生异常灾难时该怎么恢复。

索引管理:优化数据库查询效率。

锁管理:使数据库支持并发操作。

 

Mysql索引的实现,B+树

索引是优化数据库查询效率,普通的查询是全表查询,当数据量过大时会严重影响性能。

而索引就像一本词典的目录,在数据量较大时会增加查询效率,但是如果频繁的更新或删除数据,同时也需要去维护索引,反而会降低性能,所以索引不宜太多。

索引实际上也是一个文件,既然需要高效的查找当然也需要一个好的数据结构,关于索引的实现,有B树、二叉查找树等,这里只讲MySQL的B+树。

B+树的特点和插入删除过程想过很多文字描述,但是总有点说不清。推荐看看这篇博客,过程图文表现的很清楚。

https://blog.csdn.net/login_sonata/article/details/75268075

 

为什么会选择B+树?

B+树的一个特点就是其叶子结点均有一个链指针指向下一个叶子结点,再加上其是有序的,所以我们进行范围查询时,比如查询>10的数据,只需要先找到10,再直接通过叶子结点的指针就能找到其余数据。

而其他结构还需从根节点出发接着找。

 

联合索引最左匹配原则

联合索引,有的叫组合索引、有的叫复合索引,叫法无所谓,大概是那个意思就行。

1.一张表里有字段A、B,当我们需要查询where A=‘xxx’ and B='xxx',在这种场景下我们就可以使用联合索引。

而最左匹配原则就是,当创建索引时,语句如下

alter table TABLENAME add index index_name(A,B)

其中A在左边,那么如果我们只查询A时,会用到这个联合索引,而只查询B时,不会用到这个联合索引

2.还有种情况,在联合索引中,mysql会从左往右匹配,直到遇到>、<、between、like就会停止。

比如联合索引中有四个字段A,B,C,D。where A=1 and B=2 and C>3 and D=4。其中ABC会用到索引,而D不会。如果在定义索引时交换C,D的位置,ABCD就都会使用索引

alter table TABLENAME add index index_name(A,B,C,D)-->alter table TABLENAME add index index_name(A,B,D,C)

所以最左匹配原则是依据定义索引时的顺序,查询时顺序如何不影响(因为mysql查询优化器会帮我们优化查询顺序)。

最左匹配原则的原理

索引的底层是B+树,联合索引也一样。但是联合索引特殊的就是有多个值,而构建B+树只需一个值,mysql选择最左的那一个字段当值。

 

上图是一个联合索引下的B+树,假如字段分别对应(A,B),可以发现A的值是有序的(1,1,2,2,3,3),B是无序的(1,2,1,4,1,2),所以当我们直接查找B=2时是无法通过索引找的。

因为这个B+树是按A的值形成的,B的值完全不符合B+树特性,所以无法单独找到B。

那为先找到A后,就能找到B了?

大家仔细观察这个树,在A相等的区间内,B是有序的。

还有就是,为什么遇到范围查询就停止了。范围查询是针对全表的,而非最左的字段只是区间内有序。

 

MySQL两种引擎:MyISAM和InnoDB

简短地说

Myisam:是非聚集索引,不支持事务,只支持表级锁。

InnoDB:是聚集索引,支持事务,默认是行级锁,支持表级锁。

下面就这三个方面一一说明。

聚集索引

定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。即索引键值的逻辑顺序决定了表中相应行的物理顺序。

网上有个很好的例子,就是把聚集索引比作一本字典的拼音目录。而数据就是字典里面的字。拼音目录是按照一定顺序排列的,那么字典后面的字也一定是根据拼音的顺序排列的。

当我们在拼音B处插入一个新汉字,那么B后面所有的汉字要向后移动,不可能是加在字典最后面的,因为它得按拼音顺序排列。

所以聚集索引适用于:范围查询,比如<,>,=,between等,还有分组group by,因为B+树是有序的,所以分组的效率也更高。

不适用于:频繁更改的列。

非聚集索引

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

即索引在一个地方,数据在一个地方,索引带有指向数据的指针。这在物理上也体现出来了,在数据库目录下,索引存在.MYI文件下,数据存在.MYD文件下。上面的聚集索引,数据和索引在同一个文件下.ibd。

而数据的顺序和索引不一样,所以聚集索引适用于:频繁修改索引列。

 

表级锁和行级锁

两种锁即字面意思。表级锁,mysql中最大粒度得到锁,锁住整张数据表。行级锁,mysql中最小粒度的锁,只锁住操作的那一行,本表中其他行不锁。

而针对不同的操作,锁的具体类别又有所不同,为了不混淆,大家可以把表级锁和行级锁当作锁的范围。而下面说到的锁当作具体的分类。

当Myisam查询时:会在整张表的范围上加上读锁,又叫共享锁。当加上读锁,其他sql的想增删改时就会被阻塞,必须等待查询完毕。又叫共享锁的原因是,可以同时在同一张表内做查询。

当Myisam增删改时:会在整张表的范围上加上写锁,又叫排他锁。当加上写锁,其他sql不论是增删改还是查询都会被阻塞。又叫排他锁的原因是,即使我修改的是1-10行数据,你查询第11行数据也会被阻塞。

上面说的是MyISAM引擎的情况,InnoDB在读锁、写锁上的逻辑也是一样的,只是锁范围变成了行。

大家在mysql上实验InnoDB的锁时:要注意,InnoDB对select进行了优化,并未对select语句加上读锁,也就是非阻塞select,大家可以在select语句后面加上lock in share mode手动加上读锁。

具体怎么实验:1.往表中插入几百万条的数据,增删改查时在范围内进行,这样就可以模拟阻塞环境了。2.InnoDB支持事务,不过其是自动提交的,还得用set autocommit=0取消自动提交。这样在commit之前就是阻塞状态。

 

事务

简单的说,就是使多步操作具有原子性。即在事务内执行多条SQL语句,要么全部成功,要么全部不成功,不会存在部分执行成功,而导致数据不一致的情况。

事务具有四大特性:

  • 原子性,上面说到的。
  • 一致性,事务前后数据的完整性必须保持一致。
  • 隔离性,多个用户事务并发进行时,不能互相干扰。
  • 持久性,一旦事务提供,其修改的结果是永久存在的。

 重点说说隔离性。如果事务间没有隔离会发生什么情况呢。

假如有一个场景:事务A获取到一个数据为900,此时另外一个事务B在事务A提交之前就修改了数据到800,并提示成功。

而事务A的操作数据依旧是查询时的900,并进行+100,变成了1000。显然结果是不对的,这就是更新丢失问题。

这个问题是不是很像多线程并发操作,但是没有锁时就会发生的问题。实际上,事务的隔离就是用锁来实现的。

了解隔离级别之前需要知道几个概念:

脏读:事务A读取到了事务B还未提交的数据。

不可重复读:事务A多次查询数据时,事务B对该数据做了修改并提交,此时事务A发现多次查询前后结果不一样。

在我们看来仿佛没什么问题,一个事务修改了数据,一个事务查询到了修改后的结果。但有个问题就是,事务A是多次查询,如果他没有多次查询,直接在第一次查询的结果上操作,那么是不是就会出现问题。

幻读:事务A查询了一段数据集,事务B修改了事务A的数据集范围内的某些数据,导致查询结果和实际结果不一致。

了解了这三个概念后就可以很好的理解几个隔离级别:

隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted) 可能 可能  可能
已提交读(Reda committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable) 不可能 不可能 不可能

 

 

 

 

 

 

 

 

 

 

隔离级别越高,安全性越高,性能也越低,所以要根据实际业务设置不同的隔离级别。

举个例子:如果设置的是可串行化隔离级别,事务A对TableA的1-10行进行操作,事务B即使对TableA的第11行进行操作也会被阻塞。对于这种业务没必要设置为可串行化隔离级别。

原文出处:https://www.cnblogs.com/lbhym/p/12334582.html

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部