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

Mysql系列的目标是：通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是Mysql系列第24篇。

学习索引，主要是写出更快的sql，当我们写sql的时候，需要明确的知道sql为什么会走索引？为什么有些sql不走索引？sql会走那些索引，为什么会这么走？我们需要了解其原理，了解内部具体过程，这样使用起来才能更顺手，才可以写出更高效的sql。本篇我们就是搞懂这些问题。

读本篇文章之前，需要先了解一些知识：

  1. 什么是索引？

  2. mysql索引原理详解

  3. mysql索引管理详解

上面3篇文章没有读过的最好去读一下，不然后面的内容会难以理解。

先来回顾一些知识

本篇文章我们以innodb存储引擎为例来做说明。

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

b+树结构如下：

640?wx_fmt=png

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

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

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

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

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

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

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

Mysql中索引分为

聚集索引（主键索引）

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

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

非聚集索引

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

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

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

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

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

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

b+树中数据检索过程

唯一记录检索

640?wx_fmt=png

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

  1. 将P1页加载到内存

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

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

查询某个值的所有记录

640?wx_fmt=png

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

  1. 将P1页加载到内存

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

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

范围查找

640?wx_fmt=png

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

  1. 将P1页加载到内存

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

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

模糊匹配

640?wx_fmt=png

数据如上图。

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

过程如下：

  1. 将P1数据加载到内存中

  2. 在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字段升序，节点中每个数据认真看一下。

640?wx_fmt=png

查询a=1的记录

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

  1. 将P1加载到内存中

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

  3. 加载叶子节点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万测试数据

  1. /*建库javacode2018*/
  2. DROP DATABASE IF EXISTS javacode2018;
  3. CREATE DATABASE javacode2018;
  4. USE javacode2018;
  5. /*建表test1*/
  6. DROP TABLE IF EXISTS test1;
  7. CREATE TABLE test1 (
  8.   id     INT NOT NULL COMMENT '编号',
  9.   name   VARCHAR(20) NOT NULL COMMENT '姓名',
  10.   sex TINYINT NOT NULL COMMENT '性别,1：男，2：女',
  11.   email  VARCHAR(50)
  12. );
  13.  
  14. /*准备数据*/
  15. DROP PROCEDURE IF EXISTS proc1;
  16. DELIMITER $
  17. CREATE PROCEDURE proc1()
  18.   BEGIN
  19.     DECLARE i INT DEFAULT 1;
  20.     START TRANSACTION;
  21.     WHILE i <= 4000000 DO
  22.       INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
  23.       SET i = i + 1;
  24.       if i%10000=0 THEN
  25.         COMMIT;
  26.         START TRANSACTION;
  27.       END IF;
  28.     END WHILE;
  29.     COMMIT;
  30.   END $
  31.  
  32. DELIMITER ;
  33. CALL proc1();

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

无索引检索效果

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

按照id查询记录

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

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

主键检索

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

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

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

  1. mysql> select * from test1 where id = 1000000;
  2. +---------+-----------------+-----+-------------------------+
  3. | id      | name            | sex | email                   |
  4. +---------+-----------------+-----+-------------------------+
  5. | 1000000 | javacode1000000 |   2 | javacode1000000@163.com |
  6. +---------+-----------------+-----+-------------------------+
  7. 1 row in set (0.00 sec)
这个速度很快，这个走的是上面介绍的`唯一记录检索`。

between and范围检索

  1. mysql> select count(*) from test1 where id between 100 and 110;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |       11 |
  6. +----------+
  7. 1 row in set (0.00 sec)

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

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

  1. mysql> select count(*) from test1 where id between 1 and 2000000;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |  2000000 |
  6. +----------+
  7. 1 row in set (1.17 sec)

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

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

in的检索

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

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

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

  1. 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);
  2. +--------+----------------+-----+------------------------+
  3. | id     | name           | sex | email                  |
  4. +--------+----------------+-----+------------------------+
  5. | 100000 | javacode100000 |   2 | javacode100000@163.com |
  6. | 100001 | javacode100001 |   1 | javacode100001@163.com |
  7. | 100002 | javacode100002 |   2 | javacode100002@163.com |
  8. .......
  9. | 100099 | javacode100099 |   1 | javacode100099@163.com |
  10. +--------+----------------+-----+------------------------+
  11. 100 rows in set (0.00 sec)

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

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

