基本的MySQL查询非常慢。我的表索引是否正确？
我目前正在优化一个非常大的MySQL数据库，我正在围绕这个数据库构建一个基于web的查询接口。
数据库将有两个表。第一张表已经被优化(我相信)，它包含了美国950个气象数据观测站的信息：
Description for: stations (950 records)
+-----------+------------+--------+-------+---------+----------------+
|Field |Type | NULL |KEY | Default | Extra |
+-----------+------------+--------+-------+---------+----------------+
|id |INT |NO |PRI |NULL |auto_increment |
|stationID |char(4) |NO |PRI |NULL | |
|name |varchar(16) |YES | |NULL | |
|state |char(2) |YES |MUL |NULL | |
|lat |float(6,2) |YES | |NULL | |
|lon |float(6,2) |YES | |NULL | |
|elev |INT |YES | |NULL | |
+-----------+------------+--------+-------+---------+----------------+另一张表载有2014年至2017年在这些监测站收集到的观测数据(建造，未优化)：
Description for: metar_records (359786049 records)
+-----------+------------+--------+-------+---------+----------------+
|Field |Type | NULL |KEY | Default | Extra |
+-----------+------------+--------+-------+---------+----------------+
|auto_id |INT |NO |PRI |NULL |auto_increment |
|stationID |char(4) |NO |MUL |0 | |
|zdatetime |datetime |NO | |NULL | |
|ldatetime |datetime |NO | |NULL | |
|temp |tinyint(4) |YES | |NULL | |
|dew |tinyint(4) |YES | |NULL | |
|wspd |tinyint(3) |YES | |NULL | #unsigned |
|wdir |tinyint(3) |YES | |NULL | #unsigned |
|wgust |tinyint(3) |YES | |NULL | #unsigned |
|VRB |char(3) |YES | |NULL | |
+-----------+------------+--------+-------+---------+----------------+其中stationID是两个表相关的字段。metar_records在('stationID', 'zdatetime')上有一个唯一的索引。metar_records表索引列表：
+-------------+--------+---------+------------+-----------+-----------+----------+
|Table |Non_UNQ |Key_name |Seq_in_index|Column_name|Cardinality|Index_type|
+-------------+--------+---------+------------+-----------+-----------+----------+
|metar_records|0 |PRIMARY |1 |auto_id |358374698 |BTREE |
|metar_records|0 |sz_date |1 |stationID |820079 |BTREE |
|metar_records|0 |sz_date |2 |zdatetime |358374698 |BTREE |
|metar_records|1 |stationID|1 |stationID |598288 |BTREE |
+-------------+--------+---------+------------+-----------+-----------+----------+是我真正感到困惑的地方：我还有一个测试表(称为metar_test)，它与metar_records完全相同，除了没有auto_increment字段，而且没有任何索引。SELECT COUNT(*) FROM metar_test;的执行最多为0.02秒，而SELECT COUNT(*) FROM metar_records;只需1分18秒即可完成。
我知道拥有这么大的表会导致一些很长的查询时间，但是metar_records只比metar_test大3.36倍--为什么两个表的SELECT COUNT(*) ...查询之间会有这么大的差异呢？我对数据存储不是很熟悉，但这种差异对我来说似乎是出乎意料的大。
如何改进索引以优化大表大小？从这里开始，是否有可能缩短查询时间？
回答 3
Stack Overflow用户
发布于 2018-03-28 21:06:23
你可以试试：
select count(stationID)
from metar_records这将使查询优化器使用stationID的索引，从而以计数(*)的形式读取较少的数据，从而读取完整的数据。
Stack Overflow用户
发布于 2018-03-28 23:27:42
我会用这种方式重建你的桌子。
各站。StationId字符(4)唯一的Rest.
Metar_records Id作为汽车公司StationId引用stations.id Rest.
这样，您的键长度就小得多，而且是数字的。会提高你的表现。
Stack Overflow用户
发布于 2018-04-03 02:22:42
您可能打开了“查询缓存”。这使得第二次运行完全相同的查询非常快。若要正确地计时查询，请执行两次，并取第二个时间：
SELECT SQL_NO_CACHE ...COUNT(*)是计数行的常用模式。COUNT(col)比较慢，因为它需要检查每个col是否为NOT NULL。
您的大桌子上有3个INDEXes；您只需要一个：
PRIMARY KEY(stationID, zdatetime)而且，通过这样的聚类，几个可能的查询将运行得更快。
请使用SHOW CREATE TABLE；它比DESCRIBE更具描述性。
您应该使用ENGINE=InnoDB，而不是ENGINE=MyISAM (参见SHOW CREATE TABLE)。
SELECT COUNT(*) ...不是一个非常常见的查询；您不应该对它的运行速度有多少了解。
PARTITIONing不太可能提高性能。让我们看看更多你的问题MySQL没有并行处理，甚至对于PARTITIONed表也是如此。
还将id AUTO_INCREMENT从PRIMARY KEY(stationID)表中抛出；相反，使用PRIMARY KEY(stationID)。
https://stackoverflow.com/questions/49543986
复制相似问题
腾讯云开发者
