MySQL中如何使用索引

共计 16488 个字符，预计需要花费 42 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家介绍 MySQL 中如何使用索引，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

mysql 采用 b + 树的方式存储索引信息。

b+ 树结构如下：

说一下 b + 树的几个特点：

叶子节点（最下面的一层）存储关键字（索引字段的值）信息及对应的 data，叶子节点存储了所有记录的关键字信息

其他非叶子节点只存储关键字的信息及子节点的指针

每个叶子节点相当于 mysql 中的一页，同层级的叶子节点以双向链表的形式相连

每个节点（页）中存储了多条记录，记录之间用单链表的形式连接组成了一条有序的链表，顺序是按照索引字段排序的

b+ 树中检索数据时：每次检索都是从根节点开始，一直需要搜索到叶子节点

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读取一条记录的时候，并不是将这个记录本身从磁盘读取出来，而是以页为单位，将整个也加载到内存中，一个页中可能有很多记录，然后在内存中对页进行检索。在 innodb 中，每个页的大小默认是 16kb。

Mysql 中索引分为

聚集索引（主键索引）

每个表一定会有一个聚集索引，整个表的数据存储以 b + 树的方式存在文件中，b+ 树叶子节点中的 key 为主键值，data 为完整记录的信息；非叶子节点存储主键的值。

通过聚集索引检索数据只需要按照 b + 树的搜索过程，即可以检索到对应的记录。

非聚集索引

每个表可以有多个非聚集索引，b+ 树结构，叶子节点的 key 为索引字段字段的值，data 为主键的值；非叶子节点只存储索引字段的值。

通过非聚集索引检索记录的时候，需要 2 次操作，先在非聚集索引中检索出主键，然后再到聚集索引中检索出主键对应的记录，该过程比聚集索引多了一次操作。

索引怎么走，为什么有些查询不走索引？为什么使用函数了数据就不走索引了？

这些问题可以先放一下，我们先看一下 b + 树检索数据的过程，这个属于原理的部分，理解了 b + 树各种数据检索过程，上面的问题就都可以理解了。

通常说的这个查询走索引了是什么意思？

当我们对某个字段的值进行某种检索的时候，如果这个检索过程中，我们能够快速定位到目标数据所在的页，有效的降低页的 io 操作，而不需要去扫描所有的数据页的时候，我们认为这种情况能够有效的利用索引，也称这个检索可以走索引，如果这个过程中不能够确定数据在那些页中，我们认为这种情况下索引对这个查询是无效的，此查询不走索引。

b+ 树中数据检索过程

唯一记录检索

如上图，所有的数据都是唯一的，查询 105 的记录，过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

  将 P1 页加载到内存

  在内存中采用二分法查找，可以确定 105 位于 [100,150) 中间，所以我们需要去加载 100 关联 P4 页

  将 P4 加载到内存中，采用二分法找到 105 的记录后退出

查询某个值的所有记录

如上图，查询 105 的所有记录，过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

  将 P1 页加载到内存

  在内存中采用二分法查找，可以确定 105 位于 [100,150) 中间，100 关联 P4 页

  将 P4 加载到内存中，采用二分法找到最有一个小于 105 的记录，即 100，然后通过链表从 100 开始向后访问，找到所有的 105 记录，直到遇到第一个大于 100 的值为止

范围查找

数据如上图，查询 [55,150] 所有记录，由于页和页之间是双向链表升序结构，页内部的数据是单项升序链表结构，所以只用找到范围的起始值所在的位置，然后通过依靠链表访问两个位置之间所有的数据即可，过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

  将 P1 页加载到内存

  内存中采用二分法找到 55 位于 50 关联的 P3 页中，150 位于 P5 页中

  将 P3 加载到内存中，采用二分法找到第一个 55 的记录，然后通过链表结构继续向后访问 P3 中的 60、67，当 P3 访问完毕之后，通过 P3 的 nextpage 指针访问下一页 P4 中所有记录，继续遍历 P4 中的所有记录，直到访问到 P5 中所有的 150 为止。

模糊匹配

数据如上图。

查询以 `f` 开头的所有记录

过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

    将 P1 数据加载到内存中

    在 P1 页的记录中采用二分法找到最后一个小于等于 f 的值，这个值是 f，以及第一个大于 f 的，这个值是 z，f 指向叶节点 P3，z 指向叶节点 P6，此时可以断定以 f 开头的记录可能存在于 [P3,P6) 这个范围的页内，即 P3、P4、P5 这三个页中

    3.      加载 P3 这个页，在内部以二分法找到第一条 f 开头的记录，然后以链表方式继续向后访问 P4、P5 中的记录，即可以找到所有已 f 开头的数据

