MYSQL基础知识和案例分享

1

MyISAM

· Mysql 5.1之前默认的存储引擎,支持包括全文索引、压缩、空间函数(GIS)等,不支持事务和行级锁。最大的缺陷是崩溃后无法安全恢复。

· 对整张表加锁,而不是针对行。读取时会对需要读到的所有表加共享锁,写入时对表加排他锁

· 可以使用myisampack对表进行压缩。压缩表不能进行修改(除非先解压表,修改数据,再次压缩)。压缩表可以极大的减少磁盘空间占用,因此也可以减少磁盘I/O

2

MariaDB

· MariaDB是由Mysql之父主导开发的Mysql一个分支,因为Oracle收购Mysql后可能存在闭源的风险。

· MariaDB可以视作Mysql的替代品,基本保证二进制兼容。

· MariaDB采用的引擎有Percona公司的XtraDB(InnoDB引擎的改进加强版), 和用来替代MyISAM的新引擎Maria(Mysql之父的女儿…, 该引擎后来改名Aria)

3

InnoDB

· 历史:从Mysql 5.1的InnoDB plugin到 5.5中的原生编译。

· 现在是Mysql的默认事务型引擎,也是最重要的使用最广泛的存储引擎。

· 采用MVCC来支持高并发,实现了四个标准的隔离级别, 默认级别是Repeatable Read, 并通过间隙锁策略防止幻读。

· InnoDB表是基于聚簇索引建立的。

二、事务隔离级别

1

READ UNCOMMITTED 未提交读

在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)

2

READ COMMITTED 提交读 (RC)

大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。

3

REPEATABLE READ 可重复读 (RR)

MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

4

SERIALIZABLE 串行化

这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

三、 锁基本理论

1

常用存储引擎的锁

· MyISAM和MEMORY采用表级锁(table-level locking)

· BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁

· InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

各种锁特点

· 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生冲突的概率最高,并发度最低

· 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

· 页面锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般

2

锁的实现

锁的存放

A. table->locks 存放一个表的所有表级锁。

B. lock_sys->rec_hash存放所有表的行锁。Hash值根据(spaceid, pageno)来计算。

C. trx->trx_locks存放事务的所有锁,包括表级锁和行级锁。一个事务的所有锁,在事务结束时,一起释放。代码在lock_release_off_kernel().如果有等待的锁可以被授权,则会将等待的锁,转变为被授权的锁,并唤醒相应的事务。

行锁的唯一识别

第一印象想到的是,用每行记录的键值来做行锁的唯一识别.但是键值占用空间比较大。

InnoDB使用Page NO.+Heap NO.来做行锁的唯一识别。我们可以将Heap no.理解为页面上的一个自增数值。每条物理记录在被创建时,都会分配一个唯一的heap no.

A. 键值可以理解为一个逻辑值,page no. + heap no. 是物理的。

B. 物理的虽然占用空间小,但是处理要复杂一些。如:在分裂一个B+Tree页面时,一半的记录要移到新的页面中,因此要对存在的锁进行迁移。

3

多版本并发控制MVCC

· Multi-Version Concurrency Control

是通过保存数据在某个时间点的快照来实现的。

读类型: 快照Snapshot/当前Current

•快照读:简单的select操作,属于快照读,不加锁。(Serializable读加读锁)

select * from table where ?;

•当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。

◦select * from table where ? lock in share mode;

◦select * from table where ? for update;

◦insert into table values (…);

◦update table set ? where ?;

◦delete from table where ?;

· 不同的存储引擎的MVCC实现不同,有乐观并发控制,有悲观并发控制。

· InnoDB是通过在每行记录后面保存两个隐藏的列来实现的。一个保存行的创建时间,一个保存行的过期时间(或删除时间)。这个不是实际的时间值,而是系统版本号(System Version Number)。

· 每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的的系统版本号作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

· MVCC 只在RC和RR两个隔离级别下工作。这样设计使大部分读操作不用加锁,读和写不冲突。

4

InnoDB锁的类型

摘自MySQL官网:

Shared and Exclusive Locks 共享锁和排他锁

Intention Locks 意向锁

Record Locks 记录锁

Gap Locks 间隙锁

Next-Key Locks = Gap Locks + Record Locks

Insert Intention Locks

AUTO-INC Locks

Predicate Locks for Spatial Indexes

共享锁(S)和排他锁(X)

· (S)hared Lock共享锁: 持有该锁的事务可以读一行

· E(x)clusive Lock排他锁: 持有该锁的事务可以更新或删除一行

· 事务T1在行记录r上持有S锁, 事务T2在r上请求S锁是准许的,最终T1 T2同时还有r上的S锁;但T2在r上请求X锁是不允许的

