在我的MariaDB数据库中，我有这个查询，我希望加速:
select
move,
color,
count(*) as count
from
(
select
b2.move,
b2.color
from
game_fen_bridge b1
inner join game_fen_bridge b2 on b1.game_id = b2.game_id
where
b2.ply > b1.ply
and b2.ply < b1.ply + 10
and b1.fen_id = 301
) inner_query
group by
move,
color;
大约需要1.7秒，这不是很好，因为它是UI所需要的。而且，如果我不做点什么的话，随着数据的增长，它会变得越来越长。这里是EXPLAIN:
+------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | b1 | ref | game_id,game_id_2,pgf,pfg,gpf,fpg,fgp | fpg | 9 | const | 78964 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | b2 | ref | game_id,game_id_2,pgf,pfg,gpf | game_id | 9 | danbockn_gambase.b1.game_id | 37 | Using index condition |
+------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
可以看到，它有相当多的索引可供选择。在这里发布之前，我试图通过使用game_id, fen_id和ply的每个排列创建索引来解决这个问题。没有什么可以改变数据库坚持扫描78,964行。
这是桌子。它大约有840万行。其中39,701条与where子句中的fen_id匹配，301.
create table game_fen_bridge (
id bigint unsigned auto_increment primary key,
game_id bigint unsigned,
fen_id bigint unsigned,
move varchar(7),
move_number tinyint unsigned,
ply tinyint unsigned,
color enum('w', 'b'),
constraint foreign key (game_id) references games (game_id),
constraint foreign key (fen_id) references fens (fen_id),
unique(game_id, fen_id, ply),
unique(game_id, fen_id, move_number, color)
);
是否有一种方法来加快这个查询?
更新:示例数据和输出
+---------+----------+---------+-------+-------------+------+-------+
| id | game_id | fen_id | move | move_number | ply | color |
+---------+----------+---------+-------+-------------+------+-------+
| 6483612 | 9866652 | 301 | d4 | 1 | 1 | w |
| 6483613 | 9866652 | 302 | d5 | 1 | 2 | b |
| 6483616 | 9866652 | 1853 | Nf3 | 3 | 5 | w |
| 6483614 | 9866652 | 3101 | Bf4 | 2 | 3 | w |
| 6483615 | 9866652 | 44813 | e6 | 2 | 4 | b |
| 6483617 | 9866652 | 243743 | c5 | 3 | 6 | b |
| 6483618 | 9866652 | 243744 | e3 | 4 | 7 | w |
| 6483619 | 9866652 | 243745 | Nc6 | 4 | 8 | b |
| 6483620 | 9866652 | 2103234 | Bb5 | 5 | 9 | w |
| 6483621 | 9866652 | 2103235 | Bd7 | 5 | 10 | b |
| 6483622 | 9866652 | 5444081 | c3 | 6 | 11 | w |
| 6483623 | 9866652 | 5444082 | f5 | 6 | 12 | b |
| 6483624 | 9866652 | 5444083 | Bxc6 | 7 | 13 | w |
| 6483625 | 9866652 | 5444084 | Bxc6 | 7 | 14 | b |
| 6483626 | 9866652 | 5444085 | O-O | 8 | 15 | w |
| 6483627 | 9866652 | 5444086 | Nf6 | 8 | 16 | b |
| 6483628 | 9866652 | 5444087 | Ng5 | 9 | 17 | w |
| 6483629 | 9866652 | 5444088 | Bd7 | 9 | 18 | b |
| 6483630 | 9866652 | 5444089 | dxc5 | 10 | 19 | w |
| 6483631 | 9866652 | 5444090 | Bxc5 | 10 | 20 | b |
| 6483632 | 9866652 | 5444091 | Be5 | 11 | 21 | w |
| 6483633 | 9866652 | 5444092 | O-O | 11 | 22 | b |
| 6483634 | 9866652 | 5444093 | Bxf6 | 12 | 23 | w |
| 6483635 | 9866652 | 5444094 | Qxf6 | 12 | 24 | b |
| 6483636 | 9866652 | 5444095 | Qh5 | 13 | 25 | w |
| 6483637 | 9866652 | 5444096 | h6 | 13 | 26 | b |
| 6483638 | 9866652 | 5444097 | Nh3 | 14 | 27 | w |
| 6483639 | 9866652 | 5444098 | g5 | 14 | 28 | b |
| 6483640 | 9866652 | 5444099 | b4 | 15 | 29 | w |
| 6483641 | 9866652 | 5444100 | Bxb4 | 15 | 30 | b |
| 6483642 | 9866652 | 5444101 | Rc1 | 16 | 31 | w |
| 6483643 | 9866652 | 5444102 | Rac8 | 16 | 32 | b |
| 6483644 | 9866652 | 5444103 | a3 | 17 | 33 | w |
| 6483645 | 9866652 | 5444104 | Bxc3 | 17 | 34 | b |
| 6483646 | 9866652 | 5444105 | Nxc3 | 18 | 35 | w |
| 6483647 | 9866652 | 5444106 | Rxc3 | 18 | 36 | b |
| 6483648 | 9866652 | 5444107 | Rab1 | 19 | 37 | w |
| 6483649 | 9866652 | 5444108 | Rfc8 | 19 | 38 | b |
| 6483650 | 9866652 | 5444109 | Rd1 | 20 | 39 | w |
| 6483651 | 9866652 | 5444110 | Ba4 | 20 | 40 | b |
| 6483652 | 9866652 | 5444111 | Rf1 | 21 | 41 | w |
| 6483653 | 9866652 | 5444112 | b5 | 21 | 42 | b |
| 6483654 | 9866652 | 5444113 | f4 | 22 | 43 | w |
| 6483655 | 9866652 | 5444114 | g4 | 22 | 44 | b |
| 6483656 | 9866652 | 5444115 | Nf2 | 23 | 45 | w |
| 6483657 | 9866652 | 5444116 | Rxe3 | 23 | 46 | b |
| 6483658 | 9866652 | 5444117 | g3 | 24 | 47 | w |
| 6483659 | 9866652 | 5444118 | b4 | 24 | 48 | b |
| 6483660 | 9866652 | 5444119 | Rxb4 | 25 | 49 | w |
| 6483661 | 9866652 | 5444120 | Be8 | 25 | 50 | b |
| 6483662 | 9866652 | 5444121 | Qh4 | 26 | 51 | w |
| 6483663 | 9866652 | 5444122 | Qxh4 | 26 | 52 | b |
| 6483664 | 9866652 | 5444123 | gxh4 | 27 | 53 | w |
| 6483665 | 9866652 | 5444124 | Rc4 | 27 | 54 | b |
| 6483666 | 9866652 | 5444125 | Rb8 | 28 | 55 | w |
| 6483667 | 9866652 | 5444126 | Kf7 | 28 | 56 | b |
| 6483668 | 9866652 | 5444127 | Rd1 | 29 | 57 | w |
| 6483669 | 9866652 | 5444128 | Rxf4 | 29 | 58 | b |
| 6483670 | 9866652 | 5444129 | Nd3 | 30 | 59 | w |
| 6483671 | 9866652 | 5444130 | Rff3 | 30 | 60 | b |
| 6483672 | 9866652 | 5444131 | Rb7+ | 31 | 61 | w |
| 6483673 | 9866652 | 5444132 | Kf6 | 31 | 62 | b |
| 6483674 | 9866652 | 5444133 | Nc5 | 32 | 63 | w |
| 6483675 | 9866652 | 5444134 | Rxa3 | 32 | 64 | b |
| 6483676 | 9866652 | 5444135 | Re1 | 33 | 65 | w |
| 6483677 | 9866652 | 5444136 | e5 | 33 | 66 | b |
| 6483678 | 9866652 | 5444137 | Rb8 | 34 | 67 | w |
| 6483679 | 9866652 | 5444138 | Bc6 | 34 | 68 | b |
| 6483680 | 9866652 | 5444139 | Rc8 | 35 | 69 | w |
| 6483681 | 9866652 | 5444140 | Bb5 | 35 | 70 | b |
| 6483218 | 10757193 | 301 | d4 | 1 | 1 | w |
| 6483219 | 10757193 | 395 | Nf6 | 1 | 2 | b |
| 6483220 | 10757193 | 396 | c4 | 2 | 3 | w |
| 6483221 | 10757193 | 592 | e6 | 2 | 4 | b |
| 6483222 | 10757193 | 23786 | Nf3 | 3 | 5 | w |
| 6483223 | 10757193 | 29571 | b6 | 3 | 6 | b |
| 6483224 | 10757193 | 29572 | g3 | 4 | 7 | w |
| 6483228 | 10757193 | 49264 | O-O | 6 | 11 | w |
| 6483229 | 10757193 | 49265 | O-O | 6 | 12 | b |
| 6483230 | 10757193 | 49266 | Nc3 | 7 | 13 | w |
| 6483231 | 10757193 | 49267 | Ne4 | 7 | 14 | b |
| 6483225 | 10757193 | 51255 | Bb7 | 4 | 8 | b |
| 6483226 | 10757193 | 51256 | Bg2 | 5 | 9 | w |
| 6483227 | 10757193 | 117266 | Be7 | 5 | 10 | b |
| 6483232 | 10757193 | 259277 | Qc2 | 8 | 15 | w |
| 6483233 | 10757193 | 259278 | Nxc3 | 8 | 16 | b |
| 6483234 | 10757193 | 259279 | Qxc3 | 9 | 17 | w |
| 6483235 | 10757193 | 1441865 | c5 | 9 | 18 | b |
| 6483236 | 10757193 | 5443779 | b3 | 10 | 19 | w |
| 6483237 | 10757193 | 5443780 | Bf6 | 10 | 20 | b |
| 6483238 | 10757193 | 5443781 | Bb2 | 11 | 21 | w |
| 6483239 | 10757193 | 5443782 | d6 | 11 | 22 | b |
| 6483240 | 10757193 | 5443783 | e3 | 12 | 23 | w |
| 6483241 | 10757193 | 5443784 | Nd7 | 12 | 24 | b |
| 6483242 | 10757193 | 5443785 | Rad1 | 13 | 25 | w |
| 6483243 | 10757193 | 5443786 | Qc7 | 13 | 26 | b |
| 6483244 | 10757193 | 5443787 | Rd2 | 14 | 27 | w |
| 6483245 | 10757193 | 5443788 | Rac8 | 14 | 28 | b |
| 6483246 | 10757193 | 5443789 | Rfd1 | 15 | 29 | w |
| 6483247 | 10757193 | 5443790 | cxd4 | 15 | 30 | b |
| 6483248 | 10757193 | 5443791 | Nxd4 | 16 | 31 | w |
| 6483249 | 10757193 | 5443792 | Bxg2 | 16 | 32 | b |
| 6483250 | 10757193 | 5443793 | Kxg2 | 17 | 33 | w |
| 6483251 | 10757193 | 5443794 | b5 | 17 | 34 | b |
| 6483252 | 10757193 | 5443795 | Qb4 | 18 | 35 | w |
| 6483253 | 10757193 | 5443796 | bxc4 | 18 | 36 | b |
| 6483254 | 10757193 | 5443797 | Nb5 | 19 | 37 | w |
| 6483255 | 10757193 | 5443798 | Qb7+ | 19 | 38 | b |
| 6483256 | 10757193 | 5443799 | f3 | 20 | 39 | w |
| 6483257 | 10757193 | 5443800 | Bxb2 | 20 | 40 | b |
| 6483258 | 10757193 | 5443801 | Rxb2 | 21 | 41 | w |
| 6483259 | 10757193 | 5443802 | Ne5 | 21 | 42 | b |
| 6483260 | 10757193 | 5443803 | e4 | 22 | 43 | w |
| 6483261 | 10757193 | 5443804 | Nd3 | 22 | 44 | b |
| 4665951 | 13090397 | 301 | d4 | 1 | 1 | w |
| 4665952 | 13090397 | 395 | Nf6 | 1 | 2 | b |
| 4665953 | 13090397 | 901 | Nf3 | 2 | 3 | w |
| 4665954 | 13090397 | 3041 | e6 | 2 | 4 | b |
| 4665956 | 13090397 | 52873 | d5 | 3 | 6 | b |
| 4665955 | 13090397 | 76756 | e3 | 3 | 5 | w |
| 4665957 | 13090397 | 3944440 | Ne5 | 4 | 7 | w |
| 4665958 | 13090397 | 3944441 | Nbd7 | 4 | 8 | b |
| 4665959 | 13090397 | 3944442 | f4 | 5 | 9 | w |
| 4665960 | 13090397 | 3944443 | Be7 | 5 | 10 | b |
| 4665961 | 13090397 | 3944444 | Bd3 | 6 | 11 | w |
| 4665962 | 13090397 | 3944445 | O-O | 6 | 12 | b |
| 4665963 | 13090397 | 3944446 | O-O | 7 | 13 | w |
| 4665964 | 13090397 | 3944447 | c5 | 7 | 14 | b |
| 4665965 | 13090397 | 3944448 | c3 | 8 | 15 | w |
| 4665966 | 13090397 | 3944449 | Qc7 | 8 | 16 | b |
| 4665967 | 13090397 | 3944450 | Qf3 | 9 | 17 | w |
| 4665968 | 13090397 | 3944451 | b6 | 9 | 18 | b |
| 4665969 | 13090397 | 3944452 | Nd2 | 10 | 19 | w |
| 4665970 | 13090397 | 3944453 | Bb7 | 10 | 20 | b |
| 4665971 | 13090397 | 3944454 | Rf2 | 11 | 21 | w |
| 4665972 | 13090397 | 3944455 | g6 | 11 | 22 | b |
| 4665973 | 13090397 | 3944456 | Qe2 | 12 | 23 | w |
| 4665974 | 13090397 | 3944457 | Ne4 | 12 | 24 | b |
| 4665975 | 13090397 | 3944458 | Rf3 | 13 | 25 | w |
| 4665976 | 13090397 | 3944459 | Ndf6 | 13 | 26 | b |
| 4665977 | 13090397 | 3944460 | Rh3 | 14 | 27 | w |
| 4665978 | 13090397 | 3944461 | h5 | 14 | 28 | b |
| 4665979 | 13090397 | 3944462 | Ndf3 | 15 | 29 | w |
| 4665980 | 13090397 | 3944463 | Kg7 | 15 | 30 | b |
| 4665981 | 13090397 | 3944464 | Bd2 | 16 | 31 | w |
| 4665982 | 13090397 | 3944465 | Ng4 | 16 | 32 | b |
| 4665983 | 13090397 | 3944466 | Bxe4 | 17 | 33 | w |
| 4665984 | 13090397 | 3944467 | dxe4 | 17 | 34 | b |
| 4665985 | 13090397 | 3944468 | Nh4 | 18 | 35 | w |
| 4665986 | 13090397 | 3944469 | Nxe5 | 18 | 36 | b |
| 4665987 | 13090397 | 3944470 | fxe5 | 19 | 37 | w |
| 4665988 | 13090397 | 3944471 | Bxh4 | 19 | 38 | b |
| 4665989 | 13090397 | 3944472 | Rxh4 | 20 | 39 | w |
| 4665990 | 13090397 | 3944473 | Qe7 | 20 | 40 | b |
| 4665991 | 13090397 | 3944474 | Rh3 | 21 | 41 | w |
| 4665992 | 13090397 | 3944475 | f6 | 21 | 42 | b |
| 4665993 | 13090397 | 3944476 | Rg3 | 22 | 43 | w |
| 4665994 | 13090397 | 3944477 | Kh6 | 22 | 44 | b |
| 4665995 | 13090397 | 3944478 | Rf1 | 23 | 45 | w |
| 4665996 | 13090397 | 3944479 | fxe5 | 23 | 46 | b |
| 4665997 | 13090397 | 3944480 | Rf4 | 24 | 47 | w |
| 4665998 | 13090397 | 3944481 | exf4 | 24 | 48 | b |
| 4665999 | 13090397 | 3944482 | exf4 | 25 | 49 | w |
| 4666000 | 13090397 | 3944483 | Rf5 | 25 | 50 | b |
| 4666001 | 13090397 | 3944484 | Rh3 | 26 | 51 | w |
| 4666002 | 13090397 | 3944485 | Kg7 | 26 | 52 | b |
| 4666003 | 13090397 | 3944486 | g4 | 27 | 53 | w |
| 4666004 | 13090397 | 3944487 | hxg4 | 27 | 54 | b |
| 4666005 | 13090397 | 3944488 | Qxg4 | 28 | 55 | w |
| 4666006 | 13090397 | 3944489 | Rh8 | 28 | 56 | b |
| 4666007 | 13090397 | 3944490 | Rg3 | 29 | 57 | w |
| 4666008 | 13090397 | 3944491 | Rh6 | 29 | 58 | b |
| 4666009 | 13090397 | 3944492 | Rh3 | 30 | 59 | w |
| 4666010 | 13090397 | 3944493 | Rxh3 | 30 | 60 | b |
| 4666011 | 13090397 | 3944494 | Qxh3 | 31 | 61 | w |
| 4666012 | 13090397 | 3944495 | Qf8 | 31 | 62 | b |
| 4666013 | 13090397 | 3944496 | Qg4 | 32 | 63 | w |
| 4666014 | 13090397 | 3944497 | Qh8 | 32 | 64 | b |
| 4666015 | 13090397 | 3944498 | dxc5 | 33 | 65 | w |
| 4666016 | 13090397 | 3944499 | Qh5 | 33 | 66 | b |
| 4666017 | 13090397 | 3944500 | h3 | 34 | 67 | w |
| 4666018 | 13090397 | 3944501 | Qxg4+ | 34 | 68 | b |
| 4666019 | 13090397 | 3944502 | hxg4 | 35 | 69 | w |
| 4666020 | 13090397 | 3944503 | Rxc5 | 35 | 70 | b |
| 4666021 | 13090397 | 3944504 | Kf2 | 36 | 71 | w |
| 4666022 | 13090397 | 3944505 | Kf6 | 36 | 72 | b |
| 4666023 | 13090397 | 3944506 | Be3 | 37 | 73 | w |
| 4666024 | 13090397 | 3944507 | Rd5 | 37 | 74 | b |
| 4666025 | 13090397 | 3944508 | Bd4+ | 38 | 75 | w |
| 4666026 | 13090397 | 3944509 | Rxd4 | 38 | 76 | b |
| 4666027 | 13090397 | 3944510 | cxd4 | 39 | 77 | w |
| 4666028 | 13090397 | 3944511 | g5 | 39 | 78 | b |
| 4666029 | 13090397 | 3944512 | Ke3 | 40 | 79 | w |
| 4666030 | 13090397 | 3944513 | Bd5 | 40 | 80 | b |
| 4666031 | 13090397 | 3944514 | a3 | 41 | 81 | w |
| 4666032 | 13090397 | 3944515 | gxf4+ | 41 | 82 | b |
| 4666033 | 13090397 | 3944516 | Kxf4 | 42 | 83 | w |
| 4666034 | 13090397 | 3944517 | Kg6 | 42 | 84 | b |
| 4666035 | 13090397 | 3944518 | b4 | 43 | 85 | w |
| 4666036 | 13090397 | 3944519 | b5 | 43 | 86 | b |
| 4666037 | 13090397 | 3944520 | g5 | 44 | 87 | w |
| 4666038 | 13090397 | 3944521 | Kh5 | 44 | 88 | b |
+---------+----------+---------+-------+-------------+------+-------+
+------+-------+-------+
| move | color | count |
+------+-------+-------+
| b6 | b | 1 |
| Bb5 | w | 1 |
| Bb7 | b | 1 |
| Bd7 | b | 1 |
| Be7 | b | 2 |
| Bf4 | w | 1 |
| Bg2 | w | 1 |
| c4 | w | 1 |
| c5 | b | 1 |
| d5 | b | 2 |
| e3 | w | 2 |
| e6 | b | 3 |
| f4 | w | 1 |
| g3 | w | 1 |
| Nbd7 | b | 1 |
| Nc6 | b | 1 |
| Ne5 | w | 1 |
| Nf3 | w | 3 |
| Nf6 | b | 2 |
+------+-------+-------+
这看起来更少打字了…
select
b2.move,
b2.color
, count(*) total
from
game_fen_bridge b1
inner join game_fen_bridge b2 on b1.game_id = b2.game_id
where
b2.ply > b1.ply
and b2.ply < b1.ply + 10
and b1.fen_id = 301
group by move, color;
您不需要子查询来表示:
select b2.move, b2.color, count(*) as total
from game_fen_bridge b1 join
game_fen_bridge b2
on b1.game_id = b2.game_id
where b2.ply > b1.ply and
b2.ply < b1.ply + 10 and
b1.fen_id = 301
group by move, color;
对于这个查询，您想要一个game_fen_bridge(game_id, fen_id, ply)的索引。
也就是说，您很有可能使用窗口函数来表达逻辑。如果没有样本数据、期望的结果和对逻辑的清晰解释，很难说，但也许:
select gfb.*,
sum( gen_id = 301 ) over (partition by game_id order by ply range between 1 following and 9 following)
from game_fen_bridge gfb
让我们来做
PRIMARY KEY(fen_id, ply, game_id) -- deliberate reangement
INDEX(ply, game_id, color, move) -- to "cover" the join
unique(game_id, fen_id, move_number, color) -- unchanged
你知道你检查的是9层，而不是10层吗?
子查询("派生表")必须生成所有行。然后外部查询必须排序和计数。GROUP BY有三种方式:使用磁盘临时表(不太可能)，使用ram中的临时表，或者通过内存中的散列(不进行排序)。