{"id":1284,"date":"2021-12-30T17:27:48","date_gmt":"2021-12-30T08:27:48","guid":{"rendered":"https:\/\/blog.box.kr\/?p=1284"},"modified":"2021-12-30T17:59:56","modified_gmt":"2021-12-30T08:59:56","slug":"mysql-%ea%b4%80%eb%a6%ac","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=1284","title":{"rendered":"MySql \uad00\ub9ac"},"content":{"rendered":"\n<p>Table\uc5d0 \uc0dd\uc131\ub418\uc5b4 \uc788\ub294 Indexes \uc758 \uc6a9\ub7c9 \ud655\uc778 <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>select \n    database_name, \n    table_name, \n    index_name, \n    round((stat_value*@@innodb_page_size)\/1024\/1024, 2) SizeMB, \n    round(((100\/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`\nfrom mysql.innodb_index_stats iis \nwhere stat_name='size' \nand table_name = 'TargetTable'\nand database_name = 'targetDB'<\/strong><\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>\n<strong>show table status from database_name; <\/strong>-- databases\uc5d0 \uc788\ub294 \ud14c\uc774\ube14 \uc0c1\uc138\uc815\ubcf4 \ud655\uc778\n-- https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/show-table-status.html \ub97c \ucc38\uc870\n-- &#91;MySQL] SHOW TABLE STATUS\nby \ud600\ub098Lee 2020. 12. 14.\nSHOW TABLE STATUS\n    &#91;{FROM | IN} db_name]\n    &#91;LIKE 'pattern' | WHERE expr]\nSHOW TABLE STATUS \ub294 SHOW TABLES\uc758 \ub3d9\uc791\uacfc \ube44\uc2b7\ud558\uc9c0\ub9cc, non-TEMPORARY \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ub9ce\uc740 \uc815\ubcf4\ub97c \ubcf4\uc5ec\uc90d\ub2c8\ub2e4. mysqlshow --status db_name\uc744 \uba85\ub839\uc5b4\ub97c DB\uc5d0 \ud574\ub2f9\ud558\ub294 \ud14c\uc774\ube14 \uc815\ubcf4 \ubaa9\ub85d\uc744 \uac00\uc838\uc62c \uc218 \uc788\uace0, LIKE \ub098 WHERE \uc870\uac74\uc744 \ud1b5\ud574\uc11c \ud14c\uc774\ube14 Name \uc5d0 \uc6d0\ud558\ub294 \uc870\uac74\uc744 \uac78\uc5b4\uc11c \uc870\ud68c\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n \n\ncolumns\nName : \ud14c\uc774\ube14 \uc774\ub984\nEngine\n: \ud14c\uc774\ube14\uc758 \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4. See Chapter 14, The InnoDB Storage Engine, and Chapter 15, Alternative Storage Engines. \ud30c\ud2f0\uc158\uc744 \ub098\ub208 \ud14c\uc774\ube14\uc758 \uacbd\uc6b0 Engine\uc740 \ubaa8\ub4e0 \ud30c\ud2f0\uc158\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc758 \uc774\ub984\uc744 \ud45c\uc2dc\ud569\ub2c8\ub2e4.\nVersion : \ud14c\uc774\ube14 .frm \ud30c\uc77c\uc758 \ubc84\uc804\nRow_format\n: \ud589 \uc800\uc7a5 \ud615\uc2dd (Fixed, Dynamic, Compresssed, Redundant, Compact).\nFor MyISAM tables, Dynamic correspondes to what myisamchk -dvv reports as Packed.\nInnoDB \ud14c\uc774\ube14 \ud615\uc2dd\uc740 Antelope \ud30c\uc77c \ud615\uc2dd\uc744 \uc0ac\uc6a9\ud558\ub294 \uacbd\uc6b0 Redundant \ub610\ub294 Compact \uc774\uace0 Barracuda \ud30c\uc77c \ud615\uc2dd\uc744 \uc0ac\uc6a9\ud558\ub294 \uacbd\uc6b0 Compressed \ub610\ub294 Dynamic\uc785\ub2c8\ub2e4. \nRows\n: The number of rows. MyISAM \uacfc \uac19\uc740 \uc77c\ubd80 \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc740 \uc815\ud655\ud55c \uac1c\uc218\ub97c \uc800\uc7a5\ud569\ub2c8\ub2e4.\nInnoDB\uc640 \uac19\uc740 \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc758 \uacbd\uc6b0 rows \uac12\uc774 \uadfc\uc0ac\uce58\uc774\uba70 \uc2e4\uc81c \uac12\uc5d0\uc11c 40% ~ 50% \ub2e4\ub97c \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc774\ub7ec\ud55c \uacbd\uc6b0 SELECT COUNT(*) \ub85c \uc815\ud655\ud55c \uc218\ub97c \uad6c\ud574\uc57c\ud569\ub2c8\ub2e4.\nINFORMATION_SCHEMA\uc758 \uacbd\uc6b0 Rows \uac12\uc740 NULL\uc774\uba70, InnoDB \ud14c\uc774\ube14\uc758 \uacbd\uc6b0 SQL \ucd5c\uc801\ud654\uc5d0 \uc0ac\uc6a9\ub418\ub294 \ub300\ub7b5\uc801\uc778 \ucd94\uc815\uce58\uc785\ub2c8\ub2e4.\nAvg_row_length : \ud3c9\uade0 \ud589 \uae38\uc774.\nData_length\n: MyISAM\uc758 \uacbd\uc6b0 data file \uc758 \uae38\uc774 (Byte).\nInnoDB\uc758 \uacbd\uc6b0 \ud074\ub7ec\uc2a4\ud130\ud615 \uc778\ub371\uc2a4\uc5d0 \ud560\ub2f9 \ub41c \ub300\ub7b5\uc801\uc778 \uacf5\uac04 (Byte). ( = clusted index size (\ud398\uc774\uc9c0 \ub2e8\uc704) * InnoDB page size )\nMax_data_length\n: MyISAM\uc758 \uacbd\uc6b0 data file \uc758 \ucd5c\ub300 \uae38\uc774. \uc0ac\uc6a9\ub41c data pointer size\ub97c \uace0\ub824\ud558\uc5ec \ud14c\uc774\ube14\uc5d0 \uc800\uc7a5\ud560 \uc218 \uc788\ub294 \ucd1d data byte \uc218\nInnoDB\ub294 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.\nIndex_length\n: MyISAM\uc758 \uacbd\uc6b0 index file \uc758 \uae38\uc774 (byte)\nInnoDB\uc758 \uacbd\uc6b0 non-clusted index\uc5d0 \ud560\ub2f9\ub41c \ub300\ub7b5\uc801\uc778 \uacf5\uac04 (byte). ( = sum of non-clusted index size * InnoDB page size )\nData_free : \ud560\ub2f9\ub418\uc5c8\uc9c0\ub9cc \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc740 byte.\nAuto_increment : \ub2e4\uc74c AUTO_INCREMENT \uac12\nCreate_time : \ud14c\uc774\ube14\uc774 \ub9cc\ub4e4\uc5b4\uc9c4 \uc2dc\uae30\nUpdate_time : data file \uc774 \ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc5c5\ub370\uc774\ud2b8\ub41c \uc2dc\uae30.\nCheck_time : When the table was last checked. Not all storage engines update this time, in which case, the value is always NULL. For partitioned InnoDB tables, Check_time is always NULL.\nCollation : The table default collation. The output does not explicitlyu list the table default chracter set, but the collation name begins with the character set name.\nChecksum : The live checksum value, if any\nCreate_options : Extra options used with CRATE TABLE.\nComment : \ud14c\uc774\ube14 \uc0dd\uc131\uc2dc\uc5d0 \uc791\uc131\ud55c comment\n<\/code><\/pre>\n\n\n\n<p><strong>Table \ucd5c\uace0 \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>&nbsp;SHOW TABLE STATUS FROM&nbsp;DB name&nbsp;LIKE&nbsp;&#8216;Table name&#8217;;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>\uc774 \uba85\ub839\uc5b4 \uc774\ud6c4 Create_options\uc5d0 \uc788\ub294 max_rows\uc640 avg_row_length\ub97c \uacf1\ud55c \uac12\uc774 \uc6a9\ub7c9\uc774 \ub41c\ub2e4. MB \ub2e8\uc704\ub85c \ud658\uc0b0\ud558\ub824\uba74 1024\ub85c \ub450\ubc88 \ub098\ub204\uc5b4 \uc8fc\uba74 \ub41c\ub2e4.&nbsp;<\/p>\n\n\n\n<p><strong>DB \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><br>&nbsp;SELECT&nbsp;count(*) NUM_OF_TABLE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; table_schema,concat(round(sum(table_rows)\/1000000,2),&#8217;M&#8217;) rows,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(data_length)\/(1024*1024*1024),2),&#8217;G&#8217;) DATA,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(index_length)\/(1024*1024*1024),2),&#8217;G&#8217;) idx,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(data_length+index_length)\/(1024*1024*1024),2),&#8217;G&#8217;) total_size,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; round(sum(index_length)\/sum(data_length),2) idxfrac&nbsp;FROM information_schema.TABLES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY table_schema&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY sum(data_length+index_length) DESC LIMIT 10;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>\uac01 \ud14c\uc774\ube14 \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><br>&nbsp;SELECT&nbsp;table_name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;table_rows,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;round(data_length\/(1024*1024),2)&nbsp;as&nbsp;&#8216;DATA_SIZE(MB)&#8217;,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;round(index_length\/(1024*1024),2)&nbsp;as&nbsp;&#8216;INDEX_SIZE(MB)&#8217;&nbsp;FROM&nbsp;information_schema.TABLES&nbsp;&nbsp;WHERE&nbsp;table_schema =&nbsp;&#8216;DB_name&#8217;&nbsp;GROUP&nbsp;BY&nbsp;table_name&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;data_length&nbsp;DESC&nbsp;LIMIT&nbsp;10;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<ul><li>\ud14c\uc774\ube14\uc5d0 \ube48 \uacf5\uac04\uc774 \ub9ce\ub2e4\uba74..  ( data_free )<br><strong>optimize<\/strong> table \uc744 \uc2dc\ud589 \ud574\uc8fc\uba74 \uc88b\uc740\ub370 \uc774\ub54c Indexes\ub3c4 rebuild\ud558\uae30 \ub54c\ubb38\uc5d0  indexes\ub97c \uba3c\uc800 drop\ud574\uc900\ub2e4\uc74c\uc5d0 <strong>optimize<\/strong> table\uc744 \ud558\uace0 create indexes\ub97c \ud574\uc8fc\ub294\uac8c \ucd1d \uc2dc\uac04\uba74\uc5d0\uc11c \uc774\ub4dd\uc774 \uc788\uc74c.<br><\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>ALTER TABLE \ud14c\uc774\ube14\uba85 ENGINE=InnoDB, ALGORITHM=INPLACE , LOCK=NONE;<\/strong>\n-- 5.6 \uc774\uc0c1 \ubc84\uc804 \ubd80\ud130\ub294 \uc774\ub807\uac8c \ud558\uba74... Table Lock \uc5c6\uc774 Optimize \ub41c\ub2e4<\/code><\/pre>\n\n\n\n<ul><li>show indexes from \ud14c\uc774\ube14\uba85<br>\uc5d0\uc11c \ub098\uc628 \uacb0\uacfc\uc5d0\uc11c \uc778\ub371\uc2a4 \uceec\ub7fc\ubcc4 cardinality\ub97c \uc8fc\uc758 \uae4a\uac8c \ubcf4\uc790.. \uc774 \uac12\uc774 <br>\ud074\uc218\ub85d \ub2e4\ub978 \uac12\uc774 \ub9ce\uc740\uac70\uace0 <br>\uc791\uc744 \uc218\ub85d \ub2e4\ub978 \uac12\uc774 \uc801\uc740\uac70\ub2e4.. \uadf8\ub798\uc11c \uc774 \uac12\uc774 \ud070 \uac78 \uc704\uc8fc\ub85c ( \ubcf5\ud569\ud0a4\uc5d0\uc11c\ub294 \ud070\uac70 \ubd80\ud130 \uc791\uc740\uac70 \uc21c\uc73c\ub85c ) \uc778\ub371\uc2a4\ub97c \ub9cc\ub4e4\uc5b4\uc57c\uc9c0 \uc18d\ub3c4\uc5d0 \uc774\ub4dd\uc774 \uc788\ub2e4. <br> <\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>1. explain\uc758 \uc815\uc758 \n\n\n\nexplain Plan\ub780  SQL\uc744 \uc218\ud589\ud558\uae30\uc804 \ub370\uc774\ud130\ub97c \uc5b4\ub5bb\uac8c \uac00\uc838\uc62c \uac74\uc9c0\uc5d0 \ub300\ud55c \uc2e4\ud589\uacc4\ud68d\uc744 \uc758\ubbf8\ud558\uba70 \n\n\ub370\uc774\ud130 performance\ub97c \ud655\uc778 \ud558\uace0\uc790 \ud560\ub54c explain Plan \uba85\ub839\uc5b4\ub97c \uc0ac\uc6a9\ud55c\ub2e4.   \n\n\n\n2. \uc0ac\uc6a9\ubc29\ubc95\n\n\n\n2.1 )  SELECT \uc5d0\uc11c explain \uc0ac\uc6a9\ud558\uae30 \n\n\n\nselect explain\uc744 \uc0ac\uc6a9\ud558\ub824\uba74 SELECT \ud0a4\uc6cc\ub4dc \uc55e\uc5d0 explain\uc744 \ubd99\uc5ec\uc8fc\uba74\ub41c\ub2e4. \uac04\ub2e8\ud558\uac8c user\ub77c\ub294 \ud14c\uc774\ube14\uacfc author\uc774\ub77c\ub294 \ud14c\uc774\ube14\uc774 \uc788\ub2e4\ub77c\uace0 \ud560\ub54c \ub450 \ud14c\uc774\ube14\uc744 \uc870\uc778\ud55c \ud14c\uc774\ube14\uc5d0 \ub300\ud558\uc5ec explain\uc744 \uc8fc\uc5c8\ub2e4.\n\n\n\n\uc544\ub798\ub294 \ub450 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c explain \uacb0\uacfc\uc774\ub2e4. \n\n\n\n\n\n&#91;\uadf8\ub9bc 1]\n\n \n\nEXPLAIN SELECT user.user_name, author.authority_seq \n\n                \u3160 FROM tb_user_m user JOIN tb_authority_user author ON user.user_seq = author.user_seq ; \n\n\nexplain\uc740 \ucffc\ub9ac\uc5d0 \uc788\ub294 \ud14c\uc774\ube14 \ud558\ub098\ub2f9 \ud55c \ud589\uc529 \ucd9c\ub825\uc774 \ub41c\ub2e4. \n\n\uc704\uc5d0 &#91;\uadf8\ub9bc1] \ub610\ud55c \ub450\uac1c\uc758 \ud14c\uc774\ube14\uc744 \uc870\uc778\ud55c \uacb0\uacfc\uc774\uae30 \ub54c\ubb38\uc5d0 \ub450\uac1c\uc758 \ud589\uc774 \ucd9c\ub825\ub418\uc5c8\ub2e4. \n\n\n\n* \uc5ec\uae30\uc11c \ub9d0\ud558\ub294 \ud14c\uc774\ube14\uc740 \uc11c\ube0c\ucffc\ub9ac \uc77c\uc218\ub3c4 \uc788\uace0, union \uacb0\uacfc\uc77c \uc218\ub3c4 \uc788\ub2e4. \n\n\n\n2.2) SELECT\uac00 \uc544\ub2cc \ucffc\ub9ac\uad6c\ubb38\uc5d0\uc11c explain \uc0ac\uc6a9\ud558\uae30 \n\n\n\nselect \ucffc\ub9ac\uc5d0\uc11c\ub294  \uc55e\uc5d0 explain\ub9cc \ubd99\uc5ec\uc90c\uc73c\ub85c\uc368 \uc2e4\ud589\uacc4\ud68d\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc9c0\ub9cc INSERT,UPDATE,DELETE\uc640 \uac19\uc774 \uc785\ub825,\uc218\uc815,\uc0ad\uc81c\uc640 \uac19\uc740 \uac83\ub4e4\uc740 \uc2e4\ud589\uacc4\ud68d\uc744 \ud655\uc778 \ud560 \uc218 \uc5c6\ub2e4.\n\n\uadf8\ub807\uae30\ub54c\ubb38\uc5d0 INSERT,UPDATE, DELETE\ub85c \uc791\uc131\ub41c \ucffc\ub9ac\uc5d0\uc11c \uc0ac\uc6a9\ub41c \uce7c\ub7fc\uc73c\ub85c SELECT\ubb38\uc73c\ub85c \uc7ac\uad6c\uc131\uc2dc\ucf1c\uc918\uc57c\ud55c\ub2e4. \n\n\n\n\uc774\ud574\ud558\uae30 \uc880 \uc5b4\ub824\uc6b8 \ub4ef\ud574\uc11c \uc544\ub798\uc5d0 \uc608\uc2dc\ub97c \ub4e4\uc5b4\ubcf4\uaca0\ub2e4. \n\n\n\nUPDATE tb_user_m SET user_name='najung' ; \n\n\ub77c\ub294 update \ucffc\ub9ac\uac00 \uc788\ub2e4. \n\n\n\n\uc704\uc5d0 \ub098\uc624\ub294 update\ucffc\ub9ac\uad6c\ubb38\uc744 \n\nEXPLAIN SELECT user_name FROM tb_user_m ; \n\n\uc73c\ub85c \ubc14\uafd4\uc11c \uc2e4\ud589\uacc4\ud68d\uc744 \ud655\uc778\ud558\ub294 \uac83\uc774\ub2e4. \n\n\n\n3. explain \uceec\ub7fc  \n\n\n\n\uc704 &#91;\uadf8\ub9bc1]\uc744 \ud558\ub098\uc529 \ucabc\uac1c\uc11c \uc124\uba85\ud574\ubcf4\uaca0\ub2e4. \n\n\n\n3.1) id \uce7c\ub7fc \n\n\n\n  \n\n         &#91;\uadf8\ub9bc2]\n\n\n\nid\uce7c\ub7fc\uc740 \uad6c\ubb38\uc5d0 \ub530\ub77c \uc21c\ucc28\uc801\uc73c\ub85c \ubc88\ud638\uac00 \ubd80\uc5ec\ub41c\ub2e4. \ud604\uc7ac JOIN\uac19\uc740 \uacbd\uc6b0 \ud558\ub098\uc758 \ucffc\ub9ac\uc5d0\uc11c \ub450 \ud14c\uc774\ube14\uc774 \ub3d9\uc2dc\uc5d0 \uc2e4\ud589\ub42c\uae30\ub54c\ubb38\uc5d0 \ubaa8\ub4e0 \ud589\uc5d0 1\uc774\ub77c\ub294 \uac12\uc774 \ubd80\uc5ec\ub42c\uc9c0\ub9cc union\uc774\ub098 \uc11c\ube0c\ucffc\ub9ac\uac00 \uad6c\ubb38\uc5d0 \ub4e4\uc5b4\uc788\ub2e4\uba74 \uc704 \uc608\uc81c\ub294 \ub2ec\ub77c\uc9c4\ub2e4. \n\n\n\n\n\uc544\ub798\ub294 union\uc744 \uc774\uc6a9\ud55c \ucffc\ub9ac\uc774\ub2e4. \n\n\n\nEXPLAIN \n\nSELECT user_name,user_id FROM tb_user_m UNION SELECT result_code, register_time \n\nFROM tb_login_h\n\n\uc774\ub7ec\ud55c \ucffc\ub9ac\ub97c \ud1b5\ud574 explain\ud588\uc744 \ub54c \uacb0\uacfc \uac12\uc740 \n\n\n\n\n\n\n\n&#91;\uadf8\ub9bc3]\n\n\n\n \n\n        &#91;\uadf8\ub9bc4]  \n\n\ucc98\uc74c\uc5d0 \ub098\uc628 1\uc774  tb_user_m\uc5d0 \ub300\ud55c \ucffc\ub9ac \uc5f4\uc774\uace0 \ub450\ubc88\uc9f8 2\ub294 tb_login_h\uc5d0 \ub300\ud55c \ucffc\ub9ac\uc5f4\uc774\ub2e4.  \n\n\n\n\uc5ec\uae30\uc11c \ub4a4\uc5d0 select type \uce7c\ub7fc\uc744 \uc124\uba85\ud558\uba74\uc11c \ud55c\ubc88 \ub354 \uc774\uc57c\uae30 \ud558\uaca0\uc9c0\ub9cc, \n\nmysql\uc740 select \ucffc\ub9ac\ub97c \uac04\ub2e8\ud55c \ud0c0\uc785\uacfc \ubcf5\uc7a1\ud55c \ud0c0\uc785\uc73c\ub85c \uc124\uc815\ud55c\ub2e4. \n\n\ubcf5\uc7a1\ud55c \ud0c0\uc785\uc740 \uc720\ub3c4\ub41c \ud14c\uc774\ube14(sub\ucffc\ub9ac), union\uc73c\ub85c\uc11c \ubcf5\uc7a1\ud55c \ucffc\ub9ac\ub97c \uc2e4\ud589\ud560 \uacbd\uc6b0 id\uac00 \ubcf5\uc7a1\ud574\uc9c4\ub2e4. \n\n\n\n\ub610\ud55c &#91;\uadf8\ub9bc4] union \uacbd\uc6b0 3.null\ubd80\ubd84\uc740 mysql\uc5d0\uc11c union\uacb0\uacfc\uac00 \uc784\uc2dc\ud14c\uc774\ube14\uc5d0 \uc800\uc7a5\ub418\uc5c8\ub2e4\uac00 \ub2e4\uc2dc \uc77d\ud600\uc9c0\ub294\ub370 \uc784\uc2dc\ud14c\uc774\ube14\uc740 sql\uc5d0\uc11c \ub098\uc640\uc788\uc9c0 \uc54a\uc544 \uceec\ub7fc\uc774 null\uc744 \uac00\uc9c4\ub2e4. \n\n\n\n3.2) select_type \uce7c\ub7fc \n\n\n\nselect_type\uc740 \uac04\ub2e8\ud55c \ucffc\ub9ac\uc778\uc9c0 \ubcf5\uc7a1\ud55c \ucffc\ub9ac\uc778\uc9c0 \ub97c \ub098\ud0c0\ub0b8\ub2e4. \n\n&#91;\uadf8\ub9bc 1] \uac19\uc774 union\uc774\ub098 \uc11c\ube0c\ucffc\ub9ac\uac00 \uc5c6\uc744 \uacbd\uc6b0 SIMPLE \n\n&#91;\uadf8\ub9bc 3] \uac19\uc774 union\uc774\ub098 \uc11c\ube0c\ucffc\ub9ac\uac00 \uc788\uc744 \uacbd\uc6b0\uc5d0\ub294 \n\n\n\n\uac00\uc7a5 \ubc16\uc5d0 \uc788\ub294 \ubd80\ubd84\uc740 PRIMARY\ub85c \ud45c\uc2dc\ub418\uace0 \n\n\ub098\uba38\uc9c0\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4. \n\n\n\n3.2.1) SUBQUERY : \ucffc\ub9ac\ubb38\uc7a5\uc758 FROM\uc808\uc774 \uc544\ub2cc SELECT\uc808\uc5d0 \ub098\ud0c0\ub098\ub294 \uc11c\ube0c\ucffc\ub9ac\uc778 \uacbd\uc6b0 SUBQUERY\ub77c \ud55c\ub2e4. \n\n3.2.2) DERIVED : FROM\uc808\uc5d0 \uc788\ub294 \uc11c\ube0c\ucffc\ub9ac\ub97c \ud45c\uc2dc\ud558\ub294\ub370 \uc0ac\uc6a9\ub41c\ub2e4. \n\n3.2.3) UNION : UNION\uc744 \uc0ac\uc6a9\ud55c \ucffc\ub9ac\uc808\uc77c \uacbd\uc6b0 \uc0ac\uc6a9\ub41c\ub2e4. \n\n3.2.4) UNION_RESULT : UNION\uc758 \uacb0\uacfc \uac12\uc774 \uc784\uc2dc\ud14c\uc774\ube14\uc5d0 \uc800\uc7a5\ub418\uba70, \uadf8 \uc784\uc2dc\ud14c\uc774\ube14\uc744 \ud45c\ud604\ud560 \ub54c \uc0ac\uc6a9\ub41c\ub2e4. \n\n\n\n3.3) table \uce7c\ub7fc \n\n\n\ntable \uce7c\ub7fc\uc740 \uc5b4\ub5a4 \ud14c\uc774\ube14\uc5d0 \uc811\uadfc\ud558\ub294\uc9c0 \ubcf4\uc5ec\uc900\ub2e4.  \ub300\ubd80\ubd84\uc758 \uacbd\uc6b0 \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 sql\uc5d0\uc11c \uc9c0\uc815\ud574\uc900 ( ex : AS user ) \uac12\uc744 \uac00\uc9c4\ub2e4. \n\n\n\ntable \uceec\ub7fc\uc740 \uac04\ub2e8\ud55c \uc608\uc81c\ub294 \uc774\ud574\ud558\uae30 \uc26c\uc6b0\ub098 , \ubcf5\uc7a1\ud55c \ucffc\ub9ac\uac19\uc740 \uacbd\uc6b0 \uc774\ud574\ud558\uae30 \uc5b4\ub835\ub2e4. \n\n\uc544\ub798\uc758 \uc608\uc2dc\ub97c \ubcf4\uc790 \n\n\n\nEXPLAIN \n\n\n\nSELECT * \n\n  FROM \n\n       (SELECT cast(@rnum := @rnum+1 as unsigned) AS seq, \n\n              union_result.* \n\n         FROM (SELECT @rnum := 0) r, \n\n              ( \n\n                     (SELECT article_h.regist_time as time , \n\n                            article_h.user_id as id, \n\n                            article_h.user_institution as institution, \n\n                            article_h.user_position as position, \n\n                            article_h.user_name as name, \n\n                            article_h.register_ip as ip, \n\n                            concat(article_h.module_name, ' | ', article_h.article_name, ' | ', article_h.action_name) as action_name \n\n                       FROM tb_article_h article_h \n\n                     ) \n\n                    UNION ALL \n\n                     (SELECT login_h.register_time as time, \n\n                            login_h.user_id as id, \n\n                            user.user_institution as institution, \n\n                            code2.code_name AS position, \n\n                            login_h.user_name as name, \n\n                            login_h.register_ip as ip, \n\n                            code.code_name as action \n\n                       FROM tb_common_code code \n\n                          INNER JOIN tb_login_h login_h \n\n                              ON code.code=login_h.result_code, \n\n                            tb_common_code code2 \n\n                          INNER JOIN tb_user_m user \n\n                              ON code2.code=user.user_position_code \n\n                      WHERE user.user_id = login_h.user_id \n\n                     ) \n\n                   ) \n\n                   union_result \n\n             WHERE 1=1  \n\n          ORDER BY time ASC \n\n            ) result \n\n      WHERE seq BETWEEN 0 AND 10 \n\n   ORDER BY seq DESC\n\n\n\n\n\n\n\n\n\n&#91;\uadf8\ub9bc 5]\n\n\n\n&#91;\uadf8\ub9bc 5]\uc5d0 \ub300\ud55c \ud14c\uc774\ube14\uc744 \uc124\uc815\ud558\uc790\uba74 \uc774\ub7ec\ud558\ub2e4. \n\n1. id \uac12\uc774 1\uc778 table\uba85\uc740 derived2 \uc774\ub2e4. \uc774\ub7ec\ud55c \ud14c\uc774\ube14 \uba85\uc740 id\uac12\uc774 2\uc774\uace0 select_type\uc774 DERIVED\uc778 \ud14c\uc774\ube14\uc744 \ucc38\uc870\ud558\ub294 \ud14c\uc774\ube14\uc774\ub77c\uace0 \uc774\ud574\ud558\uba74 \ub41c\ub2e4.  \ube68\uac04\uc0c9\uc73c\ub85c \ud45c\uc2dc\ud55c \ubd80\ubd84\uc774 derived2\uc774\ub2e4. \n\n2. id\uac12\uc774 2\uc778 table\uba85\uc740 derived3 \uacfc derived4 \uc774\ub2e4.   \n\n\n\n\uc774\ud558 \ub3d9\ubb38 ... \n\n\n\n* \ub9c8\uc9c0\ub9c9 10\ud589\uc774 \uc774\ud574\uac00 \uac00\uc9c0\uc54a\uc74c \n\n\n\n3.4) type \uce7c\ub7fc \n\n\n\nmysql \uba54\ub274\uc5bc\uc5d0\ub294 \uc870\uc778 \ubc29\uc2dd\uc774\ub77c\uace0 \ub098\ud0c0\ub0b4\uace0 \uc788\uc73c\ub098 \ud14c\uc774\ube14\uc5d0\uc11c \ud589\uc744 \uc5b4\ub5bb\uac8c \ucc3e\ub294\uc9c0 \ub77c\uace0 \uc774\ud574\ud558\ub294\uac8c \ub354 \uc27d\ub2e4. \n\ntype\uce7c\ub7fc \uc885\ub958\ub97c \ub098\uc05c \ubc29\uc2dd\uc5d0\uc11c \uc88b\uc740 \ubc29\uc2dd \uc21c\uc73c\ub85c \uc124\uba85\ud558\ub3c4\ub85d \ud558\uaca0\ub2e4. \n\n\n\n3.4.1) ALL  : mysql\uc774 \ud589\uc744 \ucc3e\uae30 \uc704\ud574 \ucc98\uc74c\ubd80\ud130 \ub05d\uae4c\uc9c0 \uc2a4\uce94\ud574\uc57c\ud55c\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \n\n3.4.2) INDEX : \uc778\ub371\uc2a4 \uc21c\uc11c\ub85c \uc2a4\uce94 \ud55c\ub2e4\ub294 \uac83\uc744 \uc81c\uc678\ud558\uba74ALL\uacfc\uac19\ub2e4. \uc77c\ubc18\uc801\uc778 \uacbd\uc6b0 \uc778\ub371\uc2a4\uac00 \ud14c\uc774\ube14\ubcf4\ub2e4 \uc0ac\uc774\uc988\uac00 \uc791\uae30 \ub54c\ubb38\uc5d0,ALL\ubcf4\ub2e4\ub294 \ube60\ub97c \uac00\ub2a5\uc131\uc774 \ub192\ub2e4. (\uc774\ud574\uac00 \uc798 \ub418\uc9c0 \uc54a\uc74c ) \n\n3.4.3) RANGE : \uc81c\ud55c\ub41c \ud615\ud0dc\uc758 \uc778\ub371\uc2a4 \uc2a4\uce94\uc774\ub2e4. RANGE\uc2a4\uce94\uacbd\uc6b0 \uc778\ub371\uc2a4 \ud2b9\uc815\ubd80\ubd84\uc5d0\uc11c \uc2dc\uc791\ud574\uc11c \ud2b9\uc815\ubc94\uc704\uc5d0 \uc788\ub294 \uac12\uc744 \uac00\uc9c0\ub294 \ud589\uc744 \ubc18\ud658\ud55c\ub2e4. \ud0a4\uceec\ub7fc\uc774 \uc0c1\uc218\uc640 =, &lt;>, >, >=, &lt;, &lt;=, IS NULL,&lt;=>, BETWEEN \ub610\ub294 IN \uc5f0\uc0b0\uc5d0 \uc0ac\uc6a9\ub420 \ub54c \uc801\uc6a9\ub41c\ub2e4. \n\nSELECT * FROM tb_user_m  WHERE user_seq = 10;\n \nSELECT * FROM tb_user_m  WHERE user_seq BETWEEN 10 and 20;\n \nSELECT * FROM tb_user_m  WHERE user_seq IN (10);\n \nSELECT * FROM tb_user_m  WHERE user_seq= 10 AND user_seq IN (20);\n\n\n\n3.4.4) ref : \uc5b4\ub5a4 \uac12 \ud558\ub098\uc5d0 \ub9e4\uce58\ub418\ub294 \ud589\ub4e4\uc744 \ubc18\ud658\ud574\uc8fc\ub294 \uc778\ub371\uc2a4 \uc811\uadfc\ubc29\uc2dd\uc774\ub2e4.  \uc778\ub371\uc2a4\uc5d0 \ub9e4\uce58\ub418\ub294 \uac12\uc774 \ub9ce\uc9c0 \uc54a\uc740 \uacbd\uc6b0 \ub098\uc058\uc9c0 \uc54a\ub2e4. PRIMARY KEY \ub610\ub294 UNIQUE \uc778\ub371\uc2a4\uac00 \uc544\ub2d0 \uacbd\uc6b0\uc5d0 ref\uac00 \uc0ac\uc6a9\ub418\uba70  = \ub610\ub294 &lt;=> \uc5f0\uc0b0\uc790\ub97c \uc0ac\uc6a9\ud574\uc11c \ube44\uad50\ub418\ub294 \uc778\ub371\uc2a4\ub41c \uceec\ub7fc\uc5d0 \ub300\ud574 \uc0ac\uc6a9\ub420 \uc218 \uc788\ub2e4. \n\n\n\nSELECT * FROM tb_user_m WHERE user_name='\uc190\ud654\uc815';\n\n \n\nSELECT * FROM tb_user_m user, tb_login_h login where user.user_id = login.user_id ; \n\n\n\n* \uae30\uc6b8\ub9bc\ub41c \ucffc\ub9ac \ub450\uac1c\uc758 \ucc28\uc774\uac00 \uc774\ud574\uac00 \uac00\uc9c0 \uc54a\uc74c \n\n\n\n3.4.5) eq_ref : \ud14c\uc774\ube14\uc5d0\uc11c \ucc3e\uc740 \uac12\uc911 \ub2e8 \ud558\ub098\uc758 \uac12\ub9cc \ud574\ub2f9 \ud14c\uc774\ube14\uc5d0 \uc874\uc7ac\ud558\ub294 \uacbd\uc6b0  \uc774\ub7f0 \uc778\ub371\uc2a4 \ud0d0\uc0c9\ubc95\uc774 \uc0ac\uc6a9\ub41c\ub2e4. \uae30\ubcf8\ud0a4 \ud639\uc740 unique \uc778\ub371\uc2a4\uc5d0 \ube44\uad50\ud560 \ub584 \uc774\ub7f0 \uc811\uadfc \ubc29\ubc95\uc744 \ub9ce\uc774 \uc0ac\uc6a9\ud55c\ub2e4.  eq_ref\ub294 = \uc5f0\uc0b0\uc790\ub97c \uc0ac\uc6a9\ud574\uc11c \ube44\uad50\ub418\ub294 \uc778\ub371\uc2a4\ub41c \uceec\ub7fc\uc6a9\uc73c\ub85c \uc0ac\uc6a9\ub420 \uc218 \uc788\ub2e4.\n\nSELECT * FROM tb_authority_user author JOIN tb_user_m user ON user.user_seq = author.user_seq ; \n\n\n\n3.4.6) const : \ucffc\ub9ac\uc758 \uc77c\ubd80\ub97c \uc0c1\uc218\ub85c \ub300\uccb4\uc2dc\ud0ac \uc218 \uc788\uc744 \ub54c \uc0ac\uc6a9\ud55c\ub2e4. const \ud14c\uc774\ube14\uc740 \ud55c\ubc88 \ubc16\uc5d0 \uc77d\ud600\uc9c0\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0 \ub9e4\uc6b0 \ube60\ub974\ub2e4.const\ub294 PRIMARY KEY \ub610\ub294 UNIQUE \uc778\ub371\uc2a4\uc758 \ubaa8\ub4e0 \ubd80\ubd84\uc744 \uc0c1\uc218 \uac12(constant value)\uacfc \ube44\uad50\ub97c \ud560 \ub54c \uc0ac\uc6a9\ub41c\ub2e4.\n\nSELECT * FROM tb_user_m WHERE user_seq= 7\n\n3.4.7) system : \ubb34\uc870\uac74 \ud558\ub098\uc758 \uc5f4\ub9cc\uc744 \uac00\uc9c0\uace0 \uc788\ub294 \ud14c\uc774\ube14 . \uc774\uac83\uc740 const\uc758 \ud2b9\ubcc4\ud55c \uacbd\uc6b0\uc774\ub2e4.\n\n\n\n\n\n3.5) possible_key \uce7c\ub7fc \n\n\n\npossible_keys \uceec\ub7fc\uc740 \uc774 \ud14c\uc774\ube14\uc5d0\uc11c \uc5f4\uc744 \ucc3e\uae30 \uc704\ud574 MySQL\uc774 \uc120\ud0dd\ud55c \uc778\ub371\uc2a4\ub97c \uac00\ub9ac\ud0a8\ub2e4. \uc774 possible_key\uceec\ub7fc\uc740 \ucd5c\uc801\ud654\ub2e8\uacc4\uc5d0\uc11c \uc2dc\uc791\ud558\uae30 \ub584\ubb38\uc5d0 \ucd5c\uc801\ud654\uac00 \ub05d\ub098\ub294 \ub2e8\uacc4\uac00 \uc9c4\ud589\ub428\uc5d0 \ub530\ub77c \uc4f8\ubaa8\uac00 \uc5c6\uc5b4\uc9c8 \uc218\ub3c4 \uc788\ub2e4. \n\n\ub9cc\uc77c \uc774 \uceec\ub7fc \uac12\uc774 NULL\uc774\ub77c\uba74, \uc5f0\uad00\ub41c \uc778\ub371\uc2a4\uac00 \uc874\uc7ac\ud558\uc9c0 \uc54a\uac8c \ub41c\ub2e4. \uc774\uc640 \uac19\uc740 \uacbd\uc6b0, \uc5ec\ub7ec\ubd84\uc740 WHERE \uad6c\ubb38\uc744 \uac80\uc0ac\ud574\uc11c, \uc774 \uad6c\ubb38\uc774 \uc778\ub371\uc2a4 \ud558\uae30\uc5d0 \uc801\ub2f9\ud55c \uceec\ub7fc\uc744 \ucc38\uc870\ud558\uace0 \uc788\ub294\uc9c0 \uc5ec\ubd80\ub97c \uc54c\uc544 \ubd04\uc73c\ub85c\uc368 \ucffc\ub9ac \uc18d\ub3c4\ub97c \uac1c\uc120 \uc2dc\ud0ac \uc218\uac00 \uc788\uac8c \ub41c\ub2e4. \n\n\n\n3.6) key\uce7c\ub7fc \n\nkey \uceec\ub7fc\uc740 MySQL\uc774 \uc2e4\uc81c\ub85c \uc0ac\uc6a9\ud560 \uc608\uc815\uc778 \ud0a4 (\uc778\ub371\uc2a4)\ub97c \uac00\ub9ac\ud0a8\ub2e4 . \n\n\n\n3.7) key_len \uceec\ub7fc \n\n\uc778\ub371\uc2a4 \ud544\ub4dc\uac00 \uac00\uc9c8 \uc218 \uc788\ub294 \ucd5c\ub300\uc758 \uae38\uc774\ub97c \ucd9c\ub825\ud55c\ub2e4. \n\n\n\n3.8) ref \uce7c\ub7fc \n\n\ud0a4 \uce7c\ub7fc\uc5d0 \ub098\uc640 \uc788\ub294 \uc778\ub371\uc2a4\uc5d0\uc11c \ucc3e\uae30 \uc704\ud55c \uc120\ud589 \ud14c\uc774\ube14\uc758 \uc5b4\ub5a4 \uce7c\ub7fc\uc774 \uc0ac\uc6a9\ub418\uc5c8\ub294\uc9c0 \ub098\ud0c0\ub0b8\ub2e4. \n\n\n\n3.9) row\uce7c\ub7fc \n\nrow\uce7c\ub7fc \uc6d0\ud558\ub294 \ud589\uc744 \ucc3e\uae30 \uc704\ud574 \uc5bc\ub9c8\ub098 \ub9ce\uc740 \ud589\uc744 \uc77d\uc5b4\uc57c \ud560\uc9c0 \uc608\uce21\uac12\uc744 \uc758\ubbf8\ud55c\ub2e4. \n\ntb_user_m\uc5d0 50\uba85\uc758 \ud68c\uc6d0\uc774 \uc788\uc744 \ub54c tb_user_m\uc5d0 \ub300\ud55c \ud14c\uc774\ube14\uc758 row\uce7c\ub7fc\uc740 50\uc774 \ub420 \uac83\uc774\ub2e4. \n\n\n\n3.10) extra\uce7c\ub7fc \n\n\n\n3.10.1)using index : mysql \ud14c\uc774\ube14\uc5d0 \uc811\uadfc\ud558\uc9c0 \uc54a\ub3c4\ub85d \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud55c\ub2e4\ub294 \uac83\uc744 \uc54c\ub824\uc900\ub2e4. \n\n*\ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub780 , \ucffc\ub9ac\ub97c \uc2e4\ud589\uc2dc\ud0a4\uae30 \uc704\ud574 \ud544\uc694\ud55c \ub370\uc774\ud130\uac00 \ubaa8\ub450 \ud3ec\ud568\ub41c \uc778\ub371\uc2a4\ub97c \ub9d0\ud55c\ub2e4.  \n\n3.10.2)using where : mysql\uc11c\ubc84\uac00 \uac12\uc744 \uac00\uc838\uc628 \ub4a4 \ud589\uc744 \ud544\ud130\ub9c1 \ud55c\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \n\n3.10.3)using temporary :  mysql\uc774 \ucffc\ub9ac\uacb0\uacfc\ub97c \uc815\ub82c\ud558\uae30\uc704\ud574 \uc784\uc2dc\ud14c\uc774\ube14\uc744 \uc0ac\uc6a9\ud55c\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \n\n3.10.4)using filesort : mysql\uc774 \uacb0\uacfc\uc758 \uc21c\uc11c\ub97c \ub9de\ucd94\uae30 \uc704\ud574 \uc778\ub371\uc2a4 \uc21c\uc11c\ub85c \ud14c\uc774\ube14\uc744 \uc77d\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \uc678\ubd80 \uc815\ub82c\uc744 \uc0ac\uc6a9\ud574\uc57c\ud55c\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \n\n3.10.5)range checked for each record (index map:N) : \uc801\ud569\ud55c \uc778\ub371\uc2a4\uac00 \uc5c6\uc73c\ubbc0\ub85c \uac01 \ub808\ucf54\ub4dc \uc870\uc778\uc5d0\uc11c \uac01 \uc5d4\ub371\uc2a4\ub4e4\uc744 \uc7ac\ud3c9\uac00\ud55c\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. <\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>MySQL \uc778\ub371\uc2a4\uc5d0 \uad00\ud574 \uc815\ub9ac\ub97c \ud558\uc600\uc2b5\ub2c8\ub2e4.\nMySQL\uc744 \uc798 \uc54c\uc544\uc11c \uc815\ub9ac\ub97c \ud55c\uac83\uc774 \uc544\ub2c8\ub77c, \uc798 \uc54c\uace0 \uc2f6\uc5b4\uc11c \uc815\ub9ac\ud55c \uac83\uc774\ub77c \uc624\ub958\uac00 \uc788\uc744\uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.\n\n1. \uc778\ub371\uc2a4\ub780?\n\uc778\ub371\uc2a4 == \uc815\ub82c\n\n\uc778\ub371\uc2a4\ub294 \uacb0\uad6d \uc9c0\uc815\ud55c \uceec\ub7fc\ub4e4\uc744 \uae30\uc900\uc73c\ub85c \uba54\ubaa8\ub9ac \uc601\uc5ed\uc5d0 \uc77c\uc885\uc758 \ubaa9\ucc28\ub97c \uc0dd\uc131\ud558\ub294 \uac83\uc785\ub2c8\ub2e4.\ninsert, update, delete (Command)\uc758 \uc131\ub2a5\uc744 \ud76c\uc0dd\ud558\uace0 \ub300\uc2e0 select (Query)\uc758 \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0b5\ub2c8\ub2e4.\n\uc5ec\uae30\uc11c \uc8fc\uc758\ud558\uc2e4 \uac83\uc740 update, delete \ud589\uc704\uac00 \ub290\ub9b0\uac83\uc774\uc9c0, update, delete\ub97c \ud558\uae30 \uc704\ud574 \ud574\ub2f9 \ub370\uc774\ud130\ub97c \uc870\ud68c\ud558\ub294\uac83\uc740 \uc778\ub371\uc2a4\uac00 \uc788\uc73c\uba74 \ube60\ub974\uac8c \uc870\ud68c\uac00 \ub429\ub2c8\ub2e4.\n\uc778\ub371\uc2a4\uac00 \uc5c6\ub294 \uceec\ub7fc\uc744 \uc870\uac74\uc73c\ub85c update, delete\ub97c \ud558\uac8c \ub418\uba74 \uad49\uc7a5\ud788 \ub290\ub824 \ub9ce\uc740 \uc591\uc758 \ub370\uc774\ud130\ub97c \uc0ad\uc81c \ud574\uc57c\ud558\ub294 \uc0c1\ud669\uc5d0\uc120 \uc778\ub371\uc2a4\ub85c \uc9c0\uc815\ub41c \uceec\ub7fc\uc744 \uae30\uc900\uc73c\ub85c \uc9c4\ud589\ud558\ub294\uac83\uc744 \ucd94\ucc9c\ub4dc\ub9bd\ub2c8\ub2e4.\n\n\uc778\ub371\uc2a4 \uad6c\uc870\n\n(B-Tree \uc778\ub371\uc2a4 \uad6c\uc870)\n\n\uc778\ub371\uc2a4 \ud0d0\uc0c9\uc740 Root -> Branch -> Leaf -> \ub514\uc2a4\ud06c \uc800\uc7a5\uc18c \uc21c\uc73c\ub85c \uc9c4\ud589\ub429\ub2c8\ub2e4.\n\uc608\ub97c \ub4e4\uc5b4 Branch (\ud398\uc774\uc9c0\ubc88\ud638 2) \ub294 dept_no\uac00 d001\uc774\uba74\uc11c emp_no\uac00 10017 ~ 10024\uae4c\uc9c0\uc778 Leaf\uc758 \ubd80\ubaa8\ub85c \uc788\uc2b5\ub2c8\ub2e4.\n\uc989, dept_no=d001 and emp_no=10018\ub85c \uc870\ud68c\ud558\uba74 \ud398\uc774\uc9c0 \ubc88\ud638 4\uc778 Leaf\ub97c \ucc3e\uc544 \ub370\uc774\ud130\ud30c\uc77c\uc758 \uc8fc\uc18c\ub97c \ubd88\ub7ec\uc640 \ubc18\ud658\ud558\ub294 \uacfc\uc815\uc744 \ud558\uac8c \ub429\ub2c8\ub2e4.\n\uc778\ub371\uc2a4\uc758 \ub450\ubc88\uc9f8 \uceec\ub7fc\uc740 \uccab \ubc88\uc9f8 \uceec\ub7fc\uc5d0 \uc758\uc874\ud574\uc11c \uc815\ub82c\ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.\n\uc989, \ub450\ubc88\uc9f8 \uceec\ub7fc\uc758 \uc815\ub82c\uc740 \uccab\ubc88\uc9f8 \uceec\ub7fc\uc774 \ub611\uac19\uc740 \uc5f4\uc5d0\uc11c\ub9cc \uc758\ubbf8\uac00 \uc788\uc2b5\ub2c8\ub2e4.\n\ub9cc\uc57d 3\ubc88\uc9f8, 4\ubc88\uc9f8 \uc778\ub371\uc2a4 \uceec\ub7fc\ub3c4 \uc788\ub2e4\uba74 \ub450\ubc88\uc9f8 \uceec\ub7fc\uacfc \ub9c8\ucc2c\uac00\uc9c0\ub85c 3\ubc88\uc9f8 \uceec\ub7fc\uc740 2\ubc88\uc9f8 \uceec\ub7fc\uc5d0 \uc758\uc874\ud558\uace0, 4\ubc88\uc9f8 \uceec\ub7fc\uc740 3\ubc88\uc9f8 \uceec\ub7fc\uc5d0 \uc758\uc874\ud558\ub294 \uad00\uacc4\uac00 \ub429\ub2c8\ub2e4.\n\ub514\uc2a4\ud06c\uc5d0\uc11c \uc77d\ub294 \uac83\uc740 \uba54\ubaa8\ub9ac\uc5d0\uc11c \uc77d\ub294\uac83\ubcf4\ub2e4 \uc131\ub2a5\uc774 \ud6e8\uc52c \ub5a8\uc5b4\uc9d1\ub2c8\ub2e4.\n\uacb0\uad6d \uc778\ub371\uc2a4 \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a8\ub2e4\ub294 \uac83\uc740 \ub514\uc2a4\ud06c \uc800\uc7a5\uc18c\uc5d0 \uc5bc\ub9c8\ub098 \ub35c \uc811\uadfc\ud558\uac8c \ub9cc\ub4dc\ub290\ub0d0, \uc778\ub371\uc2a4 Root\uc5d0\uc11c Leaf\uae4c\uc9c0 \uc624\uace0\uac00\ub294 \ud69f\uc218\ub97c \uc5bc\ub9c8\ub098 \uc904\uc774\ub290\ub0d0\uc5d0 \ub2ec\ub824\uc788\uc2b5\ub2c8\ub2e4.\n\uc778\ub371\uc2a4\uc758 \uac2f\uc218\ub294 3~4\uac1c \uc815\ub3c4\uac00 \uc801\ub2f9\ud569\ub2c8\ub2e4.\n\ub108\ubb34 \ub9ce\uc740 \uc778\ub371\uc2a4\ub294 \uc0c8\ub85c\uc6b4 Row\ub97c \ub4f1\ub85d\ud560\ub54c\ub9c8\ub2e4 \uc778\ub371\uc2a4\ub97c \ucd94\uac00\ud574\uc57c\ud558\uace0, \uc218\uc815\/\uc0ad\uc81c\uc2dc\ub9c8\ub2e4 \uc778\ub371\uc2a4 \uc218\uc815\uc774 \ud544\uc694\ud558\uc5ec \uc131\ub2a5\uc0c1 \uc774\uc288\uac00 \uc788\uc2b5\ub2c8\ub2e4.\n\uc778\ub371\uc2a4 \uc5ed\uc2dc \uacf5\uac04\uc744 \ucc28\uc9c0\ud569\ub2c8\ub2e4. \ub9ce\uc740 \uc778\ub371\uc2a4\ub4e4\uc740 \uadf8\ub9cc\ud07c \ub9ce\uc740 \uacf5\uac04\uc744 \ucc28\uc9c0\ud569\ub2c8\ub2e4.\n\ud2b9\ud788 \ub9ce\uc740 \uc778\ub371\uc2a4\ub4e4\ub85c \uc778\ud574 \uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc798\ubabb\ub41c \uc778\ub371\uc2a4\ub97c \uc120\ud0dd\ud560 \ud655\ub960\uc774 \ub192\uc2b5\ub2c8\ub2e4.\n2. \uc778\ub371\uc2a4 \ud0a4 \uac12\uc758 \ud06c\uae30\nInnoDB (MySQL)\uc740 \ub514\uc2a4\ud06c\uc5d0 \ub370\uc774\ud130\ub97c \uc800\uc7a5\ud558\ub294 \uac00\uc7a5 \uae30\ubcf8 \ub2e8\uc704\ub97c \ud398\uc774\uc9c0\ub77c\uace0 \ud558\uba70, \uc778\ub371\uc2a4 \uc5ed\uc2dc \ud398\uc774\uc9c0 \ub2e8\uc704\ub85c \uad00\ub9ac \ub429\ub2c8\ub2e4.\n(B-Tree \uc778\ub371\uc2a4 \uad6c\uc870\uc5d0\uc11c Root, Branch, Leaf \ucc38\uace0)\n\n\ud398\uc774\uc9c0\ub294 16KB \ub85c \ud06c\uae30\uac00 \uace0\uc815\ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4.\n\n\ub9cc\uc57d \ubcf8\uc778\uc774 \uc124\uc815\ud55c \uc778\ub371\uc2a4 \ud0a4\uc758 \ud06c\uae30\uac00 16 Byte \ub77c\uace0 \ud558\uace0, \uc790\uc2dd\ub178\ub4dc(Branch, Leaf)\uc758 \uc8fc\uc18c(\uc704 \uc778\ub371\uc2a4 \uad6c\uc870 \uadf8\ub9bc \ucc38\uace0)\uac00 \ub2f4\uae34 \ud06c\uae30\uac00 12 Byte \uc815\ub3c4\ub85c \uc7a1\uc73c\uba74, 16*1024 \/ (16+12) = 585\ub85c \uc778\ud574 \ud558\ub098\uc758 \ud398\uc774\uc9c0\uc5d0\ub294 585\uac1c\uac00 \uc800\uc7a5\ub420 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\uc5ec\uae30\uc11c \uc778\ub371\uc2a4 \ud0a4\uac00 32 Byte\ub85c \ucee4\uc9c0\uba74 \uc5b4\ub5bb\uac8c \ub420\uae4c\uc694?\n16*1024 \/ (32+12) = 372\ub85c \ub418\uc5b4 372\uac1c\ub9cc \ud55c \ud398\uc774\uc9c0\uc5d0 \uc800\uc7a5\ud560 \uc218 \uc788\uac8c \ub429\ub2c8\ub2e4.\n\n\uc870\ud68c \uacb0\uacfc\ub85c 500\uac1c\uc758 row\ub97c \uc77d\uc744\ub54c 16byte\uc77c\ub54c\ub294 1\uac1c\uc758 \ud398\uc774\uc9c0\uc5d0\uc11c \ub2e4 \uc870\ud68c\uac00 \ub418\uc9c0\ub9cc, 32byte\uc77c\ub54c\ub294 2\uac1c\uc758 \ud398\uc774\uc9c0\ub97c \uc77d\uc5b4\uc57c \ud558\ubbc0\ub85c \uc774\ub294 \uc131\ub2a5 \uc800\ud558\uac00 \ubc1c\ud589\ud558\uac8c \ub429\ub2c8\ub2e4.\n\n\uc778\ub371\uc2a4\uc758 \ud0a4\ub294 \uae38\uba74 \uae38\uc218\ub85d \uc131\ub2a5\uc0c1 \uc774\uc288\uac00 \uc788\uc2b5\ub2c8\ub2e4.\n\n3. \uc778\ub371\uc2a4 \uceec\ub7fc \uae30\uc900\n\uba3c\uc800 \ub9d0\uc500\ub4dc\ub9b4 \uac83\uc740 1\uac1c\uc758 \uceec\ub7fc\ub9cc \uc778\ub371\uc2a4\ub97c \uac78\uc5b4\uc57c \ud55c\ub2e4\uba74, \ud574\ub2f9 \uceec\ub7fc\uc740 \uce74\ub514\ub110\ub9ac\ud2f0(Cardinality)\uac00 \uac00\uc7a5 \ub192\uc740 \uac83\uc744 \uc7a1\uc544\uc57c \ud55c\ub2e4\ub294 \uc810\uc785\ub2c8\ub2e4.\n\n\uce74\ub514\ub110\ub9ac\ud2f0(Cardinality)\ub780 \ud574\ub2f9 \uceec\ub7fc\uc758 \uc911\ubcf5\ub41c \uc218\uce58\ub97c \ub098\ud0c0\ub0c5\ub2c8\ub2e4.\n\uc608\ub97c \ub4e4\uc5b4 \uc131\ubcc4, \ud559\ub144 \ub4f1\uc740 \uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub0ae\ub2e4\uace0 \uc598\uae30\ud569\ub2c8\ub2e4.\n\ubc18\ub300\ub85c \uc8fc\ubbfc\ub4f1\ub85d\ubc88\ud638, \uacc4\uc88c\ubc88\ud638 \ub4f1\uc740 \uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub192\ub2e4\uace0 \uc598\uae30\ud569\ub2c8\ub2e4.\n\n\uc778\ub371\uc2a4\ub85c \ucd5c\ub300\ud55c \ud6a8\uc728\uc744 \ubf51\uc544\ub0b4\ub824\uba74, \ud574\ub2f9 \uc778\ub371\uc2a4\ub85c \ub9ce\uc740 \ubd80\ubd84\uc744 \uac78\ub7ec\ub0b4\uc57c \ud558\uae30 \ub54c\ubb38\uc785\ub2c8\ub2e4.\n\ub9cc\uc57d \uc131\ubcc4\uc744 \uc778\ub371\uc2a4\ub85c \uc7a1\ub294\ub2e4\uba74, \ub0a8\/\ub140 \uc911 \ud558\ub098\ub97c \uc120\ud0dd\ud558\uae30 \ub54c\ubb38\uc5d0 \uc778\ub371\uc2a4\ub97c \ud1b5\ud574 50%\ubc16\uc5d0 \uac78\ub7ec\ub0b4\uc9c0 \ubabb\ud569\ub2c8\ub2e4.\n\ud558\uc9c0\ub9cc \uc8fc\ubbfc\ub4f1\ub85d\ubc88\ud638\ub098 \uacc4\uc88c\ubc88\ud638 \uac19\uc740 \uacbd\uc6b0\uc5d4 \uc778\ub371\uc2a4\ub97c \ud1b5\ud574 \ub370\uc774\ud130\uc758 \ub300\ubd80\ubd84\uc744 \uac78\ub7ec\ub0b4\uae30 \ub54c\ubb38\uc5d0 \ube60\ub974\uac8c \uac80\uc0c9\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4.\n\n3-1. \uc5ec\ub7ec \uceec\ub7fc\uc73c\ub85c \uc778\ub371\uc2a4 \uad6c\uc131\uc2dc \uae30\uc900\n\uc790 \uadf8\ub7fc \uc5ec\uae30\uc11c \uad81\uae08\ud55c \uac83\uc774 \uc788\uc2b5\ub2c8\ub2e4.\n\uc5ec\ub7ec \uceec\ub7fc\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc7a1\ub294\ub2e4\uba74 \uc5b4\ub5a4 \uc21c\uc11c\ub85c \uc778\ub371\uc2a4\ub97c \uad6c\uc131\ud574\uc57c \ud560\uae4c\uc694?\n\uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub0ae\uc740->\ub192\uc740\uc21c\uc73c\ub85c \uad6c\uc131\ud558\ub294\uac8c \uc88b\uc744\uae4c\uc694?\n\uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub192\uc740->\ub0ae\uc740\uc21c\uc73c\ub85c \uad6c\uc131\ud558\ub294\uac8c \uc88b\uc744\uae4c\uc694?\n\uc2e4\uc81c \uc2e4\ud5d8\uc744 \ud1b5\ud574 \ud655\uc778\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n\ud14c\uc2a4\ud2b8 \ud658\uacbd\uc740 AWS EC2 Ubuntu 16.04\ub97c \uc0ac\uc6a9\ud588\uc2b5\ub2c8\ub2e4.\n\ucd5c\ub300\ud55c \uadf9\uc801\uc778 \ube44\uad50\ub97c \uc704\ud574 \uba54\ubaa8\ub9ac\ub294 1G, \ub514\uc2a4\ud06c\ub294 \ub9c8\uadf8\ub124\ud2f1(SSD X)\uc744 \uc0ac\uc6a9\ud588\uc2b5\ub2c8\ub2e4.\n\n\ud14c\uc774\ube14 \ud615\ud0dc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.\n\nCREATE TABLE `salaries` (\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n  `emp_no` int(11) NOT NULL,\n  `salary` int(11) NOT NULL,\n  `from_date` date NOT NULL,\n  `to_date` date NOT NULL,\n  `is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,\n  `group_no` varchar(255) DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\uc804\uccb4 Row\ub294 \uc57d 1700\ub9cc\uac74\uc73c\ub85c \uc0dd\uc131\ud588\uc2b5\ub2c8\ub2e4.\n\uac01 \uceec\ub7fc\uc758 \uce74\ub514\ub110\ub9ac\ud2f0\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.\n\nsalaries_\uce74\ub514\ub110\ub9ac\ud2f0\n\n\uc790 \uadf8\ub7fc \uc778\ub371\uc2a4\ub97c 2\uac00\uc9c0 \ud615\ud0dc\ub85c \uc0dd\uc131\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\nCREATE INDEX IDX_SALARIES_INCREASE ON salaries \n(is_bonus, from_date, group_no);\n\nCREATE INDEX IDX_SALARIES_DECREASE ON salaries \n(group_no, from_date, is_bonus);\n\uccab\ubc88\uc9f8 \uc778\ub371\uc2a4\ub294 is_bonus, from_date, group_no\uc21c\uc73c\ub85c \uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub0ae\uc740\uc21c\uc5d0\uc11c \ub192\uc740\uc21c (\uc911\ubcf5\ub3c4\uac00 \ub192\uc740 \uc21c\uc5d0\uc11c \ub0ae\uc740\uc21c\uc73c\ub85c) \uc73c\ub85c,\n\ub450\ubc88\uc9f8 \uc778\ub371\uc2a4\ub294 group_no, from_date, is_bonus\uc21c\uc73c\ub85c \uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub192\uc740\uc21c\uc5d0\uc11c \ub0ae\uc740\uc21c (\uc911\ubcf5\ub3c4\uac00 \ub0ae\uc740 \uc21c\uc5d0\uc11c \ub192\uc740\uc21c\uc73c\ub85c) \uc73c\ub85c \uc0dd\uc131\ud588\uc2b5\ub2c8\ub2e4.\n\n\uc0ac\uc6a9\ud55c \ucffc\ub9ac\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.\n\nselect SQL_NO_CACHE * \nfrom salaries \nuse index (IDX_SALARIES_INCREASE)\nwhere from_date = '1998-03-30' \nand group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406') \nand is_bonus = true;\n\nselect SQL_NO_CACHE * \nfrom salaries \nuse index (IDX_SALARIES_DECREASE)\nwhere from_date = '1998-03-30' \nand group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406') \nand is_bonus = true;\n\uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc778\ub371\uc2a4\ub97c \uc790\ub3d9 \uc120\ud0dd\ud574\ubc84\ub9ac\ub2c8 Index Hint (use index (IDX_SALARIES_INCREASE)) \ub85c \uac15\uc81c\ub85c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\uc600\uc2b5\ub2c8\ub2e4.\n\n\uc774 \uc778\ub371\uc2a4 2\uac1c\ub97c \ucd1d 10\ud68c\ub85c \ud14c\uc2a4\ud2b8\ud558\uc600\uc2b5\ub2c8\ub2e4.\n\uacb0\uacfc\uac00 \uc5b4\ub5bb\uac8c \ub420\uae4c\uc694?\n\nIDX_SALARIES_INCREASE\tIDX_SALARIES_DECREASE\n1\t110ms\t46.9ms\n2\t89.5ms\t24.6ms\n3\t95.4ms\t38.1ms\n4\t85.6ms\t29.3ms\n5\t83.6ms\t29.3ms\n6\t85.2ms\t38.2ms\n7\t59.4ms\t26.1ms\n8\t64.2ms\t29.4ms\n9\t93.7ms\t25.7ms\n10\t102ms\t35.4ms\n\ud3c9\uade0\t86.86ms\t32.3ms\n\uc6d4\ub4f1\ud55c \ucc28\uc774\uac00 \ub098\uc9c4 \uc54a\uc9c0\ub9cc 10\ud68c\ub9cc\uc73c\ub85c \ube44\uad50\ub294 \uac00\ub2a5\ud55c\uac83 \uac19\uc2b5\ub2c8\ub2e4.\n\uc989, \uc5ec\ub7ec \uceec\ub7fc\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc7a1\ub294\ub2e4\uba74 \uce74\ub514\ub110\ub9ac\ud2f0\uac00 \ub192\uc740\uc21c\uc5d0\uc11c \ub0ae\uc740\uc21c\uc73c\ub85c (group_no, from_date, is_bonus) \uad6c\uc131\ud558\ub294\uac8c \ub354 \uc131\ub2a5\uc774 \ub6f0\uc5b4\ub0a9\ub2c8\ub2e4.\n\n\uc815\ud655\ud55c \uc131\ub2a5\ube44\uad50\ub97c \uc704\ud574\uc11c\ub294 MySQL \uce90\uc2dc \uc774\uc678\uc5d0 OS \uce90\uc2dc\ub3c4 \ube44\uc6cc\uc57c\ub9cc \ud588\uc2b5\ub2c8\ub2e4.\n\uadf8\ub798\uc11c \ucffc\ub9ac\uc758 \uc870\uac74 (group_no in ()) \uc5d0 \ud3ec\ud568\ub418\ub294 \uac12\ub4e4\uc744 \ud558\ub098\uc529 \ucd94\uac00\ud558\uba74\uc11c \ucffc\ub9ac\uac00 \uce90\uc2dc \uc548\ub418\uac8c \ud558\uc5ec \ube44\uad50\ud558\uc600\uc2b5\ub2c8\ub2e4.\n\n3-2. \uc5ec\ub7ec \uceec\ub7fc\uc73c\ub85c \uc778\ub371\uc2a4\uc2dc \uc870\uac74 \ub204\ub77d\n\uaf2d \uc778\ub371\uc2a4\uc758 \uceec\ub7fc\uc744 \ubaa8\ub450 \uc0ac\uc6a9\ud574\uc57c\ub9cc \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub418\ub294 \uac83\uc740 \uc544\ub2d9\ub2c8\ub2e4.\n\uadf8\ub807\ub2e4\uba74 \uc778\ub371\uc2a4 \uceec\ub7fc\uc911 \uc5b4\ub5a4 \uac83\ub4e4\uc740 \ub204\ub77d\ub418\uc5b4\ub3c4 \ub418\uace0, \ub204\ub77d\ub418\uba74 \uc548\ub418\ub294 \uac83\uc740 \uc5b4\ub5a4 \uac83\uc77c\uae4c\uc694?\n\n\uc608\ub97c \ub4e4\uc5b4 \uc544\ub798\uc640 \uac19\uc774 \uc778\ub371\uc2a4\uac00 \uc7a1\ud600\uc788\uc2b5\ub2c8\ub2e4.\n\n\uc778\ub371\uc2a4: group_no, from_date, is_bonus\n\uc5ec\uae30\uc11c \uc911\uac04\uc5d0 \uc788\ub294 from_date\ub97c \uc81c\uc678\ud55c \uc870\ud68c \ucffc\ub9ac\uc640 \uac00\uc7a5 \uc55e\uc5d0 \uc788\ub294 group_no\ub97c \uc81c\uc678\ud55c \uc870\ud68c \ucffc\ub9ac\ub97c \uc0ac\uc6a9\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n\uccab\ubc88\uc9f8 \uc870\ud68c\ucffc\ub9ac\uc758 \uc2e4\ud589\uacc4\ud68d\uc744 \ubcf4\uba74\n\n\uc870\ud68c\uc21c\uc11c1\n\n\uc774\ub807\uac8c \uc815\uc0c1\uc801\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud588\uc74c\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\nfiltered\uac00 10% \uc778\ub9cc\ud07c \ud6a8\uc728\uc801\uc73c\ub85c \uc0ac\uc6a9\ud558\uc9c0\ub294 \ubabb\ud588\uc9c0\ub9cc, \uc778\ub371\uc2a4\ub97c \ud0dc\uc6b8 \uc218 \uc788\ub294 \ucffc\ub9ac\uc785\ub2c8\ub2e4.\n\n\uadf8\ub7fc \ub450\ubc88\uc9f8 \uc870\ud68c\ucffc\ub9ac\uc758 \uc2e4\ud589\uacc4\ud68d\uc740 \uc5b4\ub5bb\uac8c \ub420\uae4c\uc694?\n\n\uc870\ud68c\uc21c\uc11c2\n\n\uc804\ud600 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uc9c0 \ubabb\ud588\uc74c\uc744 \ud655\uc778\ud560\uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uc870\ud68c \ucffc\ub9ac \uc0ac\uc6a9\uc2dc \uc778\ub371\uc2a4\ub97c \ud0dc\uc6b0\ub824\uba74 \ucd5c\uc18c\ud55c \uccab\ubc88\uc9f8 \uc778\ub371\uc2a4 \uc870\uac74\uc740 \uc870\ud68c\uc870\uac74\uc5d0 \ud3ec\ud568\ub418\uc5b4\uc57c\ub9cc \ud569\ub2c8\ub2e4.\n\uccab\ubc88\uc9f8 \uc778\ub371\uc2a4 \uceec\ub7fc\uc774 \uc870\ud68c \ucffc\ub9ac\uc5d0 \uc5c6\uc73c\uba74 \uc778\ub371\uc2a4\ub97c \ud0c0\uc9c0 \uc54a\ub294\ub2e4\ub294 \uc810\uc744 \uae30\uc5b5\ud558\uc2dc\uba74 \ub429\ub2c8\ub2e4.\n\n4. \uc778\ub371\uc2a4 \uc870\ud68c\uc2dc \uc8fc\uc758 \uc0ac\ud56d\nbetween, like, &lt;, > \ub4f1 \ubc94\uc704 \uc870\uac74\uc740 \ud574\ub2f9 \uceec\ub7fc\uc740 \uc778\ub371\uc2a4\ub97c \ud0c0\uc9c0\ub9cc, \uadf8 \ub4a4 \uc778\ub371\uc2a4 \uceec\ub7fc\ub4e4\uc740 \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.\n\uc989, group_no, from_date, is_bonus\uc73c\ub85c \uc778\ub371\uc2a4\uac00 \uc7a1\ud600\uc788\ub294\ub370 \uc870\ud68c \ucffc\ub9ac\ub97c where group_no=XX and is_bonus=YY and from_date > ZZ\ub4f1\uc73c\ub85c \uc7a1\uc73c\uba74 is_bonus\ub294 \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.\n\ubc94\uc704\uc870\uac74\uc73c\ub85c \uc0ac\uc6a9\ud558\uba74 \uc548\ub41c\ub2e4\uace0 \uae30\uc5b5\ud558\uc2dc\uba74 \uc880 \ub354 \uc27d\uc2b5\ub2c8\ub2e4.\n\ubc18\ub300\ub85c =, in \uc740 \ub2e4\uc74c \uceec\ub7fc\ub3c4 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.\nin\uc740 \uacb0\uad6d =\ub97c \uc5ec\ub7ec\ubc88 \uc2e4\ud589\uc2dc\ud0a8 \uac83\uc774\uae30 \ub54c\ubb38\uc785\ub2c8\ub2e4.\n\ub2e8, in\uc740 \uc778\uc790\uac12\uc73c\ub85c \uc0c1\uc218\uac00 \ud3ec\ud568\ub418\uba74 \ubb38\uc81c \uc5c6\uc9c0\ub9cc, \uc11c\ube0c\ucffc\ub9ac\ub97c \ub123\uac8c\ub418\uba74 \uc131\ub2a5\uc0c1 \uc774\uc288\uac00 \ubc1c\uc0dd\ud569\ub2c8\ub2e4.\nin\uc758 \uc778\uc790\ub85c \uc11c\ube0c\ucffc\ub9ac\uac00 \ub4e4\uc5b4\uac00\uba74 \uc11c\ube0c\ucffc\ub9ac\uc758 \uc678\ubd80\uac00 \uba3c\uc800 \uc2e4\ud589\ub418\uace0, in \uc740 \uccb4\ud06c\uc870\uac74\uc73c\ub85c \uc2e4\ud589\ub418\uae30 \ub54c\ubb38\uc785\ub2c8\ub2e4.\nAND\uc5f0\uc0b0\uc790\ub294 \uac01 \uc870\uac74\ub4e4\uc774 \uc77d\uc5b4\uc640\uc57c\ud560 ROW\uc218\ub97c \uc904\uc774\ub294 \uc5ed\ud560\uc744 \ud558\uc9c0\ub9cc, or \uc5f0\uc0b0\uc790\ub294 \ube44\uad50\ud574\uc57c\ud560 ROW\uac00 \ub354 \ub298\uc5b4\ub098\uae30 \ub54c\ubb38\uc5d0 \ud480 \ud14c\uc774\ube14 \uc2a4\uce94\uc774 \ubc1c\uc0dd\ud560 \ud655\ub960\uc774 \ub192\uc2b5\ub2c8\ub2e4.\nWHERE \uc5d0\uc11c OR\uc744 \uc0ac\uc6a9\ud560\ub54c\ub294 \uc8fc\uc758\uac00 \ud544\uc694\ud569\ub2c8\ub2e4.\n\uc778\ub371\uc2a4\ub85c \uc0ac\uc6a9\ub41c \uceec\ub7fc\uac12 \uadf8\ub300\ub85c \uc0ac\uc6a9\ud574\uc57c\ub9cc \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub429\ub2c8\ub2e4.\n\uc778\ub371\uc2a4\ub294 \uac00\uacf5\ub41c \ub370\uc774\ud130\ub97c \uc800\uc7a5\ud558\uace0 \uc788\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.\nwhere salary * 10 > 150000;\ub294 \uc778\ub371\uc2a4\ub97c \ubabb\ud0c0\uc9c0\ub9cc, where salary > 150000 \/ 10; \uc740 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.\n\uceec\ub7fc\uc774 \ubb38\uc790\uc5f4\uc778\ub370 \uc22b\uc790\ub85c \uc870\ud68c\ud558\uba74 \ud0c0\uc785\uc774 \ub2ec\ub77c \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4. \uc815\ud655\ud55c \ud0c0\uc785\uc744 \uc0ac\uc6a9\ud574\uc57c\ub9cc \ud569\ub2c8\ub2e4.\nnull \uac12\uc758 \uacbd\uc6b0 is null \uc870\uac74\uc73c\ub85c \uc778\ub371\uc2a4 \ub808\uc778\uc9c0 \uc2a4\uce94 \uac00\ub2a5\n5. \uc778\ub371\uc2a4 \uceec\ub7fc \uc21c\uc11c\uc640 \uc870\ud68c \uceec\ub7fc \uc21c\uc11c\n\ucd5c\uadfc\uc5d4 \uc774\uc804\uacfc \uac19\uc774 \uaf2d \uc778\ub371\uc2a4 \uc21c\uc11c\uc640 \uc870\ud68c \uc21c\uc11c\ub97c \uc9c0\ud0ac \ud544\uc694\ub294 \uc5c6\uc2b5\ub2c8\ub2e4.\n\uc778\ub371\uc2a4 \uceec\ub7fc\ub4e4\uc774 \uc870\ud68c\uc870\uac74\uc5d0 \ud3ec\ud568\ub418\uc5b4 \uc788\ub294\uc9c0\uac00 \uc911\uc694\ud569\ub2c8\ub2e4.\n\n\uc870\ud68c\uceec\ub7fc\uacfc\uc778\ub371\uc2a4\uc21c\uc11c\n\n(3-1 \uc2e4\ud5d8\uacfc \ub3d9\uc77c\ud55c \uc778\ub371\uc2a4\uc5d0 \uc870\ud68c \uc21c\uc11c\ub9cc \ubcc0\uacbd\ud574\uc11c \uc2e4\ud589\ud55c \uacb0\uacfc)\n\n\ubcf4\uc2dc\ub294\uac83\ucc98\ub7fc \uc870\ud68c \uceec\ub7fc\uc758 \uc21c\uc11c\ub294 \uc778\ub371\uc2a4\uc5d0 \ud070 \uc601\ud5a5\uc744 \ub07c\uce58\uc9c0 \ubabb\ud569\ub2c8\ub2e4.\n\ub2e8, \uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc870\ud68c \uc870\uac74\uc758 \uceec\ub7fc\uc744 \uc778\ub371\uc2a4 \uceec\ub7fc \uc21c\uc11c\uc5d0 \ub9de\ucdb0 \uc7ac\ubc30\uc5f4\ud558\ub294 \uacfc\uc815\uc774 \ucd94\uac00\ub418\uc9c0\ub9cc \uac70\uc758 \ucc28\uc774\uac00 \uc5c6\uae34 \ud569\ub2c8\ub2e4.\n(\uadf8\ub798\ub3c4 \uc774\uc655\uc774\uba74 \ub9de\ucd94\ub294\uac8c \uc870\uae08\uc774\ub098\ub9c8 \ub0ab\uaca0\uc8e0?)\n\n6. \ud398\uc774\uc9d5 \uc131\ub2a5 \uac1c\uc120 \ud301\n\uc704 \uc778\ub371\uc2a4 \uc9c0\uc2dd\uc744 \ud1b5\ud574 \ud398\uc774\uc9d5 \uc131\ub2a5 \uac1c\uc120\uc744 \uc6d0\ud558\uc2dc\ub294 \ubd84\ub4e4\uc740 \uae30\uc874\uc758 \ud3ec\uc2a4\ud305\uc744 \ucc38\uace0\ud558\uc2dc\uba74 \uc88b\uc2b5\ub2c8\ub2e4.\n\nNoOffset \uc0ac\uc6a9\ud558\uae30\n\ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \uc0ac\uc6a9\ud558\uae30\ncount \ucffc\ub9ac \ucd5c\uc801\ud654 1, 2<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>2. \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \uc0ac\uc6a9\ud558\uae30\n\uc55e\uc11c 1\ubc88\uae00 \ucc98\ub7fc No Offset \ubc29\uc2dd\uc73c\ub85c \uac1c\uc120\ud560 \uc218 \uc788\ub2e4\uba74 \uc815\ub9d0 \uc88b\uaca0\uc9c0\ub9cc, NoOffset \ud398\uc774\uc9d5\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub294 \uc0c1\ud669\uc774\ub77c\uba74 \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c \uc131\ub2a5\uc744 \uac1c\uc120\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub780 \ucffc\ub9ac\ub97c \ucda9\uc871\uc2dc\ud0a4\ub294 \ub370 \ud544\uc694\ud55c \ubaa8\ub4e0 \ub370\uc774\ud130\ub97c \uac16\uace0 \uc788\ub294 \uc778\ub371\uc2a4\ub97c \uc774\uc57c\uae30\ud569\ub2c8\ub2e4.\n\n\uc989, select, where, order by, limit, group by \ub4f1\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 \ubaa8\ub4e0 \uceec\ub7fc\uc774 Index \uceec\ub7fc\uc548\uc5d0 \ub2e4 \ud3ec\ud568\ub41c \uacbd\uc6b0\uc778\ub370\uc694.\n\n\uc5ec\uae30\uc11c \ud558\ub098\uc758 \uc758\ubb38\uc774 \ub4dc\ub294 \uac83\uc740 select\uc808\uae4c\uc9c0 \ud3ec\ud568\ud558\uac8c \ub418\uba74 \ub108\ubb34 \ub9ce\uc740 \uceec\ub7fc\uc774 \uc778\ub371\uc2a4\uc5d0 \ud3ec\ud568\ub418\uc9c0 \uc54a\uaca0\ub0d0\ub294 \uac83\uc778\ub370\uc694.\n\uadf8\ub798\uc11c \uc2e4\uc81c\ub85c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \ud0dc\uc6b0\ub294 \ubd80\ubd84\uc740 select\ub97c \uc81c\uc678\ud55c \ub098\uba38\uc9c0\ub9cc \uc6b0\uc120\uc73c\ub85c \uc218\ud589\ud569\ub2c8\ub2e4.\n\n\uc608\ub97c \ub4e4\uc5b4 \uc544\ub798\uc640 \uac19\uc740 \ud398\uc774\uc9d5 \ucffc\ub9ac\ub97c\n\nSELECT *\nFROM items\nWHERE \uc870\uac74\ubb38\nORDER BY id DESC\nOFFSET \ud398\uc774\uc9c0\ubc88\ud638\nLIMIT \ud398\uc774\uc9c0\uc0ac\uc774\uc988\n\uc544\ub798\ucc98\ub7fc \ucc98\ub9ac\ud55c \ucf54\ub4dc\ub97c \uc774\uc57c\uae30\ud569\ub2c8\ub2e4.\n\nSELECT  *\nFROM  items as i\nJOIN (SELECT id\n        FROM items\n        WHERE \uc870\uac74\ubb38\n        ORDER BY id DESC\n        OFFSET \ud398\uc774\uc9c0\ubc88\ud638\n        LIMIT \ud398\uc774\uc9c0\uc0ac\uc774\uc988) as temp on temp.id = i.id\n\uc704 \ucffc\ub9ac\uc5d0\uc11c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\uac00 \uc0ac\uc6a9\ub41c \ubd80\ubd84\uc774 JOIN\uc5d0 \uc788\ub294 \ucffc\ub9ac\uc785\ub2c8\ub2e4.\n(\uc544\ub798 \ucffc\ub9ac\uc785\ub2c8\ub2e4.)\n\nSELECT id\nFROM items\nWHERE \uc870\uac74\ubb38\nORDER BY id DESC\nOFFSET \ud398\uc774\uc9c0\ubc88\ud638\nLIMIT \ud398\uc774\uc9c0\uc0ac\uc774\uc988\nselect\uc808\uc744 \ube44\ub86f\ud574 order by, where \ub4f1 \ucffc\ub9ac \ub0b4 \ubaa8\ub4e0 \ud56d\ubaa9\uc774 \uc778\ub371\uc2a4 \uceec\ub7fc\uc73c\ub85c\ub9cc \uc774\ub8e8\uc5b4\uc9c0\uac8c \ud558\uc5ec \uc778\ub371\uc2a4 \ub0b4\ubd80\uc5d0\uc11c \ucffc\ub9ac\uac00 \uc644\uc131\ub420 \uc218 \uc788\ub3c4\ub85d \ud558\ub294 \ubc29\uc2dd\uc778\ub370\uc694.\n\uc774\ub807\uac8c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c \ube60\ub974\uac8c \uac78\ub7ec\ub0b8 row\uc758 id\ub97c \ud1b5\ud574 \uc2e4\uc81c select \uc808\uc758 \ud56d\ubaa9\ub4e4\uc744 \ube60\ub974\uac8c \uc870\ud68c\ud574\uc624\ub294 \ubc29\ubc95\uc785\ub2c8\ub2e4.\n\n2-1. \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub294 \uc65c \ube60\ub978\uac00?\n\uc77c\ubc18\uc801\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc774\uc6a9\ud574 \uc870\ud68c\ub418\ub294 \ucffc\ub9ac\uc5d0\uc11c \uac00\uc7a5 \ud070 \uc131\ub2a5 \uc800\ud558\ub97c \uc77c\uc73c\ud0a4\ub294 \ubd80\ubd84\uc740 \uc778\ub371\uc2a4\ub97c \uac80\uc0c9\ud558\uace0 \ub300\uc0c1\uc774 \ub418\ub294 row\uc758 \ub098\uba38\uc9c0 \uceec\ub7fc\uac12\uc744 \ub370\uc774\ud130 \ube14\ub85d\uc5d0\uc11c \uc77d\uc744 \ub54c \uc785\ub2c8\ub2e4.\n\n\ud398\uc774\uc9d5 \ucffc\ub9ac\uc640 \ubb34\uad00\ud558\uac8c \uc778\ub371\uc2a4\ub97c \ud0d4\uc74c\uc5d0\ub3c4 \ub290\ub9b0 \ucffc\ub9ac\uc758 \uacbd\uc6b0 \uc774 select\uc808 \ud56d\ubaa9 \ub54c\ubb38\uc785\ub2c8\ub2e4.\n\n\uc774\ub97c\ud14c\uba74 \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \ud0dc\uc6b0\uc9c0 \uc54a\uc740 \uc77c\ubc18\uc801\uc778 \uc870\ud68c \ucffc\ub9ac\ub294 order by, offset ~ limit \uc744 \uc218\ud589\ud560\ub54c\ub3c4 \ub370\uc774\ud130 \ube14\ub85d\uc73c\ub85c \uc811\uadfc\uc744 \ud558\uac8c \ub429\ub2c8\ub2e4.\n\ncovering_intro\n\n\ubc18\ub300\ub85c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \ubc29\uc2dd\uc744 \uc774\uc6a9\ud558\uba74, where, order by, offset ~ limit \uc744 \uc778\ub371\uc2a4 \uac80\uc0c9\uc73c\ub85c \ube60\ub974\uac8c \ucc98\ub9ac\ud558\uace0, \uc774\ubbf8 \ub2e4 \uac78\ub7ec\uc9c4 10\uac1c\uc758 row\uc5d0 \ub300\ud574\uc11c\ub9cc \ub370\uc774\ud130 \ube14\ub85d\uc5d0 \uc811\uadfc\ud558\uae30 \ub54c\ubb38\uc5d0 \uc131\ub2a5\uc758 \uc774\uc810\uc744 \uc5bb\uac8c \ub429\ub2c8\ub2e4.\n\ncovering_intro2\n\nselect, where, order by, group by, having \ub4f1\uc5d0 \uc0ac\uc6a9\ub418\ub294 \uceec\ub7fc\uc5d0 \ub300\ud55c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\uc758 \uc801\uc6a9 \ubc29\uc2dd\uc740 \uc774\uc804\uc5d0 \uc791\uc131\ub41c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \uc2dc\ub9ac\uc988\ub97c \ucc38\uace0\ud558\uc2dc\uba74 \uc88b\uc2b5\ub2c8\ub2e4.\n1. \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 (\uae30\ubcf8 \uc9c0\uc2dd \/ WHERE \/ GROUP BY)\n2. \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 (WHERE + ORDER BY \/ GROUP BY + ORDER BY )\n\n\uc608\ub97c \ub4e4\uc5b4 \uc55e\uc11c 1\ubc88\uc5d0\uc11c \uc0ac\uc6a9\ub41c \uae30\uc874\uc758 \ud398\uc774\uc9d5 \ucffc\ub9ac\ub294\n\nselect id, book_no, book_type, name\nfrom book\nwhere name like '200%'\norder by id desc\nlimit 10 offset 10000;\nselect\uc5d0\uc11c \uc0ac\uc6a9\ub41c book_no, book_type\uc774 \uc778\ub371\uc2a4 (idx_book_1(name))\uc5d0 \ud3ec\ud568\ub418\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0 \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\uac00 \ub420 \uc218\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.\n\nid\ub3c4 \uc778\ub371\uc2a4\uc5d0 \uc5c6\uc9c0 \uc54a\ub098\uc694? \ub77c\uace0 \uc9c8\ubb38\ud558\uc2e0\ub2e4\uba74 \uc55e \uae00\uc5d0\uc11c\ub3c4 \uc5b8\uae09\ud558\uace0 \uc788\uc9c0\ub9cc \ud074\ub7ec\uc2a4\ud130 \uc778\ub371\uc2a4(PK)\uc778 id\ub294 \ubaa8\ub4e0 \uc778\ub371\uc2a4\uc5d0 \uc790\ub3d9 \ud3ec\ud568\ub429\ub2c8\ub2e4.\n\n\uadf8\ub798\uc11c \ucffc\ub9ac\uc5d0\uc11c \uc624\ub798\uac78\ub9ac\ub294 \ud398\uc774\uc9d5 \uc791\uc5c5\uae4c\uc9c0\ub294 \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c \ube60\ub974\uac8c \ucc98\ub9ac\ud6c4, \ub9c8\uc9c0\ub9c9 \ud544\uc694\ud55c \uceec\ub7fc\ub4e4\ub9cc \ubcc4\ub3c4\ub85c \uac00\uc838\uc624\ub294 \ud615\ud0dc\ub97c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.\n\n2-2. \uad6c\ud604 \ucf54\ub4dc\n\uc790 \uadf8\ub7fc \uc774\uc81c \ud55c\ubc88 \uc2e4\uc81c\ub85c \ucf54\ub4dc\ub97c \uad6c\ud604\ud574\ubcfc\ud150\ub370\uc694.\n\uc774\ub294 2\uac00\uc9c0 \ubc84\uc804\uc73c\ub85c \ubcf4\uc5ec\ub4dc\ub9ac\uaca0\uc2b5\ub2c8\ub2e4.\n\nQuerydsl-JPA\n\uba3c\uc800 querydsl-jpa\uc5d0\uc11c \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud574\uc57c \ud55c\ub2e4\uba74 2\uac1c\uc758 \ucffc\ub9ac\ub85c \ubd84\ub9ac\ud574\uc11c \uc9c4\ud589\ud560 \uc218 \ubc16\uc5d0 \uc5c6\uc2b5\ub2c8\ub2e4.\n\uc774\uc720\ub294 Querydsl-jpa\uc5d0\uc11c\ub294 from\uc808\uc758 \uc11c\ube0c\ucffc\ub9ac\ub97c \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc778\ub370\uc694.\n\nJPQL\uc790\uccb4\uc5d0\uc11c \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.\n\n\uadf8\ub798\uc11c \uc774\ub97c \uc6b0\ud68c\ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc73c\ub85c \uc18c\uac1c\ub4dc\ub9bd\ub2c8\ub2e4.\n\n\ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \ud65c\uc6a9\ud574 \uc870\ud68c \ub300\uc0c1\uc758 PK\ub97c \uc870\ud68c\n\ud574\ub2f9 PK\ub85c \ud544\uc694\ud55c \uceec\ub7fc\ud56d\ubaa9\ub4e4 \uc870\ud68c\nQuerydsl-jpa\ub85c \uad6c\ud604\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.\n\npublic List&lt;BookPaginationDto> paginationCoveringIndex(String name, int pageNo, int pageSize) {\n        \/\/ 1) \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c \ub300\uc0c1 \uc870\ud68c\n        List&lt;Long> ids = queryFactory\n                .select(book.id)\n                .from(book)\n                .where(book.name.like(name + \"%\"))\n                .orderBy(book.id.desc())\n                .limit(pageSize)\n                .offset(pageNo * pageSize)\n                .fetch();\n\n        \/\/ 1-1) \ub300\uc0c1\uc774 \uc5c6\uc744 \uacbd\uc6b0 \ucd94\uac00 \ucffc\ub9ac \uc218\ud589 \ud560 \ud544\uc694 \uc5c6\uc774 \ubc14\ub85c \ubc18\ud658\n        if (CollectionUtils.isEmpty(ids)) {\n            return new ArrayList&lt;>();\n        }\n\n        \/\/ 2)\n        return queryFactory\n                .select(Projections.fields(BookPaginationDto.class,\n                        book.id.as(\"bookId\"),\n                        book.name,\n                        book.bookNo,\n                        book.bookType))\n                .from(book)\n                .where(book.id.in(ids))\n                .orderBy(book.id.desc())\n                .fetch(); \/\/ where in id\ub9cc \uc788\uc5b4 \uacb0\uacfc \uc815\ub82c\uc774 \ubcf4\uc7a5\ub418\uc9c0 \uc54a\ub294\ub2e4.\n}\n(1) select\uc5d0\ub294 id\ub9cc \ud3ec\ud568\ud558\uc5ec \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c \ud65c\uc6a9\ud574 \ube60\ub974\uac8c \uc870\ud68c\n\n(1-1) \uc870\ud68c \uacb0\uacfc id\uac00 \uc5c6\uc73c\uba74 \ube48\uac12 \ubc18\ud658\n\nquerydsl \ud2b9\uc131\uc0c1 in\uc5d0 Empty List\uac00 \ub4e4\uc5b4\uac00\uac8c \ub418\uba74 \uc624\ub958\uac00 \ubc1c\uc0dd \ud558\ub294 \uac83\uc744 \ubc29\uc9c0\n\ubd88\ud544\uc694\ud558\uac8c \ucd94\uac00 \ucffc\ub9ac \ubc1c\uc0dd\ud558\ub294 \uac83\uc744 \ubc29\uc9c0\n(2) 1\uc758 \uacb0\uacfc\ub85c \ubc1c\uc0dd\ud55c id\ub85c \uc2e4\uc81c select\uc808 \uc870\ud68c\n\nwhere id in () \ubc16\uc5d0 \uc5c6\uae30 \ub54c\ubb38\uc5d0 \uc815\ub82c \ub41c \uc0c1\ud0dc\ub85c \uc870\ud68c \ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c .orderBy(book.id.desc())\ub97c \ucd94\uac00\n\uc544\ubb34\ub798\ub3c4 \ud55c\ubc88\uc758 \ucffc\ub9ac\ub85c \ud574\uacb0\ud558\uc9c0 \ubabb\ud558\ub2e4\ubcf4\ub2c8 \uc870\uae08 \ubc88\uc7a1\ud55c \ub290\ub08c\uc740 \ub4ed\ub2c8\ub2e4.\n\nJdbcTemplate\n\ub450\ubc88\uc9f8\ub85c jdbcTemplate\uc744 \uc774\uc6a9\ud558\uc5ec \ubb38\uc790\uc5f4 \ucffc\ub9ac\ub97c \uc9c1\uc811 \uc0ac\uc6a9\ud558\ub294 \ubc29\uc2dd\uc778\ub370\uc694.\n\uc774\ub807\uac8c \uc9c4\ud589\ud558\uc2dc\uac8c \ub418\uba74 querydsl-jpa\ub97c \uc4f8\ub54c\ucc98\ub7fc \ucffc\ub9ac\ub974 \ubd84\ub9ac\ud560 \ud544\uc694 \uc5c6\uc774, \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub97c from\uc808\uc5d0 \uadf8\ub300\ub85c \uc0ac\uc6a9\ud558\uc2dc\uba74 \ub429\ub2c8\ub2e4.\n\npublic List&lt;BookPaginationDto> paginationCoveringIndexSql(String name, int pageNo, int pageSize) {\nString query =\n        \"SELECT i.id, book_no, book_type, name \" +\n        \"FROM book as i \" +\n        \"JOIN (SELECT id \" +\n        \"       FROM book \" +\n        \"       WHERE name LIKE '?%' \" +\n        \"       ORDER BY id DESC \" +\n        \"       LIMIT ? \" +\n        \"       OFFSET ?) as temp on temp.id = i.id\";\n\nreturn jdbcTemplate\n        .query(query, new BeanPropertyRowMapper&lt;>(BookPaginationDto.class),\n                name,\n                pageSize,\n                pageNo * pageSize);\n}\n\ub2e4\ub9cc jdbcTemplate\ub97c \uc774\uc6a9\ud55c \ubc29\uc2dd\uc740 querydsl\uc774 \uac16\uace0 \uc788\ub294 \ucef4\ud30c\uc77c\uccb4\ud06c\/\uc790\ub3d9\uc644\uc131 \ub4f1 \uc7a5\uc810\uc774 \uc644\uc804\ud788 \uc0ac\ub77c\uc9c0\uae30 \ub54c\ubb38\uc5d0 \uac1c\uc778\uc801\uc73c\ub85c \uc120\ud638\ud558\uc9c4 \uc54a\uc2b5\ub2c8\ub2e4.\n\n2-3. \uc131\ub2a5 \ube44\uad50\n\uc790 \uadf8\ub7fc \uc774\ub807\uac8c \uac1c\uc120\ub41c \ubc29\uc2dd\uc73c\ub85c\ub294 \uc131\ub2a5\uc774 \uc5bc\ub9c8\ub098 \uc88b\uc544\uc9c8\uae4c\uc694?\n\uc2e4\ud589 \uacc4\ud68d\uacfc \ud568\uaed8 \ube44\uad50\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\uc544\ub798\uc640 \uac19\uc740 \ud658\uacbd\uc5d0\uc11c \ube44\uad50\ub97c \ud574\ubd05\ub2c8\ub2e4.\n\n\ud14c\uc2a4\ud2b8 DB\nAWS RDS Aurora MySQL r5.large\n\ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\n1\uc5b5 row\n5\uac1c \uceec\ub7fc\n\uae30\uc874 \ud398\uc774\uc9d5\n\uba3c\uc800 \uae30\uc874\uc758 \ud398\uc774\uc9d5\ubc29\uc2dd\uc785\ub2c8\ub2e4.\n\n1\ud3b8\uacfc \ub3d9\uc77c\ud569\ub2c8\ub2e4.\n\n@Test\nvoid \uae30\uc874_\ud398\uc774\uc9d5_\ubc29\uc2dd() throws Exception {\n        \/\/given\n        String name = \"200\";\n\n        \/\/when\n        List&lt;BookPaginationDto> books = bookPaginationRepository.paginationLegacy(name, 10_000, 10);\n\n        \/\/then\n        assertThat(books).hasSize(10);\n}\nexplain_legacy\n\nresult_legacy\n\n\uc2e4\uc81c \uc218\ud589 \uc2dc\uac04\uc740 26.47\ucd08 \uc785\ub2c8\ub2e4.\n\nQuerydsl \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\n2\ubc88\uc9f8\ub294 \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c pk\uc870\ud68c &amp; \uceec\ub7fc \uc870\ud68c\ub85c \uc774\ub8e8\uc5b4\uc9c0\ub294 Querydsl \uc785\ub2c8\ub2e4.\n\n@Test\nvoid \ucee4\ubc84\ub9c1\uc778\ub371\uc2a4() throws Exception {\n        \/\/given\n        String name = \"200\";\n\n        \/\/when\n        List&lt;BookPaginationDto> books = bookPaginationRepository.paginationCoveringIndex(name, 1, 10);\n\n        \/\/then\n        assertThat(books).hasSize(10);\n}\nexplain_querydsl\n\nresult_querydsl\n\n\uc55e\uc11c \uae30\uc874 \ubc29\uc2dd\uc5d0 \ube44\ud574 \ub9d0\ub3c4 \uc548\ub418\uac8c \uac1c\uc120\ub41c 0.57\ucd08 \uc785\ub2c8\ub2e4.\n\nJdbcTemplate \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\n\ub9c8\uc9c0\ub9c9\uc73c\ub85c \ud55c\ubc88\uc758 \ucffc\ub9ac\ub85c \uc218\ud589\ub418\ub294 JdbcTemplate \ubc29\uc2dd\uc785\ub2c8\ub2e4.\n\n@Test\nvoid \ucee4\ubc84\ub9c1\uc778\ub371\uc2a4_jdbc() throws Exception {\n        \/\/given\n        String name = \"200\";\n\n        \/\/when\n        List&lt;BookPaginationDto> books = bookPaginationRepository.paginationCoveringIndexSql(name, 1, 10);\n\n        \/\/then\n        assertThat(books).hasSize(10);\n}\nexplain_jdbc\n\nresult_jdbc\n\n\ub9c8\ucc2c\uac00\uc9c0\ub85c \uc55e\uc11c \uae30\uc874 \ubc29\uc2dd\uc5d0 \ube44\ud574 \ub9d0\ub3c4 \uc548\ub418\uac8c \uac1c\uc120\ub41c 0.27\ucd08 \uc785\ub2c8\ub2e4.\n\n\uc989, Querydsl\uc744 \uc774\uc6a9\ud55c \ubc29\uc2dd\uc774\ub098 JdbcTemplate\uc744 \uc774\uc6a9\ud55c \ubc29\uc2dd \ubaa8\ub450\ub2e4 \uae30\uc874 \ud398\uc774\uc9d5 \ubc29\uc2dd\uc5d0 \ube44\ud574 \uc5c4\uccad\ub098\uac8c \uac1c\uc120\ub41c \uc131\ub2a5\uc744 \uc790\ub791\ud558\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n2-4. \ub2e8\uc810\n\ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \ubc29\uc2dd\uc740 \uc77c\ubc18\uc801\uc778 \ud398\uc774\uc9d5 \ubc29\uc2dd\uc5d0\uc11c\ub294 \uac70\uc758 \ub300\ubd80\ubd84 \uc801\uc6a9\ud560 \uc218 \uc788\ub294 \ud6a8\uacfc\uc801\uc778 \uac1c\uc120 \ubc29\ubc95\uc778 \uac83\uc744 \ud655\uc778\ud558\uc600\ub294\ub370\uc694.\nNo Offset \ubc29\uc2dd\ucc98\ub7fc UX\uc758 \ubcc0\uacbd \ub4f1\uc774 \ud544\uc694\ud558\uc9c0\ub3c4 \uc54a\ub2e4\ubcf4\ub2c8 \ub2e8\uc810\uc774 \uc5c6\uc5b4\ubcf4\uc774\uc9c0\ub9cc \uc2e4\uc81c\ub85c\ub294 \uba87\uac1c\uc758 \ub2e8\uc810\uc774 \uc788\uc2b5\ub2c8\ub2e4.\n\n\ub108\ubb34 \ub9ce\uc740 \uc778\ub371\uc2a4\uac00 \ud544\uc694\ud558\ub2e4\n\uacb0\uad6d \ucffc\ub9ac\uc758 \ubaa8\ub4e0 \ud56d\ubaa9\uc774 \uc778\ub371\uc2a4\uc5d0 \ud3ec\ud568\ub418\uc5b4\uc57c\ud558\uae30 \ub54c\ubb38\uc5d0 \ub290\ub9b0 \ucffc\ub9ac\uac00 \ubc1c\uc0dd\ud560\ub54c\ub9c8\ub2e4 \uc778\ub371\uc2a4\uac00 \uc2e0\uaddc \uc0dd\uc131\ub420 \uc218\ub3c4 \uc788\ub2e4.\n\uc778\ub371\uc2a4 \ud06c\uae30\uac00 \ub108\ubb34 \ucee4\uc9c4\ub2e4\n\uc778\ub371\uc2a4\ub3c4 \uacb0\uad6d \ub370\uc774\ud130\uc774\uae30 \ub54c\ubb38\uc5d0 \ub108\ubb34 \ub9ce\uc740 \ud56d\ubaa9\uc774 \ub4e4\uc5b4\uac00\uba74 \uc131\ub2a5 \uc0c1 \uc774\uc288\uac00 \ubc1c\uc0dd\ud560 \uc218 \ubc16\uc5d0 \uc5c6\ub294\ub370, where\uc808\uc5d0 \ud544\uc694\ud55c \uceec\ub7fc\uc678\uc5d0\ub3c4 order by, group by, having \ub4f1\uc5d0 \ub4e4\uc5b4\uac00\ub294 \uceec\ub7fc\ub4e4\uae4c\uc9c0 \uc778\ub371\uc2a4\uc5d0 \ub4e4\uc5b4\uac00\uac8c \ub418\uba74 \uc778\ub371\uc2a4 \ud06c\uae30\uac00 \ub108\ubb34 \ube44\ub300\ud574\uc9c4\ub2e4.\n\ub370\uc774\ud130 \uc591\uc774 \ub9ce\uc544\uc9c0\uace0, \ud398\uc774\uc9c0 \ubc88\ud638\uac00 \ub4a4\ub85c \uac08\uc218\ub85d NoOffset\uc5d0 \ube44\ud574 \ub290\ub9ac\ub2e4.\n\uc2dc\uc791 \uc9c0\uc810\uc744 PK\ub85c \uc9c0\uc815\ud558\uace0 \uc870\ud68c\ud558\ub294 NoOffset \ubc29\uc2dd\uc5d0 \ube44\ud574\uc11c \uc131\ub2a5 \ucc28\uc774\uac00 \uc788\uc74c (NoOffset\uacfc \ub3d9\uc77c\ud55c \ub370\uc774\ud130 \uc870\ud68c\uc2dc \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \ubc29\uc2dd\uc740 272ms, No Offset\uc740 83ms)\n\ud14c\uc774\ube14 \uc0ac\uc774\uc988\uac00 \uacc4\uc18d \ucee4\uc9c0\uba74 No Offset \ubc29\uc2dd\uc5d0 \ube44\ud574\uc11c\ub294 \uc131\ub2a5 \ucc28\uc774\uac00 \ubc1c\uc0dd\n\ub300\ubd80\ubd84\uc758 \ud398\uc774\uc9d5 \ucffc\ub9ac\ub294 No Offset\uacfc \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4\ub85c \ucd5c\uc801\ud654\uac00 \ub429\ub2c8\ub2e4.<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>\uc55e\uc11c \ud3ec\uc2a4\ud305\uc5d0\uc11c \uc2e4\uc9c8 \ud398\uc774\uc9d5 \ucffc\ub9ac \uc131\ub2a5\uc744 \uc62c\ub9ac\ub294 \ubc29\ubc95\ub4e4\uc744 \uc18c\uac1c \ub4dc\ub838\ub294\ub370\uc694.\n\n1. \ud398\uc774\uc9d5 \uc131\ub2a5 \uac1c\uc120\ud558\uae30 - No Offset \uc0ac\uc6a9\ud558\uae30\n2. \ud398\uc774\uc9d5 \uc131\ub2a5 \uac1c\uc120\ud558\uae30 - \ucee4\ubc84\ub9c1 \uc778\ub371\uc2a4 \uc0ac\uc6a9\ud558\uae30\n\ud398\uc774\uc9d5 \uae30\ub2a5\uc744 \uad6c\ud604\ud558\ub294\ub370 \uc788\uc5b4, \ud398\uc774\uc9d5 \ucffc\ub9ac \uc790\uccb4\ub97c \uac1c\uc120\ud558\ub294 \uac83\ub3c4 \ubc29\ubc95\uc774\uc9c0\ub9cc \uadf8 \uc678 \ub2e4\ub978 \uae30\ub2a5\uc744 \uac1c\uc120\ud558\ub294 \ubc29\ubc95\ub3c4 \ud568\uaed8\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uc5ec\uae30\uc11c \ub9d0\ud558\ub294 \uadf8 \uc678 \uae30\ub2a5\uc740 \ubc14\ub85c count \ucffc\ub9ac\uc785\ub2c8\ub2e4.\n\uc77c\ubc18\uc801\uc778 \ud398\uc774\uc9d5 \uae30\ub2a5\uc5d0 \uc788\uc5b4 \ub370\uc774\ud130 \uc870\ud68c\uc640 \ud568\uaed8 \ub9e4\ubc88 \ud568\uaed8 \uc218\ud589\ub418\ub294 \uac83\uc774 \ubc14\ub85c count \ucffc\ub9ac\uc778\ub370\uc694.\n\ud574\ub2f9 \uc870\uac74\uc73c\ub85c \uc870\ud68c\ub418\ub294 \ucd1d \uac74\uc218\ub97c \uc54c\uc544\uc57c\ub9cc \uc544\ub798\uc640 \uac19\uc774 pageNo\ub4e4\uc744 \ub178\ucd9c\uc2dc\ud0ac \uc218 \uc788\uae30 \ub54c\ubb38\uc785\ub2c8\ub2e4.\n(\ucd1d \uac74\uc218 \/ pageSize)\n\ncount_no\n\n\ub2f9\uc5f0\ud788 No Offset\uc744 \uc0ac\uc6a9\ud55c\ub2e4\uba74 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294 \ucffc\ub9ac\uc785\ub2c8\ub2e4.\n\n\uc5ec\uae30\uc11c count \ucffc\ub9ac\uc5d0 \ub300\ud574 \ud06c\uac8c \uc0dd\uac01\ud558\uc9c0 \uc54a\uc73c\uc2dc\ub294 \ubd84\ub4e4\ub3c4 \uacc4\uc2dc\ub294\ub370\uc694.\n(\uc870\ud68c \uac74\uc218\uc5d0 \ub530\ub77c \ucc28\uc774\uac00 \ub098\uc9c0\ub9cc) \uc2e4\uc81c \ub370\uc774\ud130 \uc870\ud68c\ub9cc\ud07c \uc624\ub798 \uac78\ub9ac\uae30\ub3c4 \ud569\ub2c8\ub2e4.\n\n\uc774\uc720\ub294 \ucd1d \uba87\uac74\uc778\uc9c0 \ud655\uc778\ud558\uae30 \uc704\ud574 \uc804\uccb4\ub97c \ud655\uc778\ud574\uc57c\ud558\uae30 \ub54c\ubb38\uc785\ub2c8\ub2e4.\n\n\ub370\uc774\ud130 \uc870\ud68c\ub294 limit 10 \ub4f1\uc73c\ub85c \uc9c0\uc815\ub41c \uc0ac\uc774\uc988\ub9cc\ud07c \uc77d\uace0 \ub098\uc11c\ub294 \ub354\uc774\uc0c1 \uc77d\uc9c0 \uc54a\uc544\ub3c4 \ub418\uc9c0\ub9cc, count\ub294 \ub05d\uae4c\uc9c0 \uc77d\uc5b4\uc11c \uba87 \uac74\uc778\uc9c0 \ud655\uc778\ud574\uc57c\ud558\uae30 \ub54c\ubb38\uc5d0 \ud398\uc774\uc9d5 \ucffc\ub9ac\uc758 \uc131\ub2a5 \uc774\uc288 \uc911 \ud558\ub098\uac00 \ub429\ub2c8\ub2e4.\n\nlegacy_time\n\n(\uc5b4\ub5a4 \uc870\ud68c \ud658\uacbd\uc5d0\uc11c\ub294 count \ucffc\ub9ac\ub9cc 10\ucd08\uac00 \uac78\ub9ac\uae30\ub3c4 \ud569\ub2c8\ub2e4.)\n\nsimple_query\n\n(\uc774\ub807\uac8c \ub2e8\uc21c\ud55c \ucffc\ub9ac\ub3c4 1\uc5b5\uac74 \uc774\uc0c1\uc77c \uacbd\uc6b0 1\ucd08 \uc774\uc0c1 \uc218\ud589\ub418\uae30\ub3c4 \ud569\ub2c8\ub2e4.)\n\n\uadf8\ub798\uc11c \uc774 \ubb38\uc81c\ub97c \uac1c\uc120\ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc740 \ud06c\uac8c 2\uac00\uc9c0\uac00 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uac80\uc0c9 \ubc84\ud2bc \uc0ac\uc6a9\uc2dc \ud398\uc774\uc9c0 \uac74\uc218 \uace0\uc815\ud558\uae30\n\uccab \ud398\uc774\uc9c0 \uc870\ud68c \uacb0\uacfc cache \ud558\uae30\n\ud558\ub098\uc529 \uc54c\uc544\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n3-1. \uac80\uc0c9 \ubc84\ud2bc \uc0ac\uc6a9\uc2dc \ud398\uc774\uc9c0 \uac74\uc218 \uace0\uc815\ud558\uae30\n\uad6c\uae00 \uac80\uc0c9\uc744 \uc774\uc6a9\ud574\ubcf4\uc2e0 \ubd84\ub4e4\uc740 \uac04\ud639 \uacbd\ud5d8\ud558\uc2e4\ud150\ub370\uc694.\n\ucc98\uc74c \uac80\uc0c9 \ubc84\ud2bc\uc744 \ud074\ub9ad \ud588\uc744\ub54c\ub294 6\ud398\uc774\uc9c0 \ud639\uc740 10\ud398\uc774\uc9c0\ub85c \ubcf4\ub358 \uac80\uc0c9 \uacb0\uacfc\uac00\n\ngoogle_before\n\n\uc2e4\uc81c \ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud574\uc11c \ub118\uc5b4\uac00\uba74 \ud398\uc774\uc9c0 \uacb0\uacfc\uac00 \uc904\uc5b4\ub4dc\ub294 \uac83\uc744 \ubcfc \uc218 \uc788\ub294\ub370\uc694.\n\ngoogle_after\n\n\uc5ec\uae30\uc5d0\uc11c \ucee8\uc149\uc744 \ucc38\uace0\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uad73\uc774 \uc0ac\uc6a9\uc728\uc774 \ub5a8\uc5b4\uc9c0\ub294 \ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \uc704\ud574 \ub9e4\ubc88 \uc804\uccb4 count \ucffc\ub9ac\uac00 \uc218\ud589\ub420 \ud544\uc694\uac00 \uc788\uc744\uae4c\ub97c \ud55c\ubc88 \uace0\ubbfc\ud574\ubcfc \ud544\uc694\uac00 \uc788\ub294\ub370\uc694.\n\n\uc2e4\uc81c\ub85c \uad6c\uae00\uc758 \uac80\uc0c9 \ud398\uc774\uc9c0 \uacb0\uacfc\uac00 \uc5b4\ub5bb\uac8c \uad6c\ud604\ub418\uc5b4\uc788\ub294\uc9c0\ub294 \uc54c \uc218 \uc5c6\uae30 \ub54c\ubb38\uc5d0 \ucee8\uc149\ub9cc \ucc38\uace0\ud588\ub2e4\ub294 \uac83\uc744 \ub9d0\uc500\ub4dc\ub9bd\ub2c8\ub2e4.\n\uad6c\uae00 \uac19\uc774 \uc5c4\uccad\ub098\uac8c \ubc29\ub300\ud55c \ub370\uc774\ud130\ub97c \uc801\uc7ac\ud574\uc11c \uc81c\uacf5\ud558\ub294 \uc11c\ube44\uc2a4\uc5d0\uc11c \uc774\ub807\uac8c \ub2e8\uc21c\ud55c RDBMS \ub97c \uc0ac\uc6a9\ud558\uc9c4 \uc54a\uc744\ud14c\ub2c8 \"\uad6c\uae00\uc774 \uc774\ub807\uac8c \ud558\ub354\ub77c\" \ub77c\uace0 \uc0dd\uac01\ud558\uc2dc\uba74 \uc548\ub429\ub2c8\ub2e4.\n\n\uc989, \ub2e4\uc74c\uacfc \uac19\uc740 \uc0c1\ud669\uc5d0\uc11c \uc774 \ubc29\ubc95\uc744 \uace0\ub824\ud574\ubcf4\uc2dc\uba74 \uc88b\uc2b5\ub2c8\ub2e4.\n\n\ub300\ubd80\ubd84\uc758 \uc870\ud68c \uc694\uccad\uc774 \uac80\uc0c9 \ubc84\ud2bc \ud074\ub9ad (\uc989, \uccab \uc870\ud68c)\uc5d0\uc11c \ubc1c\uc0dd\ud558\uace0\n\ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud1b5\ud55c \uc870\ud68c \uc694\uccad\uc774 \uc18c\uc218\uc77c \uacbd\uc6b0\n\uc774\ub7f4 \uacbd\uc6b0 \uac80\uc0c9 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud55c \uacbd\uc6b0\ub9cc Page \uc218\ub97c \uace0\uc815\ud558\ub294 \uac83 \uc785\ub2c8\ub2e4.\n\n\uc989, \ub2e4\uc74c \ud398\uc774\uc9c0\ub85c \uc774\ub3d9\ud558\uae30 \uc704\ud574 \ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud588\uc744\ub54c\ub9cc \uc2e4\uc81c \ud398\uc774\uc9c0 count \ucffc\ub9ac\ub97c \ubc1c\uc0dd\uc2dc\ucf1c \uc815\ud655\ud55c \ud398\uc774\uc9c0\uc218\ub97c \uc0ac\uc6a9\ud558\uace0, \ub300\ubd80\ubd84\uc758 \uc694\uccad\uc774 \ubc1c\uc0dd\ud558\ub294 \uac80\uc0c9 \ubc84\ud2bc \ud074\ub9ad\uc2dc\uc5d0\ub294 count \ucffc\ub9ac\ub97c \ubc1c\uc0dd\uc2dc\ud0a4\uc9c0 \uc54a\ub294 \uac83 \uc785\ub2c8\ub2e4.\n\n\uc790 \uadf8\ub7fc \uc2e4\uc81c \ucf54\ub4dc\ub97c \ud55c\ubc88 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n3-1-1. \uad6c\ud604 \ucf54\ub4dc\n\uba3c\uc800 \uae30\uc874 \ud398\uc774\uc9d5 \ucffc\ub9ac\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.\n\npublic Page&lt;BookPaginationDto> paginationCount(Pageable pageable, String name) {\n    JPQLQuery&lt;BookPaginationDto> query = querydsl().applyPagination(pageable,\n            queryFactory\n                    .select(Projections.fields(BookPaginationDto.class,\n                            book.id.as(\"bookId\"),\n                            book.name,\n                            book.bookNo,\n                            book.bookType\n                    ))\n                    .from(book)\n                    .where(\n                            book.name.like(name + \"%\")\n                    )\n                    .orderBy(book.id.desc()));\n\n    List&lt;BookPaginationDto> items = query.fetch(); \/\/ \ub370\uc774\ud130 \uc870\ud68c\n    long totalCount = query.fetchCount(); \/\/ \uc804\uccb4 count\n    return new PageImpl&lt;>(items, pageable, totalCount);\n}\n\nprivate Querydsl querydsl() {\n    return Objects.requireNonNull(getQuerydsl());\n}\n\uc774 \ucf54\ub4dc\ub97c \uac80\uc0c9 \ubc84\ud2bc \ud074\ub9ad\uc2dc\uc5d0\ub294 10\uac1c \ud398\uc774\uc9c0\ub97c \uace0\uc815\uc73c\ub85c \ub178\ucd9c\ud558\ub3c4\ub85d \uac1c\uc120\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub2e4\uc74c\uc758 \ucf54\ub4dc\uac00 \ucd94\uac00\ub418\uc5b4\uc57c \ud558\ub294\ub370\uc694.\n\n\uac80\uc0c9 \ubc84\ud2bc \ud074\ub9ad\ud55c \uacbd\uc6b0(useSearchBtn)\uc5d0\ub294 10\uac1c \ud398\uc774\uc9c0\uac00 \ub178\ucd9c\ub418\ub3c4\ub85d TotalCount (fixedPageCount) \ub97c \ubc18\ud658\ud55c\ub2e4.\n\ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud55c \uacbd\uc6b0 \uc2e4\uc81c \ucffc\ub9ac\ub97c \uc218\ud589\ud574 \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4\n\ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud558\uc600\uc9c0\ub9cc, \uc804\uccb4 \uac74\uc218\ub97c \ucd08\uacfc\ud55c \ud398\uc774\uc9c0 \ubc88\ud638\ub85c \uc694\uccad\uc774 \uc628 \uacbd\uc6b0\uc5d0\ub294 \ub9c8\uc9c0\ub9c9 \ud398\uc774\uc9c0 \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.\n\ub9c8\uc9c0\ub9c9 3\ubc88\uc774 \uc870\uae08 \ubcf5\uc7a1\ud55c \ub85c\uc9c1\uc778\ub370,\n\uc774\ub7f0 \uacbd\uc6b0\uac00 \ubc1c\uc0dd\ud558\ub294 \uc774\uc720\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.\n\n1\ubc88\uc73c\ub85c \uc778\ud574\uc11c \ub178\ucd9c\ub41c \ud398\uc774\uc9c0 \ubc88\ud638\ub294 10\uac1c\n\uc2e4\uc81c \uc804\uccb4 \uac74\uc218\uc640 \ubb34\ubc29\ud558\uac8c \uac15\uc81c\ub85c 10\uac1c \ud398\uc774\uc9c0\ub97c \ub178\ucd9c\uc2dc\ucf30\uae30 \ub54c\ubb38\uc5d0 \uc0ac\uc6a9\uc790\ub294 \uc5b8\uc81c\ub4e0 10\ubc88\uc9f8 \ud398\uc774\uc9c0 \ubc88\ud638\ub97c \ud074\ub9ad\ud560 \uc218 \uc788\uc74c\n10\ubc88\uc9f8 \ud398\uc774\uc9c0\ub97c \ud074\ub9ad\ud588\ub294\ub370, \ub9c9\uc0c1 \uc804\uccb4 \ub370\uc774\ud130\uac00 \uadf8\ub9cc\ud07c \uc548\ub41c\ub2e4\uba74 (ex: \uc804\uccb4 \uac74\uc218\uac00 70\uac1c\ub77c\uba74 pageSize=10 \ub77c\uc11c \uc2e4\uc81c \uc804\uccb4 \ud398\uc774\uc9c0 \uc218\uac00 7\uac1c\ubc16\uc5d0 \uc548\ub418\ub294 \uacbd\uc6b0) \ub178\ucd9c\ud560 \ub370\uc774\ud130\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.\n\uc790 \uadf8\ub798\uc11c \uc774\ub4e4\uc744 \ub2e4 \uc801\uc6a9\ud558\uac8c \ub418\uba74 \ub2e4\uc74c\uc758 \ucf54\ub4dc\uac00 \ub429\ub2c8\ub2e4.\n\npublic Page&lt;BookPaginationDto> paginationCountSearchBtn(boolean useSearchBtn, Pageable pageable, String name) {\n    JPAQuery&lt;BookPaginationDto> query = queryFactory\n            .select(Projections.fields(BookPaginationDto.class,\n                    book.id.as(\"bookId\"),\n                    book.name,\n                    book.bookNo,\n                    book.bookType\n            ))\n            .from(book)\n            .where(\n                    book.name.like(name + \"%\")\n            )\n            .orderBy(book.id.desc());\n\n    JPQLQuery&lt;BookPaginationDto> pagination = querydsl().applyPagination(pageable, query);\n\n    if(useSearchBtn) { \/\/ \uac80\uc0c9 \ubc84\ud2bc \uc0ac\uc6a9\uc2dc\n        int fixedPageCount = 10 * pageable.getPageSize(); \/\/ 10\uac1c \ud398\uc774\uc9c0 \uace0\uc815\n        return new PageImpl&lt;>(pagination.fetch(), pageable, fixedPageCount);\n    }\n\n    long totalCount = pagination.fetchCount();\n    Pageable pageRequest = exchangePageRequest(pageable, totalCount); \/\/ \ub370\uc774\ud130 \uac74\uc218\ub97c \ucd08\uacfc\ud55c \ud398\uc774\uc9c0 \ubc84\ud2bc \ud074\ub9ad\uc2dc \ubcf4\uc815\n    return new PageImpl&lt;>(querydsl().applyPagination(pageRequest, query).fetch(), pageRequest, totalCount);\n}\n\nPageable exchangePageRequest(Pageable pageable, long totalCount) {\n\n    \/**\n        *  \uc694\uccad\ud55c \ud398\uc774\uc9c0 \ubc88\ud638\uac00 \uae30\uc874 \ub370\uc774\ud130 \uc0ac\uc774\uc988\ub97c \ucd08\uacfc\ud560 \uacbd\uc6b0\n        *  \ub9c8\uc9c0\ub9c9 \ud398\uc774\uc9c0\uc758 \ub370\uc774\ud130\ub97c \ubc18\ud658\ud55c\ub2e4\n        *\/\n    int pageNo = pageable.getPageNumber();\n    int pageSize = pageable.getPageSize();\n    long requestCount = (pageNo - 1) * pageSize; \/\/ pageNo:10, pageSize:10 \uc77c \uacbd\uc6b0 requestCount=90\n\n    if (totalCount > requestCount) { \/\/ \uc2e4\uc81c \uc804\uccb4 \uac74\uc218\uac00 \ub354 \ub9ce\uc740 \uacbd\uc6b0\uc5d4 \uadf8\ub300\ub85c \ubc18\ud658\n        return pageable;\n    }\n\n    int requestPageNo = (int) Math.ceil((double)totalCount\/pageNo); \/\/ ex: 71~79\uc774\uba74 8\uc774 \ub418\uae30 \uc704\ud574\n    return PageRequest.of(requestPageNo, pageSize);\n\n}\n\uc5ec\uae30\uc11c exchangePageRequest() \uba54\uc18c\ub4dc\ub97c \uc880 \ub354 \uac1d\uccb4\uc9c0\ud5a5\uc801\uc73c\ub85c \ubd84\ub9ac\ud558\uae30 \uc704\ud574 \ubcc4\ub3c4\uc758 Dto \ud074\ub798\uc2a4\ub85c \ucd94\ucd9c\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.\n\npublic class FixedPageRequest extends PageRequest {\n\n    protected FixedPageRequest(Pageable pageable, long totalCount) {\n        super(getPageNo(pageable, totalCount), pageable.getPageSize(), pageable.getSort());\n    }\n\n    private static int getPageNo(Pageable pageable, long totalCount) {\n        int pageNo = pageable.getPageNumber();\n        int pageSize = pageable.getPageSize();\n        long requestCount = pageNo * pageSize; \/\/ pageNo:10, pageSize:10 \uc77c \uacbd\uc6b0 requestCount=90\n\n        if (totalCount > requestCount) { \/\/ \uc2e4\uc81c \uac74\uc218\uac00 \uc694\uccad\ud55c \ud398\uc774\uc9c0 \ubc88\ud638\ubcf4\ub2e4 \ub192\uc744 \uacbd\uc6b0\n            return pageNo;\n        }\n\n        return (int) Math.ceil((double)totalCount\/pageNo); \/\/ \uc2e4\uc81c \uac74\uc218\uac00 \ubd80\uc871\ud55c \uacbd\uc6b0 \uc694\uccad \ud398\uc774\uc9c0 \ubc88\ud638\ub97c \uac00\uc7a5 \ub192\uc740 \ubc88\ud638\ub85c \uad50\uccb4\n    }\n}\n\uc774\ub807\uac8c \ud560 \uacbd\uc6b0 Repository\ub294 \ub2e4\uc74c\ucc98\ub7fc \uac1c\uc120\ub429\ub2c8\ub2e4.\n\npublic Page&lt;BookPaginationDto> paginationCountSearchBtn2(boolean useSearchBtn, Pageable pageable, String name) {\n    JPAQuery&lt;BookPaginationDto> query = queryFactory\n            .select(Projections.fields(BookPaginationDto.class,\n                    book.id.as(\"bookId\"),\n                    book.name,\n                    book.bookNo,\n                    book.bookType\n            ))\n            .from(book)\n            .where(\n                    book.name.like(name + \"%\")\n            )\n            .orderBy(book.id.desc());\n\n    JPQLQuery&lt;BookPaginationDto> pagination = querydsl().applyPagination(pageable, query);\n\n    if(useSearchBtn) {\n        int fixedPageCount = 10 * pageable.getPageSize(); \/\/ 10\uac1c \ud398\uc774\uc9c0 \uace0\uc815\n        return new PageImpl&lt;>(pagination.fetch(), pageable, fixedPageCount);\n    }\n\n    long totalCount = pagination.fetchCount();\n    Pageable pageRequest = new FixedPageRequest(pageable, totalCount);\n    return new PageImpl&lt;>(querydsl().applyPagination(pageRequest, query).fetch(), pageRequest, totalCount);\n}\n\uc790 \uc774\ub807\uac8c \ub428\uc73c\ub85c\uc368 \"3. \uc804\uccb4 \uac74\uc218\ub97c \ucd08\uacfc\ud55c \ud398\uc774\uc9c0 \uc694\uccad\uc5d0\ub294 \ub9c8\uc9c0\ub9c9 \ud398\uc774\uc9c0 \uacb0\uacfc \ubc18\ud658\"\uc5d0 \ub300\ud574\uc11c\ub294 FixedPageRequest \ud074\ub798\uc2a4\uac00 \ub2f4\ub2f9\ud558\uac8c \ub418\uc5c8\uc73c\ub2c8 \ud14c\uc2a4\ud2b8 \ucf54\ub4dc \uc5ed\uc2dc \ubcc4\ub3c4\ub85c \uc9c4\ud589\ud560 \uc218 \uc788\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4.\n\n\uadf8\ub7fc \ubc14\ub85c \ud14c\uc2a4\ud2b8 \ucf54\ub4dc\ub97c \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n3-1-2. \ud14c\uc2a4\ud2b8 \ucf54\ub4dc\n\uba3c\uc800 \ud14c\uc2a4\ud2b8 \ud574\ubcfc \uac83\uc740 FixedPageRequest \ud074\ub798\uc2a4 \uc785\ub2c8\ub2e4.\n\uc55e\uc11c \uc124\uba85 \ub4dc\ub9b0\uac83\ucc98\ub7fc FixedPageRequest \ub294 \"\uc804\uccb4 \uac74\uc218\ub97c \ucd08\uacfc\ud55c \ud398\uc774\uc9c0 \ubc88\ud638 \uc694\uccad\uc5d0\ub294 \ub9c8\uc9c0\ub9c9 \ud398\uc774\uc9c0 \uc694\uccad\"\uc73c\ub85c \ubcc0\ud658\ud558\ub294 \uc5ed\ud560\uc744 \ud569\ub2c8\ub2e4.\n\n@ParameterizedTest\n@CsvSource({\n        \"10, 100, 10\", \/\/ (1)\n        \"10, 101, 10\", \/\/ (2)\n        \"10, 91, 10\", \/\/ (3)\n        \"10, 90, 9\", \/\/ (4)\n        \"10, 79, 8\"}) \/\/ (5)\nvoid dto_exchange_page_request(int pageNo, long totalCount, int expectedPageNo) throws Exception {\n    \/\/given\n    Pageable pageRequest = PageRequest.of(pageNo, 10);\n\n    \/\/when\n    Pageable result = new FixedPageRequest(pageRequest, totalCount);\n\n    \/\/then\n    assertThat(result.getPageNumber()).isEqualTo(expectedPageNo);\n}\n\uac80\uc99d \ucf00\uc774\uc2a4\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.\n\n(1) \ud398\uc774\uc9c0\ubc88\ud638:10 \/ \uc804\uccb4 \uac74\uc218: 100 \/ \ubcc0\ud658 \ud6c4 \ubc1b\uc740 \ud398\uc774\uc9c0 \ubc88\ud638: 10\n(2) \ud398\uc774\uc9c0\ubc88\ud638:10 \/ \uc804\uccb4 \uac74\uc218: 101 \/ \ubcc0\ud658 \ud6c4 \ubc1b\uc740 \ud398\uc774\uc9c0 \ubc88\ud638: 10\n(3) \ud398\uc774\uc9c0\ubc88\ud638:10 \/ \uc804\uccb4 \uac74\uc218: 91 \/ \ubcc0\ud658 \ud6c4 \ubc1b\uc740 \ud398\uc774\uc9c0 \ubc88\ud638: 10\n(4) \ud398\uc774\uc9c0\ubc88\ud638:10 \/ \uc804\uccb4 \uac74\uc218: 90 \/ \ubcc0\ud658 \ud6c4 \ubc1b\uc740 \ud398\uc774\uc9c0 \ubc88\ud638: 9\n(5) \ud398\uc774\uc9c0\ubc88\ud638:10 \/ \uc804\uccb4 \uac74\uc218: 79 \/ \ubcc0\ud658 \ud6c4 \ubc1b\uc740 \ud398\uc774\uc9c0 \ubc88\ud638: 8\n\n\ubc14\ub85c \ud14c\uc2a4\ud2b8\ub97c \ub3cc\ub824\ubcf4\uba74?\n\ndto-test-result\n\n\uc815\uc0c1\uc801\uc73c\ub85c \ud1b5\uacfc\ud558\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uc790 \uadf8\ub7fc Repository \ud14c\uc2a4\ud2b8\ub97c \ud574\ubcfc\ud150\ub370\uc694.\nRepository \ud14c\uc2a4\ud2b8\ub294 2\uac1c\ub85c \ub098\ub269\ub2c8\ub2e4.\n\n\uc2e4\uc81c \uac74\uc218\uc640 \ubb34\uad00\ud558\uac8c 10\uac1c \ud398\uc774\uc9c0\uc758 \uac1c\uc218\uac00 \ub9ac\ud134\ub418\ub294 \ucf00\uc774\uc2a4\n\n@Test\nvoid \uac80\uc0c9\ubc84\ud2bc\uc0ac\uc6a9\uc2dc_10\uac1c_\ud398\uc774\uc9c0_\uac74\uc218\uac00_\ub9ac\ud134\ub41c\ub2e4() throws Exception {\n    PageRequest pageRequest = PageRequest.of(1, 10);\n    boolean useSearchBtn = true;\n    Page&lt;BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountSearchBtn(useSearchBtn, pageRequest, prefixName);\n\n    \/\/then\n    assertThat(page.getTotalElements()).isEqualTo(100); \/\/ 10 (pageCount) * 10 (pageSize)\n}\nsearch_btn_result\n\n\uc2e4\uc81c \uac74\uc218\uac00 \ub9ac\ud134\ub418\ub294 \ucf00\uc774\uc2a4\n\n@Test\nvoid \ud398\uc774\uc9c0\ubc84\ud2bc\uc0ac\uc6a9\uc2dc_\uc2e4\uc81c_\ud398\uc774\uc9c0_\uac74\uc218\uac00_\ub9ac\ud134\ub41c\ub2e4() throws Exception {\n    PageRequest pageRequest = PageRequest.of(1, 10);\n    boolean useSearchBtn = false;\n    Page&lt;BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountSearchBtn(useSearchBtn, pageRequest, prefixName);\n\n    \/\/then\n    assertThat(page.getTotalElements()).isEqualTo(30);\n}\npage_btn_result\n\nRepository\uc758 \ud14c\uc2a4\ud2b8 \ucf54\ub4dc \uc5ed\uc2dc \uc815\uc0c1\uc801\uc73c\ub85c \uc218\ud589 \ub418\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n3-1-3. \uacb0\ub860\n\uc2e4\uc81c \uc81c\uac00 \uc9c4\ud589\ud588\ub358 \uba87\uba87 \ud504\ub85c\uc81d\ud2b8\uc5d0\uc11c\ub294 \uac80\uc0c9 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud558\ub294 \uacbd\uc6b0\uac00 \uac80\uc0c9\uc758 80%\ub97c \ub118\uae30\ub3c4 \ud588\ub294\ub370\uc694.\n\ud398\uc774\uc9c0 \ubc84\ud2bc\uc744 \ud074\ub9ad\ud558\ub294 \uacbd\uc6b0\uac00 \uc804\uccb4 \uac80\uc0c9\uc5d0\uc11c 20%\ub3c4 \uc548\ub418\ub294 \uc0c1\ud669\uc5d0\uc11c \ub9e4\ubc88 10\ucd08\ub300\uc758 \ucffc\ub9ac\uac00 \uc218\ud589\ub418\ub294 \uac83\uc740 \ubd80\ub2f4\uc2a4\ub7ec\uc6b4 \uc77c\uc785\ub2c8\ub2e4.\n\uc774\ubc88 \ubc29\ubc95\uc740 \uc774\ub7f0 \uacbd\uc6b0\uc5d0 \ub9ce\uc740 \ud6a8\uacfc\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>\uc9c0\ub09c \uc2dc\uac04\uc5d0 \uc774\uc5b4 count\uc640 \uad00\ub828\ub41c 2\ubc88\uc9f8 \uac1c\uc120 \ubc29\ubc95\uc740 \uccab \ubc88\uc9f8 \ucffc\ub9ac\uc758 \uacb0\uacfc\ub97c Cache\ud558\uae30 \uc778\ub370\uc694.\n\ubc29\ubc95\uc740 \uac04\ub2e8\ud569\ub2c8\ub2e4.\n\n\ucc98\uc74c \uac80\uc0c9\uc2dc \uc870\ud68c\ub41c count \uacb0\uacfc\ub97c \uc751\ub2f5\uacb0\uacfc\ub85c \ub0b4\ub824\uc8fc\uc5b4 JS\uc5d0\uc11c \uc774\ub97c \uce90\uc2f1\ud558\uace0, \ub9e4 \ud398\uc774\uc9d5 \ubc84\ud2bc\ub9c8\ub2e4 count \uacb0\uacfc\ub97c \ud568\uaed8 \ub0b4\ub824\uc8fc\ub294 \uac83\uc785\ub2c8\ub2e4.\n\uadf8\ub9ac\uace0 Repository\uc5d0\uc11c\ub294 \uc694\uccad\uc5d0 \ub118\uc5b4\uc628 \ud56d\ubaa9 \uc911, \uce90\uc2f1\ub41c count\uac12\uc774 \uc788\uc73c\uba74 \uc774\ub97c \uc7ac\uc0ac\uc6a9\ud558\uace0, \uc5c6\uc73c\uba74 count \ucffc\ub9ac\ub97c \uc218\ud589\ud569\ub2c8\ub2e4.\n\nquery_cache\n\uc774\ubbf8\uc9c0 \uc6d0\uc791\uc790\ub2d8\uaed8 \ud5c8\ub77d\uc744 \ubc1b\uace0 \uc0ac\uc6a9\ud558\uc600\uc2b5\ub2c8\ub2e4. :) (\ub2e4\uc2dc \ud55c\ubc88 \uac10\uc0ac\ub4dc\ub9bd\ub2c8\ub2e4!)\n\n\uc774 \ubc29\uc2dd\uc740 \ub2e4\uc74c\uacfc \uac19\uc740 \uc0c1\ud669\uc5d0\uc11c \ub3c4\uc6c0\uc774 \ub418\ub294\ub370\uc694.\n\n\uc870\ud68c \uc694\uccad\uc774 \uac80\uc0c9 \ubc84\ud2bc\uacfc \ud398\uc774\uc9c0 \ubc84\ud2bc \ubaa8\ub450\uc5d0\uc11c \uace8\uace0\ub8e8 \ubc1c\uc0dd\ud558\uace0\n\uc2e4\uc2dc\uac04\uc73c\ub85c \ub370\uc774\ud130 \uc801\uc7ac \ub418\uc9c0 \uc54a\uace0, \ub9c8\uac10\ub41c \ub370\uc774\ud130\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0\n\uc774\ub7f4 \uacbd\uc6b0\uc5d0 \uc0ac\uc6a9\ud558\uc2e0\ub2e4\uba74 \ub9e4 \ud398\uc774\uc9c0 \ubc84\ud2bc \ud074\ub9ad\uc2dc\ub9c8\ub2e4 \ubc1c\uc0dd\ud558\ub294 count \ucffc\ub9ac\ub97c \ucc98\uc74c 1\ud68c\ub9cc \ud638\ucd9c\ub418\uace0 \uc774\ud6c4\ubd80\ud130\ub294 \ud638\ucd9c\ub418\uc9c0 \uc54a\uc544 count \uc131\ub2a5\uc744 \ud5a5\uc0c1 \uc2dc\ud0ac \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\ubb3c\ub860 JS\uc5d0\uc11c \uce90\uc2f1\ud558\uace0 \uc788\uae30 \ub54c\ubb38\uc5d0 \ube0c\ub77c\uc6b0\uc800\ub97c \uc0c8\ub85c\uace0\uce68\ud558\uac8c \ub418\uba74 count\ub294 \ub2e4\uc2dc \ucd08\uae30\ud654\uac00 \ub418\uc5b4 \uc774\ud6c4 \uccab \uc870\ud68c\uc2dc \ub2e4\uc2dc \ucffc\ub9ac\uac00 \uc218\ud589\ub418\uac8c \ub429\ub2c8\ub2e4.\n\n\uc790 \uadf8\ub7fc \uc2e4\uc81c \ucf54\ub4dc\ub97c \ud55c\ubc88 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n3-2-1. \uad6c\ud604 \ucf54\ub4dc\n\uc9c0\ub09c \uc2dc\uac04\uc5d0 \uc774\uc5b4\uc11c \uc774\ubc88\uc5d0\ub3c4 \uc5ed\uc2dc \uae30\uc874 \ud398\uc774\uc9d5 \ucffc\ub9ac\ub294 \ub3d9\uc77c\ud569\ub2c8\ub2e4.\n\npublic Page&lt;BookPaginationDto> paginationCount(Pageable pageable, String name) {\n    JPQLQuery&lt;BookPaginationDto> query = querydsl().applyPagination(pageable,\n            queryFactory\n                    .select(Projections.fields(BookPaginationDto.class,\n                            book.id.as(\"bookId\"),\n                            book.name,\n                            book.bookNo,\n                            book.bookType\n                    ))\n                    .from(book)\n                    .where(\n                            book.name.like(name + \"%\")\n                    )\n                    .orderBy(book.id.desc()));\n\n    List&lt;BookPaginationDto> items = query.fetch(); \/\/ \ub370\uc774\ud130 \uc870\ud68c\n    long totalCount = query.fetchCount(); \/\/ \uc804\uccb4 count\n    return new PageImpl&lt;>(items, pageable, totalCount);\n}\n\nprivate Querydsl querydsl() {\n    return Objects.requireNonNull(getQuerydsl());\n}\n\uc774 \ucf54\ub4dc\ub97c \uac80\uc0c9\/\ud398\uc774\uc9d5 \ubc84\ud2bc \ud074\ub9ad\uc2dc\uc5d0 cache\ub41c count\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \uac1c\uc120\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub2e4\uc74c\uc758 \ucf54\ub4dc\uac00 \ucd94\uac00\ub418\uc5b4\uc57c \ud558\ub294\ub370\uc694.\n\n\ud504\ub860\ud2b8 \uc601\uc5ed\uc5d0\uc11c \ub118\uaca8\uc900 count\uac12\uc774 \uc694\uccad \ud544\ub4dc\uc5d0 \ud3ec\ud568\uc2dc\ud0a8\ub2e4.\nRepository\uc5d0\uc11c\ub294 \ud574\ub2f9 count \uac12\uc774 \uc788\uc744 \uacbd\uc6b0\uc5d4 \uadf8\ub300\ub85c \ud398\uc774\uc9d5 \uacb0\uacfc\uc5d0 \ud3ec\ud568\uc2dc\ud0a4\uace0, \uc5c6\uc73c\uba74 \uc2e4\uc81c count \ucffc\ub9ac\ub97c \uc2e4\ud589\ud55c\ub2e4.\n\uad49\uc7a5\ud788 \uc2ec\ud50c\ud55c \ub85c\uc9c1\uc774\uae30\uc5d0 \ubc14\ub85c \ud574\ub2f9 \ub85c\uc9c1\uc744 \uc801\uc6a9\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n\ubb3c\ub860 JS\uc601\uc5ed (\ud639\uc740 \ubaa8\ubc14\uc77c \uc571)\uc5d0\uc11c \uc751\ub2f5\uac1d\uccb4\uc758 count\uac12\uc744 \uc800\uc7a5\ud558\ub294 \uac83\ub3c4 \ucd94\uac00\ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4.\n\uc5ec\uae30\uc11c\ub294 Querydsl\uc5d0\uc11c \ud574\uc57c\ud558\ub294 \uac83\ub9cc \uc18c\uac1c\ub4dc\ub9bd\ub2c8\ub2e4.\n\n\/\/ (1) \npublic Page&lt;BookPaginationDto> paginationCountCache(Long cachedCount, Pageable pageable, String name) {\n    JPQLQuery&lt;BookPaginationDto> query = querydsl().applyPagination(pageable,\n            queryFactory\n                    .select(Projections.fields(BookPaginationDto.class,\n                            book.id.as(\"bookId\"),\n                            book.name,\n                            book.bookNo,\n                            book.bookType\n                    ))\n                    .from(book)\n                    .where(\n                            book.name.like(name + \"%\")\n                    )\n                    .orderBy(book.id.desc()));\n\n    List&lt;BookPaginationDto> elements = query.fetch(); \/\/ \ub370\uc774\ud130 \uc870\ud68c\n    long totalCount = cachedCount != null ? cachedCount : query.fetchCount(); \/\/ (2) \uc804\uccb4 count\n    return new PageImpl&lt;>(elements, pageable, totalCount);\n}\n\nprivate Querydsl querydsl() {\n    return Objects.requireNonNull(getQuerydsl());\n}\n(1) Long cachedCount\n\n\ud504\ub860\ud2b8 \uc601\uc5ed\uc5d0\uc11c \ub118\uaca8\uc900 count \uac12\uc785\ub2c8\ub2e4.\n(2) cachedCount != null ? cachedCount : query.fetchCount()\n\nRequest\ub85c \ub118\uc5b4\uc628 cachedCount\uac00 \uc788\uc73c\uba74, \ud574\ub2f9 \uac12\uc744, \uc5c6\uc73c\uba74 \uc2e4\uc81c count \ucffc\ub9ac\ub97c \uc218\ud589\uc2dc\ucf1c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.\n \n\n\uc790 \uadf8\ub7fc \ud55c\ubc88 \uc815\uc0c1\uc801\uc73c\ub85c \uc791\ub3d9\ud558\ub294\uc9c0 \ud14c\uc2a4\ud2b8 \ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n3-2-2. \ud14c\uc2a4\ud2b8 \ucf54\ub4dc\n\uba3c\uc800 cached\ub41c \uacbd\uc6b0\uc785\ub2c8\ub2e4.\n\n@BeforeEach\nvoid setUp() {\n    for (int i = 1; i &lt;= 30; i++) {\n        bookRepository.save(Book.builder()\n                .name(prefixName +i)\n                .bookNo(i)\n                .build());\n    }\n}\n\n@Test\nvoid cache\ub41c_count\ub97c_\uc0ac\uc6a9\ud55c\ub2e4() throws Exception {\n    PageRequest pageRequest = PageRequest.of(1, 10);\n    Long cachedCount = 100L;\n    Page&lt;BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountCache(cachedCount, pageRequest, prefixName);\n\n    \/\/then\n    assertThat(page.getTotalElements()).isEqualTo(cachedCount);\n}\n\uc704 \ud14c\uc2a4\ud2b8\ub97c \ub3cc\ub824\ubcf4\uba74?\n\ntest_1\n\uc815\uc0c1\uc801\uc73c\ub85c \uacb0\uacfc\uac00 \ubc18\ud658\ub418\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n\uc790 \uadf8\ub7fc 2\ubc88\uc9f8\ub85c cache\uac00 \uc5c6\uc744 \uacbd\uc6b0 \uc2e4\uc81c count \uac12\uc744 \uc0ac\uc6a9\ud558\ub294 \uacbd\uc6b0\ub97c \ud14c\uc2a4\ud2b8\ud574\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.\n\n@BeforeEach\nvoid setUp() {\n    for (int i = 1; i &lt;= 30; i++) {\n        bookRepository.save(Book.builder()\n                .name(prefixName +i)\n                .bookNo(i)\n                .build());\n    }\n}\n\n@Test\nvoid cache\ub41c_count\ub97c_\uc0ac\uc6a9\ud55c\ub2e4() throws Exception {\n    PageRequest pageRequest = PageRequest.of(1, 10);\n    Long cachedCount = 100L;\n    Page&lt;BookPaginationDto> page = bookPaginationRepositorySupport.paginationCountCache(cachedCount, pageRequest, prefixName);\n\n    \/\/then\n    assertThat(page.getTotalElements()).isEqualTo(cachedCount);\n}\n\uc704 \ud14c\uc2a4\ud2b8\ub97c \uc218\ud589\ud574\ubcf4\uba74?\n\ntest_2\n\uc6d0\ud558\ub358\ub300\ub85c \uc2e4\uc81c count\uac00 \ubc18\ud658\ub418\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n\n3-2-3. \uacb0\ub860\n\ud55c\ubc88 \uc870\ud68c\ub41c \ub3d9\uc77c \uc870\uac74\uc758 count\uc5d0 \ub300\ud574\uc11c\ub294 \ud074\ub77c\uc774\uc5b8\ud2b8 \uc601\uc5ed\uc5d0\uc11c \uc800\uc7a5\ud6c4 \uc694\uccad\uc2dc\ub9c8\ub2e4 \uc7ac\uc0ac\uc6a9\ud558\ub294 \ubc29\uc2dd\uc744 \uc0ac\uc6a9\ud558\uac8c \ub418\uba74 \ucd94\uac00\uc801\uc778 \ucffc\ub9ac \uc694\uccad\uc774 \ucd5c\uc18c\ud654 \ub41c\ub2e4\ub294 \uc7a5\uc810\uc774 \uc788\uc2b5\ub2c8\ub2e4.\n\ubc18\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \ub2e8\uc810\ub3c4 \uc788\ub294\ub370\uc694.\n\n\uccab \ud398\uc774\uc9c0 \uc870\ud68c\uac00 \ub300\ubd80\ubd84\uc77c \uacbd\uc6b0 \ud6a8\uacfc\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.\n\ucd94\uac00\uc801\uc778 \ud398\uc774\uc9d5 \uc870\ud68c\uac00 \ud544\uc694\ud558\uc9c0 \uc54a\uc73c\uba74 \uacb0\uad6d \ub9e4\ubc88 \uccab \uc870\ud68c\ub77c\uc11c cache count\ub97c \uc0ac\uc6a9\ud560 \uc218\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.\n\uc2e4\uc2dc\uac04\uc73c\ub85c \ub370\uc774\ud130 \uc218\uc815\uc774 \ud544\uc694\ud574 \ud398\uc774\uc9c0 \ubc84\ud2bc \ubc18\uc601\uc774 \ud544\uc694\ud55c \uacbd\uc6b0 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.\n\uacb0\uad6d \uc0c8\ub85c\uace0\uce68 (or \ubc84\ud2bc \ud074\ub9ad\uc744 \ud1b5\ud55c \ud398\uc774\uc9c0 \uc774\ub3d9) \ud558\uae30 \uc804\uae4c\uc9c0\ub294 \ud398\uc774\uc9c0 \ubc84\ud2bc\ub4e4\uc774 \uacc4\uc18d \uadf8\ub300\ub85c \uc720\uc9c0 \ub418\uae30 \ub54c\ubb38\uc5d0 \uc2e4\uc2dc\uac04\uc131\uc774 \ub5a8\uc5b4\uc9d1\ub2c8\ub2e4.\n\ub9c8\uac10\ub41c \ub370\uc774\ud130 \ud639\uc740 \uc2e4\uc2dc\uac04\uc744 \uc720\uc9c0\ud560 \ud544\uc694 \uc5c6\uc744 \uacbd\uc6b0\uc5d0\ub9cc \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>* Mysql query cache hit ratio \uacc4\uc0b0\ud558\uae30 ( \uadf8\ub7f0\ub370 5.7 \ubd80\ud130 \uc0ac\uc6a9 \uc548\ud55c\ub2e4.. \u3160\u3160 )\n\ucffc\ub9ac\uce90\uc2dc \ud788\ud2b8\uc728\uc740 \ub2e4\uc74c\uc758 \uacf5\uc2dd\uc73c\ub85c \uad6c\ud560 \uc218 \uc788\ub2e4. \n\n\n\n\n\n\n\nQuery Cache Hit Rate = Qcache_hits \/ ( Qcache_hits + Com_select ) * 100\n\n\n\n\n\n\n\n\n\nQcache_his \ub294\ucffc\ub9ac \uce90\uc2dc\ub85c \ucc98\ub9ac\ub41c SELECT \ucffc\ub9ac\uc758 \uc218 \uc774\uace0, Com_select\ub294 \uce90\uc2dc\uc5d0\uc11c \uacb0\uacfc\ub97c \ucc3e\uc9c0 \ubabb\ud558\uc5ec MySQL \uc11c\ubc84\uac00 \ucffc\ub9ac\ub97c \uc2e4\ud589\ud55c \ud69f\uc218\ub97c \uc758\ubbf8\ud55c\ub2e4.\n\n\n\n\n\n\n\n\uadf8\ub807\ub2e4\uba74 Qcache_hits \uc640 Com_select \ub97c \uc5b4\ub5bb\uac8c \uad6c\ud558\ub294\uc9c0 \uc54c\uc544\ubcf4\uc790\n\n\n\n\n\n\n\nshow variables like \u2018query_%\u2019;\n\n\n\n\ub2e4\uc74c \uc0ac\uc9c4\uc5d0\uc11c query_cache_limit\uac00 Qcache_hits \uc774\ub2e4.\n\n\n\n\n\n\n\n\uadf8 \ub2e4\uc74c Com_select\ub97c \uad6c\ud558\uae30 \uc704\ud574 \ub2e4\uc74c \uba85\ub839\uc5b4\ub97c \uc791\uc131\ud558\uc790.\n\n\n\nshow global status like \u2018Com_select\u2019\n\n\n\n\n\n\n\n\n\n\uc774\uc81c \uacf5\uc2dd\uc73c\ub85c \uacc4\uc0b0\ud574\ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc218\uce58\ub97c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.\n\n\n\n1048576\/ ( 1048576 + 18 ) * 100 = 99.99..<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql) \ucffc\ub9ac\uc758 \uc9c4\ud589 \uacfc\uc815, \uc9c4\ud589 \uc2dc\uac04\uc744 \ud655\uc778\ud558\uace0 \uc2f6\uc744 \ub54c\n\uba85\ub839\uc5b4\ub97c \ud655\uc778\ud558\uae30 \ud558\uae30\uc804\uc5d0 \uc124\uc815\uc744 \ubc14\uafd4\uc918\uc57c\ud55c\ub2e4. \n\n\n\nmysql> set profiling=1; \n\n\n\n\n\nmysql> SET PROFILING_HISTORY_SIZE=30;\n\n\n\n\n\n\uc124\uc815\uc744 \ubc14\uafb8\uc5c8\ub2e4\uba74 \uc9c4\ud589\ub2e8\uacc4 \ub610\ub294 \uc9c4\ud589 \uc2dc\uac04\uc744 \ud655\uc778\ud558\uace0 \uc2f6\uc740 \ucffc\ub9ac\ub97c \uc791\uc131\ud55c\ub2e4.\n\n\n\nmysql> select * from \ud14c\uc774\ube14\uba85\n\n\n\n\ub9c8\uc9c0\ub9c9\uc73c\ub85c \ub2e4\uc74c \uba85\ub839\uc5b4\ub97c \uc785\ub825\ud558\uba74 \ub2e4\uc74c \uc0ac\uc9c4\uacfc \uac19\uc774 \uc790\uc2e0\uc774 \uc791\uc131\ud55c \ucffc\ub9ac\ubb38\uacfc \ucffc\ub9ac\ubb38\uc774 \uc9c4\ud589\ub41c \uc2dc\uac04\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.\n\n\n\nshow profiles; \n\n\n\n\n\n\n\n\n\n\n\n\n\n\uc791\uc131\ub41c \ucffc\ub9ac\uc758 \uc9c4\ud589 \uacfc\uc815\uc744 \ud655\uc778\ud558\uace0 \uc2f6\uc73c\uba74, \ub2e4\uc74c \uba85\ub839\uc5b4\ub97c \uc791\uc131\ud558\uc790\n\n\n\nshow profile for query 3(\uc9c4\ud589\uacfc\uc815\uc744 \ubcf4\uace0\uc2f6\uc740 \ucffc\ub9ac\uc758 Query_ID);\n\n\n\n\n\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Table\uc5d0 \uc0dd\uc131\ub418\uc5b4 \uc788\ub294 Indexes \uc758 \uc6a9\ub7c9 \ud655\uc778 Table \ucd5c\uace0 \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95 &nbsp;SHOW TABLE STATUS FROM&nbsp;DB name&nbsp;LIKE&nbsp;&#8216;Table name&#8217;; \uc774 \uba85\ub839\uc5b4 \uc774\ud6c4 Create_options\uc5d0 \uc788\ub294 max_rows\uc640 avg_row_length\ub97c \uacf1\ud55c \uac12\uc774 \uc6a9\ub7c9\uc774 \ub41c\ub2e4. MB \ub2e8\uc704\ub85c \ud658\uc0b0\ud558\ub824\uba74 1024\ub85c \ub450\ubc88 \ub098\ub204\uc5b4 \uc8fc\uba74 \ub41c\ub2e4.&nbsp; DB \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95 &nbsp;SELECT&nbsp;count(*) NUM_OF_TABLE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; table_schema,concat(round(sum(table_rows)\/1000000,2),&#8217;M&#8217;) rows,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(data_length)\/(1024*1024*1024),2),&#8217;G&#8217;) DATA,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(index_length)\/(1024*1024*1024),2),&#8217;G&#8217;) idx,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; concat(round(sum(data_length+index_length)\/(1024*1024*1024),2),&#8217;G&#8217;) total_size,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; round(sum(index_length)\/sum(data_length),2) idxfrac&nbsp;FROM information_schema.TABLES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY table_schema&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY sum(data_length+index_length) DESC LIMIT 10; \uac01 \ud14c\uc774\ube14 \uc0ac\uc774\uc988 \ud655\uc778 \ubc29\ubc95 &nbsp;SELECT&nbsp;table_name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;table_rows,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;round(data_length\/(1024*1024),2)&nbsp;as&nbsp;&#8216;DATA_SIZE(MB)&#8217;,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;round(index_length\/(1024*1024),2)&nbsp;as&nbsp;&#8216;INDEX_SIZE(MB)&#8217;&nbsp;FROM&nbsp;information_schema.TABLES&nbsp;&nbsp;WHERE&nbsp;table_schema =&nbsp;&#8216;DB_name&#8217;&nbsp;GROUP&nbsp;BY&nbsp;table_name&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;data_length&nbsp;DESC&nbsp;LIMIT&nbsp;10; \ud14c\uc774\ube14\uc5d0 \ube48 \uacf5\uac04\uc774 \ub9ce\ub2e4\uba74.. ( data_free )optimize table \uc744 \uc2dc\ud589 \ud574\uc8fc\uba74 \uc88b\uc740\ub370 \uc774\ub54c Indexes\ub3c4 rebuild\ud558\uae30 \ub54c\ubb38\uc5d0 indexes\ub97c \uba3c\uc800 drop\ud574\uc900\ub2e4\uc74c\uc5d0 optimize table\uc744 \ud558\uace0 create indexes\ub97c \ud574\uc8fc\ub294\uac8c \ucd1d \uc2dc\uac04\uba74\uc5d0\uc11c \uc774\ub4dd\uc774 \uc788\uc74c. show indexes from \ud14c\uc774\ube14\uba85\uc5d0\uc11c \ub098\uc628 \uacb0\uacfc\uc5d0\uc11c \uc778\ub371\uc2a4 \uceec\ub7fc\ubcc4 cardinality\ub97c \uc8fc\uc758 \uae4a\uac8c \ubcf4\uc790.. \uc774 \uac12\uc774 \ud074\uc218\ub85d \ub2e4\ub978 \uac12\uc774 \ub9ce\uc740\uac70\uace0 \uc791\uc744 \uc218\ub85d \ub2e4\ub978 \uac12\uc774 \uc801\uc740\uac70\ub2e4.. \uadf8\ub798\uc11c \uc774 \uac12\uc774 \ud070 \uac78 \uc704\uc8fc\ub85c ( \ubcf5\ud569\ud0a4\uc5d0\uc11c\ub294 \ud070\uac70 \ubd80\ud130 \uc791\uc740\uac70 \uc21c\uc73c\ub85c ) \uc778\ub371\uc2a4\ub97c \ub9cc\ub4e4\uc5b4\uc57c\uc9c0 \uc18d\ub3c4\uc5d0 \uc774\ub4dd\uc774 \uc788\ub2e4.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[12,5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-kI","jetpack-related-posts":[{"id":801,"url":"https:\/\/blog.box.kr\/?p=801","url_meta":{"origin":1284,"position":0},"title":"[scrap] MySQL Replication \uc124\uc815\uacfc \uba87 \uac00\uc9c0 \ud14c\uc2a4\ud2b8","date":"2015-05-18","format":false,"excerpt":"http:\/\/blog.hibrainapps.net\/130 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MySQL\uc744 DB\ub85c \uc0ac\uc6a9\ud558\uba74\uc11c \uc11c\ubc84\uc758 \ubd80\ud558 \ubd84\uc0b0\uc744 \uc704\ud55c \ubc29\ubc95 \uc911 \ud558\ub098\ub85c Replication \uc744 \uc0ac\uc6a9\ud55c\ub2e4. Replication \uc740 Master \ud558\ub098\uc5d0 n\uac1c\uc758 Slave\ub85c \uc9c0\uc815\uc774 \uac00\ub2a5\ud558\ub2e4. Slave\ub294 \ub2e4\uc2dc Master \uc5ed\ud560\uc744 \ud560\uc218 \uc788\uc73c\uba70 \uc5ed\uc2dc \ub610 \ub2e4\ub978 n\uac1c\uc758 Slave\ub97c \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. \ubd80\ud558 \ubd84\uc0b0\uc758 \ud6a8\uacfc\ub294 inser,update \ub4f1 \ubcc0\uacbd\uacfc \uad00\ub828\ub41c \ubaa8\ub4e0 \uc791\uc5c5\uc740\u2026","rel":"","context":"In &quot;DB\uad00\ub828&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":653,"url":"https:\/\/blog.box.kr\/?p=653","url_meta":{"origin":1284,"position":1},"title":"[\ud38c]Too many connection \ubb38\uc81c \ud574\uacb0","date":"2015-04-05","format":false,"excerpt":"http:\/\/netholic.tistory.com\/116 \u00a0 \u00a0 \uc774\ubc88\ud574 \ucd08\ubd80\ud130 \uc2dc\uc791\ub41c too many connection \ubb38\uc81c\uac00 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529 mysql \uc744 \ubed7\uac8c \ub9cc\ub4e4\uc5c8\ub2e4. \ub418\ub294\ub300\ub85c... \uc544\ubb34\uac83\ub3c4 \ubaa8\ub978\ucc44\ub85c \ub9cc\ub4e0 \uc6b4\uc601\ud234\uc774 \uadf8 \uc6d0\uc778... \ucffc\ub9ac\ub3c4 10\ucd08 \uc774\uc0c1 \uac78\ub9ac\ub294 \uac83\ub3c4 \uc874\uc7ac\ud588\uace0..\uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uc548\uac78\ub824\uc788\uace0.. \uadf8\ub798\uc11c \uc2ac\ub85c\uc6b0 \ucffc\ub9ac\ub97c \uc7a1\uace0 \uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uac78\uc5b4\uc11c...\uadf8\ub7f0 \ucffc\ub9ac\ubb38\uc81c\ub97c \uc5c6\uc570\uc73c\ub85c\uc11c \ucee4\ub125\uc158 \ubb38\uc81c\ub294 \uc5c6\uc5b4\uc9c8 \uc904 \uc54c\uc558\ub2e4. \uadf8\ub7f0\ub370 \uc5ec\uc804\ud788 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529...\uc11c\ubc84\ub97c \ub0b4\ub838\ub2e4\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":649,"url":"https:\/\/blog.box.kr\/?p=649","url_meta":{"origin":1284,"position":2},"title":"[\ud38c]Tomcat7 tomcat-jdbc \uc124\uc815 &#8211; Broken pipe \uc5d0\ub7ec \ud68c\ud53c","date":"2015-04-05","format":false,"excerpt":"tomcat-jdbc\ub85c MySQL\uc5d0 \uc5f0\uacb0\ud55c \uacbd\uc6b0 \"java.net.SocketException: Broken pipe\" \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\ub97c \uac00\ub054 \ubcfc \uc218 \uc788\ub2e4. \ub300\ubd80\ubd84\uc758 \uacbd\uc6b0 connection idle time\uc774 MySQL\uc5d0 \uc124\uc815\ub41c wait_timeout\uc744 \uc9c0\ub098\uc11c DB\uc5d0\uc11c \uc5f0\uacb0\uc744 \ub04a\uc740 \uac83\uc774\ub2e4. \uc774\ub97c \ubc29\uc9c0\ud558\ub824\uba74 connection\uc774 \uc77c\uc815 \uae30\uac04\ub3d9\uc548 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc73c\uba74 close\ub418\ub3c4\ub85d \uc124\uc815\ud558\uac70\ub098 connection \ub300\uc5ec\uc2dc\uc5d0 connection\uc744 \uccb4\ud06c\ud558\ub3c4\ub85d \ud558\uba74 \ub41c\ub2e4. \uc124\uc815 1 - \uc77c\uc815 \uae30\uac04\ub3d9\uc548 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc73c\uba74 connection\uc744\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":444,"url":"https:\/\/blog.box.kr\/?p=444","url_meta":{"origin":1284,"position":3},"title":"[\ud38c]JMX\ub97c \uc774\uc6a9\ud558\uc5ec JVM \ubaa8\ub2c8\ud130\ub9c1 \ud558\uae30","date":"2014-12-18","format":false,"excerpt":"JMX\ub97c \uc774\uc6a9\ud558\uc5ec JVM \ubaa8\ub2c8\ud130\ub9c1 \ud558\uae30 JDK 1.5 \ubd80\ud130 JVM\uc5d0\ub294 Platform MBeanServer\uac00 \ub0b4\uc7a5\ub418\uc5b4 \uc788\uc5b4 jvm \ud30c\ub77c\ubbf8\ud130\ub85c\u00a0 Platform MBeanServer\uac00 \ub3d9\uc791\ud558\ub3c4\ub85d\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ubaa8\ub2c8\ud130\ub9c1 \ub300\uc0c1 \ud504\ub85c\uadf8\ub7a8 \uad6c\ub3d9\uc2dc \ub2e4\uc74c\uacfc \uac19\uc740 jvm \ud30c\ub77c\ubbf8\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec \uc2e4\ud589\ud569\ub2c8\ub2e4. \u00a0 jvm \ud30c\ub77c\ubbf8\ud130: \u00a0 -Dcom.sun.management.jmxremote.port=9999 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false \uc608\uc81c \uc18c\uc2a4 : \u00a0 import javax.management.MBeanServerConnection; import javax.management.ObjectName; import javax.management.remote.JMXConnector; import javax.management.remote.JMXConnectorFactory; import\u2026","rel":"","context":"In &quot;jboss&amp;tomcat&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":859,"url":"https:\/\/blog.box.kr\/?p=859","url_meta":{"origin":1284,"position":4},"title":"[scrap]\ub9ac\ub205\uc2a4 \uba54\ubaa8\ub9ac \uc0ac\uc6a9\ub7c9 \ud655\uc778","date":"2015-05-21","format":false,"excerpt":"http:\/\/solarixer.blogspot.kr\/2010\/10\/blog-post_08.html \u00a0 \ub9ac\ub205\uc2a4\uc5d0\uc11c \uba54\ubaa8\ub9ac \uc0ac\uc6a9\ub7c9 \uce21\uc815\uc744 \uc704\ud55c \ubc29\ubc95\uc744 \uc18c\uac1c \ub4dc\ub9bd\ub2c8\ub2e4. \uc544\ub798 3\uac00\uc9c0 \uc678\uc5d0\ub3c4 \uc5ec\ub7ec \ud234\uc774\ub098 \uc2dc\uc2a4\ud15c \ucee4\ub9e8\ub4dc\uac00 \uc788\uaca0\uc9c0\ub9cc, \ube60\ub974\uace0 \uc815\ud655\ud558\uac8c \uc811\uadfc\ud558\uae30 \uc704\ud55c \ubc29\ubc95\uc744 \uc54c\uc544\ubcf4\ub294 \uac83\uc744 \ubaa9\uc801\uc73c\ub85c\u00a0\ud569\ub2c8\ub2e4. 1)\u00a0\u00a0\u00a0\u00a0 \/proc\/PID\/status \ub97c \ubcf4\uace0 VmSize \ub97c \ud1b5\ud574 \ud574\ub2f9 \ud504\ub85c\uc138\uc2a4\uc5d0 \ud560\ub2f9\ub41c \uba54\ubaa8\ub9ac \uc6a9\ub7c9\uc744 \ud655\uc778 \ud558\ub294 \ubc29\ubc95\uc774 \uc788\uc2b5\ub2c8\ub2e4. (\uc5ec\ub7ec \uac1c\uc758 \ud504\ub85c\uc138\uc2a4\ub97c \ub3d9\uc2dc\uc5d0 \ubcf4\uae30\ub294 \uc870\uae08 \ubd88\ud3b8\ud55c \uac10\uc774\u2026","rel":"","context":"In &quot;\uae30\uc220&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":379,"url":"https:\/\/blog.box.kr\/?p=379","url_meta":{"origin":1284,"position":5},"title":"[\ud38c]\ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js","date":"2014-09-15","format":false,"excerpt":"http:\/\/bcho.tistory.com\/889 \ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js - #6 MongoDB \uc5f0\ub3d9 (mongo-native) \ud074\ub77c\uc6b0\ub4dc \ucef4\ud4e8\ud305 & NoSQL\/Vert.x & Node.js\u00a0|\u00a02014\/04\/03 23:49\u00a0|\u00a0Posted by\u00a0\uc870\ub300\ud611 \ube60\ub974\uac8c \ud6dd\uc5b4\ubcf4\ub294\u00a0node.js #6- mongo-native \ubaa8\ub4c8\uc744 \uc774\uc6a9\ud55c MongoDB \uc5f0\ub3d9 \uc870\ub300\ud611\u00a0(http:\/\/bcho.tistory.com Persistence\u00a0\uc5f0\ub3d9 node.js\ub294\u00a0DB\ub098\u00a0NoSQL\ub4f1\uc758 \uc5f0\ub3d9\uc744 \uc9c0\uc6d0\ud558\ub294\ub370,\u00a0\uc774 \uc5ed\uc2dc \ucca0\uc800\ud558\uac8c\u00a0non-blocking io\u00a0\ubc29\uc2dd\uc73c\ub85c \ub3d9\uc791\ud55c\ub2e4.\u00a0\uc989\u00a0db\u00a0\uc5f0\uacb0\u00a0socket\uc744 \uc5f4\uc5b4\uc11c\u00a0query\ub97c \ub358\uc838\ub193\uace0, query\u00a0\uacb0\uacfc\uac00 \uc624\uba74 \uc774\ubca4\ud2b8\ub97c \ubc1b\uc544\uc11c\u00a0callback\u00a0\ud568\uc218\ub85c \ucc98\ub9ac\ud558\ub294 \uc21c\uc11c\uc774\ub2e4. \uadf8\ub7ec\uba74 \uc5ec\uae30\uc11c\ub294 \uba87\uac00\uc9c0\u00a0persistence\u00a0\uc5f0\ub3d9 \ubc29\uc2dd\uc5d0 \ub300\ud574\uc11c \uc54c\uc544\ubcf4\ub3c4\ub85d \ud55c\ub2e4.\u2026","rel":"","context":"In &quot;\ucc38\uace0\ub97c \uc704\ud55c \uc800\uc7a5\ubb3c&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1284"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1284"}],"version-history":[{"count":3,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1284\/revisions"}],"predecessor-version":[{"id":1289,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1284\/revisions\/1289"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}