MySQLの検索が遅い、インデックスが効かないときの対処法

2020/9/4 MySQL

シェアする

まずテスト用の注文テーブルとデータを用意してインデックスを作成しました。
コンソールから確認しても同じですが見やすいので今回はphpMyAdminの画面を貼ります。

-- 注文テーブル作成
CREATE TABLE test_order(
    id INTEGER AUTO_INCREMENT,
    product_no VARCHAR(5) NOT NULL,
    price INTEGER NOT NULL,
    PRIMARY KEY(id)
);
-- 注文テーブルにデータ格納
INSERT INTO test_order(product_no, price) VALUES('90001',1280);
INSERT INTO test_order(product_no, price) VALUES('90002',4980);
INSERT INTO test_order(product_no, price) VALUES('90003',4980);
INSERT INTO test_order(product_no, price) VALUES('90002',4980);
INSERT INTO test_order(product_no, price) VALUES('90001',1380);
INSERT INTO test_order(product_no, price) VALUES('90001',1480);
INSERT INTO test_order(product_no, price) VALUES('90009',9980);
INSERT INTO test_order(product_no, price) VALUES('90009',9980);
INSERT INTO test_order(product_no, price) VALUES('90009',9980);
INSERT INTO test_order(product_no, price) VALUES('90009',9980);
INSERT INTO test_order(product_no, price) VALUES('90009',9980);
-- 注文テーブルの商品番号と価格にインデックスを作成
CREATE INDEX test_order_product_no ON test_order(product_no);
CREATE INDEX test_order_price ON test_order(price);

MySQLIndex01

これだけデータが少ないと遅くなることはありえないですが
仮に遅くなったと仮定してそのクエリに「EXPLAIN」をつけると実行計画を確認できます。

EXPLAIN SELECT * FROM test_order WHERE product_no = 90001;

MySQLIndex02

possible_keysの「test_order_product_no」が使用候補となったインデックスですが
keyが「NULL」となっているためインデックスは使用されませんでした。
これではデータが多いときに検索が遅くなります。

原因1. データ型が異なっているとインデックスが利かないことがある

今回のケースは以下のようにデータ型が文字列型の列に対して文字列を条件にするよう変更するとインデックスが利きます。
MySQLでは型が合ってないとインデックスを使用してくれないようです。

EXPLAIN SELECT * FROM test_order WHERE product_no = 90001;

↓

EXPLAIN SELECT * FROM test_order WHERE product_no = '90001';

MySQLIndex03

しかし、上のように文字列型の列に対して数値を条件にした場合はインデックスが利かないようですが
数値型の列に対しては数値を条件にしても文字列を条件にしてもインデックスは利くようです。
文字列型の場合もどちらも対応してくれても良さそうですけどね・・

EXPLAIN SELECT * FROM test_order WHERE price = 4980;

MySQLIndex04
↓

EXPLAIN SELECT * FROM test_order WHERE price = '4980';

MySQLIndex05