可能会在 ORDER BY 中被引用的列有加 INDEX 的必要么? - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member  Sign In
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
Livid
272.76D
572.22D
V2EX    MySQL

可能会在 ORDER BY 中被引用的列有加 INDEX 的必要么?

  •  
  •   Livid
    PRO
    Jan 28, 2015 6371 views
    This topic created in 4119 days ago, the information mentioned may be changed or developed.
    15 replies    2015-03-04 13:19:11 +08:00
    ziyuan
        1
    ziyuan  
       Jan 28, 2015
    oracle的话,可以查看执行计划,一般是有必要,mysql不太清楚了
    garfeildma
        2
    garfeildma  
       Jan 28, 2015
    看具体的查询语句吧,order by在很多时候会用上索引,具体可以看这个

    http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
    p2p
        3
    p2p  
       Jan 28, 2015
    数据量大了 必要性就体现出来了
    kslr
        4
    kslr  
       Jan 28, 2015 via Android
    很明显的提高速度
    zts1993
        5
    zts1993  
       Jan 28, 2015
    有的,。。。
    xoxo
        6
    xoxo  
       Jan 28, 2015
    很必要, 而且建议BTree方式索引
    a2z
        7
    a2z  
       Jan 28, 2015   1
    必须有……百万级数据量就看出差距了
    est
        8
    est  
       Jan 28, 2015
    where key2 order by key1

    key1 必须加索引。

    http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
    JamesRuan
        9
    JamesRuan  
       Jan 28, 2015
    为什么是可能会?数据库不应该是根据业务设计的吗?
    还处在 [可能] 的时候就别想着加INDEX,等确定一定会的时候再加。
    blacktulip
        10
    blacktulip  
       Jan 29, 2015
    虽然 Knuth 说过那啥,不过我觉得这个属于那 3% 吧,加索引不麻烦又不影响别的啥,就加上呗
    nrop19
        11
    nrop19  
       Jan 29, 2015 via Android
    如果是 order by 点击量,那还要点击量这个字段也要索引吗?
    dazuiba
        12
    dazuiba  
       Jan 29, 2015
    mysql一次只用一个index,联合索引可能有用的。
    woshicixide
        13
    woshicixide  
       Jan 29, 2015
    当然有
    skywatcher
        14
    skywatcher  
       Jan 29, 2015
    根据你的常用查询来决定,所以没确定就暂时不加吧,万一后来得用复合索引,那就得换。garfeildma 已经给出了可用情况
    ShiningRay
        15
    ShiningRay  
       Mar 4, 2015
    看 where 之后筛选出来的记录数量,如果比较少的话,而且 order 后面的 selective 并不高的话,可以考虑不给 order 的字段加入索引
    About     Help     Advertise     Blog     API     FAQ     Solana     2996 Online   Highest 6679       Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 76ms UTC 08:57 PVG 16:57 LAX 01:57 JFK 04:57
    Do have faith in what you're doing.
    ubao msn snddm index pchome yahoo rakuten mypaper meadowduck bidyahoo youbao zxmzxm asda bnvcg cvbfg dfscv mmhjk xxddc yybgb zznbn ccubao uaitu acv GXCV ET GDG YH FG BCVB FJFH CBRE CBC GDG ET54 WRWR RWER WREW WRWER RWER SDG EW SF DSFSF fbbs ubao fhd dfg ewr dg df ewwr ewwr et ruyut utut dfg fgd gdfgt etg dfgt dfgd ert4 gd fgg wr 235 wer3 we vsdf sdf gdf ert xcv sdf rwer hfd dfg cvb rwf afb dfh jgh bmn lgh rty gfds cxv xcv xcs vdas fdf fgd cv sdf tert sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf shasha9178 shasha9178 shasha9178 shasha9178 shasha9178 liflif2 liflif2 liflif2 liflif2 liflif2 liblib3 liblib3 liblib3 liblib3 liblib3 zhazha444 zhazha444 zhazha444 zhazha444 zhazha444 dende5 dende denden denden2 denden21 fenfen9 fenf619 fen619 fenfe9 fe619 sdf sdf sdf sdf sdf zhazh90 zhazh0 zhaa50 zha90 zh590 zho zhoz zhozh zhozho zhozho2 lislis lls95 lili95 lils5 liss9 sdf0ty987 sdft876 sdft9876 sdf09876 sd0t9876 sdf0ty98 sdf0976 sdf0ty986 sdf0ty96 sdf0t76 sdf0876 df0ty98 sf0t876 sd0ty76 sdy76 sdf76 sdf0t76 sdf0ty9 sdf0ty98 sdf0ty987 sdf0ty98 sdf6676 sdf876 sd876 sd876 sdf6 sdf6 sdf9876 sdf0t sdf06 sdf0ty9776 sdf0ty9776 sdf0ty76 sdf8876 sdf0t sd6 sdf06 s688876 sd688 sdf86