跳至主要內容

MySQL建表注意哪些

2022年12月24日MySQL

本文基于 MySQL8.0，InnoDB引擎，如有错误欢迎指正。

字段类型

  • 数字

    1. 尽量选择符合存储范围的前提下的小类型，知道每一种占用的字节数。
    2. 不存在负数需求情况下使用无符号unsigned
    3. ID用int自增，一般选bigint没啥必要
    4. tinyint可以用来存储 布尔、枚举、年龄等，枚举时尽量不占用0值，布尔用0和1表示。
    5. 不使用浮点数。金额的存储可以用int扩大100倍的方式，或者用decimal(本质是字符串)

  • 字符串

    1. 固定长度如手机号、身份证、hash值使用char。
    2. 如博客内容，大段落文本使用text。
    3. 其他使用varchar。虽然磁盘占用按实际大小，但是字段应设置尽量小的长度限制。不需要16、64这种'整数'，完全可以10、20。
    4. 虽然存在json类型，但据说支持不太好，通常手动转为字符串存储。

  • 时间

    1. 日期时间，建议使用datetime，不建议timestamp和int，原因见下文。
    2. 创建时间设置NOT NULL DEFAULT CURRENT_TIMESTAMP，更新时间NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，自动维护。

建立索引

  • 索引类型选择。
    1. 主键索引。就用int
    2. 唯一索引。具有强唯一性的，例如登录账号、某些唯一标识。这类不要使用普通索引依靠代码来限制其唯一性。唯一索引在插入性能上逊色于普通索引，因为需要检查唯一性，不能利用change buffer。
    3. 普通索引。为了最大效率，通常建立多个字段联合索引。
    4. 全文索引。不使用，用elasticsearch替代。
  • 联合索引。
    1. 字段选择。
    2. 区分度。count(DISTINCT(nickname))/count(*)在0.8以上适合建立索引，如性别区分度很低不适合。(但是这仅供参考，例如state状态，很多时候还是适合建立索引的)
    3. 字符串字段长度。建立索引时字段太长考虑部分长度作为索引，或者通过CRC32哈希额外一个字段作为索引(容易哈希冲突，条件须同时带上原字段)。
    4. 更新是否频繁。索引也是表，频繁变动也是影响性能的。
    5. 字段顺序。
    6. 区分度。高的在前，例如pid，区分度稍低的在后，例如。
    7. 条件类型。经常=的在前，而in、<、>、between and都属于range在最后，其之后的字段不走索引

语句注意

  • 尽量不使用select *，尤其是在无limit和不清楚索引状况的时候。
  • 统计count(*)是规范，count(1)、count(id)都是错误的，不会更优且可能有问题。
  • 尽量不使用子查询，无法利用索引，代码层面拆开。
  • 避免多表join
  • where in 、where >、where between都是range
  • not in不走索引，视情况该过滤放在代码层面
  • or大多数情况不走索引，但是也可能出现索引合并的情况，需要explain确认，否则使用union。
  • union all要优于union，后者会查重，如果不存在重复的情况使用union all
  • order by尽量避免出现Using filesort。有些按创建时间排序可以考虑用id代替。
  • limit x offset y的本质是在引擎层查到y+x条，在server层抛弃前y条。所以要注意offset的偏移大小，如果应用索引了可以先select id再id in拆分为两条语句
  • 查询一条记录必须带上limit 1

