mysql数据类型优化

最新推荐文章于 2021-02-28 12:04:29 发布

原创最新推荐文章于 2021-02-28 12:04:29 发布 · 284 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文探讨了数据库设计中的关键优化策略，包括选择合适的数据类型如整型、字符串类型等，避免使用NULL值，以及如何利用枚举类型和时间戳类型提高效率。通过实例说明了不同数据类型的存储方式和性能影响。

几个优化原则

更小的数据类型通常更好，因为占用了更少的磁盘、内存、和cpu缓存
简单数据类型，简单的数据类型需要更少的cpu周期。例如整型比字符型更好。因为字符集和校对规则使字符更加复杂。有两个例子，应该用mysql内建的类型而不是字符串来存储日期和时间。应该用整型来存储ip地址。
尽量避免null，如果计划在列上建索引，就应该尽量避免设计可以成为null的列。
ps.DATATIME和TIMESTAMP都可以存储时间，但是TIMESTAMP只使用了一半的存储空间，并且会根据时区变化，但是TIMESTAMP允许的时间范围较小。

整型

使用整型可以使用这几个类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别占用8、16、24、32、64位存储空间。存储值的范围为-2的N-1次方到2的N-1次方-1。N是位数。整型有可选的UNSIGNED，表示不可以为负值，会使存储空间翻倍，例如TINYINT存储为-128~127。TINYINT UNSIGNED存储空间为0~255。可以指定整型宽度，但是没有什么意义，因为对存储和计算来说INT(1)和INT(100)是相同的。

字符串类型

varchar，存储可变长字符串，varchar需要1或2个字节来存储字符串长度，如果字符串长度小于等于255使用1个字节，否则使用2个字节。
但是，由于是变长的，所以在update操作时可能使行变得比原来长，就会导致额外的工作。如果一个行占用的空间增长，并且页内没有更多的空间存储，myisam会将行拆成不同的段存储，innodb会分裂页来使行可以放进页内。
以下情况适合varchar：字符串最大长度比平均长度大很多；列更新少；使用了utf-8这样的复杂字符集
char，定长字符串。char适合存储很短的字符串，或者所有值都接近同一个长度，例如密码的MD5值。对于经常变更的也适合，因为不容易产生碎片。
类似的还有BINARY和VARBINARY，存储二进制字符串。二进制字符串在比较时，每次按一个字节的值进行比较，比字符串比较更快。

BLOB和TEXT类型

两个家族，TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT。TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。
BLOB存储的二进制数据，没有排序规则或字符集。TEXT类型有字符集和排序规则。

ENUM类型

有时候可以用枚举类型代替字符串类型，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字–字符串”映射关系的查找表。
例如：

CREATE TABLE enum_test(
	e ENUM('fish','apple','dog') NOT NULL
	);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');
SELECT e+0 FROM enum_test;
------------输出-------------
+-----------------+-
| e+0              | 
+-----------------+-
| 1                 |
| 3                 |
| 2                 |
+-----------------+-
**枚举字段在内部按存储的整数进行排序，而不是字符串**
SELECT e FROM enum_test ORDER BY e;
------------输出-------------
+-----------------+-
| e                 | 
+-----------------+-
| fish              |
| apple             |
| dog               |
+-----------------+-

TIMESTAMP和DATATIME

DATATIME	TIMESTAMP
8个字节	4个字节
默认为null	默认为当前时间
时间的值怎么存入怎么取出	会先转化为世界标准时间存储，取的时候根据客户端时区转化为对应时区的时间
存储范围1001年到9999年	存储范围为1970-2038年

位数据类型

MYSQL把bit当做字符串类型，而不是数字类型。当检索bit(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的0或1，然而在数字上下文场景中检索时，会将位字符串转成数字。所以通常不建议使用bit

CREATE TABEL bittest(a bit(8));
INSERT INTO bittest VALUES(b'00111001');
SELECT a,a+0 FROM bittest;
+------------+------------+-
| a          | a+0        |
+------------+------------+-
| 9          | 57         |
+------------+------------+-
**57在ASCII码中对应的字符是‘9’**

如果想存储true/false值，可以使用CHAR(0),保存NULL值和长度为0的字符串。

一个范式的例子

需求：查询付费用户的最近十条消息
**如果是两个表，那么联合查询sql**
SELECT message_text,user_name
FROM message
INNER JOIN user on message.user_id = user.id
WHERE user.account_type = 'premium'
ORDER BY message.published DESC LIMIT 10;

**如果是一个表，且增加了（account_type,published）索引**
SELECT message_text,user_name
From user_messages
WHERE account_type = 'premium'
ORDER BY published DESC LIMIT 10;

一个时间相关的查询例子

需求：查询过去24小时，发消息的总数。msg_pre_hr记录了每个时间发的信息数量。
SELECT SUM(cnt) FROM msg_pre_hr
WHERE hr between
	CONCAT(LEFT(NOW(),14), '00:00')-INTERVAL 23 HOUR
	AND CONCAT(LEFT(NOW(),14), '00:00')-INTERVAL 1 HOUR;
-----------------------------------------------------------
SELECT COUNT(*) FROM message
WHERE posted>= NOW()-INTERVAL 24 HOUR
	AND posted< CONCAT(LEFT(NOW(),14),'00:00')-INTERVAL 23 HOUR
----------------------------------------------------------------
SELECT COUNT(*) FROM message
WHERE posted>=CONCAT(LEFT(NOW(),14),'00:00');
**把三个sql语句结果加起来**