在这篇博文我将介绍MySQL的索引结构如何实现非常重要的查询优化, 并且不同的存储引擎之间的不同。我还将展示如何知道并预测何时触发优化, 怎样去设计表和查询, 并且避免糟糕的查询。额外的会介绍innodb内部结构, 展示它背后到底发生了什么。

一、Mysql的主键索引和二级索引

你需要明白Mysql索引是如何工作的, 并且Innodb和其它存储引擎的不同, 例如Myisam, 因为如果你不知道, 你可能设计不了高效的表。

Innnodb:

Innodb存储引擎为每张表创建了一个聚簇索引, 如果这个表存在primary key(主键), 那么主键将会是聚簇的索引。如果没有指定主键, 那么InnoDB内部为每行分配一个六字节唯一的ID,并将其用作聚簇索引。(PS: 挑选自己的主键, 不要让它为你生成一个无用的!)

所有的索引是B-trees结构。在Innodb中, 主键的叶子节点就是存储数据值。二级索引具有指向其叶节点上的数据的指针, 如下图:

更加形象的图:

Myisam:

Myisam没有聚簇索引, 所以任何索引数据没有物理顺序(它是按照插入顺序), 但在Innodb, 通过primary key的关系这个行是有物理顺序的, 意味着在行中间位置插入数据可能会造成页分裂, 如果数据超过页大小, 则页必须被分裂。Myisam就没有这个问题, 因为它始终是以追加的方式插入, 也就是在后面插入, 也就是二级索引的叶子节点总是直接指向数据行。 实际上Myisam的主键索引和其它的二级索引在功能上没有任何区别。Myisam主键只是一个名为”PRIMARY”的唯一索引。

下图是Myisam引擎的结构图, 注意它和Innodb的不同:

更加形象的图:

 

为什么Innodb没有像Myisam一样直接指向数据行的物理地址呢?如果innodb使用这种策略, 在每次页分裂时他将需要重写所有的二级索引, 或者当行被移动到磁盘的不同地方时也是如此。为了避免这种开销, Innodb巧妙的把二级索引的叶子节点只存储该二级索引的列值和一个对应的主键值。它使得二级索引依赖主键索引的物理顺序。而不是如Myisam一样直接指向数据行的物理地址。但这样来说, innodb的二级索引在查询时会多一次主键索引的查找, 能想过来这个逻辑吗?也就是二级索引得先找到对应的记录, 然后根据这个索引中存储的主键值再去得到主键索引中存储的真正数据!但可想而知Myisam没有这样的问题。但Myisam它没有索引顺序的行, 或者说主键可能会”更深”, 两者互有利弊, 权衡而视。

二、二级索引优化

因此,InnoDB中的二级索引是有代价的。 还有一个优化。 一旦查询导航到二级索引的叶子节点,它就知道两件事情:用于查找索引的值以及被存储的相应的主键值。

例如, 我创建了如下的表:


create table apples(
 variety varchar(10),
 note varchar(50),
 price int,
 primary key(variety),
 key(price)
) engine=InnoDB;

insert into apples values
('gala', 'hello', 5),
('fuji', 'hello', 6),
('limbertwig', 'hello', 8),
('red delicious', 'hello', 3),
('pippin', 'hello', 8),
('granny smith', 'hello', 11),
('roma', 'hello', 6);

记住只有’gala’这一行的price值是5, 现在假定我有如下查询:

select variety from apples where price = 5;

这个查询为了得到price为5的值会使用price索引, 当它得到叶子节点的数据时, 它发现值’gala’, 它能使用这个值再去检索primary key。但因为查询的列正好是variety, 正是它需要的!

实际上, 如果查询仅仅只需要使用到二级索引的数据列, 它不需要离开二级索引再去查找, 如果你真要说, 可以叫它索引”覆盖”这个查询, 因此他是一个”覆盖索引”或者”索引覆盖”。

这是一个神奇的优化。他意味着每个二级索引像其它表一样, 聚簇索引优先。在这个例子中, 二级索引只包含了price和variety, 按照顺序聚集(再次参考上面相应的图)。

在MyISAM中,也可以使用”不要离开索引”优化, 但只有当查询仅引用索引本身的值时, 因为MyISAM索引在其叶节点没有任何PK值。 MyISAM索引不能用于查找任何附加数据, 而不必遵循指向行本身的指针。

