MySQL知识整理

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

字段类型

• 数字

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

• 字符串

  1. 固定长度如手机号、身份证、hash值使用char。
  2. 如博客内容，大段落文本使用text。
  3. 其他使用varchar。虽然磁盘占用按实际大小，但是字段应设置尽量小的长度限制。不需要16、64这种'整数'，完全可以10、20。

• 时间

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

索引

• 索引是什么？

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

• B+树

  • 非叶子节点只存储id和下一个节点的指针，叶子节点存储id和数据。 这样上层能尽可能多地存储id数量，树的高度更低，单页存储的id数量更多，所需内存更小，性能相对稳定。 （B树所有节点都存储数据，性能不稳定）
  • 数据都存储在底层，这样范围查找时只需要依靠链指针依次读取即可，性能好。 （B树没有指向下一节点的指针）

• 索引类型选择

  1. 主键索引。就用int，占用4字节。 通常不需要 bigint 8字节。
  2. 唯一索引。具有强唯一性的，例如登录账号、某些唯一标识。这类不要使用普通索引依靠代码来限制其唯一性。唯一索引在插入性能上逊色于普通索引，因为需要检查唯一性，不能利用change buffer。
  3. 普通索引。为了最大效率，通常建立多个字段联合索引。
  4. 全文索引。不推荐使用，通常大家用elasticsearch。
  5. 自适应哈希索引。 InnoDB不存在显式的哈希索引，在某些索引值被使用的非常频繁时, InnoDB会在内存中创建一个哈希索引, 用于这些频繁使用的索引值的快速查找。

• 联合索引建立

  1. 字段选择。
  2. 区分度。count(DISTINCT(nickname))/count(*)在0.8以上适合建立索引，如性别区分度很低不适合。(但是这仅供参考，例如state状态，很多时候还是适合建立索引的)
  3. 字符串字段长度。建立索引时字段太长考虑部分长度作为索引，或者通过CRC32哈希额外一个字段作为索引(容易哈希冲突，条件须同时带上原字段)。
  4. 更新是否频繁。索引也是表，频繁变动也是影响性能的。
  5. 用于 WHERE 条件和 GROUP BY 的字段，特别考虑索引。
  6. 字段顺序。
  7. 区分度。高的在前，例如pid，区分度稍低的在后，例如。
  8. 条件类型。经常=的在前，而in、<、>、between and都属于range

• 索引下推：考虑到联合索引 range 后的字段通常无法再利用索引，例如联合索引顺序为 (name,age) ，理想使用方式是 WHERE name='xxx' and age > 18，但是如果想统计姓“刘”的年龄大于18岁的，WHERE name LIKE '刘%' and age > 18 ，名字字段就变成了 range ，后续的年龄字段就会利用索引下推，即在姓“刘”的前缀名字里依次筛选大于18岁的，再回表查询对应数据。

SQL语句优化

• 避免 select *。尤其是多行查询，无limit和不清楚索引状况的时候。
  • 若可以索引覆盖则不用回表
  • 减少IO开销，例如varchar有溢出页
  • 减少网络流量，返回数据清晰
• 统计COUNT(*)是规范，不使用 COUNT(1)、COUNT(id)
• 尽量避免多表 JOIN
• 尽量使用内连接JOIN(即INNER JOIN)，外连接会产生临时表。
• 若JOIN是为了获取某一个字段，则需要思考表设计合理性以及是否需要适当反范式冗余字段（也带来数据一致性问题）。
• 尽量不使用子查询，会产生临时表，且一条语句只能走一个索引，有索引命中问题
• WHERE IN 、WHERE >、WHERE BETWEEN 都是range 范围，考虑这类字段（例如时间）放在联合索引的最后。
• LIKE 考虑索引左前缀问题以及联合索引的利用情况
• IN 后数组不宜过大，因为要多次回表。
• 尽量不使用 OR，可能不走索引（考虑到索引合并的情况）。可以使用 UNION ALL 句式合并替代
• UNION ALL要优于UNION，后者会查重，如果不存在重复的情况使用UNION ALL
• ORDER BY 也要注意索引问题，避免文件排序Using filesort
• 避免在字段上使用函数
• 大表分页必须考虑OFFSET偏移大小。 这一步是把结果集从引擎取出放在server层做的。优化是select id ... offset limit 再进行一次where id in查询
• 查询一条记录必须带上limit 1 (考虑到id查找或者唯一索引，可不用)
• 一条SQL语句的好不好，也不能完全基于复杂程度经验，要结合数据量分布，索引情况等综合考虑，多参考 explain 解释。

事务

