一文带你搞懂MySQL如何创建索引

前言

在数据库开发中，索引是提升查询性能最核心、最有效的手段。一个设计精良的索引可以将查询速度提升数个数量级，而一个糟糕的索引设计，不仅无法提升性能，反而会浪费磁盘空间，拖慢数据写入速度。

一、在idea中图形化创建索引

intellij idea（及其专业版内置的datagrip）的database工具窗口功能强大，让我们无需手写sql即可高效地管理数据库对象。对于索引的创建，图形化界面不仅直观，还能有效避免因拼写错误导致的语法问题。

我们以一个电商系统中的商品表 pms_product 为例，该表包含 product_code (商品编码), category_id (分类id), brand_id (品牌id), shop_price (商城价格), product_name (商品名称) 等字段。

1.定位目标表：在idea右侧的 database 工具窗口中，展开你的数据源，找到目标数据库，再找到 pms_product 表。

2.打开表结构编辑器：右键点击 pms_product 表，在弹出的菜单中选择 modify object... (或者直接使用快捷键 f4)。这将打开一个详细的表结构编辑界面。

3.切换到索引标签页：在表结构编辑界面的上方，你会看到 columns, keys, indices, foreign keys 等多个标签页。点击 indices 标签页。

4.新建索引：在 indices 标签页中，点击工具栏上的 + 号（或使用快捷键 alt + insert），从下拉菜单中选择 index。此时，界面下方会出现一个新的索引配置行。

5.配置索引属性：这是最核心的一步，我们需要仔细填写每一项：

• name (索引名称): 将默认生成的名称（如 index）修改为一个符合规范的、有意义的名称。业界通用的规范是 idx_字段名[_字段名]。例如，我们可以命名为 idx_category_brand。
• type (索引类型): 保持默认的 btree。这是mysql innodb引擎中最常用、最通用的索引类型，它支持等值查询、范围查询和排序操作。
• columns (索引字段): 点击 columns 区域下方的 + 号，会弹出当前表所有字段的列表。
  • 首先选择 category_id。
  • 再次点击 + 号，选择 brand_id。
  • 关键点：这里的上下顺序就是索引的实际顺序，它直接关系到“最左前缀原则”。
• unique (唯一索引): 这是一个复选框。如果业务逻辑要求 category_id 和 brand_id 的组合在表中是唯一的，则勾选它。对于普通的查询优化，不要勾选。

6.保存并应用

配置完成后，点击窗口右下角的 ok 按钮。idea会自动在后台生成并执行对应的 create index sql语句。

二、mysql索引的常见类型与sql创建方式

虽然图形化界面很方便，但理解其背后的sql语句是成为高级工程师的必经之路。索引的类型多种多样，每种都有其特定的应用场景。

1.普通索引（index）

这是最基本的索引类型，没有任何限制。它的作用是加速对表中数据的查询。

场景：product_name 字段经常被用于搜索，但允许重复。

-- 在 product_name 字段上创建普通索引
create index idx_product_name on pms_product(product_name);

2.唯一索引（unique index）

唯一索引与普通索引类似，但索引列的值必须唯一，允许有空值。它能保证数据列的唯一性。

场景：product_code 是商品的唯一编码，绝不允许重复。

-- 在 product_code 字段上创建唯一索引
create unique index uk_product_code on pms_product(product_code);

3.联合索引（composite index）

联合索引是在多个字段上创建一个索引。它遵循“最左前缀原则”，即查询条件中必须包含索引定义的最左侧字段，索引才能生效。

场景：经常需要根据 category_id 和 brand_id 组合查询商品。

-- 创建 (category_id, brand_id) 联合索引
create index idx_category_brand on pms_product(category_id, brand_id);

4.全文索引（fulltext index）

全文索引主要用于对大文本字段（如 text, varchar）进行关键词搜索。它比 like '%keyword%' 高效得多。

