高性能MySql笔记(1)-数据类型优化

最新推荐文章于 2024-09-01 00:36:29 发布

KAI_CHEN_J

最新推荐文章于 2024-09-01 00:36:29 发布

阅读量115

点赞数

分类专栏： MYSQL数据库文章标签： mysql 数据类型优化

本文链接：https://blog.csdn.net/qq_31638095/article/details/79647692

版权

MYSQL数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

数据类型优化

1. 选择优化的数据类型

MYSQL支持的数据类型非常多，选择正确的数据类型存储对于获得高性能只管重要。下面有几个简单的原则：

（1）更小的通常更好，应该尽量使用能够正确存储的最小数据类型。

（2）简单就好，简单的数据类型通常需要更少的CPU周期。

（3）尽量避免NULL,查询中包含NULL的列，对MYSQL来说更难优化，因为可为NULL的列，是的索引，索引统计，值的比较更加复杂。可为NULL的列会占用更多的空间，在MYSQL中，对可为NULL的列索引，索引结点中会多出一个额外的空间。

2. 整数类型

整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.分别使用的是8,16,24,32,64位存储空间。

整数类型可选UNSINGNED,表示不允许为负数，可以使得正整数的上限提高一倍。例如TINYINT的存储范围是-128~127，而TINYINT UNSIGNED的存储范围是0~255.

3. 实数类型

实数是带有小数的部分。MYSQL即支持精确类型也支持不精确类型。

FLOAT，DOUBLE类型支持使用标准的浮点数进行近似计算。

DECIMAL类型用于存储精确的小数,DECIMAL支持精确计算。浮点类型和DECIMAL类型都支持指定精度，如FLOAT(M,D)。M表示一共显示几位数，D表示小数点的位数。FLOAT使用4个字节，DOUBLE使用8个字节。MYSQL使用DOUBLE作为内部浮点数的计算。因为需要额外空间和计算开销所以应该只在对小数进行精确计算时才使用DECIMAL。

4. 字符串类型

VARCHAR和CHAR是两种主要的字符串类型。

VARCHAR

用于存储可变字符串。他比定长类型更节省空间，因为它仅使用必要的空间（例如越短的字符使用越少的空间）。

VARCHAR需要使用1个或2个字节来保存字符串的长度，如果字符串的长度小于或等于255，则使用1个，否则使用2个。

VARCHAR节省了空间，但是行是变长的，在进行UPDATE时可能使得行增长，这就需要额外的工作。这种情况：MyISAM将行分成不同的片段存储，InnoDB需要分裂页来使行可以放进页内。

在5.0版本以后，MYSQL会保留VARCHAR末尾的空格。

使用VARCHAR较合适的场景：

（1）字符串的最大长度比平均长度大很多

（2）列的更新很少，所以不会有碎片问题

（3）使用了向UTF-8这样复杂的字符集，每个字符使用不同的字节数进行存储。

CHAR

CHAR类型是定长的，MYSQL总是根据定义的长度分配足够的空间，CHAR值会根据需要采用空格进行填充以方便比较。

当存储CHAR时，MYSQL会删除所有的末尾空格

CHAR适合存储很短的数据，或者所有的值都接近同一长度。

5. BINARY和VARBINARY

它们存储的是二进制字符，存储的是字节码而不是字符。与字符串类型的填充也不一样，BINARY使用零字节(\0)填充,而字符串类型使用空格填充，则检索时不会去掉填充。

当需要存储二进制数据时，并且希望使用字节码而不是字符串进行比较时，这些类型是非常有用的。二进制比较的有时不仅体现在大小写敏感上，在比较BINARY字符串时MYSQL每次按照一个字节进行比较，因此二进制比较字符跟简单，速度更快。

6. BLOB和TEXT

BLOB和TEXT都是用来存储很大的数据而设计的字符串类型，分别使用二进制和字符存储方式。

二进制类型包括：TINYBLOB, SAMLLBLOB, MEDIUMBLOB, LONGBLOB

字符类型包括：TINYTEXT,SAMLLTEXT,MEDIUMTEXT,LONGTEXT

当BLOB和TEXT的值太大时，InnoDB会使用外部存储，在行内使用1~4个字节来存储指针，然后在外部存储实际的数据。

MYSQL对BLOB和TEXT进行排序与其他类型不同：它只对每个列的前max_sort_length字节进行排序。使用max_sort_length配置或者ORDER BY SUBSTRING(column,length)来进行指定需要进行排列的字符长度。

7. 枚举类型ENUM

使用枚举来代替常用的字符串，在存储枚举类型时，MYSQL会根据枚举类型的数量将其压缩到1~2个字节存储。会将每个值在列表中位置替换成整数。并且存储一张数字-字符串映射关系表。

如果使用数字作为ENUM枚举常量，这种双重性很容易出现混乱，建议应该避免。

枚举字段是按照内部存储的整数进行排序的而不是定义的字符串进行排序的。绕过这种限制的方式是按照需要的顺序来定义枚举列。

枚举的最严重的缺点是：字符串列表是固定的，添加或删除字符串列需要使用ALTER TABLE.

8. 日期和时间类型

两种日期类型：DATETIME, TIMESTAMP

DATETIME

这个类型能够保存大范围的值，从1001~9999年，精确度为秒。把时间和日期分装到格式为YYYYMMDDHHMMSS的整数中，不依赖时区。使用8个字节存储。MYSQL以一种可排序的，无歧义的格式显示DATETIME值。如2018-2-21 21:47:02，这是ANSI标准定义的时间和日期表示方法。

TIMESTAMP

保存了从1970年1月1日午夜以来的秒数，只是用4个字节来存储, 它和UNIX的时间戳相同。MYSQL提供FROM_UNIXTIME()函数把UNIX时间戳转换成日期，提供UNIX_TIMESTAMP()函数将日期转换成时间戳。TIMESTAMP显示的值依赖时区。默认情况下TIMESTAMP没有指定列的值，会设置这个列的值为当前时间，TIMESTAMP列默认为 NOT NULL。