teratail
回答率
85.46%
質問するログイン新規登録

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

新規登録して質問してみよう
ただいま回答率
85.46%
トップMySQLに関する質問
MySQL

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

Q&A

解決済

1回答

3357閲覧

MySql：テーブル2つをjoinし片方のテーブルのカラムでwhereする際にインデックスが使用されない

yonotsui
yonotsui

総合スコア28

MySQL

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

0グッド

0クリップ

投稿2021/06/08 10:37

編集2021/06/10 06:22

0

0

select * from shop_ce
inner join asin using(ASIN) 
where SellerName='	Dig outer';

上記のコマンドでテーブル「shop_ce」に対してテーブル「asin」をinnerjoinしています。
両方のテーブルにあるカラム「asin」をusingでinner joinし、shop_ceテーブルのカラム「SellerName」を固定文字列と比較しています。
asinテーブルのカラム「asin」はプライマリキー、shop_ceテーブルにはインデックス「asin_name」をカラム「ASIN」「SellerName」の順に使用して作成しています。
上記コマンドの高速化のためのインデックスは用意できているはずですが、インデックスは使用されず実行にも数時間かかります。
force indexにてインデックスを指定することはできますが、案の定処理時間改善には至りません。
なぜインデックスが使用されないのでしょうか。
ご教示いただけますと幸いです。

追記：MySQLのバージョンは8.0.18です。

##explain

select * from shop_ce
inner join asin using(ASIN) 
where SellerName='	Dig outer';

| id | select_type | table   | partitions | type | possible_keys                                                                                      | key     | key_len | ref                     | rows      | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|  1 | SIMPLE      | asin    | NULL       | ALL  | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL    | NULL    | NULL                    | 127656540 |   100.00 | NULL        |
|  1 | SIMPLE      | shop_ce | NULL       | ref  | PRIMARY,asin_name,asin,asin_image                                                                  | PRIMARY | 526     | const,imusedb.asin.ASIN |         6 |   100.00 | Using where |

//force indexの場合
select * from shop_ce force index(asin_name)
inner join asin using(ASIN) 
where SellerName='Dig outer';
| id | select_type | table   | partitions | type | possible_keys                                                                                      | key       | key_len | ref                     | rows      | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|  1 | SIMPLE      | asin    | NULL       | ALL  | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL      | NULL    | NULL                    | 127656540 |   100.00 | NULL                  |
|  1 | SIMPLE      | shop_ce | NULL       | ref  | asin_name                                                                                          | asin_name | 526     | imusedb.asin.ASIN,const |         2 |   100.00 | Using index condition |

shop_ceテーブルのCreateTable