查询包含 `f` 的记录

包含的查询在 sql 中的写法是 %f%，通过索引我们还可以快速定位所在的页么？

可以看一下上面的数据，f 在每个页中都存在，我们通过 P1 页中的记录是无法判断包含 f 的记录在那些页的，只能通过 io 的方式加载所有叶子节点，并且遍历所有记录进行过滤，才可以找到包含 f 的记录。

所以如果使用了 % 值 % 这种方式，索引对查询是无效的。

最左匹配原则

当 b + 树的数据项是复合的数据结构，比如 (name,age,sex) 的时候，b+ 树是按照从左到右的顺序来建立搜索树的，比如当 (张三,20,F) 这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当 (张三,F) 这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了，这个是非常重要的性质，即索引的最左匹配特性。

来一些示例我们体验一下。

下图中是 3 个字段 (a,b,c) 的联合索引，索引中数据的顺序是以 a asc,b asc,c asc 这种排序方式存储在节点中的，索引先以 a 字段升序，如果 a 相同的时候，以 b 字段升序，b 相同的时候，以 c 字段升序，节点中每个数据认真看一下。

查询 a = 1 的记录

由于页中的记录是以 a asc,b asc,c asc 这种排序方式存储的，所以 a 字段是有序的，可以通过二分法快速检索到，过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

  将 P1 加载到内存中

  在内存中对 P1 中的记录采用二分法找，可以确定 a = 1 的记录位于 {1,1,1} 和{1,5,1}关联的范围内，这两个值子节点分别是 P2、P4

  加载叶子节点 P2，在 P2 中采用二分法快速找到第一条 a = 1 的记录，然后通过链表向下一条及下一页开始检索，直到在 P4 中找到第一个不满足 a = 1 的记录为止

查询 a =1 and b= 5 的记录

方法和上面的一样，可以确定 a =1 and b= 5 的记录位于 {1,1,1} 和{1,5,1}关联的范围内，查找过程和 a = 1 查找步骤类似。

查询 b = 1 的记录

这种情况通过 P1 页中的记录，是无法判断 b = 1 的记录在那些页中的，只能加锁索引树所有叶子节点，对所有记录进行遍历，然后进行过滤，此时索引是无效的。

按照 c 的值查询

这种情况和查询 b = 1 也一样，也只能扫描所有叶子节点，此时索引也无效了。

按照 b 和 c 一起查

这种也是无法利用索引的，也只能对所有数据进行扫描，一条条判断了，此时索引无效。

按照 [a,c] 两个字段查询

这种只能利用到索引中的 a 字段了，通过 a 确定索引范围，然后加载 a 关联的所有记录，再对 c 的值进行过滤。

查询 a =1 and b =0 and c= 1 的记录

这种情况只能先确定 a =1 and b = 0 所在页的范围，然后对这个范围的所有页进行遍历，c 字段在这个查询的过程中，是无法确定 c 的数据在哪些页的，此时我们称 c 是不走索引的，只有 a、b 能够有效的确定索引页的范围。

类似这种的还有、、between and，多字段索引的情况下，mysql 会一直向右匹配直到遇到范围查询 (、、between、like) 就停止匹配。

上面说的各种情况，大家都多看一下图中数据，认真分析一下查询的过程，基本上都可以理解了。

上面这种查询叫做最左匹配原则。

索引区分度

我们看 2 个有序数组

[1,2,3,4,5,6,7,8,8,9,10][1,1,1,1,1,8,8,8,8,8]

上面 2 个数组是有序的，都是 10 条记录，如果我需要检索值为 8 的所有记录，那个更快一些？

咱们使用二分法查找包含 8 的所有记录过程如下：先使用二分法找到最后一个小于 8 的记录，然后沿着这条记录向后获取下一个记录，和 8 对比，知道遇到第一个大于 8 的数字结束，或者到达数组末尾结束。

采用上面这种方法找到 8 的记录，第一个数组中更快的一些。因为第二个数组中含有 8 的比例更多的，需要访问以及匹配的次数更多一些。

这里就涉及到数据的区分度问题：

索引区分度 = count(distint 记录) / count(记录)。

当索引区分度高的时候，检索数据更快一些，索引区分度太低，说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。

当索引区分度非常小的时候，基本上接近于全索引数据的扫描了，此时查询速度是比较慢的。

第一个数组索引区分度为 1，第二个区分度为 0.2，所以第一个检索更快的一些。

所以我们创建索引的时候，尽量选择区分度高的列作为索引。

