MySQLとMariaDBのインデックスの使い方の違い

データベース

2023.09.04

この記事は約4分で読めます。

(2023年9月)

・同じ日に約60件の(日以外は)異なるレコードが格納され、それが連日で続くようなテーブル。
・約30万レコード、非圧縮のsqlファイルで30MB程度。
・カラム`id`は一意であり、PRIMARYが設定されている。
・カラム`day`にはDATE型で年月日(例：2023-09-01)が入っている。
・カラム`day`は、上述のように一意ではない。
・カラム`day`には、indexが設定されている。
・カラム数は`id`と`day`を含んで13ある。

この中から、2023年の日数を求める。

以下のSQL文をEXPLAIN付きで実行し、インデックスの使用状況を見る。

MySQL 5.7

①SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE `day` LIKE ‘2023-%’

↑MySQL①

②SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE DATE_FORMAT(`day`,’%Y’) = ‘2023’

↑MySQL2

③SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE `day` LIKE ‘%023-%’

↑MySQL③

結果は①②③全て同じ。

「type」が「range(インデックスを用いた範囲検索)」。

「possible_keys(使用されるインデックスの候補として挙げたキー)」が`day`であり、かつ、「key(選択されたキー)」も`day`なので、`day`に設定したインデックスが使用されていることが分かる。

②のようにDATE_FORMAT(MySQLの関数)を使うと、以前は「type」が「index(フルインデックススキャン：インデックス全体をスキャンする)」となり非常に遅くなったが、今は本件のように「range(インデックスを用いた範囲検索)」となり、遅くはナラナイ。

③の頭に%が入る(後方一致)の場合でもインデックスが効いているが、これは`date`がDATE型だからである。

varcharなどとは違い、日付しか入らないことが分かっているためだ。

MariaDB 10.5

①SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE `day` LIKE ‘2023-%’

↑MariaDB①

②SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE DATE_FORMAT(`day`,’%Y’) = ‘2023’

↑MariaDB②

③SELECT COUNT(DISTINCT `day`) AS `cnt` FROM `(テーブル名)` WHERE `day` LIKE ‘%023-%’

↑MariaDB③

結果が①(②=③)となっており、MySQLの時とは異なる。

②③は「possible_keys」がNULLであるが、「key」は`day`であり、インデックスが使われている。

結果

よく分からないｗｗｗｗｗ

`varchar`にインデックスを設定して「WHERE `varchar` = ‘〇〇〇’」とした場合、

MySQL 5.7だと

・type：ref
・possible_keys：varchar
・key：varchar

となってインデックスが使われるが、

MariaDB 10.5だと

・type：ALL
・possible_keys：varchar
・key：NULL

となり、インデックスを使わないｗｗｗｗｗ

日付及び時間関数

なお「WHERE DATE_FORMAT(`day`,’%Y’) = ‘2023’」は、年を返す「WHERE YEAR(`day`) = ‘2023’」と同じである。

同様に、DATE(年月日を返す)、MONTH(月のみを返す)、DAY(日のみを返す)及びDAYOFMONTH(同)や、TIME(時分秒を返す)、HOUR(時のみを返す)、MINUTE(分のみを返す)、SECOND(秒のみを返す)等もある。

・SELECT DATE(‘2023-09-01 12:34:56’) ;
・SELECT YEAR(‘2023-09-01 12:34:56’) ;
・SELECT MONTH(‘2023-09-01 12:34:56’) ;
・SELECT DAY(‘2023-09-01 12:34:56’) ;
・SELECT TIME(‘2023-09-01 12:34:56’) ;
・SELECT HOUR(‘2023-09-01 12:34:56’) ;
・SELECT MINUTE(‘2023-09-01 12:34:56’) ;
・SELECT SECOND(‘2023-09-01 12:34:56’) ;