Mysql（mysql总结）

数据库结构(原文)

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）：

1、.frm 文件用来描述表的格式或者说定义；

2、.ibd 文件 数据索引、数据信息（默认所有表在一个文件中，打开 innodb_file_per_table设置按表区分）；

 同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB；

每个 16KB 大小的页中可以存放 2-200 行的记录。

索引

 

CREATE TABLE users(

id INT NOT NULL,

first_name VARCHAR(20) NOT NULL,

last_name VARCHAR(20) NOT NULL,

age INT NOT NULL,

PRIMARY KEY(id),

KEY(last_name, first_name, age)

KEY(first_name)

);

 

聚集索引

B+ 树通过 B+ 树实现的就会使用 id 作为索引的键，并在叶子节点中存储一条记录中的所有信息。

所有正常的表应该有且仅有一个聚集索引（绝大多数情况下都是主键），表中的所有行记录数据都是按照聚集索引的顺序存放的。

辅助索引

 

辅助索引也是通过 B+ 树实现的，但是它的叶节点并不包含行记录的全部数据，仅包含索引中的所有键和一个用于查找对应行记录的『书签』，在 InnoDB 中这个书签就是当前记录的主键。

InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

索引的数据结构

 

InnoDB 存储引擎在绝大多数情况下使用 B+ 树建立索引，但是 B+ 树索引并不能找到一个给定键对应的具体值，它只能找到数据行对应的页，数据库把整个页读入到内存中，并在内存中查找具体的数据行。

 

一、myisam和innodb索引实现的不同

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。

MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

《具体细节》

实现方式：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 
InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
 

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
 

索引优化

1、组合索引比单个索引效果更好；

2、当搜索范围超过30%，索引作用微乎其微；

3、索引最左有限原则;

4、SQL优化神器；（explain：id越大越先执行，id相同由上而下）

 

 

导致索引失效的可能情况

1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
     由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
     错误的例子：select * fromtest where tu_mdn=13333333333;
     正确的例子：select * fromtest where tu_mdn='13333333333';
2.对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
     错误的例子：select* from test where id-1=9;
     正确的例子：select * fromtest where id=10;
3.以下使用会使索引失效，应避免使用；
    a. 使用 <> 、not in、not exist、!= 、or、is null 、is not null
    b. like "%_"百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
    c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上,只有在它的第一个列被 where子句引用时，优化器才会选择使用该索引。
    d.字符型字段为数字时在where条件里不添加引号.
    e.当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
    f.不在索引上做任何操作(计算,函数,(自动/手动)类型转换,这样会导致索引失效而转向全表扫描。
    g.避免select *。
4. 不要在 SQL 代码中使用双引号。
5.排序问题
　 create index ind_test3_c1234 on test3(c1,c2,c3,c4);
  explain select * from test3 where c1='a1' and c5='a5' order by c2,c3; 
  只用了c1这个字段索引,但是c2,c3用于排序,无filesort
  explain select * from test3 where c1='a1' and c5='a5' order by c3,c2; 
  只用了c1这个字段索引,但是由于c3,c2顺序颠倒了,所以无法使用索引排序,出现filesort

锁

我们都知道锁的种类一般分为乐观锁和悲观锁两种，InnoDB 存储引擎中使用的就是悲观锁，而按照锁的粒度划分，也可以分成行锁和表锁。

并发控制机制

乐观锁和悲观锁其实都是并发控制的机制，同时它们在原理上就有着本质的差别；

  • 乐观锁指乐观的认为要访问的数据不会被人修改。因此不对数据进行加锁，如果操作的时候发现已经失败了，则重新获取数据进行更新（如CAS），或者直接返回操作失败。
  • 悲观锁就是一种真正的锁了，它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源；

虽然乐观锁和悲观锁在本质上并不是同一种东西，一个是一种思想，另一个是一种真正的锁，但是它们都是一种并发控制机制。

Optimistic-Pessimistic-Locks

乐观锁不会存在死锁的问题，但是由于更新后验证，所以当冲突频率和重试成本较高时更推荐使用悲观锁，而需要非常高的响应速度并且并发量非常大的时候使用乐观锁就能较好的解决问题，在这时使用悲观锁就可能出现严重的性能问题；在选择并发控制机制时，需要综合考虑上面的四个方面（冲突频率、重试成本、响应速度和并发量）进行选择。

锁的种类

对数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）；共享锁和互斥锁的作用其实非常好理解：

  • 　　共享锁：允许事务去读一行，阻止其他事务对该数据进行修改

    　　排它锁：允许事务去读取更新数据，阻止其他事务对数据进行查询或者修改(SELECT FOR UPDATE)

而它们的名字也暗示着各自的另外一个特性，共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容：

Shared-Exclusive-Lock

稍微对它们的使用进行思考就能想明白它们为什么要这么设计，因为共享锁代表了读操作、互斥锁代表了写操作，所以我们可以在数据库中并行读，但是只能串行写，只有这样才能保证不会发生线程竞争，实现线程安全。

 

锁的粒度

无论是共享锁还是互斥锁其实都只是对某一个数据行进行加锁，InnoDB 支持多种粒度的锁，也就是行锁和表锁；

表锁

表锁分为读写锁有read和write两种

Lock Tables......Read通常被称为共享锁或者读锁，读锁或者共享锁，是互相不阻塞的，多个用户可以同一时间使用共享锁互相不阻塞。

Lock Table......write通常被称为排他锁或者写锁，写锁或者排他锁会阻塞其他的读锁或者写锁，确保在给定时间里，只有一个用户执行写入，防止其他用户读取正在写入的同一资源。

总结：

  1. Lock Tables....READ不会阻塞其他线程对表数据的读取，会阻塞其他线程对数据变更
  2. Lock Tables....WRITE会阻塞其他线程对数据读和写
  3. Lock Tables....READ不允许对表进行更新操作(新增、删除也不行)，并且不允许访问未被锁住的表
  4. Lock Tables....WRITE允许对被锁住的表进行增删改查，但不允许对其他表进行访

显示调用： 

LOCK TABLES oauth_code WRITE/READ

UNLOCK TABLES;

 

为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock），意向锁就是一种表级锁。

与上一节中提到的两种锁的种类相似的是，意向锁也分为两种：

  • 意向共享锁：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁；
  • 意向互斥锁：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁；
  • 意向锁之间兼容，不会阻塞。但是会跟S锁和X锁冲突，冲突的方式跟读写锁相同。例如当一张表上已经有一个排它锁（X锁），此时如果另外一个线程要对该表加意向锁，不管意向共享锁还是意向排他锁都不会成功。

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

Lock-Type-Compatibility-Matrix

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

有的人可能会对意向锁的目的并不是完全的理解，我们在这里可以举一个例子：如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

 

死锁的发生

既然 InnoDB 中实现的锁是悲观的，那么不同事务之间就可能会互相等待对方释放锁造成死锁，最终导致事务发生错误；想要在 MySQL 中制造死锁的问题其实非常容易：

Deadlocks

两个会话都持有一个锁，并且尝试获取对方的锁时就会发生死锁，不过 MySQL 也能在发生死锁时及时发现问题，并保证其中的一个事务能够正常工作，这对我们来说也是一个好消息。

 

事务与隔离级别


事务还遵循 ACID 四大特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）；
几种隔离级别

隔离级别脏读（Dirty Read）不可重复读（NonRepeatable Read）幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能
  • 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据；
  • 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)；
  • 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读；
  • 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞；



　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读和幻读的区别

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

 

