安全可靠地优化MySQL选择不同的顺序

Question

我有一个有问题的查询，我知道如何写得更快，但从技术上讲，SQL是无效的，它不能保证将来工作正常。

原始的缓慢查询如下所示：

SELECT sql_no_cache DISTINCT r.field_1 value
FROM table_middle m
JOIN table_right r on r.id = m.id
WHERE ((r.field_1) IS NOT NULL) 
AND (m.kind IN ('partial')) 
ORDER BY r.field_1 
LIMIT 26

这大约需要37秒。解释产出：

+----+-------------+-------+--------+-----------------------+---------------+---------+---------+-----------------------------------------------------------+
| id | select_type | table | type   | possible_keys         | key           | key_len | rows    | Extra                                                     |
+----+-------------+-------+--------+-----------------------+---------------+---------+---------+-----------------------------------------------------------+
|  1 | SIMPLE      | r     | range  | PRIMARY,index_field_1 | index_field_1 | 9       | 1544595 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | m     | eq_ref | PRIMARY,index_kind    | PRIMARY       | 4       |       1 | Using where; Distinct                                     |
+----+-------------+-------+--------+-----------------------+---------------+---------+---------+-----------------------------------------------------------+

更快的版本如下所示；将order by子句向下推到子查询中，该子查询将被连接起来，而distinct则会受到限制：

SELECT sql_no_cache DISTINCT value 
FROM (
  SELECT r.field_1 value
  FROM table_middle m
  JOIN table_right r ON r.id = m.id
  WHERE ((r.field_1) IS NOT NULL) 
  AND (m.kind IN ('partial')) 
  ORDER BY r.field_1 
) t
LIMIT 26

这大约需要2.7秒。解释产出：

+----+-------------+------------+--------+-----------------------+------------+---------+---------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys         | key        | key_len | rows    | Extra                                                     |
+----+-------------+------------+--------+-----------------------+------------+---------+---------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                  | NULL       | NULL    | 1346348 | Using temporary                                           |
|  2 | DERIVED     | m          | ref    | PRIMARY,index_kind    | index_kind | 99      | 1539558 | Using where; Using index; Using temporary; Using filesort |
|  2 | DERIVED     | r          | eq_ref | PRIMARY,index_field_1 | PRIMARY    | 4       |       1 | Using where                                               |
+----+-------------+------------+--------+-----------------------+------------+---------+---------+-----------------------------------------------------------+

table_right和table_middle中有300万行，所有提到的列都分别进行索引。应该将查询理解为具有任意的where子句--它是动态生成的。不能以任何方式重写查询，从而阻止where子句被轻松替换，同样，索引也不能更改-- MySQL不支持足够多的索引来满足潜在的筛选字段组合的数量。

以前有人见过这个问题吗?具体来说，选择/ distinct / order / good性能很差吗?是否有另一种方法可以以不依赖于未指定的实现行为的良好性能编写相同的查询呢？

(例如，AFAIK MariaDB在子查询中忽略order by，因为它不应该在逻辑上影响查询的集合论语义。)

对于更令人怀疑的人

下面是如何为自己创建一个数据库版本的方法！这应该输出一个SQL脚本，您可以在mysql命令行客户机上运行：

#!/usr/bin/env ruby
puts "create database testy;"
puts "use testy;"
puts "create table table_right(id int(11) not null primary key, field_0 int(11), field_1 int(11), field_2 int(11));"
puts "create table table_middle(id int(11) not null primary key, field_0 int(11), field_1 int(11), field_2 int(11));"
puts "begin;"
3_000_000.times do |x|
  puts "insert into table_middle values (#{x},#{x*10},#{x*100},#{x*1000});"
  puts "insert into table_right values (#{x},#{x*10},#{x*100},#{x*1000});"
end
puts "commit;"

索引对于复制效果并不重要。上面的脚本是未经测试的；它近似于我在手工复制问题时遇到的一个撬式会话。

将m.kind in ('partial')替换为m.field_1 > 0或类似的东西。观察两种不同技术在性能上的巨大差异，以及如何保留排序语义(使用MySQL 5.5进行测试)。当然，语义的不可靠性正是我提出这个问题的原因。

Answer 1

请提供SHOW CREATE TABLE。如果没有这一点，我猜这些内容是缺失的，也许是有用的：

m:  (kind, id)
r:  (field_1, id)

您可以关闭MariaDB忽略子查询的ORDER BY。