• Posts tagged "MySQL"
  • (Page 2)

Blog Archives

[转] select count(*)的思考

select count(*)的思考

原文:MYSQL性能调优与架构设计

 

举例:

这里我们就拿一个看上去很简单的功能来分析一下。

需求:一个论坛帖子总量的统计

附加要求:实时更新

 

在很多人看来,这个功能非常容易实现,不就是执行一条SELECT COUNT(*)的Query 就可以得到结果
了么?是的,确实只需要如此简单的一个Query 就可以得到结果。但是,如果我们采用不是MyISAM 存储
引擎,而是使用的Innodb 的存储引擎,那么大家可以试想一下,如果存放帖子的表中已经有上千万的帖
子的时候,执行这条Query 语句需要多少成本?恐怕再好的硬件设备,恐怕都不可能在10 秒之内完成一

次查询吧。如果我们的访问量再大一点,还有人觉得这是一件简单的事情么?

 

既然这样查询不行,那我们是不是该专门为这个功能建一个表,就只有一个字段,一条记录,就存
放这个统计量,每次有新的帖子产生的时候,都将这个值增加1,这样我们每次都只需要查询这个表就可
以得到结果了,这个效率肯定能够满足要求了。确实,查询效率肯定能够满足要求,可是如果我们的系
统帖子产生很快,在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候,恐怕这个统计表又要
成为大家的噩梦了。要么因为并发的问题造成统计结果的不准确,要么因为锁资源争用严重造成整体性

能的大幅度下降。

 

其实这里问题的焦点不应该是实现这个功能的技术细节,而是在于这个功能的附加要求“实时更
新”上面。当一个论坛的帖子数量很大了之后,到底有多少人会关注这个统计数据是否是实时变化的?
有多少人在乎这个数据在短时间内的不精确性?我想恐怕不会有人会傻傻的盯着这个统计数字并追究当
自己发了一个帖子然后回头刷新页面发现这个统计数字没有加1 吧?即使明明白白的告诉用户这个统计

数据是每过多长时间段更新一次,那有怎样?难道会有很多用户就此很不爽么?

 

只要去掉了这个“实时更新”的附加条件,我们就可以非常容易的实现这个功能了。就像之前所提
到的那样,通过创建一个统计表,然后通过一个定时任务每隔一定时间段去更新一次里面的统计值,这

样既可以解决统计值查询的效率问题,又可以保证不影响新发贴的效率,一举两得。

 

实际上,在我们应用的系统中还有很多很多类似的功能点可以优化。如某些场合的列表页面参与列

表的数据量达到一个数量级之后,完全可以不用准确的显示这个列表总共有多少条信息,总共分了多少页,
而只需要一个大概的估计值或者一个时间段之前的统计值。这样就省略了我们的分页程序需要在分

以前实时COUNT 出满足条件的记录数。

 

其实,在很多应用系统中,实时和准实时,精确与基本准确,在很多地方所带来的性能消耗可能是
几个性能的差别。在系统性能优化中,应该尽量分析出那些可以不实时和不完全精确的地方,作出一些
相应的调整,可能会给大家带来意想不到的巨大性能提升。

行列数据存储比较,MyISAM和Brighthouse引擎

测试环境MySQL的MyISAM行式数据库引擎和InfoBright的brightHouse列式数据库引擎.
本机系统:

普通台式机,2CPU,2G内存,硬盘5400转,Linux Ubuntu 12.04 32位

InfoBright按最小默认配置

my-ib.cnf
[mysqld]
27 port = 5029
28 socket = /tmp/mysql-ib.sock
29 skip-locking
30 key_buffer = 16M
31 max_allowed_packet = 500M
32 table_cache = 16
33 sort_buffer_size = 1M
34 read_buffer_size = 1M
35 read_rnd_buffer_size = 4M
36 myisam_sort_buffer_size = 8M
37 net_buffer_length = 8K
38 thread_cache_size = 32
39 thread_stack = 512K
40 query_cache_size = 8M
41 query_cache_type=0
42 # Try number of CPU cores*4 for thread_concurrency
43 thread_concurrency = 8

