MySQL:whereしたテーブル同士のjoinでインデックスが使用されず高速化できない

whereした2つのテーブルをinner joinしたいのですがインデックスが使用できません。
それぞれwhereに使用するカラムと結合条件のカラムを合わせた複合インデックスを作成しているのですがexplainしてもusing indexにならず非常に低速です。
force indexしてもインデックスだけで解決されず、Using join buffer (Block Nested Loop) となって速度が出ません。
joinに使用する列のインデックスを作成し、force index for joinの併用も試しましたが高速化できませんでした。
どうすればインデックスを使用して高速化できるのでしょうか。

・追記
asinテーブルのwhereに使用する「FlagSer&20」を式インデックスとして作成しましたが、結果は変わりませんでした。

KEY index_test2 (MonorateBuyNum,SettingProductItem,((FlagSer & 20)),ASIN)

クエリとexplain

select
asin
from (select
ASIN
from analyzetime
where
Tick<637433317810371056&&
SiteName='サウンドハウス'
order by Tick) as work
inner join 
(select asin from asin
where MonorateBuyNum>=15&&
SettingProductItem=1&&
(FlagSer&20)=0) as workAsin using(asin)
limit 1000;

+----+-------------+-------------+------------+--------+-------------------------------------------------------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+
| id | select_type | table       | partitions | type   | possible_keys                                                           | key               | key_len | ref                     | rows   | filtered | Extra                    |
+----+-------------+-------------+------------+--------+-------------------------------------------------------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | asin        | NULL       | range  | PRIMARY,monorate_idx,jan_relation_index,dhash_get_idx,ureteru_get_index | ureteru_get_index | 3       | NULL                    | 353886 |    25.00 | Using where; Using index |
|  1 | SIMPLE      | analyzetime | NULL       | eq_ref | PRIMARY,tick_idx,asin_idx,tick_and_site_idx,all_idx                     | PRIMARY           | 812     | imusedb.asin.ASIN,const |      1 |    50.00 | Using where              |
+----+-------------+-------------+------------+--------+-------------------------------------------------------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+

//force indexを使用
select
asin
from (select
ASIN
from analyzetime
force index(all_idx)
where
Tick<637433317810371056&&
SiteName='サウンドハウス'
order by Tick) as work
inner join 
(select asin from asin
force index(ureteru_get_index)
where MonorateBuyNum>=15&&
SettingProductItem=1&&
(FlagSer&20)=0) as workAsin using(asin)
limit 1000;

+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------+-----------+----------+-----------------------------------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys     | key               | key_len | ref  | rows      | filtered | Extra                                                           |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------+-----------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | analyzetime | NULL       | range | all_idx           | all_idx           | 9       | NULL | 299937693 |    10.00 | Using where; Using index                                        |
|  1 | SIMPLE      | asin        | NULL       | range | ureteru_get_index | ureteru_get_index | 3       | NULL |    353886 |     0.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------+-----------+----------+-----------------------------------------------------------------+

//force indexとforce index for joinを使用
select
asin
from (select
ASIN
from analyzetime
force index(all_idx)
force index for join(asin)
where
Tick<637433317810371056&&
SiteName='サウンドハウス'
order by Tick) as work
inner join 
(select asin from asin
force index(ureteru_get_index)
force index for join(PRIMARY)
where MonorateBuyNum>=15&&
SettingProductItem=1&&
(FlagSer&20)=0) as workAsin using(asin)
limit 1000;

+----+-------------+-------------+------------+--------+---------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+
| id | select_type | table       | partitions | type   | possible_keys             | key               | key_len | ref                     | rows   | filtered | Extra                    |
+----+-------------+-------------+------------+--------+---------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | asin        | NULL       | range  | PRIMARY,ureteru_get_index | ureteru_get_index | 3       | NULL                    | 353886 |    25.00 | Using where; Using index |
|  1 | SIMPLE      | analyzetime | NULL       | eq_ref | asin_idx,all_idx          | asin_idx          | 812     | imusedb.asin.ASIN,const |      1 |    50.00 | Using where              |
+----+-------------+-------------+------------+--------+---------------------------+-------------------+---------+-------------------------+--------+----------+--------------------------+

create table