场景：在 product_description（商品详情）字段中进行全文搜索。

-- 在 product_description 字段上创建全文索引
alter table pms_product add fulltext index ft_product_desc(product_description);

5.主键索引（primary key）

主键索引是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。通常在创建表时定义。

create table pms_product (
    id bigint not null auto_increment,
    product_code varchar(64),
    -- ... 其他字段
    primary key (id) -- 定义主键索引
);

三、核心原理：深入理解最左前缀原则

最左前缀原则是理解联合索引的钥匙，也是面试和实战中最常被考察的知识点。很多开发者误以为sql语句中字段的书写顺序必须和索引定义完全一致，或者不理解为何索引会“莫名其妙”地失效。

什么是“最左前缀”？

联合索引 (category_id, brand_id) 在底层的b+树数据结构中，是按照以下规则进行排序和存储的：

1. 首先，所有数据严格按照 category_id 的值进行排序。
2. 在 category_id 值相同的情况下，再按照 brand_id 的值进行排序。

这就像一本电话簿，首先是按“姓氏”排序，在姓氏相同的人群内部，再按“名字”排序。

场景实战分析

基于我们刚刚创建的索引 idx_category_brand (category_id, brand_id)，我们来看几种不同的查询场景：

结论：sql语句中 where 子句的书写顺序不会影响索引的使用。但查询条件中必须包含联合索引定义的最左侧字段（即 category_id），索引才能被激活。如果缺少最左列，索引将完全失效。

四、索引失效的常见“陷阱”

即使建立了索引，如果sql语句编写不当，索引依然会失效，导致数据库进行低效的全表扫描。以下是生产环境中最常见的几种“陷阱”。

1.在索引列上进行函数运算或计算

这是新手最容易犯的错误，也是最容易被忽略的性能杀手。

错误写法

-- 对 create_time 字段使用了 year() 函数
select * from pms_product where year(create_time) = 2023;

后果：索引失效。数据库为了判断每一行是否符合条件，必须取出 create_time 的值并执行 year() 函数。这个操作破坏了索引列的原始值，导致无法利用b+树的有序性进行快速查找，只能进行全表扫描。

正确写法

-- 将计算移到等号右边，使用范围查询
select * from pms_product 
where create_time >= '2023-01-01 00:00:00' and create_time < '2024-01-01 00:00:00';

这种写法直接对索引列进行范围比较，可以高效地利用索引。

2.隐式类型转换

这是一个非常隐蔽的“陷阱”，往往在上线后才暴露问题。

场景：假设 product_code 字段在数据库中被定义为 varchar(64)，并且已经为其建立了索引。

错误写法

-- 查询值没有加单引号，被mysql识别为数字类型
select * from pms_product where product_code = 10001;

后果：mysql发现字段是字符串类型，而查询值是数字类型，会自动执行一个隐式转换，相当于 where cast(product_code as signed) = 10001。这等同于在索引列上使用了函数，导致索引失效。

正确写法

-- 加上单引号，确保查询值的类型与字段类型一致
select * from pms_product where product_code = '10001';

3.模糊查询以通配符开头

错误写法

-- 百分号在最前面
select * from pms_product where product_name like '%手机%';

后果：b+树是按照字段值的前缀进行排序的。% 开头意味着无法确定查找的起点，数据库只能扫描表中所有行的 product_name 字段，导致索引失效。

正确写法

-- 百分号在后面（前缀匹配）
select * from pms_product where product_name like '华为%';

这种前缀匹配可以利用索引快速定位到以“华为”开头的记录。

4.使用 or 连接非索引字段

场景：category_id 字段有索引，但 remark（备注）字段没有索引。

错误写法

select * from pms_product where category_id = 10 or remark = '热销商品';

后果：只要 or 连接的任意一个字段没有索引，mysql优化器通常会认为使用索引的成本更高，从而直接放弃索引，选择全表扫描。

