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
sue0917
V2EX  ›  MySQL

MSYQL-取两个时间字段中的数据-索引如何建?

  •  
  •   sue0917 · 2021-04-14 16:05:05 +08:00 · 2003 次点击
    这是一个创建于 1303 天前的主题,其中的信息可能已经有所发展或是发生改变。
    如题,一张表中有一个开始时间字段 start_datetime,结束时间字段 end_datetime,
    需求是取出当前时间大于开始时间,小于结束时间内的数据

    索引应该如何建呢?
    17 条回复    2021-04-16 09:47:37 +08:00
    chenhua19940128
        1
    chenhua19940128  
       2021-04-14 16:08:23 +08:00
    建一个 start_datetime 和 end_datetime 的联合索引?
    zengxs
        2
    zengxs  
       2021-04-14 16:15:38 +08:00
    两个字段分别建一个索引不就行了
    uselessVisitor
        3
    uselessVisitor  
       2021-04-14 16:16:14 +08:00
    联合索引,但是要注意顺序。。
    sue0917
        4
    sue0917  
    OP
       2021-04-14 16:28:44 +08:00
    @chenhua19940128
    @zengxs
    @beichenhpy

    联合索引或者分别建立索引感觉用处都非常有限啊,老哥们。。


    比如开始结束时间都在今天前的,和开始结束时间都在今天后的,分别有 200 万条数据。

    单独建开始时间或结束时间索引,都扫描出了 200 万条以上的主键 id 去回表?,基本是无效索引了。。
    搞个联合索引,好处可能是可以通过索引确实找到了主键 id 去回表,不过依然需要连续比对几百万条数据,开销依然很大
    uselessVisitor
        5
    uselessVisitor  
       2021-04-14 16:34:38 +08:00
    @sue0917 #4 数据太多确实无效索引。。应该加限制条件的吧。。
    sue0917
        6
    sue0917  
    OP
       2021-04-14 17:25:16 +08:00
    @beichenhpy 看看其他老哥们有方案没有,确实有点打脑壳。,。
    uselessVisitor
        7
    uselessVisitor  
       2021-04-14 17:27:28 +08:00
    @sue0917 #6 话说 int 类型字段的索引,not in 会走索引吗?我测了一下 type=range 。。网上都说不走索引。。range 好像也算走了索引吧
    billlee
        8
    billlee  
       2021-04-14 21:06:38 +08:00
    换个思路,加个状态字段,然后用定时器维护这个字段
    sue0917
        9
    sue0917  
    OP
       2021-04-14 23:14:29 +08:00 via Android
    @billlee 状态字段定时器是种方式,不过不一定特别及时,万一定时器那一刻压力大了忙不过来,查出的数据就可能有误

    另外经常更新的字段做索引,不知道好不好。。想了下也还好,当做删除数据插入新数据了
    lxd8023
        10
    lxd8023  
       2021-04-14 23:55:35 +08:00
    @sue0917 看具体的返回的字段吧,可以考虑利用索引覆盖的特性,一次性拉百万数据肯定要做分页限制。
    RangerWolf
        11
    RangerWolf  
       2021-04-15 09:55:43 +08:00
    @beichenhpy 我个人理解,是不是走索引要看成本优化器的预估。
    如果优化器发现需要扫描的数据非常多,比如超过 20% 或者 三分之一,类似这种,就不会走索引。
    你还可以试试看,比如网上经常说的 性别 字段的索引问题。 比如 10 条记录,1 个男的 9 个女的,查询条件分别测试 gender = m 与 gender != m
    在我的环境( MySQL 5.7 ) 索引的使用情况是不一样的
    sue0917
        12
    sue0917  
    OP
       2021-04-15 10:02:31 +08:00
    @lxd8023 返回一行全部字段,只拉取 10 条。
    但是抱歉我没有理解到你的意思,。
    dongtingyue
        13
    dongtingyue  
       2021-04-15 10:08:24 +08:00
    具体语句丢出来。筛选后数据有那么多你说会有啥办法,走索引只是说能减少时间而已。
    sue0917
        14
    sue0917  
    OP
       2021-04-15 10:14:29 +08:00
    @dongtingyue 关键是走不上索引,或者说没有找到合适的索引怎么建。,。
    现在就是来问问,索引怎么建才比较好。,。
    Cy1
        15
    Cy1  
       2021-04-15 17:59:39 +08:00
    @sue0917 除非索引覆盖,不然回表肯定是避免不了
    建联合索引,(start_datetime asc, end_datetime desc) 基本上数据分布就很符合你这个需求吧?
    这个顺序比对的次数应该不会比实际返回的行数大很多?

    如果优化器决定不走索引,你又想要用索引,那就自己手动标明使用的索引就行了。
    sue0917
        16
    sue0917  
    OP
       2021-04-15 18:25:48 +08:00
    @Cy1 查整行数据,回表是必然的,也没有想避免。而且我只查 10 条数据就行。

    问题像下面描述的,按照你这种方式建立索引,好像比对次数也有 200 万。。。。感觉不太合适
    比如开始结束时间都在今天前的,和开始结束时间都在今天后的,分别有 200 万条数据。
    Cy1
        17
    Cy1  
       2021-04-16 09:47:37 +08:00
    @sue0917 好像也是。。。 我再想想
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1102 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 22:29 · PVG 06:29 · LAX 14:29 · JFK 17:29
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.