· 事务T1在记录r上持有X锁,事务T2不能在r上再请求任何类型的锁

意向锁(I)

· (I)ntention Lock意向锁: 是一种表锁,用于表示一个事务随后将对该表的行记录请求某种类型的锁。

Intention shared(IS): 意向共享锁。

SELECT ... LOCK IN SHARE MODE

Intention exclusive(IX):意向排他锁

SELECT ... FOR UPDATE

· 意向锁协议

A. 请求获取表t某一行的S锁之前,必须在表t上获取IS或者更强的锁;

B. 请求获取某一行的X锁之前,必须在表上获取IX锁;

· 意向锁主要用来表示某人正在锁定或者即将要锁定行记录

记录锁Record Locks

· 记录锁(行锁)是一种在索引记录上设置的锁,锁定特定的记录行,其他任何事务不能更新和删除,当然也不能insert相同的记录。

· 记录锁永远锁定的是索引,即使表没有索引。InnoDB会隐含创建一个聚簇索引,用这个索引进行记录锁。

间隙锁Gap Locks

· 间隙锁是一种在索引记录的间隙上设置,或者在索引范围的间隙上设置的锁。例如 SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20; 不允许其他事务在c1列插入15这个值。开区间不包括记录本身

· 间隙锁是性能和并发之间妥协的产物,可以通过设置事务隔离级别为 RC或者开启系统变量innodb_locks_unsafe_for_binlog(已过时),来禁用间隙锁

· 间隙锁在同一个gap上没有冲突一说,事务A在gap上有个S锁,事务B允许在同一个gap上持有X锁。

The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

· 间隙锁在Innodb是“纯抑制”,意思是它只阻止其他事务插入数据到这个间隙, 而不会阻止其他事务获取这个间隙上的间隙锁。因此间隙X锁与间隙S锁效果相同。

Next-Key Locks

· next-key锁是记录锁和间隙锁的组合。是开闭区间,包括记录本身

· 假设索引记录有值 10, 11, 13, 20, 那可能的next-key lock包括:

 (-∞, 10]  (10, 11]  (11, 13]  (13,20]  (20,+ ∞)

· Innodb默认就是在RR隔离级别下, 使用的next-key locks用来查询和索引扫描,阻止幻读的发生

· next-key locks 会降级(优化)为record locks:

 当索引含有唯一约束时

 锁定一条记录

· 由于事务的隔离性和一致性要求,会对所有扫描到的record加锁。比如:update ... where/delete .. where/select ...from...lock in share mode/ select .. from .. for update这都是next-key lock。

· 相对的还有 previous-key locks, 锁定区间与next-key 的开闭相反。

插入意向锁

· Insert Intention是由insert操作设置的一种特殊的Gap lock,发生在行插入之前

· 提高并发插入性能。假设索引记录有4,7,两个不同事务分别插入5,6, 每个事务都在区间4,7上先请求“插入意向锁”,在获取插入行的排他锁之前,这样就不会互相造成阻塞因为这两行不冲突

自增长锁AUTO-INC Locks

· 在InnoDB的内存结构中,每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。对含有自增长计数器的表进行插入操作是,这个计数器会被初始化,执行如下的语句来得到计数器的值:

select max(auto_inc_col) from t for update;

插入操作会根据这个自增长的计数器值加一赋予自增长列。这个实现方式叫做 AUTO-INC Locks.

· 一种特殊的表锁,为了提高插入的性能,锁不是在一个事务完成后释放,在完成对自增长值的SQL语句后立即释放。并发插入场景,事务必须等待前一个插入的完成(虽然不用等待事务的完成)。所以有时候也会被称为“语句”级别的锁。

· 因为不是事务级别的锁,innodb的自增主键很有可能不是延续的。默认innodb_autoinc_lock_mode=1,可预判需要多少行,并一次性预生成。

· InnoDB引擎中,自增长的列必须是索引,同时必须是索引的第一列。如果不是Mysql会抛出异常。

innodb_autoinc_lock_mode = 0 (“traditional” lock mode:全部使用表锁)

innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock mode:可预判行数时使用新方式,不可时使用表锁)

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode:全部使用新方式,不安全,不适合replication)

隐式锁转显式锁

· 如果一个表有很多的索引,那么操作一个记录时,岂不是要加很多锁到不同的B-Tree上吗?

· Innodb 实现了一个延迟加锁的机制,来减少加锁的数量,在代码中称为隐式锁(Implicit Lock)。隐式锁中有个重要的元素,事务ID(trx_id)。

· 隐式锁的特点

A. 只有在很可能发生冲突时才加锁,减少了锁的数量。

B. 隐式锁是针对被修改的B+Tree记录,因此都是Record类型的锁。不可能是Gap或Next-Key类型。

· 隐式锁的使用