数据结构DDL:
CREATE TABLE t_user_myISAM(
id INT,
name varchar(32) NOT NULL ,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE t_user_righthouse(
id INT,
name varchar(32) NOT NULL ,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=Brighthouse DEFAULT CHARSET=utf8;

数据样本:
tail -f /tmp/myisam.csv
14999990,14999990–14999990,2012-07-05 16:56:54
14999991,14999991–14999991,2012-07-05 16:56:54
14999992,14999992–14999992,2012-07-05 16:56:54
14999993,14999993–14999993,2012-07-05 16:56:54
14999994,14999994–14999994,2012-07-05 16:56:54
14999995,14999995–14999995,2012-07-05 16:56:54
14999996,14999996–14999996,2012-07-05 16:56:54
14999997,14999997–14999997,2012-07-05 16:56:54
14999998,14999998–14999998,2012-07-05 16:56:54
14999999,14999999–14999999,2012-07-05 16:56:54

存储比较:数据量分别为50W条,500W条,2500W条
第一行为原始CSV,第二行为tar.gz压缩的CSV,t_user_myISAM.MYD为MyISAM引擎存储的数据文件,t_user_righthouse.bht是brighthouse的存放目录

#50w
-rw-rw-rw- 1 mysql mysql 20666643 7月 5 16:03 /tmp/myisam.csv
-rw-rw-r– 1 mysql mysql 3640018 7月 5 16:20 /tmp/myisam.tar.gz
-rwxr-xr-x 1 mysql mysql 8630 7月 5 15:32 t_user_myISAM.frm*
-rwxr-xr-x 1 mysql mysql 13959580 7月 5 15:32 t_user_myISAM.MYD*
-rwxr-xr-x 1 mysql mysql 1024 7月 5 15:32 t_user_myISAM.MYI*

#500w
-rw-rw-rw- 1 mysql mysql 221666643 7月 5 16:32 /tmp/myisam.csv
-rw-rw-r– 1 conan conan 36877526 7月 5 16:33 /tmp/myisam.tar.gz
-rw-rw—- 1 mysql mysql 8630 7月 5 16:24 t_user_myISAM.frm
-rw-rw—- 1 mysql mysql 155959580 7月 5 16:30 t_user_myISAM.MYD
-rw-rw—- 1 mysql mysql 1024 7月 5 16:30 t_user_myISAM.MYI

#2500w
-rw-rw-rw- 1 mysql mysql 908333286 7月 5 16:58 /tmp/myisam.csv
-rw-rw-r– 1 conan conan 147507267 7月 5 16:59 /tmp/myisam.tar.gz
-rw-rw—- 1 mysql mysql 8630 7月 5 16:24 t_user_myISAM.frm
-rw-rw—- 1 mysql mysql 631919160 7月 5 16:56 t_user_myISAM.MYD
-rw-rw—- 1 mysql mysql 1024 7月 5 16:56 t_user_myISAM.MYI
drwxrwx–x 2 mysql mysql 4096 7月 5 17:01 t_user_righthouse.bht/
-rw-rw—- 1 mysql mysql 8630 7月 5 16:24 t_user_righthouse.frm
#打开t_user_righthouse.bht/目录
drwxr-xr-x 2 mysql mysql 4096 7月 5 17:01 ./
drwxr-xr-x 3 mysql mysql 4096 7月 5 16:24 ../
-rw-rw—- 1 mysql mysql 0 7月 5 17:01 ab_switch
-rw-rw—- 1 mysql mysql 15344 7月 5 17:01 TA00000000000000.ctb
-rw-rw—- 1 mysql mysql 5616 7月 5 16:34 TA00000000000001.ctb
-rw-rw—- 1 mysql mysql 117 7月 5 16:34 TA00000.ctb
-rw-rw—- 1 mysql mysql 14171 7月 5 17:01 TA00000DPN.ctb
-rw-rw—- 1 mysql mysql 126449126 7月 5 17:01 TA00001000000000.ctb
-rw-rw—- 1 mysql mysql 31428144 7月 5 16:34 TA00001000000001.ctb
-rw-rw—- 1 mysql mysql 103 7月 5 16:34 TA00001.ctb
-rw-rw—- 1 mysql mysql 14171 7月 5 17:01 TA00001DPN.ctb
-rw-rw—- 1 mysql mysql 16962 7月 5 17:01 TA00002000000000.ctb
-rw-rw—- 1 mysql mysql 6015 7月 5 16:34 TA00002000000001.ctb
-rw-rw—- 1 mysql mysql 126 7月 5 16:34 TA00002.ctb
-rw-rw—- 1 mysql mysql 14171 7月 5 17:01 TA00002DPN.ctb
-rw-rw—- 1 mysql mysql 65 7月 5 16:24 Table.ctb
-rw-rw—- 1 mysql mysql 117 7月 5 17:01 TB00000.ctb
-rw-rw—- 1 mysql mysql 103 7月 5 17:01 TB00001.ctb
-rw-rw—- 1 mysql mysql 126 7月 5 17:01 TB00002.ctb

数据存储总结:brighthouse的列式引擎的压缩比,真是相当的高啊!

数据行    csv      csv.tar.gz   MyISAM   brighthouse
50w      20m     3m              14m
500w    222m   37m           156m
2500w  908m   147m         623m      159m

数据查询(2500w):无索引!
SELECT * FROM infobright.t_user_myISAM
where create_date<‘2012-07-05 16:26:32’ limit 1000000,100;
时间>30s

SELECT * FROM infobright.t_user_righthouse
where create_date<‘2012-07-05 16:26:32’ limit 1000000,100;
时间<1s

SELECT count(id) FROM infobright.t_user_myISAM
where create_date<‘2012-07-05 16:26:32’;
时间>30s

SELECT count(id) FROM infobright.t_user_righthouse
where create_date<‘2012-07-05 16:26:32’;
时间<1s

如此可以,brighthouse引擎查询是很快的,远优于无索引的MyIASM。

在t_user_myISAM表创建索引:
CREATE INDEX t_user_myISAM_IDX_1 on t_user_myISAM(id);

-rw-rw—- 1 mysql mysql 8630 7月 6 10:52 t_user_myISAM.frm
-rw-rw—- 1 mysql mysql 631919160 7月 6 10:53 t_user_myISAM.MYD
-rw-rw—- 1 mysql mysql 226201600 7月 6 10:54 t_user_myISAM.MYI
索引占用空间226m

mysql> SELECT count(id) FROM infobright.t_user_myISAM where id<15484646;
+———–+
| count(id) |
+———–+
| 19999998 |
+———–+
1 row in set (16.17 sec)

mysql> explain SELECT count(id) FROM infobright.t_user_myISAM where id<15484646;
+—-+————-+—————+——-+———————+———————+———+——+———-+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+—————+——-+———————+———————+———+——+———-+————————–+
| 1 | SIMPLE | t_user_myISAM | index | t_user_myISAM_IDX_1 | t_user_myISAM_IDX_1 | 5 | NULL | 19999998 | Using where; Using index |
+—-+————-+—————+——-+———————+———————+———+——+———-+————————–+
1 row in set (0.01 sec)

按索引的查询,需要16.17s

mysql> SELECT count(id) FROM infobright.t_user_righthouse where id<15484646;
+———–+
| count(id) |
+———–+
| 24999997 |
+———–+
1 row in set (0.00 sec)

mysql> explain SELECT count(id) FROM infobright.t_user_righthouse where id<15484646;
+—-+————-+——————-+——+—————+——+———+——+———-+———————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——————-+——+—————+——+———+——+———-+———————————–+
| 1 | SIMPLE | t_user_righthouse | ALL | NULL | NULL | NULL | NULL | 24999997 | Using where with pushed condition |
+—-+————-+——————-+——+—————+——+———+——+———-+———————————–+
1 row in set (0.00 sec)

索引总结:即使使用MyISAM的索引查询,对于2500W条数据来说,也不如brighthouse的无索引ALL查询。我们真应该在适合的时间,适合的地点,把列式数据库应用起来!!