ããã«ã¡ã¯ãããã§ãã
ä»åã¯ã¡ã¢ããŠãMySQLã®ãã¥ãŒãã³ã°ã«ã€ããŠãèªåã§èª¿ã¹ãªããã¡ã¢ãšããŠèšäºãæžããŠããããšæããŸãã
ãŸããè²ã
ãšãã¥ãŒãã³ã°æ¹æ³ãæžããŠãããŸãããåäœç°å¢ã¯ããïŒã£ãŠããã®ã¯ãããŸããã
ãã¶ãçŸåšã®ç°å¢ã§åããŠãããã®ã§ããã°åããšã¯ããããŸããã
SQLã»ããŒãã«èšèšåšãã®ãã¥ãŒãã³ã°
ã¹ããŒã¯ãšãªãèŠã€ãã
ãŸãã¯ããã«è¡ãã¹ãã¯ã¹ããŒã¯ãšãªã«ãªã£ãŠããéšåã®æ¹åã§ãããã
ã¹ããŒã¯ãšãªãèŠã€ããã«ã¯MySQLã®èšå®ãå¿
èŠã§ãã
my.cnfã«ä»¥äžã®èšå®ãããåèµ·åããŸãã
ãŸãããã°ã®ä¿åå
ãã¹ããŒã¯ãšãªãšããlong_query_timeã®å€ã¯é©å®å€ããŠãã ããã
[mysqld]
slow_query_log=ON
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 0.5
ããããŠæžãåºãããã°ã¯mysqldumpslowã³ãã³ããå©çšããããšã§éèšããããšãåºæ¥ãŸãã
äŸãã°æéãå
ã«éèšããå Žåã¯-s tãã€ããŠä»¥äžã®ããã«ããŸãã
mysqldumpslow -s t /tmp/mysql-slow.log
åèãªã³ã¯ïŒ
Mysql slow queryã®èšå®ãšè§£ææ¹æ³
http://masayuki14.hatenablog.com/entry/20120704/1341360260
å®è¡èšç»ã確èªãã
ã¹ããŒã¯ãšãªãå²ãåºããããšã§ãçŽããŠããã¹ãã¯ãšãªãã©ãã倿ããŸããã
ãããããã®ã¯ãšãªãã©ãããŠéããã®ãïŒãªã©ã¯åãããªãå Žåãå€ãã¯ãããŸããã
ãã®å Žåã«ã¯EXPLAINãå©çšããŠãMySQLã®å®è¡èšç»ã確èªããŠã¿ããšè¯ãã§ãããã
SELECT * FROM t1; // ãããªSQLãæã£ããšããŠ
EXPLAIN SELECT * FROM t1; // å
é ã«EXPLAINãã€ããã ã
ãã®çµæãèŠãããšã§ã€ã³ããã¯ã¹ã¯ãã£ããå©çšãããŠãããïŒç¡é§ã«è¡ãã§ãããããŠããªããïŒ
ãªã©è²ã
ãªåé¡ç¹ã確èªããããšãåºæ¥ãŸãã
åèãªã³ã¯ïŒ
MySQLã®EXPLAINã培åºè§£èª¬!!
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
ã€ã³ããã¯ã¹ãé©åã«å©çšãã
MySQLã§ã¯ã€ã³ããã¯ã¹ãå©çšããäžã§ããã€ãã®å¶éããããŸãã
ãã®å¶éãã¡ãããšææ¡ããŠããããšãå¿
èŠã«ãªããŸãã
1. ã€ã³ããã¯ã¹ã¯åæã«äžã€ãã䜿ãããªã
äŸãã°ãã©ã€ããªããŒ/ã€ã³ããã¯ã¹A/ã€ã³ããã¯ã¹Bããããšãã
ã€ã³ããã¯ã¹Aãšã€ã³ããã¯ã¹Bã¯ãã¡ããã®ããš
ãã©ã€ããªããŒãšã€ã³ããã¯ã¹Aãããã©ã€ããªããŒãšã€ã³ããã¯ã¹Bãåæã«å©çšããŠãããªãã
2. ã€ã³ããã¯ã¹ã®ã«ã©ã ã®é çªãšWHEREå¥ã§æå®ãããŠããã«ã©ã ã®é çªãäžèŽããŠããå¿
èŠããã
ã€ã³ããã¯ã¹ã«col1,col2ã®é ã§èšå®ãããŠããå Žåã«ã¯WHEREå¥ã§æå®ããé çªãcol1,col2ã®é çªã§ãªããšã€ã³ããã¯ã¹ãå©çšããŠãããªãã
äžèšã®æ³šæç¹ä»¥å€ã«ãInnoDBã¯ã¯ã©ã¹ã¿ã€ã³ããã¯ã¹ã§ããããšããã»ã«ã³ããªã€ã³ããã¯ã¹ã«ã¯ã¯ã©ã¹ã¿ã€ã³ããã¯ã¹ã®ã®ããŒãå«ãŸããŠããŸãã
InnoDBã®ã¯ã©ã¹ã¿ã€ã³ããã¯ã¹ã«ã€ããŠ
åèãªã³ã¯ïŒ
MySQL with InnoDB ã®ã€ã³ããã¯ã¹ã®åºç€ç¥èãšãããã¡ãªééã
http://techlife.cookpad.com/entry/2017/04/18/092524
ã«ããªã³ã°ã€ã³ããã¯ã¹
å¿
èŠãªããŒã¿ãã€ã³ããã¯ã¹ã«å«ãŸããããã«èšå®ããŠããããšã§ããŒã¿ã®ãã§ãããæ©ãããææ³ã§ãã
泚ïŒããã¿ã«ã€ã³ããã¯ã¹ã«ããŒã¿ã远å ããã®ã¯éã«ããã©ãŒãã³ã¹ã®äœäžãæããŸã
åèãªã³ã¯ïŒ
MySQLã®ã€ã³ããã¯ã¹ãåŠã¶ (1)
http://d.hatena.ne.jp/a666666/20100920/1284992435
é
ãJOINãéããã
JOINã¯ããé
ããšèšãããŸããããã¯ãªãã§ããããã
Nested Loop Join
MySQLã§ã¯ç°¡åã«èšããšããã®NLPããå®è£
ãããŠããŸããã
ã§ã¯ãNested Loop Joinã¯ã©ã®ãããªä»çµã¿ã§åããŠããã®ã§ããããã
Nested Loopãšãããããã§ãããå
¥ãåã®ã«ãŒããåã£ãŠåããŠããŸãã
ããŒãã«Aã®äžè¡ããã§ããããŠãããŒãã«Bãããããã³ã°ãããã®ãæ¢ããŠããŸãããŒãã«Aã®äžè¡ããã§ããããŠã»ã»ã»ã»ãç¹°ãè¿ããŠãããŸãã
èšç®éãšããŠãšãŠã倧ãããªãããã ãšããã®ã¯ããåãã£ãŠé ãããšæããŸãã
ããŒãã«Aã100è¡å¢ããã°ããŒãã«Bã100åèµ°æ»ããªããšãããªããªããŸããããã»ã»ã»ã
ããããèšç®éOãããŒãã«AÃããŒãã«Bã§ããããšãããããé ãããšæããŸãã
ã€ãŸãéãããããã«ã¯ããã®ããç®ã®çµæãå°ããåºæ¥ãã°è¯ããšãã圢ã«ãªããŸãã
å®äŸã§åŠã¶ãJOIN (NLJ) ãé
ããªãçå±ãšå¯ŸåŠæ³
https://qiita.com/yuku_t/items/208be188eef17699c7a5
ããŒãã£ã·ã§ãã³ã°
ããŒã¿ã1ã«æã§ã¯ãªããç¹å®ã®æ¡ä»¶ã«åºã¥ããŠæ¯ãåããè¡ãå¥ã
ã«ä¿åããä»çµã¿ã§ãã
äžæã«èšå®ãè¡ãããšã§ããã©ãŒãã³ã¹ãäžããããšãåºæ¥ãŸãã
ãŸããã°ããŒã¿ãªã©äžéšã®ããŒã¿ããŸãšããŠæ¶ãããããéã«ã圹ã«ç«ã¡ãŸãã
ãã ããæ¯ãåããè¡ãçºã«ã¯æ¡ä»¶ãšããããŒã¿ããã©ã€ããªããŒã«å«ãŸããŠããå¿
èŠããããŸãã
MySQL ããŒãã£ã·ã§ãã³ã°ãŸãšã
https://qiita.com/taroshin/items/608076c9f8e09497c4b1
MySQLã®èšå®ã«ãããã¥ãŒãã³ã°
ã¡ã¢ãªåšãã®èšå®ãè¡ãéã«ã¯ãã°ããŒãã«ãããã¡ãšã¹ã¬ãããããã¡ã®éãã«æ°ãä»ããŸãããã
ãŸãã確ä¿éãå®éã®ã¡ã¢ãªããªãŒããŒããªãããã«æ³šæããŸãããã
InnoDBã®èšå®
innodb_file_per_table
InnoDBã®ããŒã¿ãããŒãã«ããšã«ãã£ã¹ã¯ã«ä¿åããŸãã
ããŒãã«ããšã«ããŒã¿ãèšé²ãããã®ã§ããã©ãŒãã³ã¹ãäžãããŸãã
innodb_buffer_pool_size
InnoDBã«ãããŠãããŒã¿ãšã€ã³ããã¯ã¹ãä¿æãããããã¡ã§ãã
ã¡ã¢ãªå
šäœã®80%çšåºŠãšè¯ãèšãããŸãã
innodb_log_file_size
æžã蟌ã¿ãé«è² è·ã®ã·ã¹ãã ã§ã¯å€§ãããããšè¯ãã
ãã®ãã¡ã€ã«ããã£ã±ãã«ãªããšæŽæ°ããŒã¿ã®ãã£ã¹ã¯ãžã®ãã©ãã·ã¥ãè¡ãããã
ã¯ãšãªãã£ãã·ã¥ã®èšå®
query_cache_type
ã¯ãšãªãã£ãã·ã¥ãè¡ãã¿ã€ã
0:ãã£ãã·ã¥ããªã
1:å
šãŠã®åç
§ç³»ã®ã¯ãšãªããã£ãã·ã¥ããïŒSELECT SQL_NO_CACHEã§å§ãŸãã¯ãšãªã¯ãã£ãã·ã¥ããªãïŒ
2:SELECT SQL_CACHEã§å§ãŸãã¯ãšãªããã£ãã·ã¥ãã
query_cache_size
ã¯ãšãªãã£ãã·ã¥ããã¡ã¢ãªé
åèãªã³ã¯ïŒ
éãªMySQLããã©ãŒãã³ã¹ãã¥ãŒãã³ã°
https://www.slideshare.net/yoku0825/mysql-57449062
MySQL ããã©ãŒãã³ã¹ãã¥ãŒãã³ã°
http://momota.github.io/blog/2017/04/20/mysql/
5åã§ã§ãããMySQLã®ã¡ã¢ãªé¢ä¿ã®ãã¥ãŒãã³ã°ïŒ
http://dsas.blog.klab.org/archives/50860867.html
MySQL ã¯ãšãªãã£ãã·ã¥ã®æŠèŠãšå°å
¥ã»è©äŸ¡æ¹æ³
https://weblabo.oscasierra.net/mysql-query-cache/
çµããã«
MySQLã®èšå®ãã¥ãŒãã³ã°ã«é¢ããŠã¯ãšããã倧äºãªéšåã«ãšã©ããŸããã
å€ãã®éšåã¯åèãªã³ã¯ã§ãèªã£ãŠé ããŠãã®ã§ãã¡ãã«ãä»»ãããã®ãè¯ããšå€æããŸããã
ãã ãMySQLã®ãã¥ãŒãã³ã°ãããã§ãããããããã¢ããªåŽã§ã®ããŒãã«èšèšãSQLãéèŠã ãšæã£ãŠããŸãã
ããã«æžããããšãå°ãã§ãå€ãã®æ¹ã®åœ¹ã«ç«ã£ãããªãšæããŸãã
仿¥ã¯ãã®ãžãã§ãã§ã¯ã§ã¯ã