玩转Mysql系列 - 第24篇：如何正确的使用索引？

[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 i = 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字段所在的索引，过程可以解释为这样：

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

2. 遍历记录过滤出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，所以上面过程如下：

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

2. 在主键索引中检索出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)

过程：

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

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

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

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

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

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

2. 判断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上创建索引，我们分析一下这种情况，数据检索的过程：

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

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

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

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

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

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

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

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

2. 联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，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系列目录

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

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

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

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后总结

ActiveMQ+Kafka+RabbitMQ学习笔记PDF

• RabbitMQ实战指南

• 手写RocketMQ笔记

• 手写“Kafka笔记”

关于分布式，限流+缓存+缓存，这三大技术（包含：ZooKeeper+Nginx+MongoDB+memcached+Redis+ActiveMQ+Kafka+RabbitMQ）等等。这些相关的面试也好，还有手写以及学习的笔记PDF，都是啃透分布式技术必不可少的宝藏。以上的每一个专题每一个小分类都有相关的介绍，并且小编也已经将其整理成PDF啦
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。**[外链图片转存中…(img-Wpsl30jQ-1713756468699)]

[外链图片转存中…(img-OTRiUP91-1713756468700)]

[外链图片转存中…(img-QC7eOMzo-1713756468700)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

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

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后总结

ActiveMQ+Kafka+RabbitMQ学习笔记PDF

[外链图片转存中…(img-dw4P3JqL-1713756468700)]

• RabbitMQ实战指南

[外链图片转存中…(img-D1CQz3cy-1713756468701)]

• 手写RocketMQ笔记

[外链图片转存中…(img-RmZkiF9Z-1713756468701)]

• 手写“Kafka笔记”

[外链图片转存中…(img-sl0iWAZB-1713756468701)]

关于分布式，限流+缓存+缓存，这三大技术（包含：ZooKeeper+Nginx+MongoDB+memcached+Redis+ActiveMQ+Kafka+RabbitMQ）等等。这些相关的面试也好，还有手写以及学习的笔记PDF，都是啃透分布式技术必不可少的宝藏。以上的每一个专题每一个小分类都有相关的介绍，并且小编也已经将其整理成PDF啦
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！