sql调优实践（mysql）

首先来创建表

--学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--学生成绩表
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sc_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006384 DEFAULT CHARSET=utf8;

 

用存储过程来插入数据

--插入学生记录
CREATE PROCEDURE AddStudent()
BEGIN
		DECLARE var INT;
		SET var=0;
		WHILE var<10000 DO
 				INSERT INTO student(id,name)
 				VALUES(var,CONCAT('tom',var));
 				SET var=var+1;
		END WHILE ;
	END;

--插入课程记录
CREATE PROCEDURE AddCourse()
BEGIN
		DECLARE var INT;
		SET var=0;
		WHILE var<100 DO
 				INSERT INTO course(id,name)
 				VALUES(var,CONCAT('课程',var));
 				SET var=var+1;
		END WHILE ;
	END;

--在分数表中插入学生记录
CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`()
BEGIN
		DECLARE var INT;
		SET var=0;
		WHILE var<100 DO
				alter table sc auto_increment=1; //我的mysql版本是5.7，没加这句的话分数表中的自增id会跳
 				INSERT INTO sc(s_id)
 				select id from student;
 				SET var=var+1;
		END WHILE ;
	END;

--更新分数表中学生的分数
CREATE PROCEDURE AddScScore()
BEGIN
		DECLARE var INT;
		DECLARE varC INT;
		DECLARE ID INT;
		SET var=0;
		SET varC=0;
		set ID=1;
		WHILE var<10000 DO
			WHILE varC<100 DO
 				UPDATE sc set c_id=varC,score=FLOOR(1 + (RAND() * 100)) where sc_id=ID;
 				SET varC=varC+1;
				SET ID=ID+1;
			END WHILE;
 			SET var=var+1;
			SET varC=0;
		END WHILE;
	END;

执行存储过程

call AddCourse();
call AddStudent();
call AddSc();
call AddScScore();

执行完了之后学生表：1W条，课程表100条，分数表100W条

1.查询课程id为10，分数为100的学生信息

SELECT s.* FROM student s where id in(
	select sc.s_id from sc INNER JOIN course c on sc.c_id =c.id where sc.score=100 and sc.c_id='10'
);

查询时间1.156s

 

看一下执行计划，执行计划可以通过EXPLAIN+sql来查看

解释里面执行计划顺序为id大的先执行，同样大的按排列顺序从上往下执行。

可以看到在查table sc的时候type是all,全表扫描,没有用到索引（关于查询ELPLAIN解释的可以参考一下这边文章http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html），在sc表的c_id和score加上索引再试试

 

 

 

 

现在可以看到在查询sc的时候用到了索引，现在这里查询用时0.065s,

还有有一个小技巧，使用EXPLAIN EXTENDED + 你要查的sql + show WARNINGS ， 可以看到mysql查询优化器处理之后的sql语句。

再看一下查询优化器处理过的sql

SELECT
	`world`.`s`.`id` AS `id`,
	`world`.`s`.`name` AS `name`
FROM
	`world`.`course` `c` 
JOIN (`world`.`sc`)
JOIN `world`.`student` `s`
WHERE
	(
		(`world`.`sc`.`c_id` = '10')
		AND (
			`world`.`s`.`id` = `world`.`sc`.`s_id`
		)
		AND (`world`.`sc`.`score` = 100)
		AND (`world`.`sc`.`c_id` = '10')
	)

mysql将我们写的in子查询优化成join连接了

 

最后附上一些sql调优的总结，以后有时间再深入研究

1. 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等

2. 建立单列索引

3. 根据需要建立多列联合索引

当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，

那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。

4. 根据业务场景建立覆盖索引

只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率

5. 多表连接的字段上需要建立索引

这样可以极大的提高表连接的效率

6. where条件字段上需要建立索引

7. 排序字段上需要建立索引

8. 分组字段上需要建立索引

9. Where条件上不要使用运算函数，以免索引失效

参考文章

执行计划参考

http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html

sql优化

http://www.cnblogs.com/mliang/p/3637937.html

http://www.cnblogs.com/xwdreamer/archive/2012/07/19/2599494.html