东营 网站建设公司,农民工找活平台,管理系统软件,网站怎么做赚钱文章目录 1.简介2.最左匹配3.最左匹配原理4.如何建立联合索引?5.覆盖索引参考文献 1.简介
联合索引指建立在多个列上的索引。
MySQL 可以创建联合索引#xff08;即多列上的索引#xff09;。一个索引最多可以包含 16 列。
联合索引可以测试包含索引中所有列的查询#… 文章目录 1.简介2.最左匹配3.最左匹配原理4.如何建立联合索引?5.覆盖索引参考文献 1.简介
联合索引指建立在多个列上的索引。
MySQL 可以创建联合索引即多列上的索引。一个索引最多可以包含 16 列。
联合索引可以测试包含索引中所有列的查询或仅测试第一列、前两列、前三列等等的查询。如果在索引定义中以正确的顺序指定列则复合索引可以加快对同一表的多种查询的速度。
下面是一个联合索引的例子。
CREATE TABLE test (id INT NOT NULL,last_name CHAR(30) NOT NULL,first_name CHAR(30) NOT NULL,PRIMARY KEY (id),INDEX name (last_name,first_name)
);name 索引是针对 last_name 和 first_name 列的索引。该索引可加速查询。这些查询为 last_name 和 first_name 值的组合。或仅指定 last_name 值的查询因为该列是索引的最左侧前缀即联合索引支持最左匹配。
2.最左匹配
如果 SQL 语句用到了联合索引中最左边的字段那么这条 SQL 语句就可以利用这个联合索引进行匹配这便是最左匹配。
值得注意的是当遇到范围查询 (、、between、like) 就会停止匹配。
假设我们对 (a,b) 字段建立一个索引也就是说如果 WHERE 条件为下面的则可以匹配索引。
a 1
a 1 AND b 2
// 可以匹配索引优化器会自动调整 a,b 的顺序与索引顺序一致。
b 2 AND a 1 相反的下面的条件将无法匹配索引了。
b 2而你对 (a,b,c,d) 建立索引如果条件为
a 1 AND b 2 AND c 3 AND d 4那么 a,b,c 三个字段能用到索引而 d 就匹配不到因为遇到了范围查询。
3.最左匹配原理
最左匹配是针对联合索引来说的所以我们可以从联合索引的原理来了解最左匹配。
我们都知道索引的底层是一颗 B 树那么联合索引当然也是一颗 B 树只不过联合索引的键值不是一个而是多个。构建一颗 B 树只能根据一个键值来构建因此数据库依据联合索引最左边的字段来构建 B 树。
假设我们对 (a,b) 字段建立索引 如图所示是按照 a 来进行排序在 a 相等的情况下才按 b 来排序。
所以这就能够解释为什么条件 a1 AND b2 可以利用索引而 b2 不能利用索引因为 b 在全局是无序的只有 a 确定的情况下b 才是有序。
4.如何建立联合索引?
有了上面的基础我们可以看一下关于联合索引常见的面试问题。
1如果 SQL 为
SELECT * FROM table WHERE a 1 and b 2 and c 3;如果此题回答为对 (a,b,c) 建立索引那就可以回去等通知了。
此题正确答案是任意顺序都可以, 如 (a,b,c) 或 (b,a,c) 或 (c,a,b) 都可以重点是要将区分度高的字段放在前面区分度低的字段放后面。像性别、状态这种字段区分度就很低我们一般放后面。
例如假设区分度由大到小为 b,a,c。那么我们就对 (b,a,c) 建立索引。在执行 SQL 的时候优化器会帮我们调整 WHERE 后 a,b,c 的顺序让我们用上索引。
2如果 SQL 为
SELECT * FROM table WHERE a 1 AND b 2;如果此题回答为对 (a,b) 建立索引那就可以回去等通知了。
正确答案对 (b,a) 建立索引。如果你建立的是 (a,b) 索引那么只有 a 字段能用得上索引毕竟最左匹配遇到范围查询就停止匹配。
如果对 (b,a) 建立索引那么两个字段都能用上优化器会帮我们调整 WHERE 后 a,b 的顺序让我们用上索引。
3如果 SQL 为
SELECT * FROM table WHERE a 1 and b 2 and c 3; 此题回答是 (b,a) 或 (b,c) 都可以要结合具体情况具体分析。
拓展一下
SELECT * FROM table WHERE a 1 AND b 2 AND c 3;根据最左匹配因为字段 c 是范围查询应该放到最后所以应该建立 (a,b,c) 或 (b,a,c)。
4如果 SQL 为
SELECT * FROM table WHERE a 1 ORDER BY b;对 (a,b) 建索引当 a 1 的时候b 相对有序可以避免再次排序。
拓展以下如果 SQL 为
SELECT * FROM table WHERE a 1 ORDER BY b;对 (a) 建立索引因为 a 的值是一个范围这个范围内 b 值是无序的没有必要对 (a,b) 建立索引。
5如果 SQL 为
SELECT * FROM table WHERE a IN (1,2,3) AND b 1;还是对 (a, b) 建立索引因为 IN 在这里可以视为等值引用不会中止索引匹配所以还是 (a,b)。
5.覆盖索引
覆盖索引Covering Index指的是一个索引包含了所有需要查询的字段而不必回到实际的数据行中查找。当一个查询可以直接从索引中获取所有需要的信息时就称之为覆盖索引。
-- 创建表
CREATE TABLE mytable (col1 INT,col2 INT,col3 VARCHAR(255),INDEX idx_covering (col1, col2)
);-- 覆盖索引查询
-- 因为 idx_covering 包含了查询所需的所有列所以是一个覆盖索引查询
SELECT col1, col2 FROM mytable WHERE col1 1 AND col2 2;覆盖索引Covering Index是一种索引优化技术旨在最小化查询开销。
联合索引有一个作用就是实现覆盖索引如果联合索引包含了查询所需的所有列那么查询可以直接从索引中获取所需的数据避免了额外的表访问这可以减少 I/O 操作提高查询性能。
当然单列索引也可以实现覆盖索引即查询的列是索引列。 参考文献
8.3.1 How MySQL Uses Indexes - MySQL 8.3.6 Multiple-Column Indexes - MySQL 面试官:谈谈你对mysql联合索引的认识
