概念

MySQL中的索引是一种特殊的数据库对象，它可以帮助数据库管理系统（DBMS）快速检索表中的数据。索引类似于书籍的目录，它允许用户快速定位到数据，而不需要扫描整个表。

以下是索引的一些关键点：

  1. 提高查询效率：索引可以显著提高数据检索的速度，特别是对于大型数据库表来说，索引可以大幅减少查询所需的时间。

  2. 数据结构：索引通常基于B树（B-Tree）或其变种（如B+树）实现，这些数据结构支持快速的数据插入、删除和查找操作。

  3. 唯一性：唯一索引确保表中的每一行数据在索引列上都是唯一的。

  4. 复合索引：可以创建包含多个列的索引，这称为复合索引或多列索引，它允许基于多个列的组合来检索数据。

  5. 索引维护：虽然索引可以提高查询效率，但它们也会增加数据库的维护成本，因为索引需要在数据插入、删除和更新时进行同步更新。

  6. 索引类型：MySQL支持多种类型的索引，包括普通索引、唯一索引、全文索引、空间索引等。

  7. 索引选择性：选择性是指索引列中不同值的比例。高选择性的索引通常更有效，因为它们可以覆盖更多的数据行。

  8. 索引优化：数据库管理员需要定期检查和优化索引，以确保它们仍然适合当前的数据访问模式。

  9. 索引覆盖：当查询只需要从索引中检索数据时，这种情况称为索引覆盖，可以进一步提高查询效率。

  10. 索引和锁：在某些情况下，索引还与数据库的并发控制和锁机制有关，特别是在处理事务和并发访问时。

索引是数据库性能优化的重要组成部分，合理使用索引可以显著提高数据库操作的效率。

（以上关于索引的概念，来自AI）

数据结构

MySQL的数据是存储在硬盘上的，所以才能做到持久化。但是大量的磁盘I/O会带来严重的性能问题，解决问题的目标也很明确：减少磁盘I/O次数和加快I/O操作时间。本文的重点在于后者。

MySQL的Innodb存储引擎采用的是一种名为【B+树】的数据结构。

之前的文章中，我们提到过，innodb中的数据是以【行】为单位，存储在一个又一个大小为【16kb】的【页】中，这些不同的【页】以【B+树】的形式相互关联。

这里贴一篇文章，一定要好好理解哦：https://blog.csdn.net/fight_p/article/details/141191580

动画视频：https://www.bilibili.com/video/BV1pJ4m1j7Pm/?spm_id_from=333.337.search-card.all.click&vd_source=23be71daeaec4f5580907da38be874f8

详细讲解：https://www.bilibili.com/video/BV1vQ4y1N79m/?spm_id_from=333.337.search-card.all.click&vd_source=23be71daeaec4f5580907da38be874f8

常见误区：https://www.bilibili.com/video/BV1jb421e7iS/?spm_id_from=333.337.search-card.all.click

优质视频：https://www.bilibili.com/video/BV1Wm4y147mS/?spm_id_from=333.337.search-card.all.click&vd_source=23be71daeaec4f5580907da38be874f8

索引的分类和创建

1、聚簇索引和非聚簇索引

【主键和数据共存】的索引被称为【聚簇索引】，其他的索引，被称为【非聚簇索引】。聚簇索引只有在innodb存储引擎中才存在，Myisam中是不存在的：

image-20241118111543057

Innodb使用的是【聚簇索引】，他会将【主键】组织到一棵【B+】树中，而【行数据】就存储在叶子节点上，若使用where id = 1这样的条件查找猪圈，则按照B+树的检索算法即可查找到对应存放数据的叶子节点。

假设现在我们有一张学生表，表中有一个name字段，因为根据学生姓名查找学生信息的操作比较频繁，我们决定给name字段加上索引。如对name字段进行条件查询，需要两个步骤：

  1. 在name字段的索引树上查找到对应的节点，获取节点的id值
  2. 通过上一步获取到的id值，回到【主键索引树】中去查找【id对应的行数据】

