MySQL索引 专题

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL索引 专题

developerguy 2016-08-29 01:01:00 浏览834
展开阅读全文

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构,索引类似一本书的目录,我们可以快速的根据目录查找到我们想要的内容的所在页码,索引的优化应该是对查询性能优化最有效的手段了。

因此,首先你要明白的一点就是,索引它也是一个文件,它是要占据物理空间的。

Digest:
索引在文件系统中的体现:
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中,以便于快速查找;
2. 索引存在于磁盘中,会占据物理空间。因此不恰当的索引会影响性能

存储引擎与索引类型的区别:
1. 不同的存储引擎可能支持不同的索引类型;
2. 不同的存储引擎对同一种索引类型可能有不同的实现方式。

目前的情况:
1. B-Tree索引使用最广泛,主流引擎都支持。
2. 哈希索引性能高,适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

B-Tree索引与唯一索引,主键索引,普通索引的关系
B-Tree只是底层的算法实现,
唯一索引,主键索引,普通索引都是基于B-Tree索引算法的,
只不过又有各自的特点。

 

索引 在文件系统中的体现:

比如对于MyISAM存储引擎来说:
.frm后缀的文件存储的是表结构;
.myd后缀的文件存储的是表数据;
.myi后缀的文件存储的就是索引文件。
如下图所示:

对于InnoDB 存储引擎来说:
.frm后缀的文件存储的是表结构。
.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table 参数)

如下图所示:


因此,当你对一张表建立索引时,索引文件的大小也会改变,当你数据表中的数据因为增删改变化时,索引文件也会变化的,只不过MySQL会自动维护索引,这个过程不需要你介入,
这也是为什么不恰当的索引会影响MySQL性能的原因

总结:
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中,以便于快速查找;
2. 索引存在于磁盘中,会占据物理空间。

索引的类型
上面说到,索引文件时按照不同的数据结构来存储的,数据结构的不同也产生了不同的索引类型,常见的索引类型包括:

B-Tree索引
哈希索引
空间数据索引(R-Tree)
全文索引
下面做一一介绍:

1. B-Tree索引
B-Tree索引是最常用的一种索引,如果没有指定特定的类型,那么多半就是B-Tree索引,事实上,很多搜索引擎使用的是它的变种B+Tree,这是对B-Tree的一个优化,如果需要详细了解,可以参考数据结构方面的书籍,这里不做详细探讨。
以下统称为B-Tree索引。
绝大多数的存储引擎,比如MyISAM和InnoDB都支持这种索引,因此说它是应用最广泛,最常用的一种索引方式,但是不同的存储引擎在具体实现时会稍有不同,比如MyISAM会使用前缀压缩的方式对索引进行压缩,InnoDB则不会。
下图展示了B-Tree索引是如何存储被索引的数据的:

说明:
左图是一个包含三列的数据表,右图则展示了数据是如何被索引的。
可以看出B-Tree是对索引列是按照顺序存储的,每个叶子节点指向被索引的数据,这也是B-Tree索引支持范围查找数据的原因。

2. 哈希索引
相比于B-Tree索引,哈希索引的实现就比较简单了,它是基于哈希表来实现的,对于要索引的列,存储引擎会计算出一一对应的哈希码,然后把哈希码存放在哈希表中作为key,value值是指向该行数据的指针。
下图是简单的原理展示:

说明:
1、左边紫色图表示一个二列的数据表。
2、中间表示对fname列进行哈希索引,计算出哈希值,因为位置不确定,所以不支持范围查找。
3、右边绿色图表示把生成的哈希值存放于哈希表中。

当我们执行以下查询时:

select * from testTable where fname = "mary";

 

MySQL会首先计算查询条件mary的哈希值,然后到哈希表中去找该哈希值,如果找到了根据对应的指针也就找到了需要寻找的数据行。

哈希表的优势与限制:

优势:
只需比对哈希值,因此速度非常快,性能优势明显;
限制:
不支持任何范围查询,比如where price > 150,因为是基于哈希计算,支持等值比较。
哈希表是无序存储的,因此索引数据无法用于排序。
主流存储引擎不支持该类型,比如MyISAM和InnoDB。哈希索引只有Memory, NDB两种引擎支持。
因此,哈希索引虽然速度快,但其实使用很受限,只适用于某些特殊的场合。

3. 空间数据索引(R-Tree)
空间索引可用于地理数据存储,它需要GIS相关函数的支持,由于MySQL的GIS支持并不完善,所以该索引方式在MySQL中很少有人使用。