//asinテーブル
| asin  | CREATE TABLE `asin` (
  `ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `JAN` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
  `Title` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ImageUrl` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `FixFee` smallint(6) DEFAULT NULL,
  `FeeRate` float(2,2) DEFAULT NULL,
  `PriceFBA` int(11) DEFAULT NULL,
  `PriceSelf` int(11) DEFAULT NULL,
  `PriceCart` int(11) DEFAULT NULL,
  `NotExit` bit(1) NOT NULL,
  `SettingFee` bit(2) NOT NULL,
  `SettingProductItem` bit(2) NOT NULL,
  `SettingJAN` bit(2) NOT NULL,
  `PriceSetTime` bigint(20) NOT NULL,
  `ProductItemSetTime` bigint(20) NOT NULL,
  `FeeSetTime` bigint(20) NOT NULL,
  `SellerNum` smallint(6) DEFAULT NULL,
  `HadFBA` bit(1) NOT NULL,
  `SettingMonorate` bit(2) NOT NULL,
  `MonorateSetTime` bigint(20) NOT NULL,
  `MonorateBuyNum` smallint(6) NOT NULL,
  `TitleOpt` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Danger` bit(1) NOT NULL,
  `Claim` bit(1) NOT NULL,
  `MonorateNoExitRate` float(3,3) NOT NULL,
  `MonorateNoExitRateTotal` float(3,3) NOT NULL,
  `SettingCategory` bit(2) NOT NULL,
  `CategorySetTime` bigint(20) NOT NULL,
  `CategoryHash` int(10) unsigned DEFAULT NULL,
  `CategoryHash2` int(10) unsigned DEFAULT NULL,
  `CategoryHash3` int(10) unsigned DEFAULT NULL,
  `PriceUsedFBA` int(11) DEFAULT NULL,
  `PriceUsedSelf` int(11) DEFAULT NULL,
  `DHashSetTime` bigint(20) NOT NULL,
  `DHash` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RankingCategoryHash1` int(10) unsigned NOT NULL,
  `RankingCategoryHash2` int(10) unsigned NOT NULL,
  `RankingCategoryHash3` int(10) unsigned NOT NULL,
  `Ranking1` int(11) NOT NULL,
  `Ranking2` int(11) NOT NULL,
  `Ranking3` int(11) NOT NULL,
  `PricePreFBA` int(11) DEFAULT NULL,
  `PricePreSelf` int(11) DEFAULT NULL,
  `PricePreUsedFBA` int(11) DEFAULT NULL,
  `PricePreUsedSelf` int(11) DEFAULT NULL,
  `PricePreCart` int(11) DEFAULT NULL,
  `SetTypeSer` smallint(5) unsigned DEFAULT NULL,
  `NoCommunicationTick` bigint(20) NOT NULL,
  `Width` int(10) unsigned DEFAULT NULL,
  `Height` int(10) unsigned DEFAULT NULL,
  `Lenght` int(10) unsigned DEFAULT NULL,
  `Weight` int(10) unsigned DEFAULT NULL,
  `SizeTypeSer` smallint(5) unsigned NOT NULL,
  `JAN2` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
  `JAN3` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
  `JAN4` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
  `JAN5` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
  `Model` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RelationTick` bigint(20) NOT NULL,
  `FlagSer` bit(32) NOT NULL,
  PRIMARY KEY (`ASIN`),
  KEY `price_idx` (`PriceSetTime`),
  KEY `monoratetime_idx` (`MonorateSetTime`),
  KEY `rank_idx` (`Ranking1`),
  KEY `monorate_idx` (`MonorateBuyNum`),
  KEY `fee_monorate_idx` (`FeeSetTime`,`MonorateBuyNum`),
  KEY `jansindex` (`JAN`),
  KEY `jan2index` (`JAN2`),
  KEY `jan3index` (`JAN3`),
  KEY `jan4index` (`JAN4`),
  KEY `jan5index` (`JAN5`),
  KEY `relationtick_idx` (`RelationTick`),
  KEY `keepasort` (`MonorateSetTime`,`Ranking1`),
  KEY `jan_relation_index` (`MonorateBuyNum`,`FlagSer`,`RelationTick`),
  KEY `noc_idx` (`NoCommunicationTick`,`NotExit`,`SettingProductItem`,`ASIN`),
  KEY `dhash_get_idx` (`MonorateBuyNum`,`ASIN`,`FlagSer`),
  KEY `keepafilter` (`NotExit`,`MonorateSetTime`,`Ranking1`,`FlagSer`),
  KEY `category_idx` (`CategorySetTime`,`FlagSer`,`NotExit`),
  KEY `product_idx` (`ProductItemSetTime`,`FlagSer`,`NotExit`),
  KEY `fee_idx` (`FeeSetTime`,`FlagSer`,`NotExit`),
  KEY `dhash_idx` (`DHashSetTime`,`FlagSer`),
  KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`FlagSer`,`ASIN`),
  KEY `index_test` (((`FlagSer` & 20))),
  KEY `index_test2` (`MonorateBuyNum`,`SettingProductItem`,((`FlagSer` & 20)),`ASIN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |------+

//analyzetime テーブル
| analyzetime | CREATE TABLE `analyzetime` (
  `ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `SiteName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Tick` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ASIN`,`SiteName`),
  KEY `tick_idx` (`Tick`),
  KEY `asin_idx` (`ASIN`),
  KEY `tick_and_site_idx` (`Tick`,`SiteName`),
  KEY `all_idx` (`Tick`,`ASIN`,`SiteName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Orlofsky

2020/12/11 17:11

create tableは質問のSELECT文で使っているテーブルのものだけを提示してください。

行動規範の内容に同意します

回答1件

ベストアンサー

そもそもですが、[FlagSer&20]についてのインデックスが無いので、検索でインデックスのみにはならないと思います。
ただ、インデックスだけで解決できるように、先ずは以下の複合インデックスを追加してみてはどうでしょうか。
※データ分布などが良く分かりませんので効率的なものとは言えませんが。
asin:(asin, MonorateBuyNum, SettingProductItem, FlagSer)
analyzetime:(asin, Tick, SiteName)

それから目的がキーを求めるという事であれば、サブクエリーでのorder byは不要だと思います。

SQL
1select analyzetime.asin
2from analyzetime inner join asin using(asin)
3where analyzetime.Tick < 637433317810371056
4  and analyzetime.SiteName='サウンドハウス'
5  and asin.MonorateBuyNum >= 15
6  and asin.SettingProductItem=1
7  and (asin.FlagSer&20)=0

後は主キーを条件にしたinを使用するとか

SQL
1select asin
2from analyzetime
3where Tick < 637433317810371056
4  and SiteName='サウンドハウス'
5  and asian in (
6       select asin from asin
7       where MonorateBuyNum >= 15
8         and SettingProductItem=1
9         and (FlagSer&20)=0
10      )

上記の場合のインデックスは、
asin:(MonorateBuyNum, SettingProductItem, FlagSer, asin)
analyzetime:(asin, SiteName, Tick)
※既存ので間にあう気もしますが。
追記

SQL
1select asin from asin
2where MonorateBuyNum >= 15
3  and SettingProductItem=1
4  and (FlagSer&20)=0

結局のところ、上記のクエリーが早くなれば解決すると思いますので、[FlagSer&20]について、式インデックスを追加されてはどうでしょうか。
MySQL 5.7で生成カラムを使って関数INDEXを作成する

投稿2020/12/12 03:10

編集2020/12/12 03:45

sazi

総合スコア25327

yonotsui

2020/12/12 16:07

ご返信ありがとうございます。 >asin:(asin, MonorateBuyNum, SettingProductItem, FlagSer) この組み合わせの複合インデックスはインデックス名「ureteru_get_index」として既に作成しております。「ureteru_get_index」では不十分だということでしょうか？ >analyzetime:(asin, Tick, SiteName) こちらも同様です。ご提示いただいたSQLもexplainした結果は変わらず、実行時間に差は出ませんでした。 >それから目的がキーを求めるという事であれば、サブクエリーでのorder byは不要だと思います。取得する行数は1億以上あります。 order byもlimitも使わないのなら運用できるとは思えません。すべてusing indexとなれば行数に関係なく十分な速度が出るという見立てでしょうか？どちらにせよ列の値が小さい順に値を取得する必要がありますので、order byは必要です。なお、式インデックスはまだ試せておりません。申し訳ございません。後日検証させていただきたいと思います。

sazi

2020/12/12 16:21 編集

>order byもlimitも使わないのなら運用できるとは思えません。 limitについては言及していません。 > どちらにせよ列の値が小さい順に値を取得する必要がありますので、order byは必要です。 limitするならorder byとセットにすべきだと思います。

sazi

2020/12/12 16:23 編集

>すべてusing indexとなれば行数に関係なく十分な速度が出るという見立てでしょうか？それは分かりません。先ずはやってみてだと思います。条件項目のカーディナリティなども分からないし、それぞれの項目でどの程度件数がフィルターされるのかも不明では、見立てにも限界は当然あります。

sazi

2020/12/13 04:45 編集

> この組み合わせの複合インデックスはインデックス名「ureteru_get_index」として既に作成しております。インデックスは組合せが一致してれば、良いと思われていますか？並びは重要です。並びの一致していないインデックスは効率が良く無ければ使用されません。例えば、(大分類、中分類、小分類）というインデックスに対して、（大分類、小分類、中分類）というインデックスをぶつけても、使用されないのです。反して、（大分類、中分類）のように項目が不足していても並びが同じなら使用されます。それから、(大分類、中分類、小分類）のようにカーディナリティが低いものから順に並べるとインデックスの効率が良いです。

yonotsui

2020/12/14 16:06

ありがとうございます。複合インデックスのカラム順序を正しく合わせたらexplainの結果がusing indexとなりました。十分な速度はまだ出ていませんが、これからチューニングしていく予定です。ご教授いただきありがとうございました。

行動規範の内容に同意します