当前位置：首页 > news >正文

Mysql索引-学习笔记

news 2024/11/5 18:51:44

联合索引

在where子句中仅使用联合索引的第二个字段会导致性能问题

在使用联合索引时，如果where子句中仅仅使用联合索引的第二个字段作为过滤条件，将可能导致索引类型为ALL或者index
接下来，我们将做一个实验：

首先创建一个包含两个联合索引的表index_table2

-- 创建表
CREATE TABLE index_table2 (ID INT AUTO_INCREMENT PRIMARY KEY,idx1_field1 VARCHAR(255) NOT NULL,idx1_field2 VARCHAR(255) NOT NULL,idx2_field3 VARCHAR(255) NOT NULL,idx2_field4 VARCHAR(255) NOT NULL
) ENGINE=InnoDB;-- 创建联合索引
CREATE INDEX idx_index1 ON index_table2 (idx1_field1, idx1_field2);
CREATE INDEX idx_index2 ON index_table2 (idx2_field3, idx2_field4);-- 插入 5000 条随机测试数据
DELIMITER $$
CREATE PROCEDURE insert_random_data()
BEGINDECLARE i INT DEFAULT 1;WHILE i <= 5000 DOINSERT INTO index_table2 (idx1_field1, idx1_field2, idx2_field3, idx2_field4)VALUES (CONCAT('idx1_field1', FLOOR(1 + RAND() * 10000)),CONCAT('idx1_field2', FLOOR(1 + RAND() * 10000)),CONCAT('idx2_field3', FLOOR(1 + RAND() * 10000)),CONCAT('idx2_field4', FLOOR(1 + RAND() * 10000)),CONCAT('no_idx_field5_', FLOOR(1 + RAND() * 10000)));SET i = i + 1;END WHILE;
END $$
DELIMITER ;CALL insert_random_data();

接下来，我们将分析，where子句使用不同的字段对性能的影响。

-- 查询联合索引，如果 select 中的字段是联合索引的一部分，且 where 子句字段是联合索引的第一个字段，那么 type 为 ref
explain
select idx1_field1 from index_table2 it where idx1_field1 = 'idx1_field18151'
-- type refexplain
select idx1_field2 from index_table2 it where idx1_field1 = 'idx1_field18151'
-- type ref-- 查询联合索引，如果 select 中的字段补是联合索引的一部分，且 where 子句字段是联合索引的第一个字段，那么 type 为 ref
explain
select idx2_field3 from index_table2 it where idx1_field1 = 'idx1_field18151'
-- type refexplain
select idx2_field4 from index_table2 it where idx1_field1 = 'idx1_field18151'
-- type refexplain
select field5 from index_table2 it where idx1_field1 = 'idx1_field18151'
-- type ref

可以看到，如果where子句使用的是联合索引的第一个字段，那么无论查询哪个字段使用的都是ref索引。

-- 查询联合索引，如果 select 中的字段补是联合索引的一部分，且 where 子句字段不是联合索引的第一个字段，那么 type 为 ref
explain
select idx2_field3 from index_table2 it where idx1_field2 = 'idx1_field18151'
-- type ALLexplain
select idx2_field4 from index_table2 it where idx1_field2 = 'idx1_field18151'
-- type ALLexplain
select field5 from index_table2 it where idx1_field2 = 'idx1_field18151'
-- type ALL-- 查询联合索引，如果 select 中的字段是联合索引的一部分，且 where 子句字段不是联合索引的第一个字段，那么 type 为 index
explain
select idx1_field1 from index_table2 it where idx1_field2 = 'idx1_field18151'
-- type indexexplain
select idx1_field2 from index_table2 it where idx1_field2 = 'idx1_field18151'
-- type index

可以看到，如果where子句仅仅使用联合索引的第二个字段作为过滤条件，那么如果select中的字段不在联合索引中，就会使用全表查询，如果使用的字段在联合索引中，那么就会使用index索引。

如果希望使用联合索引的第二个字段作为过滤条件，此时应该在where子句中加上联合索引的第一个字段，这两个字段在where子句中的顺序，或者有其他where语句，并不影响使用ref索引。

explain
select field5 from index_table2 it where idx1_field2 = 'idx1_field29359' and idx2_field3 = 'idx2_field32298' and idx1_field1 = 'idx1_field11499';
-- ref