再谈SQL优化:组合索引

原创 2016年06月01日 00:23:23

对于任何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。如果使用的键仅仅匹配少量行,该联接类型是不错的。

版权声明:本文为博主原创文章,未经博主允许不得转载。

复合索引性能问题初探

在《品悟性能优化》一书,4.4.3章节里介绍了复合索引的两个特点:前缀性,可选性。何为前缀性,该书阐述为排除skip scan index的情况,约束条件如果不包含复合索引的第一列,则该复合索引不会被...

数据库命名规范--通用

分段式的
  • houseq
  • houseq
  • 2014年09月22日 17:56
  • 7602

AND_EQUAL和组合索引优化一条SQL

在系统负载高的时候抓出一条SQL,1分23秒左右出152行 select a.Case_ID, a.Case_Type_ID, a.Case_Subject, ...

10gen工程师谈MongoDB组合索引的优化

A. Jesse Jiryu Davis —— 10gen工程师,从事MongoDB、Python及Tornado。在Dzone上分享了MongoDB中组合索引的最佳建立方法以及索引中字段的最优顺序。...
  • shmnh
  • shmnh
  • 2014年12月14日 03:05
  • 387

创建组合索引SQL从1个多小时到1S的案例

select aa.acct_org, aa.loan_acct_no, aa.FUNCTIONARY, aa.cust_no, sum(dwm.pkg_tools.currcdtran(bb.IN...

MongoDB组合索引的优化

索引在查询中占的地位无疑是重中之重,因此建立一个好的索引对查询性能的影响也是立竿见影。来自10gen工程师A. Jesse Jiryu Davis带来的MongoDB上索引的优化方法以及MongoD...

MongoDB组合索引的优化

A. Jesse Jiryu Davis —— 10gen工程师,从事MongoDB、Python及Tornado。在Dzone上分享了MongoDB中组合索引的最佳建立方法以及索引中字段的最优顺序。...

单键索引和组合索引选择及优化

转自:http://blog.csdn.net/linminqin/article/details/44342205 这篇文章非常不错,清晰的解释了索引的选择和优化,而且优化方法很常用,可以直接应用...

[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序

写在前面的话: 之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味...

[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序

http://my.oschina.net/zhengyun/blog/162985 写在前面的话: 之前曾说过“不要求每个人一定理解 联表查询(join/left j...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:再谈SQL优化:组合索引
举报原因:
原因补充:

(最多只允许输入30个字)