A. INSERT操作只加隐式锁,不需要显示加锁。

B. UPDATE,DELETE在查询时,直接对查询用的Index和主键使用显示锁,其他索引上使用隐式锁。

C. INSERT,UPDATE,DELETE对B+Tree们的操作都是从主键的B+Tree开始,因此对主键加锁可以有效的阻止死锁。

隐式锁其实就是没有锁,比较悲观的顺序化机制。比如我插入一条记录,会假设有其他人也要并发插入数据,但是这是假设的场景而已。可能根本没有并发,那隐式锁也就最终会被取消掉;但是一旦有并发,他们就会查询这个隐式锁所在的事务是不是活跃的,是的话就把我的隐式锁升级为显示锁,自己获取共享锁并标记为等待状态。

隐式锁的逻辑过程如下:

A. InnoDB的每条记录中都一个隐含的trx_id字段,这个字段存在于簇索引的B+Tree中。

B. 在操作一条记录前,首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚).

如果是活动的事务,首先将隐式锁转换为显式锁(就是为该事务添加一个锁)。

C. 检查是否有锁冲突,如果有冲突,创建锁,并设置为waiting状态。如果没有冲突不加锁,跳到E。

D. 等待加锁成功,被唤醒,或者超时。

E. 写数据,并将自己的trx_id写入trx_id字段。Page Lock可以保证操作的正确性。

一致性非锁定读

Consistent Nonlocking Read

· 指InnoDB存储引擎通过MVCC的方式来读取当前执行时间数据库中行的数据,如果读取的行正在执行DELETE或UPDATE操作,这是读取操作不会因此等待行上锁的释放。相反的,InnoDB会去读取行的一个快照数据

· 在事务隔离级别RC和RR下,InnoDB存储引擎使用非锁定的一致性读。然而,对于快照数据的定义却不相同。在rc事务隔离级别下,对于快照数据,非一致性读总是被锁定行的最新一份快照数据。而在RR事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。

锁定读

Locking Read

SELECT ... LOCK IN SHARE MODE:锁定读取行,其他session可以读锁定行,但不能修改直到S锁的事务释放。

SELECT ... FOR UPDATE :锁定读取行以及关联的索引,与直接update这些行实现的效果一致。其他事务不能再更新这些行,也不能加S锁。

示例1:保证parent表有数据,child表才可以插入一条记录。S锁可以保证事务内查询存在的数据不会在 select和insert to child期间被其他事务删除。

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

示例2: 并发更新计数器

SELECT counter_field FROM child_codes FOR UPDATE; //排他锁

UPDATE child_codes SET counter_field = counter_field + 1;

LAST_INSERT_ID(): 在当前connection上下文内,最近一次操作insert或update影响自增列的最终ID

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);

SELECT LAST_INSERT_ID();

四、 索引的分类及使用

1

索引的类型

· 索引在存储引擎层而不是服务器层实现的,所以没有统一的索引标准。

· B-Tree索引对索引列是顺序组织存储的,所以适合范围查找。适用于全键值、键值范围或键前缀查找。启动键前缀查找只适用于根据最左前缀的查找。

· 哈希索引基于哈希表实现,只有精准匹配索引所有列的查询才有效。哈希表中保存指向每个数据行的指针。是Memory引擎的默认索引类型。限制有:只包含哈希和行指针,不存储字段值;不是按照索引列的值顺序存储的,无法用于排序;不支持部分索引列匹配查找,因为哈希索引始终使用索引列的全部内容来计算哈希值的;只支持等值比较查找不支持范围查找;哈希冲突问题;

· 空间数据索引(R-Tree): MyISAM表支持空间索引,可以用作地理数据存储。不常用

· 全文索引是一种特殊类型索引,查找的是文本中的关键词,而不是直接比较索引中的值。

2

三星系统three-star索引

一星:索引将相关的记录放到一起;

二星:索引中的数据顺序和查找中的排列顺序一致;

三星:索引中的列包含了查询中需要的全部列;

3

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

· 独立的列:指索引列不能使表达式的一部分,也不能是函数的参数。

· 前缀索引和索引选择性:对于text或者很长的varchar类型的列,需要索引开始的部分字符,可以大大结余索引空间,MySQL也不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性。

