一、创建sql

USE test;
SELECT DATABASE();
SHOW TABLES;
SHOW TABLES FROM mysq1;

CREATE TABLE tb1( 
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY DEFAULT '',
    age TINYINT UNSIGNED,
    salary FLOAT(8,2) UNSIGNED NULL
)ENGINE=InnoDB;

SHOW COLUMNS FROM tb1; //查看数据表结构

二、约束

• 唯一约束：UNIQUE KEY，唯一约束可以保证记录的唯一性、唯一约束的字段可以为空值（NULL）、每张数据表可以存在多个唯一约束

• 默认约束：DEFAULT

• 主键约束：PRIMARY KEY

• 非空约束：NOT NULL

• 外键约束：FOREIGN KEY，1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。2.数据表的存储引擎只能为InnoDB。3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

CREATE TABLE provinces( 
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
    pname VARCHAR(20) NOT NULL
);
CREATE ABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR（10）NOT NULL,
    FOREIGN KEY（pid）REFERENCES provinces (id) ON DELETE CASCADE
);
SHOW INDEXES FROM provinces \G;

• 1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行

• 2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

• 3.RESTRICT：拒绝对父表的删除或更新操作。

• 4.NOACTION：标准SQL的关键字，在MySQL中与RESTRICT相同

三、utf8

utf8_general_cs：区分大小写（cs：case sensitiveci 大小写敏感）

utf8_general_ci：不区分大小写，校对快、准确度差（ci：case insenstive 大小写不敏感）

utf8_unicode_ci：不区分大小写，校对慢、准确度高

utf8_bin：区分大小写，可存储二进制内容

字符集设置：https://lulublog.cn/p/d1ax02

utf8mb4_0900_ai_ci：https://lulublog.cn/p/K8JRL

四、char、varchar、text

A、char的总结：

• char 最大长度是 255 字符，注意是字符数和字符集没关系。

• 可以有默认值，尾部有空格会被截断。
  

B、varchar的总结：

• varchar 的最大长度 65535 是指能存储的字节数，其实最多只能存储 65532 个字节，还有 3 个字节用于存储长度。

• 注意是字节数这个和字符集有关系。

• 一个汉字字符用 utf8 占用 3 字节，用 gbk 占用 2 字节。可以有默认值，尾部有空格不会截断。
  

C、text的总结：

• text 和 varchar 基本相同。text 会忽略指定的大小这和 varchar 有所不同，text 不能有默认值。尾部有空格不会被截断。

• text 使用额外的 2 个字节来存储数据的大小，varchar 根据存储数据的大小选择用几个字节来存储。

• text 的 65535 字节全部用来存储数据，varchar 则会占用 1－3 个字节去存储数据大小。
  

五、创建表时的性能优化

①、永远为每张表设置一个 ID

给每张表建立自增的主键 ID，自增 ID 插入快，能避免页的分裂，减少碎片，尤其在做数据同步的时候 ，没有主键的表就像是没身份证，数据的比对更新删除都可能变乱套。

每张表都应该设置一个 ID 字段为主键，该主键应为 INT 或 UNSIGNED 类型，并设置上自动增加的 AUTO_INCREMENT 标志。因为使用 VARCHAR 类型的主键，会使得性能下降。

这里，只有一个情况是例外，那就是 “关联表” 的 “外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做 “外键”。比如：有一个 “学生表” 有学生的 ID，有一个 “课程表” 有课程 ID，那么，“成绩表” 就是 “关联表” 了，其关联了学生表和课程表，在成绩表中，学生 ID 和课程 ID 叫 “外键” 其共同组成主键。

②、为搜索字段建索引

将 where 中用的比较频繁的字段建立索引，联合索引

③、使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如 “国家”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

ENUM 是 MySQL 数据库特有的字段类型，使用后会影响迁移到其它数据库。所以，如果以后又改数据库的情况，一定要慎用。

④、尽可能的使用 NOT NULL

应该总是让你的字段保持 NOT NULL，因为这样节省空间（NULL 也是需要空间的）。

⑤、把 IP 地址存成 UNSIGNED INT

如果使用整形来存放 IP 而不是 VARCHAR(15) 字段，节省了很多的空间（需要写一个 IP 转换的函数）。

⑥、起名要规范

表名字段名统一小写加下划线，给每个字段加清晰的注释。相信我，不加注释的表，三个星期后的你，自己都看不懂。

⑦、禁用保留字

在创建表字段的时候，不要使用数据库官方的保留字。以前我们在适配信创的时候，发现老库中自定义的函数名跟新库的关键字 发生冲突，只能改库、改代码，这都是血淋淋的教训。

⑧、禁用外键

外键就像是紧箍咒，让表之间藕断丝连，更新删除都会变慢，还容易死锁，数据的一致性还是交给业务代码来管更加灵活高效。

⑨、字符集选 utf8mb4

记住 MySQL 里的 utf8 是间割版的，只有 utf8mb4 才是正宗的，能存表情特殊符号，妈妈再也不担心乱码了。

⑩、主业务表尽量采用 bigint

早期表主键使用的是 int，随着业务的发展，int 容量很快告急，最后被迫投入大量的人力升级为 bigint，这些年加的班全是当年脑子进的水。

六、设计表时的性能优化

①、选择正确的存储引擎

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

②、固定长度的表会更快

表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为 MySQL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。

③、垂直分割/主子表分家

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）

示例一：在 Users 表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

实例三：主子表分家，避免大胖子表。以前我们将所有的商品信息都堆在一张表导致它变成了一个大胖子，在关联查询的时候很容易形成性能瓶颈，后来我们把商品名称商品价格等核心字段放在主表，而把商品描述放在拓展表，表结构一瘦身性能咔咔起飞。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去 Join 他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

④、核心字段的冗余

用空间换时间，商品名称是一个高频字段，订单表在存储商品 ID 的时候，我们顺带的冗余商品的名称， 能够避免关联查询。

⑤、数据的删除

使用回收站模式， 千万别直接 delete 数据，一旦物理删除，恢复起来费时又费力。

聪明的做法是加个 is_delete 的字段，想恢复的时候就改回"N"，妈妈再也不担心丢数据。

⑥、更新数据的时候顺手更新时间戳字段

在需要获取增量数据的时候，这个时间字段就是你的火眼金睛。

没有它你无法感知最新数据的变化，需要每次全表对比。

有了它你只需要加 where 时间字段大于上次的执行时间就能很快的锁定所有的新数据，查询效率呈指数级上升。