[B! database] y-imayaã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

y-imaya id:y-imaya

databaseã«é–¢ã™ã‚‹y-imayaã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (8)

${{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}}

MySQLã‚¯ã‚¨ãƒªãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹æ”¹å–„ç°¡æ˜“ã¾ã¨ã‚ - Qiita
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article? æ•°å¹´ã‚„ã£ã¦ãªã„ã¨è¨˜æ†¶ã®å½¼æ–¹ã«é£›ã‚“ã§ã„ããã†ã ã£ãŸã®ã§ã€MySQLã®ã‚¯ã‚¨ãƒªæ”¹å–„æ–¹æ³•ã®ãƒ†ãƒ³ãƒ—ãƒ¬ã‚’è‡ªåˆ†ç”¨ã«æ˜Žè¨˜ã€‚ ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªã‚’é™¤åŽ»ã™ã‚‹äº‹ã€‚ åˆã‚ã¯ã¨ã«ã‹ãè¦³å¯Ÿã€‚ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªã‚’å‡ºåŠ›ã•ã›ã¦ã€è¦³å¯Ÿã™ã‚‹ã€‚ indexãŒåŠ¹ã‹ãªã„ã‚¯ã‚¨ãƒªã‚’æŽ’é™¤ã™ã‚‹ã€‚ indexãŒäºˆæœŸã§ããªã„æ¡ä»¶åˆ†å²ã«ã‚ˆã‚‹ã‚¯ã‚¨ãƒªã‚’å»ƒæ¢ã™ã‚‹ã€‚ å ´åˆã«ã‚ˆã£ã¦ã¯ã‚½ãƒ¼ãƒˆã‚’ã•ã›ãªã„ã€‚ã‚³ãƒ¼ãƒ‰å´ã§ã‚½ãƒ¼ãƒˆã•ã›ã‚‹ã€‚ JOINã‚’ã‚ã–ã¨ã•ã›ãªã„ã®ã‚‚ä¸€ã¤ã®æ‰‹ã€‚å¾Œã«DBåˆ†é›¢ãƒ¬ãƒ™ãƒ«ã®ã‚·ãƒ£ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ç‰ãŒç™ºç”Ÿã™ã‚‹å¯èƒ½æ€§ã®ã‚ã‚‹ã‚ˆã†ãªã‚·ã‚¹ãƒ†ãƒ ã§ã¯ã€JOINç¦æ¢ã«ã™ã‚‹äº‹ã¯æ±ºã—ã¦é–“é•ã£ã¦ã¯ã„ãªã„ã€‚ indexã‚’å¿…ãšåŠ¹ã‹ã›ã‚‹ ãƒ¬
y-imaya 2017/05/22
performance

mysql

