MySQL

 

MySQLで後方一致検索を高速に行う!

MySQLの後方一致検索は確実にフルスキャンになります。インデックスが効かないので非常に低速でCPUも消費してしまいます。ちょっと発想を変えて後方一致検索でインデックスを有効にしてみましょう。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

住所が「三丁目」で終わるデータの件数をカウントしてみます。

まずはインデックスを設定せずに後方一致検索します。

mysql> select count(*) from address where town_street_name like '%三丁目';
+----------+
| count(*) |
+----------+
|    12263 |
+----------+
1 row in set (0.29 sec)

mysql> explain select count(*) from address where town_street_name like '%三丁目';
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | address | ALL  | NULL          | NULL | NULL    | NULL | 250215 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

まあ当然フルスキャンですね。

ではtown_street_nameにインデックスを設定して再度カウントします。

mysql> alter table address add key idx1 (town_street_name(191));
Query OK, 0 rows affected (1.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from address where town_street_name like '%三丁目';
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | address | ALL  | NULL          | NULL | NULL    | NULL | 260632 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

ダメですね。フルスキャンです。

後方一致という言葉に拘り過ぎるとダメですね。

まず、前方一致検索であればインデックスが有効である事を思い出して下さい。

では、後方一致を前方一致に置き換えられないか?を検討します。

できますよ!

早速順を追って説明します。

まずはカラムを一個追加します。

mysql> alter table address add town_street_name_reverse text after town_street_name;
Query OK, 0 rows affected (3.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

続いて、反転させたデータをそのカラムに登録します。

例えば「旭ヶ丘一丁目」であれば、「目丁一丘ヶ旭」に反転させます。

mysql> update address set town_street_name_reverse = reverse(town_street_name);
Query OK, 253851 rows affected (11.12 sec)
Rows matched: 253851  Changed: 253851  Warnings: 0

reverse関数で簡単に反転できます。

mysql> select * from address limit 1 \G
*************************** 1. row ***************************
                 pref_cd: 01
               pref_name: 北海道
                 city_cd: 01101
               city_name: 札幌市中央区
          town_street_cd: 011010001001
        town_street_name: 旭ヶ丘一丁目
town_street_name_reverse: 目丁一丘ヶ旭
                latitude: 43.041403
               longitude: 141.31998
1 row in set (0.00 sec)

town_street_name_reverseの「目丁一丘ヶ旭」の部分です。

このカラムに対して「目丁一」で前方一致検索したら、後方一致検索になりますよね!!

では早速試してみましょう。

追加カラムにインデックスを設定し、前方一致検索してみます。

mysql> alter table address add key idx2 (town_street_name_reverse(191));
Query OK, 0 rows affected (1.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from address where town_street_name_reverse like '目丁三%';
+----+-------------+---------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | address | range | idx2          | idx2 | 767     | NULL | 21872 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

キタ━━━━(゚∀゚)━━━━!!

やりました。typeがrangeになり、インデックスが有効になりました!!

しかし「目丁三」なんて単語をユーザに入力させる訳にはいきませんね。

ではこうしたらどうでしょう。

ユーザには「三丁目」と入力させ、システム側でその単語を反転させ「目丁三」にreverseします。

反転した検索ワードを前方一致して、「like '目丁三%'」とすれば、自然に後方一致検索になりますね!!

◯ 広告