January 19, 2016

MySQL优化-《高性能MySQL》笔记¶

数据类型¶

数据库就是用来存放数据的，对数据的存储，操作是要消耗各种资源的（磁盘，内存，CPU），所以在选择存储数据类型是应该遵循在满足需要的条件下更小，更简单的数据类型，尽可能不要使用NULL。

对于数值通常要考虑取值范围、精度问题，对于字符通常需要了解要存储字符的长度，还有时间，二进制数据等

整型¶

整数类型通常有：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT，分别需要1, 23, 4, 8个字节的空间存储。还可以使用有符号和无符号，它们占用的存储空间大小一样，性能也是一样的。MySQL内部计算整型时通常使用8字节大小的BIGINT。

实型¶

FLOAT, DOUBLE支持标准的浮点运算进行近似计算，它们分别占用4个和8个字节。MySQL内部对浮点类型使用DOUBLE进行计算。

DECIMAL类型可以保存精确的小数，支持精确的数据运算。DECIMAL类型可以定义小数点前、后的最大位数，最多65个数字（不同版本可能有差异）。DECIMAL被存储为一个二进制字符串（第4个字节保存9个数字）。如：DECIMAL(18, 9)小数点前后均保存9位数字，总共需要使用9个字节（WHY?）。在计算时，DECIMAL类型被转换为DOUBLE。

字符串类型¶

字符串类型涉及到字符集和排序规则方面的的问题。

字符串的存储方式与存储引擎有极大的关系。

VARCHAR和CHAR¶

VARCHAR类型存储时只占用字符串实际大小空间，比固定大小的类型占用更少的存储空间。VARCHAR类型需要1到2个额外的字节来存储字符串的长度。VARCHAR类型适合存储很少进行更新的字符串，因为如果更新时字符串长度发生变化，会引起数据碎片（不同存储引擎行为不同），进而影响性能。

另外，对于VARCHAR类型，如果使用属性ROW_FORMAT=FIXED创建的MyISAM表，每行将会使用固定长度空间，造成空间浪费。

对于VARCHAR类型的列，被存储的字符串末尾如果有空格，可能会被存储引擎删除。

CHAR字符类型的长度是固定的，适用于哪些长度相近的字符串存储，对于需要经常变化的值也不会产生碎片。如果字符串长度小于设定长度，空格将会填充至末尾。

BLOB和TEXT¶

BLOB是以二进制形式保存大量数据的；TEXT是以文本形式来保存大量数据的。对于二进制数据BLOB没有字符集和排序规则的问题。对于长文本TEXT只会按照max_sort_length=N规定的前N个字符进行排序；也可以使用ORDER BY SUBSTRING(col, len)来排序。

由于Memory存储引擎不支持这两种数据类型，所以使用了BLOB, TEXT列的临时表查询将不得在磁盘上建立临时表，会导致非常高的性能开销。非必要时应避免使用这两种数据类型。另外临时表的大小受到max_heap_size=N和tmp_table_size=N的限制，如果超过将会在磁盘上建立临时表。

ENUM¶

以ENUM类型来代替字符串类型，可以大大节省存储空间。ENUM类型以整型存储，以表示值在列表中的位置，并且还保留了一份“查找表”来表示整数和字符串的对映关系。可以把它们按数值形式取出；

use test

start transaction;

create table enum_test(
    e ENUM('fish', 'apple', 'dog', 'cat', 'eagle')
);

insert into enum_test(e) values
    ('fish'),
    ('cat'),
    ('apple'),
    ('eagle'),
    ('dog');

select e + 1 from enum_test;
-- +-------+
-- | e + 1 |
-- +-------+
-- |     2 |
-- |     5 |
-- |     3 |
-- |     6 |
-- |     4 |
-- +-------+

select e from enum_test order by e;
-- e
-- fish
-- apple
-- dog
-- cat
-- eagle

commit;

需要注意的有：

ENUM字段内部是按数字进行排序的，而不是字符串，这一点极可能引起混乱。（如上面的ORDER BY查询）
ENUM的字符串列表是固定的，添加或删除字符串时必须使用ALTER TABLE来完成。所以，对于可能会改变的字符串，使用ENUM类型可能不太方便。在 MySQL内部权限表中使用ENUM来保存``Y | N``值的。
ENUM类型与其它字段联结时会相对较慢（原因：内部是整型保存的，连接时要用字符串，需要额外的查找）

日期、时间类型¶

MySQL可保存的最细时间粒度是秒。

DATETIME 时间范围从1001年到9999年，与时区无关，占用8个字节。

TIMESTAMP保存了从Unix元年（1970年1月1日）以来的秒数，与Unix时间戳相同，占用4个存储空间。时间范围从1970年至2038年。TIMESTAMP的显示依赖于时区。 MySQL服务器，操作系统，客户端都有时区的设置。

在默认情况下，插入行没有TIMESTAMP列的值时，MySQL会将其设定为当前时间。在更新时，没有显式定义TIMESTAMP列值时，MySQL也会自动更新它。TIMESTAMP列默认为NOT NULL。

MySQL另外提供了两个函数来操作Unix时间：

FROM_UNIXTIME()将Unix时间戳转换为日期；
UNIX_TIMESTAMP()将日期转换为Unix时间戳。

特殊类型¶

如IP地址，实际上是一个无符号32位的整数，所以最小使用无符号整数来保存IP地址，MySQL提供了INET_ATON()和INET_NTOA()两个函数来完成IP地址与整数之前的转换。

SELECT INET_ATON('127.0.0.1');
-- +------------------------+
-- | inet_aton('127.0.0.1') |
-- +------------------------+
-- |             2130706433 |
-- +------------------------+

表的正则化和非正则化¶

正则化的设计使得重复数据少，数据更新更快；同时对于一些查询可能需要使用到联接，会降低查询性能。

非正则化与正则化刚好相反，不用联接，查询性能较好，但数据冗余大。

缓存与汇总表¶

存储引擎的特点¶

MyISAM存储引擎¶

表级锁
不支持自动恢复
不支持事务。MyISAM甚至不保证单个任务会完成。
只有索引被缓存在内存中，表数据由操作系统缓存管理
紧密存储。磁盘上数据占用空间较小，全表扫描较快。

Memory存储引擎¶

表级锁
不支持动态行（即长度可变行）
默认索引为哈希索引
没有索引统计
内存中，重启数据当然没有了

InnoDB存储引擎¶

支持事务和四种事务隔离级别
支持外键
等级锁
多版本。（没太明白）
按主键聚集
所有索引包含主键列-请保持主键较短
优化缓存，自动构建哈希索引加速
索引未压缩，所以索引比较大
数据加载慢
阻塞AUTO_INCREMENT
没有缓存COUNT(*)的值。即没有保存表的行数。

参考资料¶

高性能MySQL（第二版）

Posted by LiuHui

Filed under: database, mysql

Tags: database, mysql, dba

Life

Learning, Promotion