优化MYSQL语句

Question

最近，我开始从事一个项目，在这个项目中，我遇到了许多类似于这样的MySQL语句：

SELECT s.Important, c.Interesting
FROM table1 s, table2 c 
WHERE s.IdForTableC = c.id 

或

SELECT q.Important, c.Interesting 
FROM table1 q, table2 p, table3 c 
WHERE q.IdInTableP = p.id 
AND p.IdInTableC = c.id 
AND c.Measure = 1

我对优化MySQL完全陌生，并阅读了一些建议我使用EXPLAIN SELECT的指南。我为上面的查询获得了all类型，第二个查询得到了index类型。我猜这意味着，这两个查询都应该进行优化，但我不知道如何优化。在不改变整个数据库结构的情况下，有可能做到吗？

编辑:第一个解释输出是：

'1', 'SIMPLE', 'c', 'ALL', 'PRIMARY', NULL, NULL, NULL, '467', ''
'1', 'SIMPLE', 's', 'ref', 'IdInTableC', 'IdInTableC', '5', 'table2.c.id', '2', 'Using where'

指标如下：

'table2', '0', 'PRIMARY', '1', 'id', 'A', '14184441', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'otherID', '1', 'otherID', 'A', '92708', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'IdInTableC', '1', 'IdInTableC', 'A', '3048', NULL, NULL, '', 'BTREE', '', ''

Answer 1

查询1

EXPLAIN并不容易遵循。首先，让我解释如何优化这两个查询。

SELECT s.Important, c.Interesting
FROM table1 s, table2 c 
WHERE s.IdForTableC = c.id 

这是编写JOIN的旧方法；请学习新方法：

SELECT s.Important, c.Interesting
    FROM table1 s
    JOIN table2 c  ON s.IdForTableC = c.id 

ON解释了两个表是如何相互关联的。

现在没有通过WHERE子句进行过滤，所以所能做的就是

1. 检查其中一张表的所有行。(没有索引有用)
2. 前排，伸到另一张桌子上。

如果在步骤1中使用s，那么在第2步的c中需要对id进行索引。

如果在步骤1中使用c，那么在第2步的s中需要对IdForTableC进行索引。

惯例是将PRIMARY KEY命名为"id“。一个表必须有一个PRIMARY KEY。我猜c有PRIMARY KEY(id)。这使得从s开始是一次扣篮.但是，优化器决定从c开始--从EXPLAIN中的第1行就可以看出这一点。

"ALL“表示对整个表进行扫描。其中一些人也赞同这一点。在这种情况下，“初级”并不意味着什么。c大约有467行。

为了进入s，它使用了table2.c.id。那里出了点问题。这个语法写着“数据库table2，表c，列id”。是名为"table2“的数据库。呃。

使用了名为IdInTableC的索引。想想看，你

INDEX(IdInTableC)

因此，该名称与该列相同。5可能意味着它是一个NULLable INT。使用NOT NULL，除非您有NULL的目的。JOIN将在s中找到估计的2行。

我猜错了使用c和s的顺序。但是，查询的对称性意味着从一开始可能没有太大的差别。

查询2

同样，让我们使用新的JOIN语法：

SELECT q.Important, c.Interesting 
    FROM table1 q
    JOIN table2 p  ON q.IdInTableP = p.id
    JOIN table3 c  ON p.IdInTableC = c.id
    WHERE  c.Measure = 1

通常最好先“过滤”。唯一的过滤器是c.Measure = 1，所以c可能是第一个表，特别是如果您有

INDEX(Measure)

然后，链必须是:c -> p -> q。

p:  INDEX(IdInTableC)
q:  INDEX(IdInTableP)

对于这些索引，所有三个表都应该使用INDEX，可能是ref或eq_ref。

更多提示

在Extras栏中：

• “使用索引”意味着该指数是“覆盖”。也就是说，所需的所有列都在索引中找到。这是一个轻微的性能提升。
• “使用索引条件”是不同的，而且大部分都超出了您的控制范围；它也意味着额外的提升。
• “使用连接缓冲区”--第二个表已完全加载到内存中；这可能是一个极大的额外提升。
• “使用临时文件，使用文件”--人们错误地害怕这些。有一些性能损失，但通常需要一个或两者。毕竟，ORDER BY意味着一种类型。缺少"filesort“意味着有时可以进行优化。

有关创建索引的基本技巧，请参见http://mysql.rjweb.org/doc.php/index_烹饪书_mysql。

我没有那么多地使用EXPLAIN；我尝试直接从SELECT到最优索引。