MySQL之路-1-SQL分类与数据类型

2018-07-08  本文已影响17人  灭BUG

1. SQL分类

SQL语句可以划分为以下4个类别：

1.1 DDL（Data Definition Language）

数据定义语句，用于定义数据库对象（包括：数据库、表、列、索引等），常用关键字有：create、drop、alter等。

-- 创建数据库
create database dbname;
-- 删除数据库
drop database dbname;
-- 创建表
create table tablename(...)
-- 添加列
alter table tablename column1 type;
...

1.2 DQL（Data Query Language）

数据查询语句，用于查询数据库记录，常用关键字有：select。

-- 查询某个表的所有记录
select * from tablename;
...

1.3 DML（Data Manipulation Language）

数据操纵语句，用于添加、删除和更新数据库记录，并检查数据完整新，常用关键字有：insert、delete、update等。

-- 插入数据
insert into tablenamt value(...);
-- 更新数据
update tablename set column1 = value1;
-- 删除数据
delete tablename;
...

1.4 DCL（Data Control Language）

数据控制语句，用于授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等，常用关键字有：grant、invoke和deny等。

-- 在本地环境中授予某个用户对某个数据库中所有表的select/insert权限
grant select,insert on dbname.* to 'username'@'localhost' indentified by 'password';

2. MySQL数据类型

MySQl数据类型基本分为三类：

2.1 数值类型

类型 字节数
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8
float 4
double 8
decimal (m,d) 最大值范围与double相同，取值范围可由m,d值控制

2.1.1 decimal和float、double的区别？

float、double是非标准数据类型，在数据库中保存的是近似值，decimal是标准数据类型，在数据库中是以字符串的形式保存数值。

2.1.2 decimal中的m、d取值分别是什么含义？

d是指小数部分的位数，m则是指整数+小数的总长度。

2.2 日期和时间类型

类型 字节数 格式
date 3 YYYY-MM-DD
time 3 HH:MM:SS
year 1 YYYY
datetime 8 YYYY-MM-DD HH:MM:SS
timestamp 4 YYYY-MM-DD HH:MM:SS

2.2.1 datetime类型和timestamp类型的区别？

注：实际使用中，我们再建表的时候通常需要再表中添加createTime和updateTime这两个字段，可以参考下面的例子来写

`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

2.3 字符串类型

类型 范围 备注
char 0~255字节
varchar 0~65535字节
tinyblob 0~255字节 不超过255个字符的二进制字符串
tinytext 0~255字节
blob 0~65535字节
text 0~65535字节
mediumblob 0~16777215字节
mediumtext 0~16777215字节
longblob 0~4294967295字节 二进制形式的极大文本数据
longtext 0~4294967295字节 极大文本数据

2.3.1 char类型和varchar类型的区别？

char和varchar类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。char(8)填了4个字符，会补上4个空格，varchar(8)则不会。

3. 选择合适的数据类型

3.1 char与varchar的选择

char和varchar都可以用来存储字符串，但是它们的保存和检索方式不一样。char是定长类型，而varchar是可变长类型。由于char是定长的，所以char处理速度比varchar快得多，但是浪费存储空间，适合长度变化不大并对查询速度有较高要求的数据。

注：随着MySQL版本的提升，varchar类型的性能也在不断提高，所以在实际应用中，反而是varchar被更多地使用。

在MySQL中，不同的存储引擎中，对char和varchar的使用原则有所不同：

注：对于使用InnoDB的表，内部的行存储格式没有区分定长和可变长，因此在本质上，使用定长度的CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

3.2 text和blob的选择

存储大文本的时候，通常会选取text或者blob类型，两种类型的主要差别是blob是用来保存二进制数据的，text是用来保存字符串数据的。在实际使用中，应该根据实际情况，选取能够满足需求的最小存储类型。

3.2.1 text和blob类型大批量删除数据的时候可能会存在的问题

大批量删除数据后，会在数据库表存在大量的“空洞”，具体表现形式就是，删除数据后，对应表的占用的存储空间并没有变小。这时候可以通过optimize table命令对表进行碎片整理，避免因为“空洞”导致的性能问题。

optimize table tablename;

你可以通过show table status命名去判断数据库中的表是否需要使用optimize table命令

show table status;
image

其中有两个参数是比较重要的，Data_length是代表整个表的数据量，Data_free是代表标识为已分配，但未使用的空间，并且包含了已被删除行的空间。

  • MySQL官方建议每周或者每个月整理一次即可
  • optimize table命令只对MyISAM，InnoDB起作用，其中对MyISAM作用最为明显
  • optimize table命令运行过程中会进行锁表操作
  • InnoDB执行optimize table命令的时候可能会提示Table does not support optimize, doing recreate + analyze instead，可以通过mysqld --skip-new或者mysqld –-safe-mode命令重启MySQL解决问题

3.2.2 使用合成索引提高大文本字段的查询性能

合成索引是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据。

3.2.3 在不必要的时候避免检索大文本字段

select * 查询就不是很好的想法，除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值。

3.2.4 将大文本字段分离在单独的表中

在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。

3.3 浮点数和定点数的选择

在MySQL中可以使用float和double类型表示浮点数，当数据精度超出字段的实际精度，插入值则会被四舍五入。定点数decimal不同于浮点数，定点数是以字符串形式存放的，更加精准，当数据精度大于字段的实际精度，MySQL会告警。

注：对于货币之类对精度敏感的数据，应该使用定点数存储

3.4 日期类型选择

MySQL提供常用的日期类型有：date、time、datetime、timestamp，选择日期类型一般需要遵循下面原则：

上一篇下一篇

猜你喜欢

热点阅读