索引下推

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'