MySQL 内存表效率_MySQL内存表、MyISAM表、MemCache实现Session效率比较

早先问过BleakWind，他认为，给别人做的话MySQL内存表做会话比较好一些，因为MySQL内存表做Session更容易维护(可以制作安装脚本)。这个周末，我进行了一些测试，测试MySQL MyISAM表做会话(对时间给不给索引)、内存表做会话、MemCache做会话的效率比较。

定义会话Session类：

定义会话处理器接口(Session_Handler_Interface)：

实现MySQL内存表Session处理器：

实现MemCache会话处理器：

MySQL内存表测试程序：

MemCache会话测试代码：

MySQL会话表如下：

CREATE TABLE `session` (

`id` char(32) COLLATE utf8_unicode_ci NOT NULL,

`name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,

`time` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

测试结果：

MemCache：

======================================================================================

Concurrency Level: 30

Time taken for tests: 18.234375 seconds

Complete requests: 10000

Failed requests: 0

Write errors: 0

Total transferred: 2774781 bytes

HTML transferred: 280000 bytes

Requests per second: 548.41 [#/sec] (mean)

Time per request: 54.703 [ms] (mean)

Time per request: 1.823 [ms] (mean, across all concurrent requests)

Transfer rate: 148.57 [Kbytes/sec] received

Connection Times (ms)

min mean[+/-sd] median max

Connect: 0 0 1.9 0 31

Processing: 0 53 12.1 46 328

Waiting: 0 52 11.9 46 328

Total: 0 53 12.1 46 328

Percentage of the requests served within a certain time (ms)

50% 46

66% 62

75% 62

80% 62

90% 62

95% 62

98% 62

99% 78

100% 328 (longest request)

MySQL内存表：

=================================================================================

Concurrency Level: 30

Time taken for tests: 20.375000 seconds

Complete requests: 10000

Failed requests: 4694

(Connect: 0, Length: 4694, Exceptions: 0)

Write errors: 0

Total transferred: 3118440 bytes

HTML transferred: 623048 bytes

Requests per second: 490.80 [#/sec] (mean)

Time per request: 61.125 [ms] (mean)

Time per request: 2.038 [ms] (mean, across all concurrent requests)

Transfer rate: 149.45 [Kbytes/sec] received

Connection Times (ms)

min mean[+/-sd] median max

Connect: 0 0 1.9 0 31

Processing: 0 60 7.6 62 125

Waiting: 0 59 7.6 62 125

Total: 0 60 7.5 62 125

Percentage of the requests served within a certain time (ms)

50% 62

66% 62

75% 62

80% 62

90% 62

95% 62

98% 78

99% 78

100% 125 (longest request)

其他测试：

将MySQL实现的数据库引擎改为 MyISAM(依然为 并发 30 测试 10000 次) 结果为：

Requests per second: 440.17 [#/sec] (mean)

Percentage of the requests served within a certain time (ms)

50% 62

66% 62

75% 78

80% 78

90% 78

95% 78

98% 78

99% 78

100% 640 (longest request)

为MyISAM表的 time 列增加索引(因为 会话表 读写次数几乎相等，因此应该效果不明显)，结果为：

Requests per second: 441.08 [#/sec] (mean)

Percentage of the requests served within a certain time (ms)

50% 62

66% 62

75% 78

80% 78

90% 78

95% 78

98% 109

99% 109

100% 156 (longest request)

=================================================================================

结论：

MemCache做会话效率最高，也最灵活，但目前尝不支持查看谁在线等功能，附加的，只能自己增加一个数组记录在线用户、以及最后活跃时间并实现gc等。

MySQL内存表做会话效率也相当的高，另外一个有点是，MySQL内存表似乎更稳定，longest request (125ms)明显的短于 MemCache的(328ms)。不过缺点是，存储的字段数以及字段长度受限。

MySQL MyISAM表做会话在这里居然也达到了440的rps，真不简单。不过您要是等半个小时在测试一次，您就会明白MyISAM表的缺点了，页面几乎崩溃。MyISAM表的缺点是，一旦其中有较多的碎片，这个数据库几乎都不可用了，您注释掉 gc 的代码在这里貌似可以获得更好的效率表现(^_^、当然也可以定时的Optimizer，概率触发或者Cron定时启动也不错)

MyISAM表对time列增加索引对每秒完成的请求数没什么影响，不过有一点需要注意的是，增加索引后，每次完成 request的时间更均匀了,longest request从640ms跌到了156ms。为time列增加索引有助于让完成请求的时间变均匀。

测试平台：

Acer Aspire 4520G：

CPU：AMD Athlon 64 * 2 TK-55

RAM: IG

OS: Windows XP sp2

Web Server: Apache 2.26

PHP: PHP5.25

分享到：

2016-04-05 20:49

浏览 719

评论