V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
ben548
V2EX  ›  MySQL

一个 MySQL 主键索引参与排序的问题

  •  
  •   ben548 · 2023-06-07 04:09:19 +08:00 via Android · 1546 次点击
    这是一个创建于 522 天前的主题,其中的信息可能已经有所发展或是发生改变。
    问一个 mysql 索引的问题:
    背景信息:test 表上 a 字段有 btree 索引
    有这样一个查询语句:
    select * from test where a = 1 order by b desc
    有以下两种场景:
    场景一:当 b 是普通索引的时候,explain 结果会使用到 using filesort 这样的关键字(为了不进行 filesort ,需要对 a 和 b 两个字段建立联合索引);
    场景二:b 是主键索引的时候 explain 的结果确是 using where ;
    我的理解:
    场景一是符合预期的,因为在 a 的 btree 索引上并不能保证 b 字段的有序性,所以会使用到 filesort ;
    场景二我理解是因为 a 字段的 btree 索引本身就是带有主键 id 这个信息的(理解是为了回表用的)
    但是我不太理解的是,为什么这里的主键 id 排序不需要走 filesort ,是否可以理解为表中任意字段的 btree 索引其实等价于字段索引+主键索引的组合索引,如果这么理解的话是可以解释的通的,我不太确定的是内部机制是否真的是这样实现的?还是说依赖了主键索引其他方面的能力才导致不需要走 filesort ?
    11 条回复    2023-06-07 20:09:36 +08:00
    JKeita
        1
    JKeita  
       2023-06-07 09:14:37 +08:00
    id 索引本来就有序啊
    doraf
        2
    doraf  
       2023-06-07 09:27:29 +08:00
    我认为你的理解是对的。

    这个页面 https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html 上说,

    Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
    realpg
        3
    realpg  
       2023-06-07 09:35:44 +08:00
    我记得 innodb 的普通索引机制是利用了主键的
    awalkingman
        4
    awalkingman  
       2023-06-07 09:38:40 +08:00
    “是否可以理解为表中任意字段的 btree 索引其实等价于字段索引+主键索引的组合索引” ==> 是的,所有 btree 索引都可以认为是 目标索引后面加个 id (主键)的 联合索引。
    zjq07
        5
    zjq07  
       2023-06-07 09:49:14 +08:00   ❤️ 1
    所有的非聚簇索引,都可以认为是(索引列+主键 id )的联合索引
    awalkingman
        6
    awalkingman  
       2023-06-07 09:51:05 +08:00
    @zjq07 正解,我的表述不准确
    opengps
        7
    opengps  
       2023-06-07 10:11:11 +08:00
    @JKeita mysql 的 id 不一定是聚集的,之所以平常见到的 id 是聚集的,原因在于 id 放在第一列上。
    JKeita
        8
    JKeita  
       2023-06-07 14:42:53 +08:00   ❤️ 1
    @opengps mysql 主键索引不就是聚簇索引,跟哪一列没关系吧。。。
    opengps
        9
    opengps  
       2023-06-07 14:45:48 +08:00
    @JKeita 是我记错了,查了下是没有设置为主键时候才是第一列聚集索引。聚集索引决定物理存放顺序。
    nothingistrue
        10
    nothingistrue  
       2023-06-07 15:34:07 +08:00   ❤️ 1
    Mysql InnoDB 引擎下,有两个特殊性:一,主键索引是聚集索引,主键索引就是最终数据存储;二,基于一,其他索引指向的目的地是主键,而非通常的隐藏 rownum 。

    对于特殊性一,因为主键索引就是全部数据,而你只用主键做排序条件,主键索引又是 btree 的天然具有顺序性,所以就无需做排序了,直接在主键索引上筛选数据就够了。这就是你 explain 只看到 using where 的原因。

    对于特殊性二,a 字段的 btree 索引上,索引值就是 b 字段的值,故全部查询内容都在这个索引上,可以直接在这个索引上做查询和排序,也不用回落数据文件。当然这个并没有被采用,因为用特殊性一的处理会更快。
    iosyyy
        11
    iosyyy  
       2023-06-07 20:09:36 +08:00
    对于 innodb 来说他的数据和 id 是存在一起的 btree 底层具有排序信息所以不需要额外对 id 排序 也就是不需要走 filesort
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2663 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 53ms · UTC 04:01 · PVG 12:01 · LAX 20:01 · JFK 23:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.