本文以Mysql5.7为例测试。
1:mysql索引方法
Mysql的索引方法分为btree索引和hash索引。
hash索引:是通过hash计算后比较,所以只能用于等值过滤,不能用于范围过滤。Hash索引会根据列数据维护一张hash表,所以任何时候都要进行hash表扫描,当hash表数据量庞大时,性能急剧下降。
1.pngBtree索引: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.png3:索引对锁的影响
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.pngSession1:执行下面语句,因为没有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,)。
² 如果行记录被加了共享锁,则其他事务只能在共享锁或无锁状态下读取。
网友评论