MySQL-索引优化篇(3)_利用索引优化锁

---

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

---

利用索引优化锁

为什么索引能优化锁

Innodb采用的行级锁，只有在修改行时才会对需要修改的行加锁。 但是这种情况只有在Innodb层过滤掉不需要的行是才有效。

如果存储引擎层不能过滤掉不需要的行，则需要在内存中锁定所有的行，在内存中进行过滤。

所以利用索引可以过滤掉不需要的数据， 使用索引的话，仅需要锁定被索引检索出来的数据，而不是锁定全部数据，从而达到优化锁的目的。

• 索引可以减少锁定的行数
• 索引可以加快处理速度，同时也加快了锁的释放

---

演示

举个例子 （演示锁， 肯定需要两个会话了）

无索引的情况 （获取不同的数据 发生了阻塞）

session 1 :

mysql> show create table actor \G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> drop index idx_actor_last_name on actor ;  #Step1 先拿掉 last_name的索引
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> explain select * from actor where last_name = 'WOOD' \G;    #Step2  执行下执行计划
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL   --------------------> 全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where  --------------------> 全表扫描后，通过where来过滤 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 


mysql> begin ;    #  Step3 开启事务 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where last_name = 'WOOD' for update ;  -----> Step4  通过for update ,加入一个排它锁 
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> 

新开一个会话 session 2 ：

行级锁，咋把willis 的查询也阻塞了呢？ 就是因为没有索引 ，走了全表扫描，然后通过where 过滤。 把全表的数据都锁定了， 你查willis 自然也被阻塞了。 如果有索引的话，其实就仅仅会锁定 WOOD 对应的数据行。其他数据的操作都是可以的，我们接下来通过实验来验证下

---

有索引的情况 （获取不同的数据 未阻塞）

我们把会话一和会话二 ，rollback 掉。 然后把last_name的索引加上去，然后重新做下试验 。

会话一：

mysql> 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> create index idx_actor_last_name on actor(last_name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain  select * from actor where last_name = 'WOOD' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where last_name = 'WOOD' for update ;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> 

会话二：

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where last_name = 'willis' for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       83 | BEN        | WILLIS    | 2006-02-15 04:34:33 |
|       96 | GENE       | WILLIS    | 2006-02-15 04:34:33 |
|      164 | HUMPHREY   | WILLIS    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> 

可以看到，建立完索引后，会话二查询 willis 数据， 可以获取到结果， 因为会话一的事务中通过索引仅在内中锁定了WOOD对应的两条数据，其他数据是没有被锁定的。 ------》可以看到索引对锁的优化后， 增加了并发，提高DB的性能

当然了，你要是会话二和会话一查询的都是统一批数据，比如都是WOOD，因为会话一未提交， 会话二肯定会被阻塞的。 这里简单提一下。

---