你可能会忽视的 MySQL 细节(下)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 你可能会忽视的 MySQL 细节(下)

选择合适的数据类型


我们会经常遇见的一个问题就是,在建表时如何选择合适的数据类型,通常选择合适的数据类型能够提高性能、减少不必要的麻烦,下面我们就来一起探讨一下,如何选择合适的数据类型。


CHAR 和 VARCHAR 的选择


char 和 varchar 是我们经常要用到的两个存储字符串的数据类型,char 一般存储定长的字符串,它属于固定长度的字符类型,比如下面


char(5) 存储字节
'' ' ' 5个字节
'cx' 'cx ' 5个字节
'cxuan' 'cxuan' 5个字节
'cxuan007' 'cxuan' 5个字节


可以看到,不管你的值写的是什么,一旦指定了 char 字符的长度,如果你的字符串长度不够指定字符的长度的话,那么就用空格来填补,如果超过字符串长度的话,只存储指定字符长度的字符。


这里注意一点:如果 MySQL 使用了非 严格模式的话,上面表格最后一行是可以存储的。如果 MySQL 使用了 严格模式 的话,那么表格上面最后一行存储会报错。


如果使用了 varchar 字符类型,我们来看一下例子


varchar(5) 存储字节
'' '' 1个字节
'cx' 'cx ' 3个字节
'cxuan' 'cxuan' 6个字节
'cxuan007' 'cxuan' 6个字节


可以看到,如果使用 varchar 的话,那么存储的字节将根据实际的值进行存储。你可能会疑惑为什么 varchar 的长度是 5 ,但是却需要存储 3 个字节或者 6 个字节,这是因为使用 varchar 数据类型进行存储时,默认会在最后增加一个字符串长度,占用1个字节(如果列声明的长度超过255,则使用两个字节)。varchar 不会填充空余的字符串。


一般使用 char 来存储定长的字符串,比如「身份证号、手机号、邮箱等」;使用 varchar 来存储不定长的字符串。由于 char 长度是固定的,所以它的处理速度要比 VARCHAR 快很多,但是缺点是浪费存储空间,但是随着 MySQL 版本的不断演进,varchar 数据类型的性能也在不断改进和提高,所以在许多应用中,VARCHAR 类型更多的被使用。



在 MySQL 中,不同的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不同


  • MyISAM:建议使用固定长度的数据列替代可变长度的数据列,也就是 CHAR


  • MEMORY:使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理


  • InnoDB:建议使用 VARCHAR 类型


TEXT 与 BLOB


一般在保存较少的文本的时候,我们会选择 CHAR 和 VARCHAR,在保存大数据量的文本时,我们往往选择 TEXT 和 BLOB;TEXT 和 BLOB 的主要差别是 BLOB 能够保存二进制数据;而 TEXT 只能保存字符数据,TEXT 往下细分有


  • TEXT


  • MEDIUMTEXT


  • LONGTEXT


BLOB 往下细分有


  • BLOB


  • MEDIUMBLOB


  • LONGBLOB


三种,它们最主要的区别就是存储文本长度不同和存储字节不同,用户应该根据实际情况选择满足需求的最小存储类型,下面主要对 BLOB 和 TEXT 存在一些问题进行介绍

TEXT 和 BLOB 在删除数据后会存在一些性能上的问题,为了提高性能,建议使用 OPTIMIZE TABLE 功能对表进行碎片整理。


也可以使用合成索引来提高文本字段(BLOB 和 TEXT)的查询性能。合成索引就是根据大文本(BLOB 和 TEXT)字段的内容建立一个散列值,把这个值存在对应列中,这样就能够根据散列值查找到对应的数据行。一般使用散列算法比如 md5() 和 SHA1() ,如果散列算法生成的字符串带有尾部空格,就不要把它们存在 CHAR 和 VARCHAR 中,下面我们就来看一下这种使用方式


首先创建一张表,表中记录 blob 字段和 hash 值


image.png

image.png


这是合成索引的例子,如果要对 BLOB 进行模糊查询的话,就要使用前缀索引。

其他优化 BLOB 和 TEXT 的方式:


  • 非必要的时候不要检索 BLOB 和 TEXT 索引


  • 把 BLOB 或 TEXT 列分离到单独的表中。


浮点数和定点数的选择


浮点数指的就是含有小数的值,浮点数插入到指定列中超过指定精度后,浮点数会四舍五入,MySQL 中的浮点数指的就是 floatdouble,定点数指的是 decimal,定点数能够更加精确的保存和显示数据。下面通过一个示例讲解一下浮点数精确性问题


首先创建一个表 cxuan006 ,只为了测试浮点数问题,所以这里我们选择的数据类型是 float



image.png


image.png


image.png


日期类型选择


在 MySQL 中,用来表示日期类型的有 「DATE、TIME、DATETIME、TIMESTAMP」,在


138 张图带你 MySQL 入门


这篇文中介绍过了日期类型的区别,我们这里就不再阐述了。下面主要介绍一下选择


  • TIMESTAMP 和时区相关,更能反映当前时间,如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。
  • DATE 用于表示年月日,如果实际应用值需要保存年月日的话就可以使用 DATE。
  • TIME 用于表示时分秒,如果实际应用值需要保存时分秒的话就可以使用 TIME。
  • YEAR 用于表示年份,YEAR 有 2 位(最好使用4位)和 4 位格式的年。默认是4位。如果实际应用只保存年份,那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间,还能提高表的操作效率。


MySQL 字符集


下面来认识一下 MySQL 字符集,简单来说字符集就是一套文字符号和编码、比较规则的集合。1960 年美国标准化组织 ANSI 发布了第一个计算机字符集,就是著名的 ASCII(American Standard Code for Information Interchange) 。自从 ASCII 编码后,每个国家、国际组织都研究了一套自己的字符集,比如 ISO-8859-1GBK 等。


但是每个国家都使用自己的字符集为移植性带来了很大的困难。所以,为了统一字符编码,国际标准化组织(ISO) 指定了统一的字符标准 - Unicode 编码,它容纳了几乎所有的字符编码。下面是一些常见的字符编码


字符集 是否定长 编码方式
ASCII 单字节 7 位编码
ISO-8859-1 单字节 8 位编码
GBK 双字节编码
UTF-8 1 - 4 字节编码
UTF-16 2 字节或 4 字节编码
UTF-32 4 字节编码


对数据库来说,字符集是很重要的,因为数据库存储的数据大多数都是各种文字,字符集对数据库的存储、性能、系统的移植来说都非常重要。


MySQL 支持多种字符集,可以使用 show character set; 来查看所有可用的字符集


image.png


或者使用


select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;


来查看。


使用 information_schema.character_set 来查看字符集和校对规则。


image.png


            </div>
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
存储 SQL 缓存
你可能会忽视的 MySQL 细节(上)
你可能会忽视的 MySQL 细节(上)
115 0
你可能会忽视的 MySQL 细节(上)
|
存储 算法 关系型数据库
你可能会忽视的 MySQL 细节(下)
你可能会忽视的 MySQL 细节(下)
你可能会忽视的 MySQL 细节(下)
|
存储 SQL 缓存
你可能会忽视的 MySQL 细节(上)
你可能会忽视的 MySQL 细节(上)
你可能会忽视的 MySQL 细节(上)
|
23天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
96 6
|
21天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
55 3
Mysql(4)—数据库索引
|
23天前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
58 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
|
6天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
40 2
|
9天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
42 4
|
15天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?
|
19天前
|
SQL Oracle 关系型数据库
安装最新 MySQL 8.0 数据库(教学用)
安装最新 MySQL 8.0 数据库(教学用)
87 4