首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 性能优化--优化数据库结构之优化数据类型

MySQL 性能优化--优化数据库结构之优化数据类型

作者头像
授客
发布2019-09-10 19:00:26
4.9K2
发布2019-09-10 19:00:26
举报
文章被收录于专栏:授客的专栏授客的专栏

优化数字数据(Numeric Data)

l 对于唯一ID或其它可用字符串或数字表示的值,选择用数字列好过用字符串列。因为相比对应的字符串,可使用更少的字节存储大数字,同时,转换并比较数字速度更快且消耗更少的内存。

参考连接:http://dev.mysql.com/doc/refman/5.7/en/optimize-numeric.html

优化字符和字符串类型(Character and String Type)

l 当不需要语言特定的比对功能时,使用二进制比对(binary collation)命令对来获取更快的比较和排序操作。在特定查询中使用BINARY 操作符以使用二进制比对。

eg:

mysql> SELECT 'a' = 'A';

-> 1

mysql> SELECT BINARY 'a' = 'A';

-> 0

mysql> SELECT 'a' = 'a ';

-> 1

mysql> SELECT BINARY 'a' = 'a ';

-> 0

l 当需要比较不同列的值时,尽可能为那些列定义相同的字符集和比对方法,避免执行查询时进行字符串转换。

l 对于小于8KB的列值,使用二进制VARCHAR,而非BLOB,GROPU BY和ORDER BY语句会生成临时表,如果原始表没包含任何BLOB列,那么这些临时表可使用MEMORY 存储引擎。

l 如果表包含字符串列,如名字和地址,但是许多查询不检索那些列,可考虑把这些字符串列拆分到一个单独的表,必要时使用携带外键的join查询。当MySQL检索来自某记录行的任意值时,它会读取包含该记录行(也可能还有其它相邻行)所有列的数据块。保持每个记录行尽可能的小,仅含最频繁使用的列,这样允许在每个数据块中放入更多的记录行。这样紧凑的表,可减少大部分查询带来的磁盘I/O和内存使用。

l 当在InnoDB表中使用随机生成的值作为主键时,如果可能的话,使用一个“升序值”(ascending value)如当前日期和时间作为其前缀。当连续的主键值在物理上连续存储时,可以加快InnoDB的插入和检索速度。

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/optimize-character.html

优化BLOB

l 当存储一个包含文本数据的大二进制对象,考虑先压缩。当整个表都被InnoDB或MyISAM压缩过时不能使用该技术。

l 对于包含多列的表,为减少查询内存占用,不使用BLOB列的话可考虑把BLOB列拆分到单独的表,并在需要时使用join方式引用。

l 由于检索和展示BLOB值的性能要求和其它数据类型不一样,可以考虑把特定于BLOB的表放在不同的存储设备,甚至是一个单独的数据库实例。例如,检索一个BLOB可能需要一个大的顺序读磁盘(sequential disk read),相比SSD 设备,更适合传统的硬盘驱动。

l 与其直接比较长文本字符串的相等性,可在某个单独的列中存储长文本所在列列值的哈希,并为存储哈希值的列建立索引,查询的时候测试哈希是否相等(使用MD5()、CRC32()函数生成哈希值)。由于哈希函数会为不同输入生成重复结果,所以查询中还需要增加语句blob_column=long_string_value以防止错误的匹配。针对哈希值的更小,更容易的索引扫描有助于提高性能。

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/optimize-blob.html

使用PROCEDURE ANALYSE

ANALYSE([max_elements[,max_memory]])

ANALYSE()检测来自查询的结果并返回分析结果,为每列的数据类型给出可能帮助减小表的优化建议。使用方法如下,直接把语句拼接到查询末尾:

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

eg:

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

可选参数:

l max_elements:默认256,指定每列中,ANALYSE()关注的不重复值的最大数量。ANALYSE()用于检测优化数据类型是否应该为ENUM,如果有多余max_elements个补重复值,则ENUM不为建议类型。

l max_memory:默认为8192,当ANALYSE()试图查找所有不重复值时,为每列分配的最大内存量。

注意:PROCEDURE ANALYSE()不能在UNION语句中使用。

参考连接:

http://dev.mysql.com/doc/refman/5.7/en/procedure-analyse.html

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2016-07-24 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 优化数字数据(Numeric Data)
  • 优化字符和字符串类型(Character and String Type)
  • 优化BLOB
  • 使用PROCEDURE ANALYSE
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档