Mysql数据库优化 列的选择

Mysql  /  houtizong 发布于 2年前   125

对一个设计拙劣或者没有做index的schma进行优化，可以很大程度的改善性能。
如果想获得很好的性能，就需要对你运行的特定的queries来设计schema和索引，
你需要估计不同类query的性能需求，和使用的频度，以及需要检索的字段和检索
的条件。优化是一个权衡，索引可以提高检索速度，但是会降低更新速度，一个反
范式的schema可能会在某些查询下加快速度，但在其他情况下可能降低速度。添加
冗余字段、计数器、summary表可以优化查询，但是维护起来很困难。

选择最优的数据类型：
Mysql支持大量的各种个样的数据类型，如何正确的选择类型来存储数据是产生高性能
的关键。
1.足够大的越小的类型越好
小的类型通常更高效，因为他们使用了更少的磁盘空间、内存和cpu高速缓存，需要更少
的CPU时钟周期。但是也不要低估的存储值的范围，增大schema多个地方的数据类型也是
一件痛苦和耗时的操作。

2.简单就好：
简单的类型需要更少的时钟周期，整数的比较要比字符串的比较要快。，比如你应该存储
mysql内置的类型而不是字符串来存贮日期和时间，使用整数来存储ip。

3.尽可能避免null字段：
尽可能的在定义的字段上添加NOT NULL，MySQL对于可以为null的列很难优化，
因为它让索引、索引统计以及值的比较变得复杂，nullable的列会使用更多的存储空间，并且需要一些特殊处理，对nullable的列建索引，每个entry需要额外的数据，在MyISAM甚至会使定长索引变成变长的。对于没有值的列，你可以使用一些特殊的值来代表，比如0，空字符串。将null变成not null可能对性能的提高不是很大，但是如果你打算对一个列见索引，那么要避免它是nullable的。

选择一个列的数据类型，首先决定大体上是什么类型的：整数、字符串等等。然后在再这些类型中选择合适的MySQL提供的类型。比如DATETIME和TIMESTAMPE可以存储相同的数据：日期和时间，精度为1秒。但是Timestamp使用了一半的空间，并且是时区敏感和一些特殊的自动更新的功能。但是具有很小的值的范围,到2038年就over了。
MYSQL为了兼容性提供了很多的别名，比如INTEGER，BOOL，NUMERIC,他们只是别名，不影响性能。

数字类型：
数字类型包括整数和浮点数类型，整数类型tinyint,smallint,mediumint,int,
bigint,他们分别占8,16,24,32，64个字节。整数类型可以使用unsigned属性，
tinyint unsigned可以存储0～255。MySQL的整数计算通常使用的是64位的bigint。
MySQL可以指定整数类型的宽度，但是这个宽度并不是限制数据的范围，只是用于指定交互式的工具显示保留的字符个数，在存储上INT（1）和INT（20）是等价的。

浮点类型：
浮点书类型有小数部分，但是他们并不是仅仅针对浮点数的，你可以使用DECIMAL来表示BIGINT存储不下的整数，MySQL支持两类：精确和非精确类型。
FLOAT和DOUBLE支持近似的标准的浮点计算，如果你需要知道计算的精确结果，你需要平台下浮点数的实现。DECIMAL可以存储精确的分数，MySQL5.0以上支持精确的数学计算，但是浮点类型的数学计算效率会更高,存储空间更小。浮点类型和DECIMAL都可以指定精度，比如DECIMAL(18,9).由于空间和计算的效率问题，只有需要精确的结果时，我们才使用DECIMAL。

字符串类型：
MySQL支持很多的字符串类型，从版本4.1开始，支持每个列使用一种字符集，如果这么做，会很大的影响性能。
VARCHAR和CHAR类型：
有两种主要的字符串类型VARCHAR和CHAR:
VARCHAR主要存储变长的字符类型，会比固定长度的类型要节省空间，因为它只需要和实际需要用的空间一样大，VARCHAR指定的长度并不是申请那么大的空间，只是最大可能占用的空间。使用1，2个额外的字节来存储长度，比如VARCHAR（10）最大会占11个字节，VARCHAR（1000）最大会占用1002个字节。
由于VARCHAR能够节省空间，所以有助于提高性能，但是由于行是变长的，如果行大到无法在原来的位置存储，那么在MyISAM会产生行碎片，InnoDB会分页。
MySQL 5.0以上不会自动删除掉结尾的空白。
通常在最长的数据要比平均的长度大很多，不会频繁更新这个字段的情况下使用VARCHAR。

