MySQL 索引

最新推荐文章于 2025-10-24 14:57:24 发布
原创 最新推荐文章于 2025-10-24 14:57:24 发布 · 854 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍了MySQL索引的创建与类型，包括普通索引、唯一索引和主键索引，强调了索引在提高查询效率中的作用。通过二叉搜索树、平衡二叉树、B-树和B+树的概念，解释了数据库如何利用索引快速查找数据。文章还探讨了索引的最左匹配原则、联合索引和失效场景，并提供了查询优化的技巧，如使用EXPLAIN命令。此外，讨论了MyISAM和InnoDB存储引擎在索引上的差异。

  1. MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

  2. 索引一般作用于 where 查询条件的字段上。

  3. 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为where子句的条件)。

  4. 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

  5. 索引的好处很多，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

  6. 建立索引会占用磁盘空间的索引文件。

创建单列索引

CREATE TABLE test (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
	UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

在这里插入图片描述

普通索引

普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。

-- 创建普通索引格式
index (列名)

唯一索引

唯一索引：与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。

-- 创建了唯一索引格式
unique index (列名)

主键索引

主键索引是特殊的唯一索引，不允许有空值。

-- 主键本身也是一种索引
PRIMARY KEY (id),

修改表结构添加普通索引

alter table 表名 add index (列名);

索引提高查询效率

有一张表，表里有11137条数据
在这里插入图片描述
在这里插入图片描述
这张表有 主键索引和普通索引

当我用两个没有索引的两个字段去寻找某一行数据时

select * from shop_order where shop_order_user ='5f695efcaca6445781bf963fb9d6a03c' and shop_order_code = '1600009719024';

使用cmd进入mysql

找到mysql的bin目录，cmd进入，输入mysql -uroot -p，再输入密码

输入show variables like "profiling";
在这里插入图片描述
可以看到是off状态，关闭。需要开启

输入set profiling = on;
在这里插入图片描述
再输入show variables like "profiling";看一下是否开启
在这里插入图片描述
可以看到已经开启

可以查看运行效率show profile;

树的结构

索引的数据结构就是树。
https://blog.youkuaiyun.com/Horace_hr/article/details/114530891

二叉搜索树

  • 二叉树：树的每个节点 最多 只能有两个子节点。
  • 二叉搜索树： 如果我们给二叉树加一个额外的条件，就可以得到一种被称作二叉搜索树(binary search tree)的特殊二叉树。
  • 二叉搜索树要求：若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
    若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
    它的左、右子树也分别为二叉排序树。

二叉搜索树-遍历节点：

遍历树是根据一种特定的顺序访问树的每一个节点。比较常用的有前序遍历，中序遍历和后序遍历。而二叉搜索树最常用的是中序遍历。
　　①中序遍历:左子树——> 根节点 ——> 右子树
　　②前序遍历:根节点——> 左子树 ——> 右子树
　　③后序遍历:左子树——> 右子树 ——> 根节点

平衡二叉树

AVL树（平衡二叉树）有什么特点？

  1. 具有二叉查找树的全部特性
  2. 每个节点的左子树和右子树的高度差至多等于1，即每个节点的左子树的高度减去右子树的高度的差只能是-1，0，1（此高度差又称平衡因子，1代表左子树的高度比右子树高1）,且左子树和右子树也是平衡二叉树。
  3. 对于一棵有N个节点的AVL树，其高度保持在O(log2N)

B-树

数据库索引采用树结构储存是因为：树的查询效率高，而且还可以保持有序。

索引没有使用二叉查找树来实现是因为：磁盘IO。

数据库索引是存储再磁盘上的，当数据量较大时，索引的大小可能有几个G甚至更多。

当利用索引查询的时候，不能把整个索引全部加载到内存。只能逐一加载每一个磁盘页，这里的磁盘页对应着索引树的节点。

二叉搜索树最坏的情况就是磁盘IO次数等于索引树的高度。

要尽可能少的IO次数来读取到你想查找的数据。读的越多越耗时。所以要把高度进行压缩。

具体介绍一下B-树（Balance Tree），一个m阶的B树具有如下几个特征：

  1. 根结点至少有两个子女。
  2. 每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
  3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
  4. 所有的叶子结点都位于同一层。
  5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

以一个3阶B-树为例：
在这里插入图片描述
在这里插入图片描述
通过整个流程我们可以看出，B-树再查询中的比较次数不比二叉搜索树少，尤其是当单一节点中的元素数量很多的时候。

但是相比磁盘IO的速度，内存的比较耗时几乎可以忽略，所以只要树的高度足够低，IO次数足够少，就可以提升查找性能。

相比之下节点内部元素多一些也没关系，仅仅是多了几次内存交互，只要不超过磁盘页的大小即可。

B+树

B+树是基于B-树的一种变体，有着比B-树更高的查询性能。

一个m阶的B+树具有如下几个特征：

  1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
    在这里插入图片描述
    每一个父节点的元素都出现在子节点中，是子节点的最大或最小元素。
    在这里插入图片描述
    需要注意的是，根节点的最大元素（这里是15），也就等同于B+树的最大元素。以后无论插入删除多少元素，要始终保持最大元素再根节点中。

由于父节点的元素都出现再子节点，因此所以叶子节点包含了全量元素信息。

每一个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。
在这里插入图片描述
B+树还具有一个特点，那就是卫星数据的位置。

所谓卫星数据，指的是索引元素所指向的数据记录，比如数据库中的某一行。

  • 在B-树中，无论中间节点还是叶子节点都带有卫星数据。
  • 而在B+树中，只有叶子节点待遇卫星数据，其余中间节点仅仅是索引，没有任何数据关联。

B+树的好处主要体现在查询性上：

  • 中间节点没有卫星数据，索引同样大小的磁盘页可以容纳更多的节点元素。
  • 数据相同的情况下，B+树的结构比B-树更加矮胖。因此查询是IO次数也更少。
  • 查询必须最终查找到叶子节点，而B-树只有找到匹配元素即可，无论处于中间节点还是叶子节点。
  • B+树每次查找都是稳定的。B-树并不稳定（最好情况是查到根节点，最坏情况是查到叶子节点）。

B-树做范围查询只能一块繁琐的中序遍历。

而B+树的范围查询只需要在链表上做遍历即可。

总的来说，B+树相比B-树的优势有三个：IO次数更少；查询更稳定；范围查询简便。

B+树的优势：

  1. 单一节点存储更多的元素，使得查询的IO次数更少。
  2. 所有查询都要查找到叶子节点，查询性能稳定。
  3. 所有叶子节点形成有序链表，便于范围查询。

MYSQL索引原理

不是每一个列都需要建索引，要针对区分度高的列建索引，根据业务环境创建索引。

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

查询优化神器 - explain命令

https://www.cnblogs.com/tufujie/p/9413852.html

explain 后接查询语句

运行后显示分析结果
在这里插入图片描述
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

联合索引

联合索引是指对表上的多个列进行索引。

创建联合索引语句：

create index 新建列名 using btree on 表名 (第一个列名, 第二个列名, ...);

创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

1、复合索引使用的目的是什么？
:能形成索引覆盖,提高where语句的查询效率

2、一个复合索引是否可以代替多个单一索引？
:复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会被使用
所以,正常情况下复合索引不能替代多个单一索引

3、在进行哪些类型的查询时，使用复合索引会比较有效？
:如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率

4、符合索引中索引列的排序原则是什么？
:复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推

5、什么情况下不适合使用复合索引？
:建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立,另外如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引,另外如果表是经常需要更新的也不适合做索引

最左匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

左边的区分度要高。

区分度判断

判断语句：

select count(distinct 列名)/count(*) from 表名;

索引失效

  1. 前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)。
  2. 如果是组合索引的话，如果不按照索引的顺序进行查找，比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时，则会进行全表查询。
  3. 条件中有or。
  4. 索引无法存储null值，所以where的判断条件如果对字段进行了null值判断，将导致数据库放弃索引而进行全表查询。
  5. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  6. in 和 not in 也要慎用，否则会导致全表扫描
  7. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
  9. 在索引列上进行计算。

