mysql tinyint和char(1)性能对比

在数据库设计的时候会遇到很多只需要0、1、2这种固定几个值的状态字段,基本上都建议设置为只占一字节的tinyint类型,有些觉得char(1)是一样,毕竟char(1)存储数字和字母时一个字符也只是占一个字节。

mysql是用c++写的,而在c++中字符类型是存放对应ascii码的二进制到存储空间,而整型数字是直接存数字的二进制,虽然最终都是二进制存储,但是环节上有少许不同,同样在msyql查找时也会有所不同,下图摘自小白版c++教程《c++ primer plus》:

今天对tinyint和char(1)做了个简单测试,分表建两个表t1、t2,结构如下:

mysql> show create table t1\G

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `_id` int(11) NOT NULL AUTO_INCREMENT,

  `id` tinyint(4) DEFAULT NULL,

  `title` text,

  PRIMARY KEY (`_id`),

  KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2400096 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

mysql> show create table t2\G

*************************** 1. row ***************************

       Table: t2

Create Table: CREATE TABLE `t2` (

  `_id` int(11) NOT NULL AUTO_INCREMENT,

  `id` char(1) DEFAULT NULL,

  `title` text,

  PRIMARY KEY (`_id`),

  KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2400096 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

两个表唯一不同为id字段类型,总数据量都为2400096,id只有0、1、2三个,为了两个表的数据一样且磁盘上分布也一样,降低IO对测试的影响,分别加载的数据如下:

mysql> select id,count(*) from t1 group by id;

+------+----------+

| id   | count(*) |

+------+----------+

|    0 |  1199998 |

|    1 |  1199998 |

|    2 |       99 |

+------+----------+

3 rows in set (0.55 sec)

 

mysql> select id,count(*) from t2 group by id; 

+------+----------+

| id   | count(*) |

+------+----------+

| 0    |  1199998 |

| 1    |  1199998 |

| 2    |       99 |

+------+----------+

3 rows in set (0.77 sec)

 

查看执行计划:

mysql> explain select _id from test.t2 where id='1';                      

+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra                    |

+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------+

|  1 | SIMPLE      | t2    | ref  | id            | id   | 4       | const | 1170900 | Using where; Using index |

+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------+

1 row in set (0.00 sec)

 

mysql> explain select _id from test.t1 where id=1; 

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra       |

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

|  1 | SIMPLE      | t1    | ref  | id            | id   | 2       | const | 1170601 | Using index |

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

1 row in set (0.00 sec)

 

两个表都使用了id索引,再看看information_schema.tables的信息是否和之前理解的存储字节大小是否有出入:

mysql> select DATA_LENGTH/1024/1024,INDEX_LENGTH/1024/1024,data_free from tables where table_name in ('t1','t2');

+-----------------------+------------------------+-----------+

| DATA_LENGTH/1024/1024 | INDEX_LENGTH/1024/1024 | data_free |

+-----------------------+------------------------+-----------+

|          310.81250000 |            27.56250000 |         0 |

|          313.81250000 |            29.56250000 |         0 |

+-----------------------+------------------------+-----------+

2 rows in set (0.00 sec)

 

两个表大小相差不多,确认char(1)和tinyint占字节数相同,现在直接看执行时间:

mysql> show profiles;

+----------+------------+---------------------------------------------------------------+

| Query_ID | Duration   | Query                                                         |

+----------+------------+---------------------------------------------------------------+

|        1 | 0.60804275 | select count(*) from (select _id from test.t1 where id=1) a   |

|        2 | 0.59277575 | select count(*) from (select _id from test.t1 where id=1) a   |

|        3 | 0.60398000 | select count(*) from (select _id from test.t1 where id=1) a   |

|        4 | 0.69068025 | select count(*) from (select _id from test.t2 where id='1') a |

|        5 | 0.69654200 | select count(*) from (select _id from test.t2 where id='1') a |

|        6 | 0.67788800 | select count(*) from (select _id from test.t2 where id='1') a |

+----------+------------+---------------------------------------------------------------+

 

这样就很明显可以看出为char(1)字段的t2表查询时消耗时间偏多,如果几条几百条的情况根本看不出char(1)和tinyint的差别,毕竟现在CPU的效率是非常高的,这里测试的利用了id=1的数据,有1199998条,这样就可以看出点差别了!!虽然效率差别不是很大,为了生产环境统一以及提升QPS还是使用短小的整型更好.

转载自:http://blog.51cto.com/xiaozhong991/1892569

  • 3
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
chartinyint是数据库中的字段类型,用于存储不同类型的数据。对于状态字段选择,有以下几点解释: 1. char类型:如果长度为1,可以直接从ASCII码查找对应的字符。但是如果长度大于1,相当于使用固定长度字符串,需要额外的存储空间。相对于tinyintchar类型的查询速度可能会慢一些。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [Mysql数据库状态字段类型的选择](https://blog.csdn.net/u012990630/article/details/125703168)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [char、varchar、text、ntext、bigintint、smallinttinyint和bit的区别及数据库的数据类型](https://blog.csdn.net/randyamis/article/details/3311572)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [mysql tinyintchar(1)性能对比](https://blog.csdn.net/u013008898/article/details/107259124)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值