MySQL优化表

发表于 | 更新于 | 分类于
本文字数： 18k | 阅读时长 ≈ 17 分钟

思考并回答以下问题：

  • MySQL主要支持4种模式的分区，分别是range（范围）分区、list（预定义列表）分区、hash（哈希）分区以及key（键值）分区。有什么区别？

优化表同样可以提升数据库性能，其中包括优化表结构、表的拆分、分区、表的分析与检查等内容。

优化表结构

1）尽量将字段定义为NOT NULL约束。这是由于：在MySQL中，含有空值的列很难进行查询优化，NULL值会使索引、索引的统计信息以及比较运算变得更加复杂。推荐使用0、一个特殊的值或者一个空字符串代替NULL值。

2）考虑使用enum、set等复合数据类型。对于一个只包含有限数目的特定值的字段，可以考虑将其设计为enum、set等复合数据类型。enum、set等复合数据类型的值可以更快地处理，因为它们在内部是以数值表示的。

3）虽然MySQL支持blob类型，可以存储二进制数据，但是设计数据库表时，图片、音频、视频等二进制数据不要使用数据库存储，毕竟处理这些二进制数据并不是数据库的强项。

4）数值型（例如整型）字段的比较比字符串的比较效率高很多，这也符合一项优化原则：字段类型尽量使用最小、最简单的数据类型，典型的例子是IP地址的存储。当前还有很多应用使用char(15)存储IP地址，然而使用无符号整数存储IP地址效率会更高，MySQL提供了inet_aton()和inet_ntoa()函数来解决IP地址与整数之间的转换。

表的拆分

表的拆分是指将某个表按照某个（或一些）条件拆分成两个或多个独立表。表的拆分分为垂直拆分与水平拆分。

1．垂直拆分

垂直拆分按照字段（或者列）进行拆分，其实就是把组成一行的多个列分开，放到不同的表中。这些表具有不同的结构，拆分后的表含有更少的列，例如可以将频繁访问的列置于一张表中，不经常访问的列（例如长文本信息）置于另一张表中，然后在这两张表的记录与记录之间创建1∶1关系。举例来说，对于一张用户表而言，不仅包含账号、账户名、密码、性别等基本信息，还包括兴趣爱好（不经常使用的信息）、自我介绍（长文本信息）等扩展信息。设计用户表时，可以将用户表拆分成两个表user1表与user2表，其中user1表中包含账号、账户名、密码、性别等基本信息，user2表中包含兴趣爱好、自我介绍等扩展信息。由于user1表中的记录与user2表中的记录存在1∶1关系，此时还需要在user2表中增加user1表的主键字段user1_no作为外键，并将外键设置为唯一性约束。user1表为父表，user2表为子表。

1
2
user1（user1_no，account_name，password，sex）
user2（user2_no，interest，introduction，user1_no）

使用垂直拆分的优点在于：垂直拆分可以使得列数据变小，一个数据块（Block）就可以存放更多行的记录，对频繁访问的字段执行select语句，硬盘I/O次数也会相应减少。另外，垂直拆分表可以达到最大化利用Cache的目的。

使用垂直拆分的缺点在于：由于拆分出来的两张表的记录与记录之间存在1∶1关系，这种1∶1关系需要使用冗余字段进行维护。另外，一旦访问不经常使用的字段，会引起表之间的join连接操作，额外增加了CPU的负担。

2．水平拆分

水平拆分按照记录（或者行）进行拆分，其实就是把一个表分成几个表，这些表具有相同的列，但是存放更少的数据。拆分的原则通常是按照日期时间维度、地区维度或者特殊的业务维度进行表的拆分。

案例1：某个公司销售记录数据量非常大，可以对销售记录按月进行水平拆分，每个月的销售记录拆分成一张独立表，共拆分成12张独立表。

案例2：某个集团在各个地区都有分公司，该集团的订单数据量非常大，可以按分公司所在的地区进行水平拆分。

案例3：某电信公司的话单按月份、地市水平拆分后，发现数据量依然很大，可以按号码段进行3次水平拆分。

使用水平拆分的优点在于：将维度作为查询条件执行select语句时，如果维度范围很小（例如查询12月份的销售记录），可以有效降低需要扫描的数据和索引的数据块数，加快查询速度。

使用水平拆分的缺点在于：水平拆分会给应用增加复杂度，它通常在查询时需要多个表名。查询所有数据需要union操作，有时，这种复杂度会超过它带来的优点。例如，将维度作为查询条件执行select语句时，如果维度范围很大（例如查询1月份到12月份的销售记录），此时需要进行11个union操作，硬盘I/O次数也会增加。

