MySql与MsSql性能对比

MySql与MsSql性能对比

对比前提：同一台服务器，记录总数都是：1317920条

表结构：

CREATE TABLE `me_yarn_monthly_end_detail_stock` (

`MONTHLY_END_STOCK_ID` varchar(38) NOT NULL COMMENT '成纱库存期初ID',

`MONTHLY_END_ID` varchar(38) DEFAULT NULL COMMENT '成纱月结ID',

`CREATE_DATE` datetime DEFAULT NULL COMMENT '创建日期',

`YARN_TYPE_CODE` varchar(50) DEFAULT NULL COMMENT '成纱类别',

`SHADE_CODE` varchar(50) DEFAULT NULL COMMENT '色号代码',

`GODOWN_CODE` varchar(50) DEFAULT NULL COMMENT '仓库',

`YARN_LOT_NO` varchar(50) DEFAULT NULL COMMENT '批号',

`NM` varchar(50) DEFAULT NULL COMMENT '支数',

`QUALITY_CODE` varchar(50) DEFAULT NULL COMMENT '毛纱品质',

`LOCATION_CODE` varchar(50) DEFAULT NULL COMMENT '货位',

`ACCOUNT_CODE` varchar(50) DEFAULT NULL COMMENT '所属公司',

`LAST_MONTHLY_QTY` decimal(15,2) DEFAULT NULL COMMENT '上月结存',

`CHECK_QTY` decimal(15,2) DEFAULT NULL COMMENT '初始化数量',

`IN_QTY` decimal(15,2) DEFAULT NULL COMMENT '入仓数量',

`OUT_QTY` decimal(15,2) DEFAULT NULL COMMENT '出仓数量',

`ADJUSTMENT_QTY` decimal(15,2) DEFAULT NULL COMMENT '调整数量',

`TURNOVER_QTY` decimal(15,2) DEFAULT NULL COMMENT '转仓数量',

`MONTHLY_IN_QTY` decimal(15,2) DEFAULT NULL COMMENT '本月进仓',

`MONTHLY_OUT_QTY` decimal(15,2) DEFAULT NULL COMMENT '本月出仓',

`QUANTITY` decimal(15,2) DEFAULT NULL COMMENT '库存量/kg',

`REMARKS` varchar(800) DEFAULT NULL COMMENT '备注',

PRIMARY KEY (`MONTHLY_END_STOCK_ID`),

KEY `YARN_TYPE_CODE` (`YARN_TYPE_CODE`,`SHADE_CODE`,`GODOWN_CODE`,`YARN_LOT_NO`,`NM`,`QUALITY_CODE`,` LOCATION_CODE`,`ACCOUNT_CODE`),

KEY `MONTHLY_END_ID` (`MONTHLY_END_ID`),

KEY `CREATE_DATE` (`CREATE_DATE`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1、有关联取总记录数的性能对比

MySql:81秒

MsSql:7秒

2、有联的查询

开始条数查询条数MySql用时MsSql用时

5000 10 3秒不到1秒

10000 10 8秒24秒

20000 10 78秒15秒

40000 10 76秒27秒

50000 10 126秒14秒

100000 10 180秒58秒

3、无联的查询

开始条数查询条数MySql用时MsSql用时

5000 10 不到1秒不到1秒

10000 10 不到1秒不到1秒

20000 10 1秒不到1秒

40000 10 2秒不到1秒

50000 10 3秒不到1秒

100000 10 6秒不到1秒

500000 10 31秒5秒

1000000 10 63秒5秒

4、用到的SQL

MySql查总记录数

SELECT COUNT(MONTHLY_END_STOCK_ID) as 'Total'

FROM `me_yarn_monthly_end_detail_stock`

LEFT OUTER JOIN `me_yarn_monthly_end_setting` ON `me_yarn_monthly_end_detail_stock`.`MONTHLY_END_ID` = `me_yarn_monthly_end_setting`.`MONTHLY_END_ID`

LEFT OUTER JOIN `ms_loading_port` ON `me_yarn_monthly_end_detail_stock`.`LOCATION_CODE` = `ms_loading_port`.`LOADING_PORT_CODE`

LEFT OUTER JOIN `ms_godown` ON `me_yarn_monthly_end_detail_stock`.`GODOWN_CODE` = `ms_godown`.`GODOWN_CODE` LEFT OUTER JOIN `ms_vender` ON `me_yarn_monthly_end_detail_stock`.`ACCOUNT_CODE` = `ms_vender`.`VENDER_ID` LEFT OUTER JOIN `ms_yarn_quality` ON `me_yarn_monthly_end_detail_stock`.`QUALITY_CODE` = `ms_yarn_quality`.`QUALITY_CODE`

LEFT OUTER JOIN `ms_godown_location` ON `me_yarn_monthly_end_detail_stock`.`LOCATION_CODE` = `ms_godown_location`.`LOCATION_CODE`;

MySql查询

SELECT me_yarn_monthly_end_detail_stock.*

FROM `me_yarn_monthly_end_detail_stock`

LEFT OUTER JOIN `me_yarn_monthly_end_setting` ON `me_yarn_monthly_end_detail_stock`.`MONTHLY_END_ID` = `me_yarn_monthly_end_setting`.`MONTHLY_END_ID`

LEFT OUTER JOIN `ms_loading_port` ON `me_yarn_monthly_end_detail_stock`.`LOCATION_CODE` = `ms_loading_port`.`LOADING_PORT_CODE`

LEFT OUTER JOIN `ms_godown` ON `me_yarn_monthly_end_detail_stock`.`GODOWN_CODE` = `ms_godown`.`GODOWN_CODE` LEFT OUTER JOIN `ms_vender` ON