什么是索引?
索引是一种提高表中操作速度的数据结构,类似于我们小时候常用的字典;
索引也是一种表,它将主键或索引字段的值以及所在行的数据块地址,以指针(16进制)的形式保存到实际表中;
使用索引的好处
能加快数据的查询,减少磁盘I/O,降低CPU负载;
mariadb中的索引类型:
primary key
主键索引:使用建表语句时指定了字段为PRIMARY KEY
,同时会自动创建一个同名主键索引。主键索引必须唯一且不能为空,每张表可以没有主键,但是有主键的话,只能有一个主键;
比如下面的建表语句指定了id
为自增主键
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '用户名',
PRIMARY KEY (`id`) USING BTREE)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
unique index
唯一索引:使用建表语句时指定了字段为UNIQUE KEY
,则指定该列为唯一索引,该列的值必须唯一,并且可以为空
比如下面的建表语句指定了name
为唯一索引
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '用户名',
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
plain indexes
普通索引:使用建表语句时指定了字段为INDEX
,则指定该列为普通索引,普通索引不需要唯一,并且可以为空
比如下面的建表语句指定了id
为普通索引
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '用户名',
INDEX (`id`) USING BTREE)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
fulltext indexes
全文索引:使用建表语句时指定了字段为fulltext
,则指定该列为全文索引,用于全文搜索,full-text indexes
只可以用于字段类型为VARCHAR
、CHAR
和 TEXT
格式,并且无法分区表上创建全文索引。执行全文索引,需要有单独的语法支持,格式如下:
MATCH (col_name1,col_name2,...) AGAINST (expression (search_modifier))
其中MATCH()用于选择对应列,AGAINST()输入需要搜索的值,并且值的格式只能是字符串格式
创建一个表格
CREATE TABLE `err_log` (
`log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`add_time` datetime DEFAULT NULL COMMENT '添加时间',
`log_info` text(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`log_id`) ,
fulltext `log_info` (`log_info`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='错误日志表';
单列索引、多列索引(复合索引)、聚簇索引
单列索引
在一个字段上创建的索引,叫做单列索引;创建语法如下
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(column_1 [(length)] [DESC | ASC])
示例:创建一个唯一索引
CREATE UNIQUE INDEX `name` ON users (`name`);
多列索引(复合索引)
在多个字段上创建的索引称为多列索引,又称为复合索引,创建语法如下
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(column_1 [(length)] [DESC | ASC]
column_2 [(length)] [DESC | ASC]
........)
创建一个3列的复合索引
CREATE INDEX id ON users(id,`name`,birthday)
在现有表上增加索引,请注意:在线更改表结构,会造成锁表,导致无法写入数据,请在业务不繁忙的时候进行操作;
ALTER TABLE tbl_name ADD PRIMARY KEY index_name(column_list)
聚簇索引
在innodb里必须有聚簇索引
聚簇索引怎么创建?
无法创建,由innodb引擎指定
- 在创建表时 建表语句中定义了PRIMARY KEY ,mariadb会默认创建一个和PRIMARY KEY同名的聚簇索引;
- 如果没有PRIMARY KEY,mariadb会继续搜索,搜索是否有unique key,如果有并且不为空,则将其用作聚簇索引;
- 假如表里既没有PRIMARY KEY也没有UNIQUE KEY,那么mariadb会在内部创建包含行id值的隐藏聚集索引GEN_CLUST_INDEX。
- 所以InnoDB表中只能有一个聚集索引
另外非聚簇索引以外的索引叫做二级索引或者非聚簇索引(又或者叫做辅助索引。。。), 在InnoDB 表中非聚集索引的每条记录都有行和列的主键列。 mariadb使用此主键值在聚集索引或二级索引中搜索行。
如何查询索引
SHOW INDEX FROM table_name;
Btree、INDEX
innodb架构图解
在mariadb中有不同的引擎,最常用的存储引擎为innodb,其使用的索引为B树索引,其他存储引擎使用的索引树分别如下:
存储引擎 | 支持的索引树 |
---|---|
Aria | BTREE, RTREE |
MyISAM | BTREE, RTREE |
InnoDB | BTREE |
MEMORY/HEAP | HASH, BTREE |
hash索引用在内存表,R树索引主要用来做空间计算
首先看张图,来看一下innodb在内部是如何工作的
查询
- 假如有query过来,INNODB接收到请求后,会从内存中innodb_buffer_pool里缓存的数据的页(包含表数据和index)检索是否有要查找的数据,如果有则直接返还给客户端;(逻辑读)
- 如果检索不到,INNODB就会从硬盘读取数据,查询的语句中使用了INDEX,那么数据会很快被查到,并且把查询到的数据缓存到innodb_buffer_pool里(方便下次使用)并返还给客户端;(物理读)
- 如果查询的语句中没有使用INDEX,那么INNODB便会从磁盘数据文件中开始查询数据(扫描全表),如果你的表有100W行数据,并且要查询的数据在90W行,那么磁盘I/O使用会非常大。考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,mysql默认是16k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。
下面这张图显示了mariadb数据存储结构
引用一张经典的mysql数据结构图,我们可以看到数据库中从具体的字段(col)到数据行(row)、页(page)、段(extent)、区(segement)、表空间(tablespece)之间的关系
B树
既然设计了这么优秀的存储结构,那么就应该有优秀的方法去检索这些数据,于是便出现了自平衡搜索树方法对数据进行检索。常用的搜索树为:二叉搜索树、AVL 树、红黑树等搜索树,以上的搜索树每个节点仅包含一个值(键)和最多两个子节点,但是有一种特殊类型的搜索树,其中一个节点包含多个值(键)和两个以上的子节点,这种搜索算法 由 Bayer 和 McCreight 于 1972 年开发,名称为 Height Balanced m-way Search Tree(高平衡m向搜索树),后来它被命名为B-Tree。
B-Tree 被称为自平衡树,因为它的节点在遍历中排序。在 B-tree 中,一个节点可以有两个以上的子节点。 B-tree 的高度为 logM N(其中“M”是树的顺序,N 是节点数)。每次更新都会自动调整高度。在 B-tree 中,数据按特定顺序排序,最小值在左侧,最大值在右侧。在 B-tree 中插入数据或键比二叉树更复杂。
B树图
第一层Root
为根节点,第二层Branch
为分支节点(非叶点),第三层Leaf
为叶子节点
关于分支和叶子点无需多说,我们说一下root/根页面:
由于根页是在第一次创建索引时分配的,并且该页码存储在数据字典中,因此根页永远不能重定位或删除。一旦根页面填满,就需要拆分,形成一个根页面加上两个叶子页面的小树。根中的记录被移到新的页面里(根被“提升”到一个级别),并且新页被一分为二;直到新的根再次被下面的页面指针填满,然后继续重复这一过程
B树的缺点是:分支节点存储包含键值和该键值对应的数据指针(指向包含键值的磁盘文件块的指针),这样存储就大大的减少了可以存储在该节点的数据,等于变相的增加了B树的层级,从而增加了查询时间;并且在对进行表更新(INSERT/UPDATE/DELETE)时造成的迭代,会让B树逐渐失去平衡,其性能会下降。
B树总结:
- B树的所有叶子节点必须在同一层级
- B树的叶子节点不仅存键储数据也存储数据链表
- B树的叶子节点之上,不应该有空的数据
- B树的高度应该尽可能低
- 一个节点中的所有键都是按升序排列的
B+树
B+树属于B树的扩展,B+树在分支节点仅包含索引值,而不包含数据。只有叶子点存储密钥和数据,因此使得数据指针的遍历更容易和更有效。并且叶子之间点通过链接列表连接。 ** **
叶子点不仅存放数据,而且还存放相邻的链表连接,这样查询效率更高
B+树总结:
- B+树属于B树的扩展;
- B+树中索引和记录存储在叶子点中。分支节点仅包含指向其他叶节点和分支节点的指针;
- 叶子点包含指向前一个和下一个叶节点的指针(列表链接)用于索引范围遍历。
B树和B+树的主要区别:
- B+树只有叶子节点才会存数据,而B树中间节点也会存数据,树的查询复杂度是由树的层级决定的,从上面磁盘读写原理的介绍我们知道一个page的大小是固定的,而一个固定大小的page能够存放的数据是有限的,B+树中间节点不存数据它就可以存放更多的索引信息,这个对于查询效率的提升是非常巨大的。
2.B+树每个叶子节点之间链接关系,而B树没有,当我们需要进行全表扫描的时候,B+树能提供更好的查询效率。
INNODB中的B+树
INNODB中 index使用的树为B+树,作为B树的延申,层级同样也分和B树一样分为:root
、branch
和leaf
。下面我们来看一下INNODB中的B+树结构:
叶子点:
叶子点,级别为LEVEL0
对于叶子点,每条记录(包括 infimum下确界和supremum上确界系统记录)都包含一个“下一条记录”指针,该指针存储到下一条记录的偏移量(在页面内)
链表从 infimum 开始,按 key 升序链接所有记录,到 supremum 终止。
记录在页面内没有物理排序(它们占用插入时可用的任何空间);他们唯一的顺序来自他们在链表中的位置。除了链表数据,叶子点还包含除了非键值以外的行数据;
非叶子点:
非叶页子点具有相同的结构,但它们保存的data不是非键字段,而是子页面的页码,指向的其他子页面上所需要的的最小页数(类似于导航)
同一级别的叶子点
大多数索引包含多个叶子点页面,因此多个页面以升序和降序链接在一起:
每一页都包含用于“上一页”和“下一页”的指针,其对于索引页被用来形成相同级别的页面的双向链接列表(INNODB中为双向列表)。
单个叶子点
完整架构(简化)
- 在叶子节点上,级别从0开始编号,向上递增
- 同一级别的节点的页面使用双链接记录表,按照key的升序进行双向链接(左右)
- 层与层之间的页面使用单链接记录表,按照key的升序进行单向链接(向下)
- Infimum低于页面上任何键的值,并且始终是单链接记录列表中的第一条记录。
- SuPreum表示高于页面上任何键的值,并且始终是单链接记录列表中的最后一条记录
- 非叶子点包含子页的最小键和,为指向的其他子页面上所需要的的最小页数
从此可见INNODB的B+树要比普通的B+树繁琐的多,也要效率的多,普通B+树同层之间、上下层之间都是通过单向链接表进行连接,而INNODB里B+树则是同层之间使用双向链表连接进行双向连接,这样查询和更新起来效率更加高;
B树也好,B+树也好,主要目的就是提高遍历节点的速度,从而减少查询/更新数据所需要的时间
如何设计高效的INDEX
- 经常与其他表进行连接的表,在连接字段上应该建立索引;
- 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
- 索引应该建在高区分度高的字段上;
- 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
- 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
- 正确选择复合索引中的主列字段,一般是选择区分度高的字段;
- 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
- 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
- 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
- 使用复合索引时,记住最左匹配原则,也就是匹配度高的字段放在最左;
- 频繁进行数据操作的表,不要建立太多的索引;
- 删除无用的索引,避免对执行计划造成负面影响;
- 索引不会包含有NULL值的列
- 可以使用索引的关联条件:
- =、 左like 、> 、 < 、 between、少量数据的in
- 不可以使用索引的关联条件:
- <>、!=、or 、大量数据的in 、左右like
- NOT IN不会使用索引,会进行全表扫描。NOT IN可以NOT EXISTS代替
使用优化工具SQLAdvisor
如果不知道怎么入手,或者吃不准的话可以使用美团的SQL优化工具:SQLAdvisor
SQLAdvisor 是由美团点评公司北京DBA团队开发维护的 SQL 优化工具:输入SQL,输出索引优化建议。 它基于 MySQL 原生词法解析,再结合 SQL 中的 where 条件以及字段选择度、聚合条件、多表 Join 关系等最终输出最优的索引优化建议。目前 SQLAdvisor 在公司内部大量使用,较为成熟、稳定。
https://tech.meituan.com/2017/03/09/sqladvisor-pr.html
视频学习
如果实在看不懂的,这里有2个关于INDEX的视频,比喻通俗易懂,全部是干货,虽然是关于Oracle的,但是原理基本都相同的
如何创建索引:
https://mp.weixin.qq.com/s?__biz=MzI3OTM3MDkyNg==&mid=2247493965&idx=1&sn=764f3313e217b3688e73fd559b2589ed&chksm=eb4a648cdc3ded9aa603d816d3aee27ae0178d256f66be578c848433fbbdb33d0629483daeab&scene=178&cur_album_id=1975597331630604290#rd
总结
总体来说,索引属于比较抽象的东西,通过此篇文章对索引应该有个比较详细的认识了。尾部的参考连接里有大量的文档,想加深学习的同学可以继续看一下。
参考链接
https://mariadb.com/kb/en/getting-started-with-indexes/
https://mariadb.com/kb/en/full-text-index-overview/
https://mariadbtips.com/mariadb-index/
https://zhuanlan.zhihu.com/p/66553466
https://www.cnblogs.com/abcdwxc/p/9855474.html
https://www.javatpoint.com/mysql-clustered-index#:~:text=The%20essential%20characteristics%20of%20a%20clustered%20index%20are,one%20or%20more%20column%20for%20creating%20an%20index.
http://www.btechsmartclass.com/data_structures/b-trees.html
https://www.studytonight.com/advanced-data-structures/b-trees-mway-trees-data-structure
https://www.studytonight.com/advanced-data-structures/b-plus-trees-data-structure
https://www.geeksforgeeks.org/difference-between-b-tree-and-b-tree/
http://wonderdb.org/?p=157