原始sql如下:
mysql> select * from gso WHERE express_generation_sn = 160425135761159 LIMIT 1;
1 row in set (1.15 sec)
mysql> explain select * from gso WHERE express_generation_sn = 160425135761159 LIMIT 1;
+----+-------------+----------------+------+-----------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+-----------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | gso | ALL | express_generation_sn | NULL | NULL | NULL | 390755 | Using where |
+----+-------------+----------------+------+-----------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
相当简单的sql执行需要1.15s
原因:
mysql> select * from gso WHERE express_generation_sn = '160425135761159' LIMIT 1;
1 row in set (0.00 sec)
mysql> explain select * from gso WHERE express_generation_sn = '160425135761159' LIMIT 1;
+----+-------------+----------------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | gso | ref | express_generation_sn | express_generation_sn | 153 | const | 1 | Using where |
+----+-------------+----------------+------+-----------------------+-----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
到这里一眼也就知道拉,express_generation_sn 是varchar类型,所以开发规范很重要!!
也知道不是所有带sn,num的都是数字字段,不能想当然