文档章节

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

旁观者-郑昀
 旁观者-郑昀
发布于 2013/09/22 10:06
字数 1510
阅读 594
收藏 24
写在前面的话:
  1. 之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须了解;
  2. 重复上一次的话:把mysql客户端(如SQLyog,如HeidiSQL)放在桌面上,时不时拿出来 explain 一把,这是一种美德
    • 确保亲手查过SQL的执行计划,一定要注意看执行计划里的 possible_keys、key和rows这三个值,让影响行数尽量少,保证使用到正确的索引,减少不必要的Using temporary/Using filesort;
  3. 不要在选择性非常差的字段上建索引,原因参见优化策略A;
  4. 查询条件里出现范围查询(如A>7,A in (2,3))时,要警惕,不要建了组合索引却完全用不上,原因参见优化策略B;
我们先回顾一下字段选择性的基础知识。

——字段选择性的基础知识——

引子:什么字段都可以建索引吗?

如下表所示,sort 字段的选择性非常差,你可以执行 show index from ads 命令可以看到 sort 的 Cardinality(散列程度)只有 9,这种字段上本不应该建索引:

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

ads

1

sort

1

sort

A

9

\N

\N


BTREE


 
优化策略A:字段选择性
  • 选择性较低索引 可能带来的性能问题
    • 索引选择性=索引列唯一值/表记录数;
    • 选择性越高索引检索价值越高,消耗系统资源越少;选择性越低索引检索价值越低,消耗系统资源越多;
  • 查询条件含有多个字段时,不要在选择性很低字段上创建索引
    • 可通过创建组合索引来增强低字段选择性和避免选择性很低字段创建索引带来副作用;
    • 尽量减少possible_keys,正确索引会提高sql查询速度,过多索引会增加优化器选择索引的代价,不要滥用索引;
再回顾组合索引与范围查询的业务场景。

——组合索引字段顺序与范围查询之间的关系——

引子:范围查询 city_id in (0,8,10) 能用组合索引 (ads_id,city_id) 吗?

举例,

ac 表有一个组合索引(ads_id,city_id)。

那么如下 ac.city_id IN (0, 8005) 查询条件能用到 ac表的组合索引(ads_id,city_id) 吗?

EXPLAIN

SELECT ac.ads_id

FROM ads,  ac

WHERE

      ads.id = ac.ads_id

      AND ac.city_id IN (0, 8005) 

      AND ads.status = 'online'

      AND ac.start_time<UNIX_TIMESTAMP()

      AND ac.end_time>UNIX_TIMESTAMP()

优化策略B:

由于 mysql 索引是基于 B-Tree 的,所以组合索引有“字段顺序”概念。

所以,查询条件中有 ac.city_id IN (0, 8005),而组合索引是 (ads_id,city_id),则该查询无法使用到这个组合索引。

DBA总结道:

组合索引查询的各种场景
兹有 Index (A,B,C) ——组合索引多字段是有序的,并且是个完整的BTree 索引。
  • 下面条件可以用上该组合索引查询:
    • A>5
    • A=5 AND B>6
    • A=5 AND B=6 AND C=7
    • A=5 AND B IN (2,3) AND C>5
  • 下面条件将不能用上组合索引查询:
    • B>5 ——查询条件不包含组合索引首列字段
    • B=6 AND C=7 ——查询条件不包含组合索引首列字段
  • 下面条件将能用上部分组合索引查询:
    • A>5 AND B=2 ——当范围查询使用第一列,查询条件仅仅能使用第一列
    • A=5 AND B>6 AND C=2 ——范围查询使用第二列,查询条件仅仅能使用前二列
 
组合索引排序的各种场景
兹有组合索引 Index(A,B)。
  • 下面条件可以用上组合索引排序:
    • ORDER BY A——首列排序
    • A=5 ORDER BY B——第一列过滤后第二列排序
    • ORDER BY A DESC, B DESC——注意,此时两列以相同顺序排序
    • A>5 ORDER BY A——数据检索和排序都在第一列
  • 下面条件不能用上组合索引排序:
    • ORDER BY B ——排序在索引的第二列
    • A>5 ORDER BY B ——范围查询在第一列,排序在第二列
    • A IN(1,2) ORDER BY B ——理由同上
    • ORDER BY A ASC, B DESC ——注意,此时两列以不同顺序排序
 
顺着组合索引怎么建继续往下延伸,请各位注意“索引合并”概念:
  • MySQL 5,0以下版本,SQL查询时,一张表只能用一个索引(use at most only one index for each referenced table),
  • 从 MySQL 5.0开始,引入了 index merge 概 念,包括 Index Merge Union Access Algorithm(多个索引并集访问),包括Index Merge Intersection Access Algorithm(多个索引交集访问),可以在一个SQL查询里用到一张表里的多个索引。
  • MySQL 在5.6.7之前,使用 index merge 有一个重要的前提条件:没有 range 可以使用。[出自参考资源2]