database
ãƒªãƒ³ã‚¯
MySQLã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®ãŠæ‰‹å…¥ã‚Œã®åŸºæœ¬ | Yakst
Percona Database Performance Blogã®ç¿»è¨³ã€‚æ—¢ã«é‹ç”¨ã‚’å§‹ã‚ãŸãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã§ã€ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ãŒæ£ã—ãä½¿ã‚ã‚Œã¦ã„ã‚‹ã‹ã€ç„¡é§„ã‚„ä¸è¶³ãŒãªã„ã‹ã‚’ç¢ºèªã™ã‚‹æ–¹æ³•ã®ã¾ã¨ã‚è¨˜äº‹ã€‚ã‚¯ã‚¨ãƒªã‚’ã²ã¨ã¤ã²ã¨ã¤ç¢ºèªã™ã‚‹ã®ã§ã¯ãªãã€çµ±è¨ˆæƒ…å ±ã‚’å…ƒã«åˆ¤æ–ã™ã‚‹åˆ†ã‹ã‚Šã‚„ã™ã„æ–¹æ³•ã€‚ ã“ã®ãƒ–ãƒã‚°è¨˜äº‹ã§ã¯ã€MySQLã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã«æ‰‹å…¥ã‚Œã™ã‚‹åŸºæœ¬çš„ãªã‚¹ãƒ†ãƒƒãƒ—ã«ã¤ã„ã¦è¦‹ã¦ã„ã“ã†ã¨æ€ã„ã¾ã™ã€‚ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã¯ã€ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹æ¬¡ç¬¬ã§ãƒã‚¤ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ã«ã‚‚ã€å½¹ç«‹ãŸãšã§é…ãã¦å¤§å¤‰ã«ã‚‚ãªã‚Šã†ã‚‹ã“ã¨ã¯ã”å˜çŸ¥ã§ã—ã‚‡ã†ã€‚ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã¯ã€æ™‚ã€…æ‰‹å…¥ã‚Œã‚’ã™ã‚‹ä¾¡å€¤ãŒã‚ã‚‹éžå¸¸ã«é‡è¦ãªã‚‚ã®ã§ã™ã€‚ãã‚Œã§ã¯ã€ä½•ã‚’ãƒã‚§ãƒƒã‚¯ã™ã‚Œã°ã‚ˆã„ã®ã§ã—ã‚‡ã†ã‹ï¼Ÿé †ä¸åŒã§ã™ãŒã€ç¢ºèªã™ã¹ãç‚¹ã‚’æŒ™ã’ã¦ã¿ã¾ã™ã€‚ 1. ä½¿ã‚ã‚Œã¦ã„ãªã„ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ sysã‚¹ã‚ãƒ¼ãƒžã§ã€ä½¿ã‚ã‚Œã¦ã„ãªã„ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã‚’ã¨ã¦ã‚‚ç°¡å˜ã«è¦‹ã¤ã‘ã‚‰ã‚Œã¾ã™ã€‚ schema_unused_indexes ãƒ“ãƒ¥ãƒ¼ã‚’
y-imaya 2016/10/03
mysql

performance

database
ãƒªãƒ³ã‚¯
ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã®ãŸã‚ã®MySQLå…¥é–€ | BLOG - DeNA Engineering
ã“ã‚“ã«ã¡ã¯ã“ã‚“ã«ã¡ã¯ã€‚æœ€è¿‘ãŠè…¹ç—›ã„ã°ã£ã‹ã‚Šè¨€ã£ã¦ã‚‹ã“ã¨ã§æœ‰åãªiwanagaã§ã™ã€‚ DeNAã¯å¤–éƒ¨çš„ã«ã¯ãƒ—ãƒ©ãƒƒãƒˆãƒ•ã‚©ãƒ¼ãƒ çš„ãªéƒ¨åˆ†ã®æ–¹ãŒãƒ•ã‚£ãƒ¼ãƒãƒ£ãƒ¼ã•ã‚Œã‚‹ã“ã¨ãŒå¤šã„ã§ã™ãŒã€å®Ÿã¯ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã®æä¾›ã‚‚è¡Œã£ã¦ã„ã¾ã™ã€‚æ€ªç›—ãƒãƒ¯ã‚¤ãƒ¤ãƒ«ã¨ã‹ã€ã©ã“ã‹ã§èžã„ãŸã“ã¨ãŒã‚ã‚‹ã®ã§ã¯ãªã„ã§ã—ã‚‡ã†ã‹ã€‚ åƒ•ã¯DeNAã§ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ãŒèª•ç”Ÿã—ãŸè¾ºã‚Šã‹ã‚‰ãšã£ã¨ã‚µãƒ¼ãƒã‚µã‚¤ãƒ‰ã‚’è¦‹ã¦ãã¾ã—ãŸãŒã€ãã‚“ãªé‹ç”¨ã®ä¸ã§è‡ªåˆ†ãŒè²¯ã‚ã¦ããŸçŸ¥è¦‹ã¨ã‹TIPSã‚’ã”ç´¹ä»‹ã—ãŸã„ã¨æ€ã„ã¾ã™ã€‚ ã‹ã‚Œã“ã‚Œ10ã‚¿ã‚¤ãƒˆãƒ«è¿‘ãã¯ãƒ¬ãƒ“ãƒ¥ãƒ¼ã—ãŸã‚Šé‹ç”¨ã—ãŸã‚Šã—ã¦ã‚‹ãŸã‚çµæ§‹è¨€ã„ãŸã„ã“ã¨ã¯ã„ã£ã±ã„ã‚ã‚‹ã®ã§ã€å°å‡ºã—ã«ã—ã¤ã¤è©•åˆ¤ã‚ˆã‘ã‚Œã°æ¬¡ã‚‚æ›¸ãã¾ã™ã€‚ ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã®ãŸã‚ã®MySQLå…¥é–€ä¸€è¦§ ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã®ãŸã‚ã®MySQLå…¥é–€ - Techno logy of DeNA ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã®ãŸã‚ã®MySQLå…¥é–€2 - Techno logy of DeNA ã€ŒMySQL
y-imaya 2015/03/14
mysql

