MySQL表字段类型选择：长度越小越好，类型越简单越好

字段长度越小越好

字段长度越大，占用的内存、磁盘空间越大，读写时的I/O代价就越高, 同时占用的cpu周期越多。

所以，能用int, 就别用bigint.  不过，benchmarking表明，这个差别其实也不是很大。

注1：阿里云服务器，CPU 2核, 内存4GB, 64位CentOS, MySQL版本5.1.73,InnoDB

注2：每轮执行完后都会重启MySQL, 以消除缓存的影响

字段类型越简单越好

字段类型越复杂，占用的cpu周期越多；复杂类型的字段处理起来可能还有额外的逻辑，导致更加耗时。比如varchar类型的大小比较会牵涉到charset和collation，逻辑相对复杂，性能不如int类型。

所以，

1. 能用int, 就别用varchar

2. 如果对精度要求不高，能用float/double, 就不要用decimal

这里有人对“把IP存成varchar还是unsigned int”做了下benchmarking. 他说,

9%-12%的差别，虽然不是很大，但还是值得珍惜一下的。

附： int v.s. bigint的Super Smack测试数据准备

建表

drop table if exists use_int;
drop table if exists use_big_int;

create table use_int (
         id bigint unsigned not null auto_increment,
         num int not null, 
         key idx_num (num),
         primary key(id)
);

create table use_big_int (
         id bigint unsigned not null auto_increment,
         num bigint not null, 
         key idx_num (num),
         primary key(id)
);

生成数据文件

package my.tools.mysql.supersmack;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.math.RandomUtils;

/**
 * 
 * @author chenjianjx
 * 
 */
public class GenWholeNumberLengthData {

	public static void main(String[] args) throws IOException {
		File dir = new File(System.getProperty("user.home")
				+ "/smack-whole-number");
		File dataFile = new File(dir, "/data.txt");

		int numOfRows = 1000000;
		
		
		List<Integer> lines = new ArrayList<Integer>();

		for (int i = 1; i <= numOfRows; i++) {
			int num = RandomUtils.nextInt(2000000000);
			lines.add(num);

		}
		
		FileUtils.writeLines(dataFile, lines);
		System.out.println("Done. please check " + dir);
	}
}

int类型,插入性能测试的smack

dictionary "word"
{
  type "rand"; // words are retrieved in random order
  source_type "file"; 
  source "/root/software/Super-Smack-master/whole-number-smacks/data.txt" ;
  file_size_equiv "45000"; 
}  

query "insert_num"
{
  query "insert into use_int(num) values($word)"; 
  type "insert";
  parsed "y";
}

client "myclient"
{
 user "root"; 
 pass "root"; 
 host "localhost"; 
 db "kentbench"; 
 socket "/var/lib/mysql/mysql.sock"; 
 query_barrel "1 insert_num"; 
}

main
 {
  myclient.init(); 
  myclient.set_num_rounds($2); 
  myclient.create_threads($1);
  myclient.connect();
  myclient.unload_query_barrel(); 

  myclient.collect_threads();
  myclient.disconnect();
 }

int类型,查询性能测试的smack

dictionary "word"
{
  type "rand"; // words are retrieved in random order
  source_type "file"; 
  source "/root/software/Super-Smack-master/whole-number-smacks/data.txt" ;
  file_size_equiv "45000"; 
}  

query "select_num"
{
  query "select * from use_int where num = '$word'"; 
  type "select";
  has_result_set "y";
  parsed "y";
}

client "myclient"
{
 user "root"; 
 pass "root"; 
 host "localhost"; 
 db "kentbench"; 
 socket "/var/lib/mysql/mysql.sock"; 
 query_barrel "1 select_num"; 
}

main
 {
  myclient.init(); 
  myclient.set_num_rounds($2); 
  myclient.create_threads($1);
  myclient.connect();
  myclient.unload_query_barrel(); 

  myclient.collect_threads();
  myclient.disconnect();
 }

bigint类型,性能测试的smack

把对应的int类型smack抄一份，改一下表名即可