合适的索引顺序

一.前言

正确的顺序依赖于使用索引的查询,并且同时需要考虑如何更好地满足排序分组的需要。因为哈希或者其他类型的索引并不会像 B-Tree索引一样顺序存储数据,所以这里只针对B-Tree展开讨论。

二.合适的索引顺序

1. 概念

对于如何选择索引顺序有一个经验法则: 将选择性最高的列放在索引最前列。当不需要考虑排序分组时,将选择性最高的列放在前面通常是很好的,这时索引的作用只是用于优化 Where 条件的查找。在这种情况下,这样设计的索引确实能够最快地过滤出需要的行,对于在Where子句中只使用了索引前缀列的查询来说性能也更高。然而,性能不只是依赖于索引索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值分布有关。

2. 案例

下面有个查询:

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

那按照平时的最左前缀原则,我们是不是建一个(staff_id,customer_id)的索引合适呢?

其实不然,我们可以跑一些查询来确定在这个表中值的分布情况,并确定哪个列的选择性更高。

2.1. 查看Where条件分支对应的基数有多大
SELECT SUM(staff_id=2),SUM(customer_id=584) FROM payment;

在这里插入图片描述

根据前面的经验法则,应该将所有列customer_id 放在前面,因为对应条件值的 customer_id数量更小。我们在来看看对于这个customer_id 的条件值,对应的staff_id 列的选择性如何:

SELECT SUM(staff_id=2) FROM payment WHERE customer_id=584

在这里插入图片描述
这样做有一个地方需要注意,查询的结果非常依赖于选定的具体值。如果按上述方法优化,可能对其他一些条件值的查询不公平,服务器的整体性可能变得更糟,或者其他某些查询的运行变得不如预期。

2.2. 按照全局基数和选择性

如果是从诸如 pt-query-digest这样的工具的报告中提取"最差"查询,那么再按上述方法选定的索引顺序往往是非常高效的。如果没有类似具体的查询类运行,那么最好还是按经验法则来做,因为经验法则考虑的是全局基数和选择性

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,COUNT(*) FROM payment;

在这里插入图片描述

从上面的结果也可以看出 customer_id 的选择性更高,所以答案是将其作为索引列的第一列:

ALTER TABLE payment ADD KEY(customer_id, staff_id);

3. 选择性不高的索引列

当使用前缀索引的时候,在某些条件值的基数比正常值高的时候,问题就来了。例如,在某些应用程序中,对于没有登录的用户,都将其用户名记录为"guest",在记录用户行为的会话(session)表和其他记录用户活动的表中"guset"就成为一个特殊的ID。一旦涉及到这个查询,可能性能就会大不相同。

下面给出一个真实案例,在一个用户分享购买商品和购买经验的论坛上,这个特殊表上的查询运行得非常慢:

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE FROM Message
WHERE (groupId=10137) AND (userId=1288826) AND (anoymous=0)
ORDER BY priority DESC, modifiedDate DESC

执行计划的结果:
在这里插入图片描述

看上去已经建立了索引(groupId,userId),如果不考虑列的基数,这看起来是一个非常合理的选择,但如果考虑一下userId 和groupId条件匹配的行数,可能会有不同的想法了:

SELECT COUNT(*), SUM(groupId=10137),SUM(userId=1288826),
SUM(anoymous=0) FROM Message

在这里插入图片描述
从上面的结果可以看出符合组(groupId)条件几乎满足表中的所有行,符合用户(userId)条件有130万条记录——也就是说索引基本上没什么作用。这里从索引层面上已经解决不了问题,我们可以从业务上区分这类特殊用户和组,禁止针对这类用户和组执行这个查询。

三.总结

  • 从上面的案例可以看到经验法则和推论在多数情况下是有用的,但注意不要假设平均情况下的性能也能代表特殊情况下的性能,特殊情况可能会摧毁整个应用的性能。
  • 最后,尽管关于选择性技术的经验法则值得去研究和分析,但Where子句中的排序,分组范围条件等其他因素对查询的性能也会造成非常大的影响。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/329038.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

【驱动】TI AM437x(内核调试-06):网卡(PHY和MAC)、七层OSI

1、网络基础知识 1.1 七层OSI 第一层:物理层。 1)需求: 两个电脑之间如何进行通信? 具体就是一台发比特流,另一台能够收到。于是就有了物理层:主要是定义设备标准,如网线的额接口类型、管线的接口类型、各种传输介质的传输速率等。它的主要作用是传输比特流,就是从1/0…

C++设计模式(李建忠)笔记1

C设计模式(李建忠) 本文是学习笔记,如有侵权,请联系删除。 参考链接 Youtube: C设计模式 Gtihub源码与PPT:https://github.com/ZachL1/Bilibili-plus 豆瓣: 设计模式–可复用面向对象软件的基础 文章目录 C设计模…

WSL deepin的开荒之路

WSL deepin的开荒之路 问题1:sudo apt-get install ***报错无法定位包(Unable to locate package)问题2:如果在子系统中访问windows下的其他分区 windows11安装deepin直通车https://editor.csdn.net/md/?articleId135648217 问题1:sudo apt…

企业网盘:实现文件共享与协同办公的利器

企业网盘无疑是当下热门的信息管理工具,集存储、管理和协作功能于一体,以其高性价比、便捷易用、高效安全等特质,捕获各行各业的青睐。一跃成为2023年度大热的企业工具之一。 那么企业网盘究竟有何种魅力呢?换而言之,对…

解析Transformer模型

