[High.Performance.MySQL(3rd,2012.3)].Baron.Schwartz.之数据类型优化

选择合适的数据类型

越小越好

选择能够存储和表示数据的最小的数据类型。

越简单越好

处理简单的数据类型耗费越少的CPU cycles。
在MySQL中应该使用内置的数据类型保存dates和times.
应该使用整数保存IP地址。

如果可能，避免使用NULL

对于MySQL来说，可NULL的列很难优化。因为nullable column占用更多的存储空间，在MySQL中需要特殊处理。

Whole Numbers

整数分为TINYINT，SAMLLINT，MEDIUMINT，INT，BIGINT
对应8,16,24,32,64位的存储空间。
整形可以带UNSIGNED属性。

Real Numbers

FLOAT和DOUBLE类型支持近似计算，计算速度快。
DECIMAL支持精确计算。
存储同样类型的值浮点类型耗费空间比DECIMAL类型少。
一个FLOAT占用4个字节，DOUBLE占用8个字节。

DECIMAL(18, 9) will store nine digits from each side of the decimal point, using nine bytes in total: four for the digits before the decimal point, one for the decimal point itself, and four for the digits after the decimal point.

String Types

VARCHAR and CHAR Types

VARCHAR 是可变长度字符串，仅占用所需的空间。
VARCHAR 使用1或2个字节来保存字符串长度：1个字节最大长度胃255，更长的长度
使用2个字节保存。
VARCHAR在保存和查询字符串时会保存空格。

CHAR

CHAR是固定长度:MySQL为特定数量的字符串分配固定长度空间。
CHAR在保存和查询是会去掉尾部的空格。

mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES
-> ('string1'), (' string2'), ('string3 ');

检索时：

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| ' string2'                 |
| 'string3'                  |
+----------------------------+

如果用VARCHAR保存

mysql> SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;

+-------------------------------+
| CONCAT("'", varchar_col, "'") |
+-------------------------------+
| 'string1'                     |
| ' string2'                    |
| 'string3 '                    |
+-------------------------------+

BLOB and TEXT Types

the character types are TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, and the binary types are TINYBLOB, SMALLBLOB,
BLOB, MEDIUMBLOB, and LONGBLOB. BLOB is a synonym for SMALLBLOB, and TEXT is a synonym
for SMALLTEXT.

MySQL sorts BLOB and TEXT columns differently from other types: instead of sorting the full length of the string, it sorts only the first max_sort_length bytes of such columns. If you need to sort by only the first few characters, you can either decrease the max_sort_length server variable or use ORDER BY SUBSTRING(column, length).
MySQL在排序BLOB and TEXT列时，根据该列的前max_sort_length字节的字符进行排序。自定义排序时可以使用ORDER BY SUBSTRING(column, length)方法。

Date and Time Types

DATETIME

This type can hold a large range of values, from the year 1001 to the year 9999,
with a precision of one second. It stores the date and time packed into an integer in YYYYMMDDHHMMSS format, independent of time zone. This uses eight bytes of storage space.
By default, MySQL displays DATETIME values in a sortable, unambiguous format, such as 2008-01-16 22:37:08. This is the ANSI standard way to represent dates and times.
DATETIME保存的时间格式为 2008-01-16 22:37:08

TIMESTAMP

the TIMESTAMP type stores the number of seconds elapsed since
midnight, January 1, 1970, Greenwich Mean Time (GMT)—the same as a Unix timestamp. TIMESTAMP uses only four bytes of storage, so it has a much smaller range than DATETIME
TIMESTAMP保存的是从midnight, January 1, 1970, Greenwich Mean Time (GMT)时刻开始到现在的秒数。

IIMESTAMP also has special properties that DATETIME doesn’t have. By default, MySQL will set the first TIMESTAMP column to the current time when you insert a row without specifying a value for the column.
TIMESTAMP在插入时如果没有制定值，第一个TIMESTAMP会设置为当前时间。

Bit-Packed Data Types

BIT

在MySQL5.0之前，BIT和TINYINT是一样的，但是在MySQL5.0和往后的版本中，这是一个完全不同的数据类型，具有特殊的字符。

你可以使用一个BIT来在一个列中存储多个true/false 。BIT(1)定义一个域包含单个位，BIT(2)存储2个位，一个BIT最多可以存储64位。

BIT在InnoDB引擎中，存储每一个BIT列时按照能够包含这些位的最小整数来存储。
MySQL把BIT当做一个string类型而不是数字类型。当你检索BIT值的时候，得到的是一个string,内容是二进制的0或1的，不是ASCII的0或1。
举例：当你把值b’00111001’（57的二进制形式）存入BIT(8),然后检索，你会得到字符码57刚好是9的ASCII码值。但是在数学上下文中，你会得到数值57.

mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;

+------+-------+
| a    | a + 0 |
+------+-------+
| 9    |   57  |
+------+-------+

Schema Design Gotchas in MySQL

1 太多列
2 太多join
. 能使用TINYINT的地方使用ENUM
使用ENUM的地方使用SET

You can configure MySQL’s
SQL_MODE to disallow nonsense dates, which is an especially good practice for a new
application that hasn’t yet created a database full of bad data.

Pros and Cons of a Denormalized Schema

当使用join但是数据不能完全装进内存的时候，不适用join比使用join要更快。
单个表可以使用更加高效的index策略。

Cache and Summary Tables

查找前24小时内用户提交的评论数量。

mysql>SELECT SUM(cnt) FROM msg_per_hr
->WHERE hr BETWEEN
->    CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
->    AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;

mysql>SELECT COUNT(*) FROM message
->WHERE posted >= NOW() - INTERVAL 24 HOUR
->    AND posted < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;

mysql>SELECT COUNT(*) FROM message
->    WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');

替换数据库方法

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
-- populate my_summary_new as desired
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

快速ALTER TABLE

快速修改列的默认值

普通修改表列的默认值的命令是：

mysql> ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

这个命令会创建一个新的表格然后将数据拷贝过去。其实列的默认值保存在表格的.frm文件中，
所以你可以在不改变表的情况下改变默认值：

mysql> ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;

Building MyISAM Indexes Quickly

mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load the data
mysql> ALTER TABLE test.load_data ENABLE KEYS;

只针对nonunique Indexes有效。

In modern versions of InnoDB, you can use an analogous technique that relies onInnoDB’s fast online index creation capabilities. This calls for dropping all of the non-unique indexes, adding the new column, and then adding back the indexes you drop-ped.