SQL 优化经历

#课程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)

#增加课程表100条数据
DROP PROCEDURE IF EXISTS insert_Course;
DELIMITER $
CREATE PROCEDURE insert_Course()
BEGIN
    DECLARE i INT DEFAULT 1;
        WHILE i<=100 DO
        INSERT INTO Course(`c_id`,`name`) VALUES(i, CONCAT(‘语文‘,i+‘‘));
        SET i = i+1;
    END WHILE;
END $
CALL insert_Course();

CALL insert_Course();
> OK
> 时间: 0.152s

#学生表
create table Student(
s_id int PRIMARY KEY,
name varchar(10)
)

#学生表增加70000条数据
DROP PROCEDURE IF EXISTS insert_Student;
DELIMITER $
CREATE PROCEDURE insert_Student()
BEGIN
    DECLARE i INT DEFAULT 1;
        WHILE i<=70000 DO
        INSERT INTO Student(`s_id`,`name`) VALUES(i, CONCAT(‘张三‘,i+‘‘));
        SET i = i+1;
    END WHILE;
END $
CALL insert_Student();

CALL insert_Student();
> OK
> 时间: 175.838s

#成绩表
CREATE table Result(
r_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)

#成绩表增加70W条数据
DROP PROCEDURE IF EXISTS insert_Result;
DELIMITER $
CREATE PROCEDURE insert_Result()
BEGIN
    DECLARE i INT DEFAULT 1;
        DECLARE sNum INT DEFAULT 1;
        DECLARE cNum INT DEFAULT 1;
        WHILE i<=700000 DO
                if (sNum%70000 = 0) THEN
                    set sNum = 1;
                elseif (cNum%100 = 0) THEN 
                    set cNum = 1;
                end if;
        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`) VALUES(i,sNum ,cNum , (RAND()*99)+1);
        SET i = i+1;
                SET sNum = sNum+1;
                SET cNum = cNum+1;
    END WHILE;
END $
CALL insert_Result();

CALL insert_Result();
> OK
> 时间: 2029.5s

#查询语文1考100分的考生
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100)

EXPLAIN
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100)

CREATE index result_c_id_index on Result(c_id);

CREATE index result_score_index on Result(score);

SELECT
    `example`.`s`.`s_id` AS `s_id`,
    `example`.`s`.`name` AS `name` 
FROM
    `example`.`Student` `s` semi
    JOIN ( `example`.`Result` `r` ) 
WHERE
    (
    ( `example`.`s`.`s_id` = `<subquery2>`.`s_id` ) 
    AND ( `example`.`r`.`score` = 100 ) 
    AND ( `example`.`r`.`c_id` = 1 ) 
    )

#先执行
EXPLAIN
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100);
#在执行
show warnings;

select s_id from Result r where r.c_id = 1 and r.score = 100

select s.* from Student s where s.s_id in 
(12871,40987,46729,61381,3955,10687,14047,26917,28897,31174,38896,56518,10774,25030,9778,12544,24721,27295,60361,
38479,46990,66988,6790,35995,46192,47578,58171,63220,6685,67372,46279,64693)

DROP index result_c_id_index on Result;
DROP index result_score_index on Result;

select s.* from 
Student s 
INNER JOIN Result r 
on r.s_id = s.s_id 
where r.c_id = 1 and r.score = 100;

CREATE index result_s_id_index on Result(s_id);
show index from Result;

SELECT
    `example`.`s`.`s_id` AS `s_id`,
    `example`.`s`.`name` AS `name` 
FROM
    `example`.`Student` `s`
    JOIN `example`.`Result` `r` 
WHERE
    (
    ( `example`.`s`.`s_id` = `example`.`r`.`s_id` ) 
    AND ( `example`.`r`.`score` = 100 ) 
    AND ( `example`.`r`.`c_id` = 1 ) 
    )

DROP index result_s_id_index on Result;

SELECT
    s.* 
FROM
    (
        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100 
    ) t
INNER JOIN Student s ON t.s_id = s.s_id

CREATE index result_c_id_index on Result(c_id);
CREATE index result_score_index on Result(score);

SELECT
    s.* 
FROM
    (
        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100 
    ) t
INNER JOIN Student s ON t.s_id = s.s_id

EXPLAIN
select s.* from 
Student s 
INNER JOIN Result r 
on r.s_id = s.s_id 
where r.c_id = 1 and r.score = 100;

DROP PROCEDURE IF EXISTS insert_Result_TO300W;
DELIMITER $
CREATE PROCEDURE insert_Result_TO300W()
BEGIN
    DECLARE i INT DEFAULT 700001;
        DECLARE sNum INT DEFAULT 1;
        DECLARE cNum INT DEFAULT 1;
        WHILE i<=3000000 DO
        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`) 
                VALUES(i,(RAND()*69999)+1 ,(RAND()*99)+1 , (RAND()*99)+1);
        SET i = i+1;
    END WHILE;
END $
CALL insert_Result_TO300W();

show index from Result;

select s.* from 
Student s
INNER JOIN Result r
on r.s_id = s.s_id
where r.c_id = 81 and r.score = 84;

DROP index result_c_id_index on Result;
DROP index result_score_index on Result;
CREATE index result_c_id_score_index on Result(c_id,score);

• MySQL 嵌套子查询效率确实比较低
• 可以将其优化成连接查询
• 连接表时，可以先用 where 条件对表进行过滤，然后做表连接（虽然 MySQL 会对连表语句做优化）
• 建立合适的索引，必要时建立多列联合索引
• 学会分析 sql 执行计划，mysql 会对 sql 进行优化，所有分析计划很重要

索引优化

单列索引

select * from user_test_copy where sex = 2 and type = 2 and age = 10

CREATE index user_test_index_sex on user_test_copy(sex);
CREATE index user_test_index_type on user_test_copy(type);
CREATE index user_test_index_age on user_test_copy(age);

多列索引

create index user_test_index_sex_type_age on user_test(sex,type,age);

select * from user_test where sex = 2 and type = 2 and age = 10

最左前缀

select * from user_test where sex = 2
select * from user_test where sex = 2 and type = 2
select * from user_test where sex = 2 and age = 10

索引覆盖

select sex,type,age from user_test where sex = 2 and type = 2 and age = 10

排序

select * from user_test where sex = 2 and type = 2 ORDER BY user_name

select * from user_test where sex = 2 and type = 2 ORDER BY user_name

• 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等
• 建立单列索引
• 根据需要建立多列联合索引
• 当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。
• 根据业务场景建立覆盖索引
• 只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率
• 多表连接的字段上需要建立索引
• 这样可以极大的提高表连接的效率
• where条件字段上需要建立索引
• 排序字段上需要建立索引
• 分组字段上需要建立索引
• Where条件上不要使用运算函数，以免索引失效