五、进阶优化与注意事项

索引冗余与覆盖索引

• 避免冗余索引：如果你已经创建了联合索引 idx_a_b (a, b)，那么不需要再单独为字段 a 创建索引。因为联合索引本身就包含了 a 字段的有序信息，单独为 a 创建索引是完全多余的，只会浪费存储空间并降低 insert、update 等写入操作的性能。
• 利用覆盖索引：如果一个查询所需的所有字段都包含在索引中，数据库引擎无需再“回表”查询原始数据行，性能极佳。
  • 推荐 (覆盖索引)：select category_id, brand_id from pms_product where category_id = 10;
  • 不推荐 (需要回表)：select * from pms_product where category_id = 10;
    后者在查完索引后，还需要拿着主键id回到主索引（聚簇索引）中去获取其他字段的值，多了一次查找操作。

联合索引的字段顺序设计

在设计联合索引 (a, b) 时，字段的顺序至关重要，通常遵循以下两个原则：

• 区分度高的字段放前面：区分度是指字段中不重复值的数量与总行数的比值。区分度越高，筛选能力越强。例如，user_id 的区分度远高于 gender。将高区分度的字段放在前面，可以更快地缩小数据范围。
• 范围查询的字段放最后：如果查询中既有等值查询（=），又有范围查询（>、<、between），必须将范围查询的字段放在联合索引的最后。
  • 例如：where category_id = 10 and shop_price > 100。
  • 索引应建为：(category_id, shop_price)。如果反过来 (shop_price, category_id)，那么查询时只能用到 shop_price 的索引，category_id 将无法利用索引进行过滤。

如何验证索引是否生效？

不要靠猜测，一定要使用 explain 命令。在你的 select 语句前加上 explain，重点关注结果中的以下两列：

• key：显示实际被优化器选中使用的索引名称。如果为 null，则说明没有使用任何索引。
• type：显示表的连接类型，代表了查询的效率。
  • 高效：const, eq_ref, ref, range。
  • 低效（需优化）：index (全索引扫描), all (全表扫描)。

六、常见疑难问答

1.最左前缀原则只会出现在联合索引中吗？

是的，最左前缀原则是联合索引的专属特性。对于单列索引（只有一个字段的索引），数据库要么用，要么不用，不存在“用一半”的情况，所以谈不上“最左”前缀。

2.我可以在一张表中创建一个 index idx_form_task (form_id, task_id)，再创建一个 index idx_task_form (task_id, form_id) 吗？

技术上完全允许，但在实际开发中往往没必要，甚至属于资源浪费。

假设你创建了联合索引：index idx_form_task (form_id, task_id)：

• 查询 where form_id = ?：能用上索引（最左前缀）。
• 查询 where form_id = ? and task_id = ?：能用上索引（完整匹配）。
• 查询 where task_id = ?：用不上（跳过最左）。
  此时，如果你再创建一个 index idx_task_form (task_id, form_id)：
  它是专门为了优化 where task_id = ? 这种查询的。
  只有当你有大量 单独查询 task_id 的需求时，才需要建 (task_id, form_id)。
  如果你既有 where form_id = ? 的需求，又有 where task_id = ? 的需求。
• 错误做法：只建一个联合索引 (form_id, task_id)。（因为查 task_id 会失效）
• 正确做法：建两个单列索引，或者建一个联合索引 + 一个单列索引。
• 冗余做法：同时建 (form_id, task_id) 和 (task_id, form_id)。这通常浪费空间，除非你的查询经常涉及 order by form_id, task_id 和 order by task_id, form_id 这种截然不同的排序。

3.能既创建联合索引，又创建单个索引吗？

可以，但要注意冗余。

假设你创建了联合索引：index idx_union (a, b)

情况一：你再给 a 建单列索引

