Mysql索引与锁

本文以Mysql5.7为例测试。

1：mysql索引方法

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

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

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过滤

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为普通字段，初始化一些数据方便查看

开启两个事务窗口

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的记录，获取锁超时。

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

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

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

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,）。

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