{"id":61587,"date":"2026-01-18T04:41:38","date_gmt":"2026-01-17T20:41:38","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/61587.html"},"modified":"2026-01-18T04:41:38","modified_gmt":"2026-01-17T20:41:38","slug":"mysql-%e4%bc%98%e5%8c%96","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/61587.html","title":{"rendered":"MySQL \u4f18\u5316"},"content":{"rendered":"<h2>\u4e00\u3001\u5206\u9875\u67e5\u8be2\u4f18\u5316<\/h2>\n<h4>1. \u81ea\u589e\u8fde\u7eed\u4e3b\u952e\u6392\u5e8f\u7684\u5206\u9875\u4f18\u5316<\/h4>\n<h5>\u793a\u4f8b&#xff1a;\u57fa\u7840\u5206\u9875\u5199\u6cd5&#xff1a;select * from employees limit 90000,5;\u8be5 SQL \u8868\u793a\u4ece\u7b2c 90001 \u884c\u5f00\u59cb\u53d6 5 \u884c\u6570\u636e&#xff0c;\u9ed8\u8ba4\u6309\u4e3b\u952e\u6392\u5e8f\u3002<\/h5>\n<h5>\u4f18\u5316&#xff1a;\u5f53\u4e3b\u952e\u81ea\u589e\u4e14\u8fde\u7eed\u65f6&#xff0c;\u53ef\u901a\u8fc7\u4e3b\u952e\u8303\u56f4\u67e5\u8be2\u66ff\u4ee3limit offset, rows&#xff0c;\u907f\u514d\u6570\u636e\u5e93\u626b\u63cf\u524d 90000 \u884c\u6570\u636e\u3002\u4f18\u5316\u540e SQL&#xff1a;select * from employees where id &gt; 90000 limit 5;<\/h5>\n<h5>\u6ce8\u610f\u4e8b\u9879&#xff1a;<\/h5>\n<ul>\n<li>\u4e3b\u952e\u81ea\u589e\u4f46\u4e0d\u8fde\u7eed&#xff08;\u5982\u6279\u91cf\u63d2\u5165\u56de\u6eda\u3001\u624b\u52a8\u6307\u5b9a\u4e3b\u952e&#xff09; \u4e5f\u4f1a\u5bfc\u81f4\u7ed3\u679c\u4e0d\u4e00\u81f4&#xff1b;<\/li>\n<li>\u82e5\u5206\u9875 SQL \u5305\u542border by \u975e\u4e3b\u952e\u5b57\u6bb5&#xff0c;\u8be5\u4f18\u5316\u65b9\u5f0f\u4e0d\u9002\u7528&#xff0c;\u4f1a\u5bfc\u81f4\u6392\u5e8f\u7ed3\u679c\u9519\u4e71&#xff1b;<\/li>\n<\/ul>\n<p>\u603b\u7ed3&#xff1a;\u9002\u7528\u6761\u4ef6&#xff1a;\u2460 \u4e3b\u952e\u81ea\u589e\u4e14\u8fde\u7eed \u2461 \u7ed3\u679c\u6309\u4e3b\u952e\u6392\u5e8f\u3002<\/p>\n<h4>2. \u975e\u4e3b\u952e\u5b57\u6bb5\u6392\u5e8f\u7684\u5206\u9875\u4f18\u5316<\/h4>\n<h5>\u793a\u4f8b&#xff1a;\u57fa\u7840\u5199\u6cd5&#xff1a;select * from employees ORDER BY name limit 90000,5;\u8be5 SQL \u56e0name\u7d22\u5f15\u626b\u63cf &#043; \u56de\u8868\u6210\u672c\u9ad8\u4e8e\u5168\u8868\u626b\u63cf&#xff0c;\u4f18\u5316\u5668\u653e\u5f03\u4f7f\u7528\u7d22\u5f15&#xff0c;\u89e6\u53d1filesort\u3002<\/h5>\n<h5>\u4f18\u5316&#xff1a;\u62c6\u5206\u6392\u5e8f\u5206\u9875\u548c\u6570\u636e\u67e5\u8be2&#xff1a;\u5148\u901a\u8fc7\u7d22\u5f15\u83b7\u53d6\u5206\u9875\u540e\u7684\u4e3b\u952e&#xff0c;\u518d\u901a\u8fc7\u4e3b\u952e\u56de\u8868\u67e5\u5168\u91cf\u6570\u636e&#xff0c;\u51cf\u5c11\u6392\u5e8f\u9636\u6bb5\u7684\u6570\u636e\u52a0\u8f7d\u91cf\u3002\u4f18\u5316\u540e SQL&#xff1a;<\/h5>\n<p>mysql<\/p>\n<p>select * from employees e<br \/>\ninner join (select id from employees order by name limit 90000,5) ed<br \/>\non e.id &#061; ed.id;<\/p>\n<h5>\u8865\u5145&#xff1a;<\/h5>\n<ul>\n<li>\u786e\u4fddname\u5b57\u6bb5\u6709\u7d22\u5f15&#xff08;\u5982idx_employees_name&#xff09;&#xff0c;\u5426\u5219\u5b50\u67e5\u8be2\u4ecd\u4f1a\u5168\u8868\u626b\u63cf&#xff1b;<\/li>\n<li>\u82e5\u5206\u9875\u504f\u79fb\u91cf\u6781\u5927&#xff08;\u5982limit 1000000, 10&#xff09;&#xff0c;\u53ef\u7ed3\u5408\u4e1a\u52a1\u505a &#034;\u6e38\u6807\u5206\u9875&#034;&#xff08;\u8bb0\u5f55\u4e0a\u4e00\u9875\u6700\u540e\u4e00\u6761\u7684name\u548cid&#xff0c;\u5982where name &gt; &#039;xxx&#039; and id &gt; xxx limit 10&#xff09;&#xff0c;\u5f7b\u5e95\u907f\u514d\u5927\u504f\u79fb\u91cf\u626b\u63cf\u3002<\/li>\n<\/ul>\n<h3>\u4e8c\u3001\u8868\u5173\u8054\u67e5\u8be2\u4f18\u5316<\/h3>\n<p>MySQL \u8868\u5173\u8054\u6838\u5fc3\u9075\u5faa\u5c0f\u8868\u9a71\u52a8\u5927\u8868\u539f\u5219&#xff0c;\u5e95\u5c42\u4f9d\u8d56\u4e24\u79cd\u6838\u5fc3\u7b97\u6cd5&#xff1a;<\/p>\n<h4>1. \u5d4c\u5957\u5faa\u73af\u8fde\u63a5&#xff08;Nested-Loop Join, NLJ&#xff09;<\/h4>\n<h5>\u539f\u7406&#xff1a;\u4e00\u6b21\u4e00\u884c\u5faa\u73af\u4ece\u9a71\u52a8\u8868\u53d6\u6570\u636e&#xff0c;\u901a\u8fc7\u5173\u8054\u5b57\u6bb5&#xff08;\u5e26\u7d22\u5f15&#xff09;\u67e5\u8be2\u88ab\u9a71\u52a8\u8868&#xff0c;\u5408\u5e76\u7ed3\u679c\u3002<\/h5>\n<h5>\u6267\u884c\u6d41\u7a0b&#xff1a;<\/h5>\n<li>\u9a71\u52a8\u8868&#xff08;\u5c0f\u8868&#xff09;\u5168\u626b\u63cf&#xff0c;\u53d6\u4e00\u884c\u6570\u636e&#xff1b;<\/li>\n<li>\u7528\u5173\u8054\u5b57\u6bb5\u67e5\u88ab\u9a71\u52a8\u8868&#xff08;\u5927\u8868&#xff09;\u7684\u7d22\u5f15&#xff1b;<\/li>\n<li>\u5408\u5e76\u7ed3\u679c&#xff0c;\u91cd\u590d\u4e0a\u8ff0\u6b65\u9aa4\u3002<\/li>\n<h5>\u6267\u884c\u7279\u5f81<\/h5>\n<ul>\n<li>EXPLAIN \u7ed3\u679c\u4e2dExtra\u65e0Using join buffer&#xff1b;<\/li>\n<li>\u9a71\u52a8\u8868\u9009\u62e9&#xff1a;INNER JOIN \u65f6\u4f18\u5316\u5668\u9009\u5c0f\u8868&#xff0c;LEFT JOIN \u5de6\u8868\u4e3a\u9a71\u52a8\u8868&#xff0c;RIGHT JOIN \u53f3\u8868\u4e3a\u9a71\u52a8\u8868\u3002<\/li>\n<\/ul>\n<h5><\/h5>\n<h4>2. \u57fa\u4e8e\u5757\u7684\u5d4c\u5957\u5faa\u73af\u8fde\u63a5&#xff08;Block Nested-Loop Join, BNL&#xff09;<\/h4>\n<h5>\u539f\u7406&#xff1a;\u9a71\u52a8\u8868\u6570\u636e\u8f7d\u5165join_buffer&#xff0c;\u88ab\u9a71\u52a8\u8868\u5168\u626b\u63cf&#xff0c;\u9010\u884c\u4e0ejoin_buffer\u6570\u636e\u5bf9\u6bd4\u3002<\/h5>\n<h5>\u6267\u884c\u6d41\u7a0b&#xff1a;<\/h5>\n<li>\u9a71\u52a8\u8868\u6570\u636e\u6279\u91cf\u8f7d\u5165join_buffer&#xff08;\u9ed8\u8ba4 256k&#xff0c;\u53ef\u901a\u8fc7join_buffer_size\u8c03\u6574&#xff09;&#xff1b;<\/li>\n<li>\u88ab\u9a71\u52a8\u8868\u5168\u626b\u63cf&#xff0c;\u6bcf\u884c\u4e0ejoin_buffer\u4e2d\u6570\u636e\u5339\u914d&#xff1b;<\/li>\n<li>\u82e5join_buffer\u4e0d\u8db3&#xff0c;\u5206\u6bb5\u8f7d\u5165\u9a71\u52a8\u8868\u6570\u636e&#xff0c;\u91cd\u590d\u626b\u63cf\u88ab\u9a71\u52a8\u8868\u3002<\/li>\n<h5>\u6267\u884c\u7279\u5f81<\/h5>\n<ul>\n<li>EXPLAIN \u7ed3\u679c\u4e2dExtra\u663e\u793aUsing join buffer (Block Nested Loop)&#xff1b;<\/li>\n<li>\u9002\u7528\u4e8e\u88ab\u9a71\u52a8\u8868\u5173\u8054\u5b57\u6bb5\u65e0\u7d22\u5f15\u7684\u573a\u666f\u3002<\/li>\n<\/ul>\n<h5><\/h5>\n<h4>\u5173\u8054\u67e5\u8be2\u4f18\u5316\u51c6\u5219&#xff1a;<\/h4>\n<li>\u6838\u5fc3\u4f18\u5316&#xff1a;\u88ab\u9a71\u52a8\u8868\u5173\u8054\u5b57\u6bb5\u52a0\u7d22\u5f15&#xff0c;\u4f18\u5148\u89e6\u53d1 NLJ \u7b97\u6cd5&#xff08;\u78c1\u76d8 IO \u8fdc\u4f4e\u4e8e BNL&#xff09;&#xff1b;<\/li>\n<li>\u9a71\u52a8\u8868\u9009\u62e9&#xff1a;\u8fc7\u6ee4\u540e\u6570\u636e\u91cf\u5c0f\u7684\u8868\u4f5c\u4e3a\u9a71\u52a8\u8868&#xff08;&#034;\u5c0f\u8868&#034; \u6307\u67e5\u8be2\u540e\u7684\u6570\u636e\u91cf&#xff0c;\u975e\u539f\u8868\u884c\u6570&#xff09;&#xff1b;<\/li>\n<li>\u5f3a\u5236\u9a71\u52a8\u8868&#xff1a;\u4f7f\u7528straight_join\u56fa\u5b9a\u9a71\u52a8\u8868&#xff08;\u4ec5\u9002\u7528\u4e8e INNER JOIN&#xff09;&#xff0c;\u5982select * from t2 straight_join t1 on t2.a &#061; t1.a;&#xff0c;\u4f46\u9700\u8c28\u614e&#xff08;\u4f18\u5316\u5668\u591a\u6570\u60c5\u51b5\u4e0b\u66f4\u667a\u80fd&#xff09;&#xff1b;<\/li>\n<li>join_buffer \u8c03\u4f18&#xff1a;\u82e5\u5fc5\u987b\u7528 BNL&#xff0c;\u53ef\u9002\u5f53\u8c03\u5927join_buffer_size&#xff08;\u5982 1M&#xff09;&#xff0c;\u51cf\u5c11\u5206\u6bb5\u6b21\u6570&#xff0c;\u4f46\u907f\u514d\u5168\u5c40\u8c03\u5927&#xff08;\u6bcf\u4e2a\u8fde\u63a5\u72ec\u7acb\u5206\u914d&#xff09;\u3002<\/li>\n<h3>\u4e09\u3001IN \u4e0e\u00a0EXISTS \u4f18\u5316<\/h3>\n<h4>\u6838\u5fc3\u539f\u5219&#xff1a;\u5c0f\u8868\u9a71\u52a8\u5927\u8868<\/h4>\n<table>\n<tr>\u8bed\u6cd5\u9002\u7528\u573a\u666f\u6267\u884c\u903b\u8f91<\/tr>\n<tbody>\n<tr>\n<td style=\"width:71px\">IN<\/td>\n<td style=\"width:152px\">B \u8868\u6570\u636e\u91cf &lt; A \u8868<\/td>\n<td style=\"width:466px\">\n<p>select * from A where id in (select id from B)<\/p>\n<p>\u7b49\u4ef7\u4e8e&#xff1a;\u904d\u5386 B \u8868&#xff0c;\u7528 B.id \u67e5 A \u8868<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width:71px\">EXISTS<\/td>\n<td style=\"width:152px\">A \u8868\u6570\u636e\u91cf &lt; B \u8868<\/td>\n<td style=\"width:466px\">\n<p>select * from A where exists (select 1 from B where B.id &#061; A.id)<\/p>\n<p>\u7b49\u4ef7\u4e8e&#xff1a;\u904d\u5386 A \u8868&#xff0c;\u7528 A.id \u67e5 B \u8868<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h5>\u8865\u5145&#xff1a;<\/h5>\n<ul>\n<li>EXISTS \u5b50\u67e5\u8be2\u4e2dSELECT *\u7b49\u4ef7\u4e8eSELECT 1&#xff08;\u4f18\u5316\u5668\u5ffd\u7565 SELECT \u6e05\u5355&#xff09;&#xff1b;<\/li>\n<li>\u590d\u6742\u573a\u666f\u4e0b&#xff0c;IN\/EXISTS \u53ef\u66ff\u6362\u4e3a JOIN&#xff0c;\u9700\u5b9e\u6d4b\u5bf9\u6bd4&#xff08;\u5982SELECT DISTINCT A.* FROM A JOIN B ON A.id &#061; B.id&#xff09;&#xff1b;<\/li>\n<li>MySQL8.0 \u5bf9 IN \u5b50\u67e5\u8be2\u505a\u4e86\u4f18\u5316&#xff0c;\u90e8\u5206\u573a\u666f\u4e0b IN \u548c EXISTS \u6027\u80fd\u5dee\u5f02\u7f29\u5c0f\u3002<\/li>\n<\/ul>\n<h3>\u56db\u3001COUNT (*) \u67e5\u8be2\u4f18\u5316<\/h3>\n<h4>1. COUNT \u8bed\u6cd5\u5dee\u5f02<\/h4>\n<table>\n<tr>\u8bed\u6cd5\u7edf\u8ba1\u89c4\u5219\u6027\u80fd&#xff08;\u5b57\u6bb5\u6709\u7d22\u5f15\u65f6&#xff09;\u6027\u80fd&#xff08;\u5b57\u6bb5\u65e0\u7d22\u5f15\u65f6&#xff09;<\/tr>\n<tbody>\n<tr>\n<td style=\"width:131px\">COUNT(*)<\/td>\n<td style=\"width:177px\">\u7edf\u8ba1\u6240\u6709\u884c&#xff08;\u542b NULL&#xff09;<\/td>\n<td style=\"width:243px\">\u6700\u4f18&#xff08;MySQL5.6 &#043; \u4f18\u5316&#xff0c;\u4ec5\u8ba1\u6570\u4e0d\u53d6\u503c&#xff09;<\/td>\n<td>\u6700\u4f18<\/td>\n<\/tr>\n<tr>\n<td style=\"width:131px\">COUNT(1)<\/td>\n<td style=\"width:177px\">\u7edf\u8ba1\u6240\u6709\u884c&#xff08;\u542b NULL&#xff09;<\/td>\n<td style=\"width:243px\">\u7565\u900a\u4e8e COUNT (*)<\/td>\n<td>\u7565\u900a\u4e8e COUNT (*)<\/td>\n<\/tr>\n<tr>\n<td style=\"width:131px\">COUNT (\u4e3b\u952e id)<\/td>\n<td style=\"width:177px\">\u7edf\u8ba1\u4e3b\u952e\u975e NULL \u884c<\/td>\n<td style=\"width:243px\">\u5dee\u4e8e COUNT (\u5b57\u6bb5)<\/td>\n<td>\u4f18\u4e8e COUNT (\u5b57\u6bb5)<\/td>\n<\/tr>\n<tr>\n<td style=\"width:131px\">COUNT (\u5b57\u6bb5)<\/td>\n<td style=\"width:177px\">\u7edf\u8ba1\u5b57\u6bb5\u975e NULL \u884c<\/td>\n<td style=\"width:243px\">\u4f18\u4e8e COUNT (\u4e3b\u952e id)<\/td>\n<td>\u6700\u5dee&#xff08;\u5168\u8868\u626b\u63cf&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h5>\u8865\u5145&#xff1a;COUNT (*) \u662f MySQL \u5b98\u65b9\u63a8\u8350\u5199\u6cd5&#xff0c;\u4e00\u822c\u4e0d\u7528\u66ff\u6362\u4e3a COUNT (1) \u6216 COUNT (\u4e3b\u952e)\u3002<\/h5>\n<h4>2. COUNT \u4f18\u5316\u65b9\u6848<\/h4>\n<table>\n<tr>\u65b9\u6848\u9002\u7528\u5f15\u64ce\u4f18\u70b9\u7f3a\u70b9<\/tr>\n<tbody>\n<tr>\n<td>\u4f9d\u8d56 MyISAM \u603b\u884c\u6570<\/td>\n<td style=\"width:84px\">MyISAM<\/td>\n<td style=\"width:127px\">\u65e0\u9700\u8ba1\u7b97&#xff0c;\u6027\u80fd\u6781\u9ad8<\/td>\n<td>\u4ec5\u65e0 WHERE \u65f6\u53ef\u7528&#xff1b;INNODB \u4e0d\u652f\u6301<\/td>\n<\/tr>\n<tr>\n<td>SHOW TABLE STATUS<\/td>\n<td style=\"width:84px\">INNODB<\/td>\n<td style=\"width:127px\">\u6027\u80fd\u9ad8&#xff0c;\u6beb\u79d2\u7ea7\u8fd4\u56de<\/td>\n<td>\u7ed3\u679c\u4e3a\u4f30\u8ba1\u503c&#xff08;\u8bef\u5dee 10% \u5de6\u53f3&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>Redis \u7ef4\u62a4\u8ba1\u6570<\/td>\n<td style=\"width:84px\">\u6240\u6709\u5f15\u64ce<\/td>\n<td style=\"width:127px\">\u67e5\u8be2\u6781\u5feb<\/td>\n<td>\u96be\u4fdd\u8bc1\u4e8b\u52a1\u4e00\u81f4\u6027&#xff08;\u8868\u64cd\u4f5c\u4e0e Redis \u64cd\u4f5c\u539f\u5b50\u6027&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u6570\u636e\u5e93\u8ba1\u6570\u8868<\/td>\n<td style=\"width:84px\">\u6240\u6709\u5f15\u64ce<\/td>\n<td style=\"width:127px\">\u6570\u636e\u51c6\u786e<\/td>\n<td>\u7ef4\u62a4\u6210\u672c\u9ad8&#xff08;\u9700\u5728\u4e8b\u52a1\u4e2d\u540c\u6b65\u66f4\u65b0&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>\u4e94\u3001Order by \u4e0e Group by \u4f18\u5316<\/h3>\n<h4>1. Order by \u6838\u5fc3\u95ee\u9898&#xff1a;using Filesort<\/h4>\n<h5>\u89e6\u53d1\u539f\u56e0<\/h5>\n<ul>\n<li>\u6392\u5e8f\u5b57\u6bb5\u65e0\u7d22\u5f15&#xff1b;<\/li>\n<li>\u7d22\u5f15\u6392\u5e8f\u65b9\u5411\u4e0e ORDER BY \u65b9\u5411\u4e0d\u4e00\u81f4&#xff08;MySQL8.0 \u524d\u65e0\u964d\u5e8f\u7d22\u5f15&#xff09;&#xff1b;<\/li>\n<li>\u7d22\u5f15\u5305\u542b\u7684\u5b57\u6bb5\u4e0d\u8db3&#xff0c;\u9700\u56de\u8868\u8865\u5145\u6570\u636e\u540e\u6392\u5e8f\u3002<\/li>\n<\/ul>\n<h5>\u4f18\u5316\u793a\u4f8b&#xff08;MySQL8.0 \u964d\u5e8f\u7d22\u5f15&#xff09;<\/h5>\n<p>&#8212; \u521b\u5efa\u964d\u5e8f\u7d22\u5f15<br \/>\nCREATE INDEX idx_position_desc ON employees(position DESC);<br \/>\n&#8212; \u4f18\u5316\u540eSQL&#xff08;\u907f\u514dFilesort&#xff09;<br \/>\nSELECT * FROM employees ORDER BY position DESC LIMIT 10;<\/p>\n<h4>2. Group by \u4f18\u5316<\/h4>\n<ul>\n<li>\u4e0e Order by \u539f\u7406\u4e00\u81f4&#xff0c;\u4f18\u5148\u4f7f\u7528\u7d22\u5f15\u51cf\u5c11\u6392\u5e8f&#xff1b;<\/li>\n<li>\u907f\u514dSELECT * FROM table GROUP BY non_index_field&#xff0c;\u4f1a\u89e6\u53d1\u4e34\u65f6\u8868 &#043; Filesort&#xff1b;<\/li>\n<li>\u4f18\u5316\u65b9\u5f0f&#xff1a;\u5148\u901a\u8fc7\u7d22\u5f15\u83b7\u53d6\u5206\u7ec4\u5b57\u6bb5 &#043; \u4e3b\u952e&#xff0c;\u518d\u56de\u8868\u67e5\u8be2&#xff08;\u540c\u5206\u9875\u4f18\u5316\u601d\u8def&#xff09;\u3002<\/li>\n<\/ul>\n<h4>3. Filesort \u6392\u5e8f\u65b9\u5f0f<\/h4>\n<p>MySQL8.0 \u4e2dmax_length_for_sort_data\u9ed8\u8ba4 4096 \u5b57\u8282&#xff1a;<\/p>\n<ul>\n<li>\u82e5\u6392\u5e8f\u884c\u5927\u5c0f \u2264 4096 \u5b57\u8282&#xff1a;\u4f7f\u7528 &#034;\u5feb\u901f\u6392\u5e8f&#034;&#xff08;\u5185\u5b58\u6392\u5e8f&#xff09;&#xff1b;<\/li>\n<li>\u82e5\u6392\u5e8f\u884c\u5927\u5c0f &gt; 4096 \u5b57\u8282&#xff1a;\u4f7f\u7528 &#034;\u5f52\u5e76\u6392\u5e8f&#034;&#xff08;\u78c1\u76d8\u4e34\u65f6\u6587\u4ef6\u6392\u5e8f&#xff09;&#xff1b;<\/li>\n<li>\u4f18\u5316&#xff1a;\u51cf\u5c11\u6392\u5e8f\u5b57\u6bb5\u6570\u91cf&#xff0c;\u964d\u4f4e\u5355\u884c\u5927\u5c0f\u3002<\/li>\n<\/ul>\n<h3>\u516d\u3001MySQL \u6570\u636e\u7c7b\u578b\u9009\u62e9\u89c4\u8303&#xff08;\u963f\u91cc\u89c4\u8303&#xff09;<\/h3>\n<h4>1. \u6570\u503c\u7c7b\u578b<\/h4>\n<table>\n<tr>\u7c7b\u578b\u53d6\u503c\u8303\u56f4\u4f18\u5316\u5efa\u8bae<\/tr>\n<tbody>\n<tr>\n<td>TINYINT<\/td>\n<td>-128~127&#xff08;UNSIGNED&#xff1a;0~255&#xff09;<\/td>\n<td>\u66ff\u4ee3 ENUM\/BIT&#xff0c;\u8282\u7701\u7a7a\u95f4<\/td>\n<\/tr>\n<tr>\n<td>INT<\/td>\n<td>-2^31~2^31-1&#xff08;UNSIGNED&#xff1a;0~2^32-1&#xff09;<\/td>\n<td>\u65e0\u8d1f\u6570\u65f6\u7528 UNSIGNED&#xff0c;\u4e0d\u6307\u5b9a\u663e\u793a\u5bbd\u5ea6&#xff08;\u5982 INT (10) \u65e0\u610f\u4e49&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>DECIMAL<\/td>\n<td>\u9ad8\u7cbe\u5ea6\u5c0f\u6570<\/td>\n<td>\u5b58\u50a8\u91d1\u989d\u7b49\u9ad8\u7cbe\u5ea6\u6570\u636e&#xff1b;\u4f18\u5148\u7528\u6574\u6570\u5b58\u50a8&#xff08;\u5982\u91d1\u989d \u00d7100 \u5b58 INT&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h5>\u8865\u5145&#xff1a;\u663e\u793a\u5bbd\u5ea6\u4ec5\u5728\u6dfb\u52a0UNSIGNED ZEROFILL\u65f6\u751f\u6548&#xff08;\u5982INT(2) ZEROFILL&#xff0c;\u503c 5 \u663e\u793a\u4e3a 05&#xff09;&#xff0c;\u4e0d\u4f1a\u5f71\u54cd\u5b58\u50a8\u8303\u56f4&#xff0c;\u82e5\u4e0d\u6dfb\u52a0UNSIGNED ZEROFILL\u5219\u4ec0\u4e48\u90fd\u4e0d\u5f71\u54cd\u3002<\/h5>\n<h4>2. \u65e5\u671f\u65f6\u95f4\u7c7b\u578b<\/h4>\n<table>\n<tr>\u7c7b\u578b\u5b58\u50a8\u7a7a\u95f4\u9002\u7528\u573a\u666f<\/tr>\n<tbody>\n<tr>\n<td>DATE<\/td>\n<td>3 \u5b57\u8282<\/td>\n<td>\u4ec5\u5b58\u50a8\u65e5\u671f&#xff08;yyyy-mm-dd&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>TIME<\/td>\n<td>3 \u5b57\u8282<\/td>\n<td>\u4ec5\u5b58\u50a8\u65f6\u95f4&#xff08;hh:mm:ss&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>TIMESTAMP<\/td>\n<td>4 \u5b57\u8282<\/td>\n<td>\u8de8\u65f6\u533a\u573a\u666f&#xff0c;\u81ea\u52a8\u66f4\u65b0&#xff08;\u9ed8\u8ba4 CURRENT_TIMESTAMP&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>DATETIME<\/td>\n<td>8 \u5b57\u8282<\/td>\n<td>\u65e0\u9700\u65f6\u533a\u8f6c\u6362&#xff0c;\u65e0\u65f6\u95f4\u4e0a\u9650&#xff08;\u63a8\u8350&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>3. \u5b57\u7b26\u4e32\u7c7b\u578b<\/h4>\n<table>\n<tr>\u7c7b\u578b\u7279\u6027\u4f18\u5316\u5efa\u8bae<\/tr>\n<tbody>\n<tr>\n<td>CHAR(n)<\/td>\n<td>\u5b9a\u957f&#xff0c;\u8865\u7a7a\u683c<\/td>\n<td>\u77ed\u5b57\u7b26\u4e32&#xff08;\u5982\u624b\u673a\u53f7\u3001\u90ae\u7f16&#xff09;&#xff0c;\u957f\u5ea6\u56fa\u5b9a\u573a\u666f<\/td>\n<\/tr>\n<tr>\n<td>VARCHAR(n)<\/td>\n<td>\u53d8\u957f&#xff0c;\u5b58\u957f\u5ea6\u6807\u8bc6<\/td>\n<td>\u957f\u5ea6\u5dee\u5f02\u5927\u7684\u5b57\u7b26\u4e32&#xff08;\u5982\u7528\u6237\u540d\u3001\u63cf\u8ff0&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>BLOB\/TEXT<\/td>\n<td>\u5927\u5b57\u6bb5<\/td>\n<td>\u5355\u72ec\u5206\u8868\u5b58\u50a8&#xff0c;\u907f\u514d\u5f71\u54cd\u4e3b\u8868\u67e5\u8be2\u6027\u80fd<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h5>\u8865\u5145\u89c4\u8303<\/h5>\n<ul>\n<li>\u8868\u60c5\u7b26\u53f7\u9700\u7528utf8mb4\u7f16\u7801&#xff08;utf8 \u4ec5\u652f\u6301 3 \u5b57\u8282&#xff0c;\u8868\u60c5\u5360 4 \u5b57\u8282&#xff09;&#xff1b;<\/li>\n<li>\u907f\u514d\u7528 VARCHAR \u5b58\u50a8\u6570\u503c&#xff08;\u5982\u624b\u673a\u53f7&#xff09;&#xff0c;\u65e0\u6cd5\u5229\u7528\u6570\u503c\u7d22\u5f15\u7279\u6027&#xff1b;<\/li>\n<li>VARCHAR (n) \u7684 n \u4e3a\u6700\u5927\u957f\u5ea6&#xff0c;\u6309\u5b9e\u9645\u4e1a\u52a1\u4e0a\u9650\u8bbe\u7f6e&#xff08;\u5982\u7528\u6237\u540d\u8bbe VARCHAR (32)&#xff0c;\u800c\u975e VARCHAR (255)&#xff09;\u3002<\/li>\n<\/ul>\n<h3>\u4e03\u3001\u603b\u7ed3<\/h3>\n<p>MySQL \u6027\u80fd\u4f18\u5316\u6838\u5fc3\u56f4\u7ed5\u51cf\u5c11\u78c1\u76d8 IO\u3001\u5229\u7528\u7d22\u5f15\u3001\u964d\u4f4e\u5185\u5b58\u8ba1\u7b97\u91cf\u4e09\u5927\u539f\u5219&#xff1a;<\/p>\n<li>\u5206\u9875\u67e5\u8be2&#xff1a;\u4f18\u5148\u4e3b\u952e\u8303\u56f4\u67e5\u8be2&#xff0c;\u975e\u4e3b\u952e\u6392\u5e8f\u62c6\u5206 &#034;\u6392\u5e8f\u53d6\u4e3b\u952e &#043; \u56de\u8868&#034;&#xff1b;<\/li>\n<li>\u5173\u8054\u67e5\u8be2&#xff1a;\u5c0f\u8868\u9a71\u52a8\u5927\u8868&#xff0c;\u88ab\u9a71\u52a8\u8868\u5173\u8054\u5b57\u6bb5\u52a0\u7d22\u5f15&#xff1b;<\/li>\n<li>\u8ba1\u6570\u67e5\u8be2&#xff1a;\u6839\u636e\u7cbe\u5ea6\u8981\u6c42\u9009\u62e9 MyISAM\/Redis\/ \u8ba1\u6570\u8868\u65b9\u6848&#xff1b;<\/li>\n<li>\u6392\u5e8f\u5206\u7ec4&#xff1a;\u5229\u7528\u7d22\u5f15\u907f\u514d Filesort&#xff0c;MySQL8.0 \u53ef\u4f7f\u7528\u964d\u5e8f\u7d22\u5f15&#xff1b;<\/li>\n<li>\u6570\u636e\u7c7b\u578b&#xff1a;\u6700\u5c0f\u5316\u7c7b\u578b\u957f\u5ea6&#xff0c;\u4f18\u5148\u975e NULL&#xff0c;\u907f\u514d\u4e0d\u5408\u7406\u7c7b\u578b\u9009\u62e9\u3002<\/li>\n<p>\u6700\u540e&#xff0c;\u6240\u6709\u4f18\u5316\u9700\u7ed3\u5408\u5b9e\u9645\u4e1a\u52a1\u573a\u666f&#xff0c;\u901a\u8fc7EXPLAIN\u5206\u6790\u6267\u884c\u8ba1\u5212&#xff0c;\u907f\u514d\u8fc7\u5ea6\u4f18\u5316&#xff08;\u5982\u4e3a\u4f4e\u9891\u67e5\u8be2\u521b\u5efa\u5927\u91cf\u7d22\u5f15&#xff09;\u3002<\/p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u5206\u9875\u67e5\u8be2\u4f18\u53161. \u81ea\u589e\u8fde\u7eed\u4e3b\u952e\u6392\u5e8f\u7684\u5206\u9875\u4f18\u5316\u793a\u4f8b&#xff1a;\u57fa\u7840\u5206\u9875\u5199\u6cd5&#xff1a;select * from employees limit 90000,5;\u8be5 SQL \u8868\u793a\u4ece\u7b2c 90001 \u884c\u5f00\u59cb\u53d6 5 \u884c\u6570\u636e&#xff0c;\u9ed8\u8ba4\u6309\u4e3b\u952e\u6392\u5e8f\u3002\u4f18\u5316&#xff1a;\u5f53\u4e3b\u952e\u81ea\u589e\u4e14\u8fde\u7eed\u65f6&#xff0c;\u53ef\u901a\u8fc7\u4e3b\u952e\u8303\u56f4\u67e5\u8be2\u66ff\u4ee3limit offset, rows&#xff0c;\u907f\u514d\u6570\u636e\u5e93\u626b\u63cf\u524d 90000 \u884c\u6570\u636e\u3002\u4f18\u5316\u540e SQL&#xff1a;select * from<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[114,100],"topic":[],"class_list":["post-61587","post","type-post","status-publish","format-standard","hentry","category-server","tag-mysql","tag-100"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/61587.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u4e00\u3001\u5206\u9875\u67e5\u8be2\u4f18\u53161. \u81ea\u589e\u8fde\u7eed\u4e3b\u952e\u6392\u5e8f\u7684\u5206\u9875\u4f18\u5316\u793a\u4f8b&#xff1a;\u57fa\u7840\u5206\u9875\u5199\u6cd5&#xff1a;select * from employees limit 90000,5;\u8be5 SQL \u8868\u793a\u4ece\u7b2c 90001 \u884c\u5f00\u59cb\u53d6 5 \u884c\u6570\u636e&#xff0c;\u9ed8\u8ba4\u6309\u4e3b\u952e\u6392\u5e8f\u3002\u4f18\u5316&#xff1a;\u5f53\u4e3b\u952e\u81ea\u589e\u4e14\u8fde\u7eed\u65f6&#xff0c;\u53ef\u901a\u8fc7\u4e3b\u952e\u8303\u56f4\u67e5\u8be2\u66ff\u4ee3limit offset, rows&#xff0c;\u907f\u514d\u6570\u636e\u5e93\u626b\u63cf\u524d 90000 \u884c\u6570\u636e\u3002\u4f18\u5316\u540e SQL&#xff1a;select * from\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/61587.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-01-17T20:41:38+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/61587.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/61587.html\",\"name\":\"MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-01-17T20:41:38+00:00\",\"dateModified\":\"2026-01-17T20:41:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/61587.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/61587.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/61587.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL \u4f18\u5316\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/61587.html","og_locale":"zh_CN","og_type":"article","og_title":"MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u4e00\u3001\u5206\u9875\u67e5\u8be2\u4f18\u53161. \u81ea\u589e\u8fde\u7eed\u4e3b\u952e\u6392\u5e8f\u7684\u5206\u9875\u4f18\u5316\u793a\u4f8b&#xff1a;\u57fa\u7840\u5206\u9875\u5199\u6cd5&#xff1a;select * from employees limit 90000,5;\u8be5 SQL \u8868\u793a\u4ece\u7b2c 90001 \u884c\u5f00\u59cb\u53d6 5 \u884c\u6570\u636e&#xff0c;\u9ed8\u8ba4\u6309\u4e3b\u952e\u6392\u5e8f\u3002\u4f18\u5316&#xff1a;\u5f53\u4e3b\u952e\u81ea\u589e\u4e14\u8fde\u7eed\u65f6&#xff0c;\u53ef\u901a\u8fc7\u4e3b\u952e\u8303\u56f4\u67e5\u8be2\u66ff\u4ee3limit offset, rows&#xff0c;\u907f\u514d\u6570\u636e\u5e93\u626b\u63cf\u524d 90000 \u884c\u6570\u636e\u3002\u4f18\u5316\u540e SQL&#xff1a;select * from","og_url":"https:\/\/www.wsisp.com\/helps\/61587.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-01-17T20:41:38+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"4 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/61587.html","url":"https:\/\/www.wsisp.com\/helps\/61587.html","name":"MySQL \u4f18\u5316 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-01-17T20:41:38+00:00","dateModified":"2026-01-17T20:41:38+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/61587.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/61587.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/61587.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"MySQL \u4f18\u5316"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/61587","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=61587"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/61587\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=61587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=61587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=61587"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=61587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}