一、什么是索引?
在官方文档中,对于索引的定义是:索引(index)是帮助Mysql高效获取数据的一种数据结构。这些数据结构以某种方式指向数据,就可以在数据结构上实现高级查询算法,这种数据结构就叫做索引。
所以、索引是一种数据结构,是官方指定的为MySql高效获取数据用的。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。
二、为什么要使用索引?
索引的优点:
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
-
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
- 被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
- 如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。
索引的缺点:
- 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
- 空间方面:索引需要占物理空间(磁盘空间)。
三、索引类型
对于索引,不同的分类方式可以分为很多种索引
1、按索引是否包含记录数据
聚簇索引: 将数据存储与索引放到了一块,找到索引也就找到了数据,不需要根据主键或行号去进行回表查询。
非聚簇索引: 非聚簇索引就是指B+Tree的叶子节点上的data并不是数据本身,而是数据存放的地址,找到地址之后需要进行一次回表操作,再次找到我们要找的数据。
最经典的InnoDB引擎数据库索引用的就算聚簇索引;而MyISAM引擎数据库则用的是非聚簇索引,叶节点的data域存放的是数据记录的地址。 。
澄清一个概念:innodb中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值。
使用场景
聚簇索引具有唯一性
由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引
聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。
此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段,那样的话Clustered index对效率的帮助,完全被浪费了。
刚才说到了,聚簇索引性能最好而且具有唯一性,所以非常珍贵,必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引,这个要看实际情况。
记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IO。
检索方式
1、聚簇索引检索方式
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。
若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
2、非聚簇索引检索方式
MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
聚簇索引的优缺点
优点:
- 由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
- 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。
- 聚簇索引适合用在排序的场合,非聚簇索引不适合
- 取出一定范围数据的时候,使用用聚簇索引
- 二级索引需要两次索引查找,而不是一次才能取到数据,因为存储引擎第一次需要通过二级索引找到索引的叶子节点,从而找到数据的主键,然后在聚簇索引中用主键再次查找索引,再找到数据
- 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户 ID 来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘 I/O。
缺点:
- 维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后,选在负载较低的时间段,通过OPTIMIZE TABLE优化表,因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
- 表因为使用UUId(随机ID)作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢,所以建议使用int的auto_increment作为主键。
- 主键的值是顺序的,所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB 默认的最大填充因子是页大小的 15/16,留出部分空间用于以后修改),下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满(二级索引页可能是不一样的)
- 如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值;过长的主键值,会导致非叶子节点占用占用更多的物理空间
2、按索引是否建立在主键上
主键索引: 在MySQL的主键上创建的索引就是主键索引,主键索引会自动创建,一个表只能有一个主键索引,同时主键索引也是唯一索引。
辅助索引: 在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,innodb中辅助索引叶子节点存储的不再是行的物理位置,而是键值和主键 ID。
InnoDB引擎支持聚簇索引,MyISAM引擎不支持聚簇索引。 所以,主键索引不一定是聚簇索引。
3、按索引的常规功能分类
唯一索引 (UNIQUE Indexs) 要求索引列的所有值都只能出现一次,即必须唯一。
普通索引 (Normal index) 又叫二级索引、辅助索引,仅用来提高查询速度,没有其他特性。
全文索引 (FULLTEXT Indexes) MySQL可以通过建立全文索引,利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。比如实现全匹配模糊查询。 但是实际场景测试mysql的全文索引性能非常不稳定,不建议生产环境使用。需要使用全文检索的地方,还是推荐使用ElasticSearch(ES)
空间索引 (Spatial indexes) 空间索引使用R树,R树是用于索引多维数据的专用数据结构。
覆盖索引 如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。 这个概念非常重要,灵活运用对SQL优化非常有帮助。
四、索引的创建
1、建表时创建
CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);
2、建表后创建
ALTER TABLE 表名
ADD [UNIQUE | FULLTEXT | SPATIAL]
INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC])
[USING 索引方法];
CREATE [UNIQUE | FULLTEXT | SPATIAL]
INDEX 索引名
ON 表名(字段名)
[USING 索引方法];
说明:
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
啥都不选:表示普通索引。
INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
长度:可选。指索引的长度,必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引,例如:
-
在经常需要搜索的列上,可以加快搜索的速度;
-
在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
-
在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;
-
在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
-
在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
-
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:
-
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
-
对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
-
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
-
当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
四、索引的匹配方式
1、全值匹配
指和索引的所有列进行匹配,有多少索引匹配多少列。
2、最左前缀匹配
只匹配前面几列,匹配不到,在进行向右匹配
例:联合索引的匹配规则
在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例:
CREATE TABLE `student` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增Id',
`Gid` int(11) unsigned DEFAULT NULL COMMENT '年级id',
`Cid` int(11) unsigned DEFAULT NULL COMMENT '班级id',
`SId` int(11) unsigned DEFAULT NULL COMMENT '学号',
`Name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
对列Gid、列Cid和列Sid建一个联合索引:
create unique index uni_Gid_Cid_SId on student(Gid,Cid,SId)
联合索引 uni_Gid_Cid_SId
实际建立了(Gid)、(Gid,Cid)、(Gid,Cid,SId)
三个索引。
插入模拟数据
INSERT INTO `student` (`Gid`, `Cid`, `SId`, `Name`)
VALUES (
floor(rand() * rand() *rand() * 1000000000) ,
floor(rand() * rand() *rand() * 1000000000) ,
floor(rand() * rand() * rand() *1000000000) ,
rand()
);
查询实例:
SELECT * FROM student WHERE Gid=68778 AND Cid=465176354 AND Name='0.56437948'
上面这个查询语句执行时会依照最左前缀匹配原则,检索时会使用索引(Gid
,Cid
)进行数据匹配,顺序是可以任意的:
SELECT * FROM student WHERE Gid=68778 AND Cid=465176354 ;
SELECT * FROM student WHERE Cid=465176354 AND Gid=68778;
这两个查询语句都会用到索引(Gid,Cid),mysql创建联合索引的规则是首先会对联合合索引的最左边的,也就是第一个字段Gid的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个字段Cid进行排序。其实就相当于实现了类似 order by Gid Cid这样一种排序规则。
联合索引的优点:
1、减少开销:建一个联合索引(Gid,Cid,SId),实际相当于建了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
2、覆盖索引:对联合索引(Gid,Cid,SId),如果有如下的sql: select Gid,Cid,SId from student where Gid=1 and Cid=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要提升性能的优化手段之一。
3、效率高:索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where Gid=1 and Cid=2 and SId=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合Gid=2 and Cid= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知!
联合索引的缺点:
联合索引越多,索引列越多,则创建的索引越多,索引都是存储在磁盘里的,通过索引算法(Btree代表索引算法使用二叉树的形式来做索引的)来查找数据,的确可以极大的提高查询效率,但是与此同时增删改的同时,需要更新索引,同样是需要花时间的,并且索引所占的磁盘空间也不小。
建议:单表尽可能不要超过一个联合索引,单个联合索引不超过3个字段。
3、列前缀匹配
只匹配一部分,比如:Like查询。
4、范围值匹配
最好理解了,> < 就可以用这个匹配原则
5、只访问索引匹配
查询的时候只访问了索引,没有访问数据行,本质上就是索引覆盖
6、索引失效
索引失效的几种情况:
-
如果条件中有or,即使其中有条件带索引也不会使用(这就是为什么尽量少使用or的原因)(注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引)
-
对于多列索引,不是使用的第一部分(不符合最左前缀原则),则不会使用索引,例子如下: 如果select * from key1=1 and key2= 2;则建立组合索引(key1,key2); select * from key1 = 1;组合索引有效; select * from key1 = 1 and key2= 2;组合索引有效; select * from key2 = 2;组合索引失效;不符合最左前缀原则
-
like查询是以%开头
-
如果列类型是字符串,那一定要在条件中使用引号引起来,否则不会使用索引
-
如果mysql估计使用全表扫描比使用索引快,则不使用索引
五、索引的数据结构
说起索引,我们最先知道的,就是它那个叫做B+树的数据结构没那么为什么索引要用B+树作为数据结构呢?
B+树其实就是一种二叉树,但是他和普通的二叉树有什么区别呢。
1、二叉树
说起二叉树,大家心中估计就会浮现出一幅图来,我就不放了。
二叉树特点:每个节点最多有2个分叉,左子树和右子树数据顺序左小右大。
这个特点就是为了保证每次查找都可以这折半而减少IO次数,但是二叉树就很考验第一个根节点的取值,因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”,这就很难受很不稳定,容易出现下图这种情况:
显然,这是大大不行的,于是我们引进了一个名叫平衡二叉树的东西。
2、平衡二叉树
平衡二叉树采用二分法思维,除了具备二叉树的特点,最主要的特征是树的左右两个子树的层级最多相差1。
在插入删除数据时通过左旋/右旋操作保持二叉树的平衡,不会出现左子树很高、右子树很矮的情况。
使用平衡二叉查找树查询的性能接近于二分查找法,时间复杂度是 O(log2n)。
就这个特点来看,可能各位会觉得这就很好,可以达到二叉树的理想的情况了。然而依然存在一些问题:
- 时间复杂度和树高相关。树有多高就需要检索多少次,Mysql的表是存储在磁盘上的,也就是说,每个节点的读取,都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻找时间为10ms,在表数据量大时,查询性能就会很差。(1百万的数据量,log2n约等于20次磁盘IO,时间20*10=0.2s)
- 平衡二叉树不支持范围查询快速查找,范围查询时需要从根节点多次遍历,查询效率不高。
所以我们还可以优化,继续改进。
3、B树:改造的二叉树
从上述问题中,我们可以得知:对于索引我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO,如果想要减少磁盘IO操作,就需要尽量降低树的高度。那如何降低树的高度呢?
假如我们有一个主键索引,key为bigint=8字节,每个节点有两个指针,每个指针为4个字节,一个节点占用的空间16个字节(8+4*2=16)。
因为在MySQL的InnoDB存储引擎一次IO会读取页以(默认一页4K,假设读取4页)为单位的数据量,而二叉树一次IO有效数据量只有16字节,空间利用率极低。为了最大化利用一次IO空间,一个简单的想法是在每个节点存储多个元素,在每个节点尽可能多的存储数据。每个节点可以存储1000个索引(16k/16=1000),这样就将二叉树改造成了多叉树,通过增加树的叉树,将树从高瘦变为矮胖。构建1百万条数据,树的高度只需要2层就可以(1000*1000=1百万),也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了,查询数据的效率也就提高了。
这种数据结构我们称为B树,B树是一种多叉平衡查找树,如下图主要特点:
- B树的节点中存储着多个元素,每个内节点有多个分叉。
- 节点中的元素包含键值和数据,节点中的键值从大到小排列。也就是说,在所有的节点都储存数据。
- 父节点当中的元素不会出现在子节点中。
- 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接。
其存储原理如图所示:
假如我们查询一个数据为10,其过程为:
是不是很理想?但是其实还可以优化它:
B树不支持范围查询的快速查找,你想想这么一个情况如果我们想要查找10和35之间的数据,查找到15之后,需要回到根节点重新遍历查找,需要从根节点进行多次遍历,查询效率有待提高。
如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大。
所以,最终版的B+树,就来了。
4、B+树:改造B树
B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。
- B树:非叶子节点和叶子节点都会存储数据。
- B+树:只有叶子节点才会存储数据,非叶子节点至存储键值。叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表。
B+树的最底层叶子节点包含了所有的索引项。从图上可以看到,B+树在查找数据的时候,由于数据都存放在最底层的叶子节点上,所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系,但是从另一方面来说,由于数据都被放到了叶子节点,所以放索引的磁盘块锁存放的索引数量是会跟这增加的,所以相对于B树来说,B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况,在索引中数据满足了当前查询语句所需要的全部数据,此时只需要找到索引即可立刻返回,不需要检索到最底层的叶子节点。
原文链接:
用这种结构,在查询某两个数之间的数据,其过程为:
这里需要区分的是在InnoDB中Data存储的为行数据,而MyIsam中存储的是磁盘地址。
可以看到B+树可以保证等值和范围查询的快速查找,MySQL的索引就采用了B+树的数据结构。
本博客转载自博主敖丙,原文链接为:一文搞懂MySQL索引所有知识点(建议收藏)