sqlのwhereでフィールドを変換しない(関数変換/オペレータ変換/暗黙変換)---インデックスが失効し、遅いクエリが発生する


例を見てみましょう.
mysql> show create table tb_test;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_test | CREATE TABLE `tb_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
  `name` varchar(32) NOT NULL COMMENT 'test',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',
  PRIMARY KEY (`id`),
  KEY `id_score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=10005 DEFAULT CHARSET=utf8 COMMENT='   ,      '          |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

scoreはインデックスであり、引き続き見てみましょう.
mysql> select * from tb_test where score = 1;        
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | n1   |     1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> select * from tb_test where score + 1 = 2;        
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | n1   |     1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+

|    10061 | 0.00076248 | select * from tb_test where score = 1             |
|    10062 | 0.00316482 | select * from tb_test where score + 1 = 2         |
+----------+------------+---------------------------------------------------+
15 rows in set (0.00 sec)

時間の差が大きいのはなぜですか?sqlのwhereフィールドを変換(関数変換/オペレータ変換/暗黙変換)すると、インデックスが無効になります.explainに来てみましょう.
mysql> explain select * from tb_test where score = 1;    
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
| id   | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
|    1 | SIMPLE      | tb_test | ref  | id_score      | id_score | 4       | const |    1 |       |
+------+-------------+---------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from tb_test where score + 1 = 2;
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | tb_test | ALL  | NULL          | NULL | NULL    | NULL | 10224 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

typeを見ると、後者は全テーブル検索を行い、つまりインデックスが失効したことは明らかです.keyやrowsを見ても結果がわかります.
避けなければならない.