# 6.5索引优化锁

## 利用索引优化锁

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

## 演示

我们使用actor表演示

首先我们演示，无索引时的情况

先删除actor表的idx\_actor\_last\_name索引

```
show create table actor\G
drop index idx_actor_last_name on actor;
explain select * from actor where last_name='WOOD'\G
```

发现查询的时候影响到100行数据

session 1

```
use sakila
begin;
select * from actor where last_name='WOOD' for update;
```

session 2

```
use sakila
begin;
select * from actor where last_name='willis' for update;
```

发现虽然session 1和session 2虽然查找的数据不一致,但session 2却被阻塞了

> 注意：实验完后记得回滚事务
>
> session 1
>
> ```
> rollback;
> ```
>
> session 2
>
> ```
> rollback;
> ```

接着我们演示，有索引时的情况

添加之前删除的idx\_actor\_last\_name索引

```
create index idx_actor_last_name on actor(last_name);
explain select * from actor where last_name='WOOD'\G
```

可以发现，查询的时候只影响到2行数据

session 1

```
use sakila
begin;
select * from actor where last_name='WOOD' for update;
```

session 2

```
use sakila
begin;
select * from actor where last_name='willis' for update;
```

发现session 2没有被阻塞

## 索引的维护和优化

### 删除重复和冗余的索引

重复索引示例：primary key(id),unique key(id),index(id)

冗余索引示例１：Index(a),index(a,b)

冗余索引示例２：primary key(id),index(a,b)

> 提示：对于B-tree索引，每一个二级索引之后都会增加主键的信息

### 如何知道哪些索引是重复的和冗余的呢？

可以下载安装pt-duplicate-key-checker工具进行检测

pt-duplicate-key-checker h=127.0.0.1

### 查找未被使用过的索引

```
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
```

### 更新索引统计信息及减少索引碎片

mysql的查询优化器，会根据索引统计信息来决定使用那个索引来优化查询，所以索引统计信息如果不准确，查询优化器就可能做出错误的判断。

可使用`analyze table table_name`重新生成索引统计信息

可使用optimize table table\_name维护表和索引的碎片，注意该命令使用不当会导致锁表，建议再数据库不使用的时候再使用该命令