MySQL 独立索引和联合索引的选择

2024-07-30 15:23:48

通常会对多列索引缺乏理解，常见的错误是将很多列设置独立索引，或者是索引列使用错误的次序。我们在下一篇讨论索引列次序的问题，首先看一下多列独立索引的情况，以下面的表结构为例：

CREATE TABLE test (
  c1 INT,
  c2 INT,
  c3 INT,
  KEY(c1),
  KEY(c2),
  KEY(c3),
);

使用这种索引策略通常是一些权威的建议（例如在WHERE条件中用到的条件列增加索引）的结果。事实上，这是大错特错的，要评分的话顶多给1颗星。这种方式的索引与真正优化的索引相比，要慢上几个数量级。有时候当你不能设计三星以上的索引时，去关注优化行次序或者创建覆盖索引都比忽略WHERE条件强。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest';的时候，就可以通过覆盖索引查询，无需再从数据表找数据行。

对很多列创建独立的索引在很多情况下，并不能帮助MySQL改善性能。MySQL 5.0及更新的版本可以使用索引合并策略对这类设计进行些许的优化 —— 这种方式允许在有多列索引的数据表中的查询中限制在索引的使用去定位所需的数据行。

index merge 是对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)

早期的MySQL版本只能使用一个索引，因此当没有索引辅助时，MySQL通常进行全表扫描。例如在film_actor表有一个film_id和actor_id索引，但是在WHERE条件中同时使用这两个索引并不是一个好的选择：

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;

在早期的MySQL版本中，除非你像下面的语句一样将两个查询联合起来，否则这个查询会导致全表扫描。

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 UNION ALL
SELECT film_id, actor_id FROM film_actor WHERE film_id = 1 AND actor_id <> 1;

在MySQL 5.0之后的版本中，查询会同时使用两个索引并且合并最终的结果。需要三个变体的算法实现这个过程：

使用OR条件获取并集（union）数据
使用AND条件获取交集数据
将上面两个步骤的数据的交集再取并集。

上面有点费解，其实应该是分布使用单个条件（以便使用索引）查出全部数据，然后再组合数据。下面使用EXPLAIN查看一下。

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

可以看到查询方式是全表扫描，但是使用了Extra做优化。MySQL在处理负责查询时会使用这种技巧，因此你可能会在Extra中看到嵌套操作。这种索引合并的策略有些时候会发挥很好的作用，但更多的时候应该当作是对差劲索引使用的一个指示：

当服务器使用交集索引（通常是使用AND条件），通常意味着你需要一个索引包含所有相关的列，而不是独立的索引列再组合。
当服务器使用并集索引（通常是使用OR条件），有时候缓存、排序和合并操作会占用很多的CPU和内存资源，尤其是索引并不都是具备筛选的时候，这会导致扫描返回大量的数据行供合并操作。
记住优化器并不承担这些成本——它仅仅是优化随机页读取的数量。这会使得查询“掉价”，导致全表扫描造成事实上更慢。CPU和内存的高占用会影响并发查询，但这些影响在你单独运行查询语句时并不会发生。因此，有时候像在MySQL 4.1版本那样重写那些使用UNION的查询会得到更优的效果。

当你使用EXPLAIN分析的时候看到了索引合并，你应该检查查询语句和表结构，看看是不是最优的方式。你可以使用optimizer_switch（优化开关）禁用索引合并来检查。

再将film_actor的索引改为联合索引(删除原先的两列独立索引film_id和actor_id)看一下效果，可以看到此时避免了全表查询。

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

以上就是MySQL 独立索引和联合索引的选择的详细内容，更多关于MySQL 独立索引和联合索引的资料请关注我们其它相关文章！

MySQL全文索引、联合索引、like查询、json查询速度哪个快

查询背景有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1 我们在限定为上面类型的同时,还得包含下面任意一个编码(也就是OR查询) jy157768338177
MySQL 联合索引与Where子句的优化提高数据库运行效率

网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了.涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,"深圳-枪手"热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~ 在群友的分析指点下,尝试把排序.条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是
MySQL中的联合索引学习教程

联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引.复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏
mysql的联合索引(复合索引)的实现

