MySQL的优化多个内部连接
也许是这个星球上最懒惰的MySQL的查询。我不假装成为这个话题的专家 - 我依靠社区来支持。MySQL的优化多个内部连接
我有我需要在查询运行两个表。一个表格必须内部连接才能提取链接的两端,然后需要使用第二个表格来提取地理坐标。不确定首先要做哪一个。需要一些地理剪裁 - 但这可能是另一天的问题。
过滤应该在频率场和经度/纬度盒来完成。但最重要的,在同一个表DEVICE_DETAILS的列必须连接在一起,从转换:
A面,,,,
B面,,,,
TO:
A面,B面,,,,,
加入上的说法应该解释这更好:
查询是:
SELECT T.EFL_SYSTEM AS TX_EFL_SYSTEM,
T.SITE_ID AS TX_SITE_ID,
T.FREQUENCY AS TX_FREQUENCY,
T.DEVICE_TYPE AS TX_DEVICE_TYPE,
T.EIRP AS TX_EIRP,
T.LONGITUDE AS TX_LON,
T.LATITUDE AS TX_LAT,
R.EFL_SYSTEM AS RX_EFL_SYSTEM,
R.SITE_ID AS RX_SITE_ID,
R.FREQUENCY AS RX_FREQUENCY,
R.DEVICE_TYPE AS RX_DEVICE_TYPE,
R.EIRP AS RX_EIRP,
ST.LONGITUDE,
ST.LATITUDE,
SR.LONGITUDE,
SR.LATITUDE
FROM DEVICE_DETAILS T
INNER JOIN DEVICE_DETAILS R ON (T.EFL_SYSTEM=R.EFL_SYSTEM) AND (T.FREQUENCY=R.FREQUENCY) AND (T.DEVICE_TYPE != R.DEVICE_TYPE)
INNER JOIN SITE ST ON T.SITE_ID = ST.SITE_ID
INNER JOIN SITE SR ON R.SITE_ID = SR.SITE_ID
WHERE T.FREQUENCY > 7425000000 AND T.FREQUENCY < 7525000000 AND R.FREQUENCY > 7425000000 AND R.FREQUENCY < 7525000000 AND T.DEVICE_TYPE = 'T';
这些是表:
CREATE TABLE `SITE` (
`SITE_ID` VARCHAR(31) NOT NULL,
`LATITUDE` DECIMAL(18,14) NOT NULL,
`LONGITUDE` DECIMAL(18,14) NOT NULL,
`NAME` VARCHAR(1023) NOT NULL,
`STATE` VARCHAR(80) NOT NULL,
`LICENSING_AREA_ID` INT(3) NOT NULL,
`POSTCODE` VARCHAR(18) NOT NULL,
`SITE_PRECISION` VARCHAR(31) NOT NULL,
`ELEVATION` INT(5) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
对于DEVICE_DETAILS表;错过了一些领域,但这些是基本的。没有主键可能是因为我每天早上都做CSV批量导入。我认为我不得不优化这部分以及在后期阶段
+--------------------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra|
+--------------------------------+----------------+------+-----+---------+-------+
| SDD_ID | int(10) | NO | | NULL ||
| FREQUENCY | bigint(20) | NO | | NULL ||
| BANDWIDTH | int(15) | NO | | NULL ||
| CARRIER_FREQ | bigint(20) | NO | | NULL ||
| EMISSION | varchar(63) | NO | | NULL ||
| DEVICE_TYPE | varchar(1) | NO | | NULL ||
| TRANSMITTER_POWER | int(20) | NO | | NULL ||
| SITE_ID | varchar(31) | NO | | NULL |||
| EFL_SYSTEM | varchar(63) | NO | | NULL ||
+--------------------------------+----------------+------+-----+---------+-------+
任何反馈或评论在这方面赞赏。
问题:
1 - 这些是多个内部连接好的做法呢?还有什么其他选项可以优化它 2-我从CSV中使用INFILE输入(每天);我无法使用源格式。我还可以添加索引吗?它会影响进口吗?还有什么需要优化索引方法? 3-我可以通过指定Long/Lat矩形边界在SITE表上添加过滤器 - 这是否有助于优化操作?我应该在哪里我的查询中添加这些条件
注:我的网站表是围绕11.5MB和DEVICE_DETAILS约为232.8 MB。我有8GB的RAM和8个线程处理器。
你绝对可以通过添加索引到你的表优化。
在这里阅读更多:http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html
关闭我的头顶,并没有看到你的数据(这是很重要的),我会建议添加索引你的主WHERE和JOIN子句:
ALTER TABLE DEVICE_DETAILS ADD INDEX FREQUENCY (FREQUENCY), ADD INDEX DEVICE_TYPE (DEVICE_TYPE), ADD INDEX SITE_ID (SITE_ID);
ALTER TABLE SITE ADD INDEX SITE_ID (SITE_ID);
另外,我觉得你可能有一个bug(这不是优化) - 应JOIN到ST有这样的ON子句:
ON T.SITE_ID = ST.SITE_ID
相反的:
ON R.SITE_ID = ST.SITE_ID
我的查询先按FREQUENCY过滤,然后再进行连接吗?你也认为我最好把它分解成多个查询,这样mySQL会先解析一个,然后解决另一个问题? –
另外我忘了提及;我使用LOAD DATA LOCAL INFILE从csv文件每天导入数据。我无法控制CSV文件中的列。由于格式不匹配或其他原因,添加索引是否会影响输入? –
添加索引使这个疯狂快! 1个查询的持续时间:0.063秒。感谢您分享您的体验。 –
你能澄清你的具体问题吗? – mhatch
我的加入方式是否适合我想要实现的目标?这可以优化吗?目前这个查询是永久的。 –
您需要允许MySQL充分利用其运行的计算机的资源。这意味着它需要**配置**,因为它附带的默认配置可以让它在真正的旧计算机上工作。你的查询是永远的,因为你没有为MySQL做更快的工作。基本上,正确编写查询,编制索引以及哪些不是过程的一半。 –