· 多列索引:当多个索引做相交操作时(AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。当多个索引做联合操作时(OR条件),需要耗费大量的CPU和内存在算法的缓存、排序和合并操作上,而优化器不会把这些计算到“查询成本”(COST)中, 优化器只关心随机页面读取。有些时候还不如改成UNION的方式更好。

4

聚簇索引

· 并不是一种单独的索引类型,而是一种数据的存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行

· 无法同时把数据行放在两个不同的地方,所以一个表只能有一个聚簇索引。

优点:数据集中,减少磁盘IO;数据访问快;

缺点:插入速度严重依赖于插入顺序(自增长, 避免UUID),按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式;可能面临页分裂(page split)问题; 二级索引访问需要两次索引查找(二级索引的叶子节点保存的是行的主键值,不是行记录物理位置的指针);

· 题外:顺序的主键什么时候回造成更坏的结果?对于高并发,主键的上界会成为“热点”,因为所有的插入都发生在这里,所以并发插入可能导致间隙锁竞争。另一个热点可能是 AUTO-INC锁机制。考虑更改 innodb_autoinc_lock_mode参数配置

5

覆盖索引

· 通常大家会根据查询的WHERE条件来创建合适的索引,设计优秀的索引应该考虑整个查询,而不单单是WHERE条件部分

· 索引条目远小于数据行的大小,如果只需要读取索引,会极大减少数据访问量

· 索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多

· InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

五、 案例分析

1

加锁组合分析

第一案例首选“何登成”大神的博文:

http://hedengcheng.com/?p=771

2

主键或唯一索引分析

假设有表T(id, name), id是主键或者唯一索引,已存在5条记录。在不同操作下锁定的区间情况如图,若有出入,请以实际验证结果为准。

3

非唯一索引分析

假设有表T(id, name), id是非唯一索引,已存在5条记录。在不同操作下锁定的区间情况如图,若有出入,请以实际验证结果为准。

4

一个不可思议的死锁

仍然参见何登成博文

http://hedengcheng.com/?p=844#_Toc378337494

5

INSERT SELECT并发

Insert into t_dest (col1,col2,col3…)

select var1, var2, max(col)-var2

from t_source

where …

· select 语句在这个语境下隐含会加上S锁,类似于加上 lock in share mode;

· 多线程并发运行insert select语句,多个事务会同时先对t_source表进行当前读并获取S锁后,再插入到t_dest。即,内嵌的select并不是互斥的。这种情况下可以将select单独剥离出来使用快照读,然后通过乐观锁等方式对并发插入的数据一致性控制。

6

并发插入的死锁

7

死锁的预防

1. 如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select into outfile加上load data infile代替 insert…select,这样不仅快,而且不会要求锁定

2. 一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。

3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。

4. sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。

5. 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。

6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。

本文分享自微信公众号 - 曲水流觞TechRill(geniusiandev)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏苦逼的码农

写给工程师的 MySQL 面试高频 100 问!

本文主要受众为开发人员,所以不涉及到MySQL的服务部署等操作,且内容较多,大家准备好耐心和瓜子矿泉水.

8020
来自专栏韩锋频道

解读“OB登顶TPCC”

作为十一期间数据库圈的一条刷屏新闻,“中国自研数据库超越Oracle登顶全球第一”,确实很吸引眼球。近几天来,又不断有后续消息放出。有热捧的、有唱衰的、有不以为...

16930
来自专栏崔庆才的专栏

硬货来了!轻松掌握 MongDB 流式聚合操作

信息科学中的聚合是指对相关数据进行内容筛选、处理和归类并输出结果的过程。MongoDB 中的聚合是指同时对多个文档中的数据进行处理、筛选和归类并输出结果的过程。...

8720
来自专栏芋道源码1024

日均 5 亿查询量的京东订单中心,为什么舍 MySQL 用 ES ?

京东到家订单中心系统业务中,无论是外部商家的订单生产,或是内部上下游系统的依赖,订单查询的调用量都非常大,造成了订单数据读多写少的情况。

11210
来自专栏A周立SpringCloud

超全面的 MySQL优化 面试解析

price decimal(8,2)有2位小数的定点数,定点数支持很大的数(甚至是超过int,bigint存储范围的数)

13130
来自专栏苦逼的码农

除了不要 SELECT * ,程序员使用数据库还应知道的11个技巧!

应用程序慢如牛,原因多多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。

13320
来自专栏互联网数据官iCDO

2018年全球最受欢迎的30款数据可视化工具

RAWGraphs是一个在线的开源工具和数据可视化框架,用来处理Excel表中的数据。你只需将数据导入到RAWGraphs中,设计你想要的图表,然后将其导出为S...

19520
来自专栏菜鸟致敬

建议收藏 | 专业的MySQL开发规范

命名规范的对象是指数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定

10620
来自专栏JAVA葵花宝典

ElasticSearch 查询的秘密

https://neway6655.github.io/elasticsearch/2015/09/11/elasticsearch-study-notes.h...

7320
来自专栏java达人

与我一起学习微服务架构设计模式1—逃离单体地狱

软件架构对功能性需求影响并不大,它影响非功能性需求,即质量属性或者其他能力,如交付速度的可维护性、可扩展性和可测试性。

6110

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励