MySql查询超慢
我有以下查询超级慢（仅需4-5秒的结果，我想知道是否有人可以看到任何我可以做不同的查询来加快速度？
谢谢！
SELECT
accounts.id AS account_id,
accounts. NAME AS account_name,
accounts.assigned_user_id account_id_owner,
users.user_name AS assigned_user_name,
opportunities_cstm.firstname_c, opportunities_cstm.lastname_c,
opportunities.`name`, TRIM(
Concat(
Ifnull(
opportunities_cstm.firstname_c,
''
),
' ',
Ifnull(
opportunities_cstm.lastname_c,
''
)
)
) AS 'cfull' FROM
opportunities
LEFT JOIN users ON opportunities.assigned_user_id = users.id
LEFT JOIN accounts_opportunities ON opportunities.id = accounts_opportunities.opportunity_id
LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id
LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
WHERE
(
(
opportunities.sales_stage IN (
'Prospecting',
'Appointment Set',
'MeetAndGreet',
'Qualification',
'Needs Analysis',
'Locating Vehicle',
'Demo',
'Trade Evaluation',
'Negotiation',
'Manager T/O',
'Write Up',
'Credit App Submitted',
'Pending Finance',
'Loan Approval',
'Deposit',
'Delayed Decision',
'Sold-Vehicle Ordered',
'Sold-Pending Finance',
'Sold/Pending Delivery',
'Price Quoted',
'Service Pending'
)
)
)
AND (
accounts_opportunities.deleted IS NULL
OR accounts_opportunities.deleted = 0
)
AND (
accounts.deleted IS NULL
OR accounts.deleted = 0
)
AND opportunities.deleted = 0
ORDER BY
opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
以下是来自相同查询的解释：
╔═════════════╦════════════════════════╦════════╦══════════════════════════╦═════════════════════╦═════════╦════════════════════════════════════════════╦═══════╦═════════════════════════════╗ ║ select_type ║ table ║ type ║ possible_keys ║ key ║ key_len ║ ref ║ rows ║ extra ║ ╠═════════════╬════════════════════════╬════════╬══════════════════════════╬═════════════════════╬═════════╬════════════════════════════════════════════╬═══════╬═════════════════════════════╣ ║ simple ║ opportunities ║ range ║ sales_stage, idx_deleted ║ sales_stage ║ 78 ║ null ║ 25161 ║ Using where; Using filesort ║ ║ simple ║ users ║ eq_ref ║ PRIMARY, idx_id_deleted ║ PRIMARY ║ 108 ║ version4.opportunities.assigned_user_id ║ 1 ║ ║ ║ simple ║ accounts_opportunities ║ ref ║ idx_oppid_del_accid ║ idx_oppid_del_accid ║ 111 ║ version4.opportunities.id ║ 1 ║ Using where; Using index ║ ║ simple ║ accounts ║ eq_ref ║ PRIMARY,idx_accnt_id_del ║ PRIMARY ║ 108 ║ version4.accounts_opportunities.account_id ║ 1 ║ Using where ║ ║ simple ║ opportunities_cstm ║ eq_ref ║ PRIMARY ║ PRIMARY ║ 108 ║ version4.opportunities.id ║ 1 ║ ║ ╚═════════════╩════════════════════════╩════════╩══════════════════════════╩═════════════════════╩═════════╩════════════════════════════════════════════╩═══════╩═════════════════════════════╝
我看到两个问题。
首先，您使用两个不同的WHERE (... IS NULL OR ... = 0)条件。 那些无法形容的缓慢。 这是因为索引对查找NULL值没有用处。 如果你可以在这些deleted列中deleted NULL的可能性，也许可以通过声明它们为NOT NULL DEFAULT 0来将这些标准更改为WHERE ... = 0 。 这应该会加速很多事情。 这是因为索引对查找NULL值没有用处。
其次，您正在创建一个伟大的连接结果集，然后对其进行排序以查找最近的项目。
在加入之前，您可以尝试从“机会”表中预先选择项目。 做这样的事情：
SELECT whatever....
FROM (
SELECT *
FROM opportunities
WHERE opportunities.deleted = 0
AND opportunities.sales_stage IN (
'Prospecting',
'Appointment Set', etc etc ...
'Service Pending' )
ORDER BY opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
) opportunities
LEFT JOIN users ON opportunities.assigned_user_id = users.id
...
ORDER BY
opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
这很可能可以通过从连接的右侧删除一堆记录来减少LEFT JOIN操作的基数来加快速度。
我不确定它会有什么改进，但我会尝试的是这样的：
SELECT your_fields
FROM
(SELECT * --or just the fields you need
FROM
opportunities
WHERE
opportunities.deleted = 0 AND
opportunities.sales_stage IN (stage1, stage2, ...)
) opportunities1
LEFT JOIN users ON opportunities1.assigned_user_id = users.id
LEFT JOIN accounts_opportunities ON opportunities1.id = ...etc...
WHERE
(accounts_opportunities.deleted IS NULL
OR accounts_opportunities.deleted = 0)
AND (accounts.deleted IS NULL OR accounts.deleted = 0)
ORDER BY ...etc...
让我知道是否有任何改进（但也可能会变慢）。 另一个想法是使用一个包含你需要过滤的所有阶段的表格：
CREATE TABLE filter_stage (
stage varchar(255));
（255或尝试匹配sales_stage的实际长度，如果您也为此列编制索引，则更好），在其中输入要筛选的所有字符串：
INSERT INTO filter_stage VALUES ('Prospecting'), ('Appointment Set'), ('...'), ...
那么你从你的第一个查询中删除你的IN子句，然后你的FROM变成：
FROM
opportunities INNER JOIN filter_stage
ON opportunities.sales_stage = filter_stage.stage
LEFT JOIN ...
让我知道它是否有效！
不要使用IN。 IN在mysql中运行缓慢，使用Exists
DROP TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable ( sales_stage VARCHAR(50) NOT NULL );
insert into tempTable () values ('Prospecting'),('Appointment Set'),('MeetAndGreet'),...,('Service Pending');
SELECT
...
WHERE EXISTS(select sales_stage from tempTable where opportunities.sales_stage = sales_stage);