三、怎么知道何时使用优化

理论上, 只要查询只使用聚簇索引中的值和InnoDB中的二级索引,或者仅使用MyISAM中的索引本身的值, 则可以使用优化。这并不意味着查询将使用该索引。 由于各种原因, 查询可能会使用一些其他索引。如果Extra列包含文本”Using index”, 则使用了优化。

一旦了解索引的工作原理, 就可以对索引作出有意义的决定。这是设计索引的有条理的方法。从表和数据开始, 但没有任何索引, 除了旨在将数据约束到有效值(主索引和唯一索引)之外的索引。接下来, 考虑针对表发出的查询。是查询特设还是重复发生某些类型的查询?这是非常重要的知道。

在开始之前, 请考虑表的大小以及使用的大小。你应该将你的优化工作放在最需要的地方。如果一个5分钟的查询每天运行一次, 并且你知道应该可以将其优化为5秒, 即节约4分55秒。如果每分钟发出另一个查询需要5秒钟, 并且你知道应该可以在几毫秒内运行它, 这将节省约7000秒。你应该首先优化第二个查询。你还应考虑精心设计存储, 以使这些表尽可能小。

现在, 回到索引设计讨论。如果一直查询表, 你需要创建一般有用的索引。大多数时候你应该检查数据, 弄清楚它们应该是什么。假装你正在优化上面的apples表。该表可能不需要note列上的索引。看看它的内容 – 每一行都说”hello”。索引这将是一个浪费。此外, 你想查看的note列似乎是合理的, 但不能通过它过滤。另一方面, 您希望通过price查询apples表是非常合理的。price索引是一个不错的选择。

另一方面,如果你知道有一个特定的查询发生在所有的时间, 需要非常快, 你应该考虑特别优化的索引。假设这两个查询每秒运行50次:


select variety from apples where price = ?;
select note from apples where price = ?;

这些查询值得一看。优化策略将取决于表的大小和存储引擎。

如果你正在使用InnoDB引擎, 则第一个查询已经如上所述进行了优化。它将使用price index,甚至不用查找apples table。如果你使用的是MyISAM引擎, 则需要考虑table的大小,index(price, variety)有多大。例如,如果table非常大,那么如果有一大堆varchar列,那么这个复合索引可能明显快于查找仅包含price列的单列索引。

第二个查询是非常棘手的优化, 因为它真的取决于表的大小。如果表格非常大,并且具有我前面提到的许多其他列,那么在(price,note)上创建一个索引可能是有意义的。这是需要仔细测试的地方。我将在后期的博文中解释如何进行该测试。不幸的是, 这在MySQL中是不重要的。

总体战略如下:

对于InnoDB,将where子句中的列放在索引的左边,然后再考虑将SELECT子句中出现的列添加到索引的后面,除非它们已经包含在primary key中。对于MyISAM, 将where子句中的列放在索引的左边, 然后再考虑将SELECT子句中出现的列添加到索引的后面。

四、怎样写好的sql查询

很多人倾向于编写SELECT * FROM …查询。 如果你不需要所有的列, 请不要选择所有列, 因为它可能造成慢的查询。如果你只选择所需的列, 你的查询可以使用我刚解释的优化之一。 如果你查询每一列并且查询使用了一个二级索引, 则无法做到这一点,并且查询将不得不在查找所有的索引来得到它所需要的行,然后执行其它操作以从行中获取实际值。

当然, 如果你只需要几列, 而你查询了所有的列甚至这些列都是很小的数据。但将数据从磁盘上取出并过滤多余的数据是很大的开销。除非你需要, 否则不要这样做。

五、其他InnoDB索引的设计考虑

由于InnoDB二级索引已经包含了所有的主键列, 因此无需将它们添加到二级索引中,除非索引需要前缀索引。特别是,在上面的apples表中添加(price, variety)的索引是完全多余的。在主键是存在多列的表中,通过使用我已经解释的索引来使表”聚集的两种方式”是可取的,但并不是所有的列都需要添加到其他索引中。索引需要非常仔细地设计,以免造成一大堆额外的开销。每个索引都会造成额外的开销,避免增加开销但没有收益的索引是非常重要的。