分区

分区是按照指定的规则，跨文件系统分配单个表的多个部分。对于海量数据库或者OLAP的应用而言，对表进行适当的分区可以提升数据库性能。MySQL主要支持4种模式的分区，分别是range（范围）分区、list（预定义列表）分区、hash（哈希）分区以及key（键值）分区。无论使用哪一种分区模式，它们都是对表进行“水平分区”。并且，如果表中存在主键或者唯一性约束字段，要求分区字段必须是主键字段或唯一性约束字段的一部分。分区的原则是按照日期时间维度、地区维度或者特殊的业务维度进行分区，这与水平拆分的原则相同，分区与水平拆分的不同之处如表1所示。

表1 水平拆分与分区的比较

  • range（范围）分区：这种分区模式允许数据库管理员将数据划分不同范围。例如，数据库管理员可以将销售记录表按照销售年度对该表划分分区。
  • list（预定义列表）分区：这种分区模式允许系统通过数据库管理员定义的离散值列表进行分区。例如：数据库管理员建立了一个横跨3个分区的表，分别根据2011年、2012年和2013年所对应的数据进行分区。
  • hash（哈希）分区：这种分区模式允许数据库管理员通过对表的一个或多个列的hash值进行计算，最后通过这个hash码不同数值对应的数据区域进行分区。例如数据库管理员可以建立一个对表主键hash值进行分区的表。
  • key（键值）分区：hash分区的一种延伸，这里的key由MySQL自动生成。

上述4种模式的分区中，range（范围）分区最为常用。下面以range分区为例讲解MySQL表分区的方法。下面的SQL语句首先创建一个sale销售记录表，然后再向该表添加销售年度分区（注意sale表的存储引擎为MyISAM）。

1
2
3
4
5
6
7
8
9
10
create table sale(
    sale_id int not null,
    sale_date datetime,
    money int
)engine=myisam;

alter table sale partition by range columns (sale_date) (
    partition p01 values less than ('2011-1-1'),
    partition p02 values less than ('2012-1-1')
);

MyISAM的sale表经过分区后，产生分区定义文件sale.par、分区索引文件（sale#P#p01.MYI与sale#P#p02.MYI）以及分区数据文件（sale#P#p01.MYD与sale#P#p02.MYD），如图1所示。

图1 MyISAM表分区的物理文件

说明
如果sale表的存储引擎为InnoDB，则sale表经过分区后，仅仅产生分区定义文件sale.par。如果sale表的存储引擎为InnoDB，并且使用的是独享表空间（ibd）文件，则sale表经过分区后，除了产生分区定义文件sale.par外，还会产生分区独享表空间文件sale#P#p01.ibd与sale#P#p02.ibd，如图2所示。

图2 InnoDB独享表空间分区的物理文件

接着使用下面的SQL语句向sale表插入记录信息，运行结果如图3所示。

1
2
insert into sale values(1, '2011-1-2',100);
insert into sale values(1, '2012-1-2',100);

图3 向sale表插入记录信息

注意：第二条记录插入失败，原因在于插入的值‘2012-1-2’不在sale表的两个分区范围之内。对于此类问题，可以向sale表添加一个maxvalue（无穷大值）分区。这样，第二条记录就可以成功插入p03分区中，执行结果如图4所示。

1
alter table sale add partition(partition p03 values less than maxvalue);

图4 maxvalue分区

查询2011年的销售记录可以使用下面的SQL语句，如图5所示。

1
explain partitions select * from sale where sale_date<='2011-12-31' and sale_date>='2011-1-1'\G

图5 分区select语句的分析

通过explain partitions命令可以发现，查询优化器只需要搜索p02 分区，查询范围的缩小可以有效提升检索性能。

如果要删除2011年度的数据，只需要删除p02分区即可，可以使用下面的SQL语句。此时对应分区的物理文件也随之删除。

1
alter table sale drop partition p02;

表分析与表检查

表结构一旦确定，随着向表插入记录、更新记录以及删除记录，索引文件和数据文件之间的链接可能会发生错误，或者表空间中数据文件可能存在碎片，这时需要使用一些MySQL命令对表进行分析或者检查，从而提升数据库性能。

1．analyze table

