MySQL联合索引（复合索引）

系统管理员 2023-10-06 17:18 18阅读 0赞

## 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` 并没有用到！

#### 图解 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]

#### 通俗理解 ####

联合索引相当于一个按照`姓氏——名字`的一个电话簿，只能先确定姓氏才可以命中索引，下列可以正确命中联合索引的语句（ `=` 和 `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';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210414102234397.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70