ebook mql5

7/26/2019 eBook Mql5

1/22

OKC MySQL Users Group


2/22

OKC MySQL

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


3/22

MySQL Query Optimization


4/22

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

- Way too many developers


5/22

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


6/22

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-

INDEXING!


7/22

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...


8/22

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


9/22

"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!


10/22

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


11/22

*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


12/22

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


14/22

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


15/22

,et...

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

ebook mql5

Documents