MySQL索引知识整理

原创 2017年09月02日 08:19:55

背景

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全文检索和百度的搜索引擎的索引,耗时长不可避免。

版权声明:本文为博主原创文章,未经博主允许不得转载。

Java知识整理——基础知识

什么是JVM? 为什么称Java为跨平台的编程语言? Java虚拟机(Java Virtual Machine)是可以执行Java字节码的虚拟机,每个Java源文件将被编译成字节码文件,然后在JVM...
  • Lnho2015
  • Lnho2015
  • 2015年05月09日 15:12
  • 486

传输层知识点

传输层
  • guitarhensom
  • guitarhensom
  • 2017年04月13日 21:28
  • 283

mysql整理的一些重点知识点

mysql sql 数据库
  • fqf_520
  • fqf_520
  • 2015年09月28日 16:33
  • 266

各种数据结构的知识点归纳

各种数据结构 By SemiWaker
  • semiwaker
  • semiwaker
  • 2017年03月14日 09:02
  • 322

JavaEE知识整理系列(一)JavaEE概述

1.        JavaEE基本概念 JavaEE 是 J2EE的一个新的名称,之所以改名,目的还是让大家清楚J2EE只是Java企业应用。但那是需要一个跨J2SE/WEB/EJB的微容器,保护我...
  • ICTCamera
  • ICTCamera
  • 2013年11月01日 21:19
  • 953

由浅入深探究mysql索引结构原理、性能分析与优化

摘要: 第一部分:基础知识 第二部分:MYISAM和INNODB索引结构 1、 简单介绍B-tree B+ tree树 2、 MyisAM索引结构 3、 ...
  • zhuoxiong
  • zhuoxiong
  • 2013年08月30日 01:08
  • 14707

Java注解知识点整理

注解:是元数据,可以声明在包、类、属性、方法、局部变量、方法参数等前面,用来对这些元素进行说明、注释。...
  • wen294299195
  • wen294299195
  • 2015年11月28日 15:44
  • 598

mysql索引知识

1.索引的概念   通过使用 数据中的部分数据作为关键字,建立该关键字与数据间位置的对应关系,称之为索引    索引的关键字,一定是排序过的。 查找对应的记录时,先在索引中进行检索,快速的定位为该关...
  • xiaowan206
  • xiaowan206
  • 2017年04月25日 20:34
  • 132

mysql索引整理

索引 b+树索引 常用:硬盘数据(操作系统的文件系统) b+树特点 数据有序  插入和查询时间复杂度: lgn 树介绍: 平衡二叉树 限制:左子树和右子树的高度差最大是1 多做内存...
  • u014132480
  • u014132480
  • 2017年09月30日 10:23
  • 29

Java知识点整理:第一章:基础知识梳理

把java知识给总结下,也是我自己一个梳理的过程,可能这辈子这种事情就会干这一次了,好好梳理梳理脑子里面的知识。 学习Java其实没有大家想想中的那么困难,有一句话说的很好,却往往被大家所忽视,那就是...
  • junlei0426
  • junlei0426
  • 2016年11月06日 20:27
  • 509
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL索引知识整理
举报原因:
原因补充:

(最多只允许输入30个字)