| shop_ce | CREATE TABLE `shop_ce` (
  `ShopName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `ShopNameOpt` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `SetTime` bigint(20) DEFAULT NULL,
  `ASIN` varbinary(10) NOT NULL,
  `SiteName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `SiteImgUrl` varbinary(2083) DEFAULT NULL,
  `Name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NameOpt` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `URL` varbinary(2083) NOT NULL,
  `ImageURL` varbinary(2083) DEFAULT NULL,
  `Price` int(11) DEFAULT NULL,
  `Postage` int(11) DEFAULT NULL,
  `Used` bit(1) NOT NULL,
  `DHashSetTime` bigint(20) NOT NULL,
  `DHashDif` smallint(5) unsigned NOT NULL,
  `IsJAN` bit(1) NOT NULL,
  `PointRate` float(3,3) unsigned NOT NULL,
  `SellerName` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`SellerName`,`ASIN`,`URL`),
  KEY `site_shop` (`SiteName`,`ShopName`),
  KEY `asin_name` (`ASIN`,`SellerName`),
  KEY `asin` (`ASIN`),
  KEY `asin_image` (`ASIN`,`ImageURL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

ASINテーブルのCreateTable

| 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,
  `PriceSelfNoShip` int(11) DEFAULT NULL,
  `PriceUsedSelfNoShip` int(11) DEFAULT NULL,
  `PricePreSelfNoShip` int(11) DEFAULT NULL,
  `PricePreUsedSelfNoShip` int(11) DEFAULT NULL,
  PRIMARY KEY (`ASIN`),
  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 `category_idx` (`CategorySetTime`,`FlagSer`,`NotExit`),
  KEY `product_idx` (`ProductItemSetTime`,`FlagSer`,`NotExit`),
  KEY `fee_idx` (`FeeSetTime`,`FlagSer`,`NotExit`),
  KEY `ureteru_get_index` (`ASIN`,`MonorateBuyNum`,`SettingProductItem`,`FlagSer`),
  KEY `dhash_idx` (`DHashSetTime`,`FlagSer`),
  KEY `price_idx` (`PriceSetTime`,`FlagSer`),
  KEY `get_analyze_time_idx` (`ASIN`,`SettingProductItem`,`FlagSer`),
  KEY `keepafilter` (`MonorateSetTime`,`FlagSer`),
  KEY `price_asin_idx` (`ASIN`,`PriceSetTime`,`FlagSer`),
  KEY `ureteru_get_index2` (`ASIN`,`MonorateBuyNum`,`FlagSer`),
  KEY `noc_time_idx` (`NoCommunicationTick`),
  KEY `asin_join_flagser` (`ASIN`,`FlagSer`),
  KEY `self_buy` (`FlagSer`,`PriceCart`,`PriceSelf`,`PriceSelfNoShip`,`FeeRate`,`FixFee`,`PricePreCart`,`PriceFBA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

・追記：STRAIGHT_JOINの場合

explain select STRAIGHT_JOIN * 
from shop_ce
left join asin using(ASIN) 
where SellerName='Dig outer';
| id | select_type | table   | partitions | type | possible_keys                                                                                      | key     | key_len | ref   | rows      | filtered | Extra                                                |
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------+-----------+----------+------------------------------------------------------+
|  1 | SIMPLE      | shop_ce | NULL       | ref  | PRIMARY,sellerName                                                                                 | PRIMARY | 514     | const |         1 |   100.00 | NULL                                                 |
|  1 | SIMPLE      | asin    | NULL       | ALL  | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL    | NULL    | NULL  | 127663023 |   100.00 | Range checked for each record (index map: 0x5A40001) |

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

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

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

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

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

neko_the_shadow
neko_the_shadow

2021/06/08 12:18

利用されているMySQLのバージョンは何でしょうか? select version() などでバージョン情報は取得できるでしょうか?
yonotsui
yonotsui

2021/06/08 13:52

ありがとうございます。 SQLのバージョンは 8.0.18となります。
guest

回答1件

0

ベストアンサー

EXPLAINの結果を見て気になるのは、件数の多いテーブル→件数の少ないテーブルの順番に表示されていることです。MySQLのEXPLAINでは駆動表→内部表の順番で表示されます。つまりこのEXPLAINは件数の多い表が駆動表になっていることを示していますが、パフォーマンスの観点では、一般に駆動表には小さなテーブルを選ぶべきとされています。

WHERE節により件数の少ないshop_ceを駆動表に設定すると、パフォーマンスが改善する可能性があります。一例として、左側のテーブル→右側のテーブルの順番に読み込みを強制するSTRAIGHT_JOINを利用して、パフォーマンスが改善するかどうか確認できますでしょうか?

SQL
1select STRAIGHT_JOIN * 
2from shop_ce
3left join asin using(ASIN) 
4where SellerName='    Dig outer';

投稿2021/06/08 16:10

neko_the_shadow
neko_the_shadow

総合スコア2266

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

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

yonotsui
yonotsui

2021/06/10 06:25 編集

ご返信ありがとうございます。 ご教示いただいたSTRAIGHT_JOINを試させていただきました。 shop_ceテーブルのExtraはNULLでasinテーブルのExtraはRange checked for each recordとなり、どちらもインデックスが使用されませんでした。 念のためにそれぞれのテーブルでforce indexも試しました。 残念ながらshop_ceのExtraがUsing wherとなるだけでインデックスは使用されず速度にも変化はありませんでした。 質問文の文末にexplainの結果を記載しておりますので、お手数ですがご確認いただけますと幸いです。
neko_the_shadow
neko_the_shadow

2021/06/10 08:10

追加のEXPLAIN結果を見ると、shop_ceテーブルのkeyがPRIMARYになっていますので、shop_ceの検索にはインデックスが利用されていると思われます。 asinテーブルの検索にインデックスが利用されていないのは、おそらくshop_ce.ASINがasin.ASINの型が異なることが原因かと思います (shop_ce.ASINの型がvarchar(10)、asin.ASINの型がvarbinary(10))。 わたしの手元の環境では、shop_ce.ASINとasin.ASINの型を合わせると、asinテーブルの検索にインデックスが利用できるようです。
yonotsui
yonotsui

2021/06/11 00:43

文字列の型が違うからインデックスが使用できなかったんですね。 全く思い至りませんでした。 おっしゃる通り両方の型をvarbinary(10)に統一したらインデックスを使用できるようになりました。 ご教示いただき本当にありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。

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

ただいまの回答率
85.46%

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

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

質問する

関連した質問

トップMySQLに関する質問

MySql：テーブル2つをjoinし片方のテーブルのカラムでwhereする際にインデックスが使用されない