php - 优化此查询，从具有 500.000 个用户和一个条件的 MySQL 数据库中检索用户

Question

假设我有下一个 MySQL 数据库500.000 行:

users
{ 
    id       - int, 
    name     - varchar(32), 
    verified - tinyint(1)
}

primary { id }
index   { verified }

我需要获取最后 20 个未验证的用户，所以我使用下一个查询:

SELECT * FROM users WHERE verified != 1 ORDER BY id DESC LIMIT 20

但是需要 1.2 秒才能完成。

如何优化它？或者在 php 中用其他方式得到相同的结果。

[编辑]

ID 是主索引，VERIFIED 也是索引

[编辑 2]

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Identificador del usuario',
  `login` varchar(32) NOT NULL COMMENT 'Login para entrar',
  `password` varchar(32) NOT NULL COMMENT 'Password para entrar',
  `email` varchar(384) NOT NULL COMMENT 'Email del usuario',
  `group_id` int(10) unsigned default NULL,
  `display_name` varchar(64) NOT NULL COMMENT 'Nombre para mostrar',
  `email_verified` tinyint(3) unsigned default '0' COMMENT 'Email verificado?',
  `banned` tinyint(3) unsigned default '0' COMMENT 'Baneado?',
  `admin` tinyint(3) unsigned default '0' COMMENT 'Es un super administrador del sitio?',
  `registered` int(10) unsigned NOT NULL COMMENT 'Fecha del registro',
  PRIMARY KEY  (`id`),
  KEY `login` (`login`),
  KEY `password` (`password`),
  KEY `email` (`email`(333)),
  KEY `group_id` (`group_id`),
  KEY `email_verified` (`email_verified`),
  KEY `banned` (`banned`),
  KEY `admin` (`admin`),
  KEY `registered` (`registered`)
) ENGINE=MyISAM AUTO_INCREMENT=500002 DEFAULT CHARSET=utf8;

[编辑 3]

EXPLAIN(SELECT id FROM users WHERE email_verified != 1 ORDER BY id DESC LIMIT 20)

是

id: 1   
select_type: SIMPLE 
table: users    
type: range 
possible_keys: email_verified   
key: email_verified 
key_len: 2      
ref:
rows: 345195    
Extra: Using where; Using filesort

以及查询的配置文件:

Status  Duration
(initialization)    0.0000307
Opening tables  0.000003
System lock 0.0000017
Table lock  0.0000042
init    0.000017
optimizing  0.0000077
statistics  0.000097
preparing   0.000054
executing   0.0000007
Sorting result  1.2321507
Sending data    0.000272
end 0.000004
query end   0.0000025
freeing items   0.0000099
closing tables  0.0000025
logging slow query  0.0000005

Answer 1

您需要一个包含 id 和 email_verfied 的索引>

KEY `foo` (`id`,`email_verified`)

然后 EXPLAIN(SELECT id FROM users WHERE email_verified != 1 ORDER BY id DESC LIMIT 20) 打印

+----+-------------+-------+-------+----------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | index | email_verified | Index 4 | 6       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+----------------+---------+---------+------+------+--------------------------+

特别是慢速的Using filesort 没了。