4. 全文索引
全文索引主要用于海量数据的搜索,比如淘宝或者京东对商品的搜索,你不可能使用like进行模糊匹配吧,
MySQL从5.6开始支持InnoDB引擎的全文索引,功能没有专业的搜索引擎比如Sphinx或Solr丰富,如果你的需求比较简单,可以尝试一下MySQL的全文索引,否则建议使用专业的搜索引擎。

总结:
1. B-Tree索引使用最广泛,主流引擎都支持。
2. 哈希索引性能高,适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

索引和存储引擎之间的关系
上面讲述了索引有不同的类型,存储引擎也有不同的类型,那么索引和存储引擎之间有什么关系呢?
首先你需要知道,在MySQL中,索引是在存储引擎中实现的,并不是所有的存储引擎都支持所有的索引类型,比如哈希索引,MyISAM和InnoDB是不支持的;
同样,即使对于同一类型的索引,不同的存储引擎实现的方式也可能是不同的,比如MyISAM和InnoDB对B-Tree索引,具体的实现是有差别的。

总结:
1. 不同的存储引擎可能支持不同的索引类型;
2. 不同的存储引擎对同一种索引类型可能有不同的实现方式。

B-Tree索引与唯一索引,主键索引,普通索引的关系
最开始对B-Tree索引与唯一索引,主键索引,普通索引这几种索引的关系很模糊,网上也没搜索到相关的资料,以为他们的关系是并列的,
其实并不是,B-Tree只是底层的算法实现,唯一索引,主键索引,普通索引都是基于B-Tree索引算法的,只不过又有各自的特点。
通过下图也可看出这种关系:

 

https://segmentfault.com/a/1190000010264071

 

索引的类型

索引是在MYSQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同。

B-Tree索引是最常见的索引类型,也是创建索引时默认的类型。
B-Tree是一种多叉平衡树,B-Tree 结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。一般用于数据库的索引,综合效率较高。

等值匹配 可用于

=
!=
<>
IN
NOT IN
<=>

查询语句的优化 范围匹配

可用于

>
>=
<
<=
BTEWEEN AND

等范围查询语句的优化 匹配最左前缀

name like bai%

这种语句,是可以使用name字段上建立的索引来优化查询的,但是对于

name like %bai

则没有办法使用索引了 

覆盖索引

覆盖索引是指所有需要查询的字段都在索引已经存在了,那么就不需要再去查询数据了,这种查询效率很高。

select id where id >100

排序

BTREE索引还可以用于查询中的order by 操作。

哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引,使用哈希索引可以一次定位,所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

  • 只有精确匹配索引所有列的查询才有效,因为哈希索引是利用索引的所有列的字段值来计算哈希值的,
  • 只支持等值比较查询,不能用于范围查询。
  • 哈希索引的只包含索引字段的哈希值he和指向数据的指针,所以不能使用索引中的值来避免读取行。
  • 哈希索引的数据并不是顺序存储的,无法用于排序。

全文索引

聚集索引&非聚集索引

聚集索引并不是一种单独的索引类型,而是一种数据存储方式,Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。

 

 

InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会用一个唯一且不为空的索引列做为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键。

InnoDB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值,然后再根据主键值去聚集索引中查询到对应的数据。

InnoDB将主键与数据聚集在一起的方式,使得按主键顺序的插入和查询效率会很高,更新主键或者不按主键的顺序插入数据的代价会比较高,
所以主键的选取很重要(使用AUTO INCREMENT字段或者程序自己生成的顺序字段要比无序的UUID好的多)
二级索引会保存主键的值,所以主键的值不要太大。

非聚集索引的索引与数据是存在在不同文件的,对于MyISAM引擎的一张表,会有三种文件,FRM(表结构)、MYD(数据,就是数据库中的每个行)、MYI(索引)。
MySQl使用索引查询数据时,先到MYI文件中找出数据存储的位置指针,然后再到MYD文件中读取数据。

 

 

MyISAM中主键索引和其他索引在结构上没有什么不同,主键索引就是一个名为PRIMARY的唯一非空索引。

索引操作

创建在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

  • CREATE TABLE 

  • ALTER TABLE

    ALTER TABLE table_name ADD INDEX|KEY index_name (column1,[column2]...)
    ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] (column1,[column2]...)
    ALTER TABLE table_name ADD PRIMARY KEY|INDEX (column1,[column2]...)

     

  • CREATE INDEX

    CREATE KEY|INDEX index_name ON table_name (column_list)
    CREATE UNIQUE KEY|INDEX index_name ON table_name (column_list)

      

查看 

SHOW INDEX FROM tableName

 

高效索引策略

参考文献

MySQL索引背后的数据结构及算法原理 MYSQL-索引 Mysql索引分析

http://www.tuicool.com/articles/zmiM3mB

 

