【MySQL】索引原理(三):联合索引(最左前缀原则),覆盖索引,索引条件下推

准备工作,下面的演示都是基于user_innodb表:

DROP TABLE IF EXISTS `user_innodb`;
CREATE TABLE `user_innodb` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.联合索引与最左前缀原则

在平时开发中,我们最常见的是单列索引(比如主键primary key),但在我们需要多条件查询的时候,也会建立联合索引。单列索引可以看成是特殊的联合索引。比如我们在user表上面,给name和phone建立了一个联合索引。

ALTER TABLE 可以用来创建索引,包括普通索引、UNIQUE索引或PRIMARY KEY索引:

  • ALTER TABLE table_name ADD INDEX index_name (column_list)

  • ALTER TABLE table_name ADD UNIQUE (column_list)

  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone); -- 创建联合索引

1.1 联合索引是怎么组织的?

下图是B+Tree的索引结构,是一个单值索引:

在这里插入图片描述
我们可以看到,所有非叶子结点的构成都是由两部分组成,索引值+指针,而单值索引说的就是在索引值这里就只有一个值(比如id),而联合索引在索引值可能会有多个值(比如name和phone)

在这里插入图片描述

相比于单值索引:

  1. 联合索引在 B+Tree 中是复合的数据结构
  2. 由于 B+树本身是有序的,所以联合索引是从左到右的顺序来建立搜索树的(name在左边,phone在右边)。从上图可以看出来,name是有序的,phone是无序的。当name相等的时候,phone才是有序的。
  3. 当存储引擎是InnoDB时,叶节点存储的是数据/主键

问题一:联合索引是怎么查找数据的?

比如,我们使用 where name=‘Bob’ and phone = ‘132xx’ 去查询数据的时候

  1. B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右
  2. 如果 name相同的时候再比较 phone

但是如果查询条件没有name,就不知道第一步应该查哪个节点,因为建立搜索树的时候name是第一个比较因子,所以用不到索引。

问题二:联合索引与单值索引什么关系?

假设我们的项目里面有两个查询很慢:

SELECT * FROM user_innodb WHERE name= ?;
SELECT * FROM user_innodb WHERE name= ? AND phone=?;

按照我们的想法,一个查询创建一个索引,所以我们针对这两条SQL创建了两个索引,这种做法觉得正确吗?

CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

当我们创建一个联合索引的时候,用左边的字段name去查询的时候,也能用到索引,所以单独为name创建一个索引完全没必要。相当于建立了两个联合索引(name)、(name,phone)。

如果我们创建三个字段的索引index(a,b,c),相当于创建三个索引:index(a)、index(a,b)、index(a,b,c)。用 where b=? 和 where b=? and c=? 和where a=? and c=?是不能使用到索引的。因为不能不用第一个字段,不能中断。

1.2 最左前缀原则

因为联合索引中包含了多个字段,所以不能像单值索引那样直接使用就行。那需要遵守什么规则呢?
答:最左前缀原则:带头大哥不能死,中间兄弟不能断。

我们在建立联合索引的时候,一定要把最常用的列放在最左边。比如下面的三条语句,能用到联合索引吗?

  1. 使用两个字段,可以用到联合索引(注:两个字段的顺序颠倒并不影响,因为全值匹配时mysql会优化字段顺序)
EXPLAIN SELECT * FROM user_innodb WHERE name= '张三' AND phone='12345678910'

在这里插入图片描述

  1. 使用左边的name字段,可以用到联合索引:
EXPLAIN SELECT * FROM user_innodb WHERE name= '张三'

在这里插入图片描述

  1. 使用右边的phone字段,无法使用索引,全表扫描:
EXPLAIN SELECT * FROM user_innodb WHERE name= '12345678910'

在这里插入图片描述

从联合索引的结构中,我们看到了索引是已经排好序的,那我们如在遵守最左前缀原则的前提下,order by时用到索引(避免 filesort)?

  • 使用形式一:order by 索引最前列 ==> 整体有序。
  • 使用形式二:where + order by索引列 ==> 局部有序(为了最左前缀原则,尽量order by索引列(用where 保证中间不断开))


再说一句,分组(group by)的实质是先排序后分组,原则类似order by。where 高于 having,where中能限定但条件不要去having中限定

另外,不要在索引上做任何操作,因为可能会导致索引失效,转而全表扫描。

1.3 什么情况下会索引失效?

当索引列出现以下六种操作时常常出现索引失效:

  1. 使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、计算(+ - * /)。因为当前值改变后就无法与索引存的值匹配上。
SELECT * FROM user_innodb where left(name, 3)='张三';-- left函数是一个字符串函数,它返回具有指定长度的字符串的左边部分
  1. 使用范围查询(!=,<=>,in)会导致右边列失效。因为二叉树的查找是 = 查找,若是一个范围的话无法继续下探。
    • 最左列用范围,该列也不会使用索引,全部索引列失效
    • 其余列用范围,当前列仍会使用索引,但右边索引列失效
