InnoDB存储引擎的锁分为共享锁(S锁,读锁)和排他锁(X锁,写锁)。共享锁允许多个事务并发读取数据,不允许修改;排他锁允许读取和修改数据,阻止其他事务加锁。SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE分别用于获取共享锁和排他锁。此外,还有意向锁(IX,IS)用于协调行级锁和表级锁的并发问题,意向锁在事务请求锁时自动获取。记录锁锁定索引记录,插入记录锁用于插入操作前的间隙锁定,而AUTO-INC锁确保自增列的有序性。
索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。