MySQL UPDATE能走索引但产生表锁的原因

1、MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

2、产生表锁的一些原因

• 索引失效
• 查询字段未加索引

3、思考：针对where查询条件字段增加索引后，索引未失效，update时会产生表锁吗？

-- 对name增加了索引
-- 事务A
start transaction;
update Test set appkey = 'XXX' where name = "test5";
COMMIT;
​
-- 事务B
start transaction;
update Test set appkey = 'XXX' where name = "lisi";
COMMIT;

经过测试，是有可能产生表锁的。

4、索引未失效但产生表锁的原因

MySQL内部有优化器，当随机扫描的数量超过一定比例时（一般是20% ～ 30%），优化器会决定直接改成全表扫描。所以有时会产生表锁。

5、官方文档上的介绍，http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/where-optimization.com.coder114.cn.html

查询每个表索引，并使用最佳索引，除非优化程序认为使用表扫描更有效。 一次使用扫描是基于最佳索引是否跨越了表的30％以上，但是固定百分比不再决定使用索引还是扫描。 现在，优化器更加复杂，并且根据附加因素（如表大小，行数和I / O块大小）进行估计。

(Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.)

6、总结

• 不管检索还是更新，都尽可能利用索引。
• 不要一次性检索或更新大批量数据，建议分批操作。
• 事务尽快提交，降低行锁持有时间及其影响。