MySql（十七）--索引优化

 

DROP TABLE IF EXISTS `staffs`;
CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  `pos` varchar(20) NOT NULL,
  `add_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into staffs(name,age,pos,add_time) values ('z3',22,'manager',NOW());
insert into staffs(name,age,pos,add_time) values ('july',23,'dex',NOW());
insert into staffs(name,age,pos,add_time) values ('2000',23,'manager',NOW());

alter table staffs add index idx_staffs_nap(name,age,pos);

 

如下三个sql用到了索引

如下两个sql没用到索引

这就是

口诀1：带头大哥不能死（最左前列开始）。

中间的索引字段没用，那么就相当于只用了一部分索引，第一字段进行索引。

口诀2：中间兄弟不能断（不跳过索引中的列）。

 

口诀：范围之后全失效

比如in、between  and、>、<这些之后的索引字段都失效。

最后一条sql，name用于查找，age用于排序。

 

口诀：like百分号写右边

建表

CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  `email` VARCHAR(20)  DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into tbl_user(name,age,email) values ('1aa1',21,'b@123.com');
insert into tbl_user(name,age,email) values ('2aa2',222,'a@163.com');
insert into tbl_user(name,age,email) values ('3aa3',265,'6@163.com');
insert into tbl_user(name,age,email) values ('4aa4',21,'d@163.com');

这种情况，左右两边都要有%。

没建索引的时候，全表扫描

建索引

实验证明，覆盖索引，可以解决左右百分号全表扫描的问题。

name是varchar类型，但是有个值是2000。条件查询的时候，这个2000的值可以有单引号也可以没有。接下来我们看两种情况的区别。

如果不用单引号，就会把2000从整型自动转换成字符型。对应上面的第3条规则（不能有隐式的类型转换）。