MySQL可以分为应用层,逻辑层,数据库引擎层,物理层。
应用层:负责和客户端,响应客户端请求,建立连接,返回数据。
逻辑层:包括SQK接口,解析器,优化器,Cache与buffer。
数据库引擎层:有常见的MyISAM,InnoDB等等。
物理层:负责文件存储,日志等等。
又叫做读写锁。
共享锁也称为读锁,相互不阻塞,多个客户在同一时刻可以同时读取同一个资源而不相互干扰。
排他锁也称为写锁,会阻塞其他的写锁和读锁,确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源。
表级锁: 对当前操作的整张表加锁,实现简单,加锁快,但并发能力低。
行锁: 锁住某一行,如果表存在索引,那么记录锁是锁在索引上的,如果表没有索引,那么 InnoDB 会创建一个隐藏的聚簇索引加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
Gap 锁:也称为间隙锁: 锁定一个范围但不包括记录本身。其目的是为了防止同一事物的两次当前读出现幻读的情况。
(1)防止间隙内有新数据被插入。
(2)防止已存在的数据,更新成间隙内的数
Next-key Lock: 行锁+gap锁。
乐观锁:对于数据冲突保持一种乐观态度,操作数据时不会对操作的数据进行加锁,只有到数据提交的时候才通过一种机制来验证数据是否存在冲突
。
悲观锁:对于数据冲突保持一种悲观态度,在修改数据之前把数据锁住,然后再对数据进行读写,在它释放锁之前任何人都不能对其数据进行操作
,直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁,然后才可以对数据进行操作,一般数据库本身锁的机制都是基于悲观锁的机制实现的
。
InnoDB 是 MySQL 的默认事务型引擎,支持事务
,表是基于聚簇索引建立的。支持表级锁和行级锁
,支持外键
,适合数据增删改查都频繁
的情况。
InnoDB 采用 MVCC(多版本并发控制) 来支持高并发,并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ,并通过间隙锁策略防止幻读,间隙锁使 InnoDB 不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定防止幻行的插入。 MVCC 多版本并发控制解决读写阻塞的问题。
MySQL5.1及之前,MyISAM 是默认存储引擎。MyISAM不支持事务,Myisam支持表级锁,不支持行级锁,表不支持外键
,该存储引擎存有表的行数,count运算会更快。适合查询频繁,不适合对于增删改要求高
的情况
Memory存储引擎将所有数据都保存在内存,不需要磁盘 IO。支持哈希索引,因此查找速度极快
。Memory 表使用表级锁,因此并发写入的性能较低
。
索引是存储引擎中用于快速找到记录的一种数据结构。在关系型数据库中,索引具体是一种对数据库中一列或多列的值进行排序的存储结构。
引入索引的好处:提高数据查询的效率。
坏处:索引也是一个文件需要占用内存,另外还有就是每次对数据的修改,索引也需要相应的改变。
为了提高数据查询的效率。索引对数据库查询良好的性能非常关键,当表中数据量越来越大,索引对性能的影响越重要。
数据结构角度
B+Tree索引 |BTree索引 哈希索引 全文索引
物理结构角度
主键索引(聚簇索引) 叶子节点存的是整行的数据,非聚簇索引(二级索引):叶子节点存的主键的值
B-Tree 是一种自平衡的多叉树。每个节点都存储关键字值。其左子节点的关键字值小于该节点关键字值,且右子节点的关键字值大于或等于该节点关键字值。
B+树也是是一种自平衡的多叉树。其基本定义与B树相同,不同点在于数据只出现在叶子节点,所有叶子节点增加了一个链指针,方便进行范围查询
。
B+树中间节点不存放数据,所以同样大小的磁盘页上可以容纳更多节点元素,访问叶子节点上关联的数据也具有更好的缓存命中率
。并且数据顺序排列并且相连,所以便于区间查找和搜索。
B树每一个节点都包含key和value,查询效率比B+树高。
B树是多路平衡二叉树,每个几点包含了key和value,而B+树只有叶子节点才包含value,并且叶子节点是相连的。
B+树的非叶子结点只包含导航信息,不包含实际的值,所有的叶子结点和相连的节点使用链表相连,便于区间查找和遍历
。
B+ 树的优点在于:
IO次数更少:由于B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。
遍历更加方便:B+树的叶子结点都是相链的,因此对整棵树的遍历只需要一次线性遍历叶子结点即可。
B树的优点:
其优点在于,由于B树的每一个节点都包含key和value,因此经常访问的元素可能离根节点更近,因此访问也更迅速
选择B+树的原因:
哈希索引对于每一行数据计算一个哈希码,并将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。只有 Memory 引擎显式支持哈希索引。
Hash索引不支持范围查询,无法用于排序,也不支持部分索引列匹配查找。
InnoDB对于频繁访问的二级索引的数据,会在内存中基于B+树索引之上再创建一个哈希索引,这也被称为自适应的Hash索引。
辅助索引是非聚簇索引,叶子节点不包含记录的全部数据,包含了一个主键用来告诉InnoDB哪里可以找到与索引相对应的行数据。
而回表就是根据上边的那个主键通过聚簇索引查找到相应的位置,获得数据。
联合索引是指对表上的多个列的关键词进行索引。
对于联合索引的查询,如果精确匹配联合索引的左边连续一列或者多列,则mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配。Mysql会对第一个索引字段数据进行排序,在第一个字段基础上,再对第二个字段排序。
覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值,不需要回表查询,即索引本身存了对应的值。
红黑树的出度为 2,而 B+Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B+Tree 大非常多,IO次数很多,导致会比较慢,因此检索的次数也就更多。
B+Tree 相比于 B-Tree 更适合外存索引,拥有更大的出度,IO次数较少,检索效率会更高。
对于select * from 主键=XX,基于主键的普通查询仅查找主键这棵树,因为主键上建立的有聚簇索引,主键索引中的叶子节点存储的整行的全部信息。
对于select * from 非主键=XX,基于非主键的查询有可能存在回表过程,因为如果非主键建立的索引中不能包含查询的全部的信息,需要根据主键id在根据主键建立的索引树上进行查找,这个过程叫回表。
不一定,当查询语句的要求字段全部命中索引,不用回表查询。如select 主键 from 非主键=XX,此时非主键索引叶子节点即可拿到主键信息,不用回表。 Eg,建立的联合索引(name,age) 且表中的主键为id,如下查询语句,select id,name,age from 表名 where name='cyl' 因为命中联合索引,且索引中存储的有主键id的值,所以不需要回表。
有一个特别的情况需要注意 :就是不满足最左前缀原则,但是查询的字段都在某个联合索引内部的话,会形成索引覆盖,而采用索引进行查询。
explain关键字用于分析sql语句的执行情况,可以通过他进行sql语句的性能分析。
type:表示连接类型,从好到差的类型排序为
key:显示MySQL实际决定使用的键。
key_len:显示MySQL决定使用的键长度,长度越短越好
Extra:额外信息
Using index condition :索引下推优化,就是在使用索引的基础上进行了优化,具体的如下:
当需要访问整个表行时,ICP用于range、ref、eq_ref和ref_or_null访问方法。
ICP 这种优化是如何工作的,首先考虑
当没有使用ICP时索引扫描是如何进行的:
1.获取下一行,首先通过读取索引元组,然后使用索引元组定位和读取整个表行。
2.检查WHERE条件中应用于此表的部分。根据检查结果接受或拒绝行。
使用ICP,则会变成下面这样:
1.获取下一行的索引元组(但不是整个表行)。
2.检查应用于此表的WHERE条件的部分,仅使用索引列即可进行检查。如果条件不满足,则进入下一行的索引元组。(因为索引条件下推到了存储引擎层)
3.如果条件满足,则使用index元组定位和读取整个表行。
4.测试应用于此表的WHERE条件的其余部分。根据测试结果接受或拒绝行
redo log: 存储引擎级别的log(InnoDB有,MyISAM没有),该log关注于事务的恢复.在重启mysql服务的时候,根据redo log进行重做,从而使事务有持久性
。
undo log:是存储引擎级别的log(InnoDB有,MyISAM没有)保证数据的原子性
,该log保存了事务发生之前的数据的一个版本,可以用于回滚,是MVCC的重要实现方法之一。
bin log:数据库级别的log,关注恢复数据库的数据。
总共含有四个属性:ACID
简称多版本并发控制。即同一条记录在系统中存在多个版本。其存在目的是在保证数据一致性的前提下提供一种高并发的访问性能。
对数据读写在不加读写锁的情况下实现互不干扰,从而实现数据库的隔离性,在事务隔离级别为读提交和可重复读中使用到。
在InnoDB中,事务在开始前会向事务系统申请一个事务ID,该ID是按申请顺序严格递增的。每行数据具有多个版本,每次事务更新数据都会生成新的数据版本,而不会直接覆盖旧的数据版本。
在可重复读级别下,只会在事务开始前创建视图,事务中后续的查询共用一个视图。
而读提交级别下每个语句执行前都会创建新的视图。
因此对于可重复读,查询只能看到事务创建前就已经提交的数据。而对于读提交,查询能看到每个语句启动前已经提交的数据。
利用undo log保障原子性
。该log保存了事务发生之前的数据的一个版本,可以用于回滚,从而保证事务原子性。
利用redo log保证事务的持久性
,该log关注于事务的恢复.在重启mysql服务的时候,根据redo log进行重做,从而使事务有持久性。
利用undo log+redo log保障一致性
。事务中的执行需要redo log,如果执行失败,需要undo log 回滚。
MySQL通过binlog(二进制日志)实现主备一致。binlog记录了所有修改了数据库或可能修改数据库的语句,而不会记录select、show这种不会修改数据库的语句。在备份的过程中,主库A会有一个专门的线程将主库A的binlog发送给 备库B进行备份。其中binlog有三种记录格式:
记录类型:redo log是InnoDB引擎特有的,只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的,会记录所有引擎对数据库的修改。
日志类型上:redo log是物理日志,记录的是在具体某个数据页上做了什么修改;binlog是逻辑日志,记录的是这个语句的原始逻辑。
空间上:redo log是循环写的,空间固定会用完;binlog是可以追加写入的,binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
InnoDB通过redo log保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。
WAL的全称是Write-Ahead Logging,它的关键点就是先写日志,再写磁盘
。事务在提交写入磁盘前,会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问,涉及磁盘随机I/O访问是非常消耗时间的一个过程,相比之下先写入redo log,后面再找合适的时机批量刷盘能提升性能。
总结:
因为首先是修改了redolog,要保证binlog和redolog 的一致性,所以再更改binlog之后,再ibaredolog的状态跟改为commit。
为了保证binlog和redo log两份日志的逻辑一致,最终保证恢复到主备数据库的数据是一致的,采用两阶段提交的机制。
MySQL提供主从复制功能,可以方便的实现数据的多处自动备份,不仅能增加数据库的安全性,还能进行读写分离,提升数据库负载性能。
主从复制流程:
一般来说,普通的SQL语句需要先编译然后执行,而存储过程可以理解为为了完成特定功能的已经编译后的SQL语句集。用户可通过存储过程的名字并给定参数来调用。
触发器简单来说就是监视某种情况,并触发某种操作。 当触发器所在表上出现指定事件(insert/update/delete)时,可指定时间(after/before)执行特定事件(insert/update/delete)。
总的来说就是 避免全表扫描 ,尽量走索引。
insert into tablename(col1,col2,..) values(val1,val2,...)
delete from tableName WHERE 条件表达式
update tableName set 列名=值 WHERE 条件表达式
select * from tableName WHERE 条件表达式
where、group by、having、order by、limit
delete是数据操纵语言(DML),其按行删除,支持where语句,执行操作采用行锁,执行操作时会将该操作记录在redo和undo中,因此支持回滚。
truncate是数据定义语言(DDL),其操作隐式提交,不支持回滚,不支持where,删除时采用表级锁进行删除。
针对存储了百万级乃至千万级条记录的大表。数据库在查询和插入的时候耗时太长,可通过分表,将大表拆分成小表,提升数据库性能。
另外还有就是小表驱动大表,因为在for循环中如果外层是10000内层是5 ,则需要10000次连接,若外层是5则就需要5次链接,极大的提高效率。
关系型数据库采用了关系模型(可以简单理解为二维表格类型)组织数据,一般可以遵守事务的ACID特性 不是由关系模型进行存储的均可视作非关系型数据库,比如以键值对的redis,图数据库等。
通过数据属性中,增加版本号属性,进行比较,比较目前操作数据是否是最新版本。
CAS(compare and swap)即在对数据修改过程中,采用CAS算法,保证在并发下的一致性。
直接原因是其存储机制。MySQL采用数据页进行数据存储。 如果采用自增主键,在原先数据页写满的情况下,MySQL对于新数据,直接开辟新页进行写操作。 如果不采用自增主键,为保障索引有序,新数据需插入到合适位置上,由此针对页数据满的情况下,MySQL需要申请新页,并将一部分之前的页数据挪到新页上,保证按索引有序存储,相对自增主键IO开销更大。
定位对应索引id所处的偏移位置,之后进行查询。
select * from table where num = 8 limit 100000,1;
# 变为
select * from table where num = 8 and id >= (
select id from table where num = 8 limit 100000,1
) limit 100;
# 由于id走了索引,因此速度会有一定提升。这个id走索引是说的id >= 这个地方走的主键索引。
对于分库,即将一个数据库拆分为多个库。 可以通过水平拆分,或者垂直拆分的方式,将表进行拆分。 一般可以采用中间件Sharding-JDBC进行分库分表。 我曾用过amoeba中间件测试过。
CHAR的长度是不可变的,而VARCHAR的长度是可变的。 因此CHAR效率高,VARCHAR效率偏低。
例如char(2), 表示两个长度, 如果存入一个字符"1", 会自动向后补位一个空格, 变成"1 "来满足char(2)的约束。
一个事务读取了另一个事务修改但未提交的数据
将事务隔离级别调整到 读已提交 或者 可重复读 或者 串行化。
一个事务连续读两次数据,但结果不一样。(两次读之间,数据被其他事务修改)。
将事务隔离级别设置为:串行化,可重复读进行解决。
一个事务连续读两次数据,读取数据量不一样。(两次读之前,数据被其他事务删除或新增)。
将事务隔离级别设置为:串行化,或在innodb引擎中有gap锁的情况下设置可重复读进行解决。
一个事务连续读两次数据,读取数据量不一样。(两次读之前,数据被其他事务删除或新增)。
将事务隔离级别设置为:串行化,或在innodb引擎中有gap锁的情况下设置可重复读进行解决。
数据被两个事务连续修改,导致第一个事务的修改被第二个事务覆盖丢失。
使用读未提交就能解决丢失更新。,因为只能读而不能修改所以不会丢失更新。
主键索引就是唯一索引,住家能够表示一行的属性或属性组,对于表创建的过程中,如果暂时未指定唯一索引的情况下,数据库会自动生成生成某一隐藏字段,作为唯一索引。
唯一索引是在表上一个或者多个字段组合建立的索引。
统计
或者分组
的字段复合索引不满足最左匹配原则
查询条件有or
where 查询语句对索引列有数学运算或函数
模糊查询 like %在后边
范围查询 有 > < 等
varchar字段开头包含一个变长字段的实际长度,后面存储的是真实字符。
可以更方便利于范围查询
snowflake算法:利用时间戳,机器id,当前数据库自增id进行拼接,生成的新的分布式id。
在执行数据操作之前,首先将原始数据备份,这就是undo log。之后执行数据修正。 如果执行出现了错误,系统可利用undo log中的备份将数据恢复到事务开始之前的状态,保证事务原子性
不存在,InnoDB通过引入间隙锁+行锁(next key lock)的方式,解决了幻读问题。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。