MySql学习重点摘要

2017-10-28 本文已影响0人现代愚公BevisWu

存储引擎(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锁