一步一步带你入门MySQL中的索引和锁

当然最左匹配原则还有这些规则

•  全值匹配的时候优化器会改变顺序，也就是说你全值匹配时的顺序和原先的联合索引顺序不一致没有关系，优化器会帮你调好。
•  索引匹配从最左边的地方开始，如果没有则会进行全表扫描，比如你设计了一个(a,b,c)的联合索引，然后你可以使用(a),(a,b),(a,b,c) 而你使用 (b),(b,c),(c)就用不到索引了。
•  遇到范围匹配会取消索引。比如这个时候你进行一个这样的 select 操作 

select * from stu where class > 100 and name = '张三'; 

这个时候 InnoDB 就会放弃索引而进行全表扫描，因为这个时候 InnoDB 会不知道怎么进行遍历索引，所以进行全表扫描。

索引下推

我给你挖了个坑。刚刚的操作在 MySQL5.6 版本以前是需要进行回表的，但是5.6之后的版本做了一个叫 索引下推 的优化。

select * from stu where class > 100 and name = '张三'; 

如何优化的呢？因为刚刚的最左匹配原则我们放弃了索引，后面我们紧接着会通过回表进行判断 name，这个时候我们所要做的操作应该是这样的

但是有了索引下推之后就变成这样了，此时 "李四" 和 "小明" 这两个不会再进行回表。

因为这里匹配了后面的name = 张三，也就是说，如果最左匹配原则因为范围查询终止了，InnoDB还是会索引下推来优化性能。

一些实践

哪些情况需要创建索引？

•  频繁作为查询条件的字段应创建索引。
•  多表关联查询的时候，关联字段应该创建索引。
•  查询中的排序字段，应该创建索引。
•  统计或者分组字段需要创建索引。

哪些情况不需要创建索引

• 表记录少。
•  经常增删改查的表。
•  频繁更新的字段。
•  where 条件使用不高的字段。
•  字段很大的时候。

其他

•  尽量选择区分度高的列作为索引。
•  不要对索引进行一些函数操作，还应注意隐式的类型转换和字符编码转换。
•  尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
•  多考虑覆盖索引，索引下推，最左匹配。

锁

全局锁

MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

一般会在进行 全库逻辑备份 的时候使用，这样就能确保 其他线程不能对该数据库做更新操作。

在 MVCC 中提供了获取 一致性视图 的操作使得备份变得非常简单，如果想了解 MVCC 可以参考

https://juejin.im/post/5da8493ae51d4524b25add55

表锁

MDL(Meta Data Lock)元数据锁

MDL锁用来保证只有一个线程能对该表进行表结构更改。

怎么说呢？MDL分为 MDL写锁 和 MDL读锁，加锁规则是这样的

•  当线程对一个表进行 CRUD 操作的时候会加 MDL读锁
•  当线程对一个表进行 表结构更改 操作的时候会加 MDL写锁
•  写锁和读锁，写锁和写锁互斥，读锁之间不互斥

lock tables xxx read/write;

这是给一个表设置读锁和写锁的命令，如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

这种表锁是一种处理并发的方式，但是在InnoDB中常用的是行锁。

行锁

我们知道在5.5版本以前 MySQL 的默认存储引擎是 MyISAM，而 MyISAM 和 InnoDB 最大的区别就是两个

•  事务
•  行锁

其中行锁是我们今天的主题，如果不了解事务可以去补习一下。

其实行锁就是两个锁，你可以理解为 写锁(排他锁 X锁)和读锁(共享锁 S锁)

•  共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
•  排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

而行锁还会引起一个一个很头疼的问题，那就是死锁。

如果事务A对行100加了写锁，事务B对行101加了写锁，此时事务A想要修改行101而事务B又想修改行100，这样占有且等待就导致了死锁问题，而面对死锁问题就只有检测和预防了。

next-key锁

MVCC 和行锁是无法解决 幻读 问题的，这个时候 InnoDB 使用了 一个叫 GAP锁(间隙锁) 的东西，它配合 行锁 形成了 next-key锁，解决了幻读的问题。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!