裏MySQLクエリー入門(25) 基礎編14 俺々インデックスの作り方(関数インデックス)

MySQLでは、関数インデックスが使えません。そこで擬似的に関数(式)インデックスを作ってみることにしましょう。ユーザテーブルでメールアドレスを小文字にした時の関数インデックスを作る場合を例としてみます。メールアドレスをユニークにしたものの、大文字・小文字を区別するプロバイダが有る為にオリジナルなメールアドレスはそのまま取っておき、検索時はLower(小文字化)して検索する。そうするとインデックスが効かなくて大弱りという有りがちな事例ですね。

例によってテーブル定義とかサンプルデータ準備

CREATE TABLE  `user` (
`user_id` INT                   NOT NULL AUTO_INCREMENT PRIMARY KEY 
                                         COMMENT  'ユーザID',
`email`   VARCHAR( 255 ) binary NOT NULL COMMENT  'メールアドレス',
UNIQUE (
`email`
)
) ENGINE = MYISAM COMMENT =  'ユーザ';

100万件のレコードダミーデータを生成

call insert_generate_series('user(email)','concat(replace(substring(encrypt(?,RAND()),3),"/",""),"@example.com")',1,1000000,1);

※ insert_generate_series
実行した結果こんな感じのデータが100万件出来上がります。

ussr_id	email
9991	4WsdRCdRgb.@example.com
9992	iSCM2PvASM6@example.com
9993	CszDN2k99VQ@example.com
9994	jXSRi8EYVn6@example.com
9995	bZNr7Ps9132@example.com
9996	9xoEh6NgrsY@example.com
9997	Yyp4Cwoh2Dc@example.com
9998	ZJxm097.2MU@example.com
9999	QfMBRZfQOZQ@example.com
10000	BJ.P1HKPzCs@example.com

SELECT SQL_NO_CACHE * 
FROM user
WHERE LOWER( email ) = LOWER(  'k8ojG7kzJa2@example.com' )

0.5627 秒

explain

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ALL	NULL	NULL	NULL	NULL	1000000	Using where

SELECT SQL_NO_CACHE * 
FROM user
WHERE email  = 'k8ojG7kzJa2@example.com'

0.0125 秒

explain

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	const	email	email	512	const	1

関数インデックスもどきを作る

インデックスはテーブルの中にそのキーに基づいたテーブルがもう1つあるのと同じと考えれば、そのテーブルのインデックス用にテーブルをもう一個作って、INSERTトリガーを付けてあげれば擬似的にインデックスが作れるはず。ここ重要ですテストに出ます。これが理解できれば、勝手に転置インデックスや、MySQLをスキーマレスのKVSとして使うときに無停止で擬似インデックスの張替えを行ったりと、ビットマップインデックスを勝手に作ったり、俺々インデックスを勝手に定義できたりします。

CREATE TABLE  `user_i` (
`lc_email` VARCHAR( 255 ) BINARY NOT NULL COMMENT  'メールアドレス(小文字)',
`user_id` INT NOT NULL COMMENT  'ユーザID',
PRIMARY KEY (  `lc_email` ,  `user_id` )
) ENGINE = MYISAM COMMENT =  'ユーザインデックス';

INSERT INTO user_i (lc_email,user_id) SELECT lower(email),user_id FROM user

1000000 行挿入しました。 ( クエリの実行時間 36.5448 秒 )

SELECT SQL_NO_CACHE u . * 
FROM user u
INNER JOIN user_i i ON u.user_id = i.user_id
WHERE i.lc_email = LOWER(  'k8ojG7kzJa2@example.com' )

0.0110 秒

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	i	ref	PRIMARY	PRIMARY	512	const	2	Using where; Using index
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	i.user_id	1

トリガーを張る

DROP TRIGGER IF EXISTS user_index
DELIMITER //
CREATE TRIGGER user_index
AFTER INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO user_i (lc_email,user_id)
         VALUES (lower(NEW.email), NEW.user_id);
END;
//
DELIMITER ;