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

关于 mysql 索引的一个奇怪的问题/mysql 选择错误索引

  •  
  •   ovear · 2015-08-11 21:27:10 +08:00 · 3761 次点击
    这是一个创建于 3377 天前的主题,其中的信息可能已经有所发展或是发生改变。

    OAQ 踩坑踩的伤心死了,不知道有没有大神遇到过(:з」∠)
    情景如下
    表thread有以下几个字段
    id int primary key
    fid int '所属板块
    reply int '回复的帖子id 0为帖子 其他为回复帖子的id
    lastUpdate datetime

    有索引如下
    1、联合索引 fid, reply, lastUpdate
    2、单独索引 lastUpdate

    当执行select * from thread where fid = 1 and reply = 0 order by lastUpdate desc;的时候
    mysql使用的是却是lastUpdate的单独索引,所以会进行超大量的全表搜寻。
    但是我已经为此类查询建立了合适的联合索引,但是mysql依然是不依不挠使用 lastUpdate 单独索引这是为什么呢?thx~

    在网上查到一个相同问题的,但是却没有解决这个问题
    https://github.com/ericdum/mujiang.info/issues/5

    不知道各位有没有遇到过呢?

    17 条回复    2015-08-12 18:40:00 +08:00
    jhdxr
        1
    jhdxr  
       2015-08-11 21:41:28 +08:00
    可以force index / ignor index啊
    jhdxr
        2
    jhdxr  
       2015-08-11 21:42:09 +08:00
    哦你贴的连接里也有提到这儿了。
    既然要让别人分析,为什么不把两种情况的explain贴出来看下呢。。。
    hellogbk
        3
    hellogbk  
       2015-08-11 21:43:00 +08:00
    不懂,
    你用force index让查询强制使用联合索引试试
    zts1993
        4
    zts1993  
       2015-08-11 21:59:26 +08:00
    联合索引 fid, reply 这样。?
    ovear
        5
    ovear  
    OP
       2015-08-11 22:27:33 +08:00
    @jhdxr 但是感觉这样有点奇怪=。=,我觉得应该是自己什么地方出问题了
    explain晚点我补上=。=,目前线上环境不敢乱动

    @hellogbk 恩 强制是可以的,联合索引已经试过了

    @zts1993 这样也会跑到lastUpdate去 OAQ
    liprais
        6
    liprais  
       2015-08-11 22:57:49 +08:00
    cost based optimizer 当然是选cost最低的,你可以 explain extended看下,
    或者用performance schema
    ovear
        7
    ovear  
    OP
       2015-08-11 23:41:23 +08:00
    @liprais 但是实际上似乎是我选择索引更优呢~

    mysql> explain EXTENDED SELECT * FROM `thread` force index(lastUpdate) WHERE `replyto` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
    +----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    | 1 | SIMPLE | thread | index | NULL | lastUpdate | 6 | NULL | 20 | 9668555 | Using where |
    +----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    1 row in set

    mysql> explain EXTENDED SELECT * FROM `thread` force index(unionIndex) WHERE `replyto` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    | 1 | SIMPLE | thread | ref | unionIndex | unionIndex | 5 | const,const | 106756 | 100 | Using where |
    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    1 row in set

    mysql>

    这里是explain结果在我电脑模拟出的结果,但是模拟的时候似乎选择的是 联合索引
    感觉很奇怪,不知道大神能看出原因么?
    bobbychen
        8
    bobbychen  
       2015-08-12 08:59:32 +08:00
    @ovear 你把order部分去掉之后看看,用的是什么索引
    ovear
        9
    ovear  
    OP
       2015-08-12 11:38:32 +08:00
    @bobbychen 去掉了的话,目前是使用
    reply, fid, status, lastReply
    bobbychen
        10
    bobbychen  
       2015-08-12 11:54:21 +08:00
    @ovear恩,因为查询还包括order和group by等,mysql优化器会综合考虑这些来选择index
    ovear
        11
    ovear  
    OP
       2015-08-12 12:59:59 +08:00
    @bobbychen =。=感觉选了个花费资源最多的。。用lastReply扫表每次都要扫50多W
    不知道有没有解决方法呢~
    bobbychen
        12
    bobbychen  
       2015-08-12 13:07:17 +08:00
    @ovear 可以这样建reply, fid, lastReply,status
    ovear
        13
    ovear  
    OP
       2015-08-12 13:09:54 +08:00
    @bobbychen 恩已经建立了一个了~但是还会走lastReply的单独索引,我都无奈了OAQ
    bobbychen
        14
    bobbychen  
       2015-08-12 13:11:37 +08:00
    @ovear 呵呵,建议看一看《高性能mysql》,你也许可以找到答案
    ovear
        15
    ovear  
    OP
       2015-08-12 15:21:56 +08:00
    @bobbychen 好的谢谢这位菊苣啦_(:з」∠)_
    jhdxr
        16
    jhdxr  
       2015-08-12 16:05:47 +08:00
    @ovear `explain extened` 请配合 `show warnings`使用。看你的的explain感觉很奇怪,虽然 ref 是优于 index 的,但你用lastupdate的rows才20,下面的联合索引要106756,很明显是应该用lastupdate的。

    另外我有一点不解的是,根据手册(https://dev.mysql.com/doc/refman/5.1/en/explain-extended.html) filtered 应该是个百分比,不知道你上面那个为什么会那么大。。。
    ovear
        17
    ovear  
    OP
       2015-08-12 18:40:00 +08:00
    @jhdxr 先感谢一下菊苣_(:з」∠)_

    我也觉得奇怪,因为这个值如果使用强制索引会变得非常大。。而自动选择却不会。。在我自己的开发机上无法复现,我在线上操作了一下,得到的结果感觉很奇怪

    1、强制使用lastReply
    mysql> explain EXTENDED SELECT * FROM `thread` force index(lastReply) WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;

    +----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    | 1 | SIMPLE | thread | index | NULL | LastReply | 6 | NULL | 20 | 31195690 | Using where |
    +----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
    1 row in set

    2、强制使用联合索引
    mysql> explain EXTENDED SELECT * FROM `thread` force index(adminForumGroup) WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;

    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    | 1 | SIMPLE | thread | ref | adminForumGroup | adminForumGroup | 5 | const,const | 250311 | 100 | Using where |
    +----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
    1 row in set

    3、自动选择到LastReply
    mysql> explain EXTENDED SELECT * FROM `thread` WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
    +----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
    | 1 | SIMPLE | thread | index | balalba很多 | LastReply | 6 | NULL | 83106 | 116.44 | Using where |
    +----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
    1 row in set

    我刚刚也看了下 show warnings,语句里面把 * 换成了一堆 as,除此之外没有任何差别,不知道菊苣怎么看呢_(:з」∠)_

    还有一个比较惊讶的是,使用lastReply竟然rows只有20行。。很不可思议
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2900 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 02:39 · PVG 10:39 · LAX 18:39 · JFK 21:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.