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类型的区别？

datetime类型是按照实际格式存储，与时区无关；timestamp类型是以UTC格式保存，存储时对当前的失去进行转换，检索时再转换回来当前的时区。

范围不一样，datatime类型支持的范围更宽，timestamp类型可表示的范围为：1970-01-01 00:00:00~2038-01-09 03:14:07 。

timestamp可以通过CURRENT_TIMESTAMP() 获取。

索引速度不同，timestamp更轻量，索引相对datetime更快。

注：实际使用中，我们再建表的时候通常需要再表中添加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的使用原则有所不同：

MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。

MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或

VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。

InnoDB 存储引擎：建议使用 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，选择日期类型一般需要遵循下面原则：

根据实际需要，选择能够满足应用的最小存储的日期类型

如果需要记录时分秒，并且记录的年份比较久远，尽量使用datetime

如果记录的日期需要让不同时区的用户使用，最好选择timestamp