performance

database
ãƒªãƒ³ã‚¯
ORDER BYç‹™ã„ã®ã‚ãƒ¼ãŒä½•æ•…é€Ÿã„ã‹ - æ™‚è¨ˆã‚’å£Šã›
ã©ã®æœ€é©åŒ–ãŒåŠ¹ãã‚“ã‚„â€¦ã¨ã‚°ã‚°ã£ãŸã€‚ ä»¥å‰ã‚‚èª¿ã¹ãŸæ°—ãŒã—ãŸãŒæ€ã„ã ã›ãšã€ã²ãŸã™ã‚‰ã‚°ã‚°ã‚‹ç¾½ç›®ã«ãªã£ãŸã®ã§ã€ åçœã—ã¦ãƒ–ãƒã‚°ã«æ®‹ã™ã€‚ãµã¤ãƒ¼ã«mysqlã®documentã«æ›¸ã„ã¦ã‚ã£ãŸã€‚ http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. ãƒãƒ¼ã‚¸ãƒ§ãƒ³å¤ã„ã‘ã©æ—¥æœ¬èªžã®ã»
y-imaya 2014/03/24
mysql

database
ãƒªãƒ³ã‚¯
Devsã®å¸¸è˜ã€DBAã¯éžå¸¸è˜
1. MySQL Admin ãŒè¦‹ãŸ Devs ã®å¸¸è˜ã€ DBA ã¯éžå¸¸è˜ 2013/09/14 yoku0825@MyNA PHP Conference 2013 2. ï¼¼ã“ã‚“ã«ã¡ã¯ï¼ï¼ â— yoku0825 â— ã¨ã‚ã‚‹ä¼æ¥ã® DBA â— MySQL æ´ 5 å¹´ãã‚‰ã„ â— ã‚ªãƒ©ã‚¯ã‚Œãªã„ â— ãƒã‚¹ã‚°ã‚Œãªã„ â— å«ã®å¤« â— ã›ãŒã‚Œã®çˆ¶ â— æ—¥æœ¬ MySQL ãƒ¦ãƒ¼ã‚¶ä¼š (MyNA) ã®ã‚¹ãƒ™ã‚Šæ‹…å½“ 3. ï¼¼ã—ã‚ƒã¹ã‚‹ã“ã¨ï¼ï¼ â— æ—¥å¸¸çš„ã« MySQL ã®ã‚½ãƒ¼ã‚¹ã‚³ãƒ¼ãƒ‰ã«è§¦ã‚Œã‚‹å¤‰æ…‹ DBA ãŒãƒ•ãƒ„ãƒ¼ã® Devs ã«æŠ•ã’ãŸæ„›ã®ãƒžã‚µã‚«ãƒªé›† ( ã®ã¤ã‚‚ã‚Š ) â— ã‚¦ãƒã®é–‹ç™ºè¨€èªžã¯ PHP > Java >> Ruby ã‚‰ã—ã„ã§ã™ â— ã‚¦ãƒã§ã¯ DBA ãŒã‚µãƒ¼ãƒãƒ¼ã®æ§‹ç¯‰ã€ Devs ãŒè¨è¨ˆãƒ» ãƒ†ãƒ¼ãƒ–ãƒ«æ§‹ç¯‰ãƒ»é‹å–¶ã€ DBA ã¯ãƒˆãƒ©ãƒ–ãƒ«ã‚·ãƒ¥ãƒ¼ãƒˆã‚„æ”¹ å–„ææ¡ˆ ( é‹ç”¨ ) ã€ã¨ã„ã†ã‚µã‚¤ã‚¯ãƒ«ã§å›žã—ã¦ã„ã¾ã™ã€‚
y-imaya 2014/03/24
mysql

