在MySQL中连接表(慢速)

Question

我试图将一些数据保存在两个单独的表中，然后用一个引用表将它们连接起来。

我的数据如下所示(搜索查询和页面URL)：

[
['widget', 'www.example.com/widgets'],
['blue widget', 'www.example.com/blue-widgets'],
['red widget', 'www.example.com/widgets'],
['widget', 'www.example.com/green-widgets'],
['orange widget', 'www.example.com/widgets'],
]

正如您所看到的，一些搜索查询被分配给多个页面，考虑到这一点，以及其他一些客户端性能原因，我想将搜索查询和页面url分割成单独的表，但是要跟踪哪个搜索查询属于哪个页面-url。

为了做到这一点，到目前为止，我有以下两个表( unique_key列只是用来停止添加重复项的散列)：

CREATE TABLE queries (
        id BIGINT NOT NULL AUTO_INCREMENT,
        query VARCHAR(400) collate utf8_bin,
        unique_key varchar(100) unique,
        PRIMARY KEY (id));

CREATE TABLE pages (
        id BIGINT NOT NULL AUTO_INCREMENT,
        page VARCHAR(2083),
        unique_key varchar(100) unique,
        PRIMARY KEY (id));

insert into queries (query, unique_key) values 
('widget', '1234'),
('widgets', '1233'),
('blue widget', '3243'), 
('red widget', '5432'),
('green widget', '4642');

insert into pages (page, unique_key) values 
('www.example.com/widgets', '7895'),
('www.example.com/widgets-1', '4569'),
('www.example.com/widgets-2', '4568'),
('www.example.com/widgets-3', '1254'),
('www.example.com/widgets-4', '6527');

然后，为了构造与页面相关的数据透视表，我正在这样做：

CREATE TABLE page_query_join (
    id BIGINT NOT NULL AUTO_INCREMENT,
    query_id int,
    page_id int,
    PRIMARY KEY (id));

insert into page_query_join (query_id, page_id)
                    values ((select id from queries where query='widget' limit 1), (select id from pages where page='www.example.com/widgets' limit 1)) ON DUPLICATE KEY UPDATE query_id=query_id

这都是在Python脚本中完成的，我基本上创建了散列键，在一个循环中插入所有的搜索查询，然后在另一个循环中插入所有的页面-urls，然后在最后一次循环并执行最后的查询来加入它们。

最终查找的全部功能如下所示：

def add_query_page_join(self, data):
    sql = '''insert into page_query_join (query_id, page_id)
                values ((select id from queries where query=%s limit 1), (select id from pages where page=%s limit 1)) ON DUPLICATE KEY UPDATE query_id=query_id'''
    try:
        # Execute the SQL command
        self.cursor.executemany(sql, data)
        # Commit your changes in the database
        self.db.commit()
        #return self.cursor.lastrowid
    except Exception as e:
        # Rollback in case there is any error
        self.db.rollback()
    finally:
        self.db.close()

data变量如下所示：

data = [
['widget', 'www.example.com/widgets'],
['blue widget', 'www.example.com/blue-widgets'],
['red widget', 'www.example.com/widgets'],
['widget', 'www.example.com/green-widgets'],
['orange widget', 'www.example.com/widgets'],
]

我的数据变量一次传递1,000项，但我每天总共有大约100万项要做。我现在的版本需要几个小时才能完成。

我可以做些什么来提高联接表查询的性能，或者仅仅是我将所有内容添加到表中的方式(也许有一种方法可以同时插入到所有三个表中？)

感谢你的帮助-任何遗漏的事都让我知道。

更新对join查询的解释：

+----+-------------+---------+------+---------------+-------+---------+-------+------+----------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra          |
+----+-------------+---------+------+---------------+-------+---------+-------+------+----------------+
|  1 | PRIMARY     | NULL    | NULL | NULL          | NULL  | NULL    | NULL  | NULL | No tables used |
|  3 | SUBQUERY    | pages   | ALL  | NULL          | NULL  | NULL    | NULL  | 8641 | Using where    |
|  2 | SUBQUERY    | queries | ref  | query         | query | 768     | const |    1 | Using where    |
+----+-------------+---------+------+---------------+-------+---------+-------+------+----------------+

示例演示： https://www.db-fiddle.com/f/v9ZJR6UwHqRv36qobkGxsv/1

Answer 1

目前还不清楚你在做什么，但你显然需要

queries: INDEX(query, id)  -- This seems to exist
pages: INDEX(page, id)     -- Definitely missing

但是，由于page太大，无法进行索引，所以请考虑将unique_hash更改为PRIMARY KEY (并且完全摆脱当前的id)。

同时，您有一个没有LIMIT的ORDER BY；您关心您得到哪个项目吗？

Answer 2

阐述：

我假设您已经将数据加载到一个名为widget_url_pairs的表中。这是一个模拟.

USE test;

DROP TABLE IF EXISTS widget_url_pairs;

CREATE TABLE widget_url_pairs
(widget VARCHAR(100) NOT NULL
,url VARCHAR(200) NOT NULL
,PRIMARY KEY(widget,url)
);

INSERT IGNORE INTO widget_url_pairs VALUES
('widget', 'www.example.com/widgets'),
('blue widget', 'www.example.com/blue-widgets'),
('red widget', 'www.example.com/widgets'),
('widget', 'www.example.com/green-widgets'),
('orange widget', 'www.example.com/widgets');

DROP TABLE IF EXISTS widgets;

CREATE TABLE widgets
(widget_id SERIAL PRIMARY KEY
,widget VARCHAR(100) NOT NULL UNIQUE
);

INSERT INTO widgets (widget) SELECT DISTINCT widget FROM widget_url_pairs;

DROP TABLE IF EXISTS urls;

CREATE TABLE urls
(url_id SERIAL PRIMARY KEY
,url VARCHAR(200) NOT NULL UNIQUE
);

INSERT INTO urls(url) SELECT DISTINCT url FROM widget_url_pairs;

DROP TABLE IF EXISTS widget_url;

CREATE TABLE widget_url 
(widget_id INT NOT NULL
,url_id INT NOT NULL
);


INSERT IGNORE INTO widget_url
SELECT DISTINCT w.widget_id
              , u.url_id
           FROM widget_url_pairs wu 
           JOIN widgets w 
             ON w.widget = wu.widget
           JOIN urls u
             ON u.url = wu.url;
             
ALTER TABLE widget_url ADD PRIMARY KEY (widget_id,url_id);

SELECT w.widget
     , u.url 
  FROM widgets w 
  JOIN widget_url wu 
    ON wu.widget_id = w.widget_id 
  JOIN urls u 
    ON u.url_id = wu.url_id;
+---------------+-------------------------------+
| widget        | url                           |
+---------------+-------------------------------+
| blue widget   | www.example.com/blue-widgets  |
| orange widget | www.example.com/widgets       |
| red widget    | www.example.com/widgets       |
| widget        | www.example.com/widgets       |
| widget        | www.example.com/green-widgets |
+---------------+-------------------------------+