sql - MySQL数据库挂起的简单Select语句

Question

我在非常 的大表上有一个非常 简单的sql select 语句，它是非规范化的。 (根本不是我的设计，我只是在尝试优化的同时试图说服业主重新设计)

基本上，声明是这样的:

SELECT FirstName, LastName, FullName, State
FROM Activity
WHERE (FirstName=@name OR LastName=@name OR FullName=@name)
AND State=@state;

现在，FirstName、LastName、FullName 和 State 都被索引为 BTree，但没有前缀 - 整个列都被索引了。州列是 2 个字母的州代码。

我发现的是:

1. 当@name = 'John Smith' 且@state = '%' 时，搜索速度非常快并立即产生结果。
2. 当@name = 'John Smith' 且@state = 'FL' 时，搜索需要 5 分钟(这通常意味着网络服务超时...)
3. 当我删除 FirstName 和 LastName 比较并仅使用 FullName 和 State 时，上述两种情况都非常快速。
4. 当我替换 FirstName、LastName、FullName 和 State 搜索，但对每个搜索使用 LIKE 时，它对 @name='John Smith%' 和 @state='%' 运行速度很快，但对 @name=' 运行速度慢John Smith%' 和@state='FL'
5. 当我针对“John Sm%”和@state='FL' 进行搜索时，搜索会立即找到结果
6. 当我搜索 'John Smi%' 和 @state='FL' 时，搜索需要 5 分钟。

现在，重申一下 - 该表未规范化。 John Smith 和许多其他用户一样多次出现，因为没有引用某种形式的用户/人员表。我不确定单个用户可能出现多少次，但表本身有 9000 万条记录。再一次，不是我的设计...

我想知道的是 - 尽管此设计存在很多问题，但导致此特定问题的原因是什么。

我的猜测是索引树太大，遍历它们需要很长时间。 (名字、姓氏、全名)

无论如何，我感谢任何人对此的帮助。就像我说的，我正在努力说服他们进行重新设计，但与此同时，如果有人能帮助我找出确切的问题所在，那就太好了。

更新

根据要求，以下是运行解释后的详细信息:

id: 1, select type: 'SIMPLE', table: 'activity', type: 'ref', possible keys: 'IDX_LastName,IDX_FirstName,IDX_FullName,IDX_State', key: 'IDX_State', key_len: '3', ref: 'const', rows: 7227364, extra: 'Using where'

Answer 1

我们需要知道表是如何定义的，所以给我们结果

show create table Activity

会有帮助。

从它的声音来看，State 列有点问题 - 它是否已编入索引？

另一个好工具是 EXPLAIN 命令。

使用

EXPLAIN     SELECT         FirstName, LastName, FullName, State     FROM Activity     Where (FirstName='John Smith'            OR LastName='John Smith'            OR FullName='John Smith')     AND State='FL';

并将结果与​​查询的其他变体的 EXPLAIN 输出进行比较。