[B! tips][mysql] ymm1xã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

ymm1x id:ymm1x

tipsã¨mysqlã«é–¢ã™ã‚‹ymm1xã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (7)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

äºŒåƒä¸‡ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã¸ã®alterã‚’ã‚µãƒ¼ãƒ“ã‚¹ã‚’æ¢ã‚ãšã«æµã™ | All Your Bugs Are Belong To Ass
â€»ã“ã®ã‚¨ãƒ³ãƒˆãƒªã¯MySQL Casual Advent Calendar 2015ã®5æ—¥ç›®ã®ã‚¨ãƒ³ãƒˆãƒªã§ã™ã€‚ openark-kit ã¨ã„ã†ã‚‚ã®ã«ã¤ã„ã¦ ã“ã“ã¾ã§èªã‚“ã§ã‚ã‹ã£ãŸæ–¹ã¯ã€ã“ã®å…ˆã‚’èªã‚€å¿…è¦ã¯ã‚ã‚Šã¾ã›ã‚“ã€‚ openark-kitã¨ã¯ã€mysqlã®é‹ç”¨ã«ä¾¿åˆ©ãªãƒ„ãƒ¼ãƒ«ã‚ãƒƒãƒˆã‚’14å€‹ã‚ã¤ã‚ãŸã‚½ãƒ•ãƒˆã‚¦ã‚§ã‚¢ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã§ã™ã€‚ Shlomi Noachã¨ã„ã†æ–¹ãŒPythonã§é–‹ç™ºã—ã¦ãŠã‚Šã€å°‘ãªãã¨ã‚‚2009å¹´ã«ç™ºè¡¨ã•ã‚Œã¦ã„ã‚‹ã‚ˆã†ã§ã™ã€‚ 2015-12-05æ™‚ç‚¹ã§ã®æœ€æ–°ç‰ˆã¯196.1ã¨ãªã£ã¦ãŠã‚Šã€.tar.gz ãŠã‚ˆã³ .deb ã§é…å¸ƒã•ã‚Œã¦ãŠã‚Šã¾ã™ã€‚ ã“ã®ã‚¨ãƒ³ãƒˆãƒªã‚’æ›¸ã„ãŸèƒŒæ™¯äº‹æƒ… ãã‚‚ãã‚‚åƒ•è‡ªèº«ã€50ã‚’è¶…ãˆã‚‹ã‚¯ãƒ©ã‚¹ã‚¿åŒ–ã•ã‚ŒãŸmysqlãƒŽãƒ¼ãƒ‰ã¨ä¸€ç·’ã«æ¥å‹™ç”Ÿæ´»ã‚’é€ã£ã¦ãŠã‚Šã¾ã™ã€‚ ã¨ã“ã‚ãŒã€ã‚·ã‚¹ãƒ†ãƒ ãŒéžå¸¸ã«å¤ãã•ã„æ§‹æˆã®ãŸã‚ã€åˆè¨ˆãƒ¬ã‚³ãƒ¼ãƒ‰æ•°ãŒ2å„„ã‹ã‚‰3å„„ç¨‹åº¦ã‚ã‚‹åž‚ç›´åˆ†å‰²ã•ã‚ŒãŸãƒ†ãƒ¼ãƒ–ãƒ«ã«å¯¾ã—ALTERã‚’æŠ•
ymm1x 2015/12/06
mysql