MyISAM存储引擎

  • MyISAM 的索引不论是 Primary Key 还是普通 Index,存储结构都基本一样,基本结构都是B-Tree;
  • MyISAM一个表生成3个文件,分别是(如表:test):test.frm(表结构)、test.MYD(数据,就是数据库中的每个行)、test.MYI(索引);
  • mysql服务器会先到test.MYI文件中找出数据存储的位置指针 (主键和普通索引结构一致,也就是说在索引文件的所有结构中的所有结点都不存真实数据,分支结点存索引主键普通索引,叶子结点存 索引值+数据物理地址指针),根据索引文件中叶子存放的指针地址再到test.MYD中取出数据。
  • MyISAM主键索引原理:


    MyISAM-1
    • 这里假设表一共有三列,假设我们以Col1为主键,则图8是一个MyISAM表的主索引(Primarykey)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。
      在MyISAM中,主索引和辅助索引(Secondarykey)在结构上 没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
  • MyISAM普通索引原理: 如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

  •  

  • MyISAM-2
    • 同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
  • MyISAM的索引方式也叫做 非聚集 的,之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB存储引擎

    • 虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
    • InnoDB的数据文件本身就是索引文件
    • 表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
    • InnoDB主健索引原理:
 

InnoDB-1
    • 以上是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。
      这种索引叫做 聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),
      如果没有显式指定,则MySQL系统会自动选择一个可以 唯一 标识数据记录的列作为主键
      如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

  • InnoDB普通索引原理:引用主键作为data域。例如下图定义在Col3上的一个辅助索引

 

 

  • InnoDB-2
    • 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引(普通索引)搜索需要 检索两遍索引首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录


什么是聚集索引和非聚集索引?

数据记录本身被存于索引(一颗B+Tree)的叶子节点上,这种索引叫聚集索引,InnoDB使用聚集索引。
反之则为非聚集索引,MyISAM使用非聚集索引,它的叶子节点的data域指向数据记录的地址,而不是存储数据记录本身;

聚集索引还需注意:

  1. 如果声明了主键(primary key),则这个列会被做为聚集索引。
  2. 如果没有声明主键,则会用一个唯一且不为空的索引列做为主键,成为此表的聚集索引。
  3. 上面二个条件都不满足,InnoDB会自己产生一个虚拟的字段作为聚集索引,这个字段长度为6个字节,类型为长整形。

MySql有几种索引?

  • 主键索引;
  • 唯一索引;
  • 普通索引;
  • 联合索引;
  • 全文索引。

参考资料

MySQL索引背后的数据结构及算法原理

http://www.jianshu.com/p/31eb5e4483c8

 

InnoDB存储

表空间是逻辑存放所有数据的地方,默认情况下会共享一个表空间——ibdata1,但如果把innodb_file_per_table=ON后每张表可以单独放到一个表空间内,但还是有很多数据保存在共享的表ibdata1中,如undo信息等。

表空间由各种段(segment)组成,常见的段有数据段、索引段等。Innodb是索引组织的,数据段就是clustered index的叶结点。需要注意的是,不是每个对象都有段。

区(extend)是由64个连续的页组成,每个页(page)固定为16KB,所以每个区总共为1M。页是InnoDB最小的磁盘管理单位。

InnoDB是按行进行存放的,每个区最少可以保存2条记录,否则就成链式结构了。每行数据除了自定义列以外,还会增加事务id和回滚指针列。如果没有定义primary key也没有not null的unique,则会增加6字节的RowId列作为主键。


        


            图片来自:http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html

 

InnoDB表的限制

一个表不能包含超过1000列。

内部最大键长度是3500字节,但MySQL自己限制这个到1024字节。

除了VARCHAR, BLOB和TEXT列,最大行长度稍微小于数据库页的一半。即,最大行长度大约8000字节。LONGBLOB和LONGTEXT列必须小于4GB, 总的行长度,页包括BLOB和TEXT列,必须小于4GB。InnoDB在行中存储VARCHAR,BLOB或TEXT列的前768字节,余下的存储的分散的页中。

虽然InnoDB内部地支持行尺寸大于65535,你不能定义一个包含VARCHAR列的,合并尺寸大于65535的行。

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 在一些更老的操作系统上,数据文件必须小于2GB。

 InnoDB日志文件的合并尺寸必须小于4GB。

最小的表空间尺寸是10MB。最大的表空间尺寸是4,000,000,000个数据库页(64TB)。这也是一个表的最大尺寸。

 InnoDB表不支持FULLTEXT索引

 InnoDB索引