CHAR是固定长度的类型，它会申请指定长度的空间。存储的时候默认会删除结尾的空白，
如果存储短的，并且长度差不多的字符串，使用CHAR是非常好的，比如存储MD5的用户密码。
对于频繁跟新的字段，使用CHAR也是比较好的选择，可以使用CHAR(1)来代表Y和N的值，这比VARCHAR(1)少用一个字节。

BINARY和VARBINARY类型用于存储二进制的字符串，和传统的字符串类似，只是存储的是字节而不是字母。BINARY字符串的比较要比普通字符串要快。

BLOB和TEXT类型：
BLOB和TEXT是用来存储大规模的数据的，分别用于二进制和字符串类型。还有其他同族的类型：TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT,和二进制类型TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB.
BLOB和TEXT的区别是一个用来存储二进制的，一个用来存储文本的他有字符集和编码。

BLOB和TEXT类型和其他类型排序不同，他不会将全部长度的字符串排序，他只会排序前
max_sort_length字节。如果你需要排序跟少的字符你可以减少max_sort_length,或者使用order by substring(column,length)。MySQL不能对这些类型的全部长度建索引，所以不能够使用索引来排序。

内存存储引擎不支持BLOB和TEXT类型，所以使用BLOB和TEXT列的查询，隐含的使用了On-disk临时表。即使你配置MySQL临时表在内存中，仍然需要很昂贵的系统调用。最佳的实践是不要使用BLOB和TEXT类型除非你真的需要，如果不可避免，那么可以使substring(column,length)类转换成字符串类型，这样可以使用in-memory表。需要确认使用足有短的substring，临时表的大小没有超出max_head_table_size或者tmp_table_size，否则会使用on-disk MyISAM表。

EXPLAIN的Extra列包含“Using temporary"表明这个查询使用了隐式的临时表。

使用ENUM来代替字符串类型
有时候我们可以使用ENUM列来代替传统的字符串类型，ENUM列可以存储65535个不同的字符串值。MySQL存储他们非常的紧凑，压缩为1～2个字节。每个值的内部存储都是一个整数，在.frm文件保存了数字到字符串的一个映射。

引用

mysql> CREATE TABLE enum_test(
->
e ENUM('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

mysql> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|
1 |
|
3 |
|
2 |
+-------+


ENUM字段排序是按照内部存储的整数排的，你可以显示的使用FIELD来指定排序的方式。
引用

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e
|
+-------+
| apple |
| dog
|
| fish |
+-------+

ENUM最大的缺点是字符串列表是固定大小的，添加和删除一个字符串需要使用ALTER TABLE，所以如果你表示的字符串有可能在将来改变，那么使用ENUM并不是一个好的主意。
如果ENUM需要和char,varchar进行join操作，那么会慢一些，这时候最好同一类型，
但是使用ENUM能够比使用char,varchar节省不少空间的。

日期和时间类型：
MySQL有各种日期和时间的类型，比如YEAR,DATE，MySQL能存储的最小时间粒度是秒，但是可以在微妙级别做临时的运算。

DATETIME：
这个类型能够保存更大范围的值，从1001到9999，精度为1秒，他将日期和时间存储为整数格式为YYYYMMDDHHMMSS，和时区无关，使用了8个字节。

TIMESTAMP：
TIMESTAMP按照字面意思，存储了从1970.1.1流失了多少秒，和Unix的timestamp一样。
TIMESTAMP仅使用了4个自己，所以要比DATETIME小很多：从1970到2038. MySQL提供了
FROM_UNIXTIME()和UNIX_TIMESTAMP()函数来将Unix时间戳和日期转换。
TIMESTAMP是和时区相关的。
时间戳有一些DATETIME没有的功能，他没在插入数据的时候，如果该列为空，则自动将当前时间插入。当更新一行数据时，这行数据的TIMESTAMP列默认会跟新为当前时间，除非显式的指定值，TIMESTAMP列默认是NOT NULL的。

一般来说你应该使用TIMESTAMP，因为他比DATETIME占用更少的空间。有一些人喜欢存储
Unix时间戳为一个整数，但常常没有给你带来任何好处，因为这种格式不方便处理，我们并不推荐这么用。

Bit压缩类型（Bit-packed Data Types)
MySQL提供了一些存储类型，他每一个bit存储一个值来的更紧凑的存储数据方式。所有的
这些类型都是字符串类型，不管底层的存储格式和操作：
BIT 在MySQL5.0之前，BIT是TINYINT的同义词。但是在5.0之后，它是一个完全不同的类型，具有特殊的特性：你可以使用BIT列来存储true/false值。BIT（1）定义了包含了单个比特，BIT（2)存储了两个bit，BIT字段的最大值是64bit。
BIT类型的行为在不同的存储引擎中不同，MyISAM将这些列一起压缩存储，所以17个BIT列只需要17bit的存储空间（没有列是NULL的）。其他的存储引擎比如Memory和InnoDB，使用足够大的integer类型来存储，所以使用BIT并没有节省多少空间。
MySQL将BIT类型视为字符串类型而不是数字类型。如果使用BIT（1），查询出来的是一个字符串，内容是二进制的0或者1，但不是ASCCII码的‘0’，’1‘的值。
但是在数字的场景下使用，其值是字符串二进制表示的值，比如b'00111001'为57.
这可能会比较让人费解，所以我们建议谨慎的使用BIT，在大多数的应用中，避免使用。
如果想存储true/false中使用单个bit的存储空间，那么另外的选择是使用varchar(0),
这个列可以存储NULL或者空字符串。