tips
ãƒªãƒ³ã‚¯
ç¾ä»£ã®MySQLã¯ã‚‚ã†ä¸è¦ã«INã‚’EXISTSã«æ›¸ãæ›ãˆãªã„
æ¼¢(ã‚ªãƒˆã‚³)ã®ã‚³ãƒ³ãƒ”ãƒ¥ãƒ¼ã‚¿é“: ãªãœMySQLã®ã‚µãƒ–ã‚¯ã‚¨ãƒªã¯é…ã„ã®ã‹ã€‚ ã“ã®è¨˜äº‹ã¯ 2009/3/25 ã«æ›¸ã‹ã‚ŒãŸã‚‚ã®ã®ã‚ˆã†ã§ã‚ã‚‹ã€‚ 2009å¹´3æœˆã¨ã„ãˆã°MySQL 5.1ãŒGAã«ãªã£ã¦ã‚ãšã‹åŠå¹´ã€MySQL 6.0.10-alphaãŒãƒªãƒªãƒ¼ã‚¹ã•ã‚ŒãŸé ƒã§ã€MariaDBã‚‚ã¾ã å§¿ã‚’è¦‹ã›ã¦ã„ãªã„é ƒã ã€‚ æ™‚ã¯æµã‚Œã¦2015å¹´ã€MySQL 5.6ãŒGAã«ãªã£ã¦æ—©2å¹´åŠã€5.7ã®GAï¾ï¾€ï¾žï½§-? (ï½¥âˆ€ï½¥ )ã£/å‡µâŒ’â˜†ï¾ï¾ï¾ï¾ ãªé ƒãªã®ã§ã€ã‚‚ã†ã„ã„åŠ æ¸›èª°ã‹è¨€ã£ã¦ãã‚Œã¦ã‚‚ã„ã„ã‚“ã˜ã‚ƒãªã„ã‹ã¨æ€ã†ã€‚ ã‚‚ã†MySQL(5.6)ã¯ä¸è¦ã«INã‚’EXISTSã«æ›¸ãæ›ãˆãŸã‚Šã—ãªã„ã‚“ã ã‚ˆã£ã¦ mysql51> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN -> (SELECT CountryCode FROM City WHER
ymm1x 2015/11/20
mysql

tips
ãƒªãƒ³ã‚¯
MySQL ã®NULL ã§ã¯ã¾ã£ãŸã“ã¨ã‚ã‚Œã“ã‚Œ - LukeSilviaâ€™s diary
MySQL ã«é™ã‚‰ãšã€SQL ã®NULL ã®ä»•æ§˜ã«ã¯ä½•å›žã‹ã€Œãˆã£ã€ã¨é©šãã“ã¨ãŒã‚ã£ãŸã®ã§ãƒ¡ãƒ¢ã—ã¦ãŠã“ã†ã¨æ€ã„ã¾ã™ã€‚5.1 ç‰ˆã®æ—¥æœ¬èªžãƒžãƒ‹ãƒ¥ã‚¢ãƒ«ãŒãªã‹ã£ãŸã‚‚ã®ã«ã¤ã„ã¦ã¯ã€4.1 ã®ãƒžãƒ‹ãƒ¥ã‚¢ãƒ«ã‚’å‚ç…§ã—ã¾ã—ãŸã€‚ ãã‚‚ãã‚‚NULL ã¯ä½•ã‚’æ„å‘³ã™ã‚‹ã‹ NULL ã¯æœªå®šç¾©ã¾ãŸã¯ã€ä¸æ˜Žã‚’æ„å‘³ã™ã‚‹ã€‚ã€Œé›»è©±ç•ªå·ã‚’æŒãŸãªã„ã€ã¨ã„ã†ã“ã¨ã‚’è¡¨ç¾ã™ã‚‹å ´åˆã¯ã€NULL ã§ã¯ãªãã€ç©ºã®æ–‡å—åˆ—ã‚’ä½¿ã†ã€‚ NULL å€¤ã¨ã„ã†ã‚‚ã®ã‚’ SQL åˆå¿ƒè€…ã¯ã‚ˆãæ··ä¹±ã—ã¾ã™ã€‚SQL åˆå¿ƒè€…ã¯ã€å¤šãã®å ´åˆã€NULL ãŒç©ºæ–‡å— "" ã¨åŒã˜ã§ã‚ã‚‹ã¨è€ƒãˆã¦ã—ã¾ã„ã¾ã™ã€‚ã“ã‚Œã¯é•ã„ã¾ã™ã€‚ãŸã¨ãˆã°ã€ä»¥ä¸‹ã®ã‚¹ãƒ†ãƒ¼ãƒˆãƒ¡ãƒ³ãƒˆã¯å®Œå…¨ã«åˆ¥ã®ã‚‚ã®ã§ã™ã€‚ mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); ã©ã¡ã‚‰ã®ã‚¹
ymm1x 2015/05/13
mysql