select 锁表问题。

MySQL 最常见的坑就是 InnoDB 是行锁，这是大家都知道的事，但是有时候它却会锁表，你说奇怪不奇怪。

其实只要你懂它了之后，一点也不会觉得奇怪。只有你不懂，才会觉得它奇怪。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。这一点和 Oracle 的行锁实现机制略有不同。

例如下面的表：

1
xttblog(id, title, url, text) innodb;

id PK（主键），无其他索引，即其他列都没有索引。

1
update xttblog set text='业余草' where id=1;

命中索引，行锁。

1
update xttblog set text='业余草' where id != 1;

未命中索引，表锁。

1
update xttblog set text='业余草' where title='业余草';

无索引，表锁。

启示：InnoDB 务必建好索引，否则锁粒度较大，会影响并发。

再说一下 select，如果查询没有命中索引，也将退化为表锁。下面我们结合 InnoDB 的三种锁（记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)）来说明它。再讲这三种锁的前提条件是默认的事务隔离级别为可重复读(Repeated Read, RR)。

记录锁(Record Locks)

记录锁，它封锁索引记录，例如下面的查询语句：

1
select * from xttblog where id=1 for update;

它会在 id=1 的索引记录上加锁，以阻止其他事务插入，更新，删除 id=1 的这一行。

需要说明的是，如果是下面的查询语句：

1
select * from xttblog where id=1;

则是快照读(SnapShot Read)，它并不加锁。

 

锁的算法

MVCC:

多版本控制，InnoDB实现MVCC是通过在每行记录后面保存两个隐藏的列来实现，一个保存创建的事务版本号，一个保存的是删除的事务版本号。MVCC只有在REPEATABLE READ 和 READ COMMITED两个隔离级别下工作。另外两个隔离级别与MVCC并不兼容，因为READ UNCOMMITED总是读取最新数据，跟事务版本无关，而SERIALIZABLE会对读取的所有行都进行加锁。

MVCC实现原理：https://www.toutiao.com/a6868176598705635853/?tt_from=weixin&utm_campaign=client_share&app=news_article&utm_source=weixin&iid=1336623137845079&utm_medium=toutiao_android&wxshare_count=1

间隙锁(Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。例如下面的 SQL 语句：

1
select * from xttblog where id between 8 and 15 for update;

会封锁区间 id 为 8 到 15 的记录。以阻止其他事务，如：id=10 的记录插入。

如果不阻止 id=10 的记录插入，则会产生幻读。如果能够插入成功，同一个事务执行相同的 SQL 语句，会发现结果集多出了一条记录，即幻读数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁(Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录 Record 的共享/排他锁，其他会话不能立刻在 Record 之前的区间插入新的索引记录。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

最后说一下，怎么测试当前的查询到底是行锁还是表锁呢？

以我们之前发生事故来说，首先是 select 查询不能有索引。然后 dev 环境和 sit 环境连接同一个数据库，dev 对某个事务中的查询取断点，让它停在查询操作上；sit 环境则对同一个表进行插入、更新、删除操作。查看日志，就会发现有 time out 日志。具体为事务无法提交，超时结束，因为这个表已经被锁住了，获取不到锁，就会发生超时。

总结：InnoDB 的锁，与索引类型，事务的隔离级别相关。InnoDB 到底是行锁还是表锁取决于你的 SQL 语句。如果查询没有命中索引，也将退化为表锁。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

间隙锁例子：https://blog.csdn.net/weixin_34553861/article/details/112380759

posted @ 2020-01-09 17:03  风好大  阅读(715)  评论(7)  编辑  收藏  举报