🎄teratailクリスマスプレゼントキャンペーン2024🎄』開催中!

\teratail特別グッズやAmazonギフトカード最大2,000円分が当たる!/

詳細はこちら
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

1回答

4240閲覧

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

yonotsui

総合スコア28

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2020/12/11 16:37

編集2020/12/13 16:05

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 |

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Orlofsky

2020/12/11 17:11

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

回答1

0

ベストアンサー

そもそもですが、[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となりました。 十分な速度はまだ出ていませんが、これからチューニングしていく予定です。 ご教授いただきありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.36%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問