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

Mysql Innodb 中 PRIMARY KEY 对性能的影响

  •  
  •   zeyexe · 2014-12-21 00:32:15 +08:00 · 3931 次点击
    这是一个创建于 3385 天前的主题,其中的信息可能已经有所发展或是发生改变。
    请教大家一个问题:

    我要在Mysql上面对一个内容表(contents)做分页,分页的Sql如下:

    SELECT id, title, author FROM contents WHERE id <= (SELECT id FROM contents ORDER BY id DESC LIMIT 1 OFFSET 30050) ORDER BY id DESC LIMIT 20;

    其中我将ID这个字段设置为PRIMARY KEY并添加了额外的索引(根据 http://stackoverflow.com/a/511963 )。

    Sql也是根据网上的说法“子查询使用索引所以速度快”改的。但是我发现,当将ID设置成PRIMARY KEY的时候,我从offset 30050查询要4.7秒,但是ID删除PRIMARY KEY之后,整体查询只要0.02秒,性能提升了2个数量级,这是什么原因?
    12 条回复    2014-12-21 12:11:10 +08:00
    zeyexe
        1
    zeyexe  
    OP
       2014-12-21 00:50:45 +08:00
    Google对V2EX收录是相当的快啊,我发了帖子10分钟之后再用关键词“PRIMARY KEY MYSQL 性能”搜索,Google第一页就有这个帖子了。
    zeyexe
        2
    zeyexe  
    OP
       2014-12-21 00:53:17 +08:00
    删了主键在索引上加了unique再查询又很慢了,估计是唯一性约束造成的查询缓慢。
    cdxem713
        3
    cdxem713  
       2014-12-21 01:13:34 +08:00 via iPhone   ❤️ 1
    Id是顺序值么?我记得如果不是顺序值是会大大降低查询的速度
    zeyexe
        4
    zeyexe  
    OP
       2014-12-21 01:14:35 +08:00
    @cdxem713 ID是自动递增的。
    skydiver
        5
    skydiver  
       2014-12-21 01:26:09 +08:00   ❤️ 1
    为什么不explain一下看看呢
    zeyexe
        6
    zeyexe  
    OP
       2014-12-21 01:30:26 +08:00
    @skydiver explain的结果是时间耗费在子查询那里了,当索引为unique或有主键的时候,子查询消耗时间很长。不知道这个情况如何解释。
    skydiver
        7
    skydiver  
       2014-12-21 03:03:04 +08:00
    @zeyexe explain结果是什么能不能贴出来
    mahone3297
        8
    mahone3297  
       2014-12-21 08:02:41 +08:00   ❤️ 1
    1. 我觉得子查询,是很慢的吧,所以网上推荐用join代替自查询吧。。。
    2. OFFSET 30050 这个offset这么大,很慢的吧。。。网上针对这个大offset的问题,有专门讲的吧,是关于分页的
    不知道我说的对不对,供lz参考。。。
    lincanbin
        9
    lincanbin  
       2014-12-21 11:14:18 +08:00 via Android
    缓存吧
    lincanbin
        10
    lincanbin  
       2014-12-21 11:17:42 +08:00 via Android   ❤️ 1
    对了,主键用的hash树,是不支持between这种范围的用法的,你应该在主键上额外添加一个B 树索引。
    http://www.94cb.com/t/2276
    分页这块我以前也做过优化
    zeyexe
        11
    zeyexe  
    OP
       2014-12-21 12:05:12 +08:00
    @skydiver

    不带主键



    添加主键后
    zeyexe
        12
    zeyexe  
    OP
       2014-12-21 12:11:10 +08:00
    @lincanbin 主键加了额外的btree索引
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2998 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 14:44 · PVG 22:44 · LAX 07:44 · JFK 10:44
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.