提问
我查询的对象是从表a获取所有行,其中性别= f,用户名在Campid = xxxx的表b中不存在.这是我正在成功使用的查询：SELECT `id`
FROM pool
LEFT JOIN sent
ON pool.username = sent.username
AND sent.campid = 'YA1LGfh9'
WHERE sent.username IS NULL
AND pool.gender = 'f'
问题是查询要花费9分钟以上才能完成,池表包含超过1000万行,并且发送的表最终将变得更大.我为许多列(包括用户名和性别)创建了索引.但是,MySQL拒绝对此查询使用我的任何索引.我什至尝试使用FORCE INDEX.这是我的池中的索引以及查询的EXPLAIN输出：
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| pool | 0 | PRIMARY | 1 | id | A | 9326880 | NULL | NULL | | BTREE | |
| pool | 1 | username | 1 | username | A | 9326880 | NULL | NULL | | BTREE | |
| pool | 1 | source | 1 | source | A | 6 | NULL | NULL | | BTREE | |
| pool | 1 | gender | 1 | gender | A | 9 | NULL | NULL | | BTREE | |
| pool | 1 | location | 1 | location | A | 59030 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> explain SELECT `id` FROM pool FORCE INDEX (username) LEFT JOIN sent ON pool.username = sent.username AND sent.campid = 'YA1LGfh9' WHERE sent.username IS NULL AND pool.gender = 'f';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| 1 | SIMPLE | pool | ALL | NULL | NULL | NULL | NULL | 9326881 | Using where |
| 1 | SIMPLE | sent | ALL | NULL | NULL | NULL | NULL | 351 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
2 rows in set (0.00 sec)
另外,这是我发送表的索引：
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sent | 0 | PRIMARY | 1 | primary_key | A | 351 | NULL | NULL | | BTREE | |
| sent | 1 | username | 1 | username | A | 351 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
您可以看到没有索引没有被使用,因此我的查询花费了非常长的时间.如果任何人都有涉及重新处理查询的解决方案,请向我展示一个如何使用我的数据结构进行处理的示例,这样我就不会对如何实现和测试感到困惑.谢谢.
最佳答案
首先,您的原始查询在包括营地在内的所有位置都正确.通过使用从Pool到Sent的LEFT JOIN,然后如先前建议的那样将所需的等式(例如“ CAMP”)拉入WHERE子句,最终将其转换为INNER JOIN,因此需要在两侧进行输入.保留原样.您已经在发送的表上有一个用户名索引,但是我将执行以下操作.
在(CampID,UserName)的“已发送”表上建立索引作为复合(即：多键)索引.这样,将为两个条目优化左联接.
在“池”表上,尝试对(性别,用户名,ID)的3个字段进行综合索引.
这样,您可以不必遍历包含1000万条记录的所有实际数据页.由于索引具有要比较的列,因此它不必查找实际记录并查看列,因此可以直接使用索引的列.
另外,对于笑容,我添加了关键字“ STRAIGHT_JOIN”,该关键字告诉MySQL完全按照我的显示进行查询,不要试图为我思考.很多次,我发现它可以显着提高查询性能…几乎没有人反馈说它没有帮助.
SELECT STRAIGHT_JOIN
p.id
FROM
pool p
LEFT JOIN sent s
ON s.campid = 'YA1LGfh9'
AND p.username = s.username
WHERE
p.gender = 'f'
AND s.username IS NULL
话虽如此,如果池中有1000万,而单个阵营只有5000,那么您仍将返回1000万中的多少记录.您仍然会退回几乎所有物品.
