mysql的联合索引(复合索引)的实现

联合索引

本文中联合索引的定义为（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并没有用到！

图解

通俗理解

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

到此这篇关于mysql的联合索引(复合索引)的实现的文章就介绍到这了,更多相关mysql 联合索引 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL联合索引功能与用法实例分析

    MySQL联合索引功能与用法实例分析

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

  • MySQL 联合索引与Where子句的优化 提高数据库运行效率

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

  • MySQL联合索引用法示例

    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全文索引、联合索引、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中的联合索引学习教程

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

  • 一个案例彻底弄懂如何正确使用mysql inndb联合索引

    一个案例彻底弄懂如何正确使用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的联合索引(复合索引)的实现

    mysql的联合索引(复合索引)的实现

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

  • MySQL复合索引的深入探究

    MySQL复合索引的深入探究

    复合索引(又称为联合索引),是在多个列上创建的索引.创建复合索引最重要的是列顺序的选择,这关系到索引能否使用上,或者影响多少个谓词条件能使用上索引.复合索引的使用遵循最左匹配原则,只有索引左边的列匹配到,后面的列才能继续匹配.本文主要探究复合索引的创建顺序与使用情况. (一)复合索引的概念 在单个列上创建的索引我们称为单列索引,在2个以上的列上创建的索引称为复合索引.在单个列上创建索引相对简单,通常只需要考虑列的选择率即可,选择性越好,代表数据越分散,创建出来的索引性能也就更好.通常,某列选择率

  • MySql索引和索引创建策略

    MySql索引和索引创建策略

    目录 1.B+树索引 1.1.聚集索引/聚簇索引 1.2.辅助索引/二级索引 1.3.联合索引/复合索引 1.3.1.什么是复合索引 1.3.2.最左原则 1.3.3.联合索引的查询优化 2.哈希索引 2.1.查看哈希索引的命中率等信息 3.索引的创建策略 3.1. 单列索引的策略 3.1.1.列的类型占用的空间越小,越适合作为索引 3.1.2.根据列的值的离散性 3.1.3.前缀索引 3.1.2.只为搜索.排序和分组的列建索引 3.2. 多列索引的策略 3.2.1.离散性最高的列放前面 3.2

  • 当Mysql行锁遇到复合主键与多列索引详解

    背景 今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题.分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题. 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...) InnoDB中的锁算法是Ne

  • Mysql limit 优化，百万至千万级快速分页 复合索引的引用并应用于轻量级框架

    MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发.可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了.用事实说话,看例子: 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个

  • MySQL的复合索引总结

    目录 1.背景 2.认识复合索引 3.最左匹配原则 4.字段顺序的影响 5.单字段是否可以触发索引? 7.复合索引可以替代单一索引吗? 1.背景 最近频繁出现慢SQL导致系统性能问题,于是决定针对索引进行一些优化.一些表结构本身已经有了不少索引,如果再继续添加索引,势必会影响到插入数据的性能.那么,是否可以使用组合索引来达到目的呢?这篇文章咱们来一探究竟. 2.认识复合索引 如果where条件中使用到多个字段,并且需要对多个字段建立索引,此时就可以考虑采用复合索引(组合索引).比如查询地址时需要

  • Mysql的复合索引如何生效

    目录 背景 认识复合索引 最左匹配原则 字段顺序的影响 复合索引可以替代单一索引吗? 小结 背景 最近频繁出现慢SQL导致系统性能问题,于是决定针对索引进行一些优化.一些表结构本身已经有了不少索引,如果再继续添加索引,势必会影响到插入数据的性能.那么,是否可以使用组合索引来达到目的呢?这篇文章咱们来一探究竟. 认识复合索引 如果where条件中使用到多个字段,并且需要对多个字段建立索引,此时就可以考虑采用复合索引(组合索引).比如查询地址时需要输入省.市,那么在省.市上建立索引,当数据量大时会明

  • 如何利用MySQL添加联合唯一索引

    如何利用MySQL添加联合唯一索引

    目录 联合唯一索引 扩展延伸: 附:mysql中如何用命令创建联合索引 总结 联合唯一索引 项目中需要用到联合唯一索引: 例如:有以下需求:每个人每一天只有可能产生一条记录:处了程序约定之外,数据库本身也可以设定: 例如:t_aa 表中有aa,bb两个字段,如果不希望有2条一模一样的记录(即:aa字段的值可以重复: bb字段的值也可以重复,但是一条记录(aa,bb)组合值不允许重复),需要给 t_aa 表添加多个字段的联合唯一索引: alter table t_aa add unique ind

  • 验证Mysql中联合索引的最左匹配原则详情

    验证Mysql中联合索引的最左匹配原则详情

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

  • mysql索引(覆盖索引,联合索引,索引下推)

    mysql索引(覆盖索引,联合索引,索引下推)

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

随机推荐