主页 / dba / 问题 / 58527
Accepted
matt
Asked: 2014-02-10 03:06:09 +0800 CST

MySQL，主键检索时磁盘表上的临时表？

  • 772

MySQL 5.5.24，Windows 7 64 位。

我有在三列上定义主键的 InnoDB 表：

CREATE TABLE `common_descriptions` (
  `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `object_type` varchar(32) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `lang_code` char(2) NOT NULL DEFAULT '',
  `object` varchar(12) NOT NULL DEFAULT '',
  `object_holder` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`object_id`,`lang_code`,`object`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我正在执行相当简单的查询（在我看来）应该使用索引返回行

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

但它创建了 10 个临时表，其中 3 个在磁盘临时表上（通过监控多次确认show status like '%tmp%';）。objectcolumn 似乎触发了临时表的创建。当我用 (mediumtext) 替换object(varchar(12)) 时，description不会创建临时表。explain extended以上查询：

mysql> explain extended select SQL_NO_CACHE object_id, lang_code, object from co
mmon_descriptions limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: common_descriptions
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 47
          ref: NULL
         rows: 49765
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

tmp_table_size和都max_heap_table_size设置为16M。

在阅读了关于临时表的文档（http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html）后，我没有看到任何关于为什么要创建临时表的很好的解释案子。为什么会这样？

mysql innodb

1 个回答

  1. Best Answer

    观察 #1

    您需要查看查询优化器看到的内容以及它正在解释的内容

    一、仔细看query

    select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

    没有 WHERE 子句。它正在为无限结果集生成一个临时表。

    在查询优化器看来，LIMIT 的优先级较低。为什么 ？

    根据MySQL 文档SELECT

    HAVING 子句几乎最后应用，就在项目发送到客户端之前，没有优化。（在 HAVING 之后应用 LIMIT。）

    同一页的另一部分内容如下：

    使用一个参数，该值指定从结果集开头返回的行数：

    从 tbl LIMIT 5 中选择 *；# 检索前 5 行

    也就是说，LIMIT row_count 相当于 LIMIT 0, row_count。

    使用子句LIMIT后，SELECT必须先生成结果。您的结果没有边界（没有 WHERE 子句）。之后应用 LIMIT。

    这解释了临时表的创建。

    观察#2

    您在问题中提到了以下内容

    当我用描述 (mediumtext) 替换对象 (varchar(12)) 时，未创建临时表

    根据临时表上的 MySQL 文档（您的链接）

    有些情况会阻止使用内存中的临时表，在这种情况下，服务器会改用磁盘上的表：

    • 表中存在 BLOB 或 TEXT 列
    • GROUP BY 或 DISTINCT 子句中存在任何大于 512 字节的字符串列
    • 如果使用 UNION 或 UNION ALL，SELECT 列表中存在最大长度大于 512（二进制字符串为字节，非二进制字符串为字符）的任何字符串列
    • SHOW COLUMNS 和 DESCRIBE 语句使用 BLOB 作为某些列的类型，因此用于结果的临时表是一个磁盘表。

    问题与结果集和第一个要点 ( ) 有很大关系，Presence of a BLOB or TEXT column in the table因为 TEXT 字段不能占用内存中的临时表，它直接进入磁盘。这只是冰山一角。

    当你跑的时候

    select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

    您基本上阅读了整行。怎么来的 ？

    common_descriptions是用 InnoDB 存储引擎定义的

    InnoDB 表的主键位于称为聚簇索引的逻辑结构中（内部称为gen_clust_index，只有在运行时才能看到SHOW ENGINE INNOD STATUS\G）

    该文件说

    通过聚簇索引访问一行很快，因为行数据位于索引搜索引导的同一页上。如果表很大，与使用与索引记录不同的页来存储行数据的存储组织相比，聚集索引架构通常可以节省磁盘 I/O 操作。（例如，MyISAM 使用一个文件作为数据行，另一个文件作为索引记录

    由于您访问了 PRIMARY KEY ( object_id, lang_code, object) 的每一列，因此在内部您实际上是在获取每一行（包括description），这解释了为什么 PRIMARY KEY 检索仍然会在磁盘上产生一个临时表。当您更改 SELECT 列表 ( object_id, lang_code, description) 时，事情显然发生了变化。一点。不会创建内存中的临时表。为什么？

    MySQL 文档是这样说的：

    当服务器创建内部临时表（在内存中或磁盘上）时，它会增加 Created_tmp_tables 状态变量。如果服务器在磁盘上创建表（最初或通过转换内存表），它会增加 Created_tmp_disk_tables 状态变量。

    因此，Created_tmp_tables不会因为描述为而增加mediumtext。它直接进入磁盘。Created_tmp_disk_tables反而会增加。

    概括

    涉及的因素很多

    • LIMIT 再次使用了没有 WHERE 子句的结果集
    • InnoDB 聚簇索引
    • description存在MEDIUMTEXT

    建议

    尝试添加一个ORDER BY

    select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
order by object_id, lang_code, object
limit 1;

    我在针对单个列 PRIMARY KEY 完成时看到了它。

    试试看 ！！！

    • 2

相关问题