如何优化这个Mysql简单查询 [英] How to optimze this Mysql simple query

问题描述

此查询耗时 450 毫秒

This query is taking 450ms

SELECT `u`.`user_id`, `c`.`company`
FROM `users` AS `u`
LEFT JOIN `companies` AS `c` ON `c`.`user_id` = `u`.`user_id`
WHERE `u`.`user_id` = 'search_term' 
  OR `u`.`lname` LIKE 'search_term%'    
  OR `u`.`email` LIKE 'search_term%'        
  OR `c`.`company` LIKE 'search_termeo%'

表格:

• 用户(260250 行)
• 公司(570 行)

结构:

- 用户:

   CREATE TABLE `users` (
    `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `region_id` int(10) unsigned NOT NULL,
    `fname` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `lname` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT '0',
     PRIMARY KEY (`user_id`),
     KEY `idx_lname` (`lname`),
     KEY `idx_email` (`email`),
     UNIQUE KEY `unq_region_id_email` (`region_id`, `email`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

- 公司:

 CREATE TABLE `companies` (
  `user_id` int(10) unsigned NOT NULL,
  `company` varchar(35) COLLATE utf8mb4_unicode_ci NOT NULL,
  `vat_num` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`user_id`),
   KEY `idx_company` (`company`) USING BTREE,
   CONSTRAINT `users_companies_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

解释查询的结果

我认为 450 毫秒 对于这样的查询和如此少量的数据来说太多了我想知道是否有优化的地方

I think 450ms is too much for such query and such little amount of data and I want to know if there is somthing to optimize

查询在 iMac 2017, 3,4 GHz, 16Go 下在 quereious v3 中运行

query run in querious v3 under iMac 2017, 3,4 GHz, 16Go

Mysql: 5.7.26 on MAMP pro v5.7

Mysql: 5.7.26 on MAMP pro v5.7

推荐答案

OR 条件不在同一字段或基于范围时(例如 <, >>, LIKE) 确实降低了 MySQL 利用索引的能力；您可以通过将查询分解为单独的更简单的查询来重组查询，然后您可以进行联合.像这样分离它允许 MySQL 利用 UNIONs 中每个查询的不同索引

OR conditions when not on the same field or range based (such as <, >, LIKE) really decrease MySQL's ability to take advantage of indexes; you can restructure queries by breaking them down into separate simpler ones that you can then UNION. Separating it out like this allows MySQL to take advantage of a different index of each query within the UNIONs

SELECT `u`.`user_id`, `c`.`company`
FROM `users` AS `u` LEFT JOIN `companies` AS `c` ON `c`.`user_id` = `u`.`user_id`
WHERE `u`.`user_id` = 'search_term' 
UNION DISTINCT 
SELECT `u`.`user_id`, `c`.`company`
FROM `users` AS `u` LEFT JOIN `companies` AS `c` ON `c`.`user_id` = `u`.`user_id`
WHERE `u`.`lname` LIKE 'search_term%'    
UNION DISTINCT 
SELECT `u`.`user_id`, `c`.`company`
FROM `users` AS `u` LEFT JOIN `companies` AS `c` ON `c`.`user_id` = `u`.`user_id`
WHERE `u`.`email` LIKE 'search_term%'
UNION DISTINCT 
SELECT `u`.`user_id`, `c`.`company`
FROM `users` AS `u` INNER JOIN `companies` AS `c` ON `c`.`user_id` = `u`.`user_id`
WHERE `c`.`company` LIKE 'search_termeo%'
;

另外，请注意，我将最后一个的 JOIN 更改为 INNER，因为 LEFT JOIN 右侧表上的任何条件(不是没有该表中的匹配项")基本上都是 INNER JOIN.

Also, note that I changed the last one's JOIN to an INNER since any condition on the right-hand table of a LEFT JOIN (that isn't "without a match from that table") is basically an INNER JOIN anyway.

UNION DISTINCT 用于防止重复满足多个条件的记录，但是...如果 companies.company 不是唯一的(即公司 id 1 称为"Blah"和公司 ID 12 也称为Blah")，那么它们也将合并到它们不在原始查询中的位置；如果这是一个潜在的问题，可以通过在每个 SELECT 中包含 company_id 来补救.

UNION DISTINCT is used to prevent records that satisfied multiple conditions from being repeated, however... if companies.company is not unique (i.e. company id 1 called "Blah" and company id 12 also called "Blah") then those will also be merged where they would not be in your original query; if it is a potential issue, that can be remedied by also including company_id in each SELECT.

这篇关于如何优化这个Mysql简单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！