MySQLでAES_ENCRYPTとAES_DECRYPTでvarbinary型のINDEX動作 #MySQL

前提

AES_ENCRYPTとAES_DECRYPTを使いvarbinary型へのデーター保存で暗号化

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mail` varbinary(768) NOT null,
  PRIMARY KEY (`id`),
  KEY `mail` (`mail`(112))
);

登録

INSERT INTO `user` SET
`mail` = AES_ENCRYPT('hoge@hoge.com','鍵文字列');

複合

SELECT
AES_DECRYPT(`mail`,'鍵文字列')
FROM `user`;

検索

INDEX効く

mysql> EXPLAIN SELECT * FROM `user` WHERE `mail` = AES_ENCRYPT('hoge@hoge.com','鍵文字列');
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | mail          | mail | 114     | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

INDEX効かない

mysql> EXPLAIN SELECT * FROM `user` WHERE AES_DECRYPT(`mail`,'鍵文字列') = 'hoge@hoge.com';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)