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维护表和索引的碎片，注意该命令使用不当会导致锁表，建议再数据库不使用的时候再使用该命令