你好赵伟

这个屌丝很懒，什么也没留下！

热门标签

史上最全MySQL索引详解，你想要的都在这里，吊打面试官

作者：你好赵伟 | 2024-06-07 04:40:07

踩

mysql索引

在数据库中，B+树的高度一般都在24层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，24次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

4.1聚集索引

#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一

它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

#参照第六小结测试索引的准备阶段来创建出表s1

mysql> desc s1; #最开始没有主键

±-------±------------±-----±----±--------±------+

±-------±------------±-----±----±--------±------+

| id | int(11) | NO | | NULL | |

±-------±------------±-----±----±--------±------+

4 rows in set (0.00 sec)

mysql> explain select * from s1 order by id desc limit 10; #Using filesort，需要二次排序

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±---------------+

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±---------------+

| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2633472 | 100.00 | Using filesort |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±---------------+

1 row in set, 1 warning (0.11 sec)

mysql> alter table s1 add primary key(id); #添加主键

Query OK, 0 rows affected (13.37 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from s1 order by id desc limit 10; #基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序

±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------+

±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------+

| 1 | SIMPLE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | NULL |

±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------+

1 row in set, 1 warning (0.04 sec)

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

mysql> alter table s1 drop primary key;

Query OK, 2699998 rows affected (24.23 sec)

Records: 2699998 Duplicates: 0 Warnings: 0

mysql> desc s1;

±-------±------------±-----±----±--------±------+

±-------±------------±-----±----±--------±------+

| id | int(11) | NO | | NULL | |

±-------±------------±-----±----±--------±------+

4 rows in set (0.12 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下，explain就是预估一下你的sql的执行效率

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+

| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2690100 | 11.11 | Using where |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+

1 row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);

Query OK, 0 rows affected (16.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

| 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1343355 | 100.00 | Using where |

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

1 row in set, 1 warning (0.09 sec)

4.2辅助索引

就是我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx，没法用到主键索引的效率，怎么办，就需要我们添加辅助索引了，给name添加一个辅助索引。

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）（unique key啊、index key啊），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点存放的是对应的那条数据的主键字段的值，除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark），其实这个书签你可以理解为是一个{‘name字段’，name的值，主键id值}的这么一个数据。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。如果我们select 后面要的是name，我们直接就可以在辅助索引的叶子节点找到对应的name值，比如：select name from tb1 where name=‘xx’；这个xx值你直接就在辅助索引的叶子节点就能找到，这种我们也可以称为覆盖索引。如果你select后面的字段不是name，例如：select age from tb1 where name=‘xx’；也就是说，我通过辅助索引的叶子节点不能直接拿到age的值，需要通过辅助索引的叶子节点中保存的主键id的值再去通过聚集索引来找到完整的一条记录，然后从这个记录里面拿出age的值，这种操作有时候也成为回表操作，就是从头再回去查一遍，这种的查询效率也很高，但是比覆盖索引低一些，再说一下昂，再辅助索引的叶子节点就能找到你想找的数据可称为覆盖索引。

5.MySQL索引管理

5.1功能

#1. 索引的功能就是加速查找

#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

5.2MySQL常用的索引

普通索引INDEX：加速查找

唯一索引：

-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：

-PRIMARY KEY(id,name):联合主键索引

-UNIQUE(id,name):联合唯一索引

-INDEX(id,name):联合普通索引

索引操作：

添加主键索引:

创建的时候添加: 添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.

聚集索引的添加方式

创建的是添加

Create table t1(

Id int primary key,

)

Create table t1(

Id int,

Primary key(id)

)

表创建完了之后添加

Alter table 表名 add primary key(id)

删除主键索引:

Alter table 表名 drop primary key;

唯一索引:

Create table t1(

Id int unique,

)

Create table t1(

Id int,

Unique key uni_name (id)

)

表创建好之后添加唯一索引:

alter table s1 add unique key u_name(id);

删除:

Alter table s1 drop index u_name;

普通索引:

创建:

Create table t1(

Id int,

Index index_name(id)

)

Alter table s1 add index index_name(id);

Create index index_name on s1(id);

删除:

Alter table s1 drop index u_name;

DROP INDEX 索引名 ON 表名字;

各种索引的应用场景：

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表

有下列字段：

会员编号 INT

会员姓名 VARCHAR(10)

会员身份证号码 VARCHAR(18)

会员电话 VARCHAR(10)

会员住址 VARCHAR(50)

会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY

会员姓名如果要建索引的话，那么就是普通的 INDEX

会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

#除此之外还有全文索引，即FULLTEXT

会员备注信息，如果需要建索引的话，可以选择全文搜索。

用于搜索很长一篇文章的时候，效果最好。

用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

#其他的如空间索引SPATIAL，了解即可，几乎不用

5.3索引的两大类型hash与btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

5.4创建/删除索引的语法

#方法一：创建表时

CREATE TABLE 表名 (

字段名1 数据类型 [完整性约束条件…],

字段名2 数据类型 [完整性约束条件…],

[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY

[索引名] (字段名[(长度)] [ASC |DESC])

);

#方法二：CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名

ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX

索引名 (字段名[(长度)] [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;

看下面的示范：

#方式一

create table t1(

id int,

name char,

age int,

sex enum(‘male’,‘female’),

unique key uni_id(id),

index ix_name(name) #index没有key

);

#方式二

create index ix_age on t1(age);

#方式三

alter table t1 add index ix_sex(sex);

#查看

mysql> show create table t1;

| t1 | CREATE TABLE t1 (

id int(11) DEFAULT NULL,

name char(1) DEFAULT NULL,

age int(11) DEFAULT NULL,

sex enum(‘male’,‘female’) DEFAULT NULL,

UNIQUE KEY uni_id (id),

KEY ix_name (name),

KEY ix_age (age),

KEY ix_sex (sex)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

6.测试索引

6.1准备

#1. 准备表

create table s1(

id int,

name varchar(20),

gender char(6),

email varchar(50)

);

#2. 创建存储过程，实现批量插入记录

delimiter $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲声明存储过程的结束符号为$

create procedure auto_insert1()

BEGIN

declare i int default 1;

while(i<3000000)do

insert into s1 values(i,‘egon’,‘male’,concat(‘egon’,i,‘@oldboy’));

set i=i+1;

end while;

END $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲$ 结束

delimiter ; #重新声明分号为结束符号

#3. 查看存储过程

show create procedure auto_insert1\G

#4. 调用存储过程

call auto_insert1();

6.2在没有索引的前提下测试查询速度

#无索引：mysql根本就不知道到底是否存在id等于333333333的记录，也不知道存在几条id=333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢

mysql> select * from s1 where id=333333333;

Empty set (0.33 sec)

6.3在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

或者用alter table s1 add primary key(id);加主键，建索引很慢的。

6.4在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

1. mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降低，因而速度明显提升

2. 我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了

3. 需要注意，如下图

6.5总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快

比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。

建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

你想一下，是不是全部加索引就一定好的呢，首先，我们加上索引，查询时快了，但是写入的时候就慢了，还记得吗，每次插入新的记录，你的整个索引结构都会跟着改，所以如果你乱加索引，你会发现，即便是你的网站没有几个人在注册，或者说没有几个写入数据的操作，你的磁盘IO会居高不下，磁盘在疯狂的转，因为你每插入一条数据，我们的索引都需要重新建，重建的索引要写入硬盘里面的，还记得我们给那三百万条数据建索引的时候的速度吗？每次都要把所有的数据取出来，做好一个数据结构，然后再写回硬盘，也就是要经历很多的IO才能实现这个事儿，所以乱加索引的一个弊端就是，你很少的写入都会导致你的磁盘IO非常的高，导致效率很差，所以我们要学一下怎么正确的加索引。

7.正确使用索引

7.1索引未命中

并不是说我们创建了索引就一定会加快查询速度，****若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

1 范围问题，或者说条件不明确，条件中出现这些符号或关键字：****>、>=、<、<=、!= 、between…and…、like、

大于号、小于号

如果你写where id >1 and id <1000000;你会发现，随着你范围的增大，速度会越来越慢，会成倍的体现出来。

不等于！=

between …and…

like #测like的时候，你可以先把id的主键索引去掉，然后测一下like，然后加上index key，再测一下。就知道你忘了，看语句：create index email_index on s1(email) ，email_index是索引名、on、 s1是表名（字段名），你会发现建索引的时间也是很慢的，desc s1;查看一下索引是不是创建成功了，看那个key字段是不是有个mul，有这个说明创建成功了。

like=后面如果没有那些特殊字符，通配符之类的，就跟等于是一个效果，精确匹配。

#使用like的时候，通配符写在最前面，也是需要全匹配一遍，然后在比较字符串的第二个字符，最左匹配的规则，还记得吗。

2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#先把表中的索引都删除，让我们专心研究区分度的问题

mysql> desc s1;

±-------±------------±-----±----±--------±------+

±-------±------------±-----±----±--------±------+

| id | int(11) | YES | MUL | NULL | |

±-------±------------±-----±----±--------±------+

4 rows in set (0.00 sec)

mysql> drop index a on s1;

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index d on s1;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc s1;

±-------±------------±-----±----±--------±------+

±-------±------------±-----±----±--------±------+

| id | int(11) | YES | | NULL | |

±-------±------------±-----±----±--------±------+

4 rows in set (0.00 sec)

我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）

回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<…

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为’egon’

#现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

#1：如果条件是name=‘xxxx’,那么肯定是可以第一时间判断出’xxxx’是不在索引树中的（因为树中所有的值均为’egon’,看第一条的时候就知道你不在索引树里面了），所以查询速度很快

#2：如果条件正好是name=‘egon’,查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢

3 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

总结

互联网大厂比较喜欢的人才特点：对技术有热情，强硬的技术基础实力；主动，善于团队协作，善于总结思考。无论是哪家公司，都很重视高并发高可用技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，是不是能真的得到锻炼。其实我写了这么多，只是我自己的总结，并不一定适用于所有人，相信经过一些面试，大家都会有这些感触。

**另外本人还整理收藏了2021年多家公司面试知识点以及各种技术点整理 **

下面有部分截图希望能对大家有所帮助。

在这里插入图片描述
实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！**

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-6qIK6Ezz-1712015987586)]
[外链图片转存中…(img-DFnJVV0W-1712015987587)]
[外链图片转存中…(img-BVorW6iT-1712015987587)]
[外链图片转存中…(img-GtSC310c-1712015987587)]
[外链图片转存中…(img-mFnmiwRI-1712015987588)]
[外链图片转存中…(img-BfVK5fKJ-1712015987588)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-6co9xOAo-1712015987589)]

总结

**另外本人还整理收藏了2021年多家公司面试知识点以及各种技术点整理 **

下面有部分截图希望能对大家有所帮助。

[外链图片转存中…(img-XxPZaOuy-1712015987589)]

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/683993