MySQL中text类型对查询效率的影响

背景

任何设计都需要结合实际的需求或者说系统来做，我们现在服务端的整体设计趋向于领域驱动模型（DDD）。将业务抽象划分成各个独立领域对象，各个领域各尽其职，只负责自己领域的工作。回到MySQL设计，在我们将业务关系转化为各个具体的实体时，同样秉承一个领域对象只负责自己领域的工作，数据库每张表设计落地后，也只会存储表对应的实体的属性。
当然，有时为了方便检索数据库中的数据，会在设计数据库时适当添加冗余字段（扩展字段），以空间换取时间。所以，我们系统中针对带有exten_info字段的查询接口性能都较差一点。还有一点是我们现在服务端的查询大部分带有连表查询，如果连大表级别也会有性能问题。

• MySQL中对于blob，text，varchar(5120)这样的大字段，innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中（发生溢出情况的时候），最大768字节的作用是便于创建前缀索引/prefix index，其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此查询这条记录会增加一次 io 操作。至于表设计是否需要大字段，需结合系统业务和实际情况；
  • 存大字段前尽量精简，保留重要且有用的信息；
  • 按需查询：搜索中如果不涉及到表中大字段就不进行查询；
  • 如果涉及到查询的列就尽量以新列存储；

参考：https://www.cnblogs.com/wqbin/p/11927456.html，MySQL 8.0

设计规范

1. 禁止使用SELECT *；
2. SQL语句中使用外连接时，尽量避免（join后面带）子查询，例如：join (select * from table)。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大，由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询；其次，子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
3. 主键建议使用8字节无符号的bigint(20)作为主键的数据类型，提高查询效率，存储的顺序也是有序的，往远了看，分库分表也是有好处；
4. 应该尽量使用可以正确存储数据的最小数据类型，因为它们占更少的磁盘，内存和CPU缓存，处理时需要的CPU周期也更少，比如状态、逻辑删除；（可能涉及数据库适配，可以选择“兼容”类型）
5. 隐式转换会导致索引失效（现阶段Mapper中没有使用”定值“，不会出现该情况）。例如：
   select name from user where id = ‘111’;
6. 对于频繁的查询优先考虑使用覆盖索引，但覆盖索引有利有弊（现阶段没有可以使用覆盖索引的需求）；
   参考
7. 代码层面上，禁止在循环体中调用外部接口、调用数据库或者进行数据持久化操作，减少同数据库的交互次数，数据库更适合处理批量操作；
8. 硬件优化。。。

优化建议

1.列表查询均不使用扩展字段（类型数据为text、varchar(1024~)、blob等），如果列表展示内容，查询项，均需要横向扩展到表中；
2.精简存储数据库表的扩展字段字段内容；
综上，之前从exten_info获取的信息需要额外新增字段存储，或者单独存储其他表。

优化效果

优化前：

优化后：
以下是每页20条数据，每页数越少效果越佳：

每页10条数据：

结论

对于 blob，text这样的不定长大字段或者varchar长度过大的字段，innodb 只会存放前 768 字节在数据页中，而剩余的数据则会存储在溢出段中，对于行溢出数据会存储在额外的page里，也就是FIL_PAGE_TYPE_BLOB（溢出页），哪怕只是多了一个字节。因此查询这条记录会增加一次 io 操作，数据量越大，影响效果越明显。InnoDB中，理论上行记录最大长度为8KB（实际上要小于8KB，因为页中还有其他的数据结构占用空间），所以一行的数据长度之和大于8KB时，也会出现行溢出。
此外，MySQL（InnoDB）中存储数据的最小单位是页，一页能存储16KB大小的数据，且最少能存2条数据（参考），所以如果一条记录中长度和比较大的时候，就会根据行格式处理溢出数据：

• Compact和Redundant行格式中，记录的真实数据处会存储该列的一部分数据(前768个字节)， 剩余数据存储在其他页(溢出页)，再使用20个字节存储指向溢出页的地址；
• Dynamic(MySQL默认)和Compressed行格式中，不会在记录的真实数据处存放前768个字节，而是将所有字节都存储在其它页面中，自身只存储一个指向溢出页的地址；略有不同的是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

言而总之，要么存 前缀+溢出页地址，要么只存 溢出页地址。
所以，查询这类数据时需要访问多个物理位置来检索完整的数据行（参考）。如果一条记录在一页中，查询就会很快，也没有IO去磁盘查询，因为页数据是在缓冲池（Buffer pool）中。但是行溢出可以提高表的存储效率和性能，因为它允许存储引擎只存储数据的一部分，而不是整个数据。