MYSQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构,所以说索引的本质是:数据结构;索引的目的在于提高查询效率,可以类比字典、 火车站的车次表、图书的目录等 。
一、索引的简介和作用
二、索引的优缺点
优点
- 可以快速检索,减少I/O次数,加快检索速度
- 根据索引分组和排序,可以加快分组和排序,降低CPU的消耗
缺点
-
创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加
- 索引也需要占空间,我们知道数据表中的数据也会有最大上线设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值
- 当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度
什么时候使用索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题,高并发下倾向创建组合索引
- 查询中排序的字段,排序字段通过索引访问大幅提高排序速度
- 查询中统计或分组字段
什么时候不需要使用索引
- 表记录太少
- 数据重复且分布均匀的表字段,只应该为最经常查询和最经常排序的数据列建立索引
- 经常插入、删除、修改的表要减少索引(更新索引消耗)
- where不用的字段
- 索引使用<>时,效果一般
索引失效
- 组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引
- like未使用最左前缀,where A like '%China'
- 搜索一个索引而在另一个索引上做order by,where A=a order by B,只使用A上的索引,因为查询只使用一个索引
- or会使索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效),这时应该改为两次查询,然后用UNION ALL连接起来。
- 如果列类型是字符串,要使用引号。例如where A='China',否则索引失效(会进行类型转换)
- 在索引列上的操作,函数(upper()等)、or、!=(<>)、not in等
索引优化
- 组合索引比单个索引效果更好
- 当索引范围超过30%时,索引作用微乎其微
- 索引最左有限原则
三、索引的分类
索引的基本语法为:CREATE [UNIQUE] INDEX indexName ON mytable(username(length));如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构(添加索引):ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName);
删除:DROP INDEX [indexName] ON mytable;
查看:SHOW INDEX FROM table_name\G --可以通过添加 \G 来格式化输出信息。
MySQL从数据结构角度对索引分类可以分为B+树索引、Hash索引、Full-Text全文索引、R-Tree索引;
从物理存储角度可分为:聚集索引(clustered index)、非聚集索引(non-clustered index),也叫辅助索引(secondary index)聚集索引和非聚集索引都是B+树结构;
从逻辑角度可以分为主键索引:主键索引是一种特殊的唯一索引,不允许有空值,
普通索引或者单列索引:每个索引只包含单个列,一个表可以有多个单列索引,
多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合,
唯一索引或者非唯一索引,
空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字 进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
主键索引
是一种特殊的唯一索引,不允许有空值
ALTER TABLE 'USER' ADD PRIMARY KEY id_index('id');
唯一索引
索引列中的值必须是唯一的,但是允许为空值
ALTER TABLE 'USER' ADD UQIQUE id_index('id');
普通索引
MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点
ALTER TABLE 'USER' ADD INDEX id_index ('id');
全文索引
全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,用大文本对象的列构建的索引
ALTER TABLE 'USER' ADD FULLTEXT INDEX id_index('id');
组合索引
在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合
ALTER TABLE 'USER' ADD INDEX cobo_index('id','name','age');
四、索引的实现原理
首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,B+Tree索引,哈希索引,全文索引等等。
MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。
哈希索引
全文索引
#创建全文索引
ALTER TABLE USER ADD FULLTEXT INDEX big_index(column_name)
#使用语法
SELECT * FROM table_name where MATCH(ft_index) AGAINST('查询字符串');
注意:在MySQL中,全文索引只对英文有用,目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文;在MySQL中,如果检索的字符串太短则无法检索得到预期的结果,检索的字符串长度至少为4字节,此外,如果检索的字符包括停止词,那么停止词会被忽略。
BTree索引
平衡搜索多叉树。为磁盘等外存储设备设计的一种平衡查找树。系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB,可通过参数innodb_page_size将页的大小设置为4K、8K、16K。B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。
B+Tree索引
B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果<u>data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率</u>。
在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而<u>非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度</u>。
目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的.
B+树和B树对比
B-Tree:B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K;而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
- 每个节点最多有m个孩子
- 除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子。
- 若根节点不是叶子节点,则至少有2个孩子
- 所有叶子节点都在同一层,且不包含其它关键字信息
- 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
- 关键字的个数n满足:ceil(m/2)-1 <= n <= m-1
- ki(i=1,…n)为关键字,且关键字升序排序
- Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki,但都大于k(i-1)
B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:
每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。
- 根据根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
- 比较关键字29在区间(17,35),找到磁盘块1的指针P2。
- 根据P2指针找到磁盘块3,读入内存。【磁盘I/O操作第2次】
- 比较关键字29在区间(26,30),找到磁盘块3的指针P2。
- 根据P2指针找到磁盘块8,读入内存。【磁盘I/O操作第3次】
- 在磁盘块8中的关键字列表中找到关键字29。
分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
B+Tree:
B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB 存储引擎就是用 B+Tree 实现其索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。
- 非叶子节点只存储键值信息;
- 所有叶子节点之间都有一个链指针;
- 数据记录都存放在叶子节点中
将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:
通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。
-
范围查找更快,mysql是关系型数据库,经常会按照区间来访问某个索引列,B+树的叶子节点间按顺序建立了链指针,加强了区间访问性,所以B+树对索引列上的区间范围查询很友好。而B树的数据有一部分存在在非叶子节点上面,而且默认的B树的相邻的叶子节点之间是没有指针的,所以范围查找相对更慢。
-
降低树的高度,但是最底下一层的节点会更多,因为所有的数据都堆积在最底下一层了,用空间换速度。B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data,所以一个节点可以存储更多的内节点,每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大,IO效率更高
为什么Mysql索引要用B+树不是B树?
MyISAM索引实现
MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。
如果用这个引擎创建数据库表,实际是生成三个文件:user.myi 索引文件 user.myd数据文件 user.frm数据结构类型。
在MyISAM中,索引(含叶子节点)存放在单独的.myi文件中,叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问,速度很快)。主索引是指主键索引,键值不可能重复;辅助索引则是普通索引,键值可能重复。通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。
这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。
InnoDB索引实现
如果是用这个文件创建数据库表,它实际是生成两个文件:user.ibd 索引文件 user.frm数据结构类型。InnoDB 的数据文件本身就是索引文件,而MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。
- 在辅助索引上检索name,到达其叶子节点获取对应的主键;
- 使用主键在主索引上再进行对应的检索操作
这也就是所谓的“回表查询”。
InnoDB 索引结构需要注意的点:
- 数据文件本身就是索引文件
- 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
- 聚集索引中叶节点包含了完整的数据记录
- InnoDB 表必须要有主键,并且推荐使用整型自增主键
正如我们上面介绍 InnoDB 存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL 会从表中选择数据不重复的列建立索引,如果没有符合的列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。
1、InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
2、第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
对比:
- InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引
- MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引