{"id":25026,"date":"2018-06-19T15:00:00","date_gmt":"2018-06-19T06:00:00","guid":{"rendered":"https:\/\/jirak.net\/wp\/mysql-ascending-index-vs-descending-index\/"},"modified":"2018-06-19T16:34:36","modified_gmt":"2018-06-19T07:34:36","slug":"mysql-ascending-index-vs-descending-index","status":"publish","type":"post","link":"https:\/\/jirak.net\/wp\/mysql-ascending-index-vs-descending-index\/","title":{"rendered":"MySQL Ascending index vs Descending index"},"content":{"rendered":"<p>MySQL Ascending index vs Descending index<\/p>\n<h1 id=\"\uc6a9\uc5b4-\uc815\ub9ac\">\uc6a9\uc5b4 \uc815\ub9ac<\/h1>\n<p>\uc774 \uc124\uba85\uc5d0\uc11c\ub294 \uc778\ub371\uc2a4\uc758 \uc815\ub82c \uc21c\uc11c\uc640 \ub370\uc774\ud130 \uc77d\uae30 \uc21c\uc11c \ub4f1 \ubc29\ud5a5\uc5d0 \ub300\ud55c \ub2e8\uc5b4\ub4e4\uc774 \ud63c\uc7ac\ud558\uba74\uc11c, \uc5ec\ub7ec \uac00\uc9c0 \ud63c\ub780\uc744 \ucd08\ub798\ud558\uae30 \uc26c\uc6b4 \uc124\uba85\ub4e4\uc774 \uc788\uc744 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4. \uadf8\ub798\uc11c \uc6b0\uc120 \ud45c\uc900 \uc6a9\uc5b4\ub294 \uc544\ub2c8\uc9c0\ub9cc, \ub098\ub984\ub300\ub85c \uba87 \uac1c \ub2e8\uc5b4\ub4e4\uc5d0 \ub300\ud574\uc11c \uac1c\ub150\uc744 \uc815\ub9bd\ud558\uace0 \uadf8 \ub2e8\uc5b4\ub97c \ubc88\uc5ed \uc5c6\uc774 \uc601\uc5b4\ub85c \uadf8\ub300\ub85c \ud45c\uae30\ud558\ub3c4\ub85d \ud558\uaca0\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/Terms.png\" alt=\"\uc6a9\uc5b4 \uc124\uba85\" \/><\/p>\n<ul>\n<li><code class=\"highlighter-rouge\">Ascending index<\/code> : \uc791\uc740 \uac12\uc758 \uc778\ub371\uc2a4 \ud0a4\uac00 B-Tree\uc758 \uc67c\ucabd\uc73c\ub85c \uc815\ub82c\ub41c \uc778\ub371\uc2a4<\/li>\n<li><code class=\"highlighter-rouge\">Descending index<\/code> : \ud070 \uac12\uc758 \uc778\ub371\uc2a4 \ud0a4\uac00 B-Tree\uc758 \uc67c\ucabd\uc73c\ub85c \uc815\ub82c\ub41c \uc778\ub371\uc2a4<\/li>\n<li><code class=\"highlighter-rouge\">Forward index scan<\/code> (Forward scan) : \uc778\ub371\uc2a4 \ud0a4\uc758 \ud06c\uace0 \uc791\uc74c\uc5d0 \uad00\uacc4\uc5c6\uc774 \uc778\ub371\uc2a4 \ub9ac\ud504 \ub178\ub4dc\uc758 \uc67c\ucabd \ud398\uc774\uc9c0\ubd80\ud130 \uc624\ub978\ucabd\uc73c\ub85c \uc2a4\uce94<\/li>\n<li><code class=\"highlighter-rouge\">Backward index scan<\/code> (Backward scan) : \uc778\ub371\uc2a4 \ud0a4\uc758 \ud06c\uace0 \uc791\uc74c\uc5d0 \uad00\uacc4\uc5c6\uc774 \uc778\ub371\uc2a4 \ub9ac\ud504 \ub178\ub4dc\uc758 \uc624\ub978\ucabd \ud398\uc774\uc9c0\ubd80\ud130 \uc67c\ucabd\uc73c\ub85c \uc2a4\uce94<\/li>\n<\/ul>\n<h1 id=\"descending-index-\uc9c0\uc6d0\">Descending index \uc9c0\uc6d0<\/h1>\n<p>MySQL 4.x \ubc84\uc804\ubd80\ud130 <a href=\"https:\/\/bugs.mysql.com\/bug.php?id=13375\">Feature Request<\/a>\ub85c \ub4f1\ub85d\ub418\uc5b4 \uc788\ub358 \u201c<code class=\"highlighter-rouge\">Descending index<\/code>\u201d \uae30\ub2a5\uc774 \ub4dc\ub514\uc5b4 MySQL 8.0\uc5d0 \ub3c4\uc785\ub418\uc5c8\ub2e4.<br \/>\nMySQL 8.0\ubd80\ud130\ub294 \uc774\uc81c \uc544\ub798\uc640 \uac19\uc774 \uc5ed\uc21c\uc73c\ub85c \uc815\ub82c\ub418\ub294 \uc778\ub371\uc2a4(<code class=\"highlighter-rouge\">Descending index<\/code>)\ub97c \uc0dd\uc131\ud560 \uc218 \uc788\uac8c \ub418\uc5c8\uc73c\uba70, \ud544\uc694\uc5d0 \ub530\ub77c\uc11c \uc801\uc808\ud788 \uc815\uc21c(ORDER BY ASC)\uacfc \uc5ed\uc21c(ORDER BY DESC)\uc744 \ud63c\ud569\ud574\uc11c \uc815\ub82c\ud558\ub294 \uc791\uc5c5\uc744 \uc778\ub371\uc2a4\ub97c \uc774\uc6a9\ud560 \uc218 \uc788\uac8c \ub41c \uac83\uc774\ub2e4.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"k\">TABLE<\/span> <span class=\"n\">tb_wow<\/span> <span class=\"p\">(<\/span>\n  <span class=\"n\">uid<\/span> <span class=\"n\">BIGINT<\/span> <span class=\"k\">PRIMARY<\/span> <span class=\"k\">KEY<\/span><span class=\"p\">,<\/span>\n  <span class=\"n\">age<\/span> <span class=\"n\">SMALLINT<\/span><span class=\"p\">,<\/span>\n  <span class=\"n\">score<\/span> <span class=\"n\">SMALLINT<\/span><span class=\"p\">,<\/span>\n  <span class=\"k\">INDEX<\/span> <span class=\"n\">ix_score_age<\/span> <span class=\"p\">(<\/span><span class=\"n\">score<\/span> <span class=\"k\">DESC<\/span><span class=\"p\">,<\/span> <span class=\"n\">age<\/span> <span class=\"k\">ASC<\/span><span class=\"p\">)<\/span>\n<span class=\"p\">);<\/span>\n\n<span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">tb_wow<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">score<\/span> <span class=\"k\">ASC<\/span><span class=\"p\">,<\/span>  <span class=\"n\">age<\/span> <span class=\"k\">DESC<\/span><span class=\"p\">;<\/span>\n<span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">tb_wow<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">score<\/span> <span class=\"k\">DESC<\/span><span class=\"p\">,<\/span> <span class=\"n\">age<\/span> <span class=\"k\">ASC<\/span> <span class=\"p\">;<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc544\ub9c8\ub3c4 MySQL 8.0 \uc774\uc804\uc5d0\ub3c4 <code class=\"highlighter-rouge\">Descending index<\/code>\uac00 \uc9c0\uc6d0\ub418\uc5c8\ub2e4\uace0 \uc0dd\uac01\ud588\uc744 \uc218\ub3c4 \uc788\ub294\ub370, MySQL 8.0 \uc774\uc804\uc5d0\ub294 \ubb38\ubc95\ub9cc \uc9c0\uc6d0\ub418\uace0 \uc2e4\uc81c <code class=\"highlighter-rouge\">Descending index<\/code>\uac00 \uc9c0\uc6d0\ub418\ub294 \uac83\uc740 \uc544\ub2c8\uc5c8\ub2e4. \ub610\ud55c <code class=\"highlighter-rouge\">Ascending index<\/code>\ub97c <code class=\"highlighter-rouge\">Forward scan<\/code>\ud558\ub294 \uac83\uacfc <code class=\"highlighter-rouge\">Backward scan<\/code>\ud558\ub294 \uac83\ub9cc\uc73c\ub85c <code class=\"highlighter-rouge\">Descending index<\/code>\uc758 \uc694\uac74\uc744 \ucda9\ubd84\ud788 \ub9cc\uc871\ud55c\ub2e4\uace0 \uc0dd\uac01\ud560 \uc218\ub3c4 \uc788\uc9c0\ub9cc, \uc2e4\uc81c \uadf8\ub807\uc9c0 \ubabb\ud55c \uacbd\uc6b0\ub3c4 \ub9ce\ub2e4.<\/p>\n<p>MySQL 8.0\uc5d0 \ub3c4\uc785\ub41c <code class=\"highlighter-rouge\">Descending index<\/code>\uac00 \ud544\uc694\ud55c \uac00\uc7a5 \ud070 \uc774\uc720\ub294 \uc774\ubbf8 \uc0b4\ud3b4\ubcf8 \uc608\uc81c\uc640 \uac19\uc774 \uc815\uc21c(ORDER BY ASC)\uacfc \uc5ed\uc21c(ORDER BY DESC) \uc815\ub82c\uc744 \uc11e\uc5b4\uc11c \uc5ec\ub7ec \uceec\ub7fc\uc73c\ub85c \uc815\ub82c\ud558\ub294 \uacbd\uc6b0\uc77c \uac83\uc774\ub2e4. \uadf8\ub7f0\ub370 <code class=\"highlighter-rouge\">Descending index<\/code>\uac00 \ud544\uc694\ud55c \uc774\uc720\uac00 \uc624\uc9c1 \uc774\uac83\ubfd0\uc77c\uae4c?<\/p>\n<h1 id=\"descending-index\ub97c-\uc0ac\uc6a9\ud574\uc57c-\ud558\ub294-\ub610-\ub2e4\ub978-\uc774\uc720\">Descending index\ub97c \uc0ac\uc6a9\ud574\uc57c \ud558\ub294 \ub610 \ub2e4\ub978 \uc774\uc720<\/h1>\n<p>MySQL 8.0 \uc774\uc804 \ubc84\uc804\uc744 \uc0ac\uc6a9\ud558\uba74\uc11c \uc5ed\uc21c \uc815\ub82c\uc774 \ud544\uc694\ud55c \uacbd\uc6b0\uc5d0\ub294, \ud06c\uac8c \uc131\ub2a5\uc5d0 \ub300\ud55c \uace0\ub824 \uc5c6\uc774 \uc9c0\uae08\uae4c\uc9c0 <code class=\"highlighter-rouge\">Ascending index<\/code>\ub97c \uc0dd\uc131\ud558\uace0 \u201cORDER BY index_column DESC\u201d \ucffc\ub9ac\ub85c \uc778\ub371\uc2a4\ub97c <code class=\"highlighter-rouge\">Backward scan<\/code>\uc73c\ub85c \uc77d\ub294 \uc2e4\ud589 \uacc4\ud68d\uc744 \uc0ac\uc6a9\ud574\uc654\ub2e4. \uc774\uc81c <code class=\"highlighter-rouge\">Ascending index<\/code>\ub97c <code class=\"highlighter-rouge\">Forward scan<\/code>\ud558\ub294 \uacbd\uc6b0\uc640 Backward scan\ud558\ub294 \uacbd\uc6b0\uc758 \uc131\ub2a5 \ube44\uad50\ub97c \uac04\ub2e8\ud788 \uc608\uc81c\ub85c \ud55c\ubc88 \uc0b4\ud3b4\ubcf4\uc790.<\/p>\n<p>\uc6b0\uc120 \uc544\ub798\uc640 \uac19\uc774 information_schema.COLUMNS \ud14c\uc774\ube14\uc758 \ub808\ucf54\ub4dc\ub97c \ubcf5\uc0ac\ud574\uc11c \ub300\ub7b5 1\ucc9c2\ubc31\uc5ec\ub9cc \uac74\uc758 \ub808\ucf54\ub4dc\ub97c \uac00\uc9c0\ub294 \ud14c\uc774\ube14\uc744 \ub9cc\ub4e4\uc5b4 \ubcf4\uc790.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"k\">TABLE<\/span> <span class=\"n\">t1<\/span> <span class=\"p\">(<\/span>\n  <span class=\"n\">tid<\/span> <span class=\"n\">INT<\/span> <span class=\"k\">NOT<\/span> <span class=\"k\">NULL<\/span> <span class=\"n\">AUTO_INCREMENT<\/span><span class=\"p\">,<\/span>\n  <span class=\"k\">TABLE_NAME<\/span> <span class=\"n\">VARCHAR<\/span><span class=\"p\">(<\/span><span class=\"mi\">64<\/span><span class=\"p\">),<\/span>\n  <span class=\"k\">COLUMN_NAME<\/span> <span class=\"n\">VARCHAR<\/span><span class=\"p\">(<\/span><span class=\"mi\">64<\/span><span class=\"p\">),<\/span>\n  <span class=\"n\">ORDINAL_POSITION<\/span> <span class=\"n\">INT<\/span><span class=\"p\">,<\/span>\n  <span class=\"k\">PRIMARY<\/span> <span class=\"k\">KEY<\/span><span class=\"p\">(<\/span><span class=\"n\">tid<\/span><span class=\"p\">)<\/span>\n<span class=\"p\">)<\/span> <span class=\"n\">ENGINE<\/span><span class=\"o\">=<\/span><span class=\"n\">InnoDB<\/span><span class=\"p\">;<\/span>\n\n<span class=\"k\">INSERT<\/span> <span class=\"k\">INTO<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">SELECT<\/span> <span class=\"k\">NULL<\/span><span class=\"p\">,<\/span> <span class=\"k\">TABLE_NAME<\/span><span class=\"p\">,<\/span> <span class=\"k\">COLUMN_NAME<\/span><span class=\"p\">,<\/span> <span class=\"n\">ORDINAL_POSITION<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">information_schema<\/span><span class=\"p\">.<\/span><span class=\"n\">COLUMNS<\/span><span class=\"p\">;<\/span>\n<span class=\"k\">INSERT<\/span> <span class=\"k\">INTO<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">SELECT<\/span> <span class=\"k\">NULL<\/span><span class=\"p\">,<\/span> <span class=\"k\">TABLE_NAME<\/span><span class=\"p\">,<\/span> <span class=\"k\">COLUMN_NAME<\/span><span class=\"p\">,<\/span> <span class=\"n\">ORDINAL_POSITION<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span><span class=\"p\">;<\/span> <span class=\"c1\">-- \/\/ 12\ubc88 \uc2e4\ud589<\/span>\n\n<span class=\"n\">mysql<\/span><span class=\"o\">&gt;<\/span> <span class=\"k\">SELECT<\/span> <span class=\"k\">COUNT<\/span><span class=\"p\">(<\/span><span class=\"o\">*<\/span><span class=\"p\">)<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span><span class=\"p\">;<\/span>\n<span class=\"o\">+<\/span><span class=\"c1\">----------+<\/span>\n<span class=\"o\">|<\/span> <span class=\"k\">COUNT<\/span><span class=\"p\">(<\/span><span class=\"o\">*<\/span><span class=\"p\">)<\/span> <span class=\"o\">|<\/span>\n<span class=\"o\">+<\/span><span class=\"c1\">----------+<\/span>\n<span class=\"o\">|<\/span> <span class=\"mi\">12619776<\/span> <span class=\"o\">|<\/span>\n<span class=\"o\">+<\/span><span class=\"c1\">----------+<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc774\uc81c \uc774 \ud14c\uc774\ube14\uc744 \ud480 \uc2a4\uce94 \ud558\uba74\uc11c \uc815\ub82c\ub9cc \uc218\ud589\ud558\ub294 \ucffc\ub9ac\ub97c \uc544\ub798\uc640 \uac19\uc774 \ud55c\ubc88 \uc2e4\ud589\ud574\ubcf4\uc790. \uc544\ub798 \ub450 \ucffc\ub9ac\ub294 \ud14c\uc774\ube14\uc758 \ud504\ub77c\uc774\uba38\ub9ac \ud0a4\ub97c <code class=\"highlighter-rouge\">Forward scan<\/code> \ub610\ub294 <code class=\"highlighter-rouge\">Backward scan<\/code>\uc73c\ub85c \uc77d\uc5b4\uc11c \ub9c8\uc9c0\ub9c9 \ub808\ucf54\ub4dc 1\uac74\ub9cc \ubc18\ud658\ud558\uac8c \ub41c\ub2e4. \uccab\ubc88\uc9f8 \ucffc\ub9ac\ub294 tid \uceec\ub7fc\uc758 \uac12\uc774 \uac00\uc7a5 \ud070 \ub808\ucf54\ub4dc 1\uac74\uc744 \uadf8\ub9ac\uace0 \ub450\ubc88\uc9f8 \ucffc\ub9ac\ub294 tid \uceec\ub7fc\uc758 \uac12\uc774 \uac00\uc7a5 \uc791\uc740 \ub808\ucf54\ub4dc 1\uac74\uc744 \ubc18\ud658\ud558\uac8c \ub41c\ub2e4. \ud558\uc9c0\ub9cc LIMIT .. OFFSET .. \ubd80\ubd84\uc758 \ucffc\ub9ac\ub85c \uc778\ud574\uc11c, \uc2e4\uc81c MySQL \uc11c\ubc84\ub294 \ud14c\uc774\ube14\uc758 \ubaa8\ub4e0 \ub808\ucf54\ub4dc\ub97c \uc2a4\uce94\ud574\uc57c\ub9cc \ud55c\ub2e4. (\uc774 \ucffc\ub9ac\ub294 \ubaa8\ub4e0 \ub808\ucf54\ub4dc\ub97c \uc2a4\uce94\ud558\ub294 \uc791\uc5c5\uc740 \ud558\uc9c0\ub9cc, \ud654\uba74\uc5d0\ub294 \ub808\ucf54\ub4dc 1\uac74\ub9cc \ucd9c\ub825\ud558\ub824\uace0 LIMIT .. OFFSET .. \uc635\uc158\uc744 \ucd94\uac00\ud55c \uac83\uc784)<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">tid<\/span> <span class=\"k\">ASC<\/span>  <span class=\"k\">LIMIT<\/span> <span class=\"mi\">12619775<\/span><span class=\"p\">,<\/span><span class=\"mi\">1<\/span><span class=\"p\">;<\/span>\n<span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">tid<\/span> <span class=\"k\">DESC<\/span> <span class=\"k\">LIMIT<\/span> <span class=\"mi\">12619775<\/span><span class=\"p\">,<\/span><span class=\"mi\">1<\/span><span class=\"p\">;<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc704 \ub450 \ucffc\ub9ac\uc758 \uc2e4\ud589 \uacb0\uacfc\ub294 \uc5b4\ub5a4 \ucc28\uc774\ub97c \ubcf4\uc5ec\uc904\uc9c0 \uba3c\uc800 \ud55c\ubc88 \uc608\uce21\ud574\ubcf4\uc790. \uc9c0\uae08\uae4c\uc9c0\ub294 \ub9ce\uc740 \uc0ac\uc6a9\uc790\ub4e4\uc774 \ub450 \ucffc\ub9ac\uac00 \ub3d9\uc77c\ud55c \uc2e4\ud589 \uc2dc\uac04\uc744 \ubcf4\uc5ec\uc904 \uac83\uc774\ub77c \ubbff\uc5b4 \uc758\uc2ec\uce58 \uc54a\uc558\uc744 \uac83\uc774\ub2e4. \ub2f9\uc5f0\ud788 \uadf8\ub807\uac8c \uc791\ub3d9\ud574\uc57c \ud558\ub2c8\uae4c \uace0\ub824 \ub300\uc0c1\uc870\ucc28 \uc544\ub2c8\uc5c8\ub2e4.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"n\">mysql<\/span><span class=\"o\">&gt;<\/span> <span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">tid<\/span> <span class=\"k\">ASC<\/span> <span class=\"k\">LIMIT<\/span> <span class=\"mi\">12619775<\/span><span class=\"p\">,<\/span><span class=\"mi\">1<\/span><span class=\"p\">;<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">4<\/span><span class=\"p\">.<\/span><span class=\"mi\">14<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">4<\/span><span class=\"p\">.<\/span><span class=\"mi\">15<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">4<\/span><span class=\"p\">.<\/span><span class=\"mi\">15<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">4<\/span><span class=\"p\">.<\/span><span class=\"mi\">14<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">4<\/span><span class=\"p\">.<\/span><span class=\"mi\">15<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n\n<span class=\"n\">mysql<\/span><span class=\"o\">&gt;<\/span> <span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">t1<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">tid<\/span> <span class=\"k\">DESC<\/span> <span class=\"k\">LIMIT<\/span> <span class=\"mi\">12619775<\/span><span class=\"p\">,<\/span><span class=\"mi\">1<\/span><span class=\"p\">;<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">5<\/span><span class=\"p\">.<\/span><span class=\"mi\">35<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">5<\/span><span class=\"p\">.<\/span><span class=\"mi\">35<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">5<\/span><span class=\"p\">.<\/span><span class=\"mi\">35<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">5<\/span><span class=\"p\">.<\/span><span class=\"mi\">36<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<span class=\"mi\">1<\/span> <span class=\"k\">row<\/span> <span class=\"k\">in<\/span> <span class=\"k\">set<\/span> <span class=\"p\">(<\/span><span class=\"mi\">5<\/span><span class=\"p\">.<\/span><span class=\"mi\">35<\/span> <span class=\"n\">sec<\/span><span class=\"p\">)<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<blockquote>\n<p>\ud14c\uc2a4\ud2b8 \ud658\uacbd (CPU Bound \ud14c\uc2a4\ud2b8)<\/p>\n<ul>\n<li>CPU : Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz (x 24)<\/li>\n<li>MEMORY : 64GB<\/li>\n<li>DISK : NVME SSD<\/li>\n<li>TABLE SIZE (Disk) : 883MB<\/li>\n<li>MySQL configuration\n<ul>\n<li>innodb_buffer_pool_instances=2<\/li>\n<li>innodb_buffer_pool_size=30G<\/li>\n<li>innodb_adaptive_hash_index=OFF<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/blockquote>\n<p>1\ucc9c2\ubc31\uc5ec\ub9cc\uac74\uc744 \uc2a4\uce94\ud558\ub294\ub370, \u201c1.2\ucd08 \uc815\ub3c4\uc758 \ucc28\uc774\ucbe4\uc774\uc57c!!\u201d\ub77c\uace0 \uc0dd\uac01\ud560 \uc218\ub3c4 \uc788\ub2e4. \ud558\uc9c0\ub9cc \ube44\uc728\ub85c \ub530\uc838\ubcf4\uba74, \uc5ed\uc21c \uc815\ub82c \ucffc\ub9ac\uac00 \uc815\uc21c \uc815\ub82c \ucffc\ub9ac\ubcf4\ub2e4 28.9% \ub354 \uc2dc\uac04\uc774 \uac78\ub9ac\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4. \ud558\ub098\uc758 \uc778\ub371\uc2a4\ub97c \uc815\uc21c\uc73c\ub85c \uc77d\ub290\ub0d0 \ub610\ub294 \uc5ed\uc21c\uc73c\ub85c \uc77d\ub290\ub0d0\uc5d0 \ub530\ub77c\uc11c \uc774\ub7f0 \ucc28\uc774\uac00 \ubc1c\uc0dd\ud55c\ub2e4\ub294 \uac83\uc740 \uc27d\uac8c \uc774\ud574\ud558\uae30 \uc5b4\ub835\ub2e4.<\/p>\n<h1 id=\"backward-index-scan\uc774-\ub290\ub9b0-\uc774\uc720\">Backward index scan\uc774 \ub290\ub9b0 \uc774\uc720<\/h1>\n<p>MySQL \uc11c\ubc84\uc758 InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc5d0\uc11c (\ub9ce\uc740 \uc0ac\uc6a9\uc790\ub4e4\uc774 \uc774\ubbf8 \uc798 \uc54c\uace0 \uc788\ub4ef\uc774) Forward &amp; <code class=\"highlighter-rouge\">Backward index scan<\/code> \ud398\uc774\uc9c0(\ube14\ub85d) \uac04\uc758 \uc591\ubc29\ud5a5 \uc5f0\uacb0 \uace0\ub9ac(Double linked list)\ub97c \ud1b5\ud574\uc11c \uc804\uc9c4(Forward)\ud558\ub290\ub0d0 \ud6c4\uc9c4(Backward)\ud558\ub290\ub0d0\uc758 \ucc28\uc774\ub9cc \uc788\ub2e4. \uc774\uac83\ub9cc \ubcf4\uba74 Forward\uc640 <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc758 \uc131\ub2a5 \ucc28\uc774\ub294 \uc774\ud574\ub418\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n<p>\uc2e4\uc81c InnoDB\uc5d0\uc11c <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc774 <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc5d0 \ube44\ud574\uc11c \ub290\ub9b4 \uc218\ubc16\uc5d0 \uc5c6\ub294 2\uac00\uc9c0 \uc774\uc720\ub97c \uac00\uc9c0\uace0 \uc788\ub2e4.<\/p>\n<ul>\n<li>\ud398\uc774\uc9c0 \uc7a0\uae08\uc774 <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc5d0 \uc801\ud569\ud55c \uad6c\uc870<\/li>\n<li>\ud398\uc774\uc9c0 \ub0b4\uc5d0\uc11c \uc778\ub371\uc2a4 \ub808\ucf54\ub4dc\ub294 \ub2e8\ubc29\ud5a5\uc73c\ub85c\ub9cc \uc5f0\uacb0\ub41c \uad6c\uc870 (Forwarded single linked link)<\/li>\n<\/ul>\n<h3 id=\"1-\ud398\uc774\uc9c0-\uc7a0\uae08\uc774-forward-index-scan\uc5d0-\uc801\ud569\ud55c-\uad6c\uc870\">1. \ud398\uc774\uc9c0 \uc7a0\uae08\uc774 <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc5d0 \uc801\ud569\ud55c \uad6c\uc870<\/h3>\n<p>InnoDB\uc758 \ud398\uc774\uc9c0 \uc7a0\uae08 \ubc29\uc2dd\uc740 <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc744 \uc911\uc2ec\uc73c\ub85c \uad6c\ud604\ub418\uc5b4 \uc788\ub294\ub370, <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc73c\ub85c \uc778\ub371\uc2a4 \ub9ac\ud504 \ud398\uc774\uc9c0\ub97c \uc77d\uc744 \ub54c\ub294, \uc544\ub798 <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-5.7.22\/storage\/innobase\/btr\/btr0pcur.cc#L403-L462\">\ucf54\ub4dc<\/a> \uc0d8\ud50c\uacfc \uac19\uc774 \ud398\uc774\uc9c0\uc758 \uc7a0\uae08\uc744 \ud68d\ub4dd\ud560 \ub54c\uc5d0\ub294 Forward scan \uc21c\uc11c\ub300\ub85c \uc7a0\uae08\uc744 \uac78\uace0 \ub2e4\uc2dc \uc7a0\uae08\uc744 \ud574\uc81c\ud558\uac8c \ub41c\ub2e4.<\/p>\n<div class=\"language-c highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"kt\">void<\/span>\n<span class=\"n\">btr_pcur_move_to_next_page<\/span><span class=\"p\">(<\/span>\n<span class=\"cm\">\/*=======================*\/<\/span>\n    <span class=\"n\">btr_pcur_t<\/span><span class=\"o\">*<\/span> <span class=\"n\">cursor<\/span><span class=\"p\">,<\/span> <span class=\"cm\">\/*!&lt; in: persistent cursor; must be on the\n                last record of the current page *\/<\/span>\n    <span class=\"n\">mtr_t<\/span><span class=\"o\">*<\/span>      <span class=\"n\">mtr<\/span><span class=\"p\">)<\/span>    <span class=\"cm\">\/*!&lt; in: mtr *\/<\/span>\n<span class=\"p\">{<\/span>\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>\n    <span class=\"n\">page<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_pcur_get_page<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">);<\/span>\n    <span class=\"n\">next_page_no<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_page_get_next<\/span><span class=\"p\">(<\/span><span class=\"n\">page<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>    \n    <span class=\"n\">buf_block_t<\/span><span class=\"o\">*<\/span>    <span class=\"n\">block<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_pcur_get_block<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"c1\">\/\/ \ub2e4\uc74c \ud398\uc774\uc9c0(next page)\ub97c \ucc3e\uc544\uc11c, \uc7a0\uae08 \ud68d\ub4dd\n<\/span>    <span class=\"n\">next_block<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_block_get<\/span><span class=\"p\">(<\/span>\n        <span class=\"n\">page_id_t<\/span><span class=\"p\">(<\/span><span class=\"n\">block<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">page<\/span><span class=\"p\">.<\/span><span class=\"n\">id<\/span><span class=\"p\">.<\/span><span class=\"n\">space<\/span><span class=\"p\">(),<\/span> <span class=\"n\">next_page_no<\/span><span class=\"p\">),<\/span>\n        <span class=\"n\">block<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">page<\/span><span class=\"p\">.<\/span><span class=\"n\">size<\/span><span class=\"p\">,<\/span> <span class=\"n\">mode<\/span><span class=\"p\">,<\/span>\n        <span class=\"n\">btr_pcur_get_btr_cur<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">)<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">index<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"n\">next_page<\/span> <span class=\"o\">=<\/span> <span class=\"n\">buf_block_get_frame<\/span><span class=\"p\">(<\/span><span class=\"n\">next_block<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>    \n    <span class=\"c1\">\/\/ \ub2e4\uc74c \ud398\uc774\uc9c0(next page) \uc7a0\uae08 \ud68d\ub4dd\ud6c4, \ud604\uc7ac \ud398\uc774\uc9c0(\uc774\uc804 \ud398\uc774\uc9c0)\uc758 \uc7a0\uae08\uc744 \ud574\uc81c\n<\/span>    <span class=\"n\">btr_leaf_page_release<\/span><span class=\"p\">(<\/span><span class=\"n\">btr_pcur_get_block<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">),<\/span> <span class=\"n\">mode<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span><span class=\"p\">}<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc774\uc81c <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc2dc\uc5d0 \ud398\uc774\uc9c0 \uc7a0\uae08\uc744 \ud68d\ub4dd\ud558\ub294 <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-5.7.22\/storage\/innobase\/btr\/btr0pcur.cc#L473-L546\">\ucf54\ub4dc<\/a> \uc0d8\ud50c\uc744 \ud55c\ubc88 \uc0b4\ud3b4\ubcf4\uc790.<\/p>\n<div class=\"language-c highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"kt\">void<\/span>\n<span class=\"nf\">btr_pcur_move_backward_from_page<\/span><span class=\"p\">(<\/span>\n<span class=\"cm\">\/*=============================*\/<\/span>\n        <span class=\"n\">btr_pcur_t<\/span><span class=\"o\">*<\/span>     <span class=\"n\">cursor<\/span><span class=\"p\">,<\/span> <span class=\"cm\">\/*!&lt; in: persistent cursor, must be on the first\n                                record of the current page *\/<\/span>\n        <span class=\"n\">mtr_t<\/span><span class=\"o\">*<\/span>          <span class=\"n\">mtr<\/span><span class=\"p\">)<\/span>    <span class=\"cm\">\/*!&lt; in: mtr *\/<\/span>\n<span class=\"p\">{<\/span>\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>    <span class=\"c1\">\/\/ \ucee4\uc11c\uc758 \ud604\uc7ac \uc0c1\ud0dc \ubc31\uc5c5\n<\/span>    <span class=\"n\">btr_pcur_store_position<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"n\">mtr_commit<\/span><span class=\"p\">(<\/span><span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>  <span class=\"c1\">\/\/ Mini-transaction \ucee4\ubc0b (\ud398\uc774\uc9c0 \uc7a0\uae08 \ud574\uc81c)\n<\/span>\n    <span class=\"n\">mtr_start<\/span><span class=\"p\">(<\/span><span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>   <span class=\"c1\">\/\/ Mini-transaction \uc2dc\uc791\n<\/span>\n    <span class=\"c1\">\/\/ BTR_SEARCH_PREV \ubaa8\ub4dc\ub85c \ucee4\uc11c \ubcf5\uad6c\n<\/span>    <span class=\"n\">btr_pcur_restore_position<\/span><span class=\"p\">(<\/span><span class=\"n\">latch_mode2<\/span><span class=\"p\">,<\/span> <span class=\"n\">cursor<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"n\">page<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_pcur_get_page<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"n\">prev_page_no<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_page_get_prev<\/span><span class=\"p\">(<\/span><span class=\"n\">page<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"cm\">\/* For intrinsic table we don't do optimistic restore and so there is\n       no left block that is pinned that needs to be released. *\/<\/span>\n    <span class=\"k\">if<\/span> <span class=\"p\">(<\/span><span class=\"o\">!<\/span><span class=\"n\">dict_table_is_intrinsic<\/span><span class=\"p\">(<\/span>\n         <span class=\"n\">btr_cur_get_index<\/span><span class=\"p\">(<\/span><span class=\"n\">btr_pcur_get_btr_cur<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">))<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">table<\/span><span class=\"p\">))<\/span> <span class=\"p\">{<\/span>\n\n        <span class=\"k\">if<\/span> <span class=\"p\">(<\/span><span class=\"n\">prev_page_no<\/span> <span class=\"o\">==<\/span> <span class=\"n\">FIL_NULL<\/span><span class=\"p\">)<\/span> <span class=\"p\">{<\/span>\n        <span class=\"p\">}<\/span> <span class=\"k\">else<\/span> <span class=\"k\">if<\/span> <span class=\"p\">(<\/span><span class=\"n\">btr_pcur_is_before_first_on_page<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">))<\/span> <span class=\"p\">{<\/span>\n\n            <span class=\"n\">prev_block<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_pcur_get_btr_cur<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">)<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">left_block<\/span><span class=\"p\">;<\/span>\n            <span class=\"c1\">\/\/ \ubd88\ud544\uc694\uc2dc \ud604\uc7ac \ud398\uc774\uc9c0 \uc7a0\uae08 \ud574\uc81c\n<\/span>            <span class=\"n\">btr_leaf_page_release<\/span><span class=\"p\">(<\/span><span class=\"n\">btr_pcur_get_block<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">),<\/span>\n                                        <span class=\"n\">latch_mode<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n\n            <span class=\"n\">page_cur_set_after_last<\/span><span class=\"p\">(<\/span><span class=\"n\">prev_block<\/span><span class=\"p\">,<\/span>\n                                        <span class=\"n\">btr_pcur_get_page_cur<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">));<\/span>\n         <span class=\"p\">}<\/span> <span class=\"k\">else<\/span> <span class=\"p\">{<\/span>\n            <span class=\"cm\">\/* The repositioned cursor did not end on an infimum\n               record on a page. Cursor repositioning acquired a latch\n               also on the previous page, but we do not need the latch:\n               release it. *\/<\/span>\n\n            <span class=\"n\">prev_block<\/span> <span class=\"o\">=<\/span> <span class=\"n\">btr_pcur_get_btr_cur<\/span><span class=\"p\">(<\/span><span class=\"n\">cursor<\/span><span class=\"p\">)<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">left_block<\/span><span class=\"p\">;<\/span>\n            <span class=\"c1\">\/\/ \ubd88\ud544\uc694\uc2dc \uc774\uc804 \ud398\uc774\uc9c0(Backward page) \uc7a0\uae08 \ud574\uc81c\n<\/span>            <span class=\"n\">btr_leaf_page_release<\/span><span class=\"p\">(<\/span><span class=\"n\">prev_block<\/span><span class=\"p\">,<\/span> <span class=\"n\">latch_mode<\/span><span class=\"p\">,<\/span> <span class=\"n\">mtr<\/span><span class=\"p\">);<\/span>\n        <span class=\"p\">}<\/span>\n    <span class=\"p\">}<\/span>\n\n    <span class=\"n\">cursor<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">latch_mode<\/span> <span class=\"o\">=<\/span> <span class=\"n\">latch_mode<\/span><span class=\"p\">;<\/span>\n    <span class=\"n\">cursor<\/span><span class=\"o\">-&gt;<\/span><span class=\"n\">old_stored<\/span> <span class=\"o\">=<\/span> <span class=\"nb\">false<\/span><span class=\"p\">;<\/span>\n<span class=\"p\">}<\/span>\n\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\ub300\ub7b5 \ucf54\ub4dc\ub97c \uc77d\uc5b4\ubcf4\uba74, (100% \uc774\ud574\ub294 \uc5b4\ub835\ub354\ub77c\ub3c4) \ub300\ub7b5 <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc73c\ub85c \uc774\uc804 \ud398\uc774\uc9c0\ub85c \ub118\uc5b4\uac00\ub294 \uacfc\uc815\uc740 \uc544\ub798\uc640 \uac19\uc740 \ucc98\ub9ac\uac00 \ubc1c\uc0dd\ud558\ub294 \uac83\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n<ol>\n<li>\ucee4\uc11c\uc758 \uc0c1\ud0dc\ub97c \uc800\uc7a5\ud558\uace0 \ub0b4\ubd80 \ubbf8\ub2c8 \ud2b8\ub79c\uc7ad\uc158\uc744 \ucee4\ubc0b\ud574\uc11c \ubbf8\ub2c8 \ud2b8\ub79c\uc7ad\uc158 \ubc84\ud37c\ub97c \uae00\ub85c\ubc8c \ub9ac\ub450 \ub85c\uadf8 \ubc84\ud37c\ub85c \ubcf5\uc0ac<\/li>\n<li>\ubbf8\ub2c8 \ud2b8\ub79c\uc7ad\uc158\uc744 \uc7ac\uc2dc\uc791<\/li>\n<li>\ucee4\uc11c\uc758 \uc0c1\ud0dc\ub97c \ub2e4\uc2dc \ubcf5\uad6c (\uc774 \uacfc\uc815\uc5d0\uc11c \ud604\uc7ac \ube14\ub85d\uc774 \uc774\ub3d9\ub418\ub294 \uac83\uc744 \ub9c9\uae30 \uc704\ud574\uc11c pinning\uc744 \ud558\uace0 \ud544\uc694\uc5d0 \ub530\ub77c\uc11c \ud604\uc7ac \ube14\ub85d\uacfc \uc774\uc804 \ube14\ub85d(Backward block)\uc758 \uc7a0\uae08\uc744 \ud68d\ub4dd)<\/li>\n<\/ol>\n<p>InnoDB\uc758 B-Tree \ub9ac\ud504 \ud398\uc774\uc9c0\ub294 Double linked list\ub85c \uc5f0\uacb0\ub418\uc5b4 \uc788\uae30 \ub54c\ubb38\uc5d0, \uc0ac\uc2e4 \uc5b4\ub290 \ubc29\ud5a5\uc774\ub4e0\uc9c0 \uc774\ub3d9 \uc790\uccb4\ub294 \ucc28\uc774\uac00 \uc5c6\ub2e4. \ud558\uc9c0\ub9cc InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc5d0\uc11c\ub294 \ud398\uc774\uc9c0 \uc7a0\uae08 \uacfc\uc815\uc5d0\uc11c \ub370\ub4dc\ub77d\uc744 \ubc29\uc9c0\ud558\uae30 \uc704\ud574\uc11c B-Tree\uc758 \uc67c\ucabd\uc5d0\uc11c \uc624\ub978\ucabd \uc21c\uc11c(Forward)\ub85c\ub9cc \uc7a0\uae08\uc744 \ud68d\ub4dd\ud558\ub3c4\ub85d \ud558\uace0 \uc788\ub2e4. \uadf8\ub798\uc11c <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc5d0\uc11c\ub294 \ub2e4\uc74c \ud398\uc774\uc9c0 \uc7a0\uae08 \ud68d\ub4dd\uc774 \ub9e4\uc6b0 \uac04\ub2e8\ud558\uc9c0\ub9cc, <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc5d0\uc11c \uc774\uc804 \ud398\uc774\uc9c0 \uc7a0\uae08\uc744 \ud68d\ub4dd\ud558\ub294 \uacfc\uc815\uc740 \uc0c1\ub2f9\ud788 \ubcf5\uc7a1\ud55c \uacfc\uc815\uc744 \uac70\uce58\uac8c \ub41c\ub2e4.<\/p>\n<p>\uc774\ub7f0 \ucc28\uc774\ub85c \uc778\uc11c \ub9ce\uc740 \ud398\uc774\uc9c0\ub97c \uc2a4\uce94\ud574\uc57c \ud558\ub294 Index scan\uc5d0\uc11c\ub294 \uc7a0\uae08 \ud68d\ub4dd\uc73c\ub85c \uc778\ud55c \ucffc\ub9ac \ucc98\ub9ac \uc9c0\uc5f0\uc774 \ubc1c\uc0dd\ud558\uac8c \ub41c\ub2e4.<\/p>\n<h3 id=\"2-\ud398\uc774\uc9c0-\ub0b4\uc5d0\uc11c-\uc778\ub371\uc2a4-\ub808\ucf54\ub4dc\ub294-\ub2e8\ubc29\ud5a5\uc73c\ub85c\ub9cc-\uc5f0\uacb0\ub41c-\uad6c\uc870\">2. \ud398\uc774\uc9c0 \ub0b4\uc5d0\uc11c \uc778\ub371\uc2a4 \ub808\ucf54\ub4dc\ub294 \ub2e8\ubc29\ud5a5\uc73c\ub85c\ub9cc \uc5f0\uacb0\ub41c \uad6c\uc870<\/h3>\n<p>InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc774 \ud2b9\uc815 \ub808\ucf54\ub4dc\ub97c \uac80\uc0c9\ud560 \ub54c, B-Tree\ub97c \uc774\uc6a9\ud574\uc11c \uac80\uc0c9 \ub300\uc0c1 \ub808\ucf54\ub4dc(\uc778\ub371\uc2a4 \uc5d4\ud2b8\ub9ac)\uac00 \uc800\uc7a5\ub41c \ud398\uc774\uc9c0(Block)\uae4c\uc9c0\ub294 \uac80\uc0c9\ud560 \uc218 \uc788\ub2e4. \ud558\uc9c0\ub9cc \uadf8 \ud398\uc774\uc9c0 \ub0b4\uc5d0\ub3c4 \uc218\ub9ce\uc740 \ub808\ucf54\ub4dc\uac00 \uc800\uc7a5\ub418\uc5b4 \uc788\ub294\ub370, \uc77c\ubc18\uc801\uc73c\ub85c 20\ubc14\uc774\ud2b8 \ud0a4\ub97c \uc800\uc7a5\ud558\ub294 \uc778\ub371\uc2a4 \ud398\uc774\uc9c0(16K)\ub77c\uba74, \ub300\ub7b5 600\uc5ec\uac1c \uc774\uc0c1\uc758 \ub808\ucf54\ub4dc\uac00 \uc800\uc7a5\ub420 \uc218 \uc788\ub2e4. InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc774 600\uc5ec\uac1c \ub808\ucf54\ub4dc\ub97c \ud558\ub098\uc529 \ub2e4 \uc21c\ucc28\uc801\uc73c\ub85c \ube44\uad50\ud55c\ub2e4\uba74 \ub808\ucf54\ub4dc \uac80\uc0c9\uc774 \uc0c1\ub2f9\ud788 \ub290\ub9b4 \uac83\uc774\ub2e4. \uadf8\ub798\uc11c InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc740 \ud558\ub098\uc758 \ud398\uc774\uc9c0\ub0b4\uc5d0\uc11c \uc21c\ucc28\uc801\uc73c\ub85c \uc815\ub82c\ub41c \ub808\ucf54\ub4dc 4~8\uac1c \uc815\ub3c4\uc529\uc744 \ubb36\uc5b4\uc11c \ub300\ud45c \ud0a4(\uac00\uc7a5 \ud070 \uc778\ub371\uc2a4 \uc5d4\ud2b8\ub9ac \ud0a4 \uac12)\ub97c \uc120\uc815\ud55c\ub2e4. \uadf8\ub9ac\uace0 \uc774 \ub300\ud45c \ud0a4\ub4e4\ub9cc \ubaa8\uc544\uc11c \ubcc4\ub3c4\uc758 \ub9ac\uc2a4\ud2b8\ub97c \uad00\ub9ac\ud558\ub294\ub370, \uc774\ub97c \ud398\uc774\uc9c0 \ub514\ub809\ud1a0\ub9ac(Page directory)\ub77c\uace0 \ud55c\ub2e4. \uc544\ub798 \uadf8\ub9bc\uc740 \u201cJeremy Cole\u201d\uc774 \uadf8\ub9b0 \u201c<a href=\"https:\/\/blog.jcole.us\/2013\/01\/14\/efficiently-traversing-innodb-btrees-with-the-page-directory\/\">InnoDB \uc790\ub8cc \uad6c\uc870<\/a>\u201c\uc911\uc5d0\uc11c \u201cInnoDB page directory\u201d \uadf8\ub9bc\uc744 \ucea1\ucc98\ud55c \uac83\uc774\ub2e4. \uc774\ubbf8 \ucda9\ubd84\ud788 \uc774\ud574\ud558\uae30 \uc27d\ub3c4\ub85d \uadf8\ub824\uc838 \uc788\uc5b4 \uadf8\ub300\ub85c \ucc28\uc6a9\ud558\uace0\uc790 \ud55c\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/InnodbPageDirectory.png\" alt=\"InnoDB Page Directory \uad6c\uc870\" \/><\/p>\n<p>InnoDB \uc2a4\ud1a0\ub9ac\uc9c0 \uc5d4\uc9c4\uc740 \ud558\ub098\uc758 \ud398\uc774\uc9c0\uc5d0\uc11c \ud2b9\uc815 \ud0a4 \uac12\uc744 \uac80\uc0c9\ud560 \ub54c Page directory\ub97c \uc774\uc6a9\ud574\uc11c \ubc14\uc774\ub108\ub9ac \uc11c\uce58(Binary search) \ubc29\uc2dd\uc73c\ub85c \uac80\uc0c9 \ub300\uc0c1 \ud0a4\ub97c \ud3ec\ud568\ud558\ub294 \ub300\ud45c \ud0a4\ub97c \uac80\uc0c9\ud55c\ub2e4. \uadf8\ub9ac\uace0 \ub300\ud45c \ud0a4\ub97c \ucc3e\uc73c\uba74 \uadf8\ub54c\ubd80\ud130\ub294 \uc778\ub371\uc2a4 \ud0a4 \uac12 \uc21c\uc11c\ub300\ub85c \uc5f0\uacb0\ub41c Linked list\ub97c \uc774\uc6a9\ud574\uc11c \ub300\uc0c1 \ub808\ucf54\ub4dc\ub97c \uac80\uc0c9\ud558\uac8c \ub41c\ub2e4.<\/p>\n<p>\uadf8\ub7f0\ub370 Double linked list\ub85c \uc5f0\uacb0\ub41c B-Tree\uc758 \ub9ac\ud504 \ud398\uc774\uc9c0 \uad6c\uc870\uc640\ub294 \ub2ec\ub9ac, \ud398\uc774\uc9c0 \ub0b4\ubd80\uc758 \ub808\ucf54\ub4dc(\uc778\ub371\uc2a4 \uc5d4\ud2b8\ub9ac)\ub4e4\uc740 Single linked list \uad6c\uc870\ub85c \uad6c\uc131\ub418\uc5b4 \uc788\ub2e4. Single linked list\ub294 <code class=\"highlighter-rouge\">Ascending index<\/code>\uc5d0\uc11c\ub294 \ud0a4 \uac12\uc774 \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ub418\uc5b4\uc11c Linked list\ub85c \uad6c\uc131\ub418\ub294 \uac83\uc774\ub2e4. \ub9cc\uc57d <code class=\"highlighter-rouge\">Descending index<\/code>\ub77c\uba74 \ud0a4 \uac12\uc774 \ub0b4\ub9bc\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ub418\uc5b4\uc11c Linked list \uad6c\uc131\ub420 \uac83\uc774\ub2e4. \uadf8\ub798\uc11c <code class=\"highlighter-rouge\">Ascending index<\/code>\uc5d0\uc11c <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc740 Linked list\ub97c \uadf8\ub300\ub85c \ub530\ub77c\uac00\uae30\ub9cc \ud558\uba74 \ub418\uc9c0\ub9cc, <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc740 \uadf8\ub807\uac8c \uac04\ub2e8\ud558\uc9c0 \uc54a\ub2e4.<\/p>\n<p>\uc544\ub798 <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-5.7.22\/\/storage\/innobase\/include\/page0page.ic#L836-L871\">\ucf54\ub4dc<\/a> \uc0d8\ud50c\uc740 <code class=\"highlighter-rouge\">Forward index scan<\/code>\uc744 \ud560 \ub54c \ud558\ub098\uc758 \ud398\uc774\uc9c0\uc5d0\uc11c Page directory\ub97c \uc774\uc6a9\ud574\uc11c \ub2e4\uc74c \ub808\ucf54\ub4dc\ub97c \ucc3e\uc544\uc624\ub294 \ubc29\ubc95\uc744 \ubcf4\uc5ec\uc8fc\uace0 \uc788\ub2e4.<\/p>\n<div class=\"language-c highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"n\">UNIV_INLINE<\/span>\n<span class=\"k\">const<\/span> <span class=\"n\">rec_t<\/span><span class=\"o\">*<\/span>\n<span class=\"nf\">page_rec_get_next_low<\/span><span class=\"p\">(<\/span>\n<span class=\"cm\">\/*==================*\/<\/span>\n    <span class=\"k\">const<\/span> <span class=\"n\">rec_t<\/span><span class=\"o\">*<\/span>    <span class=\"n\">rec<\/span><span class=\"p\">,<\/span>    <span class=\"cm\">\/*!&lt; in: pointer to record *\/<\/span>\n    <span class=\"n\">ulint<\/span>        <span class=\"n\">comp<\/span><span class=\"p\">)<\/span>    <span class=\"cm\">\/*!&lt; in: nonzero=compact page layout *\/<\/span>\n<span class=\"p\">{<\/span>\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>    \n    <span class=\"n\">page<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_align<\/span><span class=\"p\">(<\/span><span class=\"n\">rec<\/span><span class=\"p\">);<\/span>\n    <span class=\"n\">offs<\/span> <span class=\"o\">=<\/span> <span class=\"n\">rec_get_next_offs<\/span><span class=\"p\">(<\/span><span class=\"n\">rec<\/span><span class=\"p\">,<\/span> <span class=\"n\">comp<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>\n    <span class=\"k\">return<\/span><span class=\"p\">(<\/span><span class=\"n\">page<\/span> <span class=\"o\">+<\/span> <span class=\"n\">offs<\/span><span class=\"p\">);<\/span>\n<span class=\"p\">}<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p><code class=\"highlighter-rouge\">Forward index scan<\/code>\uc740 \ub2e8\uc21c\ud788 Linked list\ub9cc \ub530\ub77c\uac00\uba74 \ub418\uae30 \ub54c\ubb38\uc5d0 \ucf54\ub4dc \uc5ed\uc2dc \ub9e4\uc6b0 \uac04\ub2e8\ud558\ub2e4. \uc774\uc81c <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc758 <a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-5.7.22\/\/storage\/innobase\/include\/page0page.ic#L950-L995\">\ucf54\ub4dc<\/a> \uc0d8\ud50c\uc744 \ud55c\ubc88 \ube44\uad50\ud574\ubcf4\uc790.<\/p>\n<div class=\"language-c highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"n\">UNIV_INLINE<\/span>\n<span class=\"k\">const<\/span> <span class=\"n\">rec_t<\/span><span class=\"o\">*<\/span>\n<span class=\"n\">page_rec_get_prev_const<\/span><span class=\"p\">(<\/span>\n<span class=\"cm\">\/*====================*\/<\/span>\n    <span class=\"k\">const<\/span> <span class=\"n\">rec_t<\/span><span class=\"o\">*<\/span>    <span class=\"n\">rec<\/span><span class=\"p\">)<\/span>    <span class=\"cm\">\/*!&lt; in: pointer to record, must not be page\n                infimum *\/<\/span>\n<span class=\"p\">{<\/span>\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>    \n    <span class=\"n\">page<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_align<\/span><span class=\"p\">(<\/span><span class=\"n\">rec<\/span><span class=\"p\">);<\/span>\n    <span class=\"c1\">\/\/ Page directory\ub97c \uac80\uc0c9\ud574\uc11c, \ub808\ucf54\ub4dc\ub97c \uc800\uc7a5\ud558\uace0 \uc788\ub294 \uc2ac\ub86f(Directory)\uc744 \uac80\uc0c9\n<\/span>    <span class=\"n\">slot_no<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_dir_find_owner_slot<\/span><span class=\"p\">(<\/span><span class=\"n\">rec<\/span><span class=\"p\">);<\/span>\n    <span class=\"c1\">\/\/ \ud604\uc7ac \ub808\ucf54\ub4dc\uac00 \uc800\uc7a5\ub41c \uc2ac\ub86f\uc758 \uc774\uc804 \uc2ac\ub86f(slot_no-1)\uc744 \uac00\uc838\uc640\uc11c\n<\/span>    <span class=\"n\">slot<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_dir_get_nth_slot<\/span><span class=\"p\">(<\/span><span class=\"n\">page<\/span><span class=\"p\">,<\/span> <span class=\"n\">slot_no<\/span> <span class=\"o\">-<\/span> <span class=\"mi\">1<\/span><span class=\"p\">);<\/span>\n    <span class=\"c1\">\/\/ \ud574\ub2f9 \uc2ac\ub86f\uc758 \ub300\ud45c \ub808\ucf54\ub4dc\ub97c \uac00\uc838\uc628\ub2e4\n<\/span>    <span class=\"n\">rec2<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_dir_slot_get_rec<\/span><span class=\"p\">(<\/span><span class=\"n\">slot<\/span><span class=\"p\">);<\/span>\n\n    <span class=\"k\">if<\/span> <span class=\"p\">(<\/span><span class=\"n\">page_is_comp<\/span><span class=\"p\">(<\/span><span class=\"n\">page<\/span><span class=\"p\">))<\/span> <span class=\"p\">{<\/span>\n        <span class=\"c1\">\/\/ ... skip ...\n<\/span>    <span class=\"p\">}<\/span> <span class=\"k\">else<\/span> <span class=\"p\">{<\/span>\n        <span class=\"k\">while<\/span> <span class=\"p\">(<\/span><span class=\"n\">rec<\/span> <span class=\"o\">!=<\/span> <span class=\"n\">rec2<\/span><span class=\"p\">)<\/span> <span class=\"p\">{<\/span>\n            <span class=\"n\">prev_rec<\/span> <span class=\"o\">=<\/span> <span class=\"n\">rec2<\/span><span class=\"p\">;<\/span>\n            <span class=\"c1\">\/\/ \ub2e4\uc74c \ub808\ucf54\ub4dc\uac00 \uc790\uae30 \uc790\uc2e0\uc77c \ub54c\uae4c\uc9c0 loop\ub97c \uc2e4\ud589\n<\/span>            <span class=\"c1\">\/\/ \uc790\uae30 \uc790\uc2e0 \ub808\ucf54\ub4dc\ub97c \ucc3e\uc73c\uba74, \uadf8 \uc774\uc804 \ub808\ucf54\ub4dc\uac00 \uc774\uc804 \ub808\ucf54\ub4dc\uc774\ubbc0\ub85c\n<\/span>            <span class=\"n\">rec2<\/span> <span class=\"o\">=<\/span> <span class=\"n\">page_rec_get_next_low<\/span><span class=\"p\">(<\/span><span class=\"n\">rec2<\/span><span class=\"p\">,<\/span> <span class=\"n\">FALSE<\/span><span class=\"p\">);<\/span>\n        <span class=\"p\">}<\/span>\n    <span class=\"p\">}<\/span>\n\n    <span class=\"c1\">\/\/ ... skip ...\n<\/span>\n    <span class=\"c1\">\/\/ \uac80\uc0c9\ub41c \uc774\uc804 \ub808\ucf54\ub4dc \ub9ac\ud134\n<\/span>    <span class=\"k\">return<\/span><span class=\"p\">(<\/span><span class=\"n\">prev_rec<\/span><span class=\"p\">);<\/span>\n<span class=\"p\">}<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc0ac\uc2e4 \ucf54\ub4dc \uc790\uccb4\ub294 \ub9ce\uc774 \ubcf5\uc7a1\ud558\uc9c0 \uc54a\uc9c0\ub9cc, Page directory\ubcc4\ub85c 4~8\uac1c \uc815\ub3c4\uc758 \ub808\ucf54\ub4dc(\uc778\ub371\uc2a4 \ud0a4 \uc5d4\ud2b8\ub9ac)\uac00 \uc800\uc7a5\ub418\ubbc0\ub85c <code class=\"highlighter-rouge\">while loop<\/code>\uc744 \ud3c9\uade0\uc801\uc73c\ub85c 2~4\ubc88 \uc815\ub3c4\uc529 \uc2e4\ud589\ud574\uc57c \uc774\uc804 \ub808\ucf54\ub4dc(<code class=\"highlighter-rouge\">Backwrad index scan<\/code>)\ub97c \ucc3e\uc744 \uc218 \uc788\ub294 \uac83\uc774\ub2e4.<\/p>\n<h1 id=\"backward-index-scan\uc758-\uc11c\ube44\uc2a4-\uc601\ud5a5\ub3c4\">Backward index scan\uc758 \uc11c\ube44\uc2a4 \uc601\ud5a5\ub3c4<\/h1>\n<p>\uc774\ub85c\uc368 <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc774 <code class=\"highlighter-rouge\">Forward index scan<\/code>\ubcf4\ub2e4 \ub290\ub9b0 \uc774\uc720\ub97c \uc54c\uac8c \ub418\uc5c8\ub2e4. \uadf8\ub807\ub2e4\uba74 \uc2e4\uc81c <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc744 \uc0ac\uc6a9\ud558\uba74 \uc11c\ube44\uc2a4\uac00 \uc5c4\uccad\ub098\uac8c \ub290\ub824\uc9c0\ub294 \uac83\uc77c\uae4c? Forward index scan\uacfc <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc758 \uc2e4\uc81c \uc11c\ube44\uc2a4 \uc601\ud5a5\ub3c4\ub294 \uc77c\ubc18\uc801\uc73c\ub85c \uadf8\ub807\uac8c \ud06c\uc9c0 \uc54a\uc558\ub2e4. \uc544\uc8fc \ub79c\ub364\ud55c \ud0a4 \uac12\uc73c\ub85c \uac80\uc0c9\ud574\uc11c Index range scan\uc744 \uc2e4\ud589\ud558\ub294 \uacbd\uc6b0 \ub300\ub7b5 \uc544\ub798 \uadf8\ub798\ud504\uc640 \uac19\uc774 10% \uc815\ub3c4\uc758 \ucffc\ub9ac \uc2a4\ub8e8\ud48b \ucc28\uc774\ub97c \ubcf4\uc600\uc73c\uba70, CPU \uc0ac\uc6a9\ub7c9\uc758 \ucc28\uc774\ub294 \ubbf8\ubbf8\ud588\ub2e4 (Test thread\ub97c 16\uac1c \uc815\ub3c4\ub85c \uc548\uc815\uc801\uc778 \ucffc\ub9ac \ucc98\ub9ac\uac00 \uac00\ub2a5\ud55c \uc0c1\ud669\uc5d0\uc11c\uc758 \ud14c\uc2a4\ud2b8 \uacb0\uacfc).<\/p>\n<p><img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/Random_CPU.png\" alt=\"CPU usage on Random query test\" \/><br \/>\n<img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/Random_QueryPerSecond.png\" alt=\"Query throughput on Random query test\" \/><\/p>\n<blockquote>\n<p><code class=\"highlighter-rouge\">Forward index scan<\/code><\/p>\n<ul>\n<li>set @random_tid=floor(rand()*12994454);<\/li>\n<li>select tid from t1 where tid&gt;=@random_tid order by tid ASC limit 50;<\/li>\n<\/ul>\n<p><code class=\"highlighter-rouge\">Backward index scan<\/code><\/p>\n<ul>\n<li>set @random_tid=floor(rand()*12994454);<\/li>\n<li>select tid from t1 where tid&lt;=@random_tid order by tid DESC limit 50;<\/li>\n<\/ul>\n<\/blockquote>\n<p>\ud558\uc9c0\ub9cc \uc815\ub82c\ub41c Index\uc758 \ud2b9\uc815 \ubd80\ubd84(\uc778\ub371\uc2a4\uc758 \uc55e\ubd80\ubd84 \ub610\ub294 \ub05d\ubd80\ubd84)\uc744 \uc9d1\uc911\uc801\uc73c\ub85c \uc77d\ub294 \uacbd\uc6b0, 44% \uc815\ub3c4\uc758 \uc2a4\ub8e8\ud48b \ucc28\uc774\ub97c \ubcf4\uc774\uba70 CPU \uc0ac\uc6a9\ub7c9\ub3c4 \ud070 \ucc28\uc774\ub97c \ubcf4\uc600\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/Hotspot_CPU.png\" alt=\"CPU usage on Hotspot query test\" \/><br \/>\n<img decoding=\"async\" src=\"https\/\/bugs.mysql.com\/files\/Hotspot_QueryPerSecond.png\" alt=\"Query throughput on Hotspot query test\" \/><\/p>\n<blockquote>\n<p><code class=\"highlighter-rouge\">Forward index scan<\/code><\/p>\n<ul>\n<li>select tid from t1 order by tid ASC limit 1000;<\/li>\n<\/ul>\n<p><code class=\"highlighter-rouge\">Backward index scan<\/code><\/p>\n<ul>\n<li>select tid from t1 order by tid DESC limit 1000;<\/li>\n<\/ul>\n<\/blockquote>\n<p>\uc2e4\uc81c Forward vs <code class=\"highlighter-rouge\">Backward index scan<\/code>\uc5d0 \uc131\ub2a5 \ucc28\uc774\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 2\uac00\uc9c0 \uad6c\uc870\uc801 \uc774\uc720 \uc911\uc5d0\uc11c, <code class=\"highlighter-rouge\">\ud398\uc774\uc9c0 \ub0b4\uc5d0\uc11c \uc778\ub371\uc2a4 \ub808\ucf54\ub4dc\ub294 \ub2e8\ubc29\ud5a5\uc73c\ub85c\ub9cc \uc5f0\uacb0\ub41c \uad6c\uc870<\/code>\ubcf4\ub2e4\ub294 <code class=\"highlighter-rouge\">\ud398\uc774\uc9c0 \uc7a0\uae08\uc774<\/code> Forward index scan<code class=\"highlighter-rouge\">\uc5d0 \uc801\ud569\ud55c \uad6c\uc870<\/code>\uac00 \ud06c\uac8c \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4. <code class=\"highlighter-rouge\">\ud398\uc774\uc9c0 \ub0b4\uc5d0\uc11c \uc778\ub371\uc2a4 \ub808\ucf54\ub4dc\ub294 \ub2e8\ubc29\ud5a5\uc73c\ub85c\ub9cc \uc5f0\uacb0\ub41c \uad6c\uc870<\/code>\ub294 CPU \ucc98\ub9ac \uc131\ub2a5\uc5d0\ub9cc \uc758\uc874\ud558\uae30 \ub54c\ubb38\uc5d0 \ud06c\uac8c \ub208\uc5d0 \ubcf4\ub294 \ucc28\uc774\uac00 \uc5c6\uc9c0\ub9cc, <code class=\"highlighter-rouge\">\ud398\uc774\uc9c0 \uc7a0\uae08\uc774<\/code> Forward index scan<code class=\"highlighter-rouge\">\uc5d0 \uc801\ud569\ud55c \uad6c\uc870<\/code>\ub294 \uc2e4\uc81c \ud398\uc774\uc9c0\uc758 \uc7a0\uae08 \uc2dc\uac04\uc774 \uae38\uc5b4\uc9c0\ub294 \uac83\uc774\ubbc0\ub85c \ub3d9\uc2dc\uc131 \ucc98\ub9ac\ub97c \uc800\ud574\ud558\ub294 \uc694\uc18c\uc778 \uac83\uc774\ub2e4. \uadf8\ub798\uc11c \ub79c\ub364 \ucffc\ub9ac\uc758 \uc131\ub2a5 \ud14c\uc2a4\ud2b8\ub294 \ud06c\uac8c \ucc28\uc774\uac00 \uc5c6\uc9c0\ub9cc, \uc778\ub371\uc2a4\uc758 \uc55e\ucabd\uc774\ub098 \ub4a4\ucabd\ub9cc \uc9d1\uc911\uc801\uc73c\ub85c \ucffc\ub9ac \ud558\ub294 \ud14c\uc2a4\ud2b8 \ucf00\uc774\uc2a4\uc5d0\uc11c\ub294 \uc131\ub2a5 \ucc28\uc774\uac00 \ub450\ub4dc\ub7ec\uc9c0\uac00 \ub098\ud0c0\ub09c \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4. \ub450\ubc88\uc9f8 \ud14c\uc2a4\ud2b8\uc5d0\uc11c CPU \uc0ac\uc6a9\ub960\ub3c4 \ub0ae\uc73c\uba74\uc11c \uc2a4\ub8e8\ud48b\uc774 \ub0ae\uc740 \uac83\uc744 \ubcf4\uba74 \ub0b4\ubd80\uc801\uc778 \ubcd1\ubaa9\uc774 \uc2a4\ub8e8\ud48b\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce5c \uac83\uc744 \uc9d0\uc791\ud560 \uc218 \uc788\ub294\ub370, \uc2e4\uc81c \ub450\ubc88\uc9f8 \ud14c\uc2a4\ud2b8\uc5d0\uc11c\ub294 \ub808\ucf54\ub4dc\ub97c \uc77d\uae30 \uc704\ud55c \ud398\uc774\uc9c0 \uc7a0\uae08 \uc2dc\uac04\uc774 \uae38\uc5b4\uc9c4 \uac83\uc774 \ubcd1\ubaa9\uc758 \uc6d0\uc778\uc778 \uac83\uc774\ub2e4.<\/p>\n<h1 id=\"ascending-vs-descending-index\uc758-\uc120\ud0dd-\uae30\uc900\">Ascending vs Descending index\uc758 \uc120\ud0dd \uae30\uc900<\/h1>\n<p>\uc77c\ubc18\uc801\uc73c\ub85c \uc778\ub371\uc2a4\ub97c <code class=\"highlighter-rouge\">ORDER BY ... DESC<\/code>\ud558\ub294 \ucffc\ub9ac\uac00 \uc18c\ub7c9\uc758 \ub808\ucf54\ub4dc\ub97c \ub4dc\ubb3c\uac8c \uc2e4\ud589\ub418\ub294 \uacbd\uc6b0\ub77c\uba74, <code class=\"highlighter-rouge\">Descending index<\/code>\ub97c \uad73\uc774 \uace0\ub824\ud560 \ud544\uc694\ub294 \uc5c6\uc5b4 \ubcf4\uc778\ub2e4.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">tab<\/span> <span class=\"k\">WHERE<\/span> <span class=\"n\">userid<\/span><span class=\"o\">=?<\/span> <span class=\"k\">ORDER<\/span> <span class=\"k\">BY<\/span> <span class=\"n\">score<\/span> <span class=\"k\">DESC<\/span> <span class=\"k\">LIMIT<\/span> <span class=\"mi\">10<\/span><span class=\"p\">;<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\uc608\ub97c \ub4e4\uc5b4\uc11c \uc544\ub798\uc640 \uac19\uc740 \ucffc\ub9ac\uc778 \uacbd\uc6b0, \uc544\ub798 2\uac00\uc9c0 \uc778\ub371\uc2a4 \ubaa8\ub450 \uc801\uc808\ud55c \uc120\ud0dd\uc774 \ub420 \uc218 \uc788\ub2e4.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code>  <span class=\"o\">-<\/span> <span class=\"n\">Ascending<\/span> <span class=\"k\">index<\/span>  <span class=\"p\">:<\/span> <span class=\"k\">INDEX<\/span> <span class=\"p\">(<\/span><span class=\"n\">userid<\/span> <span class=\"k\">ASC<\/span><span class=\"p\">,<\/span> <span class=\"n\">score<\/span> <span class=\"k\">ASC<\/span><span class=\"p\">)<\/span>\n  <span class=\"o\">-<\/span> <span class=\"n\">Descending<\/span> <span class=\"k\">index<\/span> <span class=\"p\">:<\/span> <span class=\"k\">INDEX<\/span> <span class=\"p\">(<\/span><span class=\"n\">userid<\/span> <span class=\"k\">DESC<\/span><span class=\"p\">,<\/span> <span class=\"n\">score<\/span> <span class=\"k\">DESC<\/span><span class=\"p\">)<\/span>\n<\/code><\/pre>\n<\/div>\n<\/div>\n<p>\ud558\uc9c0\ub9cc \uc704 \ucffc\ub9ac\uac00 \uc870\uae08 \ub354 \ub9ce\uc740 \ub808\ucf54\ub4dc\ub97c \ube48\ubc88\ud558\uac8c \uc2e4\ud589\ub41c\ub2e4\uba74, <code class=\"highlighter-rouge\">Ascending index<\/code>\ubcf4\ub2e4\ub294 <code class=\"highlighter-rouge\">Descending index<\/code>\uac00 \ub354 \ud6a8\uc728\uc801\uc774\ub77c\uace0 \ubcfc \uc218 \uc788\ub2e4. \ub610\ud55c \ub9ce\uc740 \ucffc\ub9ac\uac00 \uc778\ub371\uc2a4\uc758 \uc55e\ucabd\ub9cc \ub610\ub294 \ub4a4\ucabd\ub9cc \uc9d1\uc911\uc801\uc73c\ub85c \uc77d\uc5b4\uc11c \uc778\ub371\uc2a4\uc758 \ud2b9\uc815 \ud398\uc774\uc9c0 \uc7a0\uae08\uc774 \ubcd1\ubaa9 \uc9c0\uc810\uc774 \ub420 \uac83\uc73c\ub85c \uc608\uc0c1\ub41c\ub2e4\uba74, \uc801\uc808\ud788 <code class=\"highlighter-rouge\">Descending index<\/code>\ub97c \uc0dd\uc131\ud558\ub294 \uac83\uc774 \uacbd\ud569 \uac10\uc18c\uc5d0 \ub3c4\uc6c0\uc774 \ub420 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4.<\/p>\n<blockquote>\n<p>\ubb3c\ub860 ASC\uc640 DESC \uc815\ub82c\uc744 \ud63c\ud569\ud574\uc11c \ub3d9\uc2dc\uc5d0 \uc0ac\uc6a9\ud558\ub294 \ucffc\ub9ac\ub77c\uba74, \ub2f9\uc5f0\ud788 ASC\uc640 DESC\ub97c \uc11e\uc5b4\uc11c \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud574\uc57c \ud558\ubbc0\ub85c, \uace0\ubbfc\ud560 \ud544\uc694 \uc5c6\uc774 \ucffc\ub9ac\uc758 \uc815\ub82c \uc870\uac74\uc5d0 \ub9de\uac8c \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\uba74 \ub420 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4.<\/p>\n<\/blockquote>\n<p>\uadf8\ub9ac\uace0 <code class=\"highlighter-rouge\">Ascending index<\/code>\uc640 <code class=\"highlighter-rouge\">Descending index<\/code>\uc758 \uc120\ud0dd\uc740 MySQL \uc11c\ubc84\uac00 CPU Bound\ub85c \ucffc\ub9ac\ub97c \ucc98\ub9ac\ud560 \ub54c\uc758 \uc774\uc57c\uae30\uc774\ub2e4. \ub9cc\uc57d MySQL \uc11c\ubc84\uac00 \ub370\uc774\ud130\ub97c \uc77d\uae30 \uc704\ud574\uc11c \ub9e4\ubc88 Disk\ub97c \uc77d\uc5b4\uc57c \ud55c\ub2e4\uba74, <code class=\"highlighter-rouge\">Ascending index<\/code>\ub098 <code class=\"highlighter-rouge\">Descending index<\/code>\uc758 \uad6c\uc870\uc801 \uc7a5\ub2e8\uc810\uc740 Disk \ubc18\uc751 \uc18d\ub3c4(Latency)\uc5d0 \uc774\ubbf8 \uc0c1\uc1c4\ub418\uc5b4 \ubc84\ub9ac\uae30 \ub54c\ubb38\uc5d0 \uadf8\ub2e4\uc9c0 \ucffc\ub9ac \ucc98\ub9ac\uc0c1 \uc131\ub2a5 \uc601\ud5a5 \uc694\uc18c\uac00 \uc544\ub2c8\ub77c\uace0 \ubcfc \uc218 \uc788\ub2e4.<\/p>\n<p>Source: <a href=\"http:\/\/tech.kakao.com\/2018\/06\/19\/AscendingAndDescendingIndex\/\" target=\"_blank\">MySQL Ascending index vs Descending index<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>MySQL Ascending index vs Descending index \uc6a9\uc5b4 \uc815\ub9ac \uc774 \uc124\uba85\uc5d0\uc11c\ub294 \uc778\ub371\uc2a4\uc758 \uc815\ub82c \uc21c\uc11c\uc640 \ub370\uc774\ud130 \uc77d\uae30 \uc21c\uc11c \ub4f1 \ubc29\ud5a5\uc5d0 \ub300\ud55c \ub2e8\uc5b4\ub4e4\uc774 \ud63c\uc7ac\ud558\uba74\uc11c, \uc5ec\ub7ec \uac00\uc9c0 \ud63c\ub780\uc744 \ucd08\ub798\ud558\uae30 \uc26c\uc6b4 \uc124\uba85\ub4e4\uc774 \uc788\uc744 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4. \uadf8\ub798\uc11c \uc6b0\uc120 \ud45c\uc900 \uc6a9\uc5b4\ub294 \uc544\ub2c8\uc9c0\ub9cc, \ub098\ub984\ub300\ub85c \uba87 \uac1c \ub2e8\uc5b4\ub4e4\uc5d0 \ub300\ud574\uc11c \uac1c\ub150\uc744 \uc815\ub9bd\ud558\uace0 \uadf8 \ub2e8\uc5b4\ub97c \ubc88\uc5ed \uc5c6\uc774 \uc601\uc5b4\ub85c \uadf8\ub300\ub85c \ud45c\uae30\ud558\ub3c4\ub85d \ud558\uaca0\ub2e4. Ascending index : \uc791\uc740 \uac12\uc758 \uc778\ub371\uc2a4 \ud0a4\uac00 B-Tree\uc758 \uc67c\ucabd\uc73c\ub85c \uc815\ub82c\ub41c \uc778\ub371\uc2a4 Descending index : \ud070 \uac12\uc758 \uc778\ub371\uc2a4 \ud0a4\uac00 B-Tree\uc758 \uc67c\ucabd\uc73c\ub85c \uc815\ub82c\ub41c \uc778\ub371\uc2a4 Forward index scan (Forward scan) : \uc778\ub371\uc2a4 \ud0a4\uc758 \ud06c\uace0 \uc791\uc74c\uc5d0 \uad00\uacc4\uc5c6\uc774 \uc778\ub371\uc2a4 \ub9ac\ud504 \ub178\ub4dc\uc758 \uc67c\ucabd \ud398\uc774\uc9c0\ubd80\ud130 \uc624\ub978\ucabd\uc73c\ub85c \uc2a4\uce94 Backward index scan (Backward scan) : \uc778\ub371\uc2a4 \ud0a4\uc758 \ud06c\uace0 \uc791\uc74c\uc5d0 \uad00\uacc4\uc5c6\uc774 \uc778\ub371\uc2a4 \ub9ac\ud504 \ub178\ub4dc\uc758 \uc624\ub978\ucabd \ud398\uc774\uc9c0\ubd80\ud130 \uc67c\ucabd\uc73c\ub85c \uc2a4\uce94 Descending index \uc9c0\uc6d0 MySQL 4.x \ubc84\uc804\ubd80\ud130 Feature Request\ub85c \ub4f1\ub85d\ub418\uc5b4 \uc788\ub358 \u201cDescending index\u201d \uae30\ub2a5\uc774 \ub4dc\ub514\uc5b4 MySQL 8.0\uc5d0 \ub3c4\uc785\ub418\uc5c8\ub2e4. MySQL 8.0\ubd80\ud130\ub294 \uc774\uc81c \uc544\ub798\uc640 \uac19\uc774 \uc5ed\uc21c\uc73c\ub85c \uc815\ub82c\ub418\ub294 \uc778\ub371\uc2a4(Descending index)\ub97c \uc0dd\uc131\ud560 \uc218 \uc788\uac8c \ub418\uc5c8\uc73c\uba70, \ud544\uc694\uc5d0 \ub530\ub77c\uc11c <a class=\"mh-excerpt-more\" href=\"https:\/\/jirak.net\/wp\/mysql-ascending-index-vs-descending-index\/\" title=\"MySQL Ascending index vs Descending index\">[ more&#8230; ]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[169],"tags":[705],"class_list":["post-25026","post","type-post","status-publish","format-standard","hentry","category-news","tag-kakao"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/posts\/25026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/comments?post=25026"}],"version-history":[{"count":1,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/posts\/25026\/revisions"}],"predecessor-version":[{"id":25027,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/posts\/25026\/revisions\/25027"}],"wp:attachment":[{"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/media?parent=25026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/categories?post=25026"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jirak.net\/wp\/wp-json\/wp\/v2\/tags?post=25026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}