关于 mysql 读写速度疑问

[delavior]

一张表300多万条记录，一个简单的select count(*)查询，执行时间长达100多秒，正常吗？
同样的表记录数为30多万时，同样的查询语句执行时间不到0.1秒。

[delavior]

@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]

select count(*) from `history_uint` use index (history_uint_1)

[ALeo]

连主键都没有肯定慢了。。

[akira]

试试加个主键看看效果怎么样呗。

个人习惯是 select count(id) , id 是主键
另外，select里面尽量不要用*。

[whiteblack]

这个就涉及到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]

@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]

@delavior 你这个感觉是环境和磁盘的问题，sending data这个过程表示mysql 从磁盘中取回数据，然后发送给客户端，所以这个过程中可能有很多磁盘操作。
建议在一个非主键字段（字段长度选择小的，区分度高的）上建一个index，然后select count() 那个有非主键索引的字段，看下效果

[delavior]

@xiaobaigsy 我网上查的是sending data也包括过滤数据的过程，如果是的话，那应该还是查得慢

[whiteblack]

@delavior 不是过滤数据 是 去磁盘取数据

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

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

在innodb 中，主键是聚集索引，所以主键是和所有数据绑定在一起的，而非主键索引是独立于数据的，所以在count（）过程中，走非主键索引比主键索引效率更高。

[whiteblack]

@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.

官方描述太有迷惑性了