【实测版】联合索引，最左匹配原则，走？不走？

缺乏、安全感 2024-03-26 21:15 66阅读 0赞

本文基本基于Explain做分析，如果不太了解可以先移步下文(｡･ω･｡)ﾉ

[了解MySQL的Explain，读这一篇够了(￣∇￣)/][MySQL_Explain]

开始✊✊✊

### 建表 ###

我们先创建一个表

CREATE TABLE `aqin_index` (
        `id` BIGINT(20) UnSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(50) NOT NULL,
        `card_no` VARCHAR(50) NOT NULL,
        `time_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `note` VARCHAR(100) NOT NULL,
        PRIMARY KEY (`id`),
        INDEX `index_unit` (`name`,`time_created`,`card_no`) USING BTREE) 
        ENGINE=InnoDB DEFAULT CHARSET=utf8;

![162f6f362c8f9dfd2f82d6b8be60d386.png][]

Message字段显示OK则表示创建成功

这里创建的时候直接添加了一个联合索引

![eb09958c6ffdee53da8a8c3288cbdc73.png][]

INDEX 索引名称 (字段1,字段2,字段3) USING 索引方法

*  索引名称：自己起个就成

*  字段1,字段2,字段3：想要添加的联合索引的所有字段（需要注意顺序）

*  索引方法：一般选择BTREE（BTREE/HASH）

也可以在现有的表中添加

ALTER TABLE `aqinindex`  ADD INDEX `index_unit` (`name`,`time_created`,`card_no`)

### 插入数据 ###

#### 创建函数 ####

-- 插入百万数据
    DELIMITER $$
    CREATE FUNCTION mock_data_4_aqin_index()
    RETURNS INT
    
    BEGIN
            DECLARE num INT DEFAULT 1000000;
      DECLARE i INT DEFAULT 0;
            
      WHILE i < num DO
      -- 插入语句
      INSERT INTO `aqin_index`(`name`,`card_no`,`note`)VALUES
      (CONCAT('name-',i), CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))),
      CONCAT('note-',FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100)));
         SET i = i+1;
       END WHILE;
       RETURN i;
            
    END;

![29a2b335b7523d116a9158c00cb2025c.png][]

Message字段显示如上图即为执行成功

#### 调用函数 ####

SELECT mock_data_4_aqin_index()

![51fe36595745037937aa93c5e02f9d76.png][]

Message字段显示如上图即为执行成功，瞅一眼咱们生成的数据

![56d4da7708504859ee7268438f985306.png][]

#### 删除函数 ####

DROP FUNCTION 函数名

*  注意⚠️此处的函数名是不加括号的

### 我们浅浅的测试下 ###

执行测试语句

EXPLAIN select  * from aqin_index where name = 'name-9999' and time_created="2023-01-09 16:37:56" and `card_no` = '18432153441'

![b97ef8e476cafb405bd5ae1672726a50.png][]

可以看出type类型是ref，key\_len值为310（三个字段长度和），走了联合索引并且3个字段都用上了

计算方式可以参考[了解MySQL的Explain，读这一篇够了(￣∇￣)/][MySQL_Explain]

1.  替换三个字段的顺序

![b57b40ddd888f90d0ba05739f2f620d5.png][]

各个参数无变化

结论：**字段都有，换顺序对结果无影响**

1.  删除第一个字段

![48ee5840d8b02618be8dd170d5246fbf.png][]

type类型是ALL，即走了全表查询

结论：**第一个字段缺失则不走索引**

1.  删除其他字段

![5b86058dbc8c68f1f0bcbde03dffa832.png][]

type类型是ref，key\_len值为158，用到了联合索引，但是只用了两个字段（name和time\_created）

结论：**第一个字段存在，就会使用联合索引，其他字段缺少只会影响用到索引的字段数**

#### 特殊情况 ####

![02ef9fb6fdb7f2edeef7bfffc7cbf9a4.png][]

![18c35927386c55eaa9b50ddd418daf5c.png][]

分析下上面两张图，他们的type类型是index，使用了全索引扫描，相比较ref挺慢的其实

![28e6373842f4f35eb4fca38a001f922e.png][]

![7e39776e20502edd5e9faa495e24f41d.png][]

分析下上面两张图，他们的区别在于查询的字段是否被联合索引覆盖，card\_no在联合索引中，type类型是index，note不在type类型是ALL，index的性能虽然不如ref但优于全表扫描ALL

#### 总结 ####

**联合索引的各个字段可以不按顺序出现（原因是WHERE后面字段的顺序会被优化器优化为符合最左前缀的顺序），但是第一个字段必须出现，否则不走索引（最左前缀匹配，即WHERE条件必须有联合索引的第一个字段，以最左边的为起点任何连续的索引都能匹配上），当SELECT后需要查询的字段被联合索引覆盖，也会走索引，但方式是进行全索引扫描，效率并不高**

[MySQL_Explain]: https://blog.csdn.net/aqin1012/article/details/129795071
[162f6f362c8f9dfd2f82d6b8be60d386.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b70558c5745343ee83f081070862ab47.png
[eb09958c6ffdee53da8a8c3288cbdc73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/0ed7b79b52724999a081bd9d4ca9a370.png
[29a2b335b7523d116a9158c00cb2025c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/42d237f238144f52b45184ce4a71e954.png
[51fe36595745037937aa93c5e02f9d76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/998746715f064e32bb9a96b086f7d934.png
[56d4da7708504859ee7268438f985306.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/316e65b3fefc471aaea6cbba750b7d90.png
[b97ef8e476cafb405bd5ae1672726a50.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/da3c7e64b91b4271bbfcba48329af568.png
[b57b40ddd888f90d0ba05739f2f620d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/aefa82dc20a6409c932e231876287c26.png
[48ee5840d8b02618be8dd170d5246fbf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9a500dd7d11f45588d29825aef895540.png
[5b86058dbc8c68f1f0bcbde03dffa832.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9266ff6b9ba74d4fb64f94f9ecb0c6ed.png
[02ef9fb6fdb7f2edeef7bfffc7cbf9a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/61e7fc6264224c439282b94fc60734b9.png
[18c35927386c55eaa9b50ddd418daf5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/efcb8731640d4cbd916518c8b427966d.png
[28e6373842f4f35eb4fca38a001f922e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2157ee7d2a1f4f9aa9c261f3391fdaae.png
[7e39776e20502edd5e9faa495e24f41d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/00394ade53c04e0eaa4bd9ba3ec39756.png