SELECT * FROM user_innodb where name='张三' and age > 22;
  1. like以通配符开头(‘%abc…’),mysql索引失效会变成全表扫描操作。因为无法判断%代表多少字符。
    • 方案一:like (‘abc%’)
    • 方案二:覆盖索引
SELECT * FROM user_innodb where name like '%三';
  1. 字符串不加’ '索引失效。因为会出现出现隐式转换,相当于给索引列做了操作。
SELECT * FROM user_innodb where name = 007;-- "007"从字符串变成了数字007
  1. 少用or,用它连接时很多情况下索引会失效
SELECT * FROM user_innodb where name = '张三' or name = '李四';
  1. is null,is not null 无法使用索引
SELECT * FROM user_innodb where name is null;

==> 对这一部分内容通过一首打油诗做个总结:

							全值匹配我最爱,最左前缀要遵守
							带头大哥不能死,中间兄弟不能断
							索引列上少计算,范围之后全失效
							like百分写最右,覆盖索引不写星
						    不等空值还有or,索引失效要少用

2.覆盖索引

回表:非主键索引,我们先通过索引找到主键索引的键值,再通过主键值查出索引里面没有的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。例如:select * from user_innodb where name = ‘青山’;

在这里插入图片描述

在辅助索引里面,不管是单列索引还是联合索引,如果select的数据列只用从索引中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免了回表。

我们先来创建一个联合索引:

CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

这三个查询语句都用到了覆盖索引:

EXPLAIN SELECT name,phone FROM user_innodb WHERE name='青山' AND phone='13666666666';
EXPLAIN SELECT name FROMuser_innodb WHERE name='青山' AND phone='13666666666'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name='青山' AND phone='13666666666';

在这里插入图片描述

Extra里面值为“Using index”代表使用了覆盖索引。另外,select * ,用不到覆盖索引。很明显,因为覆盖索引减少了IO次数,减少了数据的访问量,可以大大地提升查询效率。

3.索引条件下推(ICP)

在讲ICP前,我们再创建一张数据表(员工表),并且在last_name和first_name上面创建联合索引。