database
ãƒªãƒ³ã‚¯
ãƒ ãƒƒã‚¯ã€Œãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å¾¹åº•æ”»ç•¥ã€ - MySQL/Redis/MongoDB/Redshift
æœ€è¿‘ç™ºå£²ã•ã‚ŒãŸæŠ€è¡“è©•è«–ç¤¾ã®ãƒ ãƒƒã‚¯ã€Œãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å¾¹åº•æ”»ç•¥ã€ã«å¯„ç¨¿ã—ã¾ã—ãŸã€‚ ã“ã®æœ¬ã¯ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãŸã‚ã®æœ¬ã¨ã„ã†ã“ã¨ã§ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹è¨è¨ˆã€SQLã€MySQLã€Redisã€Mongo DBã€Redshiftã¨ã„ã†ä»£è¡¨çš„ãªè¦ç´ æŠ€è¡“ã«ã¤ã„ã¦ã®ã¾ã¨ã‚ã¨ãªã£ã¦ã„ã¾ã™ã€‚å„ãƒ—ãƒãƒ€ã‚¯ãƒˆ(MySQLã€Redisã€Mongo DBã€Redshift)ã«ã¤ã„ã¦ã¯ã€ç¾å ´ã§å®Ÿéš›ã«æœ¬æ ¼çš„ã«ä½¿ã‚ã‚Œã¦ã„ã‚‹æ–¹ã€…ã«ã‚ˆã‚‹è¨˜äº‹ãªã®ã§å¤§ã„ã«å‚è€ƒã«ãªã‚‹ã¨æ€ã„ã¾ã™ã€‚ ç§ã¯å†’é ã®ã¾ã¨ã‚è¨˜äº‹ã‚’å¯„ç¨¿ã—ã¾ã—ãŸã€‚è©³ç´°ã¯ãœã²ãŠæ‰‹ã«å–ã£ã¦èªã‚“ã§ãã ã•ã‚Œã°ã¨æ€ã„ã¾ã™ã€‚ã“ã“ã§ã‚‚è‡ªåˆ†ãŒå„æŠ€è¡“ã‚’ç¾æ™‚ç‚¹ã§ã©ã®ã‚ˆã†ã«ã¨ã‚‰ãˆã¦ã„ã‚‹ã‹ã€æœ¬ã§ã¯ã„ã•ã•ã‹æ›¸ãã¥ã‚‰ã„å†…å®¹ã«ã¤ã„ã¦ã€æœ€è¿‘æµè¡Œã‚Šã®è¨€è‘‰ã§ã‚‚ã‚ã‚‹ã€ŒæŠ€è¡“çš„è² å‚µã€ã¨ã„ã†è¦³ç‚¹ã‚‚è¸ã¾ãˆã¦æ›¸ã„ã¦ãŠã“ã†ã¨æ€ã„ã¾ã™ã€‚ ãƒ»MySQL (RDBMS) ç§ã¯MySQLã®ä¸ã®äººã§ã‚‚ã‚ã‚Šã¾ã—ãŸã—ã€ã“ã‚Œã¾ã§è‡³ã‚‹ã¨ã“ã‚ã§è©±ã—ã¦ããŸã®ã§çœç•¥ã—ã¾ã™
y-imaya 2014/03/24
mongodb

mysql

database
ãƒªãƒ³ã‚¯
MySQL 5.6.11ä»¥é™ã®InnoDBãƒ†ãƒ¼ãƒ–ãƒ«ã§AUTO_INCREMENTã®å€¤ã‚’å°ã•ãã§ããªã„ä»¶
ãªã‚“ã‹å¤‰ã ãªãƒ¼ã¨æ€ã£ã¦ã„ãŸã‚“ã§ã™ãŒã™ã£ãã‚Šã—ã¾ã—ãŸã€‚ mysql56> CREATE TABLE t1 (num serial, val varchar(32)) Engine= InnoDB; Query OK, 0 rows affected (0.05 sec) mysql56> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(32) DEFAULT NULL, UNIQUE KEY `num` (`num`) ) ENGINE=InnoDB DE
y-imaya 2014/02/03
mysql

