8个MYSQL索引优化实战

最新推荐文章于 2024-05-16 23:12:49 发布

计忆芳华

最新推荐文章于 2024-05-16 23:12:49 发布

阅读量600

点赞数 1

分类专栏： # 数据库学习

本文链接：https://blog.csdn.net/qq_43925089/article/details/109055569

版权

数据库学习专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文通过创建测试小表和大表，展示了SQL查询门派成员信息的各种场景，包括掌门年龄比较、门派平均年龄对比、门派成员数量统计等，并进行了查询优化，如使用LEFT JOIN、INNER JOIN、GROUP BY、HAVING子句，以及创建索引来提高查询效率。此外，还讨论了STRAIGHT_JOIN的使用以及在不同数据库版本中的表现。

摘要由CSDN通过智能技术生成

1. 创建测试小表

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11)DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT`fk_dept_id'FOREIGN KEY (deptld )REFERENCES 't_dept (id")
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES
('华山','华山'),
('丐帮','洛阳'),
('峨眉','峨眉山'),
('武当','武当山'),
('明教','光明顶'),
('少林','少林寺');

INSERT INTo t_emp (name,age,deptId,empno)VALUES
('风清扬',90,1,100001),
('岳不群',50,1,100002),
('令狐冲',24,1,100003),
('洪七公',70,2,100004),
('乔峰',35,2,100005),
('灭绝师太',70,3,100006),
('周芷若',20,3,100007),
('张三丰',100,4,100008),
('张无忌',25,5,100009),
('韦小宝',18,NULL,100010);
ALTER TABLE `t_dept` 
add  CEO  INT(11)  ;
 
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

在这里插入图片描述

2. 创建测试大表

创建方式参考：如何在mysql插入50万条测试数据：利用存储过程SQL编程实现

 CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3. Sql查询及优化实战

1、列出自己的掌门比自己年龄小的人员

SELECT a.`name`,a.`age`,c.`name` ceoname,c.`age` ceoage FROM 
t_emp a 
LEFT JOIN t_dept b ON a.`deptId`= b.`id` 
LEFT JOIN t_emp c ON b.`CEO`= c.`id`
WHERE c.`age`<a.`age`

在这里插入图片描述
改成大表测试

#优化  
EXPLAIN SELECT SQL_NO_CACHE a.`name`,a.`age`,c.`name` ceoname,c.`age` ceoage FROM 
emp a 
LEFT JOIN dept b ON a.`deptId`= b.`id` 
LEFT JOIN emp c ON b.`CEO`= c.`id`
WHERE c.`age`<a.`age`

发现id都是1，除了第一个，其他的都是主键索引，第一个进行了全表扫描，这个sql语句写的好，我们不需要建立索引
在这里插入图片描述
创建idx_age索引，发现没有用

CREATE INDEX idx_age ON emp(age)
DROP INDEX idx_age ON emp

在这里插入图片描述

2、列出所有年龄低于自己门派平均年龄的人员

SELECT c.`name`,c.`age`,aa.age FROM t_emp c INNER JOIN
(
    SELECT a.`deptId`,AVG(a.`age`)age FROM t_emp a
    WHERE a.`deptId` IS NOT NULL
    GROUP BY a.`deptId`
 )aa ON c.`deptId`=aa.deptid 
 WHERE c.`age`< aa.age

在这里插入图片描述
改成大表测试

EXPLAIN SELECT SQL_NO_CACHE c.`name`,c.`age`,aa.age FROM emp c INNER JOIN
(
    SELECT a.`deptId`,AVG(a.`age`)age FROM emp a
    WHERE a.`deptId` IS NOT NULL
    GROUP BY a.`deptId`
 )aa ON c.`deptId`=aa.deptid 
 WHERE c.`age`< aa.age

发现两个全表扫描，最后一个出现Using filesort，第二个没有办法优化了，因为出现表示是虚拟表
在这里插入图片描述
我们优化创建两个索引

CREATE INDEX idx_deptid ON emp(deptid)
CREATE INDEX idx_deptid_age ON emp(deptid,age)

查看创建的索引
在这里插入图片描述

查看优化效果:a表使用了索引Using filesort消失，c表使用了索引，查询的行数减少了一半
在这里插入图片描述

3、列出至少有2个年龄大于40岁的成员的门派

SELECT deptName,b.`id` ,COUNT(*)
from t_emp a InnER JOIN t_dept b on b.`id`=a.`deptId`
WHERE age>40
GROUP BY deptName
HAVING COUNT(*)>=2

GROUP BY b.deptName,b.id为什么要加b.id ，因为，例如：少林寺门派，有松山的，还有泰山的，地址不一样，但都是少林寺，所以做分组的时候要加上id
在这里插入图片描述

改成大表测试

EXPLAIN SELECT SQL_NO_CACHE deptName,b.`id` ,COUNT(*)
from emp a InnER JOIN dept b on b.`id`=a.`deptId`
WHERE age>40
GROUP BY deptName
HAVING COUNT(*)>=2

运行发现数据库选择了大表当驱动表，为什么会发生呢？因为被主键索引所勾引来的，按道理来说应该是选小表作为驱动，但是由于小表上有主键索引，所以mysql就认准了大表了
在这里插入图片描述
解决方法：前提是我们知道大表和小表数据差距特别大（并且项目运行一段时间后，数据量关系不会发生变化）的时候，使用STRAIGHT_JOIN （直连也是取交集），可以指定谁是驱动，谁是被驱动，我们把b设置成驱动（放在STRAIGHT_JOIN之前），a设置成被驱动。

 #优化 
 EXPLAIN SELECT SQL_NO_CACHE b.`deptName`,b.`id` ,COUNT(*) FROM  
dept b STRAIGHT_JOIN emp a  ON b.`id` = a.`deptId`
 WHERE a.age >40
 GROUP BY b.`deptName`,b.`id` 
 HAVING COUNT(*)>=2

在这里插入图片描述
建立索引

 CREATE INDEX  idx_deptid_age ON emp(deptid,age)
 CREATE INDEX  idx_deptname ON dept(deptname)

在这里插入图片描述

4、至少有2位非掌门人成员的门派

SELECT c.deptname,  c.id,COUNT(*) FROM t_emp a 
INNER JOIN t_dept c ON a.`deptId` =c.`id`
LEFT JOIN t_dept b ON a.`id`=b.`ceo`
WHERE b.`id` IS NULL
GROUP BY c.`id` ,c.deptname
HAVING COUNT(*)>=2

在这里插入图片描述
换大表查询

EXPLAIN SELECT SQL_NO_CACHE c.deptname,  c.id,COUNT(*) FROM emp a 
INNER JOIN dept c ON a.`deptId` =c.`id`
LEFT JOIN dept b ON a.`id`=b.`ceo`
WHERE b.`id` IS NULL
GROUP BY c.`id` ,c.deptname
HAVING COUNT(*)>=2

发现Using temporar；又指定了大表为驱动
在这里插入图片描述
使用STRAIGHT_JOIN 指定先后顺序

EXPLAIN SELECT SQL_NO_CACHE c.deptname,  c.id,COUNT(*) 
FROM  dept c STRAIGHT_JOIN emp a 
  ON a.`deptId` =c.`id`
LEFT JOIN dept b ON a.`id`=b.`ceo`
WHERE b.`id` IS NULL
GROUP BY c.deptname,c.`id` 
HAVING COUNT(*)>=2

在这里插入图片描述
创建索引

# 先给deptname创建索引，解决GROUP BY的为问题
CREATE INDEX idx_deptnam ON dept(deptname);
# 给关联字段创建索引，a表的关联字段是deptId
CREATE INDEX idx_deptid ON emp(deptid);
# 给关联字段创建索引，b表的关联字段是CEO
CREATE INDEX idx_ceo ON dept(ceo);

在这里插入图片描述

5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否

SELECT  a.`name`, 
CASE 
	WHEN b.`id` IS NULL 
		THEN '否' 
	ELSE '是' 
END '是否为掌门'
FROM  t_emp a 
LEFT JOIN t_dept b ON a.`id`=b.`ceo`

在这里插入图片描述

6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”

SELECT b.`deptName`,
	IF (AVG(a.age)>50,'老鸟','菜鸟')'老鸟or菜鸟'
FROM t_emp a
INNER JOIN t_dept b ON a.`deptId`= b.`id`
GROUP BY b.`id` ,b.`deptName`

在这里插入图片描述

7、显示每个门派年龄最大的人

错例：SELECT 后面只能放GROUP BY后面的字段+函数，这里SELECT 后面的字段增加了a.name
5.5之前版本不会报错，但是会出现查询错误数据，5.7后会报错

UPDATE t_emp SET age=100 WHERE id =2
SELECT b.`deptName`,a.`name`,MAX(a.`age`)FROM t_dept b
   LEFT JOIN t_emp a ON b.`id`=a.`deptId`
   WHERE a.name IS NOT NULL
GROUP BY b.`deptName`

5.7
在这里插入图片描述
5.5我们查询结果中风清扬100

但是我们的数据库中风清扬90岁

修改SQL

SELECT NAME,age FROM t_emp a
INNER JOIN
(
SELECT deptid,MAX(age) maxage
FROM t_emp
WHERE deptid IS NOT NULL
GROUP BY deptid
) aa ON a.`age`= aa.maxage AND a.`deptId`=aa.deptid

在这里插入图片描述

大表查询

EXPLAIN SELECT SQL_NO_CACHE NAME,age FROM emp a
INNER JOIN
(
	SELECT deptid,MAX(age) maxage
	FROM emp
	WHERE deptid IS NOT NULL
	GROUP BY deptid
) aa ON a.`age`= aa.maxage AND a.`deptId`=aa.deptid

在这里插入图片描述

CREATE INDEX idx_deptid_age ON emp(deptid,age)

在这里插入图片描述

8、显示每个门派年龄第二大的人

SET @rank=0;
SET @last_deptid=0;
SELECT a.deptid,a.name,a.age
 FROM(    
    SELECT t.*,
     IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
     @last_deptid:=deptid AS last_deptid
    FROM t_emp t
    ORDER BY deptid,age DESC
 )a WHERE a.rk=2;