MySQL 性能优化--优化数据库结构之优化数据大小

尽量减少表占用的磁盘空间。通常,执行查询期间处理表数据时,小表占用更少的内存。

表列

l 尽可能使用最效率(最小)的数据类型。比如,使用更小的整型以便于获取更小的表。相比INT,MEDIUMINT 通常是个更好的选择,因为MEDIUMINT列少使用25%的空间。

l 尽可能的定义列为NOT NULL,这有利于更好的使用索引,可以让sql操作更快。

行格式

l MySQL 5.7.8及以前版本,默认的,以COMPACT行格式创建InnoDB表。从5.7.9开始,默认行格式为DYNAMIC。可通过配置innodb_default_row_format来修改默认行格式。

同时,也可以通过执行CREATE TABLE、ALTER TABLE命令时指定ROW_FORMAT选项显示指定行格式化。

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html

COMPACT行格式可减少大约20%的行存储空间, 代价是,针对某些操作,会增加CPU使用。如果工作任务由缓存命中率和磁盘速度限制,使用COMPACT可能会更快,,极少情况下,由CUP限制,可能会更慢。

同时,COMPACT行格式也会影响utf8或utf8mb4数据在CHAR类型列中的存储。针对ROW_FORMAT=REDUNDANT,一个utf8、utf8mb4 CHAR(N) 列,占用“最大字符的字节长度” X N 字节。 而许多语言主要使用单字节的utf8、utf8mb4字符,所以固定长度的存储通常会浪费空间。针对ROW_FORMAT=COMPACT, InnoDB为这些列分配可变存储量,必要的话,过去掉尾部空格。最小的存储长度保存为N个字节。更多资料,查阅

http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

l 在创建表时指定ROW_FORMAT=COMPRESSED,或者对已存在MyISAM表执行myisampack命令,以压缩形式存储表数据,可更进一步的最小化空间(被压缩的InnoDB表可读可写,但是被压缩的MyISAM表只可读)。

l 针对MyISAM表,如果没有可变长度列(VARCHAR,TEXT、BLOB列),将使用fixed-size列格式。这个速度比较快,但是会浪费一些空间。查看Section 16.2.3, “MyISAM Table Storage Formats”。即使有VARCHAR列,也可以在执行CREATE TABLE命令时使用ROW_FORMAT=FIXED显示指定使用固定长度列。

索引

l 表的主索引(primary index)(所占的空间)要尽可能短。这使得行记录的识别容易而且有效率。对于InnoDB表,主索引列也存在于每个二级索引(second index)条目中,所以如果有很多二级索引的话,短的主索引可以节省大量的空间。

注:

主索引:指在指定的索引字段或表达式中不允许出现重复值的索引

参考连接:

http://baike.baidu.com/link?url=R3TFNWDpGZU9kAioDFcC5LyPLJLy-RzjY1ZMuVyJHN1j3zmKnxOBE3U2dtaT9cG53dkIM2o76li0mLXNzVpDZa

更多说明,参考文章:“MySQL InnoDB表和索引之聚簇索引与第二索引

l 仅在需要提高查询性能时创建索引。索引有利于检索,但是会减慢插入和更新操作的速度。如果大部分情况下都是通过在组合列(combinnation of columns)上搜索进行表访问,那么应该在该组合列上建立索引,而不是为组合列中的每个建立单独的索引。索引的第一部分即第一列,应该是用得最多的列。

l 很有可能,一个很长的字符串列,拥有一个唯一的前缀,最好仅索引该前缀(语法支持,具体查看 Section 14.1.14, “CREATE INDEX Syntax”)。索引越短,检索越快,不仅仅是因为其需要更少的磁盘空间,还因为在索引缓存中提供了更多的命中,进而减少磁盘搜索(disk seeks)。

eg:仅用name列的前10个字符创建索引

CREATE INDEX part_of_name ON customer (name(10));

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

Join

l 某些情况,把一张经常被扫描的表拆分成两张表是很有好处的,特别是动态格式化表,并且在扫描时,可能用一个更小的统计格式表来查询相关行

注:

动态格式化表:包含长度可变的列,或者使用ROW_FORMAT=DYNAMIC选项创建的表

参考连接:

http://dev.mysql.com/doc/refman/5.5/en/dynamic-format.html

l 不同表中用相同的数据类型声明携带相同信息的数据列,加快基于对应列的join速度。

l 尽量保持列名的简单,这样,可以跨越不同的表使用相同的名字,并简化join查询。比如,某个名为customer表中,使用列名 name ,而不是customer_name。为了使列名兼容它sql服务器,考虑保持列名少于18个字符。

标准化

l 正常,尽量保持数据不重复。赋予列名唯一ID,有必要的话,在其它更小表中使用这些id,而不是重复冗长的值,比如名称和地址,join子句中通过引用这些id来join表。

l 如果速度比磁盘空间,保存多份数据副本的维护成本更重要,例如,在一个商业智能场景中,分析来自大表的所有数据,可以适当放宽标准化规则,冗余数据信息或创建汇总表以获取更快的速度。

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/data-size.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券