正确使用索引

准备 400 万测试数据

/* 建库 javacode2018*/DROP DATABASE IF EXISTS javacode2018;CREATE DATABASE javacode2018;USE javacode2018;/* 建表 test1*/DROP TABLE IF EXISTS test1;CREATE TABLE test1 ( id INT NOT NULL COMMENT  编号 , name VARCHAR(20) NOT NULL COMMENT  姓名 , sex TINYINT NOT NULL COMMENT  性别,1：男，2：女 , email VARCHAR(50));/* 准备数据 */DROP PROCEDURE IF EXISTS proc1;DELIMITER $CREATE PROCEDURE proc1() BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i  = 4000000 DO INSERT INTO test1 (id, name, sex, email) VALUES (i,concat( javacode ,i),if(mod(i,2),1,2),concat(javacode ,i, @163.com  SET ii = i + 1; if i%10000=0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; COMMIT; END $ DELIMITER ; CALL proc1();

上面插入的 400 万数据，除了 sex 列，其他列的值都是没有重复的。

无索引检索效果

400 万数据，我们随便查询几个记录看一下效果。

按照 id 查询记录

mysql  select * from test1 where id = 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@163.com | +----+-----------+-----+-------------------+ 1 row in set (1.91 sec)

id= 1 的数据，表中只有一行，耗时近 2 秒，由于 id 列无索引，只能对 400 万数据进行全表扫描。

主键检索

test1 表中没有明确的指定主键，我们将 id 设置为主键：

mysql  alter table test1 modify id int not null primary key; Query OK, 0 rows affected (10.93 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql  show index from test1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test1 | 0 | PRIMARY | 1 | id | A | 3980477 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)

id 被置为主键之后，会在 id 上建立聚集索引，随便检索一条我们看一下效果：

mysql  select * from test1 where id = 1000000; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 1000000 | javacode1000000 | 2 | javacode1000000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

这个速度很快，这个走的是上面介绍的 ` 唯一记录检索 `。

between and 范围检索

mysql  select count(*) from test1 where id between 100 and 110; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec)

速度也很快，id 上有主键索引，这个采用的上面介绍的范围查找可以快速定位目标数据。

但是如果范围太大，跨度的 page 也太多，速度也会比较慢，如下：

mysql  select count(*) from test1 where id between 1 and 2000000; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1.17 sec)

上面 id 的值跨度太大，1 所在的页和 200 万所在页中间有很多页需要读取，所以比较慢。

所以使用 between and 的时候，区间跨度不要太大。

in 的检索

in 方式检索数据，我们还是经常用的。

平时我们做项目的时候，建议少用表连接，比如电商中需要查询订单的信息和订单中商品的名称，可以先查询查询订单表，然后订单表中取出商品的 id 列表，采用 in 的方式到商品表检索商品信息，由于商品 id 是商品表的主键，所以检索速度还是比较快的。

通过 id 在 400 万数据中检索 100 条数据，看看效果：

mysql  select * from test1 a where a.id in (100000, 100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008, 100009, 100010, 100011, 100012, 100013, 100014, 100015, 100016, 100017, 100018, 100019, 100020, 100021, 100022, 100023, 100024, 100025, 100026, 100027, 100028, 100029, 100030, 100031, 100032, 100033, 100034, 100035, 100036, 100037, 100038, 100039, 100040, 100041, 100042, 100043, 100044, 100045, 100046, 100047, 100048, 100049, 100050, 100051, 100052, 100053, 100054, 100055, 100056, 100057, 100058, 100059, 100060, 100061, 100062, 100063, 100064, 100065, 100066, 100067, 100068, 100069, 100070, 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099); +--------+----------------+-----+------------------------+ | id | name | sex | email | +--------+----------------+-----+------------------------+ | 100000 | javacode100000 | 2 | javacode100000@163.com | | 100001 | javacode100001 | 1 | javacode100001@163.com | | 100002 | javacode100002 | 2 | javacode100002@163.com | ....... | 100099 | javacode100099 | 1 | javacode100099@163.com | +--------+----------------+-----+------------------------+ 100 rows in set (0.00 sec)

耗时不到 1 毫秒，还是相当快的。

这个相当于多个分解为多个唯一记录检索，然后将记录合并。

多个索引时查询如何走？

我们在 name、sex 两个字段上分别建个索引