索引合并的简单说明:
  • MySQL 索引合并能使用多个索引
    • SELECT * FROM TB WHERE A=5 AND B=6
      • 能分别使用索引(A) 和 (B) 或 索引合并;
      • 创建组合索引(A,B) 更好;
    • SELECT * FROM TB WHERE A=5 OR B=6
      • 能分别使用索引(A) 和 (B) 或 索引合并;
      • 组合索引(A,B)不能用于此查询,分别创建索引(A) 和 (B)会更好;
最后的总结:
仍然是强调再强调:
记住,explain 后再提测是一种美德!
参考资源:
1)中文译稿,2013, MySQL 索引最佳实践之问题反馈
2)orczhou,2013, MySQL优化器:index merge介绍
3)orczhou,2013, index merge的补充说明
5)nocode,2013, MySQL Internals-Index Merge优化
 
赠图1枚:
转:喵了个咪的,老板催我去重构遗留的 Python 代码……
http://ww2.sinaimg.cn/bmiddle/7cc829d3jw1e8qihxzpebg20af07yx6p.gif

© 著作权归作者所有

旁观者-郑昀
粉丝 100
博文 77
码字总数 162700
作品 0
朝阳
私信 提问
加载中

评论(1)

underA
underA
支持一下,明天上班按照博主说的测一下MySQL中的索引
sql server中使用组合索引需要注意的地方

1、索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数),选择性越高索引的效果越好、价值越大,唯一索引的选择性最高; 2、组合索引中字段的顺序,选择性越高的字段排在最前面;如...

学习也休闲
2015/09/09
614
0
MySQL索引原则和慢查询优化步骤

建索引的几大原则 1.最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。 2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺...

乐在克里特
2018/04/06
55
0
一个不可思议的MySQL慢查分析与解决

转载自For DBA 一、前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 前段时间在优化的时候,已经在相应的查询条件上加上了索引 但是实际执行的SQL依然非常慢,为什么呢,我们来一步...

江大白
2018/09/10
0
0
通过Explain查询和分析SQL的执行计划

索引类型 在 MySQL 中,主要有四种类型的索引,分别为: B-Tree 索引, Hash 索引, Fulltext 索引和 R-Tree 索引。 Explain解释 可以通过Explain查询SQL的执行计划,例子如下: Explain ex...

underA
2016/12/03
335
0
PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)

标签 PostgreSQL , adhoc查询 , 大宽表 , 任意字段组合查询 , 索引 , btree , gin , rum 背景 大宽表,任意字段组合查询,透视。是实时分析系统中的常见需求: 1、实时写入。 2、实时任意字段...

德哥
04/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

爬虫可以采集哪些数据?爬虫借用什么代理可以提高效率

学习爬虫的门槛非常低,特别是通过Python学习爬虫,即使是网上也能找到许多学习爬虫的方法,而且爬虫在数据采集方面效果比较好,比如可以采集几万、上百万网页数据进行分析,带来极有价值的数...

xiaotaomi
24分钟前
4
0
redis自建笔记

自建redis笔记 最近在linux安装了一下redis,特做一些笔记! 本文先单节点启动redis,然后再进行持久化配置,在次基础上,再分享搭建主从模式的配置以及Sentinel 哨兵模式及集群的搭建 单节点...

北极之北
26分钟前
4
0
没想到Spring Boot居然这么耗内存,有点惊讶

Spring Boot总体来说,搭建还是比较容易的,特别是Spring Cloud全家桶,简称亲民微服务,但在发展趋势中,容器化技术已经成熟,面对巨耗内存的Spring Boot,小公司表示用不起。如今,很多刚诞...

程序员修BUG
29分钟前
4
0
Spring Security 实战干货:Spring Boot 中的 Spring Security 自动配置初探

1. 前言 我们在前几篇对 Spring Security 的用户信息管理机制,密码机制进行了探讨。我们发现 Spring Security Starter相关的 Servlet 自动配置都在spring-boot-autoconfigure-2.1.9.RELEASE...

码农小胖哥
31分钟前
4
0
Docker 容器时区时间不一致 问题解决

解决方案: 1,最傻瓜也最方便的处理方式,运行新的容器前设置本机时区和时间文件与容器的映射 docker run -v /etc/timezone:/etc/timezone -v /etc/localtime:/etc/localtime ...1 -v /etc/...

突突突酱
33分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部