《高性能MySQL》笔记（1）——Schema与数据类型优化

2018-03-14  本文已影响50人  esrever

Schema与数据类型优化

最常用的数据类型分类

在PHPmyadmin工具中，还可以看到两种大类型：spatial（空间类型）、JSON，不过使用很少

如何选择数据类型？

整数类型的范围

类型 存储位数 有符号的max(2^(n-1)-1) 显示最大宽度
TINYINT 8 127 4
SMALLINT 16 32767 6
MEDIUMINT 24 8388607 8
INT 32 2147483647 11
BIGINT 64 9223372036854775807 20

INT(11)中的11表示为整数类型指定显示的宽度。所以，对于存储与计算来说，INT(1)和INT(20)是相同的

实数（带有小数部分的数字）类型

1、 FLOAT、DOUBLE类型

精度不足，仅支持近似计算

但是由于CPU可以直接支持原生浮点型计算：

2、 DECIMAL类型

MySQL 5.0及以上的版本，将数字打包保存到一个二进制字符串中（每4个字节存9个数字），且小数点占1个字节。最多允许65个数字

建议：因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如财务数据。在数据量比较大时，可以考虑使用BIGINT代替DECIMAL，乘以相应的倍数得到最终的结果。这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

1、 VARCHAR、CHAR类型

最主要的两种字符串类型，一般存储的数据量较小

VARCHAR的特点：

使用VARCHAR的场景：

另外需要注意的： 虽然VARCHAR(5) 和 VARCHAR(200)来存储hello字符串，空间开销是一样的，但是后者需要分配更多的内存去保存内部值和排序。所以最好的策略是只分配真正需要的空间

InnoDB可以把过长的VARCHAR存储为BLOB

CHAR的特点：

使用CHAR的场景：

2、 BLOB、TEXT类型

为存储很大的数据而设计的字符串数据类型，BLOB采用二进制，TEXT采用字符方式存储

特点：

mysql> select @@max_sort_length;
+-------------------+
| @@max_sort_length |
+-------------------+
|              1024 |
+-------------------+
1 row in set (0.00 sec)

问题： 如果查询使用了BLOB、TEXT类型并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表(即EXPLAIN的Extra列包含Using temporary)，造成严重的性能开销！

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
|              16777216 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@tmp_table_size;
+------------------+
| @@tmp_table_size |
+------------------+
|         16777216 |
+------------------+
1 row in set (0.00 sec)

3、 使用枚举(ENUM)代替字符串类型

相对固定且少量的字符串，可以用枚举类型代替，在MySQL中存储为整数，并用一个“数字 - 字符串”的映射表保存

mysql> create table enum_test(e ENUM('fish', 'apple', 'dog') NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO enum_test(e) VALUES ('fish'), ('dog'), ('apple');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select e from enum_test;
+-------+
| e     |
+-------+
| fish  |
| dog   |
| apple |
+-------+
3 rows in set (0.00 sec)

mysql> select e+0 from enum_test;
+-----+
| e+0 |
+-----+
|   1 |
|   3 |
|   2 |
+-----+
3 rows in set (0.01 sec)

mysql> desc enum_test;
+-------+----------------------------+------+-----+---------+-------+
| Field | Type                       | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+-------+
| e     | enum('fish','apple','dog') | NO   |     | NULL    |       |
+-------+----------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

有些问题需要注意：

4、 日期和时间类型

MySQL能存储的最小时间粒度为秒，最常用的类型：DATETIME、TIMESTAMP

DATETIME

从1001年到9999年，把日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储

TIMESTAMP（无特殊要求的情况下，建议使用）

从1970年到2038年，对应UNIX时间戳，依赖于时区，只需要4个字节存储

存储比秒更小粒度的时间：

范式与反范式

在范式化的数据库中，每个事实数据会出现且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方

范式的优缺点：

反范式的优缺点：

缓存表和汇总表

有时提高性能最好的方法是在同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表

缓存表示例： 可以把复杂查询的结果放在一个索引合理的表中，便于多次查询

汇总表示例： 假设需要计算之前24小时内发送的消息数，可以每小时生成一张汇总表，或者在汇总表的基础上，把之前23个完整的小时的统计表中的计数全部加起来，最后加上当前小时内的计数即可

在重建缓存表和汇总表的时候，通常需要保证数据在操作时依然可用，需要通过“影子表”来实现

DROP TABLE IF EXISTS my_s_new, my_s_old;
CREATE TABLE my_s_new LIKE my_s;
-- 按照需要去填充my_s_new
RENAME TABLE my_s TO my_s_old, my_s_new TO my_s;

计数器表的例子

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题，出现一个全局的互斥锁（mutex），这会使得这些事务只能串行执行

可以预先在一张表中增加100行数据，随机选择一个槽（slot）进行更新，使用聚合查询来统计总数

UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;

可以用ON DUPLICATE KEY UPDATE来代替预先生成行，统计每日的数据

INSERT INTO daily_hit_counter(day, slot, cnt) VALUES (CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

上面的方法都是“更快地读，更慢地写”，通过建立一些额外索引、增加冗余列、创建缓存表和汇总表，虽然增加写查询的负担，但是会提升读查询的速度

上一篇下一篇

猜你喜欢

热点阅读