如图：

image-20241118112243779

MyIsam使用的是【非聚簇索引】，本质上也是一棵【B+树】，节点的结构完全一致，只是存储的内容不同而已。

表数据存储在一个独立的地方，树的叶子节点使用一个【地址指向数据的存放位置】，减少了一次回表的操作。

小知识
  • 聚簇索引【默认使用主键】，如果表中没有定义主键，innodb会选择一个【非空且唯一】的列代替。如果没有这样的列，innodb会隐式定义一个主键【rowid】来作为聚簇索引的列。
  • 如果涉及到大数据量的排序、全表扫描、count之类的操作，MyIsam还是比innodb有优势，因为索引所占空间小，这些操作是需要在内存中完成的。
小问题：MySQL中，主键为什么建议使用自增id？
  • 主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树的中间位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
  • 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据也一定是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但仍无法彻底避免。但如果是自增id，它只需要一页一页地写，索引结构相对紧凑，磁盘随便少，效率也高。

2、普通索引和唯一索引

所谓的普通索引就是普普通通的索引，没有什么特殊的要求，理论上任何列都可以当做普通索引。

下面列举一些有关索引的操作语句：

创建索引
# 第一种方式
create index [索引名] on 表名(字段名);

# 第二种方式
alter table [表名] add index [索引名](字段名);

# 第三种方式
create table [表名] (
	id int primary key auto_increment,
    name varchar(50),
    index [索引名](字段名(长度))
);
删除索引
drop index [索引名] on [表名];

创建索引是一项比较耗时的操作，数据量越大，速度越慢。

小技巧

有的列【数据量较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱其实大家的后缀都差不都，可以选择忽略。

alter table [表名] add index [索引名](字段名(字段长度));

（这也叫做前缀索引）

所谓的唯一索引呢，就是该列的值不能重复，都是唯一的。

索引的创建
# 第一种方式
create unique index [索引名] on 表名(字段名);

# 第二种方式
alter table [表名] add unique index [索引名](字段名);

# 第三种方式
create table [表名] (
	id int primary key auto_increment,
    name varchar(50),
    unique index [索引名](字段名(长度))
);
删除索引

和删除普通索引没什么区别。

唯一索引和主键的区别

主键默认【非空 + 唯一】，逻辑上强调【递增性】，一个表只能有【一个主键】；

唯一索引可以有null值（只能有一个），一张表可以有多个唯一索引。

唯一约束和唯一索引的区别

其实两者本质上是一样的，只不过有一些规范上面的东西：

创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束就是通过唯一索引来实现数据唯一的。

创建一个唯一索引，这个索引就是独立的索引，可以单独删除。

3、多个二级索引的组合使用

记住一点：MySQL在执行查询语句的时候，一般只会使用【一个索引】，除非是使用【or连接的两个索引列】会产生索引合并。

模拟：

drop table if exists tb_goods;
create table tb_goods (
                          id int primary key auto_increment comment '主键',
                          brand varchar(64) comment '商品品牌',
                          price double comment '商品价格',
                          origin varchar(512) comment '商品产地',
                          index idx_name(brand),
                          index idx_price(price),
                          index idx_origin(origin)
) engine=innodb default charset=utf8mb4 collate utf8mb4_general_ci comment '商品表';

