Mysql之组合索引方法详解

对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。

如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：

假设存在组合索引(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

举例说明： 
创建两张表book(图书表)和bookclass(图书分类表)

select b.ISBN FROM book b where b.CATEGORY_ID = 1;

执行时间为：0.053s

使用explain来分析一下该SQL：

type = ALL Extra=Using where，全表查询没有使用索引。

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

ALL 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

创建组合索引： 
create index index_isbn on book (CATEGORY_ID,ISBN) ;

再次执行SQL，发现时间缩短到0.009s

使用explain来分析一下该SQL：

type = ref，Extra = Using index 使用了索引查询。

ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

到此这篇关于Mysql之组合索引方法详解的文章就介绍到这了,更多相关Mysql之组合索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL组合索引与最左匹配原则详解

    前言 之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 什么时候创建组合索引? 当我们的where查询存在多个条件查询的时候,我们需要对查询的列创建组合索引 为什么不对没一列创建索引 减少开销 覆盖索引 效率高 减少开销:假如对col1.col2.col3创建组合索引,相当于创建了(col1).(col1,col2).(col1,col2,col3)3个索引 覆盖索引:假如查询SELECT

  • 深入了解mysql索引

    1.索引原理 索引被用来快速找出在一个列上用一特定值的行.没有索引,MySQL不得不首先以第一条记录开始,然后读完整个表直到它找出相关的行.表越大,花费时间越多.对于一个有序字段,可以运用二分查找(Binary Search),这就是为什么性能能得到本质上的提高.MYISAM和INNODB都是用B+Tree作为索引结构 (主键,unique 都会默认的添加索引) 2.创建索引 如果未使用索引,我们查询 工资大于 1500的会执行全表扫描 什么时候需要给字段添加索引: -表中该字段中的数据量庞大

  • Mysql索引类型与基本用法实例分析

    本文实例讲述了Mysql索引类型与基本用法.分享给大家供大家参考,具体如下: 索引 MySQL目前主要有以下几种索引类型: 普通索引 唯一索引 主键索引 组合索引 全文索引 - 普通索引 是最基本的索引,它没有任何限制. CREATE INDEX IndexName ON `TableName`(`字段名`(length)) - 唯一索引 与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一. CREATE UNIQUE INDEX index

  • 快速了解MySQL 索引

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索包含多个列. 创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件). 实际上,索引也是一张表,该表保存了主键与索引字

  • mysql索引原理与用法实例分析

    本文实例讲述了mysql索引原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是索引 创建索引 普通索引 唯一索引 全文索引 单列索引 多列索引 查看索引 删除索引 首发日期:2018-04-14 什么是索引: 索引可以帮助快速查找数据 而基本上索引都要求唯一(有些不是),所以某种程度上也约束了数据的唯一性. 索引创建在数据表对象上,由一个或多个字段组成,这若干个字段组成"键"存储到数据结构中(B树或者哈希表).[可以根据数据结构分类成B树索引(innodb\myisam引

  • 一篇文章掌握MySQL的索引查询优化技巧

    前言 本文的内容是总结一些MySQL的常见使用技巧,以供没有DBA的团队参考.如无特殊说明,存储引擎以InnoDB为准. MySQL的特点 了解MySQL的特点有助于更好的使用MySQL,MySQL和其它常见数据库最大的不同在于存在存储引擎这个概念,存储引擎负责存储和读取数据.不同的存储引擎具有不同的特点,用户可以根据业务的特点选择适合的存储引擎,甚至是开发一个新的引擎.MySQL的逻辑架构大致如下: MySQL默认的存储引擎是InnoDB,该存储引擎的主要特点是: 支持事务处理 支持行级锁 数

  • MySQL 索引知识汇总

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 拿汉语字典的目录页(索引)打比方,我们可以按拼音.笔画.偏旁部首等排序的目录(索引)快速查找到需要的字. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索引包含多个列. 创建索引时,你需要确保该索引是应用在

  • Mysql之组合索引方法详解

    对于任何DBMS,索引都是进行优化的最主要的因素.对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降. 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找.例如: 假设存在组合索引(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引.查询语句select * from t1 where c1=1也能够使用该索引.但是,查询语句select * from t1

  • 查看mysql当前连接数的方法详解

    1.查看当前所有连接的详细资料: ./mysqladmin -uadmin -p -h10.140.1.1 processlist2.只查看当前连接数(Threads就是连接数.): ./mysqladmin -uadmin -p -h10.140.1.1 status .查看当前所有连接的详细资料: mysqladmin -uroot -proot processlist D:\MySQL\bin>mysqladmin -uroot -proot processlist| Id | User

  • MySQL关联查询优化实现方法详解

    MySQL关联查询优化实现方法详解

    目录 左外连接 内连接INNER JOIN 我们准备如下两个表,并插入数据. #分类 CREATE TABLE IF NOT EXISTS `type` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); #图书 CREATE TABLE IF NOT EXISTS `book` ( `bookid` INT(10) UNSIGNED NO

  • zabbix监控MySQL主从状态的方法详解

    搭建MySQL主从后,很多时候不知道从的状态是否ok,有时候出现异常不能及时知道,这里通过shell脚本结合zabbix实现监控并告警 一般情况下,在MySQL的从上查看从的运行状态是通过Slave_IO_Running线程和Slave_SQL_Running线程是否ok,通过命令"show slave status\G;"即可查看.所以这里根据这两个值进行判断. agent端脚本编写及配置 说明:所有zabbix相关的脚本我都放在了/etc/zabbix/script/ 目录里面,下

  • MySQL联合查询实现方法详解

    MySQL联合查询实现方法详解

    联合查询简单说 就是将两次查询合并在一起 例如 我们这里有一个用户表 我们先编写一段SQL select name from staff where age > 21; 查询年龄大于21的 输出结果如下 然后我们再写一段sql select name from staff where status =1; 查询 status 状态字段等于1 的 输出效果如下 然后我们可以二合一一下 select name from staff where age > 21 union all select n

  • MySQL约束与索引概念详解

    目录 一.关系型数据库设计规则 二.数据完整性和约束与索引的概念 三.约束的应用 一.关系型数据库设计规则 遵循ER模型和三范式 E entity 代表实体的意思 对应到数据库当中的一张表 R relationship 代表关系的意思 三范式: 1.列不能拆分 2.唯一标识 3.关系引用主键 具体体现 将数据放到表中,表再放到库中. 一个数据库中可以有多个表,每个表都有一个名字,用来标识自己.表名具有唯一性. 表具有一些特性,这些特性定义了数据在表中如何存储,类似java和python 中 “类

  • MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将

  • mysql添加索引方法详解(Navicat可视化加索引与sql语句加索引)

    mysql添加索引方法详解(Navicat可视化加索引与sql语句加索引)

    目录 使用索引的场景: 下面是通过sql语句添加索引的方法: 1.普通索引 1).直接创建索引 2).修改表结构的方式添加索引 3).删除索引 2.唯一索引 1).创建唯一索引 2).修改表结构 3.主键索引 4.组合索引 5.全文索引 1).创建表的适合添加全文索引 2).修改表结构添加全文索引 3).直接创建索引 总结 使用索引的场景: 阿里云日志里出现了慢sql 然后发现publish_works_id字段会经常用于一些关联,所以决定把这个字段加上索引,优化sql 可视化navicat操作

  • MySQL主从数据库搭建方法详解

    本文实例讲述了MySQL主从数据库搭建方法.分享给大家供大家参考,具体如下: 主从服务器是mysql实时数据同步备份的一个非常好的方案了,现在各大中小型网都都会使用mysql数据库主从服务器功能来对网站数据库进行异步备份了,下面我们来给大家介绍主从服务器配置步骤. Mysql的主从复制至少是需要两个Mysql的服务,当然Mysql的服务是可以分布在不同的服务器上,也可以在一台服务器上启动多个服务. (1)首先确保主从服务器上的Mysql版本相同 (2)在主服务器上,设置一个从数据库的账户,使用R

  • MySQL延时复制库方法详解

    简单的说延迟复制就是设置一个固定的延迟时间,比如1个小时,让从库落后主库一个小时. MySQL延时复制库作用 存在即合理,延迟复制可以用来解决以下三类问题:  1.用来在数据库误操作后,快速的恢复数据. 比如有人误操作的表,那么这个操作在延迟时间内,从库的数据并没有发生变化,可以用从库的数据进行快速恢复. 把服务停了,把从库的表直接导入主库,补binlog,binglog不能恢复表  2.用来做延迟测试 比如你做好的数据库读写分离,把从库作为读库,那么你想知道当数据产生延迟的时候到底会发生什么.

随机推荐