一、确认数据库索引信息
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
