Schema optimization and indexing

In InnoDB, the role of the index is very important. Because they can allow statements to lock fewer rows. This is a matter to consider, because the lock is not released until a thing in MySQL5.0 InnoDB is ahead of schedule.

If the query statement does not retrieve rows that they do not need. They will lock in fewer rows. And for performance improvement, there are two reasons: first, even if the InnoDB row lock is very efficient and use less memory, but the row lock will also consume a certain amount of resources. Second, locking a lot of rows increases the lock's competition and reduces concurrency.

Locks are innodb only when the rows are accessed, and an index reduces the rows to be accessed by InnoDB, thereby reducing the lock. However, this applies only to the storage engine level, where InnoDB filters the rows that are not expected. If the index does not allow InnoDB to do so, the MySQL server applies the Where condition after InnoDB takes these values and returns to the service layer. In this case, it is too late to avoid locking the rows: InnoDB has locked them and the server is impossible to unlock.

To better understand our example, we still use the previous database Sakila

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id < 5
　　 　-> AND actor_id <> 1 FOR UPDATE;
+----------+
| actor_id |
+----------+
| 　 　 　 　2 |
| 　 　 　 　3 |
| 　 　 　 　4 |
+----------+

This query returns 2 to 4 rows, but in fact it has obtained an exclusive lock of 1 to 4 rows. InnoDB locked the first line because the statement is read as an index range:

mysql> EXPLAIN SELECT actor_id FROM sakila.actor
　　 　-> WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
+----+-------------+-------+-------+---------+--------------------------+
| id | select_type | table | type 　| key 　 　 | Extra 　 　 　 　 　 　 　 　 　 　|
+----+-------------+-------+-------+---------+--------------------------+
| 　1 | SIMPLE 　 　 　| actor | range | PRIMARY | Using where; Using index |
+----+-------------+-------+-------+---------+--------------------------+

In other words, this low-level storage engine operation is "start from the index and get all the rows until Actor_id<5 is false." The server is not going to tell Innodb,where actor_id <>1 conditions. Look at the using where of the extra column. This means that after the storage engine returns rows, the MySQL server uses a where to filter.

The following statement proves that the first row has been locked, even if it does not appear in the first query result. Drop the first connection and start the second connection to execute the following statement.

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;

This query will be suspended. Waiting for the first thing to be the first line of the lock. This behavior is to ensure that the replication based on the statement level is correct. (In the Copy section.) ）

As this example shows, even if an index is used, InnoDB still locks it out of the line that is not really needed. This is even more problematic when you do not use an index to find and lock rows: If this statement is not indexed, MySQL scans the entire table and locks each row, regardless of the need.

Here are some little-known details of InnoDB, indexing and locking: InnoDB places shared locks on secondary indexes, but exclusive locks require access to primary keys. This reduces the likelihood of using a overwrite index and causes the select for UPDATE to be slower than the lock in SHARE MODE or the query without locks.