为什么建议不要用mysql的Text和Blob类型
2020-09-26 本文已影响0人
滨岩
如何优化 Mysql 中 text 和 blob 类型：
什么是行溢出数据？
- InnoDB 会将一些大对象数据存放在数据页之外的 BLOB 页中，然后在查询时根据指针去对应的 BLOB 页中查询。
- 要不要将数据放在 BLOB 页中，取决于当前页中是否可以存放下至少两行数据，对于默认是 16 KB 大小的页，这个阈值长度是 8098，大于该值的会存放在 BLOB 页中。
- BLOB 不只存放 text 和 blob 类型，varchar 类型的数据也有可能被存放在 BLOB 页中，而 blob 类型和 text 类型的数据也有可能不被存放在 BLOB 页中。
- 对于 Compact 和 Redundant 行存储格式存放的数据，采用的是部分行溢出存储，前 768 字节还是会存放在当前数据页中的。
- 对于 Compressed 和 Dynamic 行存储格式存放的数据，采用的完全行溢出存储，只用 20 个字节存放指针，其余所有数据都放在行溢出数据中。
为什么要尽量少使用 text 和 blob 类型？
- 首先对于 text 和 blob 类型，在遇到使用临时表的情况时，无法使用内存临时表，只能在磁盘上创建临时表。
- 对于行溢出数据，InnoDB 一次只会为一个列分配一页的空间，但是当该列超过 32 个页后会一次性分配 64 个页面，存储空间有一定的浪费。
- 行溢出数据禁用了自适应哈希索引，如果作为 where 条件时必须完整的比较整个列。
- 对于 text 和 blob 字段进行排序时，只能使用部分前缀进行排序，默认是 1024 字节，可以通过 max_sort_length 进行设置。
- 数据量太大，会导致 InnoDB 每个数据页中存放的行数减少，从而影响对页面的缓存。
- 如果存放在行溢出数据中，每次会根据指针去对应的溢出页进行查询，增加页面访问次数，而且每次查询都是随机 IO，text 字段越多查询次数越多。
如何优化查询？
- 如果有许多大字段，可以考虑合并这些字段到一个字段，存储一个大的 200kb 比存储 20 个 10kb 更高效，检查随机页面访问次数。
- 查询时尽量避免对大字段查询，尤其是获取列表时，杜绝使用 select * 查询。
- 可以考虑将大字段专门放在另外一张表中，只有在需要时再关联查询，增加 InnoDB 的当前表缓存命中率。
- 如果只需要获取大字段的部分数据，可以使用 SUBSTRING( ) 函数，这样可以避免使用磁盘临时表。
- 如果必须使用到磁盘临时表，可以考虑将磁盘临时表指向在基于内存的文件系统中，可以通过修改 tmpdir 参数实现。
- 必要时可以考虑对大字段进行压缩后再存储到表中。
- 尽量不要使用大字段作为 where 中的查询条件。
如何正确使用索引
- 创建索引时尽量选择 Cardinality 值比较大的字段，你可以通过 explain 观察自己创建的索引到底有没有被使用
- order by 中的排序的列如果建了索引，则可以使用直接索引进行排序，优化性能
- 在使用索引时对应的索引列必须独立，不能是表达式的一部分也不能是函数的参数，否则不能使用索引：
-- 虽然 id 上建立了索引，但是无法使用索引优化
select id from user where id + 1 =5;
- 当服务器出现多个列做 AND 操作查询时，通常需要建了一个多列索引，而不是多个独立的单列索引
- 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的，因为可以很快的过滤出需要的行
- 如果索引包含了需要查询的所有字段值，那么就是可以使用覆盖索引查询，只需要读取索引，极大地减少了数据访问量，在 EXPLAIN 分析的 Extra 字段中可以看到 “Using index” 信息
- 如果查询中某个列是范围查询，那么其右边的所有列将无法使用索引优化，索引尽量将范围条件放在右边或者使用多个等值条件来代替范围查询
- 查询时尽量不要返回多余的列，第一可以减少网络流量，第二增加使用覆盖索引的可能性
- 多列索引时只有当索引的列和 ORDER BY 子句的顺序完全一致且所有列的排序方向一致时才能使用索引做排序
- 不要创建冗余的索引，Mysql 不仅需要单独维护索引列，并且在优化器查询时也需要逐个索引进行过滤，会影响性能，下面是创建冗余索引的几个例子：
- 创建了索引（A，B）再创建索引（A），那后者便是冗余索引
- 创建索引扩展为（A，ID），其中 ID 是主键，对于 InnoDB 来说主键已经包含在二级索引中了，所以这也是冗余的
- 有一些索引可能服务器永远都不会用到，建议考虑删除，在 percona 版本或 marida 中可以通过 information_schea.index_statistics 查看得到索引的使用情况，在官方版本中 可以使用 performance_schema.table_io_waits_summary_by_index_usage 查看索引使用情况