mysql  create index idx1 on test1(name); Query OK, 0 rows affected (13.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql  create index idx2 on test1(sex); Query OK, 0 rows affected (6.77 sec) Records: 0 Duplicates: 0 Warnings: 0

看一下查询：

mysql  select * from test1 where name= javacode3500000  and sex=2; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

上面查询速度很快，name 和 sex 上各有一个索引，觉得上面走哪个索引？

有人说 name 位于 where 第一个，所以走的是 name 字段所在的索引，过程可以解释为这样：

走 name 所在的索引找到 javacode3500000 对应的所有记录

遍历记录过滤出 sex= 2 的值

我们看一下 name= javacode3500000 检索速度，确实很快，如下：

mysql  select * from test1 where name= javacode3500000  +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

走 name 索引，然后再过滤，确实可以，速度也很快，果真和 where 后字段顺序有关么？我们把 name 和 sex 的顺序对调一下，如下：

mysql  select * from test1 where sex=2 and name= javacode3500000  +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

速度还是很快，这次是不是先走 sex 索引检索出数据，然后再过滤 name 呢？我们先来看一下 sex= 2 查询速度：

mysql  select count(id) from test1 where sex=2; +-----------+ | count(id) | +-----------+ | 2000000 | +-----------+ 1 row in set (0.36 sec)

看上面，查询耗时 360 毫秒，200 万数据，如果走 sex 肯定是不行的。

我们使用 explain 来看一下：

mysql  explain select * from test1 where sex=2 and name= javacode3500000  +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ref | idx1,idx2 | idx1 | 62 | const | 1 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

possible_keys：列出了这个查询可能会走两个索引（idx1、idx2）

实际上走的却是 idx1（key 列：实际走的索引）。

当多个条件中有索引的时候，并且关系是 and 的时候，会走索引区分度高的，显然 name 字段重复度很低，走 name 查询会更快一些。

模糊查询

看两个查询

mysql  select count(*) from test1 a where a.name like  javacode1000%  +----------+ | count(*) | +----------+ | 1111 | +----------+ 1 row in set (0.00 sec) mysql  select count(*) from test1 a where a.name like  %javacode1000%  +----------+ | count(*) | +----------+ | 1111 | +----------+ 1 row in set (1.78 sec)

上面第一个查询可以利用到 name 字段上面的索引，下面的查询是无法确定需要查找的值所在的范围的，只能全表扫描，无法利用索引，所以速度比较慢，这个过程上面有说过。

回表

当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这个过程叫做回表，如查询：

mysql  select * from test1 where name= javacode3500000  +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

上面查询是 *，由于 name 列所在的索引中只有 name、id 两个列的值，不包含 sex、email，所以上面过程如下：

走 name 索引检索 javacode3500000 对应的记录，取出 id 为 3500000

在主键索引中检索出 id=3500000 的记录，获取所有字段的值

索引覆盖

查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖。

我们来看一个查询：

select id,name from test1 where name= javacode3500000

name 对应 idx1 索引，id 为主键，所以 idx1 索引树叶子节点中包含了 name、id 的值，这个查询只用走 idx1 这一个索引就可以了，如果 select 后面使用 *，还需要一次回表获取 sex、email 的值。

所以写 sql 的时候，尽量避免使用 *，* 可能会多一次回表操作，需要看一下是否可以使用索引覆盖来实现，效率更高一些。

索引下推

简称 ICP，Index Condition Pushdown(ICP)是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式，ICP 可以减少存储引擎访问基表的次数以及 MySQL 服务器访问存储引擎的次数。

举个例子来说一下：

我们需要查询 name 以 javacode35 开头的，性别为 1 的记录数，sql 如下：

mysql  select count(id) from test1 a where name like  javacode35%  and sex = 1; +-----------+ | count(id) | +-----------+ | 55556 | +-----------+ 1 row in set (0.19 sec)

过程：

走 name 索引检索出以 javacode35 的第一条记录，得到记录的 id

利用 id 去主键索引中查询出这条记录 R1

判断 R1 中的 sex 是否为 1，然后重复上面的操作，直到找到所有记录为止。

上面的过程中需要走 name 索引以及需要回表操作。

如果采用 ICP 的方式，我们可以这么做，创建一个 (name,sex) 的组合索引，查询过程如下：

走 (name,sex) 索引检索出以 javacode35 的第一条记录，可以得到(name,sex,id)，记做 R1

判断 R1.sex 是否为 1，然后重复上面的操作，知道找到所有记录为止

这个过程中不需要回表操作了，通过索引的数据就可以完成整个条件的过滤，速度比上面的更快一些。

数字使字符串类索引失效

mysql  insert into test1 (id,name,sex,email) values (4000001, 1 ,1, javacode2018@163.com  Query OK, 1 row affected (0.00 sec) mysql  select * from test1 where name =  1  +---------+------+-----+----------------------+ | id | name | sex | email | +---------+------+-----+----------------------+ | 4000001 | 1 | 1 | javacode2018@163.com | +---------+------+-----+----------------------+ 1 row in set (0.00 sec) mysql  select * from test1 where name = 1; +---------+------+-----+----------------------+ | id | name | sex | email | +---------+------+-----+----------------------+ | 4000001 | 1 | 1 | javacode2018@163.com | +---------+------+-----+----------------------+ 1 row in set, 65535 warnings (3.30 sec)

上面 3 条 sql，我们插入了一条记录。

第二条查询很快，第三条用 name 和 1 比较，name 上有索引，name 是字符串类型，字符串和数字比较的时候，会将字符串强制转换为数字，然后进行比较，所以第二个查询变成了全表扫描，只能取出每条数据，将 name 转换为数字和 1 进行比较。

数字字段和字符串比较什么效果呢？如下：

mysql  select * from test1 where id =  4000000  +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 4000000 | javacode4000000 | 2 | javacode4000000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec) mysql  select * from test1 where id = 4000000; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 4000000 | javacode4000000 | 2 | javacode4000000@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)

