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

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) |

neko_the_shadow

2021/06/08 12:18

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

yonotsui

2021/06/08 13:52

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

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

回答1件

ベストアンサー

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

総合スコア2349

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

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

2021/06/11 00:43

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

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