• 操作：index idx_union (a, b) + index idx_single (a)
• 结果：完全冗余，浪费资源！
• 原因：根据最左前缀原则，联合索引 (a, b) 已经包含了 a 的索引功能。数据库在查询 where a = ? 时，会直接使用联合索引。再单独给 a 建索引，就像是在字典里给“姓”排了一次序，又单独拿个小本本把“姓”排了一次序，纯属多此一举。
• 建议：删掉单列索引 idx_single (a)。

情况二：你再给 b 建单列索引

• 操作：index idx_union (a, b) + index idx_single (b)
• 结果：合理，且经常需要这样做。
• 原因：联合索引 (a, b) 无法优化 where b = ? 的查询。如果你经常需要根据 b 单独查询，就必须给 b 单独建一个索引。
• 建议：保留。

4.我创建了联合索引 index idx_form_task (form_id, task_id)，sql是 where task_id = '5' and form_id = '101'。这种有违反最左前缀原则吗？

完全没有违反，索引依然会生效。

这其实是数据库开发中一个非常经典的问题，很多新手都会误以为 sql 里的字段顺序必须和索引顺序一模一样。

核心结论：mysql 的查询优化器非常聪明，它会自动分析你的 sql 语句。无论你写成：

where task_id = '5' and form_id = '101'

还是：

where form_id = '101' and task_id = '5'

数据库在执行时，都会识别出这两个条件，并根据你建立的索引 index idx_form_task (form_id, task_id)，自动调整匹配顺序。它会先利用 form_id 定位，再利用 task_id 过滤。

所以，sql 语句中 where 条件的书写顺序，不影响索引的使用。

“最左前缀原则”里的顺序，指的是索引定义时的顺序以及查询条件是否包含最左边的列，而不是 sql 语句的书写顺序。

你的索引定义：(form_id, task_id)

你的查询：where task_id = '5' and form_id = '101'

分析：

explain select * from fc_form_data where task_id = '5' and form_id = '101';

在结果中，你会看到：

• 优化器看到了 form_id = '101'，发现它是索引的第一列（最左列），匹配成功。
• 优化器看到了 task_id = '5'，发现它是索引的第二列，匹配成功。
• 结果：两个字段都用上了索引，效率最高。

怎么验证？

• 你可以使用 explain 命令来查看执行计划：
• key 字段显示使用了 idx_form_task。
• key_len 字段显示的长度应该是两个字段长度之和（说明两个字段都参与了索引查找）。
  总结：只要你的 sql 语句中包含了联合索引的最左前缀字段（即 form_id），哪怕你把 task_id 写在前面，或者把 form_id 写在最后面，索引都能正常工作。

5.mysql可以对相同字段创建不同索引吗？

可以。mysql允许对同一个字段创建多个名称不同但结构相同的索引，但这是一种极其糟糕的实践，会造成严重的资源浪费。

例如，以下两条sql语句都可以成功执行：

alter table test add index idx_test02 using btree(updated);
alter table test add index idx_test03 using btree(updated);

从效果上看，这两个索引保留一个即可。因为它们只是名称不同，索引字段相同，实际上就是相同的索引。创建重复索引会：

• 浪费磁盘空间。
• 降低 insert、update、delete 等写入操作的性能，因为每次数据变动都需要更新多个相同的索引树。

因此，应严格避免创建重复索引。

6.不同表的索引命名相同会冲突吗？

不会。mysql对索引名称的作用域限制在单个表内。也就是说，同一数据库中不同表可以拥有相同名称的索引而不会产生冲突。

然而，同一张表内的索引名称必须唯一，不能重复。

尽管如此，在实际开发中，建议采用统一规范为索引命名，比如结合表名和字段名来定义索引名称。这样不仅有助于区分不同表的索引，还能提高代码可读性和维护性。例如，对于用户表user的id字段索引，可以命名为idx_user_id；订单表order的日期字段date索引则命名为idx_order_date。

七、拓展：索引底层原理深度剖析——为什么加了索引会更快？

