独立开发者系列（16）——数据表的设计

绝大部分项目，都需要用到数据库，而数据库需要设计数据表。而设计数据表，需要理解一些关于MYSQL的属性背景。要不然设计的时候，不符合规范或者性能非常差。
建立数据库。 主要是需要掌握字符集概念，在以前gbk网页编码比较流行的时候，有些字符集是gbk,但是当前绝大部分情况下已经统一成utf8 而更进一步的最好设置成utf8mb4  。utf8mb4跟utf8的区别是兼容emoj表情，同时兼容utf8全部，在当前utf8已经基本统一大部分场景的环境下，基本选择utf8mb4字符集，更强悍的兼容相关表情。

建立新的数据表，主要考虑Myisam和Inodb ，俩者最大的区别体现在前者性能高，后者对事物支持性好。一般业务场景里面，都会选择inodb类型。其他的csv 文本储存 之类的，MYSQL相对而言，并不擅长，如果使用其他的类型，会使用更加专业化的工具，比如文本检索管理的Elasticsearch。擅长跟能做是俩个概念，在以前的时候，我特别喜欢用自己掌握的技能去实现一件它本身并不擅长的事情。比如我曾经用批处理，实现简易对象开发，还觉得很有成就感。而且当前绝大部分场景innodb都已经够使用。

表字段设计。字段 一般包括变量名（就是我们程序里面使用的），
类型，类型比较关键，varchar表示是可变的，char表示不可变的，字面意思varchar会自动根据需求扩充大小，而char占据一个固定的，因为我们一般设计字段的时候，是按照最长填写的，如果使用char会造成大量的冗余浪费，所以大部分情况下，我们是选择varchar，可以节省大量的空间。

Int 类型，数字类型，数字类型对索引特别友好，主要是性能上的影响，如果本身项目比较小，性能不关键，差别并不大，不过在一些自动化生成框架里面

    是会根据你的字段类型，生成int标签还是text标签

Text 储存文本，一般需要触发富文本编辑。

Enum  枚举类型  枚举类型性能比较高，也是出于系统表设计的性能考虑，设置枚举类型，查找，索引都很有优势。主要应对有限的类型情况

 Set 类型 集合类型。集合类型支持组合情况下使用，通俗的理解就是需要支持多选的情况下 设置，而enum只支持一个值，set支持N个唯一的值。

Datetime  时间类型 储存的是我们日常使用格式 2024-06-30 12:12:25 这种时间格式。其他的格式我们用的比较少，也有储存时间戳的，储存时间戳优势是性能高点，劣势是你直接拿到数据表是看不出发生的时间，这个在实际项目里面非常麻烦，因为你经常需要知道看到了数据表，就知道数据产生的时间。

Deciemal  精度数据储存，基本是用户金额或者瓜分奖励会用到的类型。

   其他的理论上很有用，但是用的比较少，比如对于二进制文件储存（我们当前基本使用OSS储存，没有谁会使用mysql储存二进制文件的），又比如地理坐标的储存（基本用redis开发地理定位，一般不会使用mysql实现）

默认值，也就是再没有外部插入的情况下，系统会自动生成的默认值。当类型是tinyint、init  bigint类型的时候，默认值非常有用，否则系统默认NULL，一旦外部程序没有写入对应变量，会导致查询效率大幅度降低。
 

  上面简单梳理了 数据库/数据表/字段的设计。理论上，你就可以开始设计自己的项目数据表了，如果作为小型项目，你可以不管性能，索引。

   而当数据量稍微有点大，达到了百万级别的时候，你就必须要设计索引，一般中小项目的数据目标都是百万-千万这个级别。

  所以还必须理解索引概念，索引有点类似图书馆的藏书目录。你打开藏书目录，根据书名，可以知道对应的书是在哪个图书馆，哪个图书室，哪个书架的哪个编号上，这样你根据从目录里面获取到的信息，跑到指定位置，就可以快速找到书籍。而需要注意的是，你之所以能使用索引，本身就是确保了索引规模远小于数据表本身，所以查询索引速度远远比直接去对应表里查数据快，但是当你给你的表里的大部分字段建立索引的时候，你这是多此一举，反而降低了查询速度，这个对于很多不理解索引为什么会导致数据查询变快的使用者，他们知道索引能加快速度后，就给数据表的大部分字段加索引。

  而真正的表查询过程是先去索引表（所以数据量很小的时候，索引意义不大），在索引那拿到对应的主键，然后回到主表里面，取出对应的数据（这个过程称之为回表），所以类似sex= 男|女 性别字段，放入索引其实是没意义的，可以这样理解这个过程，一条语句查询男的数量，50%的数据都在索引里面，找到了全部，然后根据索引到的全部ID再去主表里面查询一遍，本来根据男这个条件在主表里面直接查询出来更快。所以对很多筛选能力很弱的字段，一般不设置为索引。

然后还需要掌握一个基础知识点，就是当你的SQL查询语句很慢的时候，需要跟踪分析，一般使用explain  +执行的SQL语句，可以根据执行结果，可以查看到相关的执行效率。

相关字段的意义如下:

id：查询中每个选择的序列号，按照查询中出现的顺序进行编号。

select_type：查询类型，常见的有SIMPLE（简单查询），PRIMARY（主查询），UNION，SUBQUERY，DEPENDENT SUBQUERY（依赖外部查询结果的子查询）等。

table：查询中使用的表名或临时表。

partitions：查询操作的分区信息，如果表没有分区，这将显示为NULL。

type：这是重要的字段之一，表示连接类型，常见的有ALL（全表扫描），index（全索引扫描），range（范围扫描），ref（非唯一查找），eq_ref（唯一查找），const或system（常量表，通常是主键或唯一键查找）。

possible_keys：可能用于查询的索引。

key：实际使用的索引。

key_len：使用的索引的长度。

ref：对于ref类型的连接，显示使用哪个列或常量值与索引进行匹配。

rows：估计需要检查的行数。

filtered：表示通过WHERE子句过滤后剩余的行的百分比。