mysql 小类型_MySQL表字段门类选择：长度越小越好，类型越简单越好_mysql

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

字段长度越小越好

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

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

表中数据量

操作

并发数

int类型的QPS

bigint类型QPS

N/A

逐渐插入100条数据到空表中

100

1092

1071

1百万

查询

100

5615

5451

注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. 他说,

引用

Storing IPs as a string, besides requiring more disk space, takes 9% longer than storing them as integers, even with the overhead of converting the IP from a string to an integer. If the table uses utf8 encoding, it’s 12% slower.

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 lines = new ArrayList(); 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抄一份，改一下表名即可

欢迎大家阅读《MySQL表字段门类选择：长度越小越好，类型越简单越好_mysql》,跪求各位点评，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~