在执行select语句时，MySQL查询优化器需要收集一些相关信息，进而优化select语句，其中就包括索引字段的cardinality（离散度），它表示某个索引字段中包含多少个不同的值。如果索引字段cardinality的值远远小于索引字段实际的离散度，那么索引就基本失效了。通过使用show index 命令可以查看某个表所有索引字段的离散度，索引字段实际的离散度可以通过“select count(distinct(字段名))from表名;”获取。

MySQL查询优化器是基于成本的，并且最主要的成本因素就是这个查询要访问多少数据。如果这个统计信息没有生成，或者如果统计信息已经过期，优化器可能就会有个比较差的决定。analyze table命令负责对表进行分析，获得索引字段的分布情况，分析的结果可以使MySQL查询优化器得到准确的统计信息，使得SQL能够生成正确的执行计划。

举例来说：当索引字段的离散度远远小于字段实际的离散度时，可以使用MySQL命令“analyze table表名”修复某个表的索引，提高索引字段的离散度，进而为MySQL查询优化器优化select语句提供依据。例如，优化student表的索引离散度可以使用MySQL命令“analyze table student;”，执行结果如图6所示。

图6 对表进行分析说明

如果开启了二进制文件，那么analyze table的结果会写入二进制文件，可以在analyze和table之间添加关键字local以取消写入。

analyze table命令是通过执行全索引扫描来计算统计信息的，这个过程对表是锁定的。因此，对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，且实际的执行计划和预期的执行计划不同的时候，执行一次表分析可能有助于产生预期的执行计划。

2．optimize table

如果某个数据库表不断地执行delete、insert或者update等更新语句，那么表的内部结构就会出现很多碎片以及未利用的空间，使用MySQL命令“optimize table表名”可以整理MyISAM以及InnoDB表的碎片，从而提升系统性能。例如，整理student表的碎片可以使用MySQL命令“optimize table student\G”，执行结果如图7所示。需要注意的是，该MySQL命令整理student表碎片失败。

图7 对表进行优化1

在启动MySQL服务前，在my.ini配置文件[mysqld]选项组中加入skip-new或者safe-mode选项，然后重启MySQL服务，此时MySQL才支持optimize table功能。重新运行MySQL命令“optimize table student\G”，执行结果如图8所示。

图8 对表进行优化2说明

如果开启了二进制日志，那么optimize table的结果会写入二进制日志，可以在optimize和table之间添加关键字local，取消写入。

3．check table

所有存储引擎的表都会因为硬件问题、MySQL内部BUG或者操作系统的原因导致索引的损坏，索引没有同步更新。损坏的索引会导致诸多问题，例如，查询返回不正确的结果；当没有重复值出现时却抛出重复键值的错误；查询死锁以及宕机。

可以使用check table命令检查表、索引是否损坏，检测MyISAM和InnoDB表的健康程度。check table还可以指定下面一些选项。

  • quick：速度最快的选项，在检查各列的数据时，不会检查索引文件和数据文件之间链接的正确与否。如果没有遇到什么问题，可以使用这个选项。
  • fast：只检查没有正常关闭的表，如果在系统掉电之后没有遇到严重问题，可以使用这个选项。
  • changed：只检查上次检查时间之后更改的表以及没有正常关闭的表。
  • medium：默认的选项，会删除索引文件和数据文件之间错误的链接。
  • extended：最慢的选项，对索引所有关键字进行一个全面的检查。

check table也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。例如，下面的SQL语句首先创建了名字为“view1”的视图，该视图从student表中获取信息。

1
2
3
4
5
create view view1 as select student_no,student_name,student_contact from student;
```    
然后使用check table命令检查该视图。
```sql
check table view1;

接着使用下面的SQL语句删除student表中的student_contact字段。
1
alter table student drop student_contact;

最后再次使用check table命令检查该视图，将出现如图9所示的错误信息。
1
check table view1;

图9 对表进行检查

4．repair table

如果一个表或索引已经损坏，遇到如下错误时：

1
Incorrect key file for table：' '．Try to repair it.

可以使用MySQL命令repair table尝试修复它，该命令只对MyISAM和ARCHIVE表有效。repair table命令同样可以指定下面一些选项。

  • quick：最快的选项，只修复索引树。
  • extended：最慢的选项，需要逐行重建索引。
  • use_frm：只有当MYI索引文件丢失时才使用这个选项，全面重建整个索引。

与analyze table一样，repair table也可以使用local来取消写入二进制日志。

说明
修复MyISAM和ARCHIVE存储引擎的表前，建议首先备份数据。

