問題出現(xiàn)

在這個項目中印象比較深刻的是一次改進sql執(zhí)行效率。系統(tǒng)有一個 頁面需要統(tǒng)計學生是否上傳了各類文件，主要有兩類，一類是畢設文件，這類文件一種每個學生只能上傳一份。隨著數(shù)據(jù)量的增多，這個頁面的訪問越來越慢。

問題確定

然后，我想起來，我在MySQL的配置文件中，配置了慢SQL日志。查看MySQL慢日志，發(fā)現(xiàn)進入該頁面時執(zhí)行的SQL，被當作慢日志記錄下了。

使用EXPLAIN命令查看了該SQL的執(zhí)行計劃，發(fā)現(xiàn)執(zhí)行計劃是先全文搜索了論文信息表，并使用了filesort文件排序、臨時文件存儲了中間結(jié)果，之后再進行學生表、文件上傳記錄表等查詢。對后幾個表的查詢都是使用索引查詢。判斷是由于這個查詢要根據(jù)學生ID進行分組，和排序。而thesis中的學生id不是排序的，所以進行了filesort操作。由于這個SQL所以判定這個SQL訪問緩慢是由于這個表的全表掃描、文件排序等造成的?？紤]如果先查詢學生表，學生表中的id本來就是排序的，就不需要再額外排序了。所以在sql查詢中將學生表放在前邊，用學生表去join論文表。但結(jié)果還是先查了論文表。經(jīng)過查詢，我了解到mysql會對表的連接順序進行優(yōu)化，以它認為的高效率進行查詢，但這次顯然不能這樣。于是我使用了stright_join語句強制指定查詢順序解決了這個問題。

首先，通過資料了解到聯(lián)合查詢的效率是優(yōu)于子查詢的，便使用case when語句和count方法將原查詢改寫了。改寫之后發(fā)現(xiàn)效率有所上升，但還是挺不算快（1秒多2秒）。因為之前已經(jīng)在各個表中的學生id字段都加上了索引，感覺速度不至于這么慢。然后我又使用explain命令來查看sql的執(zhí)行的計劃，發(fā)現(xiàn)thesis表沒有使用索引，并且使用了filesort，

帶引號.png

不帶引號.png

過程：
將子查詢改寫為聯(lián)合查詢，使用case when進行數(shù)據(jù)統(tǒng)計

使用explain查詢執(zhí)行計劃：
發(fā)現(xiàn)thesis使用了全表查詢?？紤]將student放在前邊，發(fā)現(xiàn)執(zhí)行計劃不變，
不按指定順序執(zhí)行,thesis全表查詢，加Using temporary;Using filesort,分析了一下是因為先查詢thesis表的話，需要再對學生id進行排序，所以用到了filesort。如果使sql先查詢student表，則可以避免這種情況。但我本來寫的是student表join thesis表，不應該先查student嗎？經(jīng)過查找資料得知，Mysql會進行一定程度的sql優(yōu)化，這個過程可能會改變查詢表的順序，但這次的改變卻不是我們想要的。于是我使用stright join語句強制先查詢student。果然，沒有的temporary和filesort。效率提高了10倍左右。

使用stright join改寫，強制執(zhí)行順序

點：sql改寫、explain命令的使用，查詢表的順序沒有按照指定順序走時的做法

條件是卸載on后邊，還是卸載where后邊

如果是主表的條件，則寫在where后邊
如果是被關聯(lián)表的條件，則寫在on后邊

資料

straight join的使用經(jīng)歷（重點看高性能MySQL那一段）
MySQL優(yōu)化的奇技淫巧之STRAIGHT_JOIN（提出了MySQL選擇表順序的依據(jù)）
MySQL優(yōu)化器如何選擇索引和JOIN順序
MySQL查詢優(yōu)化器概述
MySQL查詢執(zhí)行過程
explain命令詳解
MySQL查詢條件加引號和不加引號的區(qū)別
MySQL分析SQL耗時瓶頸
子查詢和聯(lián)合查詢的效率討論
四種join語句

explain命令執(zhí)行結(jié)果分析：

id：

查詢序號，從大到小執(zhí)行，同樣大的按從上往下的順序

select_type：