db

design

tips
ãƒªãƒ³ã‚¯
SQLã§order byã¨group byã‚’ä½µç”¨ã™ã‚‹æ™‚ã®æ›¸ãæ–¹ - ã¾ã‚“ã¨ã‚‹ã½ã£ã¨
çã—ãSQLæ›¸ã„ã¦ãŸã‚‰è©°ã¾ã£ãŸã®ã§ãƒ¡ãƒ¢ã—ã¦ãŠãã¾ã™ã€‚ ã‚„ã‚ŠãŸã„ã“ã¨ ã©ã°ãƒ¼ã£ã¨ãƒ‡ãƒ¼ã‚¿ãŒè²¯ã¾ã£ã¦ã„ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ãŒã‚ã£ãŸã¨ã—ã¦ã€‚ä¾‹ãˆã°è‰²ã‚“ãªãƒ¦ãƒ¼ã‚¶ã®ã‚¢ã‚¯ã‚»ã‚¹ãƒã‚°ãŒè²¯ã¾ã£ã¦ã„ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’æƒ³å®šã—ã¾ã™ã€‚ãã®ä¸ã‹ã‚‰å„ãƒ¦ãƒ¼ã‚¶ã®æœ€æ–°ã‚¢ã‚¯ã‚»ã‚¹æƒ…å ±ã‚’å–ã‚ŠãŸã„æ™‚ã«ã©ã†ã™ã‚‹ã‹ã€‚ ã€Œdateã§order byã—ã¦ã‹ã‚‰ã€group byã™ã‚Œã°ã„ã„ã‚“ã˜ã‚ƒãªã„ã®ï¼Ÿã€ã¨æ€ã£ã¦ãŸã‚“ã™ã‘ã©ã€ã©ã†ã‚„ã‚‰ã“ã®2ã¤ã¯ä½µç”¨ã™ã‚‹ã¨å…ˆã«group byã•ã‚Œã¦ãã®çµæžœãŒorder byã•ã‚Œã‚‹ãã†ãªã€‚ãã†ã„ã†ä»•æ§˜ã‚‰ã—ã„â€¦ã€‚ä»¥ä¸‹ã®è¨˜äº‹ãŒå‚è€ƒã«ãªã‚Šã¾ã—ãŸã€‚ Mysqlã®ã‚µãƒ–ã‚¯ã‚¨ãƒªã¨groupã€orderã®å„ªå…ˆåº¦ â€“ Life order byã¨group byã‚’ä½µç”¨ã™ã‚‹æ™‚ã®ã‚µãƒ–ã‚¯ã‚¨ãƒª ã˜ã‚ƒã‚ã€ã‚µãƒ–ã‚¯ã‚¨ãƒªã§dateã§order byã—ãŸçµæžœã«å¯¾ã—ã¦group byã—ãŸã‚‰ãˆãˆã‚„ã‚“ã¨è¨€ã†è©±ã«ãªã‚Šã¾ã—ã¦ã€å…·ä½“çš„ã«ã¯ã“ã‚“ãªæ„Ÿã˜ã«ãªã‚Šã¾ã—ãŸã€‚å†…å®¹ã¯å¤‰æ•°åã‚’è¦‹ã¦å¯Ÿ
ymm1x 2015/02/25
mysql