id 上面有主键索引，id 是 int 类型的，可以看到，上面两个查询都非常快，都可以正常利用索引快速检索，所以如果字段是数组类型的，查询的值是字符串还是数组都会走索引。

函数使索引无效

mysql  select a.name+1 from test1 a where a.name =  javacode1  +----------+ | a.name+1 | +----------+ | 1 | +----------+ 1 row in set, 1 warning (0.00 sec) mysql  select * from test1 a where concat(a.name, 1) =  javacode11  +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@163.com | +----+-----------+-----+-------------------+ 1 row in set (2.88 sec)

name 上有索引，上面查询，第一个走索引，第二个不走索引，第二个使用了函数之后，name 所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据使用函数进行计算之后再进行条件判断，此时索引无效了，变成了全表数据扫描。

结论：索引字段使用函数查询使索引无效。

运算符使索引无效

mysql  select * from test1 a where id = 2 - 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@163.com | +----+-----------+-----+-------------------+ 1 row in set (0.00 sec) mysql  select * from test1 a where id+1 = 2; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@163.com | +----+-----------+-----+-------------------+ 1 row in set (2.41 sec)

id 上有主键索引，上面查询，第一个走索引，第二个不走索引，第二个使用运算符，id 所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据的 id 进行计算之后再判断是否等于 1，此时索引无效了，变成了全表数据扫描。

结论：索引字段使用了函数将使索引无效。

使用索引优化排序

我们有个订单表 t_order(id,user_id,addtime,price)，经常会查询某个用户的订单，并且按照 addtime 升序排序，应该怎么创建索引呢？我们来分析一下。

在 user_id 上创建索引，我们分析一下这种情况，数据检索的过程：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

  走 user_id 索引，找到记录的的 id

  通过 id 在主键索引中回表检索出整条数据

  重复上面的操作，获取所有目标记录

  在内存中对目标记录按照 addtime 进行排序

我们要知道当数据量非常大的时候，排序还是比较慢的，可能会用到磁盘中的文件，有没有一种方式，查询出来的数据刚好是排好序的。

我们再回顾一下 mysql 中 b + 树数据的结构，记录是按照索引的值排序组成的链表，如果将 user_id 和 addtime 放在一起组成联合索引(user_id,addtime)，这样通过 user_id 检索出来的数据自然就是按照 addtime 排好序的，这样直接少了一步排序操作，效率更好，如果需 addtime 降序，只需要将结果翻转一下就可以了。

总结一下使用索引的一些建议

鸿蒙官方战略合作共建——HarmonyOS 技术社区

在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引差不多

联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，mysql 会一直向右匹配直到遇到范围查询 (、、between、like) 就停止匹配，比如 a = 1 and b = 2 and c 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整

    3. 查询记录的时候，少使用 *，尽量去利用索引覆盖，可以减少回表操作，提升效率

    4. 有些查询可以采用联合索引，进而使用到索引下推（IPC），也可以减少回表操作，提升效率

    5. 禁止对索引字段使用函数、运算符操作，会使索引失效

    6. 字符串字段和数字比较的时候会使索引无效

    7. 模糊查询 % 值 % 会使索引无效，变为全表扫描，但是 值 % 这种可以有效利用索引

    8. 排序中尽量使用到索引字段，这样可以减少排序，提升查询效率

关于 MySQL 中如何使用索引就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

向 AI 问一下细节