`
zhengyun_ustc
  • 浏览: 82875 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

阅读更多
写在前面的话:
  1. 之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须了解;
  2. 重复上一次的话把mysql客户端(如SQLyog,如HeidiSQL)放在桌面上,时不时拿出来 explain 一把,这是一种美德
  • 确保亲手查过SQL的执行计划,一定要注意看执行计划里的 possible_keys、key和rows这三个值,让影响行数尽量少,保证使用到正确的索引,减少不必要的Using temporary/Using filesort;
  • 不要在选择性非常差的字段上建索引,原因参见优化策略A;
  • 查询条件里出现范围查询(如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 索引最佳实践之问题反馈
3)orczhou,2013,index merge的补充说明
5)nocode,2013,MySQL Internals-Index Merge优化
 

赠图1枚:
http://ww2.sinaimg.cn/bmiddle/7cc829d3jw1e8qihxzpebg20af07yx6p.gif
0
1
分享到:
评论

相关推荐

    mysql查询优化之索引优化

    MySQL查询优化是数据库管理中的关键环节,特别是在大数据量的场景下,索引优化能显著提升查询性能。本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先,了解索引的基本概念至关重要。索引是数据库为了快速...

    自定义证书查询,可以增加修改字段,和自定义查询条件

    然而,在自定义查询中,用户可以根据业务需求,自由选择需要查询的字段,比如证书编号、颁发机构、有效期等,并且可以设置各种条件,如等于、不等于、大于、小于等,甚至可以组合多个条件进行高级查询。 其次,增加...

    慢SQL优化In PostgreSQL

    但需要注意的是,索引的选择性对于提升性能至关重要。例如,如果`WHERE`条件是`update_flag = false`,而`update_flag`字段大多数情况下为`false`,那么即使创建了该索引,其效果可能与顺序扫描(SeqScan)相差无几...

    百万数据查询优化海量数据查询优化

    - **选择性**:在经常用于连接、排序或分组的列上创建索引,对于不同值较少的列,创建索引反而可能降低更新速度。 - **复合索引**:在多个列上创建复合索引可以进一步提升查询效率,特别是对于组合查询条件。 - *...

    oracle实现多字段匹配一个关键字查询

    在Oracle数据库中,有时我们需要对多个字段进行联合搜索,即多字段匹配一个关键字查询。本文将详细介绍两种在Oracle中实现...同时,考虑到性能因素,建议在设计查询时尽量优化SQL语句,并合理利用索引等数据库特性。

    Postgres查询优化方案

    8. **慢查询日志**:启用慢查询日志,记录执行时间较长的查询,便于分析并针对性优化。 9. **硬件升级**:当软件优化达到一定限度,硬件升级也是提高性能的有效途径。例如,增加RAM、使用更快的SSD或优化网络配置等...

    处理group by 查询速度太慢的问题 数据量大.doc

    知识点2:索引字段的选择 在设置索引时,需要选择合适的字段。除了 Group By 字段外,还需要考虑聚合函数用到的字段。例如,在本实例中,需要将 log_time 字段设置为索引,因为 max 函数用到了这个字段。 知识点3...

    MySQL优化之索引选择性-并不是所有字段都适合加索引

    技术面试中讨论MySQL性能调优时,普遍答案聚焦于添加索引,但面对查询效率低的问题,深入分析较少触及“索引选择性”这一关键概念。 适用人群: 技术面试候选人,尤其是数据库开发与管理员。 需要了解MySQL性能...

    数据库索引设计和优化

    2. 复合索引:当多个列共同决定查询效率时,可以创建复合索引,即包含多个列的索引,顺序很重要,应根据查询条件选择最佳顺序。 3. 覆盖索引:如果查询只需要索引中的列,覆盖索引可以避免回表操作,提高查询速度。 ...

    《查询优化》实验报告

    在s表中删除原有的聚簇索引后,执行查询sno为99999的记录,未建立索引时查询耗时553ms,而建立sno上的索引后,查询时间降低至3ms。这表明,索引显著减少了数据查找的时间,因为无索引时需全表扫描,时间复杂度为线性...

    Sybase 数据库查询索引优化

    2. **索引设计**:考虑哪些字段应该被索引,以及索引的顺序。通常,经常出现在WHERE子句中的列、主键和外键是理想的选择。同时,索引列的基数(不同值的数量)也很重要,基数越大,索引效率越高。 3. **避免过度...

    MySQL索引原理及慢查询优化

    在这种情况下,由于`operate_time`通常用于范围查询,应该放在索引的最前列,接着是其他条件字段,如`status`, `operator_id` 和 `type`,根据它们在查询中的出现频率和选择性进行排列。 索引的维护和使用策略也是...

    mysql,查询,索引

    6. **注意关联字段类型一致**:确保连接操作中涉及到的字段类型一致,以提高查询性能。 #### 九、Explain命令 Explain命令可以用来分析SQL查询的执行计划,帮助我们理解数据库如何执行查询以及是否存在潜在的性能...

    数据库文件\数据库查询慢优化方法

    - **索引管理**:创建适合查询模式的索引,注意索引维护,适时重建和碎片整理。 - **并行处理**:在适合的情况下启用并行查询,分散CPU负载,但需注意并行操作的资源消耗。 - **避免全表扫描**:使用覆盖索引,避免...

    sql查询优化 查询优化

    在编写SQL语句时,应尽量避免将字段置于复杂的表达式中,因为这可能阻碍数据库优化器进行有效的优化。例如,`select * from teachers where salary/12=2000`不如`select * from teachers where salary=2000*12`高效...

    oracle查询优化

    - **避免索引失效**:确保查询条件与索引字段相匹配,避免使用函数或表达式对索引字段进行操作。 #### 三、简化查询结构 - **减少使用IN或OR**:当WHERE子句中包含IN或OR时,可能会影响索引的有效性。应尽量避免或...

    oracle数据查询慢如何优化.txt

    ### Oracle 数据查询慢的优化策略 在日常的数据库管理与维护工作中,经常会遇到Oracle数据库查询速度慢的问题。本文将从几个方面对Oracle查询调优进行详细的解析,包括但不限于SQL语句优化、索引管理和统计信息收集...

    Mysql索引优化实战.zip

    合理排列索引字段的顺序至关重要,应将选择性高的字段放在前面。 索引的维护也是不可忽视的。定期分析和优化索引可以发现冗余或未使用的索引,减轻数据库维护负担。使用`EXPLAIN`关键字可以查看查询的执行计划,...

    百万数据查询优化技巧三十则

    - **背景**:在创建索引时,需要注意索引的选择顺序,以确保数据库能够高效地利用索引。 - **实践**:在创建复合索引时,应当根据实际查询模式来决定字段的顺序,优先考虑最常用于筛选的字段。 #### 知识点十二:...

Global site tag (gtag.js) - Google Analytics