INSERT INTO tb_goods (brand, price, origin) VALUES
                                                ('Apple', 999.99, 'USA'),
                                                ('Samsung', 899.99, 'South Korea'),
                                                ('Huawei', 1299.99, 'China'),
                                                ('Xiaomi', 699.99, 'China'),
                                                ('Sony', 1499.99, 'Japan'),
                                                ('Microsoft', 999.99, 'USA'),
                                                ('Nintendo', 399.99, 'China'),
                                                ('Lenovo', 1199.99, 'China'),
                                                ('Dell', 1299.99, 'USA'),
                                                ('Asus', 899.99, 'Taiwan');

  1. 检索品牌为【Huawei】的手机信息

    select * from tb_goods where brand = 'Huawei';

    走【name字段的索引】，检索出【华为Mate 50】的【id】，回表查询。使用了一个索引。

  2. 检索品牌为【Huawei】，价格在【3000-6000】的商品

    select * from tb_goods where brand = 'Huawei' and price between 3000 and 6000;

    走【price字段的索引】，然后回表扫描，过滤数据。使用了一个索引。

  3. 检索品牌为【Huawei】，价格为【4999.99】，且产地在【中国】的商品

    select * from tb_goods where name = 'Huawei' and price = '4999.99' and origin = '中国';

    走【price字段索引】，然后回表扫描，进行对应的【条件过滤】。

  4. 检索品牌为【 ‘Huawei’ 或者 ‘Apple’ 】的商品

    select * from tb_goods where brand = 'Huawei' or brand = 'Apple';
select * from tb_goods where brand in ('Huawei','Apple');

    并没有走索引，而是全表扫描。

  5. 检索名称为【Huawei】或者价格【大于8000】的商品

    select * from tb_goods where brand = 'Huawei' or price > 8000;

    走【name和price字段索引】，这个就是索引合并的一个过程，两个索引取【并集】。

  6. 检索名称为【Huawei】，价格【大于8000】，数量（无索引）大于【50】的商品

    select * from tb_goods where brand = 'Huawei' and price > 8000 and amount > 50;

    走【price字段的索引】。

  7. 检索名称为【Huawei】，或价格【大于8000】，或数量（无索引）大于【50】的商品

    select * from tb_goods where brand = 'Huawei' or price > 8000 or amount > 50;

    全表扫描。

4、复合索引（联合索引）

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

创建复合索引
alert table [表名] add [索引名] table (字段1，字段2，字段3，...);

复合索引的结构如下，复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序，依次类推，如下图：

image-20241118153305050

转成表格可能要好看一下：

image-20241118153336320

  1. 检索品牌为【Armani】的包包

    通过【品牌索引】直接定位。

  2. 检索品牌为【Armani】，价格在【10000~30000】之间的包包

    根据【品牌索引】进行初筛，然后在初筛后的结果集上根据价格进行最后的过滤。

  3. 检索品牌为【Armani】或者价格大于【8000】的包包

    【or】是一个并集，根据最左匹配原则，复合索引失效，全表扫描。

  4. 检索品牌为【Armani】，且价格大于【8000】，且产地（无索引）在【北京】的包包

    【and】是交集，所以没有索引没有关系，还是会走复合索引的。

  5. 检索品牌为【Armani 和 LV】之间，价格在【1000~3000】之间

    会走联合索引，但是由于由于【品牌检索】不是等值查询，是范围扫描。所以，价格过滤的时候，需要在上一步的结果集上进行遍历。

最左匹配原则：

  1. 这是一个非常重要的匹配原则，MySQL会一直向右匹配直到遇到范围查询（<、＞、between、like）就停止匹配。比如a=1 and b=2 and c > 3 and d = 4，如果建立（a，b，c，d）顺序的联合索引，d是用不到索引的，如果建立（a，c，d，c）的索引，则都可以用到，a，b，d的顺序可以任意调整。
  2. = 和 in 可以乱序，比如a=1 and b<2 and c=3，咱们建立的索引就可以是（a，c，b）或者（c，a，b）。（查询优化器会根据索引的建立顺序进行优化的）

5、全文索引

这个功能是专门针对【文本字符串】的，提高特定字符串的查找效率。了解一下就行了，真的要做，我们有更好的替代产品，如【ElasticSearch】。

使用【like】实现的模糊查询有点类似于全文索引，但是对于大量的文本数据检索，全文索引比【like】快很多，速度不是一个数量及的，但是MySQL的全文索引存在【精度问题】。同时普通索引在使用【like】时如果%放在首位，那么会导致索引失效。

