文章大纲 👇👇
一、查询优化
1.1 批量数据脚本
1.1.1 往表里插入50W数据
- 建表
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;
- 设置参数
log_bin_trust_function_creators
- 创建函数,假如报错:
This function has none of DETERMINISTIC
…
由于开启过慢查询日志,因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators=1;
- 这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法:
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
- 创建函数,保证每条数据都不同
- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;
- 随机产生部门编号
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$
#假如要删除
#drop function rand_num;
- 创建存储过程
- 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
- 创建往dept表中插入数据的存储过程
#执行存储过程,往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
- 调用存储过程
- dept
- emp
#执行存储过程,往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);
#执行存储过程,往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);
select count(*) from dept;
select count(*) from emp;
1.1.2 批量删除某个表上的所有索引
1、查询索引名
2、取出索引名
3、怎么把字符串转换成sql
如何删除索引
- ① 查出该表有哪些索引,索引名–>集合
SHOW INDEX FROM t_emp;
SELECT index_name FROM information_schema.STATISTICS WHERE table_name='t_emp'
AND table_schema='mydb'
AND index_name <>'PRIMARY' AND seq_in_index = 1
- ② 存储过程
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$
③ 执行存储过程
SELECT index_name FROM information_schema.STATISTICS WHERE table_name='t_emp'
AND table_schema='mydb'
AND index_name <>'PRIMARY' AND seq_in_index = 1
#CALL proc_drop_index("dbname","tablename"); #dbname 库名tablename 表名
CALL proc_drop_index("mydb","t_emp");
1.2 单表使用索引及常见索引失效
1.2.1 案例(索引失效)
- 全值匹配我最爱
- (1) 需求
#系统中经常出现的sql语句如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd'
#索引应该如何建立 ?
- (2) 建立索引
- ① 没有建索引之前
- ② 建索引之后
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
CREATE INDEX idx_age ON emp(age);
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
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'
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
最佳左前缀法则
- (1) 需求
如果系统经常出现的sql如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'
那原来的idx_age_deptid_name 还能否正常使用?
- (2) 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且
不跳过索引中的列
。
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'
虽然可以正常使用,但是只有部分被使用到了。
完全没有使用上索引。
结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描,如下:
LEFT(emp.name,3) = 'abc'
- 需求
#这两条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'
第一种
第二种
#这两条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);
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
- 存储引擎不能使用索引中范围条件右边的列
- 需求
#如果系统经常出现的sql如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;
#那么索引 idx_age_deptid_name这个索引还能正常使用么?
#如果系统经常出现的sql如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc';
#那么索引 idx_age_deptid_name这个索引还能正常使用么?
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
CREATE INDEX idx_age_name_deptid ON emp(age,NAME,deptid);
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
- 如果这种sql 出现较多
应该建立:
create index idx_age_name_deptid on emp(age,name,deptid)
效果
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
- is not null 也无法使用索引,但是is null是可以使用索引的
#下列哪个sql语句可以用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL
CREATE INDEX idx_age ON emp(age)
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
- like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE '%ab%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'ab%';
CREATE INDEX idx_age_name ON emp(age,NAME)
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
- 小总结
- 假设
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 |
1.2.2 一般性建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
- 书写sql语句时,尽量避免造成索引失效的情况
1.3 关联查询优化
1.3.1 建表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)));
1.3.2 案例
#下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
#结论:type 有All
#添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
换成INNER JOIN
DELETE FROM class WHERE id<5;
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
① 没有使用索引
② 使用了索引
③ book 使用了索引 class没有使用索引
③ class 使用了索引 book 没有使用索引
1.3.3 建议
- 1、保证被驱动表的join字段已经被索引
- 2、left join 时,选择小表作为驱动表,大表作为被驱动表。
- 3、inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
- 4、子查询尽量不要放在被驱动表,有可能使用不到索引。
- 5、能够直接多表关联的尽量直接关联,不用子查询。
1.4 子查询优化
尽量不要使用
not in
或者not exists
#取所有不为掌门人的员工,按年龄分组 ,每个年龄段多少人
SELECT SQL_NO_CACHE age,COUNT(*) FROM emp a
WHERE id NOT IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL)
GROUP BY age
HAVING COUNT(*)<10000
- 用
left outer join on xxx is null
替代
EXPLAIN SELECT SQL_NO_CACHE age,COUNT(*) FROM emp a
LEFT OUTER JOIN dept b ON a.id =b.ceo
WHERE b.ceo IS NULL
GROUP BY age
HAVING COUNT(*)<10000
1.5 排序分组优化
case
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;
#无过滤 不索引
3、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;
4、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,NAME;
5、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,empno;
6、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY NAME,deptid;
7、 EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
#顺序错,必排序
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 ;
#方向反 必排序
ORDER BY
子句,尽量使用Index
方式排序,避免使用FileSort
方式排序
索引的选择
- 执行案例前先清除emp上的索引,只留主键
- 索引的选择
CALL proc_drop_index('mydb','emp');
CALL proc_drop_index('mydb','dept');
#查询 年龄为30岁的,且员工编号小于101000的用户,按用户名称排序
SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND empno <101000 ORDER BY NAME;
#结论:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
#开始优化:
思路:尽量让WHERE的过滤条件和排序使用上索引
但是一共两个字段(deptno,empno
)上有过滤条件,一个字段(ename
)有索引
- 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
速度果然提高了4倍。…
但是,如果我们选择那个范围过滤,而放弃排序上的索引呢,建立
DROP INDEX idx_age_name ON emp
CREATE INDEX idx_age_empno ON emp(age,empno);
果然出现了filesort,而且type还是range光看字面其实并不美好。
我们来执行以下sql
- 结果竟然有 filesort的 sql 运行速度,超过了已经优化掉 filesort的 sql ,而且快了好多倍。何故?
- 原因是所有的排序都是在条件过滤之后才执行的,所以如果条件过滤了大部分数据的话,几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,所以索引放在这个字段上性价比最高,是最优选择。
结论: 当范围条件和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 即使没有过滤条件用到索引,也可以直接使用索引。
1.6 最后使用索引的手段:覆盖索引
什么是覆盖索引?
简单说就是,select 到 from 之间查询的列 <=使用的索引列+主键
explain select * from emp where name like '%abc';
使用覆盖索引后
二、查询优化综合
子查询优化强训:
CALL proc_drop_index('mydb','t_emp');
CALL proc_drop_index('mydb','t_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`
#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、显示每个门派年龄第二大的人(涉及sql编程)
SET @rank=0; #@rank 自定义变量
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