????????????????????????????????????????飬??????????????????????ò??????￡??????????????????????????????????

?????????????????

??????????????????????????????????????Ч?????Query ??е?????????????????????????????????????????????????ú?????????????????CPU??????????

?????????????

????1???????????????????????????????????????????????????????????? IO ??????????????μ????????

???????????

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT??
`time1` varchar(11) DEFAULT NULL??
`time2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURREN T_TIMESTAMP??
`time3` int(11) DEFAULT NULL??
`stats` tinyint(1) DEFAULT NULL??
PRIMARY KEY (`id`)??
KEY `NewIndex1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0  DEFAULT CHARSET=gbk

????a???????????????????????????????????????????????μ???????б??????idС??200????????

mysql> DELETE FROM test.test WHERE id >2000000;
Query OK?? 231412 rows affected (0.92 sec)

????b????????????????200?????????1.81??

mysql> INSERT INTO test.`test` (time1??time2??time3??stats) SELECT time1??time2??time3??stats FROM test.`test`;
Query OK?? 231412 rows affected (1.32 sec)
Records: 231412 Duplicates: 0 Warnings: 0

????c??????????????

mysql> DELETE FROM test.test WHERE id >2000000;
Query OK?? 231412 rows affected (1.00 sec)

????d?????????

mysql> ALTER TABLE `test`.`test` ADD INDEX `time1_2_3_stats` (`time1`?? `time2`?? `time3`?? `stats`);
Query OK?? 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0

????e???????????????200?????????4.88??

mysql> INSERT INTO test.`test` (time1??time2??time3??stats) SELECT time1??time2??time3??stats FROM test.`test`;
Query OK?? 231412 rows affected (2.27 sec)
Records: 231412 Duplicates: 0 Warnings: 0

????2?????????????????洢?????Щ???????????????п?????????????????

???????磺??????????????????????????δ?С???ò??????????????????????????Щ?????????????????????￡?

CREATE TABLE `friends` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT??
`uid` bigint(20) unsigned DEFAULT ’0′??
`fuid` bigint(20) unsigned DEFAULT ’0′??
`fname` varchar(50) DEFAULT ”??
`fpicture` varchar(150) DEFAULT ”??
`fsex` tinyint(1) DEFAULT ’0′??
`status` tinyint(1) DEFAULT ’0′??
PRIMARY KEY (`id`)??
KEY `fuid` (`fuid`)??
KEY `fuid_fname` (`fuid`??`fname`)??
KEY `uid_stats` (`uid`??`status`)
) ENGINE=MyISAM AUTO_INCREMENT=262145 DEFAULT CHARSET=gbk

???????10???????????????????????4.4M?????????????3.9M??

[root@qa05v /usr/local/mysql/data/test]# du -sh friends.*
12K friends.frm
3.9M friends.MYD
4.4M friends.MYI

?????????е?????????????varchar??Σ??????????????????????????????`fname` varchar(50) DEFAULT ” ????????3??byte?????????????????????50????????????????п???????????