在关系数据库中,索引是人都一种单独的、物理的不知对数据库表中一列或多列的值进行排序的一种存储结构,它是聚集聚集简单某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的索引索引试题作用相当于图书的目录,可以根据目录中的和非页码快速找到所需的内容。
能实现快速定位数据的一种存储结构,其设计思想是以空间换时间。
按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。
按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类:单列索引、联合索引。
这个话题比较大,在MySQL中有不同的存储引擎比如像InnoDB MyISAM Memory 等等,每一种存储引擎在其内部实现索引机制的原理也有所不同。在MySQL5.5之后默认的就是InnoDB,并且是目前使用最广泛的MySQL数据引擎,那我们就以InnoDB为例展开讲讲。
❓如果说我们在表中有100条数据,而我们要找出我们需要的数据,有哪些办法?
首先InnoDB和MyISAM都是使用的B+树实现的,但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引,聚簇索引根据主键创建一颗B+树,叶子节点则存放的是数据行记录,也可以把叶子结点称为数据页。通俗点来说就是把数据和索引存在同一个块,找到了索引也就找到了数据。
️在这部分只介绍InnoDB和MyISAM主键索引的不同?辅助索引后面在说
而非聚簇索引是将索引和数据分开存储,那么在访问数据的时候就需要2次查找,但是和InnoDB的非聚簇部分还是有所区别。InnoDB是需要查找2次树,先查找辅助索引树,再查找聚簇索引树(这个过程也叫回表)。而MyISAM的主键索引叶子结点的存储的部分还是有所区别。InnoDB中存储的是索引和聚簇索引ID,但是MyISAM中存储的是索引和数据行的地址,只要定位就可以获取到。
其实看到这个部分会有一个疑惑,那就是InnoDB的聚簇索引比MyISAM的主键快,那为什么会认为MyISAM查询效率比InnoDB快呢?
答案是会的!!!
InnoDB是MySQL中的一种存储引擎,它会为每个表创建一个主键索引。如果表没有明确的主键索引,InnoDB会使用一个隐藏的、自动生成的主键来创建索引。这个隐藏的主键索引使用的就是B+树结构。因此,在InnoDB中,即使表没有明确的主键索引,也会创建一个B+树索引。
数据是存储在磁盘上的,操作系统读取磁盘的最小单位是块,如果没有索引,会加载所有的数据到内存,依次进行检索,加载的总数据会很多,磁盘IO多。
如果有了索引,会以学号为key创建索引,MySQL采用B+树结构存储,一方面加载的数据只有学号和主键ID,另一方便采用了多叉平衡树,定位到指定学号会很快,根据关联的ID可以快速定位到对应行的数据,所以检索的速度会很快,因为加载的总数据很少,磁盘IO少。
可见,索引可以大大减少检索数据的范围、减少磁盘IO,使查询速度很快,因为磁盘IO是很慢的,是由它的硬件结构决定的。
答案是不一定,任何事物我们都应该辩证的看,知道其运行逻辑从而利用其优点,尽量避开它的缺点。在上面我们已经和大家介绍了过了索引带来的优缺点,那接下来就和大家分享几个建索引的提示。
⬆️ 上代码 ⬆️
show index from table_name (表名)
这个问题要分版本回答!!!版本不同可能会导致索引失效的场景也不同,直接给答案的都是耍流氓!!!
这里回答基于最新MySQL8版本,MySQL8失效的以前版本也失效,MySQL8不失效的,以前可能会失效。
首先讲答案不一定。即使我们使用is null 或者is not null 它其实都是会走索引的。那为什么会有这样的言论呢?这里首先就得来讲讲NULL值是怎么在记录中存储的,又是怎么在B+树中存储的呢。
那么在InnoDB中分为聚簇索引和非聚簇索引两种,聚簇索引本身是不允许记录为空的,所以可以不不用考虑,那么就剩下非聚簇索引也就是我们的辅助索引。
那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引,那到底什么时候索引,什么时候采用全表扫描呢?
首先我们得知道两个东西,第一个在InnoDB引擎是如何存储NULL值的,第二个问题是索引是如何存储NULL值的,这样我们才能从根上理解NULL在什么场景走索引,在什么场景不走索引。
1⃣️ 在InnoDB引擎是如何存储NULL值的?
InnoDB引擎通过使用一个特殊的值来表示null,这个值通常被称为"null bitmap"。null bitmap是一个二进制位序列,用来标记表中每一个列是否为null。当null bitmap中对应的位为1时,表示对应的列为null;当null bitmap中对应的位为0时,表示对应的列不为null。在实际存储时,InnoDB引擎会将null bitmap作为行记录的一部分,存储在行记录的开头,这样可以在读取行记录时快速判断每个列是否为null。
从头开始说理解起来会比较容易,理解了独占表空间文件就更容易理解行格式了,接着往下看:
当我们创建表的时候默认会创建一个*.idb 文件,这个文件又称为独占表空间文件,它是由段、区、页、行组成。InnoDB存储引擎独占表空间大致如下图;
Segment(表空间) 是由各个段(segment)组成的,段是由多个区(extent)组成的。段一般分为数据段、索引段和回滚段等。
Extent(区) 在表中数据量大的时候,为某个索引分配空间的时候就不再按照页为单位分配了,而是按照区(extent)为单位分配。每个区的大小为 1MB,对于 16KB 的页来说,连续的 64 个页会被划为一个区,这样就使得链表中相邻的页的物理位置也相邻,就能使用顺序 I/O 了 。
(我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。B+ 树中每一层都是通过双向链表连接起来的,如果是以页为单位来分配存储空间,那么链表中相邻的两个页之间的物理位置并不是连续的,可能离得非常远,那么磁盘查询时就会有大量的随机I/O,随机 I/O 是非常慢的。解决这个问题也很简单,就是让链表中相邻的页的物理位置也相邻,这样就可以使用顺序 I/O 了,那么在范围查询(扫描叶子节点)的时候性能就会很高。)
Page(页) 记录是按照行来存储的,但是数据库的读取并不以「行」为单位,否则一次读取(也就是一次 I/O 操作)只能处理一行数据,效率会非常低。
因此,InnoDB 的数据是按「页」为单位来读写的,也就是说,当需要读一条记录的时候,并不是将这个行记录从磁盘读出来,而是以页为单位,将其整体读入内存。
默认每个页的大小为 16KB,也就是最多能保证 16KB 的连续存储空间。
页是 InnoDB 存储引擎磁盘管理的最小单元,意味着数据库每次读写都是以 16KB 为单位的,一次最少从磁盘中读取 16K 的内容到内存中,一次最少把内存中的 16K 内容刷新到磁盘中。
页的类型有很多,常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的,数据页的结构这里我就不讲细说了,总之知道表中的记录存储在「数据页」里面就行。
Row(行) 数据库表中的记录都是按行(row)进行存放的,每行记录根据不同的行格式,有不同的存储结构。
重点来了!!!
InnoDB 提供了 4 种行格式,分别是 Redundant、Compact、Dynamic和 Compressed 行格式。
那么我们来看看Compact里面长什么样,先混个脸熟。
这里简单介绍一下,Compact行格式其他内容后面单独出一个章节介绍。
2⃣️ 索引是如何存储NULL值的?
我们知道InnoDB引擎中按照物理存储的不同分为聚簇索引和非聚簇索引,聚簇索引也就是主键索引,那么是不允许为空的。那就不再我们本问题的讨论范围,我们重点来看看非聚簇索引,非聚簇索引是允许值为空的。
在InnoDB中非聚簇索引是通过B+树的方式进行存储的
从图中可以看出,对于s1表的二级索引idx_key1来说,值为NULL的二级索引记录都被放在了B+树的最左边,这是因为设计InnoDB的大叔有这样的规定:
We define the SQL null to be the smallest possible value of a field.
也就是说他们把SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后,也就是本例中id值为521的那条记录之后,就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了,直到某条记录的key1列不为NULL。
3⃣️ 我们了解了上面的两个问题之后,我们就可以来看看,使不使用索引的依据是什么了
实际上来说我们用is null is not null ≠ 这些条件都是能走索引的,那什么时候走索引什么时候走全表扫描呢?
总结起来就是两个字:成本!!!
如何去度量成本计算使用某个索引执行查询的成本就非常复杂了,展开讲这个话题就停不下来了,后面考虑单独列一个篇幅去讲。
这里总结性讲讲:第一个,读取二级索引记录的成本,第二,将二级索引记录执行回表操作,也就是到聚簇索引中找到完整的用户记录操作所付出的成本。
要扫描的二级索引记录条数越多,那么需要执行的回表操作的次数也就越多,达到了某个比例时,使用二级索引执行查询的成本也就超过了全表扫描的成本(举一个极端的例子,比方说要扫描的全部的二级索引记录,那就要对每条记录执行一遍回表操作,自然不如直接扫描聚簇索引来的快)
所以MySQL优化器在真正执行查询之前,对于每个可能使用到的索引来说,都会预先计算一下需要扫描的二级索引记录的数量,比方说对于下边这个查询:
SELECT * FROM s1 WHERE key1 IS NULL;
优化器会分析出此查询只需要查找key1值为NULL的记录,然后访问一下二级索引idx_key1,看一下值为NULL的记录有多少(如果符合条件的二级索引记录数量较少,那么统计结果是精确的,如果太多的话,会采用一定的手段计算一个模糊的值,当然算法也比较麻烦,我们就不展开说了),这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然,对于某些查询,比方说WHERE子句中有IN条件,并且IN条件中包含许多参数的话,比方说这样:
SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
这样的话需要统计的key1值所在的区间就太多了,这样就不能采用index dive的方式去真正的访问二级索引idx_key1,而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条(很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多)。
反正不论采用index dive还是依据统计数据估算,最终要得到一个需要扫描的二级索引记录条数,如果这个条数占整个记录条数的比例特别大,那么就趋向于使用全表扫描执行查询,否则趋向于使用这个索引执行查询。
理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引,本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。
大家可以看到,MySQL中决定使不使用某个索引执行查询的依据很简单:就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。大家以后也多多辟谣吧,没那么复杂,只是一个成本而已。
首先看看B+树是如何查找数据的:
查找数据时,MySQL会从根节点开始,按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值,则跳到该节点的左子节点继续查找;如果查询条件大于节点中的键值,则跳到该节点的右子节点继续查找;如果查询条件等于节点中的键值,则继续查找该节点的下一个节点。
比如说我有下面这条SQL:
select * from `user` where nickname like '%冥';
如果数据库中存在南冥 北冥 西冥 东冥 ,那么在B+树中搜索的效率和全表扫描还有什么区别呢?
我走聚簇索引全表扫描还不用回表。
最后在扩展讲一个点,其实不一定会导致索引失效。举个例子:
create table `user`( id int primary key auto_increment, name varchar(20), index idx_name(name),);// 那么这种情况是会走索引的。select id,name from `user` where name like '%冥';
为什么说上面的例子会走索引呢?
首先我们需要查询的id name 这两个字段是不是都在我们的辅助索引中,叶子节点是不是存的索引值和主键值,所以我们只要查辅助索引就可以直接拿到我们的需要的结果了,那么这个叫做索引覆盖。我们观察执行计划会发现它的查询级别是index ,其实也是全表遍历了辅助索引。
第二个问题来了,那为什么就要走辅助索引而不是走全表扫描呢?
因为辅助索引中记录的东西比主键索引少了很多,只有索引值和主键值,但是主键索引中就包含了,其他值、事物ID、MVCC的回流指针等等。再加上索引覆盖不用回表,优化器就认为直接遍历辅助索引的效率高于主键索引。
索引覆盖(Index Covering)是指通过在索引中包含所有查询语句中所需的列,可以避免对表中的数据进行额外的访问,从而提高查询效率。(避免了回表操作)
例如,对于一个查询语句:
SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y
如果在table表中建立了一个索引,包含col1、col2和col3三列,那么MySQL可以通过索引定位到符合条件的数据,并在索引中提取col1、col2和col3列的值,无需对表中的数据进行额外的访问。这种方式就叫做索引覆盖。
索引覆盖能够显著提高查询效率,因此在建立索引时应尽量考虑包含查询语句中所需的所有列。
聚簇索引是一种特殊的索引,它将数据存储在索引树的叶子节点上。这种索引方式的优点是,在查询数据时可以减少一次查询,因为查询索引树的同时就能获取到数据。聚簇索引的缺点是,因为数据存储在索引树中,所以对数据进行修改或删除操作时需要更新索引树,这会增加系统的开销。
在InnoDB中聚簇索引和非聚簇索引实际上是物理空间存储方式的一个不同。
结合“聚簇索引与非聚集索引的特点是什么?”加上下图就明白了
可以,这题容易混淆聚簇和非聚簇,聚簇只能有一个,但是非聚簇可以有很多,因为聚簇是和数据存放在一起的,但是非聚簇是单独的。(同时这题可以结合上面两个问题回答)
我们知道在MyISAM引擎中是没有聚簇索引,都是存的辅助索引。但是和InnoDB不同的是存储的,它是存储索引值和数据地址,而我们InnoDB中存储的是主键ID。
我们要记住知道一个点,数据是会不断变动的,那么它的一个地址也是会跟着不断变动,如果直接存储地址,下次找到的数据可能就不是原来的数据了。如果要解决这个问题的话,成本是非常高的。每次数据变动都需要进行调整。
哈希索引(hash index)基于哈希表实现。哈希索引通过Hash算法将数据库的索引列数据转换成定长的哈希码作为key,将这条数据的行的地址作为value一并存入Hash表的对应位置。
在MySQL中,只有Memeory引擎显式的支持哈希索引,这也是Memory引擎表的默认索引结构,Memeory同时也支持B-Tree索引。并且,Memory引擎支持非唯一哈希索引,如果多个列的哈希值相同(或者发生了Hash碰撞),索引会在对应Hash键下以链表形式存储多个记录地址。
哈希索引还有如下特点:
对于InnoDB的哈希索引,确切的应该这么说:
那什么是自适应哈希索引(Adaptive Hash Index, AHI)呢?
1、自适应即我们不需要自己处理,当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点,就会给其建立一个hash索引;
2、hash索引底层的数据结构是散列表(Hash表),其数据特点就是比较适合在内存中使用,自适应Hash索引存在于InnoDB架构中的缓存中(不存在于磁盘架构中).
索引下推(INDEX CONDITION PUSHDOWN,简称 ICP)是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎,来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB,本篇的内容只基于 INNODB。
在讲这个技术之前你得对mysql架构有一个简单的认识,见下图
索引下推相关语句
# 查看索引下推是否开启select @@optimizer_switch# 开启索引下推set optimizer_switch="index_condition_pushdown=on";# 关闭索引下推set optimizer_switch="index_condition_pushdown=off";
讲起来非常简单,其实和 "普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 可以是单列唯一索引,也可以是联合唯一索引。
我们前面讲了唯一索引最大的好处就是能保证唯一性。看似没什么太大的价值,可能就会有同学说,我业务层做一个重复检查不就好了。问题就在这个地方,“业务是无法确保唯一性的”,除非你说你的代码没有BUG。很多时候业务场景需要保证唯一性,如果不在数据库加限制的话,总有一天会出现脏数据。
那又有同学就说了,既然你不想重复你可以使用主键索引。这个回答也很有意思。
聚簇索引的原理就不在这里细讲了,会有一个单独的章节来介绍。
我们通过和普通索引来做一个对比,有查询和插入两个场景。
首先第一个数据查询,一般情况下来说索引是通过B+树从根节点开始层序遍历到叶子结点,数据页内部通过二分搜索。
InnoDB 它是以数据页为单位进行读写的,我们读一条记录,并不是从磁盘加载一条记录,而是以页为单位整体读到内存里面来的。
普通索引比唯一索引就多了一次查找和判断下一条记录的操作,也就是一次指针寻找数据和一次计算。当然还有一种特殊情况,读取到的这条数据正好是数据页的最后一条,但是这种概率也是非常低,几乎可以忽略不计。
整体看下来看上去性能差距并不大对吧。
来看第二个更新的性能,我们按照上面图上的例子在2和6之间插入一个3。
在内存中
不在内存中
数据读取到内存涉及了随机IO访问,这是在数据库里面成本最高的操作之一,而change buffer 就可以减少这种随机磁盘访问,所以性能提示比较明显。所以在这一块来说,如果两者在业务场景下都能满足时可以优先考虑使用普通索引。
我们在索引回顾的时候和大家对索引做了一个分类对吧,按照字段个数来分的话,就分为了单列索引和组合索引对吧。那么他们之间的特点是什么呢?我们来看
看上去感觉这组合索引并没有太大作用是吧,我一个列已经有一个索引了,我还要这组合索引干嘛?
真相往往不那么简单,首先我们得承认我们的业务千变万化,我们的查询语句条件肯定是非常多的。
但是我们按照我们的查询条件去创建一个联合索引的话,就避免了上面的问题。那么联合索引是怎么工作的呢?
这里涉及到了一个重点,叫做最左前缀,简单理解就是只会从最左边开始组合,组合索引的第一个字段必须出现在查询组句中,还不能跳跃,只有这样才能让索引生效,比如说我查询条件里面有组合索引里面的第二个字段,那么也是不会走组合索引的。举个例子
// 假设给username,age创建了组合索引// 这两种情况是会走索引的select username,age from user where username = '张三' and age = 18;select * from user where username = '张三';// 这种是不会走索引的select * from user where age = 18;select * from user where city = '北京' and age = 18;
// 特殊情况,这种也是会走索引的,虽然我的age在前面,username在后面。// 刚刚不是手最左前缀匹配吗,为什么放到第二位也可以呢?// 虽说顺序不一致,但是在SQL执行过程中,根据查询条件命中索引,// 无论我username在不在前面,都会按照username去进行索引查找。select * from user where age = 18 and username = '张三';
我们知道在很多场景下会导致索引失效,比如说没有遵循B+树的最左匹配原则,但是也有一些情况是遵循了最左匹配原则但是还是没有走索引,这里我们使用order by进行排序的时候就有不走索引的情况,那么带大家来分析一下
drop table if exists `user`;drop table if exists `user_example`;create table `user`( `id` int primary key comment '主键ID', `card_id` int comment '身份证', `nickname` varchar(10) comment '昵称', `age` int not null comment '年龄', key `card_id` (`card_id`)) engine=InnoDB default charset=utf8mb4;// 这里我们明明对card_id建好了单列索引,那为什么不走索引呢?select * from `user` order by card_id
explain select nickname,card_id,age from user order by card_id;
我们在了解mysql底层是怎么排序的之前,我们先来了解一下一个概念 sort buffer .
首先mysql会为每一个线程都分配一个固定大小的sort buffer 用于排序。它是一个具有逻辑概念的内存区域,我们可以通过sort_buffer_size参数来控制,默认值是256kb 。
// 输入查看最,小可以设置为 32K,最大可以设置为 4G。show variables like 'sort_buffer_size';
由于sort buffer 大小是一个固定的,但是我们待排序的数据量它不是,所以根据它们之间的一个差值呢,就分为了内部排序和外部排序
和大家说一个这个参数max_length_for_sort_data ,在我们MySQL中专门控制用户排序的行数据长度参数。默认是4096,也就是说如果超过了这个长度MySQL就会自动升级成row_id算法。
// 默认max_length_for_sort_data的大小为4096字节show variables like 'max_length_for_sort_data';
row_id排序的思想就是把不需要的数据不放到sort_buffer中,让sort_buffer中只存放需要排序的字段。
举个例子:
explain select nickname,card_id,age from user order by card_id;
我们前面说到了sort buffer,在sort buffer里面进行排序的数据是我们select的全部字段,所以当我们查询的字段越多,那么sort buffer能容纳的数据量也就越小。而通过row_id排序就只会存放row_id 字段和排序相关的字段。其余的字段等排序完成之后通过主键ID进行回表拿。
没什么太大的差异group by实际是先进行排序,再进行分组。所以遵循order by的索引机制。
责任编辑:武晓燕 来源: 今日头条 聚集索引存储数据(责任编辑:探索)
申万宏源(06806.HK)“21申证C2”3月19日起上升交易 期限3年
想探究 Windows 背后的故事和技术吗?微软推出 Windows Core OS Platform 官方博客
中国银行(03988.HK)拟赎回全部2.8亿股第二期境内优先股 每股面值人民币100元