今天在公司上班期间,发现一个很奇怪的问题。就是sql表中如果字段类型为字符串类型,而我们在写sql语句的时候用的确实整型的时候。在性能上会出现问题。接下来我们来看测试
表结构如下:
CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_num` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT '订单编号',
`addr_id` int(11) NOT NULL COMMENT '收货地址ID',
`send_time` int(11) NOT NULL COMMENT '预计发货时间',
`time_space` int(11) DEFAULT NULL COMMENT '预计几天到货',
`send_price` int(11) NOT NULL COMMENT '配送费用',
`coupon_id` int(11) DEFAULT NULL COMMENT '优惠券ID',
`pay_status` int(11) NOT NULL COMMENT '1表示未支付,2表示已支付,3表示已退款',
`pay_by` int(11) NOT NULL DEFAULT '1' COMMENT '支付方式',
`pay_time` int(11) DEFAULT NULL COMMENT '支付时间',
`order_status` int(11) NOT NULL DEFAULT '1' COMMENT '订单状态。包含1待付款,2待发货,3待收货,4已取消',
`created_at` int(11) NOT NULL COMMENT '创建的时间',
`user_id` int(11) NOT NULL COMMENT '用户的id',
`agent_id` int(11) DEFAULT NULL COMMENT '代理的id',
`goods_id` int(11) DEFAULT NULL COMMENT '商品id',
`goods_num` int(11) DEFAULT NULL COMMENT '商品数量',
`logistics_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '物流号',
`express_id` int(11) DEFAULT NULL COMMENT '快递id',
`pay_id` int(11) DEFAULT '0' COMMENT '支付的id',
`combine_pay_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '合并支付的id',
`is_del` tinyint(1) DEFAULT '0' COMMENT '0表示没有删除,1表示删除',
`transaction_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '微信支付订单号',
`order_price` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `订单创建的时间` (`created_at`) USING BTREE,
KEY `agent_id` (`agent_id`) USING BTREE,
KEY `logstics_code` (`logistics_code`) USING BTREE,
KEY `user_id` (`user_id`) USING BTREE,
KEY `transaction_id` (`transaction_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='订单表'
在transaction_id字段我建立了一个辅助索引,并且该字段的类型为varchar
当我尝试这样写sql语句,用explain查看性能的时候
explain select * from `order` where transaction_id=4008622001201709131815785475;
结果如下:
我们从结果发现,我们建立的索引并没有用到,在查询数据库的时候,导致了全表扫描(表里面一共就132条数据)
于是乎,我又测试像下面这样写
explain select * from `order` where transaction_id= '4008622001201709131815785475';
结果如下:
从结果看来,这次我们用到了表中建立的索引,并且只扫描了一行就得到了数据。
原因分析:
因为数据表中的字段类型实际上是为String类型,而我们传值过去的时候如果是整型,数据库会把String类型的字段先变成整型再来比较字段的值。因为数据库不知道转换后的值是多少,因此会导致全表扫描。所以我们在写sql语句的时候,要注意表中的字段类型。