假设你添加了一个索引(price,variety)到apples表。你可能会认为,只能从内部节点中优化种类列,因为值已经在叶节子点。这是不可能的,因为主键值仅在叶子节点而不是内部节点,并且它们不能从内部节点进行优化,因为它们是索引定位所必需的。再次,将该列添加到索引的末尾只会使索引更大,这是没用的。

我想说,并不是索引总能优化表,确保每个查询都可以在不离开索引的情况下得到最终结果并不一定是一个好的设计目标。这是不切实际的。但在特殊情况下,这可能是值得的。

六、其它innodb优化

这是另一个高效的优化: 一个微小的索引可能会被特殊地使用, 例如:


create table something (
 id bigint not null auto_increment primary key,
 is_something tinyint not null,
 othercol_1 bigint not null,
 othercol_2 bigint not null,
 othercol_3 bigint not null,
 index(is_something)
);

is_something是关于该行是否为真的1/0状态值。通常我会说一个索引会浪费磁盘和CPU,因为对查询优化器如何去使用它没足够的选择,假设1和0的分布数量一致。 但事实上,对于一些查询它的作用非常小。 例如,查询sum(id)将扫描is_something索引,因为它足够轻量级。 它的内部节点只有一个字节的tinyint值,叶子节点有一个tinyint和一个8字节的bigint。 这比聚簇索引小得多,因为聚簇索引(主键)在内部节点中有8个字节的值,每个叶子的字节数为33个字节。

七、InnoDB自动创建聚簇索引的证明

我说如果没有主键,每个InnoDB表都会获得一个6字节的内部聚簇索引。我创建了一个这样的表:


create table test(
 a int,
 b int,
 c int
 )engine = InnoDB;

insert into test values (1,1,1),(2,2,2);

我开始一个事务并获得一个排它锁, 然后在另一个连接上启动另一个事务,并试图更新该表:


-- connection 1:
 start transaction;
 select * from test;

-- connection 2:
 start transaction;
 update test set a = 5;

查询被阻塞并且等待锁分配。然后在期中一个connection中执行show engine innodb status, 显示的信息如下, 它包含了内部生成的主键(聚簇)索引:


---TRANSACTION 0 81411, ACTIVE 1410 sec, process no 8799, OS thread id 1141414240 starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 1216
 MySQL thread id 4, query id 194 localhost xaprb Updating
 update test set a = 5
 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 0 page no 131074 n bits 72 index `GEN_CLUST_INDEX` of table `test/test` trx id 0 81411 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 6; hex 000000018a02; asc       ;; 1: len 6; hex 000000013e0a; asc     > ;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000001; asc     ;; 4: len 4; hex 80000001; asc     ;; 5: len 4; hex 80000001; asc     ;;

请注意,索引上的锁称为GEN_CLUST_INDEX。还要注意锁结构中的字段数(n_fields):比表中的列数多两个。索引中的第一个字段是内部生成的唯一值,如上所述,它是6个字节。

如果在a列上添加主键, 它的信息是不同的:


 ---TRANSACTION 0 81456, ACTIVE 17 sec, process no 8799, OS thread id 1141680480 starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 4 lock struct(s), heap size 1216
 MySQL thread id 9, query id 277 localhost xaprb Updating
 update test set a = 5 where a = 1
 ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 0 page no 131076 n bits 72 index `PRIMARY` of table `test/test` trx id 0 81456 lock_mode X locks rec but not gap waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000013e27; asc     >';; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000001; asc     ;; 4: len 4; hex 80000001; asc     ;;

现在锁在名为PRIMARY的索引上, 锁结构中只有5个字段,第一个是4个字节而不是6。字段值为2的十六进制值为80000001。当主键为某一列时,字段将第一个出现在锁结构中。

这些示例证明,当你不创建主键时,InnoDB会为表添加”隐藏列”。也许我经常这样说,但是你应该总是创建一个精心设计的主键,因为如果没有,你会得到InnoDB给你的最好的东西之一:聚簇索引。

总结

你对索引的工作原理了解越多, 你的数据库的优化就好。有时, 这些优化不会有太大的帮助, 但有时它们是巨大的。在本文中,我解释了InnoDB的主键和二级索引与其他存储引擎的不同之处。既然你了解这些差异,你可以了解每个存储引擎的优化和最优权衡,以及如何利用优化,并尽可能避免缺点。我向你展示了索引设计的几个副作用,例如查询扫描二级索引而不是表,并进入了一些InnoDB内部结构,以了解表的主键是如何工作的。