MYSQL5.6优化经验：从30248.271s到0.001s

目的查询：

查找语文考了100分的考生

查询语句：

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

执行时间：3 0248.271秒

您好，为什么这么慢？我们先检查一下查询计划：

EXPLAIN
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

我发现没有使用索引，而且类型是ALL，所以我首先想到的是创建索引。要索引的字段始终是条件where 中的列。 。

首先为c_id和sc表score创建索引

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次执行上面的查询语句，时间：1.054s

快了30000倍以上，看起来查询时间更短。索引可以大大提高查询效率，建立索引是很有必要的。很多时候我忘记创建索引

。当数据量小时，我没有感觉。这个优化感觉确实不错。

但是1秒还是太长了。还可以优化吗？仔细看执行计划：

看优化后的sql：

SELECT
    `YSB`.`s`.`s_id` AS `s_id`,
    `YSB`.`s`.`name` AS `name`
FROM
    `YSB`.`Student` `s`
WHERE
    < in_optimizer > (
        `YSB`.`s`.`s_id` ,< EXISTS > (
            SELECT
                1
            FROM
                `YSB`.`SC` `sc`
            WHERE
                (
                    (`YSB`.`sc`.`c_id` = 0)
                    AND (`YSB`.`sc`.`score` = 100)
                    AND (
                        < CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
                    )
                )
        )
    )

补充：这里有网友问如何查看优化后的语句

方法如下：

运行命令

with type=all

按照我之前的想法，sql执行顺序应该先执行子查询

select s_id from SC sc where sc.c_id = 0 and sc.score = 100

耗时：0.001s

获取然后执行：所需时间：0.001s

这速度相当快。 mysql不会先执行内查询，而是将sql优化到现有子句中，出现Without SUBQUERY EPENDENT,

mysql先执行外查询，再执行内查询，需要循环 80007* 。

使用联接查询怎么样？

SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100

重新分析连接查询的状态，暂时去掉sc_c_id_index、sc_score_index

执行时间为：0.057s

效率有所提升。看一下执行计划：

这里出现连接表的情况，我想是否应该从表sc中为s_id创建索引。 SC(s_id)中的

GIAT索引sc_s_id_index；时间已经不多了，为什么呢？看一下执行计划：

优化后的查询语句是：

SELECT
    `YSB`.`s`.`s_id` AS `s_id`,
    `YSB`.`s`.`name` AS `name`
FROM
    `YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
    (
        (
            `YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
        )
        AND (`YSB`.`sc`.`score` = 100)
        AND (`YSB`.`sc`.`c_id` = 0)
    )

好像是先连接查询完成，然后条件过滤where did

返回到之前的执行计划：

这是这里条件过滤完成，然后与表join，执行计划不固定，我们看一下标准的SQL执行顺序：

正常情况下，先join，然后在哪里过滤，但在我们的例子中，如果先join ，会有70万条数据发送到join进行操作，这样wise plan先做where

过滤。现在排除mysql查询优化，我写优化sql

SELECT
    s.*
FROM
    (
        SELECT
            *
        FROM
            SC sc
        WHERE
            sc.c_id = 0
        AND sc.score = 100
    ) t
INNER JOIN Student s ON t.s_id = s.s_id

，也就是先做sc表过滤。然后顺着表连接，执行时间为：0.054s

和s_id索引没有建之前的时间差不多

看执行计划：

先提取sc再加入表，从而使效率更高。当前问题 提取sc时出现扫描表，所以现在可以指定相关索引

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

并执行查询：

SELECT
    s.*
FROM
    (
        SELECT
            *
        FROM
            SC sc
        WHERE
            sc.c_id = 0
        AND sc.score = 100
    ) t
INNER JOIN Student s ON t.s_id = s.s_id

执行时间为：0.001秒，这个时间相当可靠，快了50倍

执行计划:

我们会看到索引是用来先提取sc，然后连接表的。

然后我们来执行sql

SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100

执行时间0.001s

执行计划：

Mysql这里已经优化了过滤语句，然后这里进行了过滤操作，然后进行了过滤操作。

2015年4月30日新增：近期重新导入了部分生产数据。经过测试，发现前几天优化的SQL执行效率又下降了

调整的内容是SC表数据增加到了300W。学生成绩更加离散。

单列索引

查询语句如下：

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);

对性别、性别、年龄列建立索引。数据量300w。查询时间：0.415s

执行计划：

Found type=index_merge

这是MySQL针对单列索引的优化，对结果集使用交并并操作