SET
如果你想存储很多的true/false值，那么考虑将很多列组合成1列，使用SET数据类型，内部表示为压缩的bit集合。MySQL有函数FIND_IN_SET（）和FIELD（），让在查询中使用
更容易。主要的缺点是，修改需要ALTER TABLE.

在整数列使用bit操作
另一种对于SET的选择是使用整数作为压缩的比特集合，比如你可以在TINYINT类型使用
8个bit，然后通过bit操作。这种方式相对于SET的最大有点是修改这个字段不需要ALTER TABLE。缺点是比较拿写和理解，位操作，有人喜欢有人不喜欢，所以完全看你的口味了。

选择主键：
选择一个主键的正确类型是非常重要的。你经常使用这些列来和其他的值比较，或者使用它来查找，在其他的表中作为外键。选择主键不仅仅药考虑存储的类型，还要考虑比较和计算的性能。比如MySQL存储ENUM和SET类型的内部形式是整数，但是在字符串的环境下会转化为字符串。
一旦选择好一个类型，要确保所有关联的表对应的外键具有相同的类型。类型要精确的匹配比较好，除了UNSIGNED。混用不同的类型可能导致性能问题，并且隐式的类型转换可能会带来错误。

选择能够包含数据区间的最小的类型，比如你想用state_id字段来存储美国的州，那么TINYINT已经足够。
整数类型：
通常是最佳的主键类型的选择，因为它速度快并且可以自增。
ENUM和SET：
通常是一个比较糟糕的选择，他们比较适合作为状态，类型的值。
String类型：
尽可能避免使用字符串类型的主键，因为占用较多的空间并且比整数慢。使用MyISAM的表更应该特别谨慎使用字符串类型的主键，因为他会默认压缩字符串索引，这会导致查询非常慢。
使用packed索引大约要慢六倍。
使用完全随机的字符串，比如MD5(),SHA1(),UUID()，要特别小心，每一个新生成的值都会
在一个很大空间的一个随机的值，这将会降低插入和部分查询语句。
INSERT慢的原因是插入的值在索引的随机位置，这会导致页的分裂或者磁盘的随机访问，对于聚集存储引擎会引起聚集索引碎片。
SELECT慢的原因逻辑相邻的行将会在磁盘和内存分散。
随机的值也会导致所有类型查询的缓存表现很差。

特殊类型数据：
比如很多人使用VARCHAR（15）来表示IP，其实IP是一个无符号的32为整数，所以存储IP地址为整数是一个比较好的方法。MySQL提供了INET_ATON()和INET_NTOA函数来转换这两种表示。

参考《高性能MySQL》