本文介绍了使LEFT JOIN查询更有效的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

以下带有LEFT JOIN的查询占用了过多的内存(〜4GB),但是主机仅允许大约120MB用于此过程。

The following query with LEFT JOIN is drawing too much memory (~4GB), but the host only allows about 120MB for this process.

SELECT grades.grade, grades.evaluation_id, evaluations.evaluation_name, evaluations.value, evaluations.maximum FROM grades LEFT JOIN evaluations ON grades.evaluation_id = evaluations.evaluation_id WHERE grades.registrar_id = ?

为成绩创建表语法:

  CREATE TABLE `grades` (
  `grade_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `evaluation_id` int(10) unsigned DEFAULT NULL,
  `registrar_id` int(10) unsigned DEFAULT NULL,
  `grade` float unsigned DEFAULT NULL,
  `entry_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`grade_id`),
  KEY `registrarGrade_key` (`registrar_id`),
  KEY `evaluationKey` (`evaluation_id`),
  KEY `grades_id_index` (`grade_id`),
  KEY `eval_id_index` (`evaluation_id`),
  KEY `grade_index` (`grade`),
  CONSTRAINT `evaluationKey` FOREIGN KEY (`evaluation_id`) REFERENCES `evaluations` (`evaluation_id`),
  CONSTRAINT `registrarGrade_key` FOREIGN KEY (`registrar_id`) REFERENCES `registrar` (`reg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1627 DEFAULT CHARSET=utf8;

评估表:

CREATE TABLE `evaluations` (
      `evaluation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `instance_id` int(11) unsigned DEFAULT NULL,
      `evaluation_col` varchar(255) DEFAULT NULL,
      `evaluation_name` longtext,
      `evaluation_method` enum('class','email','online','lab') DEFAULT NULL,
      `evaluation_deadline` date DEFAULT NULL,
      `maximum` int(11) unsigned DEFAULT NULL,
      `value` float DEFAULT NULL,
      PRIMARY KEY (`evaluation_id`),
      KEY `instanceID_key` (`instance_id`),
      KEY `eval_name_index` (`evaluation_name`(3)),
      KEY `eval_method_index` (`evaluation_method`),
      KEY `eval_deadline_index` (`evaluation_deadline`),
      KEY `maximum` (`maximum`),
      KEY `value_index` (`value`),
      KEY `eval_id_index` (`evaluation_id`),
      CONSTRAINT `instanceID_key` FOREIGN KEY (`instance_id`) REFERENCES `course_instance` (`instance_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8;

提取数据的Php代码:

The Php code to pull the data:

$sql = "SELECT grades.grade, grades.evaluation_id, evaluations.evaluation_name, evaluations.value, evaluations.maximum FROM grades LEFT JOIN evaluations ON grades.evaluation_id = evaluations.evaluation_id WHERE grades.registrar_id = ? AND YEAR(entry_date) = YEAR(CURDATE())";
                    $result = $mysqli->prepare($sql);
                    if($result === FALSE)
                        die($mysqli->error);
                    $result->bind_param('i',$reg_ids[$i]);
                    $result->execute();
                    $result->bind_result($grade, $eval_id, $evalname, $evalval, $max);
                    while($result->fetch()){

以及致命错误消息

有没有办法大大减少此查询的内存负载?

Is there a way to drastically reduce the memory load on this query?

谢谢!


  1. 奇怪的是,更改MySQL查询并不会更改尝试分配的内存量


推荐答案

请为两个表提供 SHOW CREATE TABLE ;我想看看您是否有这样的东西:

Please provide SHOW CREATE TABLE for both tables; I want to see if you have anything like these:

成绩: INDEX(registration_id)

评估: PRIMARY KEY(evaluation_id)

编辑

您现在在两个表中都有多余的索引-可能是由于我的建议。也就是说,您已经拥有有助于查询的两个索引。

You now have redundant indexes in both table -- probably because of my suggestion. That is, you already have both the indexes that would help with the query.

由于您拥有LONGTEXT,并且它正试图分配4GB(LONGTEXT的最大大小),我想这就是问题所在。建议您将该列更改为TEXT(最大64KB)或MEDIUMTEXT(最大16MB)。我以前在PHP中从未见过这种现象,但是后来我很少使用大于TEXT的东西。

Since you have LONGTEXT and it is trying to allocate exactly 4GB, the max size of LONGTEXT, I guess that is the problem. Suggest you ALTER that column to be TEXT (64KB max) or MEDIUMTEXT (16MB max). I have not seen this behavior before in PHP, but then I rarely use anything bigger than TEXT.

这篇关于使LEFT JOIN查询更有效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-25 07:01