MySQL各版本对于全文索引的支持
  1. MySQL5.6以前的版本，只有MyIsam存储引擎支持全文索引
  2. MySQL5.6及以后的版本，MyIsam和InnoDB存储引擎均支持全文索引
  3. 只有字段类型为【char、varhcar、text】及其系列才可以创建全文索引
使用全文索引的注意事项
  1. 搞清楚MySQL的版本
  2. 虽然比【like】语句快，但是存在精度问题
  3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引
  4. 对于中文，MySQL5.7.6之后默认支持，或者使用第三方插件
创建全文索引
# 建表是创建
create table [表名] (
	...
    fulltext key [索引名](字段名)
);

# 建表后创建(1)
create fulltext index [索引名] on [表名](字段名);

# 建表后创建(2)
alter table [表名] add fulltext index [索引名](字段名);
删除全文索引
drop index fulltext [索引名] on [表名];
全文检索语法
select * from [表名] where match(字段名) against('xxx');

6、hahs索引

hash索引是Memory存储引擎的默认方式，而且只有Memory引擎支持hahs索引，Memory存储引擎的数据是放在内存当中的，一旦服务关闭，表中的数据就会丢失。

合理的使用Memory引擎可以极大的提升性能，针对Memory引擎的特点（服务重启或者宕机数据会丢失），我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，浙西诶数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载。

当然，内存是有限且较小的，MySQL默认单表内存中的最大大小是16MB，可以通过修改配置文件进行更改：

tmp_table_size = 2048M
max_heap_table_size = 2048M
创建hash索引
create index [索引名] using hash on hash_user(字段名);
删除hash索引

和删除普通索引没有任何区别。

需要注意的一些地方
  • hash是一种【key-value】形式的数据结构。实现的一般形式是【数组+链表】，通过hash函数计算出【key】在数组中的位置，然后如果出现hash冲突的话，就通过【链表】来解决。
  • 即使很相近的【key】，hash的取值也是完全没有规律的，hash索引不支持范围查询。
  • hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（查询行位置，然后找到具体数据）
  • hash索引查询数据的前提就是计算hash值，也就是要求【key】为一个能准确指向一条数据的【key】，所以对于like等一类的匹配查询是不支持的。
  • 只要是只需要做等值比较查询，而不包含排序或者范围查询的需求，都适合使用hash索引。

Explain关键字

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain的使用也很简单，只需要在目标SQL前加上该关键字即可：

explain select * from tb_user where username = 'Bob456';

image-20241119111408727

对结果集做简要的说明：

image-20241119111940923

1、id字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

（1）id相同

id如果相同，可以认为是一组，执行顺序从上到下：

explain select * from tb_student s , tb_score c where s.id = c.user_id;

image-20241119112855193

（2）id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行：

explain select * from tb_student where age > (
     select age from tb_student where name = '吴九'
    );

image-20241119113329165

（3）id部分相同，部分不同

id如果相同，可以认为是一组，从上往下顺序执行在所有组中，id值越大，优先级越高：

explain select * from tb_student s,tb_score c where s.id = c.user_id
union
select * from tb_student s,tb_score c where s.id = c.user_id;

image-20241119113634946

2、select_type字段

（1）SIMPLE

简单查询，不包含子查询或者union查询的SQL语句。

（2）PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。

（3）SUBQUERY

在select或者where中包含子查询

（4）UNION

若第二个select出现在union之后，则被标记为UNION。

（5）UNION RESULT

从UNION表获取结果的合并操作

3、type字段（重点）

最好到最差的查询类型排序：

null > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all

（1）null

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，比如id找不到：

explain select * from tb_student where  id = '-1';

image-20241119114602273

（2）system

表只有一行记录（等同于系统表）：

explain select * from mysql.proxies_priv;

image-20241119114803971

（3）const

