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

关于 mysql 读写速度疑问

  •  
  •   delavior ·
    delavior · 2015-04-16 13:55:45 +08:00 · 4423 次点击
    这是一个创建于 3494 天前的主题,其中的信息可能已经有所发展或是发生改变。
    一张表300多万条记录,一个简单的select count(*)查询,执行时间长达100多秒,正常吗?
    同样的表记录数为30多万时,同样的查询语句执行时间不到0.1秒。
    第 1 条附言  ·  2015-04-16 16:00:32 +08:00
    引擎是InnoDB,确实没有主键.
    是跟这有关系吗
    30 条回复    2015-04-17 18:25:20 +08:00
    ALeo
        1
    ALeo  
       2015-04-16 14:02:12 +08:00
    用count(1)
    tabris17
        2
    tabris17  
       2015-04-16 14:08:14 +08:00
    没任何where group by?
    fising
        3
    fising  
       2015-04-16 14:08:32 +08:00
    1. MyISAM
    2. USE INDEX
    tb4649120073rs
        4
    tb4649120073rs  
       2015-04-16 14:21:32 +08:00
    explain一下看看
    XadillaX
        5
    XadillaX  
       2015-04-16 14:29:02 +08:00
    COUNT(1)
    delavior
        6
    delavior  
    OP
       2015-04-16 14:49:21 +08:00
    @ALeo @XadillaX 结果没什么变化,还是100多秒
    delavior
        7
    delavior  
    OP
       2015-04-16 14:49:57 +08:00
    @tabris17 没有
    delavior
        8
    delavior  
    OP
       2015-04-16 14:55:18 +08:00
    @fising 对数据库不懂,而且现在只允许查询,不允许对表结构和内容作修改.我只是觉得这查询速度不太正常,想确认一下是不是不正常和什么原因
    delavior
        9
    delavior  
    OP
       2015-04-16 14:55:37 +08:00
    @tb4649120073rs
    mysql> explain history_uint;
    +--------+---------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +--------+---------------------+------+-----+---------+-------+
    | itemid | bigint(20) unsigned | NO | MUL | NULL | |
    | clock | int(11) | NO | | 0 | |
    | value | bigint(20) unsigned | NO | | 0 | |
    | ns | int(11) | NO | | 0 | |
    +--------+---------------------+------+-----+---------+-------+
    4 rows in set (0.09 sec)
    heyli
        10
    heyli  
       2015-04-16 15:18:09 +08:00
    表引擎是InnoDB?
    delavior
        11
    delavior  
    OP
       2015-04-16 15:19:40 +08:00
    @tb4649120073rs mysql> explain select count(*) from history_uint;
    +----+-------------+--------------+-------+---------------+----------------+----
    -----+------+---------+-------------+
    | id | select_type | table | type | possible_keys | key | key
    _len | ref | rows | Extra |
    +----+-------------+--------------+-------+---------------+----------------+----
    -----+------+---------+-------------+
    | 1 | SIMPLE | history_uint | index | NULL | history_uint_1 | 12
    | NULL | 3393731 | Using index |
    +----+-------------+--------------+-------+---------------+----------------+----
    -----+------+---------+-------------+
    1 row in set (0.00 sec)
    delavior
        12
    delavior  
    OP
       2015-04-16 15:20:12 +08:00
    @heyli mysql默认不是myisam吗,没改过
    wanjun
        13
    wanjun  
       2015-04-16 15:20:44 +08:00
    是不是你的表没有主键
    wanjun
        14
    wanjun  
       2015-04-16 15:22:04 +08:00
    默认 InnoDB ,问题应该不是使用什么引擎的问题
    huigeer
        15
    huigeer  
       2015-04-16 15:30:14 +08:00
    是不是你的表没有主键 + 1024,
    innodb的灵魂就是主键
    denghongcai
        16
    denghongcai  
       2015-04-16 15:41:26 +08:00
    desc看下
    explain看上去没问题,用了索引
    tonyzhimin
        17
    tonyzhimin  
       2015-04-16 15:45:50 +08:00
    你这个要先确认是啥存储引擎
    delavior
        18
    delavior  
    OP
       2015-04-16 15:52:50 +08:00
    @wanjun 是没有主键,是跟这有关系吗
    idblife
        19
    idblife  
       2015-04-16 16:25:07 +08:00
    很难理解不加主键的行为
    或者说绝对不理解
    heyli
        20
    heyli  
       2015-04-16 16:30:47 +08:00
    先确定你的表引擎先 innoDB没记错的话 count是全表扫描的
    SHOW CREATE TABLE 看下 ENGINE
    delavior
        21
    delavior  
    OP
       2015-04-16 16:42:54 +08:00
    @heyli | history_uint | CREATE TABLE `history_uint` (
    `itemid` bigint(20) unsigned NOT NULL,
    `clock` int(11) NOT NULL DEFAULT '0',
    `value` bigint(20) unsigned NOT NULL DEFAULT '0',
    `ns` int(11) NOT NULL DEFAULT '0',
    KEY `history_uint_1` (`itemid`,`clock`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

    是InnoDB的
    所以逐行扫描会这么慢?但是30万行的时候还是挺快的啊,多了一个数量级,差的时间可不只是差一个数量级啊,而且慢得恐怖啊
    tabris17
        22
    tabris17  
       2015-04-16 16:54:25 +08:00
    select count(*) from `history_uint` use index (history_uint_1)
    ALeo
        23
    ALeo  
       2015-04-16 16:56:40 +08:00
    连主键都没有肯定慢了。。
    akira
        24
    akira  
       2015-04-16 16:59:48 +08:00
    试试加个主键看看效果怎么样呗。

    个人习惯是 select count(id) , id 是主键
    另外,select里面尽量不要用*。
    whiteblack
        25
    whiteblack  
       2015-04-16 19:38:58 +08:00   ❤️ 2
    这个就涉及到myisam 和 InnoDB数据结构问题了,
    myisam会保存表的行数,所以不用count(*)直接
    SELECT TABLE_NAME,TABLE_ROWS FROM
    INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' and TABLE_NAME = "table"
    就可以了

    InnoDB则不同,他没有保存这个数据。

    ”InnoDB的数据文件本身要按主键排序,所以在创建InnoDB表时必须要有主键,如果没有显式指定,那么系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则系统自动为该表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。“

    所以并不是主键的问题,即时你没有设置主键,mysql也会给你设置,而且从你的explain来看,type也是 index,使用了主键索引。

    所以 先show processlist查看 这个是不是由于锁表导致的(一般来说不会,因为innodb使用多版本控制,一般select不会要求锁,除非是meta锁之类的坑)。

    然后使用profiling 查看select语句每一步的耗时,分析性能瓶颈,查看可能的原因
    随便搜的一个连接 http://www.cnblogs.com/adforce/archive/2012/06/02/2532287.html 里面有说明。

    把结果贴上来 在具体分析
    delavior
        26
    delavior  
    OP
       2015-04-17 09:37:11 +08:00
    @xiaobaigsy

    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    | 0 |
    +-------------+
    1 row in set (0.06 sec)

    mysql> set profiling=1;
    Query OK, 0 rows affected (0.01 sec)

    mysql> select count(1) from history_uint;
    +----------+
    | count(1) |
    +----------+
    | 3464122 |
    +----------+
    1 row in set (1 min 52.51 sec)

    mysql> show profiles;
    +----------+--------------+-----------------------------------+
    | Query_ID | Duration | Query |
    +----------+--------------+-----------------------------------+
    | 1 | 20.63920150 | select count(*) from history_uint |
    | 2 | 112.46709225 | select count(1) from history_uint |
    +----------+--------------+-----------------------------------+
    2 rows in set (0.06 sec)

    mysql> show profile for query 2;
    +----------------------+------------+
    | Status | Duration |
    +----------------------+------------+
    | starting | 0.000042 |
    | checking permissions | 0.000007 |
    | Opening tables | 0.000017 |
    | System lock | 0.000009 |
    | init | 0.000010 |
    | optimizing | 0.000008 |
    | statistics | 0.000011 |
    | preparing | 0.000008 |
    | executing | 0.000004 |
    | Sending data | 112.466869 |
    | end | 0.000019 |
    | query end | 0.000006 |
    | closing tables | 0.000014 |
    | freeing items | 0.000058 |
    | logging slow query | 0.000004 |
    | logging slow query | 0.000003 |
    | cleaning up | 0.000005 |
    +----------------------+------------+
    17 rows in set (0.03 sec)

    mysql> show profile block io,cpu for query 2;
    +----------------------+------------+-----------+------------+--------------+---
    ------------+
    | Status | Duration | CPU_user | CPU_system | Block_ops_in | Bl
    ock_ops_out |
    +----------------------+------------+-----------+------------+--------------+---
    ------------+
    | starting | 0.000042 | 0.000000 | 0.000000 | NULL |
    NULL |
    | checking permissions | 0.000007 | 0.000000 | 0.000000 | NULL |
    NULL |
    | Opening tables | 0.000017 | 0.000000 | 0.000000 | NULL |
    NULL |
    | System lock | 0.000009 | 0.000000 | 0.000000 | NULL |
    NULL |
    | init | 0.000010 | 0.000000 | 0.000000 | NULL |
    NULL |
    | optimizing | 0.000008 | 0.000000 | 0.000000 | NULL |
    NULL |
    | statistics | 0.000011 | 0.000000 | 0.000000 | NULL |
    NULL |
    | preparing | 0.000008 | 0.000000 | 0.000000 | NULL |
    NULL |
    | executing | 0.000004 | 0.000000 | 0.000000 | NULL |
    NULL |
    | Sending data | 112.466869 | 27.331375 | 3.759624 | NULL |
    NULL |
    | end | 0.000019 | 0.000000 | 0.000000 | NULL |
    NULL |
    | query end | 0.000006 | 0.000000 | 0.000000 | NULL |
    NULL |
    | closing tables | 0.000014 | 0.000000 | 0.000000 | NULL |
    NULL |
    | freeing items | 0.000058 | 0.000000 | 0.000000 | NULL |
    NULL |
    | logging slow query | 0.000004 | 0.000000 | 0.000000 | NULL |
    NULL |
    | logging slow query | 0.000003 | 0.000000 | 0.000000 | NULL |
    NULL |
    | cleaning up | 0.000005 | 0.000000 | 0.000000 | NULL |
    NULL |
    +----------------------+------------+-----------+------------+--------------+---
    ------------+
    17 rows in set (0.04 sec)
    whiteblack
        27
    whiteblack  
       2015-04-17 17:20:43 +08:00
    @delavior 你这个感觉是环境和磁盘的问题,sending data这个过程表示mysql 从磁盘中取回数据,然后发送给客户端,所以这个过程中可能有很多磁盘操作。
    建议在一个非主键字段(字段长度选择小的,区分度高的)上建一个index,然后select count() 那个有非主键索引的字段,看下效果
    delavior
        28
    delavior  
    OP
       2015-04-17 17:35:45 +08:00
    @xiaobaigsy 我网上查的是sending data也包括过滤数据的过程,如果是的话,那应该还是查得慢
    whiteblack
        29
    whiteblack  
       2015-04-17 18:19:57 +08:00
    @delavior 不是过滤数据 是 去磁盘取数据

    Sending data
    The thread is processing rows for a SELECT statement and also is sending data to the client.

    使用辅助索引则就不用去磁盘取数据,所有查询过程在index内部进行。

    在innodb 中,主键是聚集索引,所以主键是和所有数据绑定在一起的,而非主键索引是独立于数据的,所以在count()过程中,走非主键索引比主键索引效率更高。
    whiteblack
        30
    whiteblack  
       2015-04-17 18:25:20 +08:00
    @delavior 更正下,确实是会过滤数据
    This is quite a misleading status. It should be called "reading and filtering data".

    This means that MySQL has some data stored on the disk (or in memory) which is yet to be read and sent over. It may be the table itself, an index, a temporary table, a sorted output etc.

    If you have a 1M records table (without an index) of which you need only one record, MySQL will still output the status as "sending data" while scanning the table, despite the fact it has not sent anything yet.

    官方描述太有迷惑性了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   978 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 22:03 · PVG 06:03 · LAX 14:03 · JFK 17:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.