详细问题

  • 索引是什么？

    1. 索引也是一张表，MySQL主键之所以叫聚簇索引，就是因为表本身就是主键索引表本身直接拿到数据，其他非聚簇索引都是通过索引表查到主键id，如果索引表包含所有select的字段，则索引覆盖返回数据，否则把id进行MRR排序，随机IO变顺序IO回表查询。
    2. InnoDB的索引是B+树，使用二分查找
    3. 数据的修改涉及索引表的更改，所以表索引不能多，5个以内。

  • 日期时间存储选择datetime、timestamp、int那种？

    1. timestamp底层结构是整型UTC时间戳，存储和搜索时都自动转为UTC时间戳，取出后转为本地日期时间。占用4字节，范围1970-2038年。优点：按时区时间自动转换，占用空间小。缺点：客户端和服务端时区不一致时存在问题。
    2. datetime底层结构是对bit的拆解使用，无关时区。占用5字节(5.6.4前占8字节)，范围0000-9999年。
    3. datetime和timestamp优于int的点是，前两者可以默认当前时间，且不需要前后端手动转化。网上总说int效率高，但是我觉得这点容易歧义，在底层扫描查找时应该效率是相同的，差别在于扫描前和得到结果后的格式转化，用int是把这一点放在前后端手中。
    4. timestamp的好处在于时区，不稳定的地方也在时区，客户端与服务端时区不一致时，客户端传过来的时间参数需要转化为服务端时区时间。
    5. 总结。还是建议用datetime的。

  • “硬删除”还是“软删除”？

    1. 这个问题比较复杂，只能说视情况而定。
    2. 即便是硬删除，在磁盘中数据也还是存在的，空间不会释放，这是底层每行数据的隐藏字段控制的。(行记录复用:如果存在100，300，500三条记录，删除300，此时再插入100-500之间的记录就可以复用300的空间。数据页复用：整页不需要收到ID的限制，可以全然复用)
    3. 存在软删除，查询语句要时刻想着带删除条件（当然很多orm支持默认带删除过滤）(注意与唯一索引的冲突)
    4. 软删除字段。可以用datetime default null记录删除时间，查询时is not null；用int not null default 0记录删除时间戳甚至删除者id，查询时= 0；用tinyint not null default 0记录是否删除is_delete，查询时= 0。三者比较，虽然我上边说日期时间建议datetime，但是这里datetime的零值如果用0000-01-01 00:00:00很怪异，用is not null空表示未删除则不能用索引，且datetime比int多占用1字节，比tinyint多4字节。对于tinyint和int，似乎删除时间的记录也可以参考更新时间，那么删除只是标记就可以了。
    5. 软删除字段索引。通常来说，绝大部分数据是未删除的，建立索引时没效果的，但是也不排除个别查询语句索引覆盖有应用价值。
    6. 总结。如果不在乎，硬删除就好。软删除选择int存储deleted_by或者tinyint存储is_deleted，该字段建立索引要慎重。当然也可以考虑其他方案：如把删除数据放到单独的表备份，原表硬删除。

  • 昵称模糊查询不能用索引？

    1. 左前缀属于老生常谈，但业务上很难和产品达成一致，不符合用户习惯，通常是全模糊。
    2. 全模糊未必不走索引。须满足：昵称为普通索引，语句为select id,nickname from user where nickname like '%XXX%';。 全模糊不能利用索引直接二分法跳过，但是普通索引意味着可能存在重复，那么扫描一遍普通索引的数量仍然小于等于全表扫描，但是这已经是极限了，不能再加筛选条件，不能查询其他字段造成回表(有需求的话在代码层面用id再回表查一次)

  • 字符集用utf8还是utf8mb4? 参考

    1. 在Unicode编码下，英文占1字节，汉字占3字节，例："Hi世界"占用的长度是8字节。SELECT length(column)
    2. MySQL中的utf8其实是utf8mb3的别名，utf8mb4比utf8mb3多支持1字节的增补字符。即对于一般的汉字，两者具有相同的码值、相同的编码、相同的长度。对于emoji这类，utf8mb4才多一个增补字符，占用4字节存储。
    3. 以varchar(10)举例，它实际占用的大小可能在10-40字节(全是英文或全是emoji)，但是该字段索引长度为43(4*10+2字节长度+1字节是否null)

扩展概念（InnoDB)

  1. Buffer Pool缓冲池。内存空间，专用服务器通常将80%的内存给缓冲池。查找数据时先将磁盘页读取到内存，在内存中查找或者更改，使用LRU算法。

  2. Change Buffer更改缓冲区。内存空间。当要修改的数据不在缓冲池时，减少读盘，先把修改记录在更改缓冲区中，稍后再刷盘

  3. binlog。server层二进制文件，用作主从复制和数据恢复。

  4. Redo Log重做日志。文件，满了后擦出前一部分循环写。记录对数据的更改，当程序突然崩溃，缓冲池数据未同步到磁盘丢失，依据重做日志恢复。（redo log减少写操作，不管磁盘页是否在缓冲池，都记录。 change buffer只在磁盘页不在缓冲池时记录。前者减少写盘，后者减少读盘)

  5. Undo Log撤销日志。是文件，记录旧版本行数据，应用在MVCC和事务回滚。

  6. binlog、redolog、undolog对比

  7. row format行格式，大于等于5.7版本的默认行格式都是Dynamic，主要是针对varchar和text，当字段数据过长，影响存储性能，设计溢出页，数据中存储指向溢出页的指针。（所以真的不要随便select *，会有额外的操作)

  8. 每行数据的隐藏字段

  9. 事务ID。6字节,该记录最新的创建或修改的事务ID，与MVCC有关。(执行删除时，其中一个特殊位标记为删除)

  10. 回滚指针。7字节,指向undo log中这条记录的上一个版本

  11. _rowid。6字节,如果有主键，其值为主键的值(或唯一索引的值)，否则自动生成，可直接select查看到

  12. 数据读取方式

  13. 快照读: 普通select都是快照读，读取数据的快照版本，基于MVCC和undo log，没有锁。

  14. 当前读: insert、update、delete 是当前读，读取数据的最新记录，可能发生等待。临键锁(行锁+间隙锁)实现