美文网首页技术
Mysql索引与锁

Mysql索引与锁

作者: 那些年搬过的砖 | 来源:发表于2019-06-18 08:54 被阅读0次

    本文以Mysql5.7为例测试。

    1:mysql索引方法

    Mysql的索引方法分为btree索引和hash索引。

    hash索引:是通过hash计算后比较,所以只能用于等值过滤,不能用于范围过滤。Hash索引会根据列数据维护一张hash表,所以任何时候都要进行hash表扫描,当hash表数据量庞大时,性能急剧下降。

    1.png

    Btree索引:BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中。

    2:组合索引

    组合索引index1(c1,c2)

    Sql语句 是否使用索引
    select * from t1 where c1=1 and c2=2
    select * from testindex where c2>'1' and c1<'2'
    select * from t1 where c1=1
    select * from t1 where c2=2 ×

    结论:如果需要使用c2过滤时使用索引,必须同时用c1过滤

    2.png
    3:索引对锁的影响

    Innodb存储引擎下,mysql锁是通过锁索引实现。

    Mysql在innodb下默认是表级锁。可通过下面实例看出。

    我们创建表testnoindex:

    
    CREATE TABLE `testnoindex` (
    
      `id` int(11) NOT NULL AUTO_INCREMENT,
    
      `c1` varchar(255) DEFAULT NULL,
    
      PRIMARY KEY (`id`)
    
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    

    Id为主键索引,c1为普通字段,初始化一些数据方便查看

    3.png

    开启两个事务窗口

    Session1:设置不自动提交,并执行update语句,根据条件可以看出,更新的是id为10的记录,执行完如果没有显式调用commit,则会锁整个表。

    SET AUTOCOMMIT=0;

    update testnoindex set c1='2017-08-0209:52:21' where c1='2017-08-02 10:47:20';

    注意:c1一定是无索引情况,如果有索引(非唯一索引),都不会锁整张表。

    session2:更新id为1的记录,获取锁超时。

    4.png

    以上实例可以证明mysql innodb存储引擎默认是表级锁,当然innodb是支持行级锁的,比如通过唯一主键就可实现行级锁。

    如果where条件的索引数据是多行的话,多行数据会同时被锁定,可理解为范围锁。

    Mysql有三种锁Record,Gap, and Next-KeyLocks。Recode为行锁,Gap为间隙锁,Next-KeyLocks为前两种的结合。我们先看一个实例,加入mysql中记录为如下所示:

    5.png

    Session1:执行下面语句,因为没有7的记录,会向左扫描至id=2的记录,向右扫描至id=8的记录,锁定区间为(2,8)。

    set autocommit=0;
    
    update testnoindex set c1='2017-08-0210:47:09' where id=7;
    

    session2:执行如下语句,可以看出id=4的记录被锁定。

    INSERT into testnoindex (id,c1)values(16,NOW());
    
    INSERT into testnoindex (id,c1)values(4,NOW());
    
    [SQL]INSERT into testnoindex (id,c1)values(16,NOW());
    

    受影响的行: 1

    时间: 0.087s

    [SQL]
    
    INSERT into testnoindex (id,c1)values(4,NOW());
    
    [Err] 1205 - Lock wait timeout exceeded;try restarting transaction
    
    4:共享锁与排他锁

    Mysql行级锁又分为共享锁(读锁或S锁)和排他锁(写锁或X锁),比如forupdate就是排它锁。Mysql默认delete,insert,update都是排它锁,select默认无锁。

    ² 如果行记录被加了排它锁,则其他事务无法再在其上加锁,也就是加锁状态无法读取/修改。换句话说就是如果被加了排他锁的行,不能再加排他或共享锁,但是可以无锁读取(普通select,)。

    ² 如果行记录被加了共享锁,则其他事务只能在共享锁或无锁状态下读取。

    相关文章

      网友评论

        本文标题:Mysql索引与锁

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