我们在前面学会了如何创建索引、如何避免索引失效，但你是否思考过：为什么加了索引，查询速度就能从几秒甚至几分钟缩短到几毫秒？这背后到底发生了什么？

要理解这个问题，我们需要深入到mysql的存储引擎（以最常用的innodb为例）的底层，从数据结构和磁盘i/o两个维度来揭开索引的神秘面纱。

1. 索引的本质：空间换时间

索引的本质，其实就是一种数据结构。

如果把数据库表比作一本书，那么索引就是书的目录。

• 没有索引（全表扫描）：就像你要找书中关于“mysql原理”的内容，如果没有目录，你只能从第一页翻到最后一页，逐字逐句地找。数据量小的时候无所谓，如果书有几百万页（几千万行数据），这将是灾难性的。
• 有了索引：你可以直接查目录，找到对应的页码，翻过去就能找到内容。

在计算机领域，这是一种典型的**“空间换时间”**的策略。索引文件需要占用额外的磁盘空间来存储，但它能极大地减少查询时需要扫描的数据量，从而换取查询时间的缩短。

2. 为什么mysql选择b+树？（数据结构层面的降维打击）

mysql innodb引擎默认使用的索引结构是b+树。为什么不是数组、链表或者二叉树？这完全是为了适应磁盘存储的特性。

磁盘i/o的瓶颈：计算机的内存速度极快，但数据是持久化存储在磁盘上的。磁盘的读写速度（i/o）比内存慢几十万倍。因此，数据库性能优化的核心目标就是：尽量减少磁盘i/o的次数。

磁盘读取数据是按“页”（page）为单位的，innodb中默认一页的大小是16kb。每次i/o，至少读取一页。

二叉树的缺陷（树太高了）：

如果使用二叉树（每个节点最多两个分叉），数据量一大，树的高度就会变得非常高（瘦高型）。

查找一个数据，可能需要从根节点遍历到叶子节点，经过几十层。每一层节点如果不在内存中，就需要一次磁盘i/o。如果树高20，就需要20次i/o，这在数据库领域是不可接受的慢。

b+树的优势（矮胖子）：

b+树是一种多路平衡查找树。

• 多路：一个节点可以有非常多的分叉（innodb中一个节点可以存储上千个键值）。这意味着树非常“矮胖”。
• 高度极低：对于千万级数据的表，b+树的高度通常只有2到3层。这意味着，查找任意一条数据，最多只需要2到3次磁盘i/o。

3. b+树是如何实现的？（硬核原理解析）

让我们通过一个简单的计算，来看看b+树到底有多快。

假设：

• innodb页大小为16kb。
• 主键是bigint类型，占8字节。
• 指针占6字节。
• 那么一个键值对（key+pointer）大约14字节。

一个非叶子节点能存多少个键值？

16kb / 14b ≈ 1170个。

这意味着，b+树的一个节点（一页）可以指向1170个子节点。

b+树的存储能力：

• 树高为1：只能存一页数据（很少见）。
• 树高为2：根节点指向1170个叶子节点。假设每页存10行数据，能存 1170 * 10 ≈ 1万行。
• 树高为3：根节点 -> 1170个中间节点 -> 1170 * 1170个叶子节点。能存 1170 * 1170 * 10 ≈ 1300万行数据！

结论：几千万行数据的表，b+树高度仅为3。也就是说，无论数据量多大，innodb主键索引查询最多只需要3次磁盘i/o。这就是为什么加了索引会快的根本原因——它将o(n)的全表扫描复杂度降低到了o(log n)，且常数极小。

4. 聚簇索引与非聚簇索引（数据到底存在哪？）

在innodb中，索引不仅仅是“目录”，它和数据是绑定在一起的。

聚簇索引（主键索引）：b+树的叶子节点直接存储了整行数据。

当你通过主键查询时，一旦在b+树中定位到叶子节点，数据就已经拿到了，不需要再做任何操作。这就是为什么主键查询最快。

