MySQL数据类型优化原则

MySQL支持的数据类型很多，选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。

  • 更小的通常更好

应该尽量使用可以正确储存数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。如果无法确定哪个数据类型时最好的，就选择你认为不会超过范围的最小类型。

  • 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整形比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整形更复杂。比如用MySQ内建的类型而不是使用字符串来存储日期和时间、应该用整形存储IP地址。

  • 尽量避免null

如果查询中包含可为null的列，对MySQL来说更难优化，因为可为null的列使得索引、索引统计和值比较都较为复杂。可为null的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为null当列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。

通常把可为null的列改为not null带来的性能提升比较小，所以没有必要在现有的架构中查找并修改这种情况，除非确定这回导致问题。

例外的是在InnoDB使用独立的位存储null值，所以对于稀疏数据(很多值为null，只有少数行的列有非null值)有很好的空间效率，但这一点不适用于MyISAM。

在为列选择数据类型时。

第一步需要确定合适的大类型：数字、字符串、时间等
第二步是选择具体类型。MySQL的很多数据类型都可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不一样，或者需要的物理空间不同。

整数类型

存储整数可以用这几种类型

类型 存储位数
tinyint 8
smallint 16
mediumint 24
int 32
bigint 64

它们可以存储的值的范围-2^(N-1)^到2^(N-1)^-1，其中N是存储空间的位数。
整数类型还有可选有符号属性，表示不允许负值，这可以使正数的上限提高一倍。如tinyint无符号可以存储的范围是0到255，但他允许负值但存储范围是-128到127。

MySQL选择不同的整数类型是决定怎么在内存和磁盘中保存数据的。但是在整数计算一般使用64位但bigint整数，即使32位环境也是一样。(除了聚合函数)

MySQL还可以为整数类型指定宽度。如int(11),但是它不会限制值的合法范围，只是规定了MySQL的一些交互工具(如SQLyog，navicat)用来显示字符的个数。对于存储和计算来说，int(1)和int(11)是相同的。

实数类型

实数是带有小数部分的类型。MySQL中可以用decimal、float和double来存储实数类型。

float和double类型支持使用标准的浮点运算进行近似计算。
decimal类型用于存储精确的小数，在MySQL5.0以及更高的版本中，MySQL服务器自身实现了decimal的高精度计算。

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储，double使用8个字节存储，相比flaot有更高的精度和更大的范围。在浮点计算时，MySQL都使用的double作为内部浮点计算的类型。

在考虑对小数进行精确计算时，使用decimal(如财务数据)，但在数据量大时，可以考虑使用bigint代替decimal，将需要存储但货币单位根据小数的位数乘以相同的倍数存储。这样可以避免decimal精确计算代价高的问题。

float和double使用的是cpu支持的原生浮点计算，所以快。
decimal是MySQL服务器自身实现的高精度计算。

字符串类型

varchar和char是两种最主要的字符串类型。

varchar

varchar类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。

varchar需要使用1个或2个额外字节记录字符串长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

varchar节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。因为行占用的空间增长，并且在页内没有更多的空间可以存储。这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB需要分裂页来使行可以放进页内。其他一些存储引擎也许不在原数据位置更新数据。

使用varchar最合适的场景是：在字符串列的最大长度比平均长度大很多；列更新很少(碎片不是问题)；使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

使用varchar(5)和varchar(200)存储‘hello'的空间开销是一样的，但是使用更长的列会消耗更多的内存，MySQL通常会分配固定大小的内存快来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕，在利用磁盘临时表进行排序同样糟糕。所以最好的策略是只分配真正需要的空间。

char

char类型是定长的。MySQL总是根据定义的字符串长度分配足够的空间。当存储char值时，MySQL会删除所有的末尾空格。char值会根据需要采用空格进行填充以方便比较。

char适合存储很短的字符串，或者所有值都接近同一个长度。例如密码的MD5值。对于经常变更的数据，char也比varchar更好，因为定长的char不容易产生碎片。对于非常短的列，char比varchar在存储空间上也更有效率，因为varchar需要额外字节记录长度。

