索引下推
2024-01-21 本文已影响0人
wuyuan0127
1. create table
CREATE TABLE `demo` (
`id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
`address` VARCHAR(10) NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
`status` TINYINT(1) NULL DEFAULT '1',
`num` INT(10) NULL DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
INDEX `ix_pnnc` (`status`, `address`, `name`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;
2. query sql：
explain SELECT * FROM demo WHERE `status`=1 AND `name` LIKE '%小红%' AND address='beijing'
