mysql进阶（三）31-43

以你之姓@ 2022-05-18 00:54 209阅读 0赞

### 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');

## ##

.![70][]

![70 1][]

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

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

![70 2][]![70 3][]

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

![70 4][]![70 5][]

### 优化案例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)));

![70 6][]

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

![70 7][]

索引优化案例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)));

![70 8][]

### 2.索引优化 ###

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

![70 9][]

涉及到的表结构：

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越大，使用了覆盖索引；

![70 10][]

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

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

![70 11][]

![70 12][]

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

![70 13][]

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

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

![70 14][]

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

![70 15][]

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

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

![70 16][]

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

![70 17][]

![70 18][]

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

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

![70 19][]

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

![70 20][]

**涉及到的表：**

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);

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

![70 21][]

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

![70 22][]

总结：

![70 23][]

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

![70 24][]

[70]: /images/20220518/c0283afb66fd438e9f18219cc9e1cd38.png
[70 1]: /images/20220518/fa3dec74784547d2abdb36297de2ed61.png
[70 2]: https://img-blog.csdn.net/20180727153100671?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3ODYyMzM1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 3]: /images/20220518/0c26d4b2eb1d4d00a1b65f89b1972b06.png
[70 4]: https://img-blog.csdn.net/20180727153350310?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3ODYyMzM1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 5]: /images/20220518/1205381368474dc8a4a2ba6f09d263ab.png
[70 6]: /images/20220518/d6d6ee9c021e44aba5192ce47104da02.png
[70 7]: /images/20220518/cb2fac095ab54a3fa8afc3a8553fae8d.png
[70 8]: /images/20220518/fc6fb80e3ef748bbba02c73842dc37f8.png
[70 9]: /images/20220518/d8bd602b15d749d79a93d080bf26c403.png
[70 10]: /images/20220518/7e8d95cb34bc4838ace92a8290c71932.png
[70 11]: /images/20220518/78fb8a0c886b461991e87ea94395ff0f.png
[70 12]: /images/20220518/fec3e1ae516441ebaec491b7d5daef28.png
[70 13]: /images/20220518/cde7fb33ca4f447f9988bec3c716a6e8.png
[70 14]: /images/20220518/0c5bee96de154413bb45d139f99532a0.png
[70 15]: /images/20220518/36e36ece09fd4211ad680a18d714dc29.png
[70 16]: /images/20220518/4ae80138e91c4532a64bf533bd361e92.png
[70 17]: /images/20220518/9ef0ab6416f04ffda6d125b7edd626dd.png
[70 18]: /images/20220518/104d3b108d974f54bc1f5ea2e204b528.png
[70 19]: /images/20220518/bc97e987b29d4b638e224e216b2e9cb3.png
[70 20]: /images/20220518/9a1a3dbef84143acbcaa3ae30c73fcb1.png
[70 21]: /images/20220518/8728020a105845e09cdb840c993b7f2d.png
[70 22]: /images/20220518/7caa4987aaa343e5ad66b7924b87b490.png
[70 23]: /images/20220518/99613494d26a464a98803510296bf65c.png
[70 24]: /images/20220518/ed8e535ec2be4a8782da640f75756700.png