美文网首页
MySql学习重点摘要

MySql学习重点摘要

作者: 现代愚公BevisWu | 来源:发表于2017-10-28 16:28 被阅读0次

    存储引擎(Storeage Engine)

    MySql提供了多种引擎可供选择,我们最常用的就是InnoDB,MyISAM.
    如果用MySql的SHOW ENGINES\G查一下,MySQL其实在不断的丰富引擎类型,提供不同类型,不同场景、不同特性的需求,所以随着MySql版本不同,提供可供选择的引擎也不同。

    以MySql 5.7版本支持存储引擎列表(MySQL 5.7 Supported Storage Engines):
    MySql5.7SupportedStorgaeEngines.png
    那自从MySql5.5版本之后,默认的引擎就是InnoDB.
    各存储引擎的特性简要对比(Storage Engines Feature Summary):
    mysql5.7sotrageenginefeaturesummary.png
    InnoDB主要特性

    可以总结一下我们常用InnnoDB的主要特性:


    MySQL5.7InnoDBfeature.png

    从中可以看出:
    1.InnoDB支持行级锁;
    2.也支持全文索引;
    3.B-tree Index;
    4.MVCC支持;
    5.最重要的是支持事务;

    MySql隔离级别

    af5b9c1e-4517-3df2-ad62-af25d1672d12.jpg

    MySql InnoDB存储引擎默认隔离级别为:Repeadtable read.

    索引(index)

    索引是加快数据查询的性能。
    MySQL InnoDB存储引擎是用B-tree来实现索引的功能。
    平常用到索引有PRIMARY KEY, UNIQUE, INDEX。

    聚簇索引与非聚簇索引

    索引分为聚簇索引和非聚簇索引两种,
    聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引中数据和索引不在同一个地方;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快,一个表中只能有一个聚簇索引。

    对于MyISAM是非聚簇索引,InnoDB是聚簇索引。


    WangWang20170929171850.png

    非聚簇索引主要是中索引和数据是放在不同的地方,索引中的叶子节点存放的是真实数据的地址。
    聚簇索引,对于主索引而言索引和数据是在一起的,索引中叶子节点中既存放在关键字,又存放了数据。对于辅助索引而言,索此叶子节点中存放了对应数据的主键值。

    InnoDB的的辅索引的叶子节点存放的是KEY字段加主键值。因此,通过辅索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的辅索引叶子节点存放的还是列值与行号的组合,叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和辅索引没有任何区别,主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引,且MYISAM引擎中可以不设主键。

    对于MyISAM是非聚簇索引,InnoDB是聚簇索引。

    InnoDB将通过主键聚集数据,如果没有定义主键,Innodb会选择第一个非空的唯一索引代替,如果没有非空唯一索引,Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。

    联合索引与最左前缀匹配原理

    高效使用索引的首要条件是知道什么样的查询会使用到索引,这个问题和B+Tree中的“最左前缀原理”有关,下面通过例子说明最左前缀原理。

    联合索引
    先说一下联合索引的概念。MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般来说一个联合索引是一个有序元组<a1, a2, …, an>,其中各个元素均为数据表的一列。另外,单列索引可以看成联合索引元素数为1的特例。
    假如有一张employee表有employee_no,title,name,campany,age 五个字段,此时如果以如下三个字段(顺序也一致)employee_no,title,age上建立联合索引。
    那么当我们查询的时候,如下几种情况可以使用建立的索引:1)查询条件里有提供employee_no;2)employee_no+title;3)employee_no,title,age这三种情况,可以用到刚刚创建的联合索引。
    在以下几种情况用不到刚建立的联合索引:
    1)查询条件只有age;2)查询条件只有title;3)查询条件有title和age;4)查询条件有employee_no和age(此种情况employee_no用到索引,但是age不能使用索引)
    以上总结的情况只是最基本上的情况,实际中一定要结合最左匹配的实际情况来分析。
    比如以下两种情况都提供了employee_no和title查询,但是一种使用索引,一种不能使用索引:
    select * from employee where employee_no='xxxx' and title like 'xxx%'//title能使用索引;
    select * from employee where employee_no='xxxx' and title like '%xx'//title不能使用索引

    锁(MySql InnoDB)

    https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
    下文中主要是针对InnoDB引擎的锁的摘要笔记

    MVCC

    MySQL后续的版本中增加了MVCC (Multiversion Concurrency Control),即多版本并发控制技术, 它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能。

    数据库锁定机制,从简单来说就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。MySQL数据库存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,所以提供的锁机制也不一样。MySQL使用了三种类型(级别)的锁机制:表级锁,行级锁和页级锁。

    共享锁和排它锁(Shared and Exclusive Locks)

    InnoDB存储引擎在row level锁上实现了共享锁(shared lock,又称S锁)和排它锁(exclusive locks,又称X锁)。
    共享锁(shared lock,又称S锁):允许持此锁的事务去读取(read)此行。
    通过如下语句可以对加一把共享锁

    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE //共享锁(S)
    

    排它锁(exclusive locks,又称X锁):允许持有此锁的事务去更新(update)或删除(delete)此行操作。
    通过如下语句可以加排它锁

    SELECT * FROM table_name WHERE ... FOR UPDATE //排他锁(X)
    

    假设有两个事务t1和t2
    如果事务t1获取了一个元组的共享锁,事务t2还可以立即获取这个元组的共享锁,但不能立即获取这个元组的排它锁(必须等到t1释放共享锁之后)。
    如果事务t1获取了一个元组的排它锁,事务t2不能立即获取这个元组的共享锁,也不能立即获取这个元组的排它锁(必须等到t1释放排它锁之后)

    意向锁(intenion lock)
    意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁(IS)和意向排它锁(IX)两类。意向共享锁表示一个事务有意对数据上共享锁或者排它锁。“有意”这两个字表达的意思比较微妙,说的明白点就是指事务想干这个事但还没真去干。意向锁是MySql自己加的。

    几种锁的兼容和互斥关系

    WangWang20171001204905.png

    加锁使用汇总

    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE //共享锁(S)
    SELECT * FROM table_name WHERE ... FOR UPDATE //排他锁(X)
    

    1.对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
    2.对于普通SELECT语句,InnoDB不会加任何锁;

    在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

    以MySQL InnoDB为例:
    快照读:简单的select操作,属于快照读,不加锁。(当然也有例外)
    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 ?;
    所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。


    DingTalk20171028154046.png
    MySql行锁的实现

    InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
    在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。

    使用行锁的的几点注意:
    1.在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
    2.由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
    3.当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
    4.即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

    间隙锁(Next-Key锁)

    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;
    对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

    假如emp表中只有101条记录,其empid的值分别是 1,2,...,100,101,下面的SQL:
    mysql> select * from emp where empid > 100 for update;
    是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

    通过索引实现锁定的方式还存在其他几个较大的性能隐患:
    1.当Query无法利用索引的时候,InnoDB会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低;
    2.当Query使用的索引并不包含所有过滤条件的时候,数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键;
    3.当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同的时候(索引只是过滤条件的一部分),一样会被锁定。

    GAP锁,就是Repeatable Read隔离级别,相对于Repeatable Commited隔离级别,不会出现幻读的关键。GAP锁锁住的不是位置,也不是记录本身,而是两条记录之间的GAP。所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

    如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。

    查看数据库的行锁情况
    mysql> show status like 'InnoDB_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | InnoDB_row_lock_current_waits | 0     |
    | InnoDB_row_lock_time          | 0     |
    | InnoDB_row_lock_time_avg      | 0     |
    | InnoDB_row_lock_time_max      | 0     |
    | InnoDB_row_lock_waits         | 0     |
    +-------------------------------+-------+
    InnoDB_row_lock_current_waits:当前正在等待锁定的数量;
    InnoDB_row_lock_time:从系统启动到现在锁定总时间长度;
    InnoDB_row_lock_time_avg:每次等待所花平均时间;
    InnoDB_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
    InnoDB_row_lock_waits:系统启动后到现在总共等待的次数;
    

    MYSQL InnoDB的索引和锁
    Mysql的锁机制解读
    MySql-两阶段加锁协议
    排它锁
    共享锁
    表锁
    行数
    gap锁

    Mysql的record锁、gap锁、next-key锁

    存储引擎

    mysql数据表存储引擎类型及特性

    事务相关

    • mysql事务隔

    MySql锁示例分析

    说明:以下转自简小鹿奔跑ing 的 MySQL 加锁处理分析

    DingTalk20171028154904.png DingTalk20171028154949.png DingTalk20171028155017.png DingTalk20171028155208.png DingTalk20171028155336.png DingTalk20171028155752.png DingTalk20171028155917.png FireShot Capture 3 - MySQL 加锁处理分析 - 简书 - http___www.jianshu.com_p_e96e26c89869.png FireShot Capture 4 - MySQL 加锁处理分析 - 简书 - http___www.jianshu.com_p_e96e26c89869.png FireShot Capture 5 - MySQL 加锁处理分析 - 简书 - http___www.jianshu.com_p_e96e26c89869.png

    参考:
    mysql数据表存储引擎类型及特性
    MYSQL InnoDB的索引和锁
    MySQL的btree索引和hash索引的区别
    B-tree、B+tree、B*tree
    MySQL优化之BTree索引使用规则

    如何理解并正确使用MySql索引
    Btree,B-Tree,B+Tree,B*Tree

    《MySQL技术内幕:InnoDB存储引擎》读书笔记五-锁、索引及事务

    MySQL索引背后的数据结构及算法原理

    参考:
    MySQL锁详解
    MySQL 加锁处理分析
    一个最不可思议的MySQL死锁分析
    初步理解MySQL的gap锁

    MySql 5.7 官方文档
    MySql5.7官方文档之Clustered and Secondary Indexes
    MySql 5.7官方文档之InnoDB Locking
    MySql 5.7官方文档之Transaction Isolation Levels

    相关文章

      网友评论

          本文标题:MySql学习重点摘要

          本文链接:https://www.haomeiwen.com/subject/gvqesxtx.html