mysql-使用联合索引

栏目: 数据库 · 发布时间: 6年前

内容简介:项目使用mysql数据库,某张表数据量2000W左右,比较大,在组合查询时发生超时,需要优化。查询优化常用方法就是建立索引查询语句样例:查询在20181001-20181010时间范围内A=a且B=b的数据

背景

项目使用 mysql 数据库,某张表数据量2000W左右,比较大,在组合查询时发生超时,需要优化。

优化

查询优化常用方法就是建立索引

查询语句样例:查询在20181001-20181010时间范围内A=a且B=b的数据

select * from table where A = 'a' and B = 'b' and Date > '20181001' and Date < '20181010'

按照查询建立了一个组合索引 idx_query(A, B, Date),查询仍然报超时。然后开始研究索引原理,最后更改索引中列的顺序为idx_query(B,A,Date)解决。

---------------------------------------------------------------------------------------------

上面只是简单的使用情况,下面从原理细细解读索引

索引原理

首先,出问题以后再建立索引,是一种不好的习惯。排查、定位、解决问题会耗费很多开发时间和精力。

当然,不充分思考就给大多数列建立单列索引,给所有查询建立联合索引,是一种更坏的习惯。毕竟删除索引风险远高于添加索引。

使用索引需要成本:

  • 空间成本:索引是占空间的,大表的联合索引占用空间不可忽略
  • 时间成本:使用不对的索引会浪费时间
  • 更新成本:增删改操作只要跟索引有关系,就需要更新索引

索引适合在返回大表中很小一部分数据时使用,很小当然越小越好,5%以下还可以,1%以下更好,如果表的数据量非常大,千万级别,返回行数要小道0.1%以下

从很大表中用联合条件查询出很小的数据,等价于count(distinct 联合条件) 的值很大,这个值被称为Cardinality,索引适合度=Cardinality/表记录数。

索引误区

多个单列索引(如idx_A, idx_B)不等同于联合索引(idx_A_B)

两个联合索引里的列顺序不同,这两个联合索引也不等价,适用场景不同

联合索引idx_A_B能够覆盖的场景

  • A = ? and B = ?
  • A = ? and B in (?, ?, ?)
  • A = ?
  • A in (?,?,?)

覆盖场景可以根据索引存储结构(B+Tree)推导出。

上面罗列的集中覆盖场景,效率由上至下越来越低,因为扫描行数越来越大

联合条件中,最左侧的索引列很重要,最好使用Cardinality最大的列。

总结

项目中遇到的问题,最终根据索引原理中 最左侧索引列使用Cardinality最大列 解决。


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

D3.js in Action

D3.js in Action

Elijah Meeks / Manning Publications / 2014-3 / USD 44.99

Table of Contents Part 1: An Introduction to D3 1 An introduction to D3.js 2 Information Visualization Data Flow 3 D ata-Driven Design and Interaction Part 2: The Pillars of Information......一起来看看 《D3.js in Action》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

HTML 编码/解码
HTML 编码/解码

HTML 编码/解码