• SIMPLE：簡單SELECT(不使用UNION或子查詢等)
• PRIMARY：最外面的SELECT
• UNION：UNION中的第二個或后面的SELECT語句
• DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
• UNION RESULT：UNION的結(jié)果。
• SUBQUERY：子查詢中的第一個SELECT
• DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢
• DERIVED：導出表的SELECT(FROM子句的子查詢)

table

顯示這一行的數(shù)據(jù)是關于哪張表的

type

分析sql瓶頸的關鍵一列
結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > range > index > all

一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現(xiàn)性能問題。
ALL：全表掃描，性能最差
index：也是全表掃描，只不過只查詢索引中的數(shù)據(jù)，不需要磁盤數(shù)據(jù)
range：只檢索給定范圍的行
ref：非唯一性索引
eq_ref：唯一性索引
const:

possible_keys和key

possible顯示可能的索引
key顯示實際使用的索引

ref

顯示索引的的那一列被使用了
因為使用的索引可能有多個列（聯(lián)合索引），但真正使用的列不一定有幾個

rows

根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需記錄所需要讀取的行數(shù)，也就是說，用的越少越好

extra

包含不適合在其他列中顯式但十分重要的額外信息

1.Using filesort：

說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”。

2.Using temporary

使用了用臨時表保存中間結(jié)果，MySQL在對查詢結(jié)果排序時使用臨時表。常見于排序order by和分組查詢group by。

3.Using index

表示相應的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數(shù)據(jù)行，效率不錯。如果同時出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找；如果沒有同時出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。

        SELECT
            `student`.`id` AS `id`,
            `student`.`sname` AS `sname`,
            `student`.`sno` AS `sno`,
            `student`.`year` AS `year`,
            `teacher`.`tname` AS `tname`,
            `student`.`email` AS `email`,
            `student`.`phone` AS `tel`,
            `student`.`department` AS `department`,
        count(
            DISTINCT (
            CASE `upfile`.`period`
              WHEN 2 THEN
              `upfile`.`id`
              ELSE
                NULL
            END
            )
        ) AS `plan_cnt`,
        count(
            DISTINCT (
            CASE `upfile`.`period`
              WHEN 3 THEN
                `upfile`.`id`
            ELSE
              NULL
            END
            )
        ) AS `open_cnt`,
        count(
        DISTINCT (
        CASE `upfile`.`period`
          WHEN 4 THEN
          `upfile`.`id`
          ELSE
            NULL
          END
        )
        ) AS `midterm_cnt`,
        count(
            DISTINCT (
                CASE `upfile`.`period`
                WHEN 41 THEN
                  `upfile`.`id`
                ELSE
                  NULL
                END
            )
        ) AS `midterm_ppt_cnt`,
        count(
            DISTINCT (
              CASE `upfile`.`period`
              WHEN 6 THEN
                `upfile`.`id`
              ELSE
                NULL
              END
        )
        ) AS `graduation_cnt`,
        count(
        DISTINCT (
          CASE `upfile`.`period`
            WHEN 16 THEN
              `upfile`.`id`
            ELSE
             NULL
            END
            )
        ) AS `graduation_edit_cnt`,
        count(
            DISTINCT (
            CASE `upfile`.`period`
            WHEN 61 THEN
              `upfile`.`id`
            ELSE
              NULL
            END
        )
        ) AS `graduation_ppt_cnt`,
        count(DISTINCT `week_report`.`id`) AS `weekly_report_cnt`
        FROM
        `student`
        STRAIGHT_JOIN `thesis` ON (
            `thesis`.`sid` = `student`.`id`
            AND `thesis`.`year` = '2018'
        )
        LEFT JOIN `upfile` ON (
        `thesis`.`sid` = `upfile`.`sid` AND  upfile.year = 2018
        )
        LEFT JOIN `week_report` ON (
        `thesis`.`sid` = `week_report`.`sid` AND week_report.year = 2018
        )
        LEFT JOIN `teacher` ON (
        `teacher`.`id` = `thesis`.`tid`
        )
        WHERE
            student.department = '電子工程學院' AND student.`year` =  2018
        GROUP BY
        `student`.`id`
            ORDER BY student.id

            LIMIT 0,10