大量データの検索速度を上げる適切なチューニングについて

Question

いつもお世話になっております。
大量のデータ(1億とか)から1件のデータを検索する際に何かしら早く検索する方法はないのか知りたくて質問をさせて頂きます

CREATE TABLE IF NOT EXISTS `serials` (
  `id` int(11) NOT NULL auto_increment,
  `serial_code` char(12) BINARY NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_code` (`serial_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

このテーブルにcsvデータの流し込みで1億件のデータがあるとします。
普通に

SELECT * FROM `serials` where serial_code=1111111111111;

で検索すると検索に時間がかかってしまいます。explainで確認したところ

+----+-------------+---------+-------+---------------+------------+---------+------+----------+--------------------------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows     | Extra                    |
+----+-------------+---------+-------+---------------+------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | serials | index | serial_code   |serial_code | 36      | NULL | 40413015 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------+---------+------+----------+--------------------------+

typeがindexとなりフルスキャンではないですがやはり遅くはなってしまいます。
これをidで検索すると一瞬で表示され、typeもconstになります。

だいたい大量のデータから検索する場合はINDEXを貼るのが有効みたいな記事をよく見るのですが、numberをユニークキーに設定しているのでINDEXを貼っている認識ですが間違いないでしょうか？
また何か出来る設定でnumberの検索を早く出来る方法があればご教授頂きたいと思います。

なお、実際はPHPでシステムを組んでいて、シリアルナンバーをフォームで入力してそのシリアルがserialsテーブルとserial_registration_historiesテーブルに存在しているかどうかのチェックを行っています。ですので

CREATE TABLE IF NOT EXISTS `serials` (
  `id` int(11) NOT NULL auto_increment,
  `serial_code` char(12) BINARY NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_code` (`serial_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `serial_registration_histories` (
  `id` int(11) NOT NULL auto_increment,
  `serial_id` int(11) NOT NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `serial_id` (`serial_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

このテーブルにserialsテーブルにはデータを流し込み、serial_registration_historiesにはserialsテーブルには存在して

`serial_registration_histories`にはないシリアル情報を格納するデータが存在しているのですが、この複数のテーブルに対して
SELECT `Serial`.`id`, `SerialRegistrationHistory`.`id` FROM `テーブル名`.`serials` AS `Serial` LEFT JOIN `テーブル名`.`serial_registration_histories` AS `SerialRegistrationHistory` ON (`Serial`.`id` = `SerialRegistrationHistory`.`serial_id`) WHERE `Serial`.`serial_code` = '719653552612' LIMIT 1

このSQLを投げています。このSQLが遅くならなければいいのですが、これをexplainをつけると

+----+-------------+---------------------------+-------+---------------+------------+---------+-------+------+----------------------+
| id | select_type | table                     | type  | possible_keys | key        | key_len | ref   | rows | Extra                |
+----+-------------+---------------------------+-------+---------------+------------+---------+-------+------+----------------------+
|  1 | SIMPLE      | Serial                    | const | serial_code   | serial_code| 36      | const |    1 | Using index          |
|  1 | SIMPLE      | SerialRegistrationHistory | const | serial_id     | serial_id  | 4       | const |    0 | unique row not found |
+----+-------------+---------------------------+-------+---------------+------------+---------+-------+------+----------------------+

このような結果になってtypeがconstになっているのでそこまで遅くならないとは思うのですが正直なんで単純なsqlはindexでこれがconstになっているのか不明です。

質問が多くなって申し訳ありませんが、よろしくお願いします

※修正点
命名が良くないと指摘を受けたので修正しました。(number→serial_code)
頂いた回答から上半分の単純なSELECT文の検索が遅いと言う点は型変換を行って検索を行っているので遅いというのがわかりましたので解決済みです。

Comments

[Orlofsky]

>`number` char(12) BINARY NOT NULL,
文字型のカラムに数値型のカラム名を付けるのは止めませんか？ネーミングのセンスがないし、コードを追う気が失せます。

[test_87097]

確かに命名って大事ですよね。次回から気をつけます。不快にさせてしまったら申し訳ないです

[Orlofsky]

だから、
>SELECT * FROM serials where number=1111111111111;
で型変換に気が付かない墓穴を掘っているんです。

今回から直すという選択肢もあります。

[Orlofsky]

explain も https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

[test_87097]

修正しました

[Orlofsky]

SELECT も https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

Answer 1

型変換が入っているためにインデックスが正しく使えない、なんてことはないでしょうか。

試しにSELECT * FROM serials where number='1111111111111';と、文字列として検索してみてください。

Comments

[maisumakun]

実データの方は文字列で検索してconstになっているので、この線が有力な気もします。

[test_87097]

ありがとうございます。完全にそのとおりでした。
上記のSELECT文でしたら一瞬で表示されました。

[退会済みユーザー]

パフォーマンスの良いSQLを記述しよう [SQLServer] 2. 暗黙の型変換
https://qiita.com/tsuyo_pon/items/c0eea1008771324c318f#2-%E6%9A%97%E9%BB%99%E3%81%AE%E5%9E%8B%E5%A4%89%E6%8F%9B

SQLServerの記事ですが、どのデータベースでも通用する内容です。
本件の「暗黙の型変換」以外も目を通しておくといいです。

Answer 2

serial_idがセカンダリだからじゃないでしょうか
serial_idは**UNIQUE **で　NOT NULLなんだから、**PRIMARY KEY **にすれば、改善されないでしょうか。

CREATE TABLE IF NOT EXISTS `serial_registration_histories` (
  `serial_id` int(11) NOT NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`serial_id`)
)

Answer 3

シンプルに

SELECT `S`.`id`
     , `SRH`.`id`
FROM `serials` AS `S`
LEFT JOIN `serial_registration_histories` AS `SRH`
ON `S`.`id` = `SRH`.`serial_id`
WHERE `S`.`serial_code` = '719653552612' LIMIT 1

Answer 4

知って得するInnoDBセカンダリインデックス活用術！を見るとプライマリキーと他のインデックスとの違いが分かると思います。

・・・ってか、numberって文字列だったんかーーーいっ！
同じ数値でも、「数値として」と「文字列として」の長さが違うので、検索が遅くなるのは当たり前です。

Comments

[test_87097]

参考サイトありがとうございます。
すいません、上記の単純なSQLに関しては単純なミスで恥ずかしいです…
numberが文字列もおかしいので今後気をつけます