• 说明：每一条增删改都是隐性的事务。我们常说的事务是显式地set autocommit=0开启事务，然后commit提交及rollback回滚。
• 特性
  • A: 原子性。一个事务是一个单元，一个事务中的所有操作，要么都成功，要么都不成功。
  • C: 一致性。数据只能从一种正确状态转换到另一种正确状态
  • I: 隔离性。事务之间不干扰。一个事务T1修改记录a时，另一个事务T2要读写a必须等待T1完成。
  • D: 持久性。对数据的修改是持久的。
• 隔离级别: 默认可重复读
  • 读未提交：A可以读到B未提交的数据R1，脏读。
  • 读已提交：A读R1，B修改R1，A读R1无改变。避免脏读。B提交，A读R1发生改变。R1在A中前后不一致，为不可重复读。
  • 可重复读：A查询数据为10条，B插入或删除，A再查询数据不是10条。幻读。
  • 串行化：事务可以完全隔离，依次执行，可以避免一切问题，但性能下降。
• 可重复读状态下MVCC的InnoDB的select（怎么做到B已经修改数据了，A却读到的仍然是之前的数据）
  • 查找版本<=当前事务版本号数据
  • 行的删除版本要么未定义，要么大于当前事务版本

详细问题

• 日期时间存储选择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)

隐藏字段

• DB_ROW_ID：6字节，主表如果没有主动设置主键，则使用此字段，如果设置了主键则没有此字段。
• DB_ROLL_PTR：7字节，回滚指针。修改数据前拷贝旧数据到undo log，此指针指向旧数据。
• DB_TRX_ID：6字节，数据插入或更新时的事务ID，删除视为更新，只是行内某个bit位设置为删除。

数据的增删改

• 增删改过程

  • 磁盘读入内存。如果数据不在内存中，先从磁盘中把数据所在页加载到内存buffer pool中。
  • 操作内存中的数据页。
    • insert。自增索引，在对应页内顺序写。空间不足产生页分裂。
    • update。把旧数据记录到undo log，再修改数据、修改隐藏字段的事务ID和指向旧数据的指针。
    • delete。同update，只是删除标识位设为1。 (所以是逻辑删除，范围查询时会被扫到，只是过滤掉了)
    • 如果事务A增删改这个数据加了临键锁(行锁+间隙锁)，那么事务B再操作会等待事务A提交后才进行。
  • 把修改记录到redo log buffer。
  • redo log buffer 刷盘。 根据 innodb_flush_log_at_trx_commit 参数，决定是否立即刷盘。默认为1，每次事务提交都会刷盘。此时磁盘中数据未改变，redo log已改变，视为事务成功，即使崩溃，也能通过redo log恢复。
  • 回滚。如果事务失败，通过undo log记录的旧数据回滚。

• 数据读取方式

  • 当前读。insert、update、delete，读取数据的最新记录，可能发生等待。临键锁(行锁+间隙锁)实现
  • 快照读。普通select，读取数据的快照版本，基于MVCC和DB_ROLL_PTR指向的undo log，没有锁。 在可重复读的隔离级别下，一个事务B读取数据，无论事务A是否对该数据修改和提交，事务B读取的都是事务A修改前的数据。

• 内存中脏页刷到磁盘时机

  • buffer pool满了，无法读入新的数据页，脏页刷盘。
  • redo log满了，脏页刷盘。
  • 系统空闲时
  • 数据库正常关闭时

扩展概念（InnoDB)

• Buffer Pool缓冲池。内存空间，专用服务器通常将80%的内存给缓冲池。查找数据时先将磁盘页读取到内存，在内存中查找或者更改，使用LRU算法。
• Change Buffer更改缓冲区。内存空间。当要修改的数据不在缓冲池时，减少读盘，先把修改记录在更改缓冲区中，稍后再刷盘
• binlog。server层二进制文件，用作主从复制和数据恢复。
• Redo Log重做日志。文件，满了后擦出前一部分循环写。记录对数据的更改，当程序突然崩溃，缓冲池数据未同步到磁盘丢失，依据重做日志恢复。（redo log减少写操作，不管磁盘页是否在缓冲池，都记录。 change buffer只在磁盘页不在缓冲池时记录。前者减少写盘，后者减少读盘)
• Undo Log撤销日志。是文件，记录旧版本行数据，应用在MVCC和事务回滚。
• row format行格式，大于等于5.7版本的默认行格式都是Dynamic，主要是针对varchar和text，当字段数据过长，影响存储性能，设计溢出页，数据中存储指向溢出页的指针。（所以真的不要随便select *，会有额外的操作)