tips
ãƒªãƒ³ã‚¯
YappoLogs: ãªãœ SQL_CALC_FOUND_ROWS ã‚„ LIMIT OFFSET ã®ãƒšãƒ¼ã‚¸ãƒ³ã‚°ãŒè‰¯ãç„¡ã„ã®ã‹
ãªãœ SQL_CALC_FOUND_ROWS ã‚„ LIMIT OFFSET ã®ãƒšãƒ¼ã‚¸ãƒ³ã‚°ãŒè‰¯ãç„¡ã„ã®ã‹ ã“ã“æœ€è¿‘ã®å¤§è¦æ¨¡ã‚µãƒ¼ãƒ“ã‚¹é–¢é€£ã—ãŸãƒ‡ãƒ¼ã‚¿ãƒšãƒ¼ã‚¸ãƒ³ã‚°è€ƒã§ã™ã€‚ mysql 5.5.34 ã§è©¦ã—ã¦è¨˜äº‹æ›¸ã„ã¦ã¾ã™ã€‚ bigdata ãƒ†ãƒ¼ãƒ–ãƒ«ã¯ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ãªã‚«ãƒ©ãƒ ãŒã‚ã‚‹å‰æã§ã™ã€‚ã‚‚ã¡ã‚ã‚“ InnoDB ã§ã™ã€‚ 2014å¹´ãªã‚“ã ã‹ã‚‰COUNT(*)ã¨ã‹SQL_CALC_FOUND_ROWSã¨ã‹LIMIT OFFSETã®ãƒšãƒ¼ã‚¸ãƒ³ã‚°ã¯ã‚„ã‚ã‚ˆã†ãœ - Togetterã¾ã¨ã‚ãŒç™ºç«¯ã«ã¿ãˆã‚‹ã‘ã©ã€ã‚ã‚Šã¨æ˜”ã‹ã‚‰è©±ã•ã‚Œã¦ã‚‹äº‹ãªã‚“ã ã‘ã©ã€ã€Œnippondanji SQL_CALC_FOUND_ROWSã€ã§ã‚°ã‚°ã£ã¦ã‚‚æœ‰ç”¨ãªæƒ…å ±ãªã„ã—æ–‡æ›¸åŒ–ã•ã‚Œã¦ãªã„ã‹ã‚‰ã—ã¨ãã€‚ ãƒšãƒ¼ã‚¸ãƒ³ã‚°å‡¦ç†ã§ä½¿ã‚ã‚ŒãŒã¡ãªæ©Ÿèƒ½ã§ã™ã€‚ S
ymm1x 2014/09/30
æ¤œç´¢çµæžœã®è¡Œæ•°ã‚’å¾—ã‚‹ã¨ãã®ã‚³ã‚¹ãƒˆã®è©±ã€‚Twitter ã®ã‚ˆã†ã«æ¬¡ã®æ¤œç´¢çµæžœãŒã‚ã‚‹ã‹ã©ã†ã‹ãŒåˆ†ã‹ã‚‹ã ã‘ã§æ¸ˆã‚€ãƒ‡ã‚¶ã‚¤ãƒ³ã«ã—ã¦ãŠãã¨æ¥½ãã†ã ãã€‚

mysql

ui

tips

performance

