mysql索引优化解决方案(在b站动力节点学习的)
可能因为这个视频是比较新的视频,评论区都没有什么笔记和文档。于是我就跟着视频边学边记录笔记。希望有些建表的代码,有需要的可以直接复制,减少了大家的无效内卷时间,哈哈
# 随表建立索引
create table customer(
id int(10) auto_increment,
customer_no varchar(20),
customer_name varchar(20),
primary key(id),
unique indx_customer_no(customer_no),
key indx_customer_name(customer_name),
key indx_customer_no_name(customer_no,customer_name)
)
drop table if exists customer;
create table customer(
id int(10),
customer_no varchar(20),
customer_name varchar(20)
)
# 创建主键索引
alter table customer add primary key(id);
# 删除主键索引
alter table customer drop primary key;
# 创建唯一索引
alter table customer add unique idx_customer_no(customer_no);
# 删除唯一索引
drop index idx_customer_no on customer;
# 创建单值索引
alter table customer add index idx_customer_name(customer_name);
# 删除单值索引
drop index idx_customer_name on customer
#创建复合索引
alter table customer add index idx_customer_no_name(customer_no,customer_name);
#删除复合索引
drop index idx_customer_no_name on customer
-- 建表
DROP TABLE IF EXISTS person;
create table person(
PID int (11) auto_increment COMMENT '编号',
PNAME varchar(50) COMMENT '姓名',
PSEX varchar(10) COMMENT '性别',
PAGE int(11) COMMENT '年龄',
SAL decimal(7,2) COMMENT '工资',
primary key(PID)
);
-- 创建存储过程
create procedure insert_person(in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,concat('test',floor(rand()*10000000)),if(rand()>0.5,'男','女'),
floor((rand()*100)+10),floor((rand()*19000)+1000));
until i = max_num
end repeat;
commit;
end;
-- 调用存储过程
call insert_person(30000000);
-- 不使用索引,根据Pname进行查询
select * from person where PNAME='test7839204'
-- 给PNAME建立索引
alter table person add index idx_pname(PNAME);
-- 使用索引后,根据Pname进行查询
select * from person where PNAME='test7839204'
EXPLAIN select * from person where PNAME='test7839204';
EXPLAIN select * from person where PID='2800000'
-- 创建四张测试表
create table t1(
id int(10) auto_increment,
content varchar(100),
primary key(id)
);
create table t2(
id int(10) auto_increment,
content varchar(100),
primary key(id)
);
create table t3(
id int(10) auto_increment,
content varchar(100),
primary key(id)
);
create table t4(
id int(10) auto_increment,
content varchar(100),
primary key(id)
);
-- 每张表中添加一条数据
insert into t1(content) values(concat('t1_',floor(1+rand()*1000)));
insert into t2(content) values(concat('t2_',floor(1+rand()*1000)));
insert into t3(content) values(concat('t3_',floor(1+rand()*1000)));
insert into t4(content) values(concat('t4_',floor(1+rand()*1000)));
#id相同时,执行顺序是从上往下
EXPLAIN select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;
#id不同时
explain select t1.id from t1 where t1.id in
(select t2.id from t2 where t2.id in
(select t3.id from t3 where t3.id =1)
);
#id相同和id不同
EXPLAIN select t2.* from t2,(select * from t3) s3 where s3.id =t2.id;
-- select_type
EXPLAIN select * from t1 -- SIMPLE简单类型查询
EXPLAIN select * from (select t1.content from t1) as s1 -- DERIVED是在form列表中包含的子查询被标记成为DERIVED,mysq会递归执行这些子查询,把结果放在临时表里。
EXPLAIN select t2.* from t2 where t2.id =(select t3.id from t3);
-- type
#ALL
EXPLAIN select * from t1 where t1.content = 'abc';
#system
EXPLAIN select * from (select t1.id from t1 where id =1) t
#const 索引一次就找到了
EXPLAIN select * from t1 where id =1;
#eq_ref
EXPLAIN select t1.*,t2.*
from t1
join t2
on t1.id=t2.id
#ref非唯一索引扫描
EXPLAIN select * from t1 where t1.content = 'abc';
alter table t1 add index idx_t1_content(content)
#range
EXPLAIN select * from t2 where t2.id>0;
#index
EXPLAIN select * from t1
-- 创建员工表
drop table if exists emps;
create table emps(
id int PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
name varchar (24) COMMENT '姓名',
age int COMMENT '年龄',
job varchar (20) COMMENT '职位'
);
insert into emps(name,age,job) values('zhangsan',22,'manager');
insert into emps(name,age,job) values('lisi',23,'cleark');
insert into emps(name,age,job) values('wangwu',24,'salsman');
insert into emps(name,age,job) values('赵六',23,'salsman');
-- extra
-- using filesort 排序时没有使用索引 这个效率比较低
explain select * from emps order by age;
-- using temporary 分组时没有使用索引
explain select COUNT(*),job from emps group by job
-- using index
EXPLAIN select id from emps
-- using whrer
EXPLAIN select id from emps where id >2
-- 创建学生表
create table students(
id int primary key auto_increment comment '主键id',
sname varchar(24) comment '学生姓名',
age int comment '年龄',
score int comment '分数',
time TIMESTAMP comment '入学时间'
);
insert into students(sname,age,score,time) VALUES('小明',22,100,now());
insert into students(sname,age,score,time) VALUES('小红',23,80,now());
insert into students(sname,age,score,time) VALUES('小绿',24,80,now());
insert into students(sname,age,score,time) VALUES('黑',23,70,now());
alter table students add index idx_sname_age_score(sname,age,score);
-- 索引失效情况
-- 第一种索引失效(复合索引不遵循最左匹配原则)
EXPLAIN select * from students where sname='小明' and age =22 and score=100;
EXPLAIN select * from students where sname='小明' and age =22 ; -- key_len 数量越大表明索引使用更充分
EXPLAIN select * from students where sname='小明'
EXPLAIN select * from students where sname='小明' and score=100; -- 最左匹配原则,不遵循所以和上面一条语句索引使用率一样
-- 第二种失效情况(不准在索引列上做任何计算,函数操作,会导致索引失效而转向全表扫描)
EXPLAIN select * from students where left(sname,2)='小明'
-- 第三种失效情况(存储引擎不能使用索引中范围条件右边的列)
EXPLAIN select * from students where sname='小明' and age >22 and score=100;
-- 第四种失效情况(mysql在使用不等于时无法使用索引会导致全表扫描)
EXPLAIN select * from students where sname='小明' and age =22 and score!=100;
-- 第五种失效情况(isnull 可以使用索引,但是is not null无法使用索引)
EXPLAIN select * from students where sname is not null;
-- 第六种失效情况(like以通配符开头会使索引失效导致全表扫描)
EXPLAIN select * from students where sname like '% %';
-- 第七种失效情况(字符串不加单引号索引会失效)
EXPLAIN select * from students where sname =123
-- 第八种失效情况(使用or连接时索引会失效)
EXPLAIN select * from students where sname='小明' or age =22
-- 单表优化查询优化
create table if not exists article(
id int(10) primary key AUTO_INCREMENT,
author_id int(10) not null,
category_id int(10) not null,
views int(10) not null,
comments int(10) 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');
#1.查询category_id为1的,且comments大于1的情况下,views最多的id和author_id的信息
EXPLAIN select id,author_id from article where category_id=1 and comments>1 ORDER BY views desc limit 1;
#2.建立索引
alter table article add index index_category_id_comments_views(category_id,comments,views)
#3.再次测试
EXPLAIN select id,author_id from article where category_id=1 and comments>1 ORDER BY views desc limit 1;
#4.重新创建索引
drop index index_category_id_comments_views on article
alter table article add index index_category_views(category_id,views)
#5.再次测试
EXPLAIN select id,author_id from article where category_id=1 and comments>1 ORDER BY views desc limit 1;
-- 关联查询优化
create table if not exists class(
id int(10) auto_increment,
card int(10),
primary key(id)
);
create table if not exists book(
bookid int(10) auto_increment,
card int(10),
primary key(bookid)
);
#class表
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)));
insert into class(card) VALUES(floor(1+(rand()*20)));
#book表
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
insert into book(card) VALUES(floor(1+(rand()*20)));
#1.联表查询
EXPLAIN select *
from class
left join book
on class.card=book.card;
#2.建立索引
alter table book add index indx_card(card)
#3.测试
EXPLAIN select *
from class
left join book
on class.card=book.card;
-- 分组优化
EXPLAIN select count(*),sname from students
where sname='小明' and age>22
GROUP BY score
alter table students add index idx_sas1(sname,score);
drop index idx_sas1 on students
-- 慢查询日志
-- 查看是否开启
show variables like '%slow_query_log%'
-- 开启日志:set global slow_query_log=1;
set global slow_query_log=1;
-- 设置时间:set global long_query_time=1;
set global long_query_time=3;
-- 查看时间:show variables like 'long_query_time%'
show variables like 'long_query_time%';
-- 关闭慢查询日志
set global slow_query_log=0
学习心得
花了一个上午的时间认真的学习了一遍,感觉收获比较深刻,之前自己项目里面的代码有的sql执行了几秒钟,一直没有优化,现在学完之后知道怎么去优化sql,挺有成就感的,加油。各位
标签:insert,rand,解决方案,into,floor,索引,mysql,id,card From: https://www.cnblogs.com/zxyyds/p/16909163.html