多个索引时查询如何走？

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

  1. mysql> create index idx1 on test1(name);
  2. Query OK, 0 rows affected (13.50 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> create index idx2 on test1(sex);
  6. Query OK, 0 rows affected (6.77 sec)
  7. Records: 0  Duplicates: 0  Warnings: 0

看一下查询：

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

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

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

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

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

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

  1. mysql> select * from test1 where name='javacode3500000';
  2. +---------+-----------------+-----+-------------------------+
  3. | id      | name            | sex | email                   |
  4. +---------+-----------------+-----+-------------------------+
  5. | 3500000 | javacode3500000 |   2 | javacode3500000@163.com |
  6. +---------+-----------------+-----+-------------------------+
  7. 1 row in set (0.00 sec)

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

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

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

  1. mysql> select count(id) from test1 where sex=2;
  2. +-----------+
  3. | count(id) |
  4. +-----------+
  5. |   2000000 |
  6. +-----------+
  7. 1 row in set (0.36 sec)

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

我们使用explain来看一下：

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

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

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

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

模糊查询

看两个查询

  1. mysql> select count(*) from test1 a where a.name like 'javacode1000%';
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |     1111 |
  6. +----------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select count(*) from test1 a where a.name like '%javacode1000%';
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. |     1111 |
  14. +----------+
  15. 1 row in set (1.78 sec)

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

回表

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

  1. mysql> select * from test1 where name='javacode3500000';
  2. +---------+-----------------+-----+-------------------------+
  3. | id      | name            | sex | email                   |
  4. +---------+-----------------+-----+-------------------------+
  5. | 3500000 | javacode3500000 |   2 | javacode3500000@163.com |
  6. +---------+-----------------+-----+-------------------------+
  7. 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如下：

  1. mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;
  2. +-----------+
  3. | count(id) |
  4. +-----------+
  5. |     55556 |
  6. +-----------+
  7. 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，然后重复上面的操作，知道找到所有记录为止

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

数字使字符串类索引失效

  1. mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'javacode2018@163.com');
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> select * from test1 where name = '1';
  5. +---------+------+-----+----------------------+
  6. | id      | name | sex | email                |
  7. +---------+------+-----+----------------------+
  8. | 4000001 | 1    |   1 | javacode2018@163.com |
  9. +---------+------+-----+----------------------+
  10. 1 row in set (0.00 sec)
  11.  
  12. mysql> select * from test1 where name = 1;
  13. +---------+------+-----+----------------------+
  14. | id      | name | sex | email                |
  15. +---------+------+-----+----------------------+
  16. | 4000001 | 1    |   1 | javacode2018@163.com |
  17. +---------+------+-----+----------------------+
  18. 1 row in set, 65535 warnings (3.30 sec)

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

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

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

  1. mysql> select * from test1 where id = '4000000';
  2. +---------+-----------------+-----+-------------------------+
  3. | id      | name            | sex | email                   |
  4. +---------+-----------------+-----+-------------------------+
  5. | 4000000 | javacode4000000 |   2 | javacode4000000@163.com |
  6. +---------+-----------------+-----+-------------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select * from test1 where id = 4000000;
  10. +---------+-----------------+-----+-------------------------+
  11. | id      | name            | sex | email                   |
  12. +---------+-----------------+-----+-------------------------+
  13. | 4000000 | javacode4000000 |   2 | javacode4000000@163.com |
  14. +---------+-----------------+-----+-------------------------+
  15. 1 row in set (0.00 sec)

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

函数使索引无效

  1. mysql> select a.name+1 from test1 a where a.name = 'javacode1';
  2. +----------+
  3. | a.name+1 |
  4. +----------+
  5. |        1 |
  6. +----------+
  7. 1 row in set, 1 warning (0.00 sec)
  8.  
  9. mysql> select * from test1 a where concat(a.name,'1') = 'javacode11';
  10. +----+-----------+-----+-------------------+
  11. | id | name      | sex | email             |
  12. +----+-----------+-----+-------------------+
  13. |  1 | javacode1 |   1 | javacode1@163.com |
  14. +----+-----------+-----+-------------------+
  15. 1 row in set (2.88 sec)

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

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

运算符使索引无效

  1. mysql> select * from test1 a where id = 2 - 1;
  2. +----+-----------+-----+-------------------+
  3. | id | name      | sex | email             |
  4. +----+-----------+-----+-------------------+
  5. |  1 | javacode1 |   1 | javacode1@163.com |
  6. +----+-----------+-----+-------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select * from test1 a where id+1 = 2;
  10. +----+-----------+-----+-------------------+
  11. | id | name      | sex | email             |
  12. +----+-----------+-----+-------------------+
  13. |  1 | javacode1 |   1 | javacode1@163.com |
  14. +----+-----------+-----+-------------------+
  15. 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系列目录

  1. 第1篇：mysql基础知识

  2. 第2篇：详解mysql数据类型（重点）

  3. 第3篇：管理员必备技能(必须掌握)

  4. 第4篇：DDL常见操作

  5. 第5篇：DML操作汇总（insert,update,delete）

  6. 第6篇：select查询基础篇

  7. 第7篇：玩转select条件查询，避免采坑

  8. 第8篇：详解排序和分页(order by & limit)

  9. 第9篇：分组查询详解（group by & having）

  10. 第10篇：常用的几十个函数详解

  11. 第11篇：深入了解连接查询及原理

  12. 第12篇：子查询

  13. 第13篇：细说NULL导致的神坑，让人防不胜防

  14. 第14篇：详解事务

  15. 第15篇：详解视图

  16. 第16篇：变量详解

  17. 第17篇：存储过程&自定义函数详解

  18. 第18篇：流程控制语句

  19. 第19篇：游标详解

  20. 第20篇：异常捕获及处理详解

  21. 第21篇：什么是索引？

  22. 第22篇：mysql索引原理详解

  23. 第23篇：mysql索引管理详解

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

路人甲java

640?wx_fmt=png

▲长按图片识别二维码关注

路人甲java：工作10年的前阿里P7分享Java、算法、数据库方面的技术干货！坚信用技术改变命运，让家人过上更体面的生活！

来源：https://itsoku.blog.csdn.net/article/details/102597624

posted @ 2022-04-23 23:20  程序员小明1024  阅读(218)  评论(0)  编辑  收藏  举报