mysql优化-数据库结构

mysql5.6参考书册读书笔记

优化表结构

优化表的大小

小表可以只占用较少的磁盘空间、内存空间（表的索引也如此），读写时可以减少磁盘的IO操作，从而提高性能。

mysql支持多种存储引擎和行格式，选择合适的存储引擎、行格式及索引类型能极大的提高数据库的性能。

可以从以下几个方面着眼，提高mysql的性能

选择合适的表列

1、在满足需求的情况下尽可能的使用小的数据类型。

2、尽可能不要允许列有null值。如果确实需要允许有null值，也没有必要刻意去避免。

3、固定大小的列可以提高性能，但会浪费磁盘空间。

选择合适的行存储格式

mysql支持多种行存储格式，REDUNDANT,COMPACT，还有COMPRESSED

InnoDB存储引擎默认使用COMPACT的行存储格式（ROW_FORMAT=COMPACT），相比REDUNDANT存储格式，COMPACT能减少20%的磁盘空间，代价是需要更多的CPU来处理行。

COMPRESSED是比COMPACT更节省空间的存储格式。对于INNODB表，COMPRESSED行存储格式是可以进行读写操作的。对于MYISAM表，COMPRESSED行存储格式是只读的。

索引

1、主键应该尽可能的小。这可以减少根据主键查找做比较所需要的时间。对于INNODB表，因为主键会包含在每个二级索引中，短小的主键能减少二级索引的大小，从而提高查询效率。

2、创建正确的必需的索引。多余的索引会影响插入和更新的性能。不恰当的索引会帮倒忙。

3、索引应该尽量短小。如果某列的值是一些非常长的字符串，但仅需要前面N个字符便可以有效区分每个值，则应该在该列上创建基于前缀的索引。短小的索引不仅能节省磁盘空间，更重要的是他还能提高缓存命中率。

大胆使用关联查询

1、如果表中包含一些经常使用的列和一些不经常使用的列，那最好把这张表一分为二。然后在需要的时候用join来查找相关信息。

2、用于关联两张表的列的数据类型和长度最好一致。否则有可能导致索引不能使用。

3、表的列名不要太长了，最好不要超过18个字符。

优化表列的数据类型

数字类型

对于既能用数字表示又能用字符串表示的列，使用数字。数字类型占用的空间少，并且传递和比较操作都要快。

字符和字符串类型

使用字符和字符串时遵循以下指导原则：

1、尽可能使用二进制的排序（比较）字符集来做比较和排序操作。

2、在不同列之间做比较，参与比较的列应该定义为相同字符集和相同的排序字符集；

3、对于列值大小小于8kb的列，使用varchar类型而不是BLOB类型。分组和排序操作会生成临时表，如果表中没有任何列定义为BLOB，这些临时表将基于MEMORY引擎。

4、如果表中包含一些不经常被查询到的列，考虑将这些列保存在单独的表中，用外键关联。mysql查找一行中的某些值是是将整行都读取。保持小的行将会减少IO操作，使更多的行能缓存在缓存中，提高查询效率。

5、在innodb表中，如果必须使用随机的值作为主键，那么可以在该值前面加上一些递增的值作为前缀，比如时间。INNODB表行的存储顺序和主键的顺序一致。相邻的主键对应的行很可能存储在同一个内存块上。

BLOB数据类型

1、存储文本数据的BLOB列，存储前应先进行压缩。行存储模式为COMPRESSED时除外。

2、BLOB列应该存储在额外的表中，用外键关联。甚至可以存储在不同的存储设备上或者不同的数据库实例中。

3、避免比较两个BLOB类型的数据。可以用二外的列来存储他们的hash值（MD5()或者CRC32()），然后比较这两个hash值。注意不同的字符串可以计算得出相同的hash值，索引还需要进一步的判断。

使用PROCEDURE ANALYSE()

ANALYSE([max_elements[,max_memory]])

用来分析已存在表中的数据，返回所选各列的建议使用的数据类型。

用法：SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

max_elements：默认256。推荐使用ENUM类型的阈值。如果被分析的查询中某列的distinct结果集小于max_elements,则推荐使用ENUM。显然一般情况下该值应至为1.

max_memory：默认8192。计算某一列的distinct值的多少时最多允许分配的内存大小。

多表优化

文件描述符

mysql是多线程的，在同一时刻，可以有很多客户端同时连接mysql，如果每个客户端（一个session）都请求同一张表，则该表在每个session中都会被打开，表的打开在session之间是相互独立的。线程在打开表之前必须先持有一个文件描述符（file descriptor）。对于MYISAM表，打开一张表需要持有两个文件描述符。（索引的文件描述符是session共享的）。

影响mysql服务器同时打开的文件描述符数量的系统变量有table_open_cache和max_connections，当然还要受到操作系统的限制。

table_open_cache指定了mysql服务器某一时刻打开的文件描述符的最大值（有时候会临时超出这个值）。这个值必需小于等于操作系统允许打开的最大值。

mysql使用临时表

mysql在处理一些语句时会使用到临时表，这对用户来说是透明的。在下述情况下，mysql会创建临时表：

1、处理包涵UNION的查询

2、处理定义中包涵UNION,聚集或临时表算法的视图（复杂的视图）

3、FROM子句中包涵子查询

4、包涵ORDER BY和GROUP BY子句的查询，且这两个子句包含的列或列的排列顺序不相同；ORDER BY或GROUP BY子句包含的列并非全部来自查询处理的第一张表。

5、包含DISTINCT和ORDER BY子句的查询可能会用到临时表

6、使用SQL_SMALL_RESULT选项，mysql在需要创建临时表时会创建基于内存的临时表（MEMORY 存储引擎）；但如果临时表的大小超过了tmp_table_size或者max_heap_table_size指定的大小，则会创建使用disk的临时表（MYISAM存储引擎）。

7、更新多张表的update语句。

8、处理GROUP_CONCAT() orCOUNT(DISTINCT)语句

查看一条语句是否使用到临时表，使用EXPLAIN查看SQL语句的执行计划，并检查extra列的值是否是using temporary。

可以创建基于MEMORY存储引擎或者MYISAM存储引擎的临时表。当MEMORY临时表的大小超过tmp_table_size或者max_heap_table_size时，mysql自动将其转换为使用磁盘的临时表。从性能考虑应该避免这种自动转换，使用big_tables系统变量强制mysql每次都创建基于DISK的临时表。

以下场景mysql不会创建MEMORY临时表

1、表中包含BLOB或者text列

2、GROUP BY子句或者DISTINCT子句中包含超过512字节（binary）或者512字符（非binary）的列。

3、当使用UNION，UNION ALL时，如果SELECT子句中某一列存储的实际值超过512字节或者512字符。这里是实际存储的长度而不是定义的长度。

 

每创建一张临时表Created_tmp_tables加1；每创建一张存储于DISK表的临时表Created_tmp_disk_tables加1.