Innodb索引和锁的学习笔记

标签: innodb 索引 学习 | 发表时间:2014-04-09 01:09 | 作者:Magician8421
出处:http://www.iteye.com


附录:前段时间学习了下innodb锁的相关知识,对锁和事务有了大体理解,这里做个小总结。

1.Innodb事务和锁的关系。

   Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持。事务要求了一组SQL语句的ACID特性,同时为了避免对一行记录的并发更新,innodb本身会在一定情况下加锁,然后等语句所在的事务退出后(rollbak或者commit)释放锁。其实在autocommit=true时,一个sql本身就是一个事务。

   Innodb在执行Update,Delete,Insert时会对记录加写锁(排他锁,加上排他锁后,不能再加共享锁和排他锁)。而Select语句不会对记录加锁。

 

   共享锁:SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

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

 

举例:

 

 

   如图,左侧事务加了排他锁(for update),右侧事务直接查询不会等待(不加锁),加排他锁失败,加共享锁失败(lock in share mode)。左侧事务提交或者回滚后,释放锁。

 

2.Innodb的行锁特性。

   Innodb使用行锁加锁记录,也就是说会在加锁条件下对访问的具体行进行加锁,而不会锁住全表。这样能够大大提高表访问的吞吐量,减少不必要的记录锁。而Innodb的行锁是使用索引实现的,因此有一下几点需要注意:

  •     如果对已一条加锁语句访问的数据走不了索引或者没加索引,其实他锁的是全表, 这点需要注意。
  •     如果索引使用的是范围,那么他会锁住命中的记录,同时会锁住不存在的记录间隙防止插入(这一行并不存在),gap lock。

          如图

          

    

       左边事务锁住了age>3的记录,那么很自然age=4的记录会被锁住。而在右边事务中,插入一条age=5个记录,而插入操作被hang住,这就是间隙锁。间隙锁是为了避免幻象读的发生,即A事务批量update condition=x的记录,同时B事务插入了一条condition=X的记录,那么A事务提交后,发现仍然存在一条没有更新的记录,貌似出现了幻觉,这就是幻读。

       因为间隙锁似乎为了防止插入,所以update age=5并不锁表,因为记录并不存在- -。

  •     如果两个查询走不同索引,但是却需要处理同一条记录,会竞争锁。
  •     虽然两个查询到的记录不同,但是走的是同一个索引,那么依然会竞争锁。

   例如:



 
 learntransaction表在age加了索引,左边窗口查询age=122 AND id=6的记录,右边窗口查询age=122 and id=3的记录,可以发现两者的记录并不同,但是却出现了锁竞争,因为两个公用了一个索引age=122。

  •      当where语句的查询字段涉及到多个索引时,mysql会优化sql决定走的索引,所以如果id也加了索引,那么索然在where语句里age在前面,但不一定走age的索引而走了id的索引。所以这个时候对于怎么加锁需要看具体索引的使用情况,建议使用explain工具查询执行计划判断。

        这里需要注意的是,慢查询导致数据库hang住并不一定是因为竞争锁,慢查询可能因为要更新的记录太多,导致sql迟迟无法完成,而innodb默认32个槽,也就是32个并发工作线程,当32个线程都在运行而无法接收新的sql时,数据就可能被hang住了,而与锁无关。

 

 

参考资料:

《深入浅出MySQL——数据库开发、优化与管理维护》 http://book.51cto.com/art/200803/68127.htm

 



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [innodb 索引 学习] 推荐:

Innodb索引和锁的学习笔记

- - 数据库 - ITeye博客
附录:前段时间学习了下innodb锁的相关知识,对锁和事务有了大体理解,这里做个小总结. 1.Innodb事务和锁的关系.    Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持. 事务要求了一组SQL语句的ACID特性,同时为了避免对一行记录的并发更新,innodb本身会在一定情况下加锁,然后等语句所在的事务退出后(rollbak或者commit)释放锁.

Mysql-innodb-B+索引

- - 掘金后端
这是读书笔记,Mysql,innodb系列一共3篇. Mysql-innodb-B+索引(本篇). Mysql-innodb-锁(预计20200523). Mysql-innodb-事务预计20200530). CREATE TABLE `aid_***_detail` ( //省略所有字段 PRIMARY KEY (`id`), KEY `range_idx` (`range_id`,`is_delete`,`range_detail_num`,`goods_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4复制代码.

MySQL InnoDB B+树索引

- - OurMySQL
B+树索引在DB中有一个特点就是高扇出性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的IO操作即可. 而现在的磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒. InnoDB存储引擎表是索引组织表,即表中数据安装主键顺序存放. 而聚集索引就是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,因此也让聚集索引也是索引的一部分.

建索引的原则-以innodb为例

- - CSDN博客推荐文章
随着开发、测试任务进入尾声,大家都在整理一些项目发布前的一些准备工作,其中一个重要的工作就是为之前写的一些sql语句建立索引,这高并发、高访问量的环境下是非常有必要的,建立一个好的索引能够极大地提高sql语句的查询效率,那么问题来了,到底什么是索引,怎样才能建立一个好的索引呢. 本文以mysql Innodb存储引擎为例,结合实际的项目来看一下,如何建立一个好的而索引.

InnoDB一定会在索引中加上主键吗

- - OurMySQL
DBA群里在讨论一个问题,到底InnoDB会不会在索引末尾加上主键,什么时候会加. 我之前看代码记得是如果索引末尾就是主键,那么InnoDB就不再添加主键了,如果索引末尾不是主键,那么会添加主键,但是这跟测试结果不符:. 插入部分数据后可以看到idx1和idx2两个索引的大小相同. 这说明idx1和idx2的内部结构是一样的,因此 不可能 是idx1在内部存为(a,b,a).

关于InnoDB索引长度限制的tips

- - IT技术博客大学习
有同学问到MySQL数据库InnoDB存储引擎的索引长度问题,简单说几个tips. 大家经常碰到InnoDB单列索引长度不能超过767bytes,实际上联合索引还有一个限制是3072. 可以看到,由于每个字段占用255*3, 因此这个索引的大小是3825>3072,报错. 我们知道InnoDB一个page的默认大小是16k.

Mysql InnoDB锁

- - 数据库 - ITeye博客
抄自:http://www.cnblogs.com/qq78292959/archive/2013/01/30/2882745.html. Mysql常用存储引擎的锁机制. MyISAM和MEMORY采用表级锁(table-level locking). BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁.

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

- - MySQL中文网
本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的. 1、关于索引对写入速度的影响: a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%; b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;. 因此,InnoDB表最好总是有一个自增列做主键.

MySQL学习(索引、引擎、优化)

- - 数据库 - ITeye博客
索引对于查询的速度至关重要,理解索引也是数据库调优的起点. 建立索引前,先设计好建立索引列的数据类型. 1)越小的数据类型性能越好:因为越小的数据类型对于硬盘读取、内存、CPU缓存都需要更少的空间,处理起来更快. 2)简单的数据类型更好:整型比字符型更好. 3)尽量避免使用NULL: 建立索引的列最好是Not Null约束的,如果一定要用NULL,可以用0或者某特殊值替代.

Mysql Innodb 引擎优化

- 彦强 - 阿辉的空间
作/译者:吴炳锡,来源:http://imysql.cn/ & http://www.mysqlsupport.cn 转载请注明作/译者和出处,并且不能用于商业用途,违者必究. InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎. InnoDB锁定在行级并且也在SELECT语句提供 一个Oracle风格一致的非锁定读.