SQL 索引最左前缀原理

最新推荐文章于 2024-07-07 10:00:00 发布
最新推荐文章于 2024-07-07 10:00:00 发布
阅读量1.1w 收藏 11
点赞数 1
分类专栏： sql 文章标签： 索引 sql

表结构和索引列

假设数据库中表是这样的：
这里写图片描述

我们只考虑一张表employees.titles：
索引是（emp_no，title，from_date）

SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            2 | title       | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      443308 |      | BTREE      |
| titles |          1 | emp_no   |            1 | emp_no      | A         |      443308 |      | BTREE      |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。

为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：

ALTER TABLE employees.titles DROP INDEX emp_no;

下面分情况讨论，注意每个查询结果的key_len，key_len 越长，索引用到的越多

全列匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | titles | const | PRIMARY       | PRIMARY | 59      | const,const,const |    1 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

当按照索引中所有列进行精确匹配（这里精确匹指“=”或“IN”匹配）时，索引可以被用到。

这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如将where中的条件顺序颠倒：

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | titles | const | PRIMARY       | PRIMARY | 59      | const,const,const |    1 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

依然能触发索引

最左前缀匹配

比如（emp_no，title）查询是可以触发索引的

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

比如查询（emp_no，from_date）

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

这样只会触发emp_no索引，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date。

如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。

除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几种不同的值：

SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |    7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。

如果title的值很多，用填坑就不合适了，必须建立辅助索引。

查询条件没有指定索引第一列

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 443308 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

不是最左前缀不会触发

匹配某列的前缀字符串

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 56      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引。（原文表述有误，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）

范围查询

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
比如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

可以触发（emp_no ，title） 索引

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no < 10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

也是只能触发（emp_no ，title） 索引

用了“between”并不意味着就是范围查询，例如下面的查询：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

查询条件中含有函数或表达式

MySQL不会为这列使用索引，比如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。

再如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 443308 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

最左前缀原理

假设联合索引是state/city/zipCode

那么state就是第一关，city是第二关，zipCode就是第三关

必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关

你不能直接到第二关的

索引的格式就是第一层是state，第二层才是city

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了

举例说明
假设数据 表T (a,b,c) rowid 为物理位置

rowid a b c
(1) 1 1 1
(2) 2 1 13
(3) 2 2 14
(4) 1 3 3
(5) 2 3 12
(6) 1 2 5
(7) 2 3 9
(8) 1 2 2
(9) 1 3 6
(10) 2 2 11
(11) 2 2 8
(12) 1 1 7
(13) 2 3 15
(14) 1 1 4
(15) 2 1 10

当你创建一个索引 create index xxx on t(a,b), 则索引文件逻辑上等同于如下

a b rowid
1 1 1
1 1 12
1 1 14
1 2 6
1 2 8
1 3 4
1 3 9
2 1 2
2 1 15
2 2 3
2 2 10
2 2 11
2 3 5
2 3 7
2 3 13

当select * from T where a=1 and b=3 的时候， 数据库系统可以直接从索引文件中直接二分法找到A=1的记录，然后再B=3的记录。
但如果你 where b=3 则需要遍历这个索引表的全部！

