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

MYSQL 查询优化

  •  
  •   Aluhao · 2017-08-03 16:16:47 +08:00 · 5738 次点击
    这是一个创建于 2429 天前的主题,其中的信息可能已经有所发展或是发生改变。
    表 article 有 100 多万主题内容
    cid 是分类,建立索引
    uid 是用户 ID,建立索引
    time 是发布时间,建立了索引
    表内还有标题和内容及其它杂项字段

    SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC LIMIT 20;

    查询时间需要 0.5 秒,如果不加 ORDER BY time DESC 只需要 0.08 秒;
    如果不加 ORDER BY time DESC 又达不到业务需求,我需要查询的是用户最新的主题内容,除了这个方法还有没其它更优化的查询方法?

    还有就是当翻页更多的时候更慢 LIMIT 100, 20

    我再经过优化了下语句:

    SELECT * FROM `aws_article` WHERE cid=1 AND uid=100 ORDER BY time DESC, id DESC LIMIT 20;

    增加了 id DESC 查询时间减少到 0.2 秒了,还能再优化吗?
    第 1 条附言  ·  2017-08-03 18:08:36 +08:00
    经过测试最终还是使用这个方案,即增加一个 id DESC,这样由每次查询时间由 0.5 秒降到 0.02 秒;
    索引依旧使用单独的 cid uid time,总体看 100 多万数据 0.02 应该都接受了;
    SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC, id DESC LIMIT 20;

    后面还有一个回复表,表已经是 2000 多万数据了,目前也在优化中。。。
    第 2 条附言  ·  2017-08-03 19:05:39 +08:00
    为什么第一条那么快呀,没太搞明白

    2017-08-03 18:42:56 : Array
    (
    [sql] => SELECT * FROM `article` WHERE cid=1 OR (cid=2 AND uid IN('62923','1','3461','25823','64582','64587','64588','64589','64590')) OR (cid=5 AND uid=62923) ORDER BY time DESC LIMIT 20
    [time] => 0.000885
    )

    2017-08-03 18:42:58 : Array
    (
    [sql] => SELECT * FROM `article` WHERE cid=1 AND uid=12091 ORDER BY time DESC, id DESC LIMIT 20
    [time] => 0.022885
    )

    2017-08-03 18:43:58 : Array
    (
    [sql] => SELECT * FROM `article` WHERE cid=1 AND uid=12091 ORDER BY time DESC LIMIT 20
    [time] => 0.49885
    )
    53 条回复    2017-08-06 11:11:06 +08:00
    xiaobai987
        1
    xiaobai987  
       2017-08-03 16:21:48 +08:00
    我感觉直接用 ORDER BY ID 吧 如果 ID 是自增的
    Aluhao
        2
    Aluhao  
    OP
       2017-08-03 16:23:19 +08:00
    @xiaobai987 如果只用 ORDER BY id DESC 查询速度和 ORDER BY time DESC 是一样的。
    imnpc
        3
    imnpc  
       2017-08-03 16:25:30 +08:00
    列表页要显示哪些 请把哪些字段加索引
    查询语句只查询需要的字段
    Aluhao
        4
    Aluhao  
    OP
       2017-08-03 16:29:13 +08:00
    @imnpc 列表是显示标题和内容,这个 varchar 和 text 不需要加索引吧,整个表也就大概有 10 个字段吧,除了标题和内容其它都是 int 型的数字。
    id4alex
        5
    id4alex  
       2017-08-03 16:29:38 +08:00
    cid 和 uid 上建一个索引就好了
    sagaxu
        6
    sagaxu  
       2017-08-03 16:32:14 +08:00
    加个索引就行了 (cid,uid,time desc)
    zjsxwc
        7
    zjsxwc  
       2017-08-03 16:33:19 +08:00
    explain 看下用了哪些索引。
    sagaxu
        8
    sagaxu  
       2017-08-03 16:34:27 +08:00
    或者用(uid,cid,time desc) 哪个索引更快,看数据分布,多个 and 条件,把分布更稀疏的排前面更好
    RuzZ
        9
    RuzZ  
       2017-08-03 16:36:13 +08:00
    如果是单列的索引,可以添加联合索引试试看。explain 下查询,看看实际上使用的索引情况
    cnwtex
        10
    cnwtex  
       2017-08-03 16:37:34 +08:00
    把内容挪到另外一个表,用 id 关联起来,会提速百-千倍左右
    Aluhao
        11
    Aluhao  
    OP
       2017-08-03 16:39:34 +08:00
    @zjsxwc

    已经建立的索引

    PRIMARY KEY (`id`),
    KEY `uid` (`uid`),
    KEY `cid` (`cid`),
    KEY `time` (`time`)
    Aluhao
        12
    Aluhao  
    OP
       2017-08-03 16:40:12 +08:00
    @RuzZ 联合索引 (uid,cid,time) 也试过了,还是一样很慢
    Aluhao
        13
    Aluhao  
    OP
       2017-08-03 16:41:08 +08:00
    @id4alex 那二个都建立过索引了,主要是这个影响到了,加了 ORDER BY time DESC 就是全表扫描了
    Aluhao
        14
    Aluhao  
    OP
       2017-08-03 16:41:44 +08:00
    @cnwtex 这个可以考虑呀
    sagaxu
        15
    sagaxu  
       2017-08-03 16:42:23 +08:00
    @Aluhao 贴 explain SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC LIMIT 20; 的结果
    noNOno
        16
    noNOno  
       2017-08-03 16:45:38 +08:00
    select * from (SELECT * FROM `article` WHERE cid=1 AND uid=100) t ORDER BY time DESC LIMIT 20;
    放到子查询里走索引么
    wayne712
        17
    wayne712  
       2017-08-03 16:46:01 +08:00
    order by time, 如果 time 没有索引则会拖慢查询 , cid,uid 设置组合索引
    Aluhao
        18
    Aluhao  
    OP
       2017-08-03 16:49:52 +08:00
    @sagaxu 你的方法我测试了,可以优化一些 (cid,uid,time) desc 新建一个字段 desc 我以前是直接用 time 所以没效果
    Aluhao
        19
    Aluhao  
    OP
       2017-08-03 16:50:21 +08:00
    @wayne712 time 有建立索引的
    sagaxu
        20
    sagaxu  
       2017-08-03 16:51:36 +08:00
    @Aluhao desc 是让你建索引的时候按照 time 降序,order by time desc 的时候可以提高些许性能,不是增加一个 desc 字段
    Aluhao
        21
    Aluhao  
    OP
       2017-08-03 16:51:46 +08:00
    @noNOno 你这个没有效果呀,反而多一次查询
    id4alex
        22
    id4alex  
       2017-08-03 16:54:12 +08:00
    KEY `uid` (`uid`),
    KEY `cid` (`cid`),
    KEY `time` (`time`)
    按道理说这种索引都不需要建立, 没卵用。


    你建立联合索引( cid,uid )跑下 explain 就一清二楚了。
    Aluhao
        23
    Aluhao  
    OP
       2017-08-03 16:54:54 +08:00
    @sagaxu 如果不建立一个字段查询出错呀?

    查询出错 (1054): Unknown column 'desc' in 'order clause'
    Aluhao
        24
    Aluhao  
    OP
       2017-08-03 16:56:53 +08:00
    @id4alex 我试过了,cid 和 uid 不建索引 几十秒查不出来,你要看有 WHERE cid=1 AND uid=12091
    还是有用的。
    Aluhao
        25
    Aluhao  
    OP
       2017-08-03 16:58:33 +08:00
    @wayne712 cid,uid,time 都有单独建立索引
    RuzZ
        26
    RuzZ  
       2017-08-03 16:59:15 +08:00
    你的 explain 结果贴一下
    Aluhao
        27
    Aluhao  
    OP
       2017-08-03 17:02:43 +08:00
    @RuzZ 现在按 @sagaxu 的方法新建立一个字段 displayorder 用它来做联合索引(cid,uid,time)

    SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY displayorder DESC LIMIT 20;

    查询时间优化到 0.020 秒了
    liprais
        28
    liprais  
       2017-08-03 17:03:13 +08:00
    先贴下加了联合索引的执行计划
    Aluhao
        29
    Aluhao  
    OP
       2017-08-03 17:03:47 +08:00
    @liprais 结果在上面 优化到 0.020 秒 了
    frye
        30
    frye  
       2017-08-03 17:04:05 +08:00
    @Aluhao MySQL 的一个 Query 只能用一条索引,所以你分别建索引是没有用的,MySQL 只会用其中一条索引。就像大家说的,你建一个 cid,uid,time 的联合索引就好了,多多仔细深入思考下。然后还得 explain 一下,看看用对了索引没有。
    phx13ye
        31
    phx13ye  
       2017-08-03 17:04:21 +08:00
    人家让你这样建索引,把单条索引删掉,睿智

    CREATE INDEX index_name ON article (cid, uid, time DESC)
    Aluhao
        32
    Aluhao  
    OP
       2017-08-03 17:06:23 +08:00
    @frye 我有测试的,如果把单独索引去了,查询也是很慢,因为查询的时候除了排序还有条件,如:
    WHERE cid=1 AND uid=12091
    Aluhao
        33
    Aluhao  
    OP
       2017-08-03 17:07:00 +08:00
    @phx13ye 我试试先
    frye
        34
    frye  
       2017-08-03 17:08:45 +08:00
    @Aluhao 如果你做了 @phx13ye 31 楼的操作,并且用到了这个索引,剩下能够优化的就只有字段类型了。如果还是很慢的话,就只能升级机器配置,或者考虑其他的数据库了。
    daye
        35
    daye  
       2017-08-03 17:08:54 +08:00
    索引是有使用顺序的,建了联合索引之后,请删除其他索引,确保正确性
    zhx1991
        36
    zhx1991  
       2017-08-03 17:09:54 +08:00
    大体上讲 uid, time 的索引是没问题的

    但是要看区分度, 你索引建完 explain 一下吧. 如果 cid 是分类我猜区分度巨低, 删了吧.
    frye
        37
    frye  
       2017-08-03 17:10:14 +08:00
    @Aluhao 在我的印象中,MySQL 优化好,做你这样的 Query 的话,查询一个亿的数据,还是可以的。
    Aluhao
        38
    Aluhao  
    OP
       2017-08-03 17:11:10 +08:00
    @phx13ye 删除其它单条索引不行,我有其它地方专门针对 cid 和 uid 查的。
    Aluhao
        39
    Aluhao  
    OP
       2017-08-03 17:13:38 +08:00
    @frye 谢谢指示
    phx13ye
        40
    phx13ye  
       2017-08-03 17:14:17 +08:00
    @Aluhao (cid, uid, time DESC)对 cid 应该也是有用的,uid 可能需要单独建,具体还是看 explain
    gouchaoer
        41
    gouchaoer  
       2017-08-03 17:15:11 +08:00 via Android
    让你贴 explain 你就贴啊,这么多人帮你看墨迹啥
    akira
        42
    akira  
       2017-08-03 17:15:41 +08:00
    SELECT * FROM 换成 select id , time 呢
    Aluhao
        43
    Aluhao  
    OP
       2017-08-03 17:16:13 +08:00
    @gouchaoer 这里不能传图片呀
    frye
        44
    frye  
       2017-08-03 17:16:26 +08:00
    @Aluhao MySQL 可以用 FORCE INDEX 语句指定使用哪个索引,另外如果 uid 比 cid 的离散度高的话,建立索引的时候建议把 uid 放在前面 CREATE INDEX index_name ON article (uid, cid, time DESC),另外如果你建立了 uid, cid, time DESC 的索引的话,就完全没有必要再单独给 uid 建立索引了。
    Aluhao
        45
    Aluhao  
    OP
       2017-08-03 17:18:52 +08:00
    @frye 好的,谢谢
    simaguo
        46
    simaguo  
       2017-08-03 17:26:51 +08:00
    unique (cid,uid,time desc)?
    debye
        47
    debye  
       2017-08-03 17:27:51 +08:00
    索引也不是万能的
    还有索引多了也有牺牲
    Aluhao
        48
    Aluhao  
    OP
       2017-08-03 17:30:13 +08:00
    @simaguo CID 是分类 UID 是用户 ID TIME 是时间
    orzfly
        49
    orzfly  
       2017-08-03 17:33:33 +08:00
    @Aluhao #43 觉得“发不了图”为什么不早点说呢?

    https://www.v2ex.com/t/130567?p=1
    dikT
        50
    dikT  
       2017-08-03 17:42:40 +08:00
    SELECT * FROM `aws_article` WHERE id in (SELECT id FROM `aws_article` WHERE cid=1 AND uid=100 ORDER BY id DESC LIMIT 100, 20; )
    yanze0613
        51
    yanze0613  
       2017-08-03 17:42:56 +08:00
    ORDER BY time DESC LIMIT 20
    会导致扫描大量数据,就很麻烦
    joeke
        52
    joeke  
       2017-08-03 17:55:54 +08:00
    @orzfly 为什么不早说,哈哈
    Aluhao
        53
    Aluhao  
    OP
       2017-08-06 11:11:06 +08:00 via iPad
    @dikT 对,用 IN 取数据也快
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4549 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 10:06 · PVG 18:06 · LAX 03:06 · JFK 06:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.