原文地址:https://zhanghan.xyz/posts/17281/ 进入Transformer RNN很难处理冗长的文本序列,且很容易受到所谓梯度消失/爆炸的问题。RNN是按顺序处理单词的,所以很难并行化。 用一句话总结Transformer:当一个扩展性极佳的模型和一…

STM32——ADC知识总结及多通道采样实验

1.ADC概念 ADC,全称:Analog-to-Digital Converter,指模拟/数字转换器 2 STM32各系列ADC的主要特性 3.F4框图 4.转换序列与转换时间 A/D转换被组织为两组:规则组(常规转换组)和注入组(注入…

JNI笔记

JNI笔记 背景Demo代码JNI.javaMainActivity.javaAndroid.mkApplication.mkcom_stone_javacallc_JNI.hjavacallc.cbuild.gradle 背景 Demo代码 代码结构 JNI.java package com.stone.javacallc;/*** Created by stoneWang* Created on 2024/1/16* java调用C*/ public class …

mysql常见的需求,对于关键字的使用

如何使用MySQL将列数据转化为逗号分隔的形式。我们可以使用内置函数GROUP_CONCAT()来实现这个功能 如何使用MySQL将列数据转化为逗号分隔的形式。我们可以使用内置函数GROUP_CONCAT()来实现这个功能,也可以根据实际需求自定义一个函数。这种技术在一些需要对数据进…

架构师之超时未支付的订单进行取消操作的几种解决方案

今天给大家上一盘硬菜,并且是支付中非常重要的一个技术解决方案,有这块业务的同学注意自己尝试一把哈! 一、需求如下: 生成订单30分钟未支付,自动取消 生成订单60秒后,给用户发短信 对上述的需求,我们给…

leetcode—矩阵

1 矩阵置零 给定一个 m x n 的矩阵,如果一个元素为 0 ,则将其所在行和列的所有元素都设为 0 。请使用 原地 算法。 示例 1: 输入:matrix [[1,1,1],[1,0,1],[1,1,1]] 输出:[[1,0,1],[0,0,0],[1,0,1]] 方法一&#xf…

知识库建设教程来啦,赶紧收藏起来

播种知识,收获效率。知识库,这个企业内部服务的“大百科”,可能是你下一步需要建立的重要工具哦!今天,就让我们一起来看一下如何进行知识库的建设和维护。 首先,让我们理解一下知识库的定义。知识库就像是一…

探索Python数据结构与算法:解锁编程的无限可能

文章目录 一、引言1.1 数据结构与算法对于编程的重要性1.2 Python作为实现数据结构与算法的强大工具 二、列表和元组2.1 列表:创建列表、索引、切片和常用操作2.2 元组:不可变序列的特性和使用场景 三、字符串操作和正则表达式3.1 字符串的常见操作和方法…

第36期 | GPTSecurity周报

GPTSecurity是一个涵盖了前沿学术研究和实践经验分享的社区,集成了生成预训练Transformer(GPT)、人工智能生成内容(AIGC)以及大型语言模型(LLM)等安全领域应用的知识。在这里,您可以…

c语言-库函数strstr()、strtok()、strerror()介绍

文章目录 前言一、库函数strstr()1.1 strstr()介绍1.2 strstr()模拟实现 二、库函数strtok()2.1 strtok()介绍 三、库函数strerror()3.1 strerror()介绍 总结 前言 本篇文章介绍c语言库函数strstr()、strtok()、strerror()的使用。 一、库函数strstr() 1.1 strstr()介绍 str…

Linux/Networked

Enumeration nmap 网站更新之后有了一个引导模式,更利于学习了,之前看ippsec的视频,要不总是没有思路,现在出现的问题多了提示也更多了,还没有使用,一会用用再说 首先,第一个问题是“目标上正…

RocketMQ源码阅读-Producer消息发送

RocketMQ源码阅读-Producer消息发送 1. 从单元测试入手2. 启动过程3. 同步消息发送过程4. 异步消息发送过程5. 小结 Producer是消息的生产者。 Producer和Consummer对Rocket来说都是Client,Server是Broker。 客户端在源码中是一个单独的Model,目录为rock…

WordPress后台仪表盘自定义添加删除概览项目插件Glance That

成功搭建WordPress站点,登录后台后可以在“仪表盘 – 概览”中看到包括多少篇文章、多少个页面、多少条评论和当前WordPress版本号及所使用的主题。具体如下图所示: 但是如果我们的WordPress站点还有自定义文章类型,也想在概览中显示出来应该…

《计算机视觉处理设计开发工程师》

计算机视觉(Computer Vision)是一门研究如何让计算机能够理解和分析数字图像或视频的学科。简单来说,计算机视觉的目标是让计算机能够像人类一样对视觉信息进行处理和理解。为实现这个目标,计算机视觉结合了图像处理、机器学习、模…

我的年终总结2023

As a DBA 从2023年初开始,我就给自己定下了23年的主要任务——学习PostgreSQL数据库。虽然没有定下细致的计划,但总体的目标是把PG的一些基础知识学完。后来发现我想简单了,学习PG的成本比我想象的多的多,导致23年这个目标没有完…

【CSP】2023年12月真题练习(更新到202312-2)

试题编号:202312-1试题名称:仓库规划时间限制:1.0s内存限制:512.0MB问题描述: 问题描述 西西艾弗岛上共有 n 个仓库,依次编号为 1⋯n。每个仓库均有一个 m 维向量的位置编码,用来表示仓库间的物…