MySQL索引知识整理

背景

TokuDB引擎表按小时生成，每小时的数据量为1亿条左右，在TokuDB高压缩比下，每张表的数据文件大小为10G左右，原始数据100多G，大数据量下查询该怎么办呢，所以进行了TokuDB索引测试。

由于之前没有详细了解过各种索引创建语句的区别，只是用百度来的索引创建语句简单测试了一下，可以创建，于是就写入测试脚本中了。测试结果表明，不同的索引创建方式下，查询效率差距甚大，看来了解索引基础知识，还是必要的。

这里就来补一补自己曾经欠下的知识吧。

聚集索引

Clustering alters the data block into a certain distinct order to
match the index, resulting in the row data being stored in order.
Therefore, only one clustered index can be created on a given database
table. Clustered indices can greatly increase overall speed of
retrieval, but usually only where the data is accessed sequentially in
the same or reverse order of the clustered index, or when a range of
items is selected.

上文是维基百科中对聚集索引的解释，在我初学数据库原理的时候，知道聚簇索引，其实这俩是一个概念。上段文字，说明了聚集索引的特点：数据在索引树中的物理顺序和其在磁盘上的排序是一致的，索引树的叶子节点中存储的是真正的数据。因为不需要寻址，所以该索引方式查询效率很高。

聚集索引涉及到磁盘存储顺序，所以聚集索引只能是按一种排序方式，即聚集索引只允许创建一个。MySQL的存储引擎InnoDB的主键就是聚集索引，这就可以理解为什么主键只能有一个，主键和唯一索引的区别也在这一点上，除了“唯一性”相同外，唯一索引是非聚集索引，而主键是聚集索引。

非聚集索引

The data is present in random order, but the logical ordering is
specified by the index. The data rows may be randomly spread
throughout the table. The non-clustered index tree contains the index
keys in sorted order, with the leaf level of the index containing the
pointer to the page and the row number in the data page.

非聚集索引，数据在索引中的逻辑顺序和磁盘上的数据存储顺序不一致，索引树的叶子节点中存储的是数据的物理地址。一张表可以创建多个非聚集索引，主键以外的第二索引，就非聚集索引。

非聚集索引的复合索引是一个索引在多个列上的组合，例如：myindex(col1,col2,col3)，那么该索引生效的前提是条件是索引列左匹配有效。
该类型的索引相当于创建了三个索引myindex(col1),myindex(col1,col2),myindex(col1,col2,col3)，只有条件中列是这三种之一才会走索引。

TokuDB的聚集索引

TokuDB的索引，官网称其支持第二索引的聚集索引。

Clustering Keys and Other Indexing Improvements: TokuDB tables are clustered on the primary key. TokuDB also supports clustering secondary keys, providing better performance on a broader range of queries. A clustering key includes (or clusters) all of the columns in a table along with the key. As a result, one can efficiently retrieve any column when doing a range query on a clustering key. Also, with TokuDB, an auto-increment column can be used in any index and in any position within an index. Lastly, TokuDB indexes can include up to 32 columns.

TokuDB的主键是聚集索引，同时它还提供了clustering key，支持创建非主键索引的聚集索引。也就是说，除了主键外，它还能够同时创建多个聚集索引。建表时创建语法为：

create table mytable (
_id varchar(50) primary key,
session_id varchar(50),
request_time varchar(15),
hour varchar(200),
src_ip varchar(200),
dest_ip varchar(200),
CLUSTERING KEY ipIndex (src_ip,dest_ip) ,
CLUSTERING KEY sessionIndex (session_id)
)engine=TokuDB;

此外，TokuDB离线添加索引语法为：

alter table mytable add clustering key sessionIndex(session_id)
alter table mytable add clustering key ipIndex(src_ip,dest_ip)
另一种创建一个索引N个字段
alter table mytable add clustering key allIndex(src_ip,dest_ip，session_id)
另外用create index方式创建的是非聚集索引

TokuDB索引结构

TokuDB的覆盖索引和聚集索引，其索引树中存储的结构如下：（网上找来的图，跟维基百科定义的索引结构一致）

覆盖索引，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去查询数据。其索引树叶子节点的data是空值，索引中包含的是数据的物理地址的数据页面的信息；聚集索引，它的data值就是具体的数据。

有一个疑问，为什么TokuDB支持非主键的聚集索引呢？只找到片语，可能是跟TokuDB的高压缩能力有关。但是我还是有点想不明白，一张表的物理存储方式怎么能按多种排序方式排序呢？

查看执行计划

MySQL的explain关键字可以查看执行计划，关注type列的值（百来的图片，网络分享）：

组合索引和单列索引

测试TokuDB的索引创建，10G左右数据表，创建一个聚集索引包含N个查询字段的情况下，只会生成一个索引文件，索引文件大小跟原数据文件大小一致，耗时好几个小时。

单独对查询列创建聚集索引，时间耗时更长，每个索引会生成一个文件，上述表中创建两个聚集索引，生成两个索引文件，同时时间是累积增加的。

这两种索引查询效率相差巨大，根源是src_ip和dest_ip是一组查询条件，而session_id是一种单独的条件，不会同时出现。

大量需要根据session_id查询的记录，根本就不走索引。

TokuDB索引对比

选取数据库中两张数据量相近的表，分别以上述两种方式创建索引后，执行相同查询语句，得到执行计划如下：

从查询计划就对比来看，两种种索引方式下的相同查询效率：session_id走单列索引的情况下，仅仅扫描了三行数据，查询耗时0.04s；而全列索引查询时，type为index，几乎是全表扫描，扫描总记录为七千多万条，耗时4分钟。

启示录

TokuDB的索引创建资料很少，搜到的网页就那么几个，在不同时间和空间里找到了create index和add clustering key两种索引创建语句，但是没有意识到它俩有可能有区别，因为语句能够执行并且不报错，就分别在两个时空点里用了两种索引创建方式。

第一次建立索引是用clustering key,时隔一个月，领导又让找索引创建语句，找到的是create index,但是领导提示说以前用到是clustering key,而且以前生成的索引文件非常大，所以还让用以前的创建方式。

才惊觉，花了一上午时间搞清楚了索引的基本概念，同时得到的结论是：测试创建索引两种方式中，应该权衡一下，clustering key有效，而且按单列方式创建、有关联关系的条件可以一起创建聚集索引，session_id单独创建是有效的。

此外，索引耗时问题，无法解决，要解决查询效率必须要经过一个漫长的索引创建过程。想起以前用过的lucene全文检索和百度的搜索引擎的索引，耗时长不可避免。