MySQL索引——如何提高查询速度

alter table 表名 ADD {INDEX | KEY} 索引名（字段）

复制代码

注意：给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引

单字段索引的作用原理

要知道索引是怎么起作用的，我们需要借助 MySQL 中的 EXPLAIN 这个关键字

EXPLAIN 关键字能够查看 SQL 语句的执行细节，包括表的加载顺序，表是如何连接的，以及索引使用情况等

首先我们有这样一张表和数据

itemnumber barcode goodsname price

1 0001 书 0.47

2 0002 笔 0.44

3 0002 胶水 0.19

复制代码

其中itemnumber是自增主键，barcode是编号，goodsname是商品名称，price是商品价格

然后我们尝试使用范围搜索

EXPLAIN select * from goodsmaster where itemnumber > ‘1’ and itemnumber < ‘4’ and goodsname = ‘书’

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 33.33 | Using where |

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

1 row in set, 1 warning (0.00 sec)

复制代码

• type=range：表示使用索引查询特定范围的数据记录

• rows=2：表示需要读取的记录数（也就是itemnumber为2和3的记录）

• possible_keys=PRIMARY：表示可选的是主键索引

• key=PRIMARY：确实使用的索引是主键索引

• extra=using where：对sql语句的执行细节做了进一步的解释

通过这个例子我们可以发现，有了索引之后，可以先通过查询索引快速定位，然后再找到对应的数据进行读取，这样就大大提高了查询的速度

如何选择索引字段

在上面的查询中，我们是选择主键字段当作索引字段，你可能会问，为什么不选择其他的字段作为索引字段呢？这是因为，itemnumber是作为了一个查询条件，当然我们上面还是用了goodsname作为了查询条件，所以我们也可以给goodsname加上索引

但是建议你在选择索引字段的时候，要选择那些经常被用做筛选条件的字段。这样才能发挥索引的作用，提升检索的效率

如何创建组合索引（复合索引）

---

直接给数据表创建索引的语法：

create INDEX 索引名 ON TABLE 表名 （字段1，字段2，…）

复制代码

创建表的同时创建索引：

create table 表名

(

字段 数据类型，

…,

{INDEX | KEY} 索引名（字段1，字段2，…）

)

复制代码

修改表时创建索引：

ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段1，字段2，…)

复制代码

还是使用上面的表为例

假设我们给goodsname和barcode都加上索引，我们需要查找的是goodsname是书，而且编码是0001的记录，有了组合索引之后，查询的速度会明显提升

索引字段

名称 字段 索引类型 索引方法

test goodsname, barcode UNIQUE BTREE

EXPLAIN select * from goodsmaster where barcode=‘0001’ and goodsname = ‘书’;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | const | test | test | 806 | const,const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

EXPLAIN select * from goodsmaster where barcode=‘0001’ and goodsname = ‘书’ and itemnumber=1;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | const | PRIMARY,test | PRIMARY | 4 | const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

复制代码

可以看出来可以作为备选的索引有一个名叫test，然后选择了test作为索引。第二个我们在where条件中加入了主键itemnumber的过滤条件，所以会呈现出多个备选索引，如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL 会选择使用最优的索引来执行查询操作

但是，当我们使用上面的联合索引的时候，也能很大程度上优化查询速度

组合索引的原理

组合索引的多个字段是有序的，遵循左对齐的原则。比如我们创建的组合索引，排序的方式是goodsname和barcode，所以筛选的条件也要遵循从左往右的原则，如果终端，那么后面的条件就无法利用索引

EXPLAIN select * from goodsmaster where barcode=‘0001’;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

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

1 row in set, 1 warning (0.00 sec)

EXPLAIN select * from goodsmaster where goodsname = ‘书’;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | ref | test | test | 403 | const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

复制代码

比如上述我们只是用了barcode一个字段，可见并没有使用联合索引，再看第二个搜索结果是goodsname，符合最左原则，就能够使用联合索引。

如果其中有一个范围，假设我们给price也加入联合索引中，并且将price放置在最左侧，如果你的查询语句变成了这样

EXPLAIN select * from goodsmaster where price > 1000 and goodsname = ‘书’ and barcode=‘0001’;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | goodsmaster | NULL | range | test | test | 4 | NULL | 1 | 33.33 | Using where; Using index |

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

1 row in set, 1 warning (0.00 sec)

复制代码

如果你的price>1000并没有找到满足的内容，那么也会中断，后面的索引也无法使用

删除索引

---

最后

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

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

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

[外链图片转存中…(img-BZ9M6si3-1715546654324)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

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