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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL性能优化--优化数据库结构之优化数据大小

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

 


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

 

表列

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

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

 

行格式

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

 

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

 

参考连接:

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

 

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

 

同时,COMPACT行格式也会影响utf8utf8mb4数据在CHAR类型列中的存储。针对ROW_FORMAT=REDUNDANT,一个utf8utf8mb4 CHAR(N)列,占用“最大字符的字节长度”X N字节。而许多语言主要使用单字节的utf8utf8mb4字符,所以固定长度的存储通常会浪费空间。针对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,TEXTBLOB),将使用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子句中通过引用这些idjoin表。

 

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

 

 

参考连接:

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

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1天前
|
OLAP 数据处理 Apache
众安保险 CDP 平台:借助阿里云数据库 SelectDB 版内核 Apache Doris 打破数据孤岛,人群圈选提速4倍
众安保险在CDP(Customer Data Platform,客户数据平台)建设中,通过引入阿里云数据库SelectDB版内核Apache Doris,成功打破了数据孤岛,并显著提升了人群圈选的速度
11 1
|
2天前
|
消息中间件 存储 关系型数据库
【微服务】mysql + elasticsearch数据双写设计与实现
【微服务】mysql + elasticsearch数据双写设计与实现
|
2天前
|
关系型数据库 数据库 数据库管理
关系型数据库数据唯一性保证
【5月更文挑战第17天】
9 1
|
3天前
|
缓存 NoSQL 数据库
记录线上数据库飙升到60%的性能优化
有一天,dba在数据库告警群找到我,说我们数据库CPU有规律性的尖刺,qps每次突然增加500+,尖刺时cpu飙升到60%,没尖刺时只有5%左右
记录线上数据库飙升到60%的性能优化
|
3天前
|
SQL 分布式计算 关系型数据库
使用 Spark 抽取 MySQL 数据到 Hive 时某列字段值出现异常(字段错位)
在 MySQL 的 `order_info` 表中,包含 `order_id` 等5个字段,主要存储订单信息。执行按 `create_time` 降序的查询,显示了部分结果。在 Hive 中复制此表结构时,所有字段除 `order_id` 外设为 `string` 类型,并添加了 `etl_date` 分区字段。然而,由于使用逗号作为字段分隔符,当 `address` 字段含逗号时,数据写入 Hive 出现错位,导致 `create_time` 值变为中文字符串。问题解决方法包括更换字段分隔符或使用 Hive 默认分隔符 `\u0001`。此案例提醒在建表时需谨慎选择字段分隔符。
|
3天前
|
SQL 缓存 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
【5月更文挑战第20天】下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
24 3
|
6天前
|
SQL 关系型数据库 MySQL
解决向MySQL中导入文件中的 数据时出现的问题~
解决向MySQL中导入文件中的 数据时出现的问题~
|
6天前
|
SQL 关系型数据库 MySQL
mysql插入500条数据sql语句
【5月更文挑战第12天】
|
6天前
|
SQL 资源调度 关系型数据库
实时计算 Flink版产品使用合集之源表的数据被删除后,目标数据库在重启服务后没有进行相应的删除操作,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
6天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从Oracle数据库同步数据时,checkpoint恢复后无法捕获到任务暂停期间的变更日志,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。