如何优化缓慢的Mysql SELECT查询？

Question

如何优化缓慢的Mysql SELECT查询？

3

这是我的查询：

SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col1= 'val' and col7='Y' and col16='203' OR col16='201' order by col4 desc

我不知道是什么让这个查询变慢了，无论是order by还是where子句...

已经正确添加了索引，但仍然很慢。

我正在使用JSP + STRUTS + EJB2.0 + MYSQL。 table1有超过50万条记录。如何优化查询或者有哪些其他可能性可以提高执行速度？

表结构：

col1                varchar(20) NO  PRI 
col2                varchar(50) NO  PRI 
col3                varchar(50) YES     [NULL]
col4                varchar(20) YES     [NULL]
col5                varchar(6)  YES     [NULL]
col6                varchar(20) YES     [NULL]
col7                varchar(1)  YES     [NULL]
col8               mediumtext   YES     [NULL]
col9              mediumtext    YES     [NULL]
col10             mediumtext    YES     [NULL]
col11              mediumtext   YES     [NULL]
col12              mediumtext   YES     [NULL]
col13               mediumtext  YES     [NULL]
col14             mediumtext    YES     [NULL]
col15               mediumtext  YES     [NULL]
col16               varchar(20) YES     [NULL]
col17            varchar(50)    YES     [NULL]
col18             varchar(5)    YES     [NULL]
col19              varchar(5)   YES     [NULL]
col20               varchar(5)  YES     [NULL]
col21                  text YES     [NULL]
col 22                  text    YES     [NULL]
col23              text YES     [NULL]
col24              varchar(5)   YES     [NULL]
col25              int(11)  YES     [NULL]

- user_vs

1

表定义，解释计划 - Mihai

这个查询应该返回多少行？使您的查询变慢的是 order by，而不是 where 条件。 - Truche

它返回满足条件的所有记录，从五十万条记录中筛选。 - user_vs

通常有多少条记录符合条件？ - dnoeth

如果您取得了500000条记录，那么大约有10000条记录满足条件col1='val'，在这10000条记录中，有7000条记录满足条件col7='Y'，在这7000条记录中，有1000条记录满足条件col16='209'，在这6000条记录中，有2000条记录满足条件col16='201'。因此，最终输出的记录数大约为3000条。 - user_vs

3个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Atsby · Answer 1

我不知道是order by还是conditions导致这个查询变慢了。

如果这些记录只是一些常规的姓名、电话号码、电子邮件之类的东西（而不是文档），那么50万条记录应该可以放在内存中。所以，如果速度非常慢，那么肯定出了什么问题。

即使正确地添加了索引，速度仍然很慢。

哪些列被索引了？你需要按照能够最有效地过滤的列进行索引。例如，如果col2是一个是或否的问题的答案，那么对它进行索引是没有帮助的。

- Abhik Chakraborty · Answer 2

您的查询 -

SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18  
FROM table1 
where 
col1= 'val' 
and col2='Y' 
and (col3='203' OR col3='201')
order by col4 desc

这将首先需要一个覆盖索引

alter table table1 add index search_idx(col1,col2,col3) ;

现在要解决order by子句，您还需要对其进行索引

alter table table1 add index col4_idx(col4) ;

请注意，or条件是致命的，从性能角度考虑，最好将其转换为union all。

SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18  
FROM table1 
where 
col1= 'val' 
and col2='Y' 
and col3='203'
union all
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18  
FROM table1 
where 
col1= 'val' 
and col2='Y' 
and col3='201'
order by col4 desc

您可以使用explain select来分析上述查询的健康状况。

在应用索引之前，请确保备份表格。

https://dev.mysql.com/doc/refman/5.0/en/select-optimization.html

https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

- dnoeth · Answer 3

你确定你的 WHERE 条件正确吗？

AND 优先于 OR，因此

where col1= 'val' and col2='Y' and col3='203' OR col3='201'

等同于

where (col1= 'val' and col2='Y' and col3='203') OR (col3='201')

但是你可能想要的是

where col1= 'val' and col2='Y' and (col3='203' OR col3='201')

编辑： 根据您的评论，我的假设是错误的，您实际上想要原始结果（那么我建议添加括号以使其更明确）。在这种情况下，唯一可能的索引是col3（如果足够有选择性）。

我不知道mysql的优化器是否足够聪明，可以使用同一索引两次访问表，如果不能，则需要联合两个查询：

SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col1= 'val' AND col2='Y' AND col3='203'

UNION ALL

SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col3='201'
ORDER BY col4 DESC

编辑2：在问题的编辑后，列名是错误的（col2 应该是 col7，col3 应该是 col16）