以上就是MySQL数据类型优化原则的详细内容，更多关于MySQL数据类型优化的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL中数据类型相关的优化办法

    有助于效率的类型选择 1.使你的数据尽可能小 最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小.这能给出巨大的改进,因为磁盘读入较快并且通常也用较少的主存储器.如果在更小的列上做索引,索引也占据较少的资源. 你能用下面的技术使表的性能更好并且使存储空间最小: ·尽可能地使用最有效(最小)的类型.MySQL有很多节省磁盘空间和内存的专业化类型. ·如果可能使表更小,使用较小的整数类型.例如,MEDIUMINT经常比INT好一些. ·如果可能,声明列为NOT NULL

  • Mysql支持的数据类型(列类型总结)

    一.数值类型 Mysql支持所有标准SQL中的数值类型,其中包括严格数据类型(INTEGER,SMALLINT,DECIMAL,NUMBERIC),以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展. 扩展后增加了TINYINT,MEDIUMINT,BIGINT这3种长度不同的整形,并增加了BIT类型,用来存放位数据. 整数类型        字节       范围(有符号)      范围(无符号)          用途 TINYINT  

  • mysql8.0.19基础数据类型详解

    mysql基础数据类型 mysql常用数据类型概览 ![1036857-20170801181433755-146301178](D:\笔记\mysql\复习\1036857-20170801181433755-146301178.png)1. 数字: 整型:tinyinit int bigint 小数: float :在位数比较短的情况下不精准 double :在位数比较长的情况下不精准 0.000001230123123123 存成:0.000001230000 decimal:(如果用小数

  • MySQL数据类型中DECIMAL的用法实例详解

    MySQL数据类型中DECIMAL的用法实例详解

    MySQL数据类型中DECIMAL的用法实例详解 在MySQL数据类型中,例如INT,FLOAT,DOUBLE,CHAR,DECIMAL等,它们都有各自的作用,下面我们就主要来介绍一下MySQL数据类型中的DECIMAL类型的作用和用法. 一般赋予浮点列的值被四舍五入到这个列所指定的十进制数.如果在一个FLOAT(8, 1)的列中存储1. 2 3 4 5 6,则结果为1. 2.如果将相同的值存入FLOAT(8, 4) 的列中,则结果为1. 2 3 4 6. 这表示应该定义具有足够位数的浮点列以便

  • MySQL优化之表结构优化的5大建议（数据类型选择讲的很好）

    殊不知,在N年前被奉为"圣经"的数据库设计3范式早就已经不完全适用了.这里我整理了一些比较常见的数据库表结构设计方面的优化技巧,希望对大家有用. 由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了.反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能

  • 详解MySQL中的数据类型和schema优化

    最近在学习MySQL优化方面的知识.本文就数据类型和schema方面的优化进行介绍. 1. 选择优化的数据类型 MySQL支持的数据类型有很多,而如何选择出正确的数据类型,对于性能是至关重要的.以下几个原则能够帮助确定数据类型: 更小的通常更好 应尽可能使用可以正确存储数据的最小数据类型,够用就好.这样将占用更少的磁盘.内存和缓存,而在处理时也会耗时更少. 简单就好 当两种数据类型都能胜任一个字段的存储工作时,选择简单的那一方,往往是最好的选择.例如整型和字符串,由于整型的操作代价要小于字符,所

  • MySQL操作之JSON数据类型操作详解

    上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容. 概述 mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点.但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的. 创建一个JSON字段的表 首先先创建一个表,这个表包含一个json格式的字段: CREATE TABLE table_name ( id INT NOT NULL

  • MySQL中的数据类型binary和varbinary详解

    前言 BINARY和VARBINARY与 CHAR和VARCHAR类型有点类似,不同的是BINARY和VARBINARY存储的是二进制的字符串,而非字符型字符串.也就是说,BINARY和VARBINARY没有字符集的概念,对其排序和比较都是按照二进制值进行对比. BINARY(N)和VARBINARY(N)中的N指的是字节长度,而CHAR(N)和VARCHAR(N)中N指的是的字符长度.对于BINARY(10) ,其可存储的字节固定为10,而对于CHAR(10) ,其可存储的字节视字符集的情况而

  • 将pymysql获取到的数据类型是tuple转化为pandas方式

    我就废话不多说了,大家还是直接看代码吧! #执行结果转化为dataframe df = pd.DataFrame(list(result)) 补充知识:python pymysql注意事项 cursor.execute 与 cursor.executemany有许多不同的地方 1. execute 中字段的值是字符串形式时必须加引号,但是executemany只需要使用占位符%s,pymysql利用给的参数list自动会加上引号 2.execute返回结果都是数字,但是executemany返回

  • MySQL与Oracle数据类型对应关系(表格形式)

    MySQL与Oracle两种数据库在工作中,都是用的比较多的数据库,由于MySQL与Oracle在数据类型上有部分差异,在我们迁移数据库时,会遇上一定的麻烦,下面介绍MySQL与Oracle数据库数据类型的对应关系. 一.常见数据类型在MySQL与Oracle数据库中的表现形式 说明 mysql oracle 变长字符串 VARCHAR[0-65535] 定义长度默认按字符长度计算,如果是GBK编码的汉字将占用2个字节 VARCHAR2[1-4000] VARCHAR是VARCHAR2的同义词

  • 设置MySQL中的数据类型来优化运行速度的实例

    今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记,来纪念一下随便的字段定义的问题. 回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例. 查询语句: SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_

随机推荐