CREATE TABLE `employees`( 
    `emp_no`int(11)NOTNULL,
    `birth_date`date NULL,
    `first_name`varchar(14)NOTNULL,
    `last_name`varchar(16)NOTNULL, 
    `gender`enum('M','F')NOTNULL, 
    `hire_date`date NULL, 
    PRIMARYKEY(`emp_no`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;

alter table employees add index idx_lastname_firstname(last_name,first_name); -- 创建联合索引

INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(1, NULL,'698','liu','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(2, NULL,'d99','zheng','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(3, NULL,'e08','huang','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(4, NULL,'59d','lu','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(5, NULL,'0dc','yu','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(6, NULL,'989','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(7, NULL,'e38','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(8, NULL,'0zi','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(9, NULL,'dc9','xie','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(10, NULL,'5ba','zhou','F',NULL);

3.1 ICP是干什么用的?

现在我们要查询所有姓wang,并且名字最后一个字是zi的员工,比如王胖子,王瘦子。查询的SQL如下:

select * from employees where last_name='wang' and first_name LIKE '%zi';

这条SQL有两种执行方式:

  1. 根据联合索引查出所有姓wang的二级索引数据,然后回表,到主键索引上查询全部符合条件的数据(3 条数据)。然后返回给 Server 层,在 Server 层过滤出名字以zi结尾的员工。

    注:索引的比较是在存储引擎进行的,数据记录的比较是在Server层进行的。而当first_name的条件不能用于索引过滤时,Server 层不会把first_name的条件传递给存储引擎,所以读取了两条没有必要的记录。如果将数据规模扩大,比如满足last_name='wang’的记录有100000条,就会有99999条没有必要读取的记录。

在这里插入图片描述

  1. 根据联合索引查出所有姓wang的二级索引数据(3个索引),然后从二级索引中筛选出first_name以zi结尾的索引(1个索引),然后再回表,到主键索引上查询全部符合条件的数据(1条数据),返回给Server 层。

很明显,第二种方式到主键索引上查询的数据更少,但mysql在没开启ICP前使用的都是第一种。

explain select * from employees where last_name='wang' and first_name LIKE '%zi';

在这里插入图片描述

Using Where代表从存储引擎取回的数据不全部满足条件,需要在Server 层过滤。先用last_name 条件进行索引范围扫描,读取数据表记录,然后进行比较,检查是否符合first_name LIKE ‘%zi’ 的条件。此时3条中只有1条符合条件。

3.2 怎么开启ICP?

开启命令如下:

set optimizer_switch='index_condition_pushdown=on';

开启后再查看此时的执行计划,Using index condition:

在这里插入图片描述

把first_name LIKE '%zi’下推给存储引擎后,只会从数据表读取所需的1条记录。索引条件下推(IndexConditionPushdown),5.6以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

<think>好的,我现在需要处理用户关于“索引联合索引”的查询。用户之前询问过@NotBlank注解,现在转向数据库索引,可能是在学习后端开发中的数据持久化部分。 首先,要明确用户的需求。他们可能想了解索引的基本概念,以及联合索引的具体应用场景和优化技巧。由于用户之前接触的是Java校验,可能对数据库优化不熟悉,需要从基础讲起。 接下来,我需要确定回答的结构。用户提供的示例回答结构清晰,分点说明,我应该效仿。先介绍索引,再讲联合索引,然后对比,注意事项,最后使用建议。这样用户能逐步理解。 索引部分要解释是什么、为什么使用、优缺点。联合索引则需要强调多个列的组合,以及最左前缀原则。需要举例说明,比如(name, age)的查询情况,这样用户更容易理解。 同时,用户可能对如何创建索引联合索引的SQL语法感兴趣,所以需要给出示例代码,比如CREATE INDEX语句。注意事项部分要提到过度索引的问题,写操作的性能影响,以及索引选择策略。 还要考虑用户的实际应用场景,比如在订单表中使用联合索引来优化查询,这能帮助他们将理论应用到实践中。最后,总结部分要强调合理使用索引的重要性,避免常见误区。 需要确保用词准确,避免技术术语过多,但又要保持专业性。例如,解释“最左前缀原则”时,要简明扼要,用例子说明。同时,检查是否有遗漏点,比如索引的类型(B-tree、哈希等),但用户的问题可能更关注基础,所以暂时不深入。 最后,确保回答符合之前的格式要求,使用中文,数学公式用$...$,独立公式用$$...$$,但这次内容可能不需要太多公式,但如果有涉及计算的地方要注意格式。检查是否有错误,比如索引的优缺点是否全面,联合索引的应用场景是否明确。</think>以下是对数据库索引联合索引的全面解析,采用技术文档结构呈现: 一、索引基础原理 1. **本质定义** $$ \text{索引} = \text{预排序数据结构} + \text{快速定位指针} $$ - 采用B+Tree/Hash等数据结构实现(主流数据库默认B+Tree) - 存储字段值+对应数据物理地址 2. **核心价值** - 将$O(n)$的全表扫描优化为$O(\log n)$的树搜索 - 典型空间换时间策略(索引占表空间20%-30%) 3. **创建语法示例** ```sql -- 单列索引 CREATE INDEX idx_name ON users(name); -- 联合索引(多列组合) CREATE INDEX idx_name_age ON employees(name, age DESC); ``` 二、联合索引专项解析 1. **结构特性** - 索引键值按声明顺序存储:$(\text{col1}, \text{col2}, \text{col3}) \rightarrow$ 先按col1排序,col1相同按col2排序,依此类推 - 存储结构示例: ``` (Alice,20) -> 0x1234 (Alice,25) -> 0x5678 (Bob,18) -> 0x9abc ``` 2. **最左前缀原则** - 有效查询模式: ```sql WHERE col1 = ? WHERE col1 = ? AND col2 = ? WHERE col1 = ? AND col2 > ? ``` - 失效场景: ```sql WHERE col2 = ? -- 缺少左前缀 WHERE col1 LIKE '%abc' -- 左前缀模糊匹配 WHERE col1 = ? OR col2 = ? -- OR操作破坏连续性 ``` 、性能对比矩阵 | 对比维度 | 单列索引 | 联合索引 | |----------------|---------------------------|-------------------------| | 存储开销 | 每个索引独立存储 | 多列组合存储,节省空间 | | 查询覆盖 | 仅优化单列查询 | 支持多列组合查询 | | 排序优化 | 单字段排序 | 多字段复合排序 | | 维护成本 | 写操作需维护多个索引 | 单索引维护成本更低 | 四、实践应用建议 1. **优先场景** - 高频组合查询:`WHERE status=1 AND create_time > '2023'` - 多列排序需求:`ORDER BY last_name, first_name` - 覆盖索引优化:`SELECT id,name FROM users WHERE age=25` 2. **设计策略** - 区分度原则:高区分度列(如手机号)放左侧 - 长度控制:varchar列建立前缀索引`INDEX idx(email(10))` - 冷热分离:更新频率差异大的列避免组成联合索引 五、典型案例分析 **订单表优化场景**: ```sql CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) ); -- 创建联合索引 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); ``` **生效查询**: ```sql SELECT * FROM orders WHERE user_id=123 AND status=2 ORDER BY create_time DESC LIMIT 10; EXPLAIN结果: type=ref, key=idx_user_status_time, Extra=Using where ``` 六、注意事项 1. **索引失效陷阱**: - 隐式类型转换:`WHERE varchar_col=123` - 函数操作:`WHERE YEAR(create_time)=2023` - 不等于操作:`WHERE status != 1` 2. **监控维护**: ```sql -- 查看索引使用率 SELECT * FROM sys.schema_index_statistics WHERE table_name='orders'; -- 重建索引 ALTER TABLE orders REBUILD INDEX idx_name; ``` 正确使用索引可使查询速度提升$10^2 \sim 10^4$倍,但需根据EXPLAIN执行计划和慢查询日志持续优化。建议单表索引数控制在$[3,5]$个区间,避免过度索引影响写性能。
评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

A minor

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值