MySQL优化-2-数据库对象

2019独角兽企业重金招聘Python工程师标准>>>

优化表的数据类型 - PROCEDURE ANALYSE()

表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。

语法：

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16 个或者256 字节的ENUM 类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM 定义通常很难阅读。

在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

通过拆分提高表的访问效率：

这里我们所说的拆分，主要是针对Myisam 类型的表，拆分的方法可以分成两种情况：

1. 纵向拆分：

纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。

2. 横向拆分：

横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam 表的读取和更新导致的锁问题。

逆规范化：

数据库德规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。

但是对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余纪录在相同表中，更新的代价增加不多，但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。

使用冗余统计表：

使用create temporary table 语法，它是基于session 的表，表的数据保存在内存里面，当session 断掉后，表自然消除。对于大表的统计分析，如果统计的数据量不大，利用insert。。。select 将数据移到临时表中比直接在大表上做统计要效率更高。

选择更合适的表类型：

1、如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。

2、如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。更多存储引擎选择的原则，请参考开发篇的相关章节。

选择mysql 存储引擎

mysql的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

 

最常使用的2 种存储引擎:

1. Myisam存储引擎。每个MyISAM在磁盘上存储成三个文件, 文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

2. InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

注： MySQL之前是MyISAM为默认存储引擎，5.5后是InnoDB为默认存储引擎。

如何选择合适的存储引擎：

1） MyISAM：MySQL 插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一.
2） InnoDB：用于事务处理应用程序，具有众多特性，包括ACID 事务支持。
3） Memory：将所有数据保存在RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
4） Merge：允许MySQL DBA 或开发人员将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为1 个对象引用它们。对于诸如数据仓储等VLDB 环境十分适合。

选择合适的数据类型

选择原则-根据选定的存储引擎,确定如何选择合适的数据类型.

1） MyISAM 数据存储引擎和数据列: MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列。
2） MEMORY存储引擎和数据列: MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
3） InnoDB 存储引擎和数据列: 建议使用VARCHAR类型. 对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

1. CHAR 和VARCHAR 

它们类型类似，但它们保存和检索的方式不同。

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4 个字节	''	1 个字节
'ab'	'ab '	4 个字节	'ab '	3 个字节
'abcd'	'abcd'	4 个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4 个字节	'abcd'	5个字节

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL 运行在严格模式，超过列长度的值不但不保存，并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为从CHAR 列检索时会删除尾部的空格，而Varchar类型检索时会保存尾部空格。 

2. TEXT和BLOB

在使用text 和blob 字段类型时要注意以下几点,以便更好的发挥数据库的性能.

1. BLOB和TEXT值会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理.

2. 使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。我们可以使用MD5()函数生成散

列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

3. 在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或TEXT值。

4. 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

3. 浮点数与定点数

在mysql 中float、double（或real）是浮点数，decimal（或numberic）是定点数。

浮点数相对于定点数的优点是在长度一定的情况下， 浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

在今后关于浮点数和定点数的应用中，大家要记住以下几点：

1、浮点数存在误差问题；从上面的例子中我们看到c1 列的值由131072.32 变成了131072.31. 

2、对货币等对精度敏感的数据，应该用定点数表示或存储；

3、编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

4、要注意浮点数中一些特殊值的处理。

选择合适的字符集

字符集是一套符号和编码的规则，而且如果在数据库创建阶段没有正确选择字符集，那么可能在后期需要更换字符集，而字符集的更换是代价比较高的操作，也存在一定的风险，所以，我们推荐在应用开始阶段，就按照需求正确的选择合适的字符集，避免后期不必要的调整。

MySQL 支持的字符集

mysql服务器可以支持多种字符集（可以用show character set命令查看所有mysql支持的字符集），在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，mysql明显存在更大的灵活性。

mysql的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义mysql存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系, MySQL支持30多种字符集的70多种校对规则。每个字符集至少对应一个校对规则。可以用SHOW COLLATION LIKE 'utf8%';命令查看相关字符集的校对规则。

怎样选择合适的字符集

我们建议在能够完全满足应用的前提下，尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接的提高了系统的性能。

有很多字符集可以保存汉字，比如utf8、gb2312、gbk、latin1 等等，但是常用的是gb2312 和gbk。因为gb2312 字库比gbk 字库小，有些偏僻字（例如：洺）不能保存，因此在选择字符集的时候一定要权衡这些偏僻字在应用出现的几率以及造成的影响，不能做出肯定答复的话最好选用gbk。

MySQL 字符集的设置

mysql 的字符集和校对规则有4 个级别的默认设置：服务器级、数据库级、表级和字段级。分别在不同的地方设置，作用也不相同。服务器字符集和校对，在mysql 服务启动的时候确定。

• 可以在my.cnf 中设置：[mysqld] default-character-set=utf8
• 或者在启动选项中指定：mysqld --default-character-set=utf8
• 或者在编译的时候指定：./configure --with-charset=utf8

如果没有特别的指定服务器字符集，默认使用latin1 作为服务器字符集。上面三种设置的方式都只指定了字符集，没有指定校对规则，这样是使用该字符集默认的校对规则，如果要使用该字符集的非默认校对规则，则需要在指定字符集的同时指定校对规则。可以用show variables like 'character_set_server';命令查询当前服务器的字符集和校对规则。

转载于:https://my.oschina.net/jiyayun/blog/97015