联合索引本文中联合索引的定义为(MySQL): ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`); 联合索引的优点若多个一条SQL,需要多个用到两个条件 SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX'; 当索引在检索 password字段的时候,数据量大大缩小,索引的命中率减小,增大了索引的效率. 符合索引的索引体积比单独索引的体积要小,而且只是一
MySQL联合索引功能与用法实例分析

本文实例讲述了MySQL联合索引功能与用法.分享给大家供大家参考,具体如下: 联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用
MySQL联合索引用法示例

本文实例讲述了MySQL联合索引.分享给大家供大家参考,具体如下: 员工表 userid 部门表 deptid 员工部门表条件:一个员工可以对应多个部门问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid userid deptid 111 212 311(这个就不能让其添加) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dep
一个案例彻底弄懂如何正确使用mysql inndb联合索引

有一个业务是查询最新审核的5条数据 SELECT `id`, `title` FROM `th_content` WHERE `audit_time` < 1541984478 AND `status` = 'ONLINE' ORDER BY `audit_time` DESC, `id` DESC LIMIT 5; 查看当时的监控情况 cpu 使用率是超过了100%,show processlist看到很多类似的查询都是处于create sort index的状态. 查看该表的结构 CREAT
深入浅析Mysql联合索引最左匹配原则

前言之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 最左前缀匹配原则在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列col1.列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合索引 test_col1_col2_col3 实际建
MySQL 独立索引和联合索引的选择

通常会对多列索引缺乏理解,常见的错误是将很多列设置独立索引,或者是索引列使用错误的次序.我们在下一篇讨论索引列次序的问题,首先看一下多列独立索引的情况,以下面的表结构为例: CREATE TABLE test ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3), ); 使用这种索引策略通常是一些权威的建议(例如在WHERE条件中用到的条件列增加索引)的结果.事实上,这是大错特错的,要评分的话顶多给1颗星.这种方式的索引与真正优化的索引相比,要慢
mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

目录聚集索引(Clustered Index) 辅助索引(Secondary Index) 覆盖索引(Covering index) 联合索引 <MySQL技术内幕 InnoDB存储引擎>学习笔记聚集索引(Clustered Index) 聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据. 举个例子,直观感受下聚集索引. 创建表t,并以人为的方式让每个页只能存放两个行记录(不清楚怎么人为控制每页只存放两个行记录): 最后<MySQL技术内幕>
详解MySQL单列索引和联合索引

目录一.简介二.单列索引三.最左前缀原则四.同时存在联合索引和单列索引(字段有重复) 五.联合索引本质六.索引失效七.其它知识点八.MySQL存储引擎简介九.索引结构(方法.算法) 一.简介利用索引中的附加列,可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引. 联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序.如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓
验证Mysql中联合索引的最左匹配原则详情

目录前言如何验证联合索引的有效性多个单一索引进行验证联合索引总结前言后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识.在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到了mysql,几乎都会遇到Mysql查询需要优化的需求.经常有时候前端业务没有压力,经常会在管理后台逻辑中遇到mysql统计查询压力,可能是代码写太烂了,哈哈.在日常工作中我遇到过同事建立索引后问我某个查询条件是否能命
SpringDataJpa创建联合索引的实现

目录 SpringDataJpa创建联合索引创建联合索引对应类创建映射实体类添加新数据 SpringDataJpa指定联合索引在@Table中指定UniqueConstraint 自动加上唯一索引了 SpringDataJpa创建联合索引创建联合索引对应类 /** * 作者:guoyzh * 时间:2019/12/30 14:58 * 功能:戴镜视力复查联合主键 */ @Data @Embeddable public class VisualReexaminationUnionKey
mysql 联合索引生效的条件及索引失效的条件

目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单
mysql索引(覆盖索引,联合索引,索引下推)

目录什么是索引? 索引的实现方式 innodb的索引模型索引维护覆盖索引联合索引索引下推什么是索引? 当我们使用汉语字典查找某个字时,我们会先通过拼音目录查到那个字所在的页码,然后直接翻到字典的那一页,找到我们要查的字,通过拼音目录查找比我们拿起字典从头一页一页翻找要快的多,数据库索引也一样,索引就像书的目录,通过索引能极大提高数据查询的效率. 索引的实现方式在数据库中,常见的索引实现方式有哈希表.有序数组.搜索树哈希表哈希表是通过键值对(key-value)存储数据的索引实现

MySQL 独立索引和联合索引的选择

相关推荐

随机推荐