WEB开发网
开发学院数据库MySQL Schema的优化和索引 - 高性能的索引策略 - 索引和... 阅读

Schema的优化和索引 - 高性能的索引策略 - 索引和锁

 2009-09-02 00:00:00 来源:WEB开发网   
核心提示:InnoDB中,索引所扮演的角色是非常重要的,Schema的优化和索引 - 高性能的索引策略 - 索引和锁,因为它们可以能让语句锁定更少的行,这是个要考虑的事情,索引和锁的一些鲜为人知的细节:InnoDB会把共享锁放置在次要索引上,但是独占锁需要访问主键,因为在MySQL5.0 InnoDB中一个事物提前之前,是不会释

InnoDB中,索引所扮演的角色是非常重要的。因为它们可以能让语句锁定更少的行。这是个要考虑的事情,因为在MySQL5.0 InnoDB中一个事物提前之前,是不会释放锁的。

如果查询语句不会检索它们不需要的行。它们将锁定更少的行。并且对于性能有提高,原因有二:首先,即使InnoDB行锁是非常有效率的并且使用更少的内存,但是行锁也会消耗一定的资源。其次,锁定很多的行就提高了锁的竞争并且降低了并发。

仅当InnoDB访问行的时候,才对它们加锁,并且一个索引可以降低InnoDB所要访问的行,因此也会降低锁。然而,这种情况只适用于在存储引擎级别中,InnoDB过滤了不期望的行。如果索引不允许InnoDB那么做,MySQL服务器就会在InnoDB取到这些值并且返回服务层之后,应用WHERE条件了。这种情况下,避免行的锁定就太晚了:InnoDB已经锁定了它们,并且服务器是不可能解锁的。

为了更好的理解我们看个例子,我们还是用以前的数据库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 |
+----------+

这个查询返回了2到4行,但是实际上它已经获得了1到4行的独占锁。InnoDB锁定了第一行,因为这个语句是个索引范围读取:

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 |
+----+-------------+-------+-------+---------+--------------------------+

换句话说,这个低级别的存储引擎操作是“从索引开始并且获取所有的行直到actor_id<5为false”。服务器是不会告诉InnoDB,WHERE actor_id <>1的条件。来看下EXTRA列的Using where。这就说明了在存储引擎返回行之后,MySQL服务器用WHERE进行了过滤。

下面的语句证明了第一行已经被锁了,即使它不会出现在第一个查询结果之中。丢掉第一个连接,开始第二个连接执行下列语句。

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

这个查询会被挂起。等待第一个事物是否第一行的锁。这个行为是为了保证基于语句级的复制是正确的。(在复制的一节会说道。)

就像这个例子所显示的,即使使用了索引,InnoDB还回会锁定它并不是真正需要的行。当不使用索引去查找和锁定行,这样的问题会更严重:如果这个语句没有索引,不管需不需要,MySQL都会扫描整张表并且锁定每个行。

下面谈一下InnoDB,索引和锁的一些鲜为人知的细节:InnoDB会把共享锁放置在次要索引上,但是独占锁需要访问主键。这就降低了使用覆盖索引的可能性并且会导致SELECT FOR UPDATE 慢于LOCK IN SHARE MODE 或者没有锁的查询。

Tags:Schema 优化 索引

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接