mysql 枚举字段加什么索引 mysql 枚举类型需要索引吗

转载

mob64ca140234eb 2024-05-14 12:24:04

文章标签 mysql 枚举字段加什么索引 mysql 数据库 database 字符串 文章分类 MySQL 数据库

2，选择优化的数据类型

2.1，使用枚举(ENUM)类型代替字符串类型

枚举列可以将一些不重复的字符串存储到一个预定义的集合中。MySQL存储枚举的时候非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存数字-字符串的映射关系的查找表。

示例：

mysql 枚举字段加什么索引 mysql 枚举类型需要索引吗_数据库

绕过这种限制的方式有两种：

按照需要的顺序定义枚举列
在查询中使用FIELD()函数显式地制定排序顺序，但这会导致MySQL无法利用索引消除排序

SELECT e FROM enum_test ORDER BY FIELD(e,'apple','dog','fish'); #第二种情况

但如果在定义时就是按照字母的顺序，就没有必要这么做了

枚举的不好的地方是：字符串列表是固定的，添加或者删除字符串必须使用ALTER TABLE。所以对于经常改变的字符串使用枚举，除非能接受只在列表末尾添加元素，这样在MySQL5.1中就可以不用重建整个表来完成修改了。

值得一提的是，因为MySQL会将每个枚举值保存为整数，所以必须进行查找才能转换为字符串，所以会导致一定的开销，但通常情况下，这种开销还可以控制。

2.2，日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。
MySQL能存储的最小时间粒度是秒(MariaDB支持微秒级别的时间类型)。但是MySQL也可以使用微秒级的粒度进行临时运算。

2.2.1，DATETIME 和 TIMESTAMP

DATETIME

使用8个字节的存储空间
能保存大范围的值，从1001年到9999年，精度为秒。
它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关

TIMESTAMP

使用4个字节的存储空间
只能表示从1970年到2038年的时间，表示的时间与时区有关！
保存了从1970年1月1号到现在的秒数
值得一提的是：

在MySQL4.1及之后的版本中，MySQL会以DATETIME的方式格式化TIMESTAMP的值
而4.1及之前的版本中，MySQL不会在各个部分之间显示任何标点符号。
而这仅仅是显示格式上的区别，在存储方式上，任何版本都没有区别

通常情况下，应当尽量使用TIMESTAMP，因为其比DATETIME空间效率要高。

如果需要存储更小粒度的日期与时间，可以使用自己的存储格式：例如可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。两种方式都可以，或者也可以使用MariaDB来代替MySQL

2.3，位数据类型

MySQL有少数几种存储类型使用紧凑的为存储数据，所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型

2.3.1，BIT和SET

BIT

在MySQL5.0之前，BIT是TINYINT的同义词；但在5.0及之后，BIT是一个特性完全不同的数据类型
值得注意的是，MySQL将BIT当作字符串类型，而不是数字类型，同一列数据，在不同的语义场景中，会有不同的结果。
实例：

CREATE TABLE bittest(a bit(8))
INSERT INTO bittest VALUES(b '00111001')
SELECT a,a+0 FROM bittest
# 结果：
+--------+--------+
|a		 |a+0	  |
+--------+--------+
|9		 |57	  |
+--------+--------+

同一个数据【00111001（字节码，二进制值等于57，对应字符为 “9”）】在字符串上下文场景中，结果为9，而在数字上下文场景中，结果为57

SET

SET可以将很多的tru/false值进行合并，它在MySQL内部都是以一些列打包的位的集合来表示的。这样就能有效地利用存储空间。
缺点：

改变列的代价较高，需要ALTER TABLE，这对大表来说是非常昂贵的操作
一般来说无法在SET列上通过索引操作

在整数列上进行按位操作

一种替代SET的方式是用一个整数来包装一系列的位。
例如将8个位包装到一个TINYINT中，并进行按位操作。

好处在于可以不使用ALTER TABLE改变字段代表的”枚举“值
坏处是查询语句更加难写，因为有时候需要对第几个位上的数据进行改写，不容易理解。

示例：

# 这是使用SET列时的查询
CREATE TABLE acl(
	perms SET('CAN_READ','CAN_WRITE','CAN_DELETE');
);
INSERT INTO acl(perms) VALUEs ('CAN_READ,CAN_DELETE');
SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ',perms);
# 结果
+----------------------------+ 
|a		                     | 
+----------------------------+ 
|CAN_READ,CAN_DELETE		 | 
+----------------------------+ 


# 这是使用整数来进行存储
SET @CAN_READ := 1 <<0,
	@CAN_WRITE := 1<<1,
	@CAN_DELETE := 1<<2;

CREATE TABLE acl(
	perms TINYINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);

SELECT perms FROM acl WHERE perms & @CAN_READ;
# 结果
+----+ 
|a	 | 
+----+ 
|5   | 
+----+

2.4，选择标识符

为标识列选择合适的数据类型非常重要。一般来说有可能会用标识列与其它值进行比较。例如关联操作；而在关联操作中，类型之间需要精确匹配，包括像UNSIGNED这样的属性，混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。

在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型

选择标识列的数据类型的小技巧：

整数类型

通常是标识列的最好的选择，因为它们很快并且可以使用 AUTO_INCREAMENT

ENUM和SET类型

通常是一个糟糕的选择，尽管对某些只包含固定状态或者状态的静态“定义表”来说是没有问题的
例如也许会设计一张以某个枚举字段为主键的查找表。这时使用枚举类型作为标识列是可行的，但是大部分情况下都要避免这么做

字符串类型

如果可以的话，尽量避免使用字符串类型作为标识列，因为很消耗空间，并且比整数类型要慢
在MyISAM里使用字符串作为标识列要特别小心，MyISAM默认对字符串使用压缩索引，导致查询慢的多
对于一些随机生成的字符串也要注意，例如MD5(),SHA1(),UUID()产生的字符串，因为其生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

因为插入值会随机的写到索引的不同位置，使得INSERT语句更慢
SELECT语句会变得很慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方【思考与局部性原理有何关联】
随机值将会导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效，如果整个数据集都一样的热，那么缓存任何一部分特定数据到内存都没有好处，如果工作集比内存大，缓存将会有很多刷新和不命中
解决