mysql(六)多列索引之索引顺序问题

这篇具有很好参考价值的文章主要介绍了mysql(六)多列索引之索引顺序问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

使用索引常见的错误是

  • 为每列创建单独的索引,
  • 或者按照错误的顺序创建多列索引

多列索引

多列索引,是指在创建索引时所关联的字段不是一个字段,而是多个字段,虽然可以通过所关联的字段进行查询,但是只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用

多列索引时顺序问题

平时我们遇到的比较容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要(针对B-Tree索引

在一个多列B-Tree索引中,索引列的顺序意味着
索引首先按照最左列进行排序,其次是第二列,等等。所以,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的 ORDER BY,GROUP BY和DISTINCT等字句的查询需求。

所以多列索引的顺序至关重要。在Lahdenmaki和Leach的“三星索引”系统中,列顺序也决定了一个索引是否能够成为一个真正的“三星索引”

对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引的最前列。这个建议有用吗?在某些场景可能有帮助,但通常不如避免随机IO和排序那么重要,考略问题需要更全面(场景不同则选择不同,没有一个放之四海皆准的法则。这里只能说明,这个经验法则没有你想的那么重要)

当不需要考虑排序和分组时,将选择性最高的列放在前面。这个时候索引的作用只是用于优化WHERE条件的查找。在这种情况下,这样的设计的索引确实能够最快的过滤出需要的行,对于在WHERE子句中只使用了索引部分前缀的列的查询来说选择性也更高。然而,性能不只是依赖于所有索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值的分布有关。这和选择前桌的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下的索引的选择性最高

演示示例

新建表

CREATE TABLE `payment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `staff_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `money` decimal(20,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=169682 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

随机插入数据

此时有如下查询:

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

此时是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序?

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

先使用以下查询预测一下,看看各个WHERE条件分支应对的数据基数有多大

mysql> select SUM(staff_id=2),SUM(customer_id=584) from payment;
+-----------------+----------------------+
| SUM(staff_id=2) | SUM(customer_id=584) |
+-----------------+----------------------+
|          219555 |                   30 |
+-----------------+----------------------+
1 row in set (0.08 sec)

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

mysql> select sum(staff_id = 2) from payment where customer_id = 584;
+-------------------+
| sum(staff_id = 2) |
+-------------------+
|                17 |
+-------------------+
1 row in set (0.05 sec)

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

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

mysql> select count(distinct staff_id)/count(*) as staff_id_selectivity,count(distinct customer_id)/count(*) as customer_id_selectivity,count(*) from payment;
+----------------------+-------------------------+----------+
| staff_id_selectivity | customer_id_selectivity | count(*) |
+----------------------+-------------------------+----------+
|               0.0001 |                  0.0373| 15543109 |
+----------------------+-------------------------+----------+
1 row in set (1 min 27.19 sec)

customer_id的选择性更高,所以答案是将其作为索引列的第一列:

ALTER TABLE payment ADD KEY(customer_id,staff_id);

当使用前缀索引的时候,在某些条件值的基数比正常值高的时候,问题就来了。例如,在某些应用程序中,对于没有登录的用户,都将其用户名记录为“guest”,在记录用户行为的会话表和其他记录用户活动的表中“guest”就成为了一个特殊用户ID,一旦涉及这个用户,那么和对于正常用户的查询就大不同了,因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号,和普通账号不同,他并不是一个具体的用户,系统中所有的其他用户都是这个用户的好友,所以系统往往通过他向网站的所有用户发送状态通知和其他消息。这个账号的巨大好友列表很容易导致网站出现服务器性能问题。

这实际上是一个非常典型的问题。任何的异常用户,不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题,那些拥有大量好友,图片,状态,收藏的用户,也会有前面提到的系统账号同样的问题

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

select count(distinct threadId) as count_value from Message where (group_id = 10137) and (userId = 1288826) and (anonymous = 0) order by priority desc,modifiedDate desc

这个查询看似没有建立合适的索引,所以客户咨询我们是否可以优化。EXPLAIN的结果如下:

         id:1
select_type:SIMPLE
      table:Message
       type:ref
        key:ix_groupId_userId
    key_len:18
        ref:const,const
       rows1251162
      Extra:Using where

MySQL 为这个查询选择了索引(groupId,userId),如果不考虑列的基数,这看起来是一个非常合理的选择。但如果考虑一下 user ID 和 group ID 条配的行数,可能就会有不同的想法了 :

mysql> SELECT COUNT(*),SUM(groupId = 10137),SUM(userId = 1288826),SUM(anonymous = 0) FROM Messagel
+----------------------+-------------------------+----------+----------+----------+
|             count(*) |     sum(groupId =10137) | sum(userId=1288826) |sum(anonymous = 0) |
+----------------------+-------------------------+----------+----------+----------+
|              4142217 |                 4092654 |             1288496 |           4141934 |
+----------------------+-------------------------+----------+----------+----------+
1 row in set (1 min 27.19 sec)

从上面的结果来看符合组 (groupId) 条件几乎满足表中的所有行,符合用户(userId)条件的有 130 万条记录一一也就是说索引基本上没什么用。因为这些数据是从其他应用中迁移过来的,迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码,区分这类特殊用户和组,禁止针对这类用户和组执行这个查询

从这个小案例可以看到经验法则和推论在多数情况是有用的,但要注意不要假设平均情况下的性能也能代表特殊情况下的性能,特殊情况可能会摧毁整个应用的性能。

最后,尽管关于选择性和基数的经验法则值得去研究和分析,但一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素,这些因素可能对查询的性能造成非常大的影响。文章来源地址https://www.toymoban.com/news/detail-606417.html

到了这里,关于mysql(六)多列索引之索引顺序问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL数据库索引机制

    MySQL是一款有客户端和服务端的网络应用,mysql是它的客户端,mysqld是它的服务端。服务端本质就是一个进程,它存在于内存当中。而我们存储在MySQL中的数据是保存在磁盘上的,当我们对MySQL中数据进行增删查改操作时,不可能是直接在磁盘上进行操作,而是将对应的数据加

    2024年02月12日
    浏览(72)
  • MySQL数据库索引的数据结构

    数据库索引的功能就是让查找更加的高效,所以索引的数据结构应该是能够加速查找的数据结构。 MySQL的innoDB存储引擎的索引的数据结构就是多叉搜索树中的b+树,这可以说是为索引量身定做的一个数据结构。 首先,索引可以通过主键,unique修饰创建,也可以直接使用sql语句

    2024年02月10日
    浏览(53)
  • 简单认识MySQL数据库索引

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 ●索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。 ●使用索引后可以不用扫描全表来定位某行的

    2024年02月16日
    浏览(65)
  • 【MySQL数据库 | 第十七篇】索引以及索引结构介绍

    目录 前言: 索引简介:  索引结构:           二叉树索引结构         Tree(普通二叉树)         B-Tree(多路平衡查找树)         B+Tree          哈希索引数据结构 总结: 在实际生活中,我们对SQL语句进行优化实际上有很大一部分都是对索引进行优化,因此对索引

    2024年02月09日
    浏览(69)
  • 【MySql系列】深入解析数据库索引

    MySQL索引是数据库中一个关键的概念,它可以极大地提高查询性能,加快数据检索速度。但是,要充分发挥索引的作用,需要深入理解它们的工作原理和使用方式。 在本文中,我们将深入解析MySQL索引,探讨它们的重要性、类型、创建、维护以及最佳实践。 在数据库中,索引

    2024年02月08日
    浏览(71)
  • MySQL数据库索引的种类、创建、删除

    目录 一:MySQL 索引 1、MySQL 索引介绍 2、 索引的作用  3、索引的副作用 4、 创建索引的原则依据  二、索引的分类和创建 1、 普通索引 (1) 直接创建索引 (2) 修改表方式创建 (3) 创建表的时候指定索引 2、 唯一索引 (1) 直接创建唯一索引 (2) 修改表方式创建

    2024年02月09日
    浏览(175)
  • 【Mysql系列】——详细剖析数据库“索引”【上篇】

        😎博客昵称:博客小梦 😊最喜欢的座右铭:全神贯注的上吧!!! 😊作者简介:一名热爱C/C++,算法,数据库等技术、喜爱运动、热爱K歌、敢于追梦的小博主! 😘博主小留言:哈喽! 😄各位CSDN的uu们,我是你的博客好友小梦,希望我的文章可以给您带来一定的帮

    2024年02月02日
    浏览(54)
  • 数据库索引面试的相关问题

    查看索引的执行计划 索引失效的情况 1、索引列上做了计算,函数,类型转换等操作。索引失效是因为查询过程需要扫描整个索引并回表。代价高于直接全表扫描。 Like匹配使用了前缀匹配符“%abc” 字符串不加引号导致类型转换。 原因: 常见索引的优化的方法 1、前缀索引

    2024年02月22日
    浏览(40)
  • B+树:MySQL数据库索引的实现

    作为一个软件开发工程师,你对数据库肯定再熟悉不过了。作为主流的数据存储系统,它在我们的业务开发中,有着举足轻重的地位。在工作中,为了加速数据库中数据的查找速度,我们常用的处理思路是,对表中数据创建索引。那你是否思考过,数据库索引是如何实现的呢

    2024年02月09日
    浏览(58)
  • MySQL数据库的ID列添加索引

    要为MySQL数据库的ID列添加索引,可以使用以下语法: 其中, table_name 是要添加索引的表名, index_name 是索引的名称, id 是要添加索引的列名。 例如,如果要为名为 users 的表的 id 列添加索引,可以执行以下语句: 这将在 users 表的 id 列上创建名为 idx_id 的索引。 需要注意的

    2024年02月07日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包