优化MySQL中依赖子查询的索引

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (488)

我有一个带有从属子查询的查询,想知道哪些字段应该被索引以获得最大性能?查询(如下)显示了名称中包含“smi”的所有人的所有订单。查询是通过订单和客户表中的共享cust#从客户表中获取客户名称。我几乎坚持这个查询,因为它主要是从php开发工具自动生成的。那么,鉴于这些约束,应该将哪些字段编入索引以获得最佳性能?

SELECT *, (SELECT CONCAT(customers.last, ', ', customers.first)
FROM customers WHERE customers.cust_num = orders.cust_num) AS c_lastname 
FROM orders WHERE (SELECT CONCAT(customers.last, ', ', customers.first) 
FROM customers WHERE customers.cust_num = orders.cust_num) LIKE '%smi%' 
ORDER BY order_id DESC LIMIT 0, 20

EXPLAIN给了我:

"1" "PRIMARY"   "orders"    "index" \N  "PRIMARY"   "4" \N  "20"    "Using where"
"3" "DEPENDENT SUBQUERY"    "customers" "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "orders.cust_num"   "1" ""
"2" "DEPENDENT SUBQUERY"    "customers" "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "orders.cust_num"   "1" ""
提问于
用户回答回答于

我将从重写查询开始:

SELECT o.*, c.last_name
FROM (SELECT cust_name, CONCAT(c.last, ', ', c.first) as c_lastname
      FROM customers c
      HAVING c_lastname LIKE '%smi%'
     ) c JOIN
     orders o
     ON o.cust_num = c.cust_num
ORDER BY order_id DESC
LIMIT 0, 20;

我不认为你可以做很多事情like,除非你真的是这样做liike 'smi%'

但是,您可以orders(cust_num)为此创建索引join。关于外部,你无能为力order by

用户回答回答于

看起来像查询将更直接像这样:

SELECT o.*, CONCAT(c.`last`, ', ', c.`first`) AS cName
FROM orders AS o
INNER JOIN customers AS c ON o.cust_num = c.cust_num
WHERE c.`last` LIKE '%smi%' OR c.`first` LIKE '%smi%'
ORDER BY order_id DESC 
LIMIT 0, 20

或者,类似于戈登的:

SELECT o.*, c.Name
FROM orders AS o
INNER JOIN (
    SELECT cust_num, CONCAT(`last`, ', ', `first`) AS Name 
    FROM customers
    WHERE `last` LIKE '%smi%' OR `first` LIKE '%smi%'
) AS c ON o.cust_num = c.cust_num
ORDER BY order_id DESC 
LIMIT 0, 20;

第一个避免子查询,但后者将减少对同一客户的订单的冗余CONCAT操作; 表现更好的可能取决于表中的实际数据。

......为了回应戈登,关于“%smi%”,你无能为力; MySQL不会从中间索引字符串,因此领先的通配符基本上消除了索引使用的可能性。

扫码关注云+社区

领取腾讯云代金券