MySQL 优化 锁

MySQL 锁

 

1.锁使用情况

（1）查看表级锁竞争情况

show status like 'table_locks%';

说明：

    Table_locks_immediate：能够立即获得表级锁的锁请求次数

    Table_locks_waited：不能立即获取表级锁而需要等待的锁请求次数

（2）查看InnoDB行锁竞争情况

show status like 'innodb_row_lock%';

说明：

    Innodb_row_lock_current_waits：当前正在等待锁的数量；

    Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

    Innodb_row_lock_time_avg：每次等待所花平均时间；

    Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间长度；

    Innodb_row_lock_waits：系统启动到现在总共等待的次数；

2.InnoDB行锁

    InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

注意：

(1)如果不通过索引条件检索数据，InnoDB将对表中所有记录加锁，效果如同表锁；

(2)即便是访问的不同的记录，如果使用了相同的索引键，仍然会发生锁冲突，

例：（表 user_table 中 对Id、name列分别建立了索引）

session_1 执行：
   set autocommit=0;
   select *from user_table where name = '张三' and id=1 for update;

session_2执行：

    select *from user_table where name = '张三' and id=2 for update;（等待）

当session_1 执行 commit 后session_2才会执行。

(3)表中有多个索引时，不同的事务可以使用不同的索引锁定不同的行，

例：（表同上）

session_1 执行：
    set autocommit=0;
    select *from user_table where name='张三' for update;

session_2执行：

    select *from user_table where id=2 for update;（等待）

session_1 会锁定id为1、2的两条记录，当session_1 执行 commit 后session_2才会执行。

(4)即便在检索条件中使用了索引，但mysql如果认为全表扫描效率更高，它就不会使用索引。

例：

explain select *from user_table where name = '1';（使用索引）

explain select *from user_table where name = 1;（不使用索引）