【mysql】性能调优实验

mysql版本：mysql  Ver 14.12 Distrib 5.0.37, for pc-linux-gnu (i686) using readline 5.0

1、未调优的情况

my.cnf的主要配置参数：

[mysqld]

skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

 

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

 

[mysqlhotcopy]
interactive-timeout

建两张表：

 

CREATE TABLE `T_TRANS_INFO_INNODB` (
  `MID` varchar(32) NOT NULL DEFAULT '',
  `SCHOOLNO` char(5) NOT NULL DEFAULT '',
  `MOBILENO` char(12) NOT NULL DEFAULT '' COMMENT '手机号',
  `CARDNO` char(32) NOT NULL DEFAULT '' COMMENT '卡号',
  `TRANSTYPE` char(2) NOT NULL DEFAULT '' COMMENT '交易类型',
  `AMOUNT` int(11) NOT NULL DEFAULT 0 COMMENT '金额',
  `TRANSTIME` char(20) NOT NULL DEFAULT '' COMMENT '交易时间，格式yyyy-MMdd HH:mm:ss',
  `INTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
  `MODTIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
  `RESERVED1` varchar(32) NOT NULL DEFAULT '' COMMENT '扩展字段1',
  `RESERVED2` varchar(32) NOT