首页 > 数据库 >mysql索引优化解决方案

mysql索引优化解决方案

时间：2022-11-20 18:46:48浏览次数：41  
标签：insert rand 解决方案 into floor 索引 mysql id card

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

相关文章

  • 为NAS文件创建目录索引
    技术工坊NAS虽为局域网内的文件共享提供了便利，但大体量文件的管理又成了件麻烦事。市面上的很多NAS不像面面俱到的群晖，能附带一套完善（臃肿）的管理套件。它们往往只是个带基......
  • MySQL子查询
    MySQL子查询什么是子查询subquery，通过select查询结果当作另外一条select查询的条件或者数据源子查询的分类根据子查询出现的位置分类from子查询:子查询出现在from后......
  • MYSQL连接字符串参数解析
    最新在重新使用MySQL数据库，发现读取数据库时，tinyint类型的值都被转化为boolean了，这样大于1的值都丢失，变成true了。查阅资料MySQL中无Boolean类型，都是存储为tinyint了，这也无......
  • Mysql介绍
    1.Mysql介绍   •   MySQL是一款开源的关系型数据库管理系统,由瑞典MySQLAB公司1995年研发   •   2008年被Sun公司收购,2009年Sun公司被Oracle公司收......
  • mysql root权限恢复
    让公司运维禁止root远程连接，结果公司运维今天把mysql所有权限给去除了，导致线上程序挂了，远程也连不上数据库，哎，心累，我查了mysql.user中所有root权限都是N,导致远程、本地都无......
  • 【错误记录】Visual Studio 2019 中运行 Unity C# 脚本时报错 ( 根据解决方案, 可能需
    文章目录​​一、报错信息​​​​二、解决方案​​​​三、VisualStudio2019中运行UnityC#脚本需要的组件​​​​1、.NET桌面开发​​​​2、使用Unity的游戏开......
  • Mysql数据库引擎MYISAM和INNODB详解
    一、数据库​​引擎​​数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程......
  • （二）mysql一主一从
    1、环境说明操作系统：centos7.9mysql版本：mysql5.7安装方式：yum源安装2、环境准备准备两台全新的机器完成系初始化操作关闭防火墙关闭selinux配置yum源安装mysq......
  • MySQL知识点(一)
    MySQL知识点(一)目录MySQL知识点(一)一、B树和B+树之间的区别是什么?1、B树2、B+树二、Innodb中的B+树是怎么产生的？三、高度为3的B+树能存多少条数据?四、Innodb引擎是如......
  • mysql字段类型大小了解
    转自：https://www.kancloud.cn/thinkphp/mysql-design-optimalize/393251.数值类型 可以通过它来计算查询结果集所占大小，一行数据占的内存大小。2.字符串类型 3.时......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99