非聚簇索引（二级索引/普通索引）：我们在form_id上建立的索引就是二级索引。

它的b+树叶子节点不存整行数据，只存索引列的值和主键值。

5. 回表（table lookup）：为什么查主键最快？

这就引出了一个重要的概念——回表。

假设你执行了这样一条sql：

select * from fc_form_data where form_id = '101';

因为你查的是select *（所有字段），而form_id索引树上只有form_id和id（主键）。

1. 第一步：先在form_id的二级索引树中找到form_id = '101'的记录，拿到主键id（假设是1001）。
2. 第二步：拿着主键id = 1001，去**主键索引树（聚簇索引）**中再查找一遍，获取完整的行数据（如task_id, create_time等）。

这个过程叫回表。回表意味着多了一次b+树查询（多几次i/o）。

6. 覆盖索引（covering index）：高手的优化技巧

如果你执行的是：

select id, form_id from fc_form_data where form_id = '101';

你会发现，查询需要的字段id和form_id在form_id的索引树上全都有！这时候，mysql就不需要去查主键索引树了，直接返回索引树上的数据即可。

这就叫覆盖索引。覆盖索引避免了回表，是性能优化的重要手段。

总结

索引之所以快，是因为：

1. 使用了b+树数据结构，将树高控制在极低水平（2-3层），极大地减少了磁盘i/o次数。
2. 利用了数据的有序性，让数据库能像查字典一样通过二分查找快速定位，而不是逐行扫描。
3. 通过覆盖索引等机制，避免了额外的数据读取操作。

八、可视化图解：b+树结构与回表流程

为了更直观地理解索引的运作机制，我们使用 mermaid 流程图来展示 innodb 中 b+ 树的结构以及“回表”查询的全过程。

假设我们有一张表 user，包含字段 id (主键), name (普通索引), age。

1. b+树结构图解

innodb 中，主键索引（聚簇索引）的叶子节点存储整行数据，而普通索引（二级索引）的叶子节点只存储主键值。

图解说明：

1. 左侧（普通索引）：当我们执行 select * from user where name = 'b' 时，首先在 name 的索引树中找到记录。
2. 中间（获取主键）：在 name 索引的叶子节点中，我们只找到了主键值 id=5。
3. 右侧（回表）：拿着 id=5 回到主键索引树（聚簇索引）中查找，最终获取到完整的行数据（name, age 等）。这个过程就是回表。

2. 覆盖索引流程图

如果 sql 优化为 select id, name from user where name = 'b'，则不需要回表。

九、深度对比：b+树、b树与哈希索引

mysql 之所以选择 b+ 树作为默认索引结构，是因为它在磁盘 i/o、范围查询和稳定性之间取得了最佳平衡。以下是三种主流索引结构的详细对比。

核心总结：

1. b+ 树胜在“范围查询”与“i/o友好”：b+ 树的内部节点不存数据，这使得它比 b 树更“矮胖”，同样的磁盘页能容纳更多索引键，大大降低了树的高度，减少了磁盘 i/o 次数。同时，叶子节点的链表设计让它成为了范围查询（between, >, <）和排序（order by）的王者。
2. 哈希索引胜在“精准打击”：虽然哈希索引在 = 查询上速度无敌，但它是个“偏科生”。一旦遇到 >、< 或者 order by，它就完全失效。因此，innodb 仅在内存中提供“自适应哈希索引”来辅助热点数据的等值查询，而不会将其作为默认的持久化索引结构。
3. b 树的“中庸之道”：b 树虽然也能工作，但由于其内部节点存储数据导致树高较高，且范围查询效率不如 b+ 树，因此在现代关系型数据库中逐渐被 b+ 树取代。

以上就是一文带你搞懂mysql如何创建索引的详细内容，更多关于mysql创建索引的资料请关注代码网其它相关文章！