索引规约

  1. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
  2. 超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
  3. 在varchar字段上建立索引时，必须制定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
  4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
  5. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
  6. 利用覆盖索引来进行查询操作，避免回表。
  7. 利用延迟关联或者子查询优化超多分页场景。
  8. SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
  9. 健组合索引的时候，区分度最高的在最左边。
  10. 防止因字段类型不同造成的隐式转换，导致索引失效。

分库分表

大致了解即可。
https://www.cnblogs.com/jett010/p/4885767.html

MyISAM与InnoDB

MyISAM 和 InnoDB 都使用B+树来实现索引：

  • MyISAM的索引与数据分开存储
  • MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别
  • InnoDB的聚集索引和数据行统一存储
  • InnoDB的聚集索引存储数据行本身，普通索引存储主键
  • InnoDB一定有且只有一个聚集索引
  • InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK
mysql索引详解
学Java，找哪吒
07-07 11万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL索引
一直走的很慢的鸭子
10-30 1371
二级索引：idx_name_age 包含了 name 与 age两个字段查询语句：select name,age from table where name = 'xxx'像上述这种情况，所需要查询的字段正是二级索引中所包含的字段，二级索引可以直接满足查询需求，所以这种情况下就无需再回表查询了。
MySQL -- 索引
Afison的博客
06-04 1538
当表中的数据量过大时，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。
索引覆盖与回表
AF_0228的博客
09-09 6053
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
【MySQL】索引创建语法：CREATE INDEX的完整指南
最新发布
2501_93877867的博客
10-24 892
语句用于在现有表上创建索引，从而加速数据检索。本指南将逐步解释其完整语法、使用方法和注意事项，确保你掌握高效索引创建技巧。所有内容基于MySQL官方文档（以MySQL 8.0版本为准），力求真实可靠。在MySQL中，索引是提高查询性能的关键工具。以下是一些常见场景的代码示例，帮助你快速上手。是优化MySQL查询的核心工具。表上创建多列索引，并对。
mysql索引的一点小知识
程序员小董的专栏
11-10 2653
msyql索引的相关知识
mysql where索引_MySQL的where条件优化
weixin_28728495的博客
01-18 1269
where 条件优化适合select delete update1.避免无用的括号((a AND b) AND c OR (((a AND b) AND (c AND d))))-> (a AND b AND c) OR (a AND b AND c AND d)2.常量合并(a-> b>5 AND b=c AND a=53.常量条件移除(B>=5 AND B=5) O...
MySQL搜索：WHERE
知行知的专栏
07-10 1748
MySQL指定搜索条件进行搜索可以使用where条件。 在SELECT语句中，数据根据WHERE子语句中指定的条件进行过滤，WHERE子语句在表名之后给出。 product表如下： a 查找价格等于2.50的行 对于WHERE语句后面的操作符有如下种类，有了这些操作符就可以进行相应的条件过滤了： 操作符 说明 = 等于
精选资源
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
mysql的索引
m0_47017717的博客
07-16 870
本人学习数据库索引的总结和笔记
mysql多条件索引
02-23
数据库的索引可以加快查询速度，原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。 索引优化是数据库优化的最重要手段。 如果查询语句使用索引（通常是where条件匹配索引)就会利用树的结构加快查找，索引会按值查找到要查找的行在表中位置，不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据，需要消耗额外的磁盘IO。所以有些时候（如按顺序读取数据）全表扫描会比使用索引快的原因就在于此。
mysql where 条件后面索引
若疆~赤云
02-15 5267
假设有组合索引 index(a,b,c) where a = 3 用到索引 a where a = 3 and b = 3 用到索引 a,b where a = 3 and b = 4 and c = 5 用到索引 a,b,c where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 索引失效 where a = 3 and c = 5 ...
MySQL---索引
flyingcloud6的博客
07-16 732
对MySQL的索引进行了总结。
详解 MySQL 中的索引
Weiki的博客
04-03 1849
索引是 MySQL 数据库性能优化的重要工具，但其设计和使用需要权衡查询性能和写入性能之间的平衡。在实际项目中，根据业务需求、数据量大小以及查询频率等因素，合理规划和调整索引，才能达到最佳的性能表现。
mysql排序与索引关系
qq_43517448的博客
12-05 1748
MySQL排序与索引的关联 sql语句： EXPLAIN SELECT id FROM account LIMIT 99999, 20 EXPLAIN SELECT id FROM account WHERE id >( SELECT id FROM account LIMIT 99999, 1 ) LIMIT 0, 20 这两种效率是类似的，网上不晓得是如何弄得，亲测 https://www.cnblogs.com/wanghongsen/p/126165
MySql索引基础指南
世界既不黑也不白，而是一道精致的灰。
05-07 737
MySql索引指南索引概述索引类型1.创建索引2.删除索引3.设计索引原则1.要在条件列上创建索引2.尽量使用唯一索引3.使用短索引4.利用最左前缀5.对于InnoDB存储引擎的表，尽量手工指定主键。 索引概述 所有MYSQL列类型都能被索引，对相关列使用索引是提高SELECT操作性能的最佳途径 索引类型 1.主键索引：数据列不允许重复，不允许为NULL，一个表只有一个主键。 2.唯一索引：数据列不允许重复，允许为NULL，一个表允许多个列创建唯一索引。 3.普通索引：基本的索引类型，没有唯一性的限制，允许
mysql 索引顺序_MySQL中where条件的顺序对索引的影响
weixin_42370927的博客
01-18 9787
看到有资料说，where条件的顺序和索引的顺序不一致，会影响查询的效率，根据的逻辑是：where条件的运行是从右到左的，将选择性强的条件放到最右边，可以先过滤掉大部分的数据(而选择性不强的条件过滤后的结果集仍然很大)，在根据其它条件过滤时，需要比较的数据量就少，提高查询的效率。当我看到这个逻辑时，自我认为不是太合理，数据库引擎在执行SQL语句时，都是要经过查询优化器的，查询优化器会将SQL进行优化...
MySQL基础(六)-索引（index）详解
热门推荐
five小点心的博客
12-11 1万+
索引的创建：create index 索引名 on 表名(字段)
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引、哈希索引、全文索引等。其中，B树索引是最常用的一种，也是默认的索引类型。B树索引可以用于精确匹配和范围查询，而哈希索引主要用于等值查询，全文索引则用于文本检索。 2. 索引创建 可以在创建表时指定索引，例如： ``` CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), INDEX idx_email (email) ); ``` 也可以在已有的表上添加索引，例如： ``` ALTER TABLE users ADD INDEX idx_name (name); ``` 3. 索引使用 查询语句中可以使用WHERE子句和ORDER BY子句来利用索引，例如： ``` SELECT * FROM users WHERE email = 'example@example.com'; SELECT * FROM users WHERE name LIKE 'John%' ORDER BY id DESC; ``` 需要注意的是，索引并不是越多越好，过多的索引会占用过多的磁盘空间并降低写操作的性能。因此，需要根据实际情况选择合适的索引。同时，还需要定期对索引进行维护，包括优化查询语句、删除不必要的索引等。 4. 索引优化 MySQL提供了一些工具来优化索引，例如EXPLAIN命令可以帮助分析查询语句的执行计划，找出慢查询和不必要的全表扫描。可以使用OPTIMIZE TABLE命令来优化表的索引和碎片，从而提高查询性能。还可以使用缓存来避免频繁的查询操作，例如使用Memcached或Redis等缓存工具。 以上就是MySQL索引的一些基本概念和使用方法，需要根据实际情况进行选择和优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值