mysql索引访问方式实践 （const、ref、range、index、all、index merge）

如果你对mysql索引访问方式还一知半解，请继续看下去，相信本文会对你有帮助：
本文会讲解：

• mysql索引的实现方式
• mysql有哪些索引访问方式

mysql索引的实现方式

Mysql索引的数据结构采用的是B+树，B+树是一棵多路搜索树。类似这样。

在Innodb存储引擎中，又可分为聚簇索引（主键索引）与非聚簇索引，聚簇索引的非叶子结点记录的是索引，叶子结点记录了对应的数据行。
非聚簇索引与聚簇索引不一样的是，它的叶子结点记录的是普通索引列与主键的对应关系。
而在Myisam存储引擎中，就全是非聚簇索引了，索引和数据是分开存储的。

mysql有哪些索引访问方式

我们先创建一个测试表：

create table test_index
(
    id           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    key1         VARCHAR(100),
    key2         INT,
    key3         VARCHAR(100),
    key_part1    VARCHAR(100),
    key_part2    VARCHAR(100),
    key_part3    VARCHAR(100),
    common_field VARCHAR(100),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part (key_part1, key_part2, key_part3)
) Engine = InnoDB CHARSET = utf8mb4;

写一个存储过程往表里插入10w条数据：

DROP PROCEDURE IF EXISTS insert_into_test_index;
DELIMITER //
CREATE PROCEDURE insert_into_test_index()
BEGIN
    SET @i := 1;
    WHILE @i <= 100000 DO
    SET @key1 := concat('key1', @i);
    SET @key2 := FLOOR(@i); -- floor函数（如FLOOR(X)）返回小于等于X的最大整数
    SET @key3 := concat('key1', @i);
    SET @key_part1 := concat('key_part1', @i);
    SET @key_part2 := concat('key_part2', @i);
    SET @key_part3 := concat('key_part3', @i);
    SET @common_field := concat('common_field', @i);

    INSERT INTO test_index VALUES (@i, @key1, @key2, @key3, @key_part1, @key_part2, @key_part3, @common_field);
    SET @i := @i + 1;
    if @i % 100 = 0 then
        COMMIT;
    end if ;
    END WHILE;
END //
CALL insert_into_test_index();

看一下表的统计信息，没什么问题：

show table status like '%test%';

索引访问方式

const： 根据主键、普通唯一索引列等值匹配查询（is null除外），这种查询是很快的，查询速率认为是常数级别的，定义为const。

如：

-- 根据主键等值查询
explain select * from test_index where id = 2;

-- 根据普通唯一索引等值查询
explain select * from test_index where key2 = 10;

ref ： 根据普通索引等值匹配，或is null。（前面说的普通唯一索引列查询时 is null也是这种场景）。这种方式需要先根据普通索引匹配到多个主键，然后根据主键进行回表。

-- 根据普通唯一索引匹配 is null
explain select * from test_index where key2 is null;

查看执行计划中的type是ref

-- 根据普通索引等值匹配
explain select * from test_index where key1 = 'key112';

range：根据主键索引或普通索引（包含唯一索引）进行范围查找

-- 根据主键索引进行范围查询
explain select * from test_index where id < 100;

-- 根据普通唯一索引进行范围查找
explain select * from test_index where key2 < 20 and key2 > 10;

index：索引覆盖，你查询的列刚好是索引列，即使查询条件是联合索引的非最左索引列，查询的条件是联合索引中的列，也可能会走索引覆盖

-- 根据联合索引的最左列查询索引列的值
explain select key_part1,key_part2,key_part3 from test_index where key_part1 = 'key_part112';

我们看到，据联合索引的最左列查询索引列的值，走的是ref形式，是using index的，这种查询也是索引覆盖的。
如果改成查询所有列：explain select * from test_index where key_part1 = 'key_part112';就需要回表了，无法索引覆盖。

下面这种：

-- 根据联合索引的非最左列查询索引列的值
explain select key_part1,key_part2,key_part3 from test_index where key_part2 = 'key_part212';

这种情况，原本是需要全表扫描的，因为我们知道，只根据联合索引的非最左列查询索引列等值匹配索引是不生效的，但我们查询的列都是联合索引中的列。mysql采用扫描全部索引替代了全表扫描，效率更高，因为索引的访问是顺序IO，占用空间较小，而回表的IO是随机IO，且全表扫描的磁盘占用也更高。

ALL: 全表扫描，直接扫描主键索引，这种访问方式称为all。
还是引用上面提到的例子，根据联合索引的非最左列查询索引列的值查询：

-- 根据联合索引的非最左列查询索引列的值
explain select * from test_index where key_part2 = 'key_part212';

index merge ： 除此之外，还会有index merge（索引合并），针对一些and、or的操作，单纯的回表可能速度会慢一些，如果先将使用到的索引先进行求 交集、并集之后在进行回表，会更加高效。

主要有：
Intersection合并：求交集
Union合并：求并集
Sort-Union合并，（各二级索引都是等值匹配的时候才会被用到）

这个放后面讲，有兴趣的小伙伴有没有好的相关资料可以分享给我。在此谢过！