5．checksum table

MySQL主从复制环境中，从表出现写入操作或者主表执行了具备不确定性的查询指令，或者主从数据在网络传输过程中发生了数据变化，这些情况都会导致从属数据未能与主体数据正确同步，继而出现主从数据不一致问题。

对于复制环境中的每一对主从表，可以使用checksum table语句计算主从表的checksum（校验值），从而确定主从数据是否一致。通常情况下，对于每一对主从表而言，如果两个表存储的数据相同、表结构相同（字段数据类型相同以及字段的顺序相同），那么主从表的checksum校验值是相同的。

在执行checksumtable时，可以在命令的最后指定选项quick或是extended。quick 表示返回存储的checksum（校验值），而extended会重新计算checksum（校验值），如果没有指定选项，则默认使用extended。

说明
创建MyISAM存储引擎的表时，如果在create table语句后加上“CHECKSUM=1”选项，那么MyISAM存储引擎的表会把checksum存储起来，称为live checksum，当数据发生变化时，checksum会相应变化。

NULL值对统计信息的影响

使用analyze table命令统计索引字段的分布情况时，如果索引字段包括NULL值，MySQL如何统计该字段的cardinality（离散度）？对于MyISAM而言，myisam_stats_method参数用于控制MySQL如何处理NULL值；对于InnoDB而言，innodb_stats_method参数用于控制MySQL如何处理NULL值。这两个参数用于控制MySQL如何统计、处理NULL值，共有如下3种取值。

  • nulls_equal：所有的NULL值被看成相等的一个值。
  • nulls_unequal：每个NULL值被看成单独的一个值。
  • nulls_igored：NULL值被忽略。

使用MySQL命令“show variables like ‘％stats_method％’;”可以查看这两个参数的相关信息，如图10所示。innodb_stats_method的默认值为“nulls_equal”，如果此时InnoDB表的某个索引字段的NULL值较多，那么该索引字段的离散度较小，这就意味着该索引基本失效。myisam_stats_method的默认值为“nulls_unequal”，如果此时MyISAM表的某个索引字段的NULL值较多，那么该索引字段的离散度依然较大，这样就会让查询优化器处理查询语句的时候使用该索引的倾向性更高。如果索引字段的NULL值本身就很少，此时不管使用nulls_unequal还是nulls_equal，对查询优化器选择执行计划的影响很小。

图10 NULL值对统计信息的影响

记录的格式

使用MySQL命令“show table status;”可以查看某个数据库的所有表或者视图（不包括临时表）的状态信息。如果需要查看某一个表的状态信息，可以使用“show table status like ‘表名’;”。例如，下面的命令负责查看account表的状态信息（注意，该表的存储引擎目前为MyISAM），如图11所示。

1
show table status like 'account'\G

图11 查看MyISAM表的状态信息

使用下面的命令将account表的存储引擎修改为InnoDB，然后查看account表的状态信息，如图12所示。

1
2
alter table account engine=innodb;
show table status like 'account'\G

图12 查看InnoDB表的状态信息

show table status显示了表的大量信息，如表2所示。

表2 表的状态信息

说明
对于MyISAM表，记录的格式有3种：Dynamic，Fixed或Compressed。Dynamic记录格式是指表的每一行记录长度是可变的。Fixed记录格式是指表的每一行记录使用固定大小的空间。Compressed记录格式仅仅用在compressed tables中。使用create table或alter table创建或者修改表结构时，可以使用row_format选项强制表的记录格式为fixed固定格式或dynamic动态格式，这会导致char和varchar列因fixed格式变成char，或因dynamic格式变成varchar。如果表的row_format是fixed，该表是静态表，静态表每条记录所占用的字节空间系统，静态表的优点是读取速度快，缺点是浪费存储空间。如果表的row_format是dynamic，该表是动态表，动态表的每条记录所占用的字节空间是动态的，动态表的优点是节省储存空间，缺点是读取速度较慢。

说明
对于InnoDB表而言，记录的格式有两种：Compact以及Redundant。MySQL5.0.3之前的版本仅支持Redundant，MySQL5.0.3以及MySQL5.0.3 之后的版本，默认的row_format是Compact。

与Redundant相比，Compact以牺牲CPU使用为代价，节省大约20％的行储存空间。如果当前应用系统的负荷受限于缓存命中率以及硬盘的速度，推荐使用 Compact。如果当前应用系统的负荷受限于CPU的速度，推荐使用Redundant。

0%