database
ãƒªãƒ³ã‚¯
ç‘æ³¢å¤§å¦ã§ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®è©±ã‚’ã—ã¦ãã¾ã—ãŸ - kuenishi's blog
ç‘æ³¢å¤§å¦ã®å·å³¶å…ˆç”Ÿã«å‘¼ã°ã‚Œã¦æœ¨ã€é‡‘ã¨æƒ…å ±ã‚·ã‚¹ãƒ†ãƒ ç‰¹åˆ¥è¬›ç¾©Dã¨ã„ã†ã‚„ã¤ã«å‚åŠ ã—ã¦ããŸã€‚ã“ã‚“ãªã“ã¨ã«ãªã‚‹ã¨ã¯æ€ã£ã¦ã„ãªã‹ã£ãŸãŒã€ã‚ã‚ã†ã“ã¨ã‹è¬›å¸«å´ã§å‘¼ã°ã‚Œã¦ã—ã¾ã„ã€æ€ãˆã°é ãã¸æ¥ãŸã‚‚ã®ã ã¨æ„Ÿæ…¨æ·±ã„ã€‚ãƒ•ãƒªã¯ã€ŒRiakã¨NoSQLã®è©±ã‚’ã—ã¦ã‚‚ã‚‰ãˆãŸã‚‰ã€ã¨ã„ã†éžå¸¸ã«è‡ªç”±åº¦ã®é«˜ã„å†…å®¹ãªã®ã§ã€ã›ã£ã‹ããªã®ã§åƒ•ã®çŸ¥ã£ã¦ã„ã‚‹ã“ã¨ã‚’å…¨éƒ¨è©°ã‚è¾¼ã‚“ã§è©±ã—ã¦ã‚„ã‚ã†ã¨æ€ã£ãŸã‚‰10åˆ†å»¶é•·ã—ã¦ã•ã‚‰ã«ã‚¹ãƒ©ã‚¤ãƒ‰10æžšåˆ†ãã‚‰ã„ã‚’æ¶ˆåŒ–ä¸è‰¯ã§çµ‚äº†ã¨ã„ã†ã€ã¿ã£ã¨ã‚‚ãªã„æ„Ÿã˜ã«ãªã£ã¦ã—ã¾ã£ãŸã€‚ã‹ãªã‚Šç«¯æŠ˜ã£ã¦ãƒã‚¤ãƒ³ãƒˆã ã‘èª¬æ˜Žã—ãŸã®ã§æµã‚ŒãŒåˆ†ã‹ã‚‰ãšè‹¦åŠ´ã—ãŸæ–¹ã‚‚å¤šã„ã¨æ€ã†ãŒã€ã¾ã‚åƒ•ã®æ€§æ ¼ãªã®ã§è¨±ã—ã¦ã»ã—ã„ã€‚ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®è¬›ç¾©ã‚’ã²ã¨é€šã‚Šçµ‚ãˆãŸé™¢ç”Ÿãƒ¬ãƒ™ãƒ«ã‚’æƒ³å®šã—ã¦ã‚¹ãƒ©ã‚¤ãƒ‰ã‚’ä½œã£ãŸã®ã§ã€ã‚‚ã—ã‹ã™ã‚‹ã¨ã€ã‚ã‚Šã¨é›£ã—ã‹ã£ãŸã‚Šåˆ†ã‹ã‚Šã«ãã‹ã£ãŸã‚Šã™ã‚‹ã‹ã‚‚ã—ã‚Œãªã„ã®ã§ã€ã‚ã‹ã‚‰ãªã„ã“ã¨ãŒã‚ã£ãŸã‚‰é©å½“ã«è³ªå•ã—ã¦ãã ã•ã„ã€‚ è¨€ã„ãŸã‹ã£ãŸã“ã¨ã®æµã‚Œã‚’åƒ•ãªã‚Šã«ã¾ã¨ã‚ã‚‹ã¨
y-imaya 2014/02/03
database
ãƒªãƒ³ã‚¯
1