默认情况下Memory使用存储hash索引,但也支持b+tree索引。
Hash索引只用于=或者<=>的等式比较,不能用来加速order by操作,只能通过关键字来搜索一行。innodb只支持b+树索引,进一步分为clustered index与secondary index。
在一次查询中,只能使用一个索引。

InnoDB是索引组织表,clustered index的叶结点保存着整行的数据。
如果,定义了primary key,则clustered index就是primary key的索引;
如果没有定义primary key mysql会选中第一个仅有not null列的unique索引作为主键,并把此索引当作clustered index使用;
如果没找到这样的列,innodb会创建一个6字节的RowId作为主键。所以每张表有且只有一个clustered index。

Secondary index的叶结点不包括行的全部数据,包含键值以外还包括一个bookmark,可以告诉innodb到什么地方可以找到相对应的完整行数据,还保存了主键的健值。
Secondary index包含主键,但不包含完整的行数据,所以innodb总是会先从secondary index的叶节点判断是否能得到所需的数据。
如:

Create table t(a int, b varchar(20), primary key(a), key(b));

Explain select * from t;

会发现mysql选择了索引b,而不是a.

复合索引

复合索引是在多列(>=2)上建立的索引,又叫多列索引或联合索引。Innodb中的复合索引也是b+ tree结构。索引的数据包含多列(col1, col2, col3…),在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…

使用复合索引要充分利用最左前缀原则,顾名思义,就是最左优先。
如创建索引ind_col1_col2(col1, col2),那么在查询where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引。

在创建多列索引时,要根据业务需求,where子句中使用最频繁且过滤效果好的的一列放在最左边。

索引操作

可以通过DML语句操作innodb索引。
因为innodb是索引组织的表,对索引的操作会造成锁表,先生成一张临时表,将数据从原始表中写到临时表,再将原始表删除,最后将临时表表名改为原始表表名!
因增加、删除、修改字段会对主索引产生影响,所以也会锁表。
对secondary index从Innodb plugin开始,支持快速索引创建的方法,在创建的过程中不需要重建表,所以速度会很快,同时引擎会在表上加S锁,在创建过程中只能进行读操作。

索引设计原则

1.搜索的索引列,不一定是所要选择的列。也就是说,最适合索引的列是出现在where子句中的列,或者连接子句中指定的列,而不是出现在select关键字后的选择列表中的列。
2.使用唯一索引。考虑某列的分布,索引的列的基数越大,索引的效果越好。例如,对性别M/F列做索引没多大用处。
3.使用短索引。如果是对字符串进行索引,如果有可能应该指定前缀长度。
4.利用最左前缀。尽量将使用频繁且过滤效果好的字段放“左边”
5.不要过度索引。
6.Innodb默认会按照一定的顺序保存数据,如果明确定义了主键,则按照主键顺序保存。如果没有主键,但有唯一索引,就按照唯一索引的顺序保存。
如果有几个列都是唯一的,都可以作为主键的时候,为了提高查询效率,应选择最常用访问的列作为主键。
另外,innodb的secondary index都会保存主键的键值,所有主键要尽可能选择较短的数据类型。可以看出,应当尽量避免对主键的修改。经过dba的测试,保证主键的递增可以提高插入性能。

Mysql如何使用索引

1.对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用。

2.对于使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能被使用。

3.如果对大文本进行搜索,应该使用全文索引,而不是使用like ‘%...%’. 但不幸的是innodb不支持全文索引。

4.如果列名是索引,使用 index_column is null将使用索引。Oracle是不行的。

5.如果mysql估计使用索引比全表扫描更慢,最不会使用索引。

6.如果使用memory/head表并且where条件中不使用”=”进行索引列,那么不会用到索引。Head表只有在”=”的时候才会使用索引。

7.用or分割开的条件,如果or前的条件中的列有索引,而后面列中没有索引,那么涉及到的索引都不会被用到。

8.不是多列索引的第一部分不会走索引。

9.以%开始的like不会走索引

10.如果列是字符串,那么一定要在where条件中把字符串常量值用引号引起来,否则不能走索引。因为,mysql默认把输入的常量值进行转换以后才进行检索。

11.经过普通运算或函数运算后的索引字段不能使用索引

12.不等于操作不能使用索,<>、not in等

13.Order by 优化:某些情况下,mysql可以使用一个索引满足order by,而不需要额外的排序。
Where条件与order by 使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者都是降序。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下情况不使用索引:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC--order by 的字段混合 ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ;

-- 用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2 ;

-- 对不同的关键字使用 ORDER BY    

可以使用explain查看sql的执行计划。

http://www.blogjava.net/happyenjoylife/archive/2011/12/17/366639.html

 

网友评论

登录后评论
0/500
评论
developerguy
+ 关注