mysql - 查询结果耗时太长。有没有更好的方法来编写这个 MySQL 查询？

Question

我正在尝试优化一个mysql查询，该查询运行良好，但花费的时间太长。我的库存表有近300,000 条记录(还不错)。我不确定使用子查询或join或附加索引是否会加快我的结果。我确实在学生表和库存表中都索引了 District_id 列。

基本上，下面的查询会提取教师名册中所有学生的所有库存。因此，它首先必须搜索学生表以查找教师名册中的学生，然后必须搜索每个学生的库存表。因此，如果老师有 30 多个学生，则需要对库存进行大量搜索，并且每个学生可以拥有 30 多个库存。任何意见将是有益的!

SELECT  inventory.inventory_id, items.title, items.isbn, items.item_num,
        items.price, conditions.condition_name, inventory.check_out,
        inventory.check_in, inventory.student_id, inventory.teacher_id
    FROM  inventory, conditions, items, students
    WHERE  students.teacher_id = '$teacher_id'
      AND  students.district_id = $district_id
      AND  inventory.student_id = students.s_number
      AND  inventory.district_id = $district_id
      AND  inventory.item_id = items.item_id
      AND  items.consumable !=1
      AND  conditions.condition_id = inventory.condition_id
    ORDER BY  inventory.student_id, inventory.inventory_id

这是表结构:

CREATE TABLE `inventory` (
  `id` int(11) NOT NULL,
  `inventory_id` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `item_id` int(6) NOT NULL DEFAULT '0',
  `district_id` int(2) NOT NULL DEFAULT '0',
  `condition_id` int(1) NOT NULL DEFAULT '0',
  `check_out` date NOT NULL DEFAULT '0000-00-00',
  `check_in` date NOT NULL DEFAULT '0000-00-00',
  `student_id` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `teacher_id` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `acquisition_date` date NOT NULL DEFAULT '0000-00-00',
  `notes` text CHARACTER SET utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

首先，您重写它以使用显式 JOIN:

SELECT inventory.inventory_id, 
       items.title, items.isbn, items.item_num, items.price, 
       conditions.condition_name,
       inventory.check_out,  inventory.check_in, 
       inventory.student_id, inventory.teacher_id  
FROM inventory
  JOIN conditions ON (conditions.condition_id = inventory.condition_id)
  JOIN items ON (inventory.item_id = items.item_id AND items.consumable != 1)
  JOIN students ON (inventory.student_id = students.s_number) 
WHERE students.teacher_id = '$teacher_id' 
  AND students.district_id = $district_id
  AND inventory.district_id = $district_id 
ORDER BY inventory.student_id, inventory.inventory_id

然后检查 JOIN。例如这个:

  JOIN items ON (inventory.item_id = items.item_id AND items.consumable != 1)

意味着需要扫描items表的item_id和consumable，它可能是一个常量。如果可能的话，最好不要使用负面条件。但至少您可以在 item_id 上对项目建立索引(除非它已经是主键，这很可能)。如果消费品可以假设值为 0、1、2、3，那么您可以:

  JOIN items ON (inventory.item_id = items.item_id AND items.consumable IN (0, 2, 3))

并使用 CREATE INDEX 在消费品上添加索引。

您可能会注意到，库存中的一些列始终在其他 JOIN 中使用，并且还有一些恒定的约束。

所以另一个有用的索引可能是

 CREATE INDEX ... ON inventory(district_id, student_id, item_id, condition_id)

另一个有用的索引是

ON 学生(teacher_id、district_id、student_id、s_number)

它允许立即限制所涉及学生的 WHERE，并检索 JOIN 所需的信息，而无需加载表，只需使用索引。