ebook mql5

Author: cakung204

Post on 13-Apr-2018




3 download

Embed Size (px)


  • 7/26/2019 eBook Mql5


    OKC MySQL Users Group

  • 7/26/2019 eBook Mql5



    Discuss topics about MySQL and related open source RDBMS

    Discuss complementary topics (big data, NoSQL, etc)

    Help to grow te local ecosystem troug meetups and e!ents

  • 7/26/2019 eBook Mql5


    MySQL Query Optimization

  • 7/26/2019 eBook Mql5


    I mean, the query gives me the right answer, so why does itmatter? My job is done!

    - Way too many developers

  • 7/26/2019 eBook Mql5


    What is it?

    Human "#uestions$ can generally be written in multiple %ormsin actual SQL

    Manywill even give the "orre"t answer #

    Most #ueries will start out being poorly written and&or e'ecuted RMs are notorious %or writing "bad$ #ueries

    ays to optimi*e

    $ewrite the query

    %dd inde&es to the target tables

  • 7/26/2019 eBook Mql5


    Optimization Basics

    +'amine as %ew rows as possible to get result set

    Read rows in sorted order

    !oid creating temporary tables

    How do we do tat-


  • 7/26/2019 eBook Mql5


    Indein! "i!h Le#e$

    n inde' in a database is tesame in teory as te inde'in a boo.

    ic is %aster-

    /nde'ing wor.s in te same

    way 0 sortcuts to data

    $ead every page and 'eep tra"' o(pages with )

    *o to inde&, (ind ), jump to those pages

    %s we+ve seen, you "an do a whole hour tal' just on inde&ing so that is outsidethe s"ope here...

  • 7/26/2019 eBook Mql5


    Indein! Basic Concepts

    1olumns you want to inde'

    /hose in where "lause

    /hose being sorted0grouped

    /hose being joined 2ou can create composite (multi3column) inde'es

    MySQL uses composite inde'es %rom Le%t 4 Rigt

    /nde'es DOre#uire space, so don5t o!er inde'

    1omposite inde'es are o%ten BETTERtan se!eral singleinde'es

  • 7/26/2019 eBook Mql5


    "o% do I &ind 'ueries?

    6eriodic re!iew o% production #ueries

    Re!iew o% all #ueries in pre3prod prior to release

    De!eloper re!iew o% #ueries wile de!eloping (tis ma.es te

    abo!e easier)

    nd te number one way people %ind bad #ueries777

    %n outage in produ"tion!

  • 7/26/2019 eBook Mql5


    Great( )ut ho% do I &ind them?

    Slow #uery log

    1or histori"al review

    SH 89LL 6R1+SSL/S:

    /o (ind slow queries running now

    2i.e. site is down and db is "rawling

    6lease don5t use te general log

    3ess in(o than slow log, mu"h less use(ul

  • 7/26/2019 eBook Mql5


    *he S$o% Lo!

    :is is te best tool %or %inding slow #ueries

    long;#uery;time de%ines tresold %or #ueries to be reported

    4ote this "an be set to 5 to "apture allqueries

    ealt o% in%ormation

    $ows e&amined vs rows returned

    6&e"ution time

    6&e"ution metadata 2(ilesort, et"

    7er"ona 8erver o((ers additional metri"s

    Numerous tools to parse te log

    pt-query-digest is most used

  • 7/26/2019 eBook Mql5


    I &ound one+ ,o% %hat?+

    SELECT * FROM foo WHERE user_id = 1 ORDERBY date_created DESC

    Query ta.es %ore!er to run Seems super easy since it sould only return one row EXPLAIN SELECT * FROM foo WHERE user_i ! " OR#ER $% &'e_(re&'e#ESC)

    *************************** "+ ro, *************************** i- " sele('_'y.e- SIMPLE '&/le- foo type: ALL

    .ossi/le_0eys- N1LL 0ey- N1LL 0ey_le2- N1LL

    ref- N1LL ro,s- 34 E5'r&- 1si26 ,7ere8 Using filesort" ro, i2 se' 9:+:: se(;

  • 7/26/2019 eBook Mql5


    Second Step...

    1ec. te table structure (primarily inde'es)

    mysql> SHOW CREATE TA$LE foo)*************************** "+ ro, ***************************

    T&/le- fooCre&'e T&/le- CREATE TA$LE

  • 7/26/2019 eBook Mql5



    Determine wat inde' is best (won5t always be per%ect)

    lter te table (you do a!e a test en!ironment, rigt--)

    Re3run te #uery wit e'plain

    1all it a day