mysql - 优化一个简单的 MySQL 查询

Question

我有下表:

idTelephone int(11)
name varchar(255)
nameUrl varchar(255)
address text
city varchar(255)
cityUrl varchar(255)
cellphone int(1)

具有以下指标:

PRIMARY idTelephone
nameUrl BTREE
cityUrl BTREE
cityUrlNameUrl  BTREE   cityUrl, nameUrl
search  FULLTEXT name, nameUrl, address, city
name    FULLTEXT

在我的网站中有一个页面，我必须在其中显示所有带分页的电话号码。所以我使用以下查询(针对页面 26994):

SELECT * FROM dir_Telephone WHERE 1=1 ORDER BY nameUrl LIMIT 269930, 10

但它真的很慢...您如何优化像这样的简单查询？

EXPLAIN 的输出是:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  dir_Telephone   ALL     NULL            NULL       NULL     NULL   269965   Using filesort

如果没有 WHERE 语句，结果是:

EXPLAIN (SELECT * FROM dir_Telephone ORDER BY nameUrl LIMIT 269930 , 10);

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  dir_Telephone   ALL        NULL         NULL    NULL        NULL   269965   Using filesort

创建语句是:

CREATE TABLE IF NOT EXISTS `dir_Telephone` (
  `idTelephone` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `nameUrl` varchar(255) DEFAULT NULL,
  `address` text,
  `city` varchar(255) DEFAULT NULL,
  `cityUrl` varchar(255) DEFAULT NULL,
  `cellphone` int(1) unsigned DEFAULT NULL,
  `htmlPublic` text,
  `htmlComplete` text,
  PRIMARY KEY (`idTelephone`),
  KEY `nameUrl` (`nameUrl`),
  KEY `cityUrl` (`cityUrl`),
  KEY `cityUrlNameUrl` (`cityUrl`,`nameUrl`),
  FULLTEXT KEY `search` (`name`,`nameUrl`,`address`,`city`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

您要做的是向表中添加一个行号列。一种方法是定义一个新表，就像旧表一样，但有一个自动递增的列。然后将数据加载到其中。或者，您可以执行以下操作。

alter table dir_Telephone add rownum integer;

update dir_telephone cross join
       (select @rn := 0) var
    set rownum = (@rn := @rn + 1)
    order by nameUrl;

create index dir_Telephone_rownum on dir_Telephone(rownum);

然后，当您运行查询时，不要在 limit 子句中使用偏移量。使用 where 子句:

SELECT *
FROM dir_Telephone
WHERE 1=1 AND rownum >= 269930
ORDER BY rownum
LIMIT 10;