paging
ãƒªãƒ³ã‚¯
mysqlã‚³ãƒžãƒ³ãƒ‰ã§ã€ãƒ†ãƒ¼ãƒ–ãƒ«åã¨ã‹ã‚«ãƒ©ãƒ åã®è£œå®Œ(completion)ã‚’ã™ã‚‹æ–¹æ³• - (ã²)ãƒ¡ãƒ¢
è¿½è¨˜: rehash(auto-rehashã‚‚å«ã‚€)ã™ã‚‹ã¨ã€SQLæ–‡ã®è£œå®Œ(seleã§ã‚¿ãƒ–æ‰“éµã¨ã‹)ãŒåŠ¹ã‹ãªããªã‚‹ã‚ˆã¨ã€ã¯ã™å‘ã‹ã„ã®äººã«æ•™ãˆã¦ã‚‚ã‚‰ã„ã¾ã—ãŸã€‚ å€‹äººçš„ã«ã¯ã€SQLã¯ã€Œmysql> help selectã€ã¨ã‹ã§ã‚ªãƒ³ãƒ©ã‚¤ãƒ³ãƒ˜ãƒ«ãƒ—ãŒã³ã‚‡ã£ã¨å‡ºã‚‹ã®ã§ã€ã‚¹ã‚ãƒ¼ãƒžã®è£œå®ŒãŒã§ãã‚‹ã‚“ãªã‚‰SQLã®è£œå®Œã¯ã¨ã‚Šã‚ãˆãšã‚ãã‚‰ã‚ã¦ã‚‚ã„ã„ã‹ãªã¨æ€ã£ã¦ã„ã¾ã™ã€‚ å¸¸ã€…ã€ãƒ†ãƒ¼ãƒ–ãƒ«åã¨ã‹è£œå®Œã§ãã‚‹ã¨ã„ã„ãªãƒ¼ã¨æ€ã£ã¦ã„ãŸã®ã§ã€ãƒœãƒã£ã¨ãã„ã¦ã¿ãŸã‚‰æ•™ãˆã¦ã‚‚ã‚‰ã„ã¾ã—ãŸã€‚ã‚ã–ãƒ¼ãƒ¼ãƒ¼ãƒ¼ã£ã™ï¼ id:mikihoshi++ id:tokuhirom++ id:precuredaisuki++ ãŠã‹ã’ã§åŠ¹çŽ‡ãŒ300%ä¸ŠãŒã‚Šã¾ã—ãŸã€‚(Benchmark::Stopwatchã§è¨ˆæ¸¬) http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html#option
ymm1x 2014/08/29
ã“ã‚Œã‚‚ã£ã¨æ—©ãçŸ¥ã£ã¦ãŠããŸã‹ã£ãŸâ€¦

mysql

command

tips
ãƒªãƒ³ã‚¯
KOSHIGOEå¦ç¿’å¸³ - [MySQL] ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹é–¢é€£ãƒ¡ãƒ¢
{{toc_here}} InnoDB ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° MySQL :: MySQL 5.1 ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ãƒžãƒ‹ãƒ¥ã‚¢ãƒ« :: 13.5.11 InnoDB ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° ãƒ’ãƒ³ãƒˆ MySQL :: MySQL 5.1 Reference Manual :: 13.6.13.1 InnoDB Performance Tuning Tips é•·éŽãŽã‚‹ PRIMARY KEY ã‚’é¿ã‘ã¦ãƒ‡ã‚£ã‚¹ã‚¯é ˜åŸŸã®ç„¡é§„é£ã„ã‚’é¿ã‘ã‚‹ ã‚»ã‚«ãƒ³ãƒ€ãƒªã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ç”¨ã«ä½™è¨ˆãªé ˜åŸŸã‚’ä½¿ã‚ãªã„ã‚ˆã†ã€é•·ã„ä¸»ã‚ãƒ¼ã‚’é¿ã‘ã‚‹ ä¸»ã‚ãƒ¼ãŒé•·ã„å ´åˆã€ä»£ã‚ã‚Šã« AUTO_INCREMENT ãªã‚«ãƒ©ãƒ ã‚’ä¸»ã‚ãƒ¼ã¨ã—ã¦ä½œæˆã™ã‚‹ã¨ã‚ˆã„ è£œè¶³ MySQL :: MySQL 5.1 ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ãƒžãƒ‹ãƒ¥ã‚¢ãƒ« :: 13.5.13 InnoDB ãƒ†ãƒ¼ãƒ–ãƒ«ã¨ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹æ§‹é€ MySQL :: MySQL 5.1 Reference Ma
ymm1x 2014/08/25
å¤§é‡ãƒ‡ãƒ¼ã‚¿ã‚¤ãƒ³ãƒãƒ¼ãƒˆã«é–¢ã™ã‚‹ tips ãŒå‚è€ƒã«ãªã£ãŸ

##

mysql

tips

index

performance
ãƒªãƒ³ã‚¯
1