六、关联查询优化、七 排序分组优化、八截取查询分析

六、关联查询优化

建表语句

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、LEFT JOIN优化

SQL语句

--未建立索引时的左外连接查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--左表（class）建立索引
CREATE INDEX idx_class_card ON class(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--去掉左表索引
DROP INDEX idx_class_card ON class;

--右表建立索引
CREATE INDEX idx_book_card ON book(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

对应结果

结论

• 在优化关联查询时，只有在被驱动表上建立索引才有效
• left join 时，左侧的为驱动表，右侧为被驱动表

2、INNER JOIN优化

SQL语句

--查询操作，目前索引在book表的card上，class表和book表的位置不会改变查询结果
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM book INNER JOIN class ON book.card = class.card;

--删除book表中的几条记录
DELETE FROM book WHERE bookid<10;

--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

--删除book表card字段索引，给class表的card字段添加索引
DROP INDEX idx_book_card ON book;
CREATE INDEX idx_class_card ON class(card);

--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

对应结果

结论：inner join 时，mysql 会把小结果集的表选为驱动表（小表驱动大表）

所以最好把索引建立在大表（数据较多的表）上

3、RIGHT JOIN优化

优化类型和LEFT JOIN类似，只不过被驱动表变成了左表

七、排序分组优化

在查询中难免会对查询结果进行排序操作。进行排序操作时要避免出现 Using filesort，应使用索引给排序带来的方便

索引信息

1、ORDER BY 优化

以下查询都是在索引覆盖的条件下进行的

SQL语句

--不满足索引覆盖时进行排序查询
EXPLAIN SELECT empno FROM t_emp  WHERE age > 50 ORDER BY age, deptId;

--按照复合索引顺序进行排序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId, name;

--不按照复合索引顺序进行排序（无 age 字段），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name;

--不按照复合索引顺序进行排序（索引顺序打乱），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name, age;

--排序时部分(age)升序，部分(deptId)降序，发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age ASC, deptId DESC;

--排序时都为降序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age DESC, deptId DESC;

--排序时，在前面的字段为常量时（非范围）
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 ORDER BY deptId, name;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 AND deptId>10000 ORDER BY deptId, name;

对应结果

结论：

要想在排序时使用索引，避免 Using filesort，首先需要发生索引覆盖，其次

• ORDER BY 后面字段的顺序要和复合索引的顺序完全一致
• ORDER BY 后面的索引必须按照顺序出现，排在后面的可以不出现
• 要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序
• 如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段

MySQL的排序算法

当发生 Using filesort 时，MySQL会根据自己的算法对查询结果进行排序

• 双路排序
  • MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  • 从磁盘取排序字段，在 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 操作，反而得不偿失
• 优化Using filesort
  • 增大 sort_butter_size 参数的设置
    • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整
  • 增大 max_length_for_sort_data 参数的设置
    • mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data
    • 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大， 明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）
  • 减少 select 后面的查询的字段
    • 查询的字段减少了，缓冲里就能容纳更多的内容了，间接增大了sort_buffer_size

2、GROUP BY 优化

优化方式和 ORDER BY 类似，参考ORDER BY 的优化方式即可

八、截取查询分析

1、慢日志查询

概念

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
• 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句
• 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析

使用

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件

SQL 语句	描述	备注
SHOW VARIABLES LIKE ‘%slow_query_log%’	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF
set global slow_query_log=1	开启慢查询日志
SHOW VARIABLES LIKE ‘long_query_time%’	查看慢查询设定阈值	单位：秒
set long_query_time=1	设定慢查询阈值	单位：秒

运行查询时间长的 sql，可以打开慢查询日志查看

根据查询出来结果的记录数排序等可以通过mysqldumpslow命令来进行分析，而不是由人工去分析。

1 、dba、运维监控数据库报障。查询时间过长，原因：内存泄露、死锁、网络连接、数据库sql烂
2、如是sql问题，把sql抓出来了，给sql跑一下，在测试环境下重现故障。
3、打开慢查询日志，抓出执行慢的sql提取出来。
4、explain分析，结合业务和sql应该可以找到问题。
5、如上还是不行，那么show profile
6、配合dba，服务器重要属性参数调优。

2、批量数据脚本

建表语句

--dept 部门表
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;

-- emp 员工表
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 参数为 1，即 on 开启状态。 否则会报错

--查询
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

--设置
SET GLOBAL log_bin_trust_function_creators=1;

编写随机函数

随机产生字符串

--DELIMITER 是用于改变结束的标志的，一般以分号结尾，但这里改为了以 $$ 结尾
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 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);

添加数据到员工表

--执行存储过程，往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(100000,500000);

批量删除某个表上的所有索引

删除索引的存储过程

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$$

执行存储过程

CALL proc_drop_index("dbname","tablename");

show profile