表结构
CREATE TABLE `llt_orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`type` tinyint(3) unsigned NOT NULL,
`order_id` int(10) unsigned DEFAULT NULL,
`order_number` bigint(20) DEFAULT NULL,
`pharmacy_id` int(10) unsigned DEFAULT NULL,
`user_id` int(10) unsigned NOT NULL,
`subtotal` decimal(10,2) NOT NULL,
`vendor_id` int(10) unsigned DEFAULT 0,
`note` text COLLATE utf8mb4_unicode_ci NOT NULL,
`count_status` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `orders_order_number_unique` (`order_number`),
KEY `orders_user_id_foreign` (`user_id`),
KEY `orders_order_id_foreign` (`order_id`),
KEY `orders_pharmacy_id_foreign` (`pharmacy_id`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6091 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
没命中索引
EXPLAIN SELECT * from llt_orders WHERE vendor_id in ( 13 , 44 )
EXPLAIN SELECT * from llt_orders WHERE vendor_id > 13 and vendor_id < 44
EXPLAIN SELECT * from orders WHERE vendor_id BETWEEN 13 and 44
这个命中索引
EXPLAIN SELECT * from llt_orders WHERE vendor_id in ( 21 , 45 );