单表索引优化
建立的表
然后emp表插入了50万行,dept表插入了10万行数据
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;
全值匹配我最爱
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
#解决方法 create index idx_age ON emp(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
#解决方法 create index idx_age_deptid ON emp(age,deptid);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 and emp.age=30 AND emp.name = 'abcd'
#解决方法都是 create index idx_age_deptid_name ON emp(age,deptid,name);
# 位置顺序变了 没关系,会进行优化
最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
虽然可以正常使用,但是只有部分被使用到了。
完全没有使用上索引。
#原始的select+索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'
create index idx_age_deptid_name ON emp(age,deptid,name);
#命中了age字段
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'
#一个都没命中
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'abcd'
#最左指的是的索引的第一个字段 也就是age,从第一个开始 顺序命中 age->deptid->name
结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
这两条sql哪种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%' #索引有效
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc' #索引无效
CREATE INDEX idx_name ON emp(NAME)
第一种
第二种
存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;
# 创建这个索引效果不是很好 用上两个字段 age deptid
# 因为deptid使用>进行查询 所以deptid的右边name无效
create index idx_age_deptid_name ON emp(age,deptid,name);
如果这种sql 出现较多
应该建立:
# (这个 emp.deptId>20的索引关键字要放到最后)
create index idx_age_name_deptid on emp(age,name,deptid)
**和SQL里的顺序没关系 要索引的范围查询在最后就行了 **
效果
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'
is not null 也无法使用索引,但是is null是可以使用索引的
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
总结
假设index(a,b,c)
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 | Y,使用到a,b |
where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
where a = 3 and c = 5 | 使用到a, 但是c不可以,b中间断了 |
where a = 3 and b > 4 and c = 5 | 使用到a和b, c不能用在范围之后,b断了 |
where a is null and b is not null | is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用 |
where a <> 3 | 不能使用索引 |
where abs(a) =3 | 不能使用 索引 |
where a = 3 and b like ‘kk%’ and c = 4 | Y,使用到a,b,c |
where a = 3 and b like ‘%kk’ and c = 4 | Y,只用到a |
where a = 3 and b like ‘%kk%’ and c = 4 | Y,只用到a |
where a = 3 and b like ‘k%kk%’ and c = 4 | Y,使用到a,b,c |
建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引(性别过滤性就不好)
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
- 书写sql语句时,尽量避免造成索引失效的情况
关联查询优化
建表
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)));
INSERT INTO class(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)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
下面开始explain分析这时候还没建立索引
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
以下是没用索引的情况
建立了索引之后
ALTER TABLE book
ADD INDEX Y ( card
);
给class表也添加索引
ALTER TABLE class
ADD INDEX Y ( card
);
虽然type从all 变成了index,但是rows还是20行(总共就20行)还是全表扫描
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
inner join中 mysql自己选择驱动表和被驱动表
-
保证被驱动表的join字段已经被索引(前面的表是驱动表,后面的表是被驱动表)
left join前面是驱动表后面是被驱动表
-
inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
-
left join 时,选择小表作为驱动表,大表作为被驱动表。
-
子查询尽量不要放在被驱动表,有可能使用不到索引。
-
能够直接多表关联的尽量直接关联,不用子查询。
子查询优化
- 尽量不要使用not in 或者 not exists
- 用left outer join on xxx is null 替代
SELECT * FROM emp a where a.id NOT IN
(SELECT b.ceo FROM dept b where b.ceo IS NOT NULL)
优化
SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.CEO WHERE b.id IS NULL;
排序分组优化
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
create index idx_age_deptid_name on emp (age,deptid,name)
#无过滤 不索引 以下 是否能使用到索引,能否去掉using filesort
#这个不可以
1、explain select SQL_NO_CACHE * from emp order by age,deptid;
#这个可以
2、explain select SQL_NO_CACHE * from emp order by age,deptid limit 10;
#分页也是过滤 oder by一定需要过滤
#顺序错,必排序
简历索引 create index idx_age_deptid_name on emp (age,deptid,name)
3、 explain select * from emp where age=45 order by deptid;
#用上过滤条件了:age->deptid
4、explain select * from emp where age=45 order by deptid,name;
#用上过滤条件了:age->deptid->name
5、explain select * from emp where age=45 order by deptid,empno;
#索引是(age,deptid,name)empno没包含进去,产生了using filesort
6、explain select * from emp where age=45 order by name,deptid;
#索引是(age,deptid,name) order by name,deptid 这个和索引的顺序不一样不能优化
# using filesort
7、 explain select * from emp where deptid=45 order by age;
# 和索引顺序不一致 using filesort
#方向反 必排序
#还是这个索引 (age,deptid,name)
8、 explain select * from emp where age=45 order by deptid desc, name desc ;
9、 explain select * from emp where age=45 order by deptid asc, name desc ;
# 要同时升序或者降序 using filesort
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
#结论:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
#开始优化:
思路: 尽量让where的过滤条件和排序使用上索引
但是一共两个字段(age,empno)上有过滤条件,一个字段(name)有索引
1、我们建一个三个字段的组合索引可否?
CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
我们发现using filesort 依然存在,所以name 并没有用到索引。
原因是因为empno是一个范围过滤,所以索引后面的字段不会再使用索引了。
所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引,DROP INDEX idx_age_empno_name ON emp
为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON emp(age,NAME);
也就是说empno 和name这个两个字段我只能二选其一。
这样我们优化掉了 using filesort。
执行一下sql
但是
如果我们选择那个范围过滤,而放弃排序上的索引呢
建立
DROP INDEX idx_age_name ON emp
create index idx_age_eno on emp(age,empno);
结果竟然有 filesort的 sql 运行速度,超过了已经优化掉 filesort的 sql ,而且快了好多倍。何故?
原因是所有的排序都是在条件过滤之后才执行的,所以如果条件过滤了大部分数据的话,几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,所以索引放在这个字段上性价比最高,是最优选择。
两个索引都简历Mysql选择了有using filesort的索引,他选择了最快的方式
结论: 当范围条件和group by 或者 order by 的字段出现二选一时 ,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序
双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,
读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出(把书全部取出来 然后单独排序)从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。
单路排序
从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,
它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,
因为它把每一行都保存在内存中了。
单路的缺陷
在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取取sort_buffer容量大小,再排……从而多次I/O。
本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。
优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- 减少select 后面的查询的字段。
why
提高Order By的速度
-
Order by时select * 是一个大忌只Query需要的字段, 这点非常重要。在这里的影响是:
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。 -
尝试提高 sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的 1M-8M之间调整 -
尝试提高 max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整
GROUP BY关键字优化
group by 使用索引的原则几乎跟order by一致 ,唯一区别是groupby 即使没有过滤条件用到索引,也可以直接使用索引。
最后使用索引的手段:覆盖索引
将 * 转换为 具体的值
8个sql分析
创建表
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 (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',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;
CALL proc_drop_index('mydb','emp');
CALL proc_drop_index('mydb','dept');
#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`
CREATE INDEX idx_age ON emp(age)
#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
CREATE INDEX idx_deptid ON emp(deptid)
CREATE INDEX idx_deptid_age ON emp(deptid,age)
#3、列出至少有2个年龄大于40岁的成员的门派
SELECT b.`deptName`,COUNT(*) FROM t_emp a
INNER JOIN t_dept b ON b.`id` = a.`deptId`
WHERE a.age >40
GROUP BY b.`deptName`,b.`id`
HAVING COUNT(*)>=2
#优化
EXPLAIN SELECT SQL_NO_CACHE b.`deptName`,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)
STRAIGHT_JOIN 强制确定驱动表和被驱动表 1、概念非常明确 2、对数据量的比例非常明确
#4、至少有2位非掌门人成员的门派
SELECT * FROM t_emp a WHERE a.id NOT IN
{
SELECT b.`ceo` FROM t_dept b WHERE b.`ceo`IS NOT NULL
}
NOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL
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 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
CREATE INDEX idx_ceo_deptnam ON dept(ceo,deptname)
CREATE INDEX idx_deptnam ON dept(deptname)
CREATE INDEX idx_deptid ON emp(deptid)
SELECT b.`id`,b.`deptName` ,COUNT(*) FROM t_emp a INNER JOIN t_dept b ON a.`deptId`= b.`id`
GROUP BY b.`deptName`,b.`id`
SELECT b.`id`,b.`deptName`, COUNT(*) FROM emp a INNER JOIN dept b ON a.`deptId`= b.`id`
GROUP BY b.`deptName`,b.`id`
UPDATE t_dept SET deptname='明教' WHERE id=5
#5、列出全部人员,并增加一列备注“是否为掌门”,如果是掌门人显示是,不是掌门人显示否
CASE WHEN
IF
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 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)
#错例
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`
UPDATE t_emp SET age=100 WHERE id =2
#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;
#分组排序
SET @rank=0;
SET @last_deptid=0;
SELECT * 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 <=1
#oracle rank() over()
UPDATE t_emp SET age=100 WHERE id =1
SET @rank=0;
SET @last_deptid=0;
SET @last_age=0;
SELECT t.*,
IF(@last_deptid=deptid,
IF(@last_age = age,@rank,@rank:=@rank+1)
,@rank:=1) AS rk,
@last_deptid:=deptid AS last_deptid,
@last_age :=age AS last_age
FROM t_emp t
ORDER BY deptid,age DESC