MySQL优化多个内连接

Question

这可能是地球上最懒的 mySQL 查询。我不会假装自己是该主题的专家 - 我依靠社区的支持。

我有两个表需要在查询中运行。一个表必须内部连接以提取链接的两端，然后需要使用第二个表来提取地理坐标。不确定必须先完成哪一项。需要进行一些地理切割 - 但这可能是另一天的问题。

过滤应在频率字段和长/纬度框中完成。但最重要的是，同一表 DEVICE_DETAILS 的列必须连接在一起才能转换:

A面,,,,

B面,,,,

致:

A面，B面,,,,,

join on 语句应该更好地解释这一点:

查询是:

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-这些多个 INNER JOINS 是好的做法吗？还有哪些其他选项可以优化它2-我使用 CSV 中的 INFILE 导入(每天)；我无法使用源格式。我还可以添加索引吗？对进口有影响吗？还需要什么来优化索引方法吗？3-我可以通过指定长/纬度矩形边界在 SITE 表上添加过滤器 - 这将有助于优化操作吗？我应该在查询中的哪里添加这些条件

注意:我的 SITE 表约为 11.5 MB，DEVICE_DETAILS 约为 232.8 MB。我有 8GB RAM 和 8 线程处理器。

Answer 1

您绝对可以通过向表中添加索引来进行优化。

在此处了解更多信息: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);

此外，我认为您可能有一个错误(这不是为了优化) - 如果 JOIN 到 ST 具有此 ON 子句:

ON T.SITE_ID = ST.SITE_ID

而不是:

ON R.SITE_ID = ST.SITE_ID