mysql进阶（三）31-43

1.优化案例1——单表

案例使用的表：

-- 31讲----------------------------------------------------------
create table if not exists `article`(
	`id` int(10) unsigned not null primary key auto_increment,
	`author_id` int(10) unsigned not null ,
	`category_id` int(10) unsigned not null,
	`views` int(10) unsigned not null,
	`comments` int(10) unsigned not null,
	`title` varbinary(255) not null,
	`content` text not null
);

insert into `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content` ) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

 

.

由于type是all,而且Extra是使用了using filesort，如何优化？

第一种：将category_id,comments,views设为索引；优化成range

 

第二种方式：将category_id,views设为索引，此时type变为ref,而using filesort也消失了

优化案例2——多表

-- 32讲----------------------------------------------------------
create table if not exists `class`(
	`id` int(10) unsigned not null  auto_increment,
	`card` int(10) unsigned not null,
	primary key (`id`)
);
create table if not exists `book`(
	`bookid` int(10) unsigned not null  auto_increment,
	`card` int(10) unsigned not null,
	primary key (`bookid`)
);

insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));
insert into `class`(card ) values (floor(1+(rand()*20)));

原因分析：由于在左连接中，left join 条件用于确定如何从右表中搜索行，左边表的行一定都存在，索引右边表才是我们关键的地方。

索引优化案例3：三表优化

用到的表：

create table if not exists `phone`(
	`phoneid` int(10) unsigned not null  auto_increment,
	`card` int(10) unsigned not null,
	primary key (`phoneid`)
);

insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));
insert into `phone`(card ) values (floor(1+(rand()*20)));

 

2.索引优化

主要讨论下面索引失效的原因：

涉及到的表结构：

CREATE TABLE `staffs`(
	id int primary key auto_increment,
	name varchar(24) not null default "" comment'姓名',
	age int not null default 0 comment '年龄',
	pos varchar(20) not null default ""  comment'职位',
	add_time timestamp not null default current_timestamp comment '入职时间'
	)charset utf8 comment '员工记录表';

	
insert into staffs(name,age,pos,add_time) values('z3',22,'manage',now());
insert into staffs(name,age,pos,add_time) values('july',23,'dev',now());
insert into staffs(name,age,pos,add_time) values('2000',23,'dev',now());

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

①全值匹配    ：精度越高，所使用key_len越大，使用了覆盖索引；

 

②最佳左前缀法则  如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

 演示 ，没有用到索引的情况 ：

③不要在索引列上做任何操作（计算、函数、（自动或者手动）类型转换），会导致索引失效而转向全表扫描。

④存储引擎不能使用索引中范围条件右边的列。

也就是说在原来表的索引是（name,age,pos），在下面的查询中age>23,是索引的第二个字段，它的右边pos列就失效了

⑤尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

⑥mysql使用不等于（！=或者<>）的时候无法使用索引导致全表扫描；

下面是用了，覆盖索引和非覆盖索引

⑦is null,is not null也无法使用索引

⑧like通配符开头（“%abc”）mysql索引失效会变成全表扫描的操作。

最好将like的通配符放在最右边

如果like的通配符必须放在开头，可以结合覆盖索引来优化；

涉及到的表：

CREATE TABLE `tb1_user`(
	id int not null auto_increment,
	name varchar(20)  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 tb1_user(name,age,email) values ('1aa1','21','b@163.com');
insert into tb1_user(name,age,email) values ('2aa2','222','a@163.com');
insert into tb1_user(name,age,email) values ('3aa3','265','c@163.com');
insert into tb1_user(name,age,email) values ('4aa4','21','d@163.com');

建立索引：

 create index idx_nameAge on tb1_user(name,age);

⑨字符串不加单引号索引会失效

⑩少用or,用它会使索引失效。 

总结：

对于上面like案例的验证：索引是（name,age,pos）