56,687
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE IF NOT EXISTS `company` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET gbk NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `work` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET gbk NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `pact` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`cid` int(4) NOT NULL,
`wid` int(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
KEY `wid` (`wid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `spec` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`pid` int(6) NOT NULL,
`color` varchar(100) CHARACTER SET gbk NOT NULL,
`size` varchar(10) CHARACTER SET gbk NOT NULL,
`qty` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `goods_in` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `goods_out` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `barcode` (
`barcode` int(11) NOT NULL AUTO_INCREMENT,
`in_id` int(6) NOT NULL DEFAULT '0',
`out_id` int(6) NOT NULL DEFAULT '0',
`sid` int(6) NOT NULL,
`qty` int(3) NOT NULL,
`in_wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
`out_wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`barcode`),
KEY `in_id` (`in_id`),
KEY `out_id` (`out_id`),
KEY `spec_id` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE `pact`
ADD CONSTRAINT `pact_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `pact_ibfk_2` FOREIGN KEY (`wid`) REFERENCES `work` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `spec`
ADD CONSTRAINT `spec_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pact` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `barcode`
ADD CONSTRAINT `barcode_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `barcode_ibfk_2` FOREIGN KEY (`out_id`) REFERENCES `goods_out` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `barcode_ibfk_3` FOREIGN KEY (`sid`) REFERENCES `spec` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
SELECT `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM `goods_in` AS `g`,`barcode` AS `b`,`spec` AS `s`,`pact` AS `p`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`pid` = `p`.`id` AND `p`.`cid` = `c`.`id` AND `b`.`in_id` <> 0
GROUP BY `b`.`in_id`
ORDER BY `b`.`in_id` DESC
LIMIT 0, 100;
数据库A
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | name | 102 | NULL | 89 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | PRIMARY,cid | cid | 4 | testA.c.id | 3 | Using index |
| 1 | SIMPLE | s | ref | PRIMARY,pid | pid | 4 | testA.p.id | 6 | Using index |
| 1 | SIMPLE | b | ref | sid,in_id | sid | 4 | testA.s.id | 6 | Using where |
| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | testA.b.in_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
数据库B
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | name | 102 | NULL | 89 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | PRIMARY,cid | cid | 4 | testB.c.id | 2 | Using index |
| 1 | SIMPLE | s | ref | PRIMARY,pid | pid | 4 | testB.p.id | 6 | Using index |
| 1 | SIMPLE | b | ref | sid,in_id | sid | 4 | testB.s.id | 8 | Using where |
| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | testB.b.in_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
SELECT SQL_NO_CACHE `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM (SELECT `g`.`id`,`g`.`date` FROM `goods_in` AS `g`,`goods_barcode` AS `b` WHERE `g`.`id` = `b`.`in_id` AND `g`.`id` <> 0 GROUP BY `g`.`id` ORDER BY `id` DESC LIMIT 0, 100) AS `g`,`goods_barcode` AS `b`, `specification` AS `s`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`cid` = `c`.`id`
GROUP BY `g`.`id`
ORDER BY `g`.`id` DESC;
+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using filesort |
| 1 | PRIMARY | b | ref | in_id,sid | in_id | 4 | g.id | 140 | |
| 1 | PRIMARY | s | eq_ref | PRIMARY,cid | PRIMARY | 4 | testA.b.sid | 1 | |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | testA.s.cid | 1 | |
| 2 | DERIVED | g | range | PRIMARY | PRIMARY | 4 | NULL | 831 | Using where |
| 2 | DERIVED | b | ref | in_id | in_id | 4 | testA.g.id | 140 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------------+------+----------------+
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000123 |
| Opening tables | 0.000020 |
| System lock | 0.000003 |
| Table lock | 0.000087 |
| optimizing | 0.000009 |
| statistics | 0.000104 |
| preparing | 0.000034 |
| executing | 0.000004 |
| Sorting result | 0.000001 |
| Sending data | 0.014002 |
| init | 0.000033 |
| optimizing | 0.000009 |
| statistics | 0.000030 |
| preparing | 0.000010 |
| executing | 0.000070 |
| Sorting result | 0.000024 |
| Sending data | 0.108234 |
| end | 0.000004 |
| removing tmp table | 0.000008 |
| end | 0.000016 |
| query end | 0.000003 |
| freeing items | 0.000074 |
| removing tmp table | 0.000004 |
| closing tables | 0.000004 |
| logging slow query | 0.000001 |
| cleaning up | 0.000013 |
+--------------------+----------+
SELECT `g`.`id`,DATE_FORMAT(`g`.`date`, '%Y-%m-%d') AS `date`,SUM(`b`.`qty`) AS `qty`,SUM(`b`.`in_wgt`) AS `wgt`,GROUP_CONCAT(DISTINCT `c`.`name`) AS `name`
FROM `goods_in` AS `g`,`barcode` AS `b`,`spec` AS `s`,`company` AS `c`
WHERE `g`.`id` = `b`.`in_id` AND `b`.`sid` = `s`.`id` AND `s`.`cid` = `c`.`id` AND `g`.`id` <> 0
GROUP BY `g`.`id`
ORDER BY `g`.`id` DESC
LIMIT 0,100;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | g | range | PRIMARY | PRIMARY | 4 | NULL | 531 | Using where |
| 1 | SIMPLE | b | ref | in_id,sid | in_id | 4 | testA.g.id | 26 | |
| 1 | SIMPLE | s | eq_ref | PRIMARY,cid | PRIMARY | 4 | testA.b.sid | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | testA.s.cid | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+