[转帖]MySQL联合索引（复合索引）

济南小老虎 / 2024-01-09 / 原文

Mysql联合唯一索引添加相同数据插入报错

联合索引在两个字段都存在唯一，将报错。

1.添加联合索引

alter table "表名" add unique index(`字段1`，`字段2`)

2.此时如果在插入相同的数据会报错，可以使用 no duplicate key update 解决相同数据不存储，不会报错

insert into "表名" （`name`，`age`，`time`）values('zhangsan'，'18'，'2001:10:10') on duplicate key
update `name`=values(`name`)，`age`=values(`age`)

联合索引

本文中联合索引的定义为（MySQL）：

ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);

联合索引的优点

若多个一条SQL，需要多个用到两个条件

SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX';

当索引在检索 password 字段的时候，数据量大大缩小，索引的命中率减小，增大了索引的效率。

符合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度。

联合索引命中的本质（最左匹配的理解）

定义

当创建( col1,col2,col3 )联合索引时，相当于创建了( col )单列索引，( clo1,clo2 )联合索引以及( col1,col2,col3 )联合索引想要索引生效，只能使用 col1 和 col1,col2 和 col1,col2,col3 三种组合；

当然，col1,col3 组合也可以，但实际上只用到了 col1 的索引，col3 并没有用到！

图解

通俗理解

联合索引相当于一个按照姓氏——名字的一个电话簿，只能先确定姓氏才可以命中索引，下列可以正确命中联合索引的语句（ = 和 IN 直接的字段都可以乱序，MySQL的查询优化器可以优化成索引识别的形式）

-- 只命中 col1，col2
SELECT * FROM `table_name` WHERE `col1`='XX';
-- 命中col1，col2。col1，col2的顺序可以颠倒

SELECT  FROM table_name WHERE clo1='XX',clo2='XXX';

SELECT  FROM table_name WHERE clo2='XXX', clo1='XX';
-- 命中col1,col2,col3，同理，三个列的顺可以颠倒

SELECT  FROM table_name WHERE col1='X',col2='XX',col3='XXX';

SELECT  FROM table_name WHERE col1='X',col3='XX',col2='XXX';

SELECT * FROM table_name WHERE col2='X',col3='XX',col1='XXX';