表示通过索引一次就找到了，const用于比较【primary key】或【unique】索引，因为值匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量级别的查询：

explain select * from tb_student where id = 1;

image-20241119115053184

（4）eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描：

explain select * from tb_score c left join tb_student s on c.user_id = s.id;

image-20241119142412897

（5）ref

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能找到多个符合条件的行，是一种扫描和查找的的混合体：

explain select * from tb_student s left join tb_score c on s.id = c.user_id;

image-20241119143548235

（我复现不出来，哈哈）

（6）ref_or_null

类似ref，单位可以搜索值为null的行：

explain select * from tb_student where name = 'kaiven' or name is null;

image-20241119143433675

（7）index_merge

表示使用了索引合并的优化方法：

explain select * from tb_student where id = 1 or name = 'kaiven';

image-20241119143734129

（8）range

值检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。一般就是你的where语句中出现的【between、<>、in】等的查询：

explain select * from tb_student where id between 1 and 5;

image-20241119143921541

（9）index（全索引扫描）

遍历整颗索引二叉树：

explain select name from tb_student;

image-20241119144053439

（10）all（全表扫描）

就是遍历整张表嘛（包扣工资的）：

explain select * from tb_student;

image-20241119144151324

4、table字段

表示数据来自哪张表。

5、possible_keys字段

本次查询可能会走的索引字段集合。

6、key字段

实际使用到的索引，如果为null，则表示没有是用结构索引查询。

7、key_len字段

表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精确度的情况下，长度越短越好。key_len显示的值为索引字段最大的可能长度，并非实际使用长度。

8、ref字段

哪些列或常量被用于查找索引列上的值。

9、rows字段

根据表统计信息以及索引选用情况，大致估算出找到所需读取的行数。

10、partitions字段

匹配的分区，默认为null。

11、filtered字段

返回查询的结果行对于整张表记录行的占比（估算值）。

12、extra字段

一些比较重要的额外信息：

（1）Using filesort

只要使用非索引字段排序，就会使用文件排序（效率较低）。

（2）Using temporary

使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于【order by】和分组查询【group by】

（3）Using where

使用了where条件。

（4）Impossible where

where子句的值总是false，不能用来获取任何数据。

（5）Select tables optimized away

最优的SQL查询语句了，不用走索引和表。

（6）no matching row in const table

没有匹配的记录。

索引相关问题

合理的创建索引可以让查询的性能起飞。但是，不合理的索引创建同样会产生许多性能问题。

适合创建索引的场景

  1. 频繁作为where条件语句查询的字段
  2. 关联字段需要建立索引
  3. 分组、排序字段可以建立索引
  4. 统计字段可以创建索引

不适合创建索引的场景

  1. 频繁更新的字段不适合建立索引
  2. where条件中用不到的字段不适合建立索引
  3. 表数据可以确定且较少的不需要创建索引
  4. 区分度不高的字段不适合创建索引（比如说性别，枚举值）
  5. 参与列计算的列不适合创建索引，索引有可能会失效

注意点

  1. 能用复合索引就要使用复合索引
  2. null值也是可以走索引的，它被处理成最小值放在B+树的最左侧
  3. 使用短索引 => 提高查询速度、节约磁盘空间
  4. 使用组合索引的情况下，只会使用一个索引。因此，如果where子句中已经使用了索引的话，那么【order by】中的列是不会使用索引的。

MySQL索引失效的几种情况

  1. 使用【or】连接的查询，其中一个查询字段没有带索引的话，整个查询就不会走索引
  2. 复合索引不满足最左原则就不能使用全部索引
  3. like查询以%开头
  4. 存在列计算 => explain select * from student where age-1 = 18
  5. 存在类型转化 => 这个分场景，具体还需要explain
  6. 如果MySQL估计使用全表扫描要比使用索引快，则不会走索引

（以上列举出的只是一些常见的现象，具体在面试中还是需要结合实际的测试和场景去给面试官聊）

2024.11.19

writeBy kaiven