一、确认数据库索引信息

MySQL [bonnie]> show index from user;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4979256 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

二、 实际场景

查询语句中，存在运算符。实际运算后的列，不能使用索引

2.1 确认“ select * from user where id+1=2;”的执行过程

explain select * from user where id+1=2;

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4979256 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

执行该条语句，需要遍历4979256 行数据
type是all

2.2 实际执行查看运行时间

select * from user where id+1=2;

+----+-------+-----+-----+---------------+---------+------------+------------+
| id | name  | sex | age | email         | address | company    | city       |
+----+-------+-----+-----+---------------+---------+------------+------------+
|  1 | PlNej |   1 |  77 | TbCFvn@qq.com | bu      | 1604932650 | 1604932650 |
+----+-------+-----+-----+---------------+---------+------------+------------+
1 row in set (2.62 sec)

实际运行后，一条语句运行了2s+

2.3 对比，看下不加计算的运行效果

 select * from user where id=1;
+----+-------+-----+-----+---------------+---------+------------+------------+
| id | name  | sex | age | email         | address | company    | city       |
+----+-------+-----+-----+---------------+---------+------------+------------+
|  1 | PlNej |   1 |  77 | TbCFvn@qq.com | bu      | 1604932650 | 1604932650 |
+----+-------+-----+-----+---------------+---------+------------+------------+
1 row in set (0.01 sec)

去掉运算“+”，直接给出运算结果，一条语句只执行了0.01s

2.3 对比，看下不加计算的执行过程

explain select * from user where id=1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

只需要查看1行，并切type的类型是const