最近公司有意将数据库迁移到MySQL,所以专门使用公司业务逻辑对MySQL进行了一下简单的对比测试。该测试使用jmeter模拟并发调用一个只读接口来实现。
系统架构如下:
其中压测软件为jmeter,模拟1000个并发。使用10台负载(每个负载的连接池大小为100)和一台数据服务器。MSSQL为Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64);MySQL为Server version: 5.6.26-74.0-log Percona Server (GPL), Release 74.0, Revision 32f8dfd。
测试结果MSSQL的请求处理能力为7251.6/sec,MySQL很尴尬的只有2694.5/SEC,可怜的三分之一。具体结果如下(图很大加载不全,最好另存为再看,麻烦了):
MSSQL:
MySQL:
MySQL的服务器的CPU使用率很高,CPU中断为8-9W,上下文切换3W左右;IO基本为0;内存有空闲。MySQL的状态和配置文件太长,所以以附件的形式上传。
求解。。。
表tb_shop_goods上有如下索引:
PRIMARY KEY (`f_spid`),
KEY `IX_tb_shop_goods_f_shzh` (`f_shzh`),
KEY `IX_tb_shop_goods_f_shzh_f_sptm` (`f_shzh`,`f_sptm`),
KEY `IX_tb_shop_goods_f_splx` (`f_splx`),
KEY `IX_tb_shop_goods_f_spxl` (`f_spxl`),
KEY `IX_tb_shop_goods_f_sfmg` (`f_sfmg`),
KEY `IX_tb_shop_goods_f_px` (`f_px`),
KEY `IX_tb_shop_goods_f_lmxh` (`f_lmxh`)
查询语句基本都使用到了f_shzh列,但是却又都没有f_sptm列,可以考虑把IX_tb_shop_goods_f_shzh_f_sptm索引删掉。
另外建议你使用f_shzh列做分区试试。
MySQL更重于设计,因为表的连接方式有限,优化器也不够完善,所以建议多设计成主键的查找。
收起这个还得看是读还是写,是写的话,可能MYSQL默认将日志与数据存一个目录(也就是同一块磁盘),可能MSSQL的日志与数据文件分离在不同磁盘上。。。之前有听闻MYSQL在多核心下并发是有问题,但未生产性玩过MYSQL,所以不便猜测,而且版本不同,说法的差异肯定就很大。按常理推测,MYSQL 5.6应该比较成熟了,其白皮书说比MYSQL 5.5性能提升数倍
CREATE TABLE `tb_shop_category` (
`f_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT \'分类id(商户分区开头)\',
`f_shzh` bigint(20) NOT NULL COMMENT \'商户账号\',
`f_lmxh` int(11) NOT NULL COMMENT \'类目序号\',
`f_lmmc` varchar(60) NOT NULL COMMENT \'类目名称\',
`f_sfdj` smallint(6) NOT NULL DEFAULT \'0\' COMMENT \'是否冻结\',
`f_sjlmxh` int(11) NOT NULL DEFAULT \'0\' COMMENT \'上级类目序号\',
`f_lmlj` varchar(255) NOT NULL DEFAULT \'\' COMMENT \'类目路径\',
`f_lmtpmc` varchar(255) NOT NULL DEFAULT \'\' COMMENT \'类目图片名称\',
`f_bz` varchar(255) NOT NULL DEFAULT \'\' COMMENT \'备注\',
`f_jb` int(11) NOT NULL DEFAULT \'0\' COMMENT \'级别\',
`f_mj` int(11) NOT NULL DEFAULT \'1\' COMMENT \'未级\',
`f_sx` int(11) NOT NULL DEFAULT \'0\' COMMENT \'顺序\',
`f_type` int(11) NOT NULL DEFAULT \'0\' COMMENT \'类型\',
`f_cjsj` datetime(3) NOT NULL DEFAULT \'1900-01-01 00:00:00.000\' COMMENT \'创建时间\',
`f_sfzk` int(11) NOT NULL DEFAULT \'0\' COMMENT \'是否展开\',
`f_spsl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'商品数量\',
`f_zlmspsl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'子类目商品数量\',
`f_lmzsx` int(11) NOT NULL DEFAULT \'0\' COMMENT \'类目总顺序\',
PRIMARY KEY (`f_id`),
KEY `IX_tb_shop_category_f_lmxh` (`f_lmxh`),
KEY `IX_tb_shop_category_f_sx` (`f_sx`),
KEY `IX_tb_shop_category_f_sjlmxh` (`f_sjlmxh`),
KEY `IX_tb_shop_category_f_shzh_f_lmxh_f_sx` (`f_shzh`,`f_lmxh`,`f_sx`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_shop_activity_goods` (
`f_hdxh` bigint(20) NOT NULL AUTO_INCREMENT COMMENT \'活动序号\',
`f_spid` bigint(20) NOT NULL COMMENT \'商品id\',
`f_shzh` bigint(20) NOT NULL COMMENT \'商户账号\',
`f_hdlx` smallint(6) NOT NULL DEFAULT \'0\' COMMENT \'活动类型\',
`f_ksrq` int(11) NOT NULL COMMENT \'开始日期\',
`f_jsrq` int(11) NOT NULL COMMENT \'结束日期\',
`f_kssj` int(11) NOT NULL COMMENT \'活动开始时间\',
`f_jssj` int(11) NOT NULL COMMENT \'活动结束时间\',
`f_hdjg` decimal(18,2) NOT NULL COMMENT \'活动价格\',
`f_mrxgsl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'每人限购量\',
`f_hdzsl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'活动总数量\',
`f_hdxl` int(11) DEFAULT \'0\' COMMENT \'活动销量\',
`f_hdpx` int(11) NOT NULL DEFAULT \'0\' COMMENT \'活动排序\',
`f_hdzdsl` int(11) NOT NULL DEFAULT \'0\',
`f_msje` decimal(18,2) NOT NULL DEFAULT \'0.00\',
`f_sfqyxg` tinyint(4) NOT NULL DEFAULT \'0\',
PRIMARY KEY (`f_hdxh`),
KEY `IX_tb_shop_activity_goods_f_ksrq_f_jsrq` (`f_ksrq`,`f_jsrq`),
KEY `IX_tb_shop_activity_goods_f_kssj_f_jssj` (`f_kssj`,`f_jssj`),
KEY `IX_tb_shop_activity_goods_f_hdlx_f_ksrq` (`f_hdlx`,`f_ksrq`),
KEY `IX_tb_shop_activity_goods_f_shzh` (`f_shzh`) USING BTREE,
KEY `IX_tb_shop_activity_goods_f_spid` (`f_spid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_shop_goods` (
`f_spid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT \'商品自增id\',
`f_shzh` bigint(20) NOT NULL COMMENT \'商户账号\',
`f_lmxh` int(11) NOT NULL COMMENT \'类目序号\',
`f_ppbm` varchar(25) NOT NULL DEFAULT \'\' COMMENT \'品牌编码\',
`f_splx` int(11) NOT NULL COMMENT \'商品类型 0系统商品1自编商品\',
`f_sptm` bigint(20) NOT NULL COMMENT \'商品条码\',
`f_spmc` varchar(200) NOT NULL COMMENT \'商品名称\',
`f_spzj` varchar(200) NOT NULL COMMENT \'商品助记\',
`f_ggxh` varchar(200) NOT NULL DEFAULT \'\' COMMENT \'规格型号\',
`f_jldw` varchar(60) NOT NULL DEFAULT \'\' COMMENT \'计量单位\',
`f_lsj` decimal(18,2) NOT NULL DEFAULT \'0.00\' COMMENT \'零售价\',
`f_sptp` varchar(255) NOT NULL COMMENT \'商品图片\',
`f_sptpxgsj` datetime(3) NOT NULL DEFAULT \'1900-01-01 00:00:00.000\' COMMENT \'商品图片修改时间\',
`f_zt` int(11) NOT NULL COMMENT \'状态 -1已删除0仓库中1出售中\',
`f_sxjsj` datetime(3) NOT NULL COMMENT \'上下架时间\',
`f_spxl` decimal(18,2) NOT NULL DEFAULT \'0.00\' COMMENT \'商品销量\',
`f_spgjz` varchar(255) NOT NULL DEFAULT \'\' COMMENT \'商品关键字\',
`f_spgjzzj` varchar(255) NOT NULL DEFAULT \'\' COMMENT \'商品关键字助记\',
`f_spkcl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'商品库存量\',
`f_spms` varchar(4000) NOT NULL DEFAULT \'\' COMMENT \'商品描述\',
`f_spft` varchar(2000) NOT NULL DEFAULT \'\' COMMENT \'商品附图图片\',
`f_sfmg` int(11) NOT NULL DEFAULT \'0\' COMMENT \'是否卖光 0 否 1 是 \',
`f_sfhdsp` tinyint(4) NOT NULL DEFAULT \'0\' COMMENT \'是否活动商品\',
`f_px` int(11) NOT NULL DEFAULT \'0\' COMMENT \'排序\',
`f_yszsl` int(11) NOT NULL DEFAULT \'0\' COMMENT \'已售总数量\',
`f_dnm` int(11) NOT NULL DEFAULT \'0\',
`f_sfxsyjsp` tinyint(4) NOT NULL DEFAULT \'1\',
`f_spfbt` varchar(50) NOT NULL DEFAULT \'\' COMMENT \'商品副标题\',
`f_spzl` int(11) NOT NULL DEFAULT \'0\',
`f_jylsj` decimal(10,0) NOT NULL DEFAULT \'0\',
`f_sjxsdj` decimal(10,0) NOT NULL DEFAULT \'0\',
`f_xtsptm` bigint(20) NOT NULL DEFAULT \'0\',
`f_shzt` int(11) NOT NULL DEFAULT \'0\',
`f_shsj` datetime(3) NOT NULL DEFAULT \'1900-01-01 00:00:00.000\',
`f_dzs` varchar(400) DEFAULT \'\',
`f_xjyy` int(11) DEFAULT NULL,
`f_xjsm` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`f_spid`),
KEY `IX_tb_shop_goods_f_shzh` (`f_shzh`),
KEY `IX_tb_shop_goods_f_shzh_f_sptm` (`f_shzh`,`f_sptm`),
KEY `IX_tb_shop_goods_f_splx` (`f_splx`),
KEY `IX_tb_shop_goods_f_spxl` (`f_spxl`),
KEY `IX_tb_shop_goods_f_sfmg` (`f_sfmg`),
KEY `IX_tb_shop_goods_f_px` (`f_px`),
KEY `IX_tb_shop_goods_f_lmxh` (`f_lmxh`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1、SELECT COUNT (?) FROM `tb_shop` WHERE `f_shzh` = ?
2、SELECT `f_spid` , ? `f_hdlx` , `f_shzh` , `f_sptm` , `f_spmc` , `f_spfbt` , `f_ggxh` , `f_jldw` , `f_sptp` , `f_sptpxgsj` , `f_spxl` , `f_lsj` AS `f_ylsj` , `f_lmxh` , ? `f_sfhdsp` , `f_lsj` , `f_px` , ? `f_sx` , ? `f_hdzsl` , ? `f_mrxgsl` , `f_sfmg` , ? `f_hdxh` , ? `f_mrxgsl_wcl` , ? `f_yhgmsl` , ? `f_msje` , ? `f_sfqyxg` , ? `f_ksrq` , ? `f_jsrq` , ? `f_kssj` , ? `f_jssj` FROM `tb_shop_goods` WHERE `f_shzh` = ? AND `f_zt` = ? AND `f_lmxh` IN (...) AND `f_sfxsyjsp` = ? AND `f_shzt` >= ? ORDER BY `f_sfmg` , `f_hdlx` DESC , `f_px` , `f_sx`
3、SELECT `SG` . `f_spid` , `f_hdlx` , `SG` . `f_shzh` , `SG` . `f_sptm` , `SG` . `f_spmc` , `SG` . `f_spfbt` , `SG` . `f_ggxh` , `SG` . `f_jldw` , `SG` . `f_sptp` , `SG` . `f_sptpxgsj` , `SG` . `f_spxl` , `SG` . `f_lsj` AS `f_ylsj` , ? `f_lmxh` , `SG` . `f_sfhdsp` , `SA` . `f_hdjg` `f_lsj` , `SG` . `f_px` , `SA` . `f_hdpx` `f_sx` , `SA` . `f_hdzsl` , `SA` . `f_mrxgsl` , CASE WHEN `SA` . `f_hdzsl` = ? OR `SG` . `f_spkcl` = ? THEN ? ELSE ? END `f_sfmg` , `SA` . `f_hdxh` , `SA` . `f_mrxgsl` AS `f_mrxgsl_wcl` , ? `f_yhgmsl` , `f_msje` , `f_sfqyxg` , `SA` . `f_ksrq` , `SA` . `f_jsrq` , `SA` . `f_kssj` , `SA` . `f_jssj` , `SG` . `f_splx` FROM `tb_shop_goods` `SG` LEFT JOIN `tb_shop_activity_goods` `SA` ON `SG` . `f_spid` = `SA` . `f_spid` WHERE `SG` . `f_shzh` = ? AND `f_sfhdsp` = ? AND `f_zt` = ? AND `f_shzt` >= ? ORDER BY `f_sfmg` , `f_hdlx` DESC , `f_px` , `f_sx`
4、SELECT COUNT (?) `ItemCount` FROM `tb_shop_goods` `SG` WHERE `f_sfhdsp` = ? AND `f_zt` = ? AND `SG` . `f_shzh` = ?
5、SELECT `f_lmtpmc` , `f_sfzk` , `f_shzh` , `f_lmxh` , `f_sjlmxh` , `f_jb` , `f_spsl` , `f_zlmspsl` , `f_lmmc` FROM `tb_shop_category` WHERE `f_shzh` = ? AND `f_lmxh` != ? AND ( ( `f_jb` = ? AND `f_sjlmxh` = ? AND `f_zlmspsl` > ? ) OR ( `f_jb` = ? AND `f_sjlmxh` != ? AND `f_spsl` > ? ) ) ORDER BY `f_jb` , `f_lmzsx`
6、SELECT `f_spid` , ? `f_hdlx` , `f_shzh` , `f_sptm` , `f_spmc` , `f_spfbt` , `f_ggxh` , `f_jldw` , `f_sptp` , `f_sptpxgsj` , `f_spxl` , `f_lsj` AS `f_ylsj` , `f_lmxh` , ? `f_sfhdsp` , `f_lsj` , `f_px` , ? `f_sx` , ? `f_hdzsl` , ? `f_mrxgsl` , `f_sfmg` , ? `f_hdxh` , ? `f_mrxgsl_wcl` , ? `f_yhgmsl` , ? `f_msje` , ? `f_sfqyxg` , ? `f_ksrq` , ? `f_jsrq` , ? `f_kssj` , ? `f_jssj` FROM `tb_shop_goods` WHERE `f_shzh` = ? AND `f_zt` = ? AND `f_lmxh` IN (?) AND `f_sfxsyjsp` = ? AND `f_shzt` >= ? ORDER BY `f_sfmg` , `f_hdlx` DESC , `f_px` , `f_sx`
收起