我有两张桌子具有48 959 281行的歌曲,以及5 721 117行的popular_songs.
这是两个表的结构：
CREATE TABLE songs ( songId BIGINT(20) NOT NULL,songName VARCHAR(1000) NOT NULL,songDuration BIGINT(20) NOT NULL,songPreview VARCHAR(1000) NOT NULL,PRIMARY KEY (songId),INDEX (songDuration) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE utf8_general_ci; CREATE TABLE popular_songs ( storeFrontId BIGINT(20) NOT NULL,genreId BIGINT(20) NOT NULL,songId BIGINT(20) NOT NULL,songRank INT(11) NOT NULL,INDEX (storeFrontId),INDEX (genreId),INDEX (songId),INDEX (songRank),CONSTRAINT popular_song UNIQUE (storeFrontId,genreId,songId),FOREIGN KEY (storeFrontId) REFERENCES storefront (storeFrontId),FOREIGN KEY (genreId) REFERENCES genre (genreId),FOREIGN KEY (songId) REFERENCES songs (songId) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE utf8_general_ci;
当我在两个以下的运行时,一切都正常.
MysqL> SELECT count(*) FROM songs; +----------+ | count(*) | +----------+ | 48959281 | +----------+ 1 row in set (9.10 sec) MysqL> SHOW PROFILE; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000008 | | Waiting for query cache lock | 0.000002 | | checking query cache for query | 0.000019 | | checking permissions | 0.000003 | | opening tables | 0.000011 | | System lock | 0.000004 | | Waiting for query cache lock | 0.000017 | | init | 0.000006 | | optimizing | 0.000003 | | statistics | 0.000004 | | preparing | 0.000004 | | executing | 0.000003 | | Sending data | 9.100444 | | end | 0.000012 | | query end | 0.000005 | | closing tables | 0.000008 | | freeing items | 0.000007 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000012 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000001 | | storing result in query cache | 0.000002 | | logging slow query | 0.000001 | | cleaning up | 0.000003 | +--------------------------------+----------+ 24 rows in set (0.00 sec)
MysqL> SELECT count(*) FROM popular_songs; +----------+ | count(*) | +----------+ | 5721117 | +----------+ 1 row in set (1.34 sec) MysqL> SHOW PROFILE; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000012 | | Waiting for query cache lock | 0.000004 | | checking query cache for query | 0.000037 | | checking permissions | 0.000006 | | opening tables | 0.000017 | | System lock | 0.000007 | | Waiting for query cache lock | 0.000031 | | init | 0.000010 | | optimizing | 0.000017 | | statistics | 0.000004 | | preparing | 0.000004 | | executing | 0.000003 | | Sending data | 1.343991 | | end | 0.000010 | | query end | 0.000005 | | closing tables | 0.000007 | | freeing items | 0.000007 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000011 | | Waiting for query cache lock | 0.000001 | | freeing items | 0.000001 | | storing result in query cache | 0.000002 | | logging slow query | 0.000001 | | cleaning up | 0.000003 | +--------------------------------+----------+ 24 rows in set (0.00 sec)
当我运行这些以下查询时,MysqL没有响应.我不明白是什么原因.
SELECT COUNT(*) FROM songs INNER JOIN popular_songs ON popular_songs.songId = songs.songId
SELECT COUNT(*) FROM songs INNER JOIN popular_songs ON popular_songs.songId = songs.songId WHERE songs.songDuration > 0
更新：
MysqL响应上面两个查询.但发送数据需要250-300秒.如何优化这个.
EXPLAIN EXTENDED报告：
MysqL> EXPLAIN EXTENDED -> SELECT COUNT(*) -> FROM songs -> INNER JOIN popular_songs ON popular_songs.songId = songs.songId; +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+ | 1 | SIMPLE | popular_songs | index | songId | songRank | 4 | NULL | 6449163 | 100.00 | Using index | | 1 | SIMPLE | songs | eq_ref | PRIMARY | PRIMARY | 8 | itunes.popular_songs.songId | 1 | 100.00 | Using index | +----+-------------+---------------+--------+---------------+----------+---------+-----------------------------+---------+----------+-------------+ 2 rows in set,1 warning (0.01 sec) MysqL> EXPLAIN EXTENDED -> SELECT COUNT(*) -> FROM songs -> INNER JOIN popular_songs ON popular_songs.songId = songs.songId -> WHERE songs.songDuration > 0; +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+ | 1 | SIMPLE | popular_songs | index | songId | songRank | 4 | NULL | 6449163 | 100.00 | Using index | | 1 | SIMPLE | songs | eq_ref | PRIMARY,songDuration | PRIMARY | 8 | itunes.popular_songs.songId | 1 | 100.00 | Using where | +----+-------------+---------------+--------+----------------------+----------+---------+-----------------------------+---------+----------+-------------+ 2 rows in set,1 warning (0.00 sec)
当使用INDEX songId加入两个表时：
MysqL> SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId; +----------+ | COUNT(*) | +----------+ | 5721117 | +----------+ 1 row in set (25.35 sec) MysqL> SHOW PROFILE; +--------------------------------+-----------+ | Status | Duration | +--------------------------------+-----------+ | starting | 0.000011 | | Waiting for query cache lock | 0.000004 | | checking query cache for query | 0.000045 | | checking permissions | 0.000004 | | checking permissions | 0.000004 | | opening tables | 0.000023 | | System lock | 0.000007 | | Waiting for query cache lock | 0.000030 | | init | 0.000021 | | optimizing | 0.000011 | | statistics | 0.000020 | | preparing | 0.000012 | | executing | 0.000005 | | Sending data | 25.350160 | | end | 0.000019 | | query end | 0.000005 | | closing tables | 0.000024 | | freeing items | 0.000022 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000016 | | Waiting for query cache lock | 0.000001 | | freeing items | 0.000001 | | storing result in query cache | 0.000002 | | logging slow query | 0.000001 | | logging slow query | 0.000005 | | cleaning up | 0.000003 | +--------------------------------+-----------+ 26 rows in set (0.00 sec)
这很漂亮但是,发送数据仍然需要25秒.
说明扩展以上查询：
MysqL> EXPLAIN EXTENDED SELECT COUNT(*) FROM songs INNER JOIN popular_songs USE INDEX FOR JOIN (songId) ON popular_songs.songId = songs.songId; +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+ | 1 | SIMPLE | popular_songs | index | songId | songId | 8 | NULL | 2684407 | 100.00 | Using index | | 1 | SIMPLE | songs | eq_ref | PRIMARY | PRIMARY | 8 | itunes.popular_songs.songId | 1 | 100.00 | Using index | +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+---------+----------+-------------+ 2 rows in set,1 warning (0.00 sec)