mitsuhide1992
关注
  • 1
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
深入浅析Mysql联合索引最左匹配原则
12-16
联合索引遵循最左前缀匹配原则，这是理解其工作方式的关键。这一原则指出，当创建一个包含多个列的联合索引（例如，`KEY test_col1_col2_col3 (col1, col2, col3)`），MySQL会构建三个子索引：`(col1)`，`(col1, col...
MySQL组合索引与最左匹配原则详解
01-19
之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章，自以为就了解了其原理，最近面试时和面试官交流，发现遗漏了些东西，这里自己整理一下这方面的内容。 什么时候创建组合索引? 当我们的where查询存在多个...
【SQL】什么是最左前缀原则/最左匹配原则
最新发布
hui_zai_的博客
07-07 485
最左前缀原则在设计和使用复合索引时非常重要。
Mysql中索引的最左前缀原则图文剖析（全）
码农研究僧的博客
12-29 6310
目录前言1. 定义2. 全索引顺序3. 部分索引顺序3.1 正序3.2 乱序4. 模糊索引5. 范围索引 前言 之所以有这个最左前缀索引 归根结底是mysql的数据库结构 B+树 在实际问题中 比如 索引index (a,b,c)有三个字段， 使用查询语句select * from table where c = '1' ，sql语句不会走index索引的 select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引 1. 定义 最左前缀匹配原则：
四、联合索引最左前缀原理
XZQ_STUD的博客
01-21 604
B+Tree联合索引底层数据结构 单列索引：是指由一个字段（单个列）组成的索引； 联合索引：联合索引又叫复合索引，是指由多个字段（字段是有顺序的）组成的索引，比如： CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, nick varchar(30) DEFAULT NULL, phone varchar(25) NOT NULL, password varchar(64) DEFAULT NULL, email varchar(50) DEFA
MySQL 最左前缀原则
weixin_43677673的博客
04-06 2914
MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，以最左边的为起点任何连续的索引都能匹配上,同时遇到范围查询(>、<、between、like)就会停止匹配。。 我先说一下，我从看来的文章里理解的内容。 例如，我创建了一张表，有A、B、C三个属性。我们在此基础上创建联合索引（A、B、C），实际上这是创建了三个索引，（A）（A、B）（A、B、C）。我们通过索引查询的时候，就可以查（A=？、B=?），Sql语言甚至有自动检测功能，当你输入的顺序不是按照索引的顺序时，例如，（Ｂ＝？，Ａ.
关于索引的最左前缀原则
热门推荐
zzx125的专栏
03-24 4万+
索引的最左前缀
Mysql最左前缀原则
minton
10-21 452
底层原理 当有多列索引时，mysql底层使用索引查询的时候是从左往右进行匹配，并且是需要从左往右连续的。 示例 如：索引顺序是a、b、c，当查询条件为a = 1 或 a = 1 and b = 1 时将会使用到索引，但是当查询条件为b = 1 或 a = 1 and c = 1将不会使用上索引 多了索引创建示例 如有一个2列索引(a,b),则在a,(a,b)上会建立索引 如有一个3列索引(a,b,...
SQL Server全文索引服务
09-14
SQL Server全文索引服务是数据库管理系统SQL Server中的一项重要功能，它为用户提供了高效、强大的文本搜索能力，允许用户执行复杂的全文本查询，而不仅仅是基于精确匹配的简单查询。全文索引服务大大提升了在大量...
MySQL索引长度限制原理解析
12-15
在MySQL 5.5之前，InnoDB引擎不支持大的索引键前缀，单个索引列的最大长度限制为767字节。从MySQL 5.5开始，引入了`innodb_large_prefix`选项。当此选项开启时，单个索引的长度可以增加到3072字节，但联合索引的总...
MySQL前缀索引导致的慢查询分析总结
12-15
本篇文章将深入分析一个特定案例，即“前缀索引”如何导致慢查询，并探讨其背后的原理。 首先，让我们回顾一下问题的背景。在一个与DB相关的项目中，开发者发现一个查询在使用索引后执行速度显著变慢。通过`EXPLAIN...
innodb组合索引的最左前缀原则
vinter_he
08-21 2万+
我们在使用数据库的时候，为了增加访问速度，经常会给某张表增加一些索引。单列索引我之前已经写过一篇文章提过，今天我们主要讲一下怎么使用多列索引，和多列索引的最左前缀原则。 关于最左前缀的误解 早些年读高性能mysql这本书的时候，当年感觉书中对于最左前缀的描述相对来说晦涩一点，并且没有详细的例子来说明具体什么是最左前缀，所以在一段时间内我都以为where还要按照顺序写才能触发最左前缀，其实并不是这样。 假设表结构如下 CREATE TABLE `testindex` ( `id` int(10) NOT
Mysql中的最左前缀原则
With_Her的博客
02-19 790
前述： 在了解mysql索引机制时，组合索引提到了遵循最左前缀原则，所以对此进行了了解，在此记录 对于我们的sql有没有使用索引，可以使用explain关键字来查看sql语句的执行计划 详述： 由上面所说的，最左原则一般作用用于mysql中的组合索引中的 假设现在创建一个表mytable，且创建a,b,c组合索引，索引名为abc， CREATE TABLE mytable ...
数据库索引-最左前缀原理及其优化
伽蛙的专栏
05-26 1492
你一定听过最左前缀原理，要高效地使用msyql的索引进行查询，我们就必须掌握最左前缀原理，达到建立合适索引的目的 这里我们使用mysql自带的employees数据库，请读者自行下载： 这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组，其中各个元素
最左前缀原则和索引下推
cxy_19891109的博客
12-21 609
mysql最左前缀概念_Mysql的最左前缀匹配原则(上)
weixin_30896727的博客
01-21 754
最左前缀在mysql的官方文档中称之为leftmost prefix，该原则适用于多列索引，想仅仅用三言两语来说清楚什么是最左前缀匹配原则不太现实，但是如果使用官方文档的一个例子来说明该原则，或许会好得多。假如现在有一张表的创建语句如下：CREATE TABLE test (id INT NOT NULL,last_name CHAR(30) NOT NULL,first_nam...
数据库优化-------创建索引与最左前缀原则
ustcyy91的博客
07-11 585
一.Mysql 创建索引在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。1．ALTER TABLEALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE...
【数据库MySQL】-- 索引（三）（最左前缀法则、索引失效情况、SQL提示）
张修宇的博客
05-03 1390
一、最左前缀法则 如果索引了多列（联合索引），要遵循最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。 如果跳跃某一列，索引将部分失效（后面的字段索引失效）。 例： 第一个语句可以索引。 第二个语句不能索引，因为profession最左侧的索引被跳过，后面的不能索引。 第三个语句部分索引，只有profession可以索引，status不能。 explain select * from tb_user where profession='软件工程' and age
SQL索引，最左前缀法则
04-20
索引是数据库中非常重要的优化技术之一，可以提高查询效率。在使用索引的过程中，最左前缀法则是一条非常重要的原则。它指的是对于复合索引（即包含多个属性的索引）来说，从左到右依次使用它的属性作为查询条件，可以获得最佳的查询效果。这是因为数据库引擎可以利用这个顺序对索引进行快速匹配，从而加快查询速度。同时，建立正确的索引也是提高数据库性能的重要手段之一。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值