联合索引

实践：

1、

CREATE TABLE `t0` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`left_col` INT(11) NOT NULL DEFAULT '0',
`right_col` INT(11) NOT NULL DEFAULT '0',
`nickname` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`id`) USING BTREE,
INDEX `index_left_right_nickname` (`left_col`, `right_col`, `nickname`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
AUTO_INCREMENT=3
;

1）Using index condition index_left_right_nickname

explain SELECT * FROM t0 WHERE left_col>99 AND right_col<20000 AND nickname="abc";
explain SELECT * FROM t0 WHERE right_col>99 AND left_col<20000 AND nickname="abc";
explain SELECT * FROM t0 WHERE left_col>99 AND right_col<20000 ;
explain SELECT * FROM t0 WHERE nickname="abc" AND left_col>99 AND right_col<20000;
explain SELECT * FROM t0 WHERE left_col>99 AND right_col<20000 AND nickname!="abc";
explain SELECT * FROM t0 WHERE left_col<99 AND right_col>20000 AND nickname!="abc";
explain SELECT * FROM t0 WHERE nickname="abc" AND right_col<20000 AND left_col>99;

explain SELECT * FROM t0 WHERE left_col<99 ;

explain SELECT * FROM t0 WHERE left_col<99 AND nickname!="abc";

2）Using where

explain SELECT * FROM t0 WHERE right_col>20000 AND nickname!="abc";
explain SELECT * FROM t0 WHERE nickname!="abc";
explain SELECT * FROM t0 WHERE right_col>20000;

https://mp.weixin.qq.com/s?__biz=MzIwMDgzMjc3NA==&mid=2247484811&idx=1&sn=fb702f90cdd86f5139a857b933bf438f&chksm=96f667e2a181eef4443f08cf380b0a02a38a4f08e6ff0faf69df63e6cfac8ca7babddcfac16f&token=239858186&lang=zh_CN#rd

https://mp.weixin.qq.com/s?__biz=MzIwMDgzMjc3NA==&mid=2247484720&idx=1&sn=7bd7774058e7886eeb3dedb38aa8657a&chksm=96f66759a181ee4f4c177a755c3ac6b6e97fef148bbf4afea8616f4edec33bf6d4f18cda9f69&scene=21#wechat_redirect