{"id":110006,"date":"2026-09-25T15:40:44","date_gmt":"2026-09-25T07:40:44","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/110006.html"},"modified":"2026-09-25T15:40:44","modified_gmt":"2026-09-25T07:40:44","slug":"%e4%bb%8e%e4%b8%80%e6%9d%a1-sql-%e7%9a%84%e7%94%9f%e5%91%bd%e5%91%a8%e6%9c%9f%e5%bc%80%e5%a7%8b%ef%bc%8c%e6%8a%8a-mysql-%e4%b8%80%e6%ac%a1%e6%80%a7%e6%83%b3%e6%98%8e%e7%99%bd","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/110006.html","title":{"rendered":"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d"},"content":{"rendered":"
\n

\u8fd9\u7bc7\u7b14\u8bb0\u6cbf\u7740\u300c\u4e00\u6761 SQL \u8bed\u53e5\u5728 MySQL \u91cc\u7684\u5b8c\u6574\u65c5\u7a0b\u300d\u8fd9\u6761\u4e3b\u7ebf，\u628a\u67b6\u6784\u3001\u5b58\u50a8\u5f15\u64ce\u3001\u7d22\u5f15\u3001\u4e8b\u52a1\u3001MVCC\u3001\u9501\u3001\u65e5\u5fd7\u4e0e\u6027\u80fd\u4f18\u5316\u5168\u90e8\u4e32\u8d77\u6765\u2014\u2014\u6bcf\u7ae0\u90fd\u914d\u53ef\u8dd1\u7684\u5b9e\u6218 SQL（\u7535\u5546\u5e93 mall），\u7ed3\u5c3e\u9644 20 \u95ee\u901f\u7b54\u3002\u5fd8\u4e86\u6ca1\u5173\u7cfb，\u8ddf\u7740\u4e3b\u7ebf\u8d70\u4e00\u904d，\u77e5\u8bc6\u70b9\u4f1a\u81ea\u5df1\u300c\u957f\u300d\u56de\u8111\u5b50\u91cc\u3002 \u9002\u7528\u7248\u672c：MySQL 8.0 \u00b7 InnoDB \u00b7 10 \u7ae0 \u00b7 40+ \u6bb5\u5b9e\u6218 SQL \u00b7 20 \u95ee\u901f\u67e5<\/p>\n

\u76ee\u5f55<\/h3>\n
  • \u67b6\u6784\u5168\u666f：\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f<\/li>\n
  • \u5b58\u50a8\u5f15\u64ce\u4e0e\u6570\u636e\u7c7b\u578b<\/li>\n
  • SQL \u6838\u5fc3\u8bed\u6cd5\u4e0e\u7535\u5546\u5efa\u5e93\u5b9e\u6218<\/li>\n
  • \u67e5\u8be2\u8fdb\u9636：JOIN\u3001\u7a97\u53e3\u51fd\u6570\u4e0e\u7ecf\u5178\u4e1a\u52a1\u9898<\/li>\n
  • \u7d22\u5f15：\u4ece B+ \u6811\u5230 EXPLAIN \u5b9e\u6218<\/li>\n
  • \u4e8b\u52a1\u4e0e MVCC<\/li>\n
  • \u9501：\u4ece MDL \u5230\u95f4\u9699\u9501<\/li>\n
  • \u65e5\u5fd7\u7cfb\u7edf：redo \/ undo \/ binlog<\/li>\n
  • \u6027\u80fd\u4f18\u5316\u5b9e\u6218<\/li>\n
  • \u901f\u67e5\u624b\u518c\u4e0e\u590d\u4e60\u8def\u7ebf<\/li>\n
    \n

    \u4e00\u3001\u67b6\u6784\u5168\u666f：\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f<\/h3>\n

    \u56de\u5fc6 MySQL \u6700\u9ad8\u6548\u7684\u8def\u5f84，\u4e0d\u662f\u628a\u77e5\u8bc6\u70b9\u4e00\u4e2a\u4e2a\u80cc\u56de\u6765，\u800c\u662f\u987a\u7740**\u300c\u4e00\u6761 SQL \u8bed\u53e5\u4ece\u5ba2\u6237\u7aef\u53d1\u51fa\u53bb，\u5230\u62ff\u5230\u7ed3\u679c\u300d\u7684\u5b8c\u6574\u65c5\u7a0b**\u8d70\u4e00\u904d\u2014\u2014\u6bcf\u4e2a\u7ec4\u4ef6\u5728\u65c5\u7a0b\u4e2d\u8f6e\u756a\u767b\u573a，\u5fd8\u4e86\u54ea\u4e2a\u5c31\u8865\u54ea\u4e2a\u3002\u8fd9\u4e00\u7ae0\u5148\u5efa\u7acb\u5168\u666f\u5730\u56fe，\u540e\u9762\u4e5d\u7ae0\u90fd\u662f\u5bf9\u5730\u56fe\u67d0\u4e00\u5757\u7684\u653e\u5927\u3002<\/p>\n

    1.1 \u5148\u770b\u5168\u666f：\u56db\u5c42\u7ed3\u6784<\/h4>\n

    \u628a MySQL \u60f3\u8c61\u6210\u4e00\u680b\u56db\u5c42\u697c：\u5ba2\u6237\u7aef\u5728\u697c\u5916，SQL \u4ece\u697c\u9876\u8fdb\u3001\u6570\u636e\u5728\u697c\u5e95\u5b58\u3002<\/p>\n

    #mermaid-svg-7YIbNxS3lh4m5yJp{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-7YIbNxS3lh4m5yJp .error-icon{fill:#552222;}#mermaid-svg-7YIbNxS3lh4m5yJp .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-7YIbNxS3lh4m5yJp .marker{fill:#333333;stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .marker.cross{stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp svg{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-7YIbNxS3lh4m5yJp p{margin:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .label{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label text{fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label span{color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label span p{background-color:transparent;}#mermaid-svg-7YIbNxS3lh4m5yJp .label text,#mermaid-svg-7YIbNxS3lh4m5yJp span{fill:#333;color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .node rect,#mermaid-svg-7YIbNxS3lh4m5yJp .node circle,#mermaid-svg-7YIbNxS3lh4m5yJp .node ellipse,#mermaid-svg-7YIbNxS3lh4m5yJp .node polygon,#mermaid-svg-7YIbNxS3lh4m5yJp .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .rough-node .label text,#mermaid-svg-7YIbNxS3lh4m5yJp .node .label text,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label,#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label{text-anchor:middle;}#mermaid-svg-7YIbNxS3lh4m5yJp .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .rough-node .label,#mermaid-svg-7YIbNxS3lh4m5yJp .node .label,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label,#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label{text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .node.clickable{cursor:pointer;}#mermaid-svg-7YIbNxS3lh4m5yJp .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .arrowheadPath{fill:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-7YIbNxS3lh4m5yJp .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster text{fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster span{color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-7YIbNxS3lh4m5yJp .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp rect.text{fill:none;stroke-width:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape p,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label rect,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-7YIbNxS3lh4m5yJp .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-7YIbNxS3lh4m5yJp :root{–mermaid-font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;}<\/p>\n

    <\/p>\n

    \u6587\u4ef6\u7cfb\u7edf\u5c42<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u5b58\u50a8\u5f15\u64ce\u5c42（InnoDB）<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    MySQL \u670d\u52a1\u5c42（Server Layer）<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    SQL \u8bed\u53e5<\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u4ec5\u66f4\u65b0\u8bed\u53e5，\u63d0\u4ea4\u65f6\u5199<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u5ba2\u6237\u7aefNavicat \u00b7 JDBC \u00b7 \u7a0b\u5e8f<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u8fde\u63a5\u5668\u8fde\u63a5\u7ba1\u7406 \u00b7 \u6743\u9650\u6821\u9a8c<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u5206\u6790\u5668\u8bcd\u6cd5 \u00b7 \u8bed\u6cd5\u89e3\u6790<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u4f18\u5316\u5668\u9009\u7d22\u5f15 \u00b7 \u5b9a\u6267\u884c\u8ba1\u5212<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u6267\u884c\u5668\u8c03\u7528\u5f15\u64ce\u63a5\u53e3<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    Buffer Pool\u5185\u5b58\u6570\u636e\u9875<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u4e8b\u52a1 \u00b7 \u9501 \u00b7 MVCC<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    \u6570\u636e\u6587\u4ef6.ibd \u8868\u7a7a\u95f4<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    redo log<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    undo log<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

    binlog<\/p>\n

    <\/span><\/p>\n

      \n
    • \u8fde\u63a5\u5c42：\u7ba1\u7406 TCP \u8fde\u63a5\u3001\u8d26\u53f7\u5bc6\u7801\u8ba4\u8bc1\u3001\u7ebf\u7a0b\u590d\u7528\u3002\u5ba2\u6237\u7aef\u771f\u6b63\u8fde\u7684\u662f\u5b83\u3002<\/li>\n
    • \u670d\u52a1\u5c42：\u5305\u542b\u8fde\u63a5\u5668\u3001\u5206\u6790\u5668\u3001\u4f18\u5316\u5668\u3001\u6267\u884c\u5668，\u4ee5\u53ca\u5185\u7f6e\u51fd\u6570\u3001\u5b58\u50a8\u8fc7\u7a0b\u3001\u89e6\u53d1\u5668\u7b49\u8de8\u5f15\u64ce\u80fd\u529b\u3002\u6838\u5fc3 SQL \u903b\u8f91\u90fd\u5728\u8fd9\u5c42\u3002<\/li>\n
    • \u5b58\u50a8\u5f15\u64ce\u5c42：\u63d2\u4ef6\u5f0f\u8bbe\u8ba1，\u771f\u6b63\u8d1f\u8d23\u6570\u636e\u7684\u5b58\u4e0e\u53d6\u3002InnoDB \u8fd8\u72ec\u5360\u7ba1\u7406\u4e8b\u52a1\u3001\u9501\u3001MVCC \u548c redo\/undo \u65e5\u5fd7\u3002<\/li>\n
    • \u6587\u4ef6\u7cfb\u7edf\u5c42：\u6570\u636e\u6587\u4ef6（.ibd）\u548c\u4e09\u7c7b\u65e5\u5fd7\u7684\u6700\u7ec8\u5f52\u5bbf，\u843d\u76d8\u7684\u5730\u65b9\u3002<\/li>\n<\/ul>\n

      \u8bb0\u5fc6\u951a\u70b9：\u670d\u52a1\u5c42\u7ba1\u300c\u600e\u4e48\u6267\u884c SQL\u300d，\u5f15\u64ce\u5c42\u7ba1\u300c\u6570\u636e\u600e\u4e48\u5b58\u53d6\u300d\u3002binlog \u5c5e\u4e8e\u670d\u52a1\u5c42，redo\/undo \u5c5e\u4e8e InnoDB\u2014\u2014\u8fd9\u4e2a\u5f52\u5c5e\u5173\u7cfb\u540e\u9762\u8bb2\u4e24\u9636\u6bb5\u63d0\u4ea4\u65f6\u4f1a\u53cd\u590d\u7528\u5230\u3002<\/p>\n

      1.2 \u4e00\u6761 SELECT \u7684\u4e94\u6b65\u65c5\u7a0b<\/h4>\n

      \u4ee5\u6700\u5e38\u89c1\u7684\u4e00\u53e5\u8bdd\u4e3a\u4f8b：SELECT * FROM users WHERE id = 1;<\/p>\n

    • \u8fde\u63a5\u5668：\u5efa\u7acb（\u6216\u590d\u7528）\u8fde\u63a5，\u6821\u9a8c\u8d26\u53f7\u5bc6\u7801，\u968f\u540e\u8bfb\u53d6\u8be5\u8d26\u53f7\u7684\u6743\u9650\u8868\u5e76\u7f13\u5b58\u5230\u8fd9\u4e2a\u8fde\u63a5\u91cc\u3002\u6240\u4ee5\u4fee\u6539\u7528\u6237\u6743\u9650\u53ea\u5bf9\u65b0\u8fde\u63a5\u751f\u6548，\u8001\u8fde\u63a5\u8981\u91cd\u8fde\u624d\u611f\u77e5\u3002<\/li>\n
    • \u67e5\u8be2\u7f13\u5b58（8.0 \u5df2\u79fb\u9664）：5.7 \u53ca\u4ee5\u524d\u7684\u7248\u672c\u4f1a\u5148\u62ff SQL \u539f\u6587\u5f53 key \u67e5\u7f13\u5b58，\u547d\u4e2d\u76f4\u63a5\u8fd4\u56de\u3002\u4f46\u53ea\u8981\u8868\u91cc\u6709\u4e00\u884c\u66f4\u65b0，\u8fd9\u5f20\u8868\u76f8\u5173\u7684\u7f13\u5b58\u5168\u90e8\u5931\u6548，\u5b9e\u9645\u547d\u4e2d\u7387\u6781\u4f4e，MySQL 8.0 \u628a\u6574\u5757\u529f\u80fd\u5220\u6389\u4e86\u3002<\/li>\n
    • \u5206\u6790\u5668：\u5148\u505a\u8bcd\u6cd5\u5206\u6790（\u628a\u5b57\u7b26\u4e32\u62c6\u6210\u5173\u952e\u5b57\u3001\u8868\u540d\u3001\u5217\u540d），\u518d\u505a\u8bed\u6cd5\u5206\u6790\u751f\u6210\u8bed\u6cd5\u6811\u3002\u62a5\u9519 You have an error in your SQL syntax \u5c31\u662f\u8fd9\u4e00\u6b65\u629b\u7684\u3002<\/li>\n
    • \u4f18\u5316\u5668：\u51b3\u5b9a\u300c\u600e\u4e48\u6267\u884c\u6700\u5feb\u300d\u2014\u2014\u7528\u4e3b\u952e\u7d22\u5f15\u8fd8\u662f\u4e8c\u7ea7\u7d22\u5f15\u3001\u591a\u8868 JOIN \u65f6\u8c01\u505a\u9a71\u52a8\u8868\u3002\u5b83\u4ea7\u51fa\u7684\u65b9\u6848\u5c31\u662f EXPLAIN \u80fd\u770b\u5230\u7684\u4e1c\u897f\u3002<\/li>\n
    • \u6267\u884c\u5668：\u5148\u68c0\u67e5\u5bf9\u8fd9\u4e2a\u8868\u6709\u6ca1\u6709\u6267\u884c\u6743\u9650，\u7136\u540e\u8c03\u7528 InnoDB \u7684\u63a5\u53e3\u53d6\u6570\u636e\u3002InnoDB \u5b9a\u4f4d\u5230 id=1 \u7684\u884c\u8fd4\u56de\u7ed9\u6267\u884c\u5668，\u6267\u884c\u5668\u628a\u7ed3\u679c\u96c6\u53d1\u7ed9\u5ba2\u6237\u7aef\u3002<\/li>\n

      \u8bb0\u5fc6\u951a\u70b9：\u5206\u6790\u5668\u7ba1\u300c\u8fd9\u53e5\u8bdd\u5bf9\u4e0d\u5bf9\u300d，\u4f18\u5316\u5668\u7ba1\u300c\u600e\u4e48\u6267\u884c\u6700\u5feb\u300d，\u6267\u884c\u5668\u7ba1\u300c\u771f\u53bb\u5e72\u6d3b\u300d，\u5b58\u50a8\u5f15\u64ce\u7ba1\u300c\u6570\u636e\u600e\u4e48\u5b58\u53d6\u300d\u3002\u56db\u8fde\u95ee，\u9762\u8bd5\u548c\u6392\u969c\u90fd\u9760\u5b83\u5b9a\u4f4d\u95ee\u9898\u5c42\u3002<\/p>\n

      1.3 \u4e00\u6761 UPDATE \u591a\u4e86\u4ec0\u4e48（\u4f0f\u7b14）<\/h4>\n

      \u6362\u6210 UPDATE users SET status = 0 WHERE id = 1;，\u524d\u56db\u6b65\u5b8c\u5168\u4e00\u6837，\u4f46\u8fdb\u5165 InnoDB \u540e\u591a\u51fa\u4e00\u6574\u5957\u52a8\u4f5c：<\/p>\n

    • \u628a\u76ee\u6807\u884c\u6240\u5728\u6570\u636e\u9875\u52a0\u8f7d\u8fdb Buffer Pool（\u5185\u5b58）；<\/li>\n
    • \u5199 undo log（\u8bb0\u5f55\u4fee\u6539\u524d\u7684\u503c，\u4f9b\u56de\u6eda\u548c MVCC \u7528）；<\/li>\n
    • \u5728 Buffer Pool \u91cc\u6539\u6570\u636e\u884c，\u8be5\u9875\u53d8\u6210\u810f\u9875，\u7531\u540e\u53f0\u7ebf\u7a0b\u5f02\u6b65\u5237\u56de\u78c1\u76d8；<\/li>\n
    • \u5199 redo log（prepare \u72b6\u6001）；<\/li>\n
    • \u6267\u884c\u5668\u5199 binlog，\u968f\u540e redo log \u7f6e\u4e3a commit\u2014\u2014\u8fd9\u5c31\u662f\u4e24\u9636\u6bb5\u63d0\u4ea4，\u7b2c 8 \u7ae0\u4e13\u95e8\u62c6\u3002<\/li>\n

      \u8fd9\u5957\u300c\u5148\u5199\u65e5\u5fd7\u3001\u540e\u5237\u6570\u636e\u9875\u300d\u7684\u8bbe\u8ba1\u53eb WAL（Write-Ahead Logging），\u662f MySQL \u5d29\u6e83\u540e\u80fd\u81ea\u52a8\u6062\u590d\u7684\u5168\u90e8\u5e95\u6c14\u3002<\/p>\n

      1.4 \u8fde\u63a5\u7ba1\u7406\u5b9e\u6218<\/h4>\n

      \u8fde\u63a5\u5efa\u7acb\u6709\u771f\u5b9e\u5f00\u9500：TCP \u63e1\u624b\u3001\u8eab\u4efd\u8ba4\u8bc1\u3001\u8bfb\u6743\u9650\u8868\u3002\u9ad8\u5e76\u53d1\u77ed\u8fde\u63a5\u573a\u666f\u4e0b\u8fd9\u4e9b\u5f00\u9500\u4f1a\u88ab\u653e\u5927，\u6240\u4ee5\u5e94\u7528\u4fa7\u6c38\u8fdc\u7528\u8fde\u63a5\u6c60（FastAPI \u4fa7\u5982 SQLAlchemy \u7684 pool_size\u3001pool_recycle）\u3002\u4e24\u4e2a\u7ecf\u5178\u95ee\u9898\u5148\u8bb0\u4e0b\u6765：<\/p>\n

        \n
      • \u7a7a\u95f2\u8fde\u63a5\u8d85\u65f6：\u7531 wait_timeout（\u9ed8\u8ba4 8 \u5c0f\u65f6）\u63a7\u5236，\u8d85\u65f6\u540e\u670d\u52a1\u7aef\u4e3b\u52a8\u65ad\u5f00，\u5ba2\u6237\u7aef\u518d\u53d1 SQL \u5c31\u4f1a\u6536\u5230 MySQL server has gone away\u3002\u8fde\u63a5\u6c60\u7684 pool_recycle \u8981\u8bbe\u5f97\u6bd4\u5b83\u5c0f\u3002<\/li>\n
      • \u957f\u8fde\u63a5\u5185\u5b58\u4e0a\u6da8：5.7 \u53ca\u4ee5\u524d\u7248\u672c\u4e2d，\u8fde\u63a5\u5185\u6267\u884c\u8fc7\u7684\u5927\u7684\u4e34\u65f6\u7ed3\u679c\u4e0d\u4f1a\u7acb\u5373\u91ca\u653e，\u957f\u8fde\u63a5\u8d8a\u4e45\u5185\u5b58\u8d8a\u9ad8\u3002\u5bf9\u7b56：\u8fde\u63a5\u6c60\u5b9a\u671f\u56de\u6536\u91cd\u5efa\u8fde\u63a5，\u6216\u6267\u884c\u5b8c\u5927\u4efb\u52a1\u540e\u8c03\u7528 mysql_reset_connection()\u3002<\/li>\n<\/ul>\n

        \u5b9e\u6218\u6f14\u7ec3：\u4e09\u6761\u547d\u4ee4\u770b\u6e05\u5f53\u524d\u8fde\u63a5<\/p>\n

        SHOW<\/span> PROCESSLIST;<\/span> — \u8c01\u8fde\u7740\u3001\u6b63\u5728\u5e72\u561b<\/span>
        \nSHOW<\/span> STATUS<\/span> LIKE<\/span> 'Threads%'<\/span>;<\/span> — \u8fde\u63a5\u6570\u6982\u51b5<\/span>
        \nSHOW<\/span> VARIABLES LIKE<\/span> 'wait_timeout'<\/span>;<\/span> — \u7a7a\u95f2\u591a\u4e45\u4f1a\u88ab\u65ad\u5f00<\/span><\/p>\n

        mysql> SHOW PROCESSLIST;
        \n+—-+——+———————+——+———+——+————+————————–+
        \n| Id | User | Host | db | Command | Time | State | Info |
        \n+—-+——+———————+——+———+——+————+————————–+
        \n| 8 | root | 192.168.1.10:52331 | mall | Query | 0 | starting | SHOW PROCESSLIST |
        \n| 12 | app | 192.168.1.20:60012 | mall | Sleep | 120 | | NULL |
        \n| 15 | app | 192.168.1.20:60018 | mall | Query | 12 | sending da | SELECT * FROM orders … |
        \n+—-+——+———————+——+———+——+————+————————–+
        \n— Command=Query \u4e14 Time \u5f88\u5927：\u5927\u6982\u7387\u662f\u6162 SQL \u6216\u88ab\u9501\u963b\u585e；\u786e\u8ba4\u540e KILL 15; \u6390\u6389\u5b83<\/p>\n

        \n

        \u4e8c\u3001\u5b58\u50a8\u5f15\u64ce\u4e0e\u6570\u636e\u7c7b\u578b<\/h3>\n

        \u8fd9\u4e00\u7ae0\u56de\u7b54\u4e24\u4e2a\u95ee\u9898：\u8868\u7528\u4ec0\u4e48\u5f15\u64ce\u5efa？\u6bcf\u4e2a\u5b57\u6bb5\u9009\u4ec0\u4e48\u7c7b\u578b？\u2014\u2014\u8fd9\u4e24\u4ef6\u4e8b\u5728\u5efa\u8868\u90a3\u4e00\u523b\u5c31\u51b3\u5b9a\u4e86\u8fd9\u5f20\u8868\u672a\u6765\u4e94\u5e74\u7684\u6027\u80fd\u4e0a\u9650\u3002<\/p>\n

        2.1 InnoDB \u4e0e MyISAM：\u4e00\u573a\u5df2\u7ecf\u7ed3\u675f\u7684\u6bd4\u8d5b<\/h4>\n

        MySQL 5.5 \u4e4b\u524d\u9ed8\u8ba4\u5f15\u64ce\u662f MyISAM，5.5 \u8d77\u6362\u6210 InnoDB，8.0 \u8fde\u7cfb\u7edf\u8868\u4e5f\u5168\u90e8\u6539\u7528 InnoDB\u3002\u9762\u8bd5\u8fd8\u7231\u95ee\u8fd9\u4e2a\u5bf9\u6bd4，\u56e0\u4e3a\u5bf9\u6bd4\u7684\u6bcf\u4e00\u884c\u90fd\u662f\u8003\u70b9：<\/p>\n\n\u7ef4\u5ea6InnoDBMyISAM<\/tr>\n\n\n\n\n\n\n\n\n\n
        \u4e8b\u52a1<\/td>\n\u652f\u6301（ACID）<\/td>\n\u4e0d\u652f\u6301<\/td>\n<\/tr>\n
        \u9501\u7c92\u5ea6<\/td>\n\u884c\u7ea7\u9501（\u5e76\u53d1\u9ad8）<\/td>\n\u8868\u7ea7\u9501（\u8bfb\u5199\u4e92\u65a5）<\/td>\n<\/tr>\n
        \u5916\u952e<\/td>\n\u652f\u6301<\/td>\n\u4e0d\u652f\u6301<\/td>\n<\/tr>\n
        \u5d29\u6e83\u6062\u590d<\/td>\nredo log \u81ea\u52a8\u6062\u590d，\u5d29\u6e83\u5b89\u5168<\/td>\n\u51e0\u4e4e\u6ca1\u6709，\u65ad\u7535\u6613\u574f\u8868<\/td>\n<\/tr>\n
        \u7d22\u5f15\u7ec4\u7ec7<\/td>\n\u805a\u7c07\u7d22\u5f15：\u53f6\u5b50\u8282\u70b9\u5b58\u6574\u884c\u6570\u636e<\/td>\n\u975e\u805a\u7c07：\u7d22\u5f15\u4e0e\u6570\u636e\u6587\u4ef6\u5206\u79bb<\/td>\n<\/tr>\n
        \u65e0 where \u7684 count(*)<\/td>\n\u8981\u626b\u63cf（MVCC \u4e0b\u5404\u4e8b\u52a1\u53ef\u89c1\u884c\u6570\u4e0d\u540c）<\/td>\nO(1)，\u76f4\u63a5\u8bfb\u5b58\u50a8\u7684\u884c\u6570<\/td>\n<\/tr>\n
        \u5168\u6587\u7d22\u5f15<\/td>\n5.6 \u8d77\u652f\u6301<\/td>\n\u652f\u6301<\/td>\n<\/tr>\n
        \u4eca\u5929\u7684\u7528\u9014<\/td>\n99% \u7684 OLTP \u573a\u666f\u9ed8\u8ba4\u9009\u5b83<\/td>\n\u7eaf\u53ea\u8bfb\u7edf\u8ba1\u3001\u5f52\u6863（\u4e5f\u57fa\u672c\u88ab\u6dd8\u6c70）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

        \u5b9e\u6218\u6f14\u7ec3：\u67e5\u770b\u5f15\u64ce<\/p>\n

        SHOW<\/span> ENGINES;<\/span> — \u67e5\u770b\u652f\u6301\u7684\u5f15\u64ce\u6e05\u5355<\/span>
        \nSHOW<\/span> TABLE<\/span> STATUS<\/span> FROM<\/span> mall WHERE<\/span> Name =<\/span> 'orders'<\/span>;<\/span> — \u770b\u67d0\u5f20\u8868\u7528\u7684\u5f15\u64ce<\/span>
        \n— 8.0 \u91cc\u67e5\u5230\u7684 Engine \u5217\u5e94\u4e3a InnoDB；\u60f3\u8fc1\u79fb\u8001\u8868：<\/span>
        \nALTER<\/span> TABLE<\/span> old_table ENGINE<\/span> =<\/span> InnoDB<\/span>;<\/span><\/p>\n

        2.2 \u6570\u503c\u7c7b\u578b：\u6574\u6570\u4e0e\u5b9e\u6570<\/h4>\n\n\u7c7b\u578b\u5b57\u8282\u6709\u7b26\u53f7\u8303\u56f4UNSIGNED\u5178\u578b\u7528\u9014<\/tr>\n\n\n\n\n\n\n
        TINYINT<\/td>\n1<\/td>\n-128 ~ 127<\/td>\n0 ~ 255<\/td>\n\u72b6\u6001\u3001\u7c7b\u578b\u3001\u5f00\u5173<\/td>\n<\/tr>\n
        SMALLINT<\/td>\n2<\/td>\n\u00b13.2 \u4e07<\/td>\n0 ~ 6.5 \u4e07<\/td>\n\u5c0f\u8303\u56f4\u8ba1\u6570<\/td>\n<\/tr>\n
        MEDIUMINT<\/td>\n3<\/td>\n\u00b1838 \u4e07<\/td>\n0 ~ 1677 \u4e07<\/td>\n\u4e2d\u7b49\u8ba1\u6570<\/td>\n<\/tr>\n
        INT<\/td>\n4<\/td>\n\u00b121.4 \u4ebf<\/td>\n0 ~ 42.9 \u4ebf<\/td>\n\u4e00\u822c\u6570\u503c<\/td>\n<\/tr>\n
        BIGINT<\/td>\n8<\/td>\n\u00b19.2 \u00d7 10^18<\/td>\n\u66f4\u5927<\/td>\n\u4e3b\u952e\u3001\u8ba2\u5355\u53f7\u3001\u65f6\u95f4\u6233(ms)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
          \n
        • INT(11) \u91cc\u7684 11 \u53ea\u662f\u300c\u663e\u793a\u5bbd\u5ea6\u300d，\u4e0d\u9650\u5236\u53d6\u503c\u8303\u56f4，8.0.17 \u8d77\u5b98\u65b9\u5df2\u5e9f\u5f03\u8fd9\u4e2a\u5199\u6cd5，\u76f4\u63a5\u5199 INT \u5373\u53ef\u3002<\/li>\n
        • \u72b6\u6001\u5b57\u6bb5\u7528 TINYINT UNSIGNED \u5e76\u5728 COMMENT \u91cc\u5199\u6e05\u679a\u4e3e\u542b\u4e49，\u522b\u7528 ENUM（\u89c1 2.3）\u3002<\/li>\n
        • \u4e3b\u952e\u7edf\u4e00 BIGINT UNSIGNED：INT \u7684 21 \u4ebf\u5728\u6570\u636e\u91cf\u5927\u7684\u4e1a\u52a1\u91cc\u771f\u4f1a\u7528\u5b8c，\u4e14\u672a\u6765\u63a5\u96ea\u82b1 ID\u3001\u5408\u5e76\u8868\u90fd\u65b9\u4fbf\u3002<\/li>\n<\/ul>\n

          \u5b9e\u6570\u53ea\u6709\u4e00\u6761\u519b\u89c4：\u91d1\u989d\u5fc5\u987b\u7528 DECIMAL\u3002FLOAT\/DOUBLE \u662f\u4e8c\u8fdb\u5236\u6d6e\u70b9\u6570，\u5929\u751f\u65e0\u6cd5\u7cbe\u786e\u8868\u793a 0.1 \u8fd9\u6837\u7684\u5341\u8fdb\u5236\u5c0f\u6570：<\/p>\n

          SELECT<\/span> 0.1<\/span> +<\/span> 0.2<\/span> =<\/span> 0.3<\/span>;<\/span> — 0（\u4e0d\u76f8\u7b49！\u6d6e\u70b9\u7cbe\u5ea6\u4e22\u5931）<\/span>
          \nSELECT<\/span> 0.1<\/span> +<\/span> 0.2<\/span>;<\/span> — 0.30000000000000004<\/span><\/p>\n

          — \u6b63\u786e\u59ff\u52bf：\u91d1\u989d DECIMAL(10,2)，\u603b\u5171 10 \u4f4d\u3001\u5c0f\u6570 2 \u4f4d<\/span>
          \nSELECT<\/span> CAST(<\/span>0.1<\/span> AS<\/span> DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span>)<\/span> +<\/span> CAST(<\/span>0.2<\/span> AS<\/span> DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span>)<\/span>;<\/span> — 0.3<\/span><\/p>\n

          DECIMAL \u662f\u7cbe\u786e\u5b58\u50a8，\u4ee3\u4ef7\u662f\u6bd4 DOUBLE \u5360\u7a7a\u95f4\u3001\u8ba1\u7b97\u6162\u3002\u8d85\u5927\u5e76\u53d1\u7684\u652f\u4ed8\u6838\u5fc3\u8868\u91cc，\u5e38\u89c1\u7684\u66ff\u4ee3\u65b9\u6848\u662f\u300c\u4ee5\u5206\u4e3a\u5355\u4f4d\u5b58 BIGINT\u300d，\u5728\u5e94\u7528\u5c42\u6362\u7b97\u3002<\/p>\n

          2.3 \u5b57\u7b26\u4e32：CHAR \/ VARCHAR \/ TEXT \/ ENUM<\/h4>\n
            \n
          • CHAR(N)：\u5b9a\u957f，\u4e0d\u8db3 N \u4f4d\u81ea\u52a8\u8865\u7a7a\u683c（\u8bfb\u51fa\u65f6\u53bb\u6389）\u3002\u9002\u5408\u957f\u5ea6\u6052\u5b9a\u7684\u503c：\u56fd\u5bb6\u7801 CHAR(2)\u3001MD5 \u503c CHAR(32)\u3002<\/li>\n
          • VARCHAR(N)：\u53d8\u957f，\u989d\u5916\u82b1 1~2 \u5b57\u8282\u8bb0\u5f55\u5b9e\u9645\u957f\u5ea6\u3002N \u662f\u300c\u5b57\u7b26\u6570\u300d，utf8mb4 \u4e0b\u4e00\u4e2a\u5b57\u7b26\u6700\u591a\u5360 4 \u5b57\u8282，\u6240\u4ee5\u5355\u5217\u7406\u8bba\u4e0a\u9650\u7ea6 16383 \u5b57\u7b26（\u53d7\u884c\u5927\u5c0f 65535 \u5b57\u8282\u7ea6\u675f）\u3002<\/li>\n
          • TEXT \/ BLOB：\u8d85\u957f\u5185\u5bb9\u6ea2\u51fa\u5230\u72ec\u7acb\u6570\u636e\u9875\u5b58\u50a8，\u67e5\u8be2\u4ee3\u4ef7\u9ad8\u3002\u786e\u6709\u9700\u6c42\u65f6\u5782\u76f4\u62c6\u5230\u9644\u8868，\u522b\u548c\u4e3b\u4e1a\u52a1\u5b57\u6bb5\u6df7\u5728\u4e00\u884c\u3002<\/li>\n
          • ENUM：\u6539\u4e00\u4e2a\u679a\u4e3e\u503c\u5c31\u8981 DDL\u3001\u6392\u5e8f\u6309\u5b9a\u4e49\u987a\u5e8f\u800c\u975e\u5b57\u5178\u5e8f，\u56e2\u961f\u89c4\u8303\u901a\u5e38\u7981\u7528\u2014\u2014\u7528 TINYINT + \u5b57\u5178\u8868（\u6216 COMMENT \u679a\u4e3e\u8bf4\u660e）\u66ff\u4ee3\u3002<\/li>\n<\/ul>\n

            \u5b9e\u6218\u6f14\u7ec3：IP \u5730\u5740\u5b58\u4ec0\u4e48\u7c7b\u578b？<\/p>\n

            — \u65b9\u6848 A：VARCHAR(15)，\u53ef\u8bfb\u6027\u6700\u597d，\u901a\u7528\u4e1a\u52a1\u76f4\u63a5\u7528<\/span>
            \n— \u65b9\u6848 B：INT UNSIGNED，\u7701 7 \u5b57\u8282，\u4e14\u80fd\u6309\u7f51\u6bb5\u8303\u56f4\u6bd4\u8f83（\u5185\u7f51\/\u65e5\u5fd7\u7edf\u8ba1\u573a\u666f）<\/span>
            \nSELECT<\/span> INET_ATON(<\/span>'192.168.1.100'<\/span>)<\/span>;<\/span> — 3232235876（\u70b9\u5206\u5341\u8fdb\u5236 \u2192 \u6574\u6570）<\/span>
            \nSELECT<\/span> INET_NTOA(<\/span>3232235876<\/span>)<\/span>;<\/span> — '192.168.1.100'（\u6574\u6570 \u2192 \u70b9\u5206\u5341\u8fdb\u5236）<\/span><\/p>\n

            — \u65b9\u6848 C：VARBINARY(16)，\u9700\u8981\u652f\u6301 IPv6 \u65f6\u7528 INET6_ATON\/INET6_NTOA<\/span><\/p>\n

            2.4 \u65f6\u95f4\u7c7b\u578b：DATETIME vs TIMESTAMP<\/h4>\n\n\u7c7b\u578b\u5b57\u8282\u8303\u56f4\u65f6\u533a\u884c\u4e3a<\/tr>\n\n\n\n\n\n
            DATETIME<\/td>\n8<\/td>\n1000-01-01 ~ 9999-12-31<\/td>\n\u65e0\u65f6\u533a\u8bed\u4e49：\u5199\u5165\u4ec0\u4e48\u8bfb\u51fa\u4ec0\u4e48<\/td>\n<\/tr>\n
            TIMESTAMP<\/td>\n4<\/td>\n1970 ~ 2038-01-19（2038 \u95ee\u9898）<\/td>\n\u5b58 UTC，\u8bfb\u5199\u6309\u4f1a\u8bdd\u65f6\u533a\u81ea\u52a8\u8f6c\u6362<\/td>\n<\/tr>\n
            DATE<\/td>\n3<\/td>\n\u4ec5\u65e5\u671f<\/td>\n\u2014<\/td>\n<\/tr>\n
            TIME \/ YEAR<\/td>\n3 \/ 1<\/td>\n\u65f6\u95f4 \/ \u5e74\u4efd<\/td>\n\u2014<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

            \u63a8\u8350\u7ec4\u5408：DATETIME + DEFAULT CURRENT_TIMESTAMP（5.6.5 \u8d77 DATETIME \u4e5f\u652f\u6301\u8be5\u9ed8\u8ba4\u503c\u4e0e ON UPDATE CURRENT_TIMESTAMP），\u907f\u5f00 TIMESTAMP \u7684 2038 \u4e0a\u9650；\u8de8\u56fd\u4e1a\u52a1\u7edf\u4e00\u5b58 UTC，\u5c55\u793a\u5c42\u518d\u8f6c\u65f6\u533a\u3002<\/p>\n

            2.5 JSON \u4e0e\u751f\u6210\u5217（8.0）<\/h4>\n

            8.0 \u7684 JSON \u4e0d\u662f\u7b80\u5355\u5b58\u4e2a\u5b57\u7b26\u4e32：\u5b83\u662f\u4e8c\u8fdb\u5236\u683c\u5f0f，\u80fd\u6309\u8def\u5f84\u67e5\u8be2\u3002\u4f46 JSON \u5217\u4e0d\u80fd\u76f4\u63a5\u5efa\u7d22\u5f15，\u5957\u8def\u662f\u300c\u751f\u6210\u5217 + \u7d22\u5f15\u300d：<\/p>\n

            ALTER<\/span> TABLE<\/span> products ADD<\/span> COLUMN<\/span> attrs JSON COMMENT<\/span> '\u6269\u5c55\u5c5e\u6027'<\/span>;<\/span><\/p>\n

            — \u628a\u5e38\u7528\u7684 JSON \u5b57\u6bb5\u7269\u5316\u6210\u751f\u6210\u5217，\u518d\u5efa\u7d22\u5f15<\/span>
            \nALTER<\/span> TABLE<\/span> products
            \n ADD<\/span> COLUMN<\/span> attrs_brand VARCHAR<\/span>(<\/span>50<\/span>)<\/span>
            \n GENERATED ALWAYS AS<\/span> (<\/span>JSON_UNQUOTE(<\/span>attrs –<\/span>>><\/span> '$.brand'<\/span>)<\/span>)<\/span> STORED,<\/span>
            \n ADD<\/span> INDEX<\/span> idx_attrs_brand (<\/span>attrs_brand)<\/span>;<\/span><\/p>\n

            SELECT<\/span> *<\/span> FROM<\/span> products WHERE<\/span> attrs_brand =<\/span> 'Apple'<\/span>;<\/span> — \u80fd\u8d70\u7d22\u5f15\u4e86<\/span><\/p>\n

            2.6 \u5efa\u8868\u89c4\u8303\u6e05\u5355（\u56e2\u961f\u901a\u7528\u7248）<\/h4>\n
              \n
            • \u5b57\u6bb5\u5c3d\u91cf NOT NULL + DEFAULT：NULL \u53c2\u4e0e\u6bd4\u8f83\u3001\u7edf\u8ba1\u3001\u7d22\u5f15\u90fd\u6709\u989d\u5916\u6210\u672c，COUNT(\u5b57\u6bb5) \u8fd8\u4f1a\u8df3\u8fc7 NULL \u884c；<\/li>\n
            • \u4e3b\u952e BIGINT UNSIGNED AUTO_INCREMENT，\u4fdd\u6301\u4e1a\u52a1\u65e0\u610f\u4e49；<\/li>\n
            • \u91d1\u989d DECIMAL(10,2)（\u6216\u5206\u5355\u4f4d BIGINT），\u7981 FLOAT\/DOUBLE；<\/li>\n
            • \u5b57\u7b26\u96c6 utf8mb4\u2014\u2014MySQL \u7684 utf8 \u662f\u5386\u53f2\u9609\u5272\u7248（utf8mb3），\u5b58\u4e0d\u4e86 emoji；<\/li>\n
            • \u6bcf\u8868\u6bcf\u5b57\u6bb5\u5199 COMMENT，\u534a\u5e74\u540e\u7684\u4f60\u4f1a\u611f\u8c22\u73b0\u5728\u7684\u4f60；<\/li>\n
            • created_at \/ updated_at \u4ea4\u7ed9\u9ed8\u8ba4\u503c\u81ea\u52a8\u7ef4\u62a4，\u5e94\u7528\u5c42\u5c11\u4e00\u6b21\u8d4b\u503c；<\/li>\n
            • \u5355\u8868\u5b57\u6bb5\u63a7\u5236\u5728 50 \u4e2a\u4ee5\u5185，TEXT \u5927\u5b57\u6bb5\u5782\u76f4\u62c6\u5206；<\/li>\n
            • \u4e0d\u5efa\u7269\u7406\u5916\u952e：\u5916\u952e\u5e26\u6765\u989d\u5916\u7684\u9501\u4e0e\u7ea7\u8054\u5f00\u9500\u3001DDL \u8026\u5408，\u5206\u5e93\u5206\u8868\u540e\u5b8c\u5168\u6ca1\u6cd5\u7528\u2014\u2014\u7528\u300c\u903b\u8f91\u5916\u952e + \u5e94\u7528\u5c42\u4fdd\u8bc1\u300d\u3002<\/li>\n<\/ul>\n

              \u8bb0\u5fc6\u951a\u70b9：\u7c7b\u578b\u9009\u578b\u4e00\u53e5\u8bdd\u2014\u2014\u80fd\u5c0f\u4e0d\u7528\u5927，\u80fd\u5b9a\u957f\u4e0d\u7528\u53d8\u957f，\u6570\u503c\u4f18\u4e8e\u5b57\u7b26\u4e32，NULL \u8d8a\u5c11\u8d8a\u597d\u3002\u7d22\u5f15\u5217\u5c24\u5176\u5982\u6b64：\u7c7b\u578b\u8d8a\u5c0f，\u4e00\u9875\u88c5\u5f97\u8d8a\u591a，\u6811\u8d8a\u77ee，\u67e5\u5f97\u8d8a\u5feb\u3002<\/p>\n

              \n

              \u4e09\u3001SQL \u6838\u5fc3\u8bed\u6cd5\u4e0e\u7535\u5546\u5efa\u5e93\u5b9e\u6218<\/h3>\n

              \u4ece\u8fd9\u4e00\u7ae0\u5f00\u59cb\u52a8\u624b：\u6211\u4eec\u5efa\u4e00\u4e2a\u8d2f\u7a7f\u5168\u6587\u7684\u7535\u5546\u5e93 mall，\u540e\u9762\u6240\u6709\u7684\u7d22\u5f15\u3001\u4e8b\u52a1\u3001\u9501\u3001\u4f18\u5316\u6848\u4f8b\u90fd\u5728\u8fd9\u4e94\u5f20\u8868\u4e0a\u53d1\u751f\u3002\u8bed\u6cd5\u53ea\u8bb2\u300c\u7528\u4e86\u4f1a\u8e29\u5751\u300d\u7684\u90e8\u5206，\u624b\u518c\u4e0a\u67e5\u5f97\u5230\u7684\u7565\u8fc7\u3002<\/p>\n

              3.1 \u5efa\u5e93\u5efa\u8868：\u4e94\u5f20\u8868\u7684\u5b8c\u6574 DDL<\/h4>\n

              CREATE<\/span> DATABASE<\/span> mall DEFAULT<\/span> CHARACTER<\/span> SET<\/span> utf8mb4 COLLATE<\/span> utf8mb4_unicode_ci;<\/span>
              \nUSE<\/span> mall;<\/span><\/p>\n

              CREATE<\/span> TABLE<\/span> users (<\/span>
              \n id BIGINT<\/span> UNSIGNED<\/span> AUTO_INCREMENT<\/span> COMMENT<\/span> '\u7528\u6237ID'<\/span>,<\/span>
              \n username VARCHAR<\/span>(<\/span>50<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u7528\u6237\u540d'<\/span>,<\/span>
              \n email VARCHAR<\/span>(<\/span>100<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u90ae\u7bb1'<\/span>,<\/span>
              \n status<\/span> TINYINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 1<\/span> COMMENT<\/span> '1\u6b63\u5e38 0\u7981\u7528'<\/span>,<\/span>
              \n created_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> COMMENT<\/span> '\u6ce8\u518c\u65f6\u95f4'<\/span>,<\/span>
              \n updated_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> ON<\/span> UPDATE<\/span> CURRENT_TIMESTAMP<\/span>,<\/span>
              \n PRIMARY<\/span> KEY<\/span> (<\/span>id)<\/span>,<\/span>
              \n UNIQUE<\/span> KEY<\/span> uk_username (<\/span>username)<\/span>,<\/span>
              \n UNIQUE<\/span> KEY<\/span> uk_email (<\/span>email)<\/span>
              \n)<\/span> ENGINE<\/span>=<\/span>InnoDB<\/span> COMMENT<\/span>=<\/span>'\u7528\u6237\u8868'<\/span>;<\/span><\/p>\n

              CREATE<\/span> TABLE<\/span> categories (<\/span> — \u6811\u5f62\u5206\u7c7b<\/span>
              \n id BIGINT<\/span> UNSIGNED<\/span> AUTO_INCREMENT<\/span> COMMENT<\/span> '\u5206\u7c7bID'<\/span>,<\/span>
              \n name VARCHAR<\/span>(<\/span>50<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u5206\u7c7b\u540d'<\/span>,<\/span>
              \n parent_id BIGINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> COMMENT<\/span> '\u7236\u5206\u7c7bID，0\u4e3a\u9876\u7ea7'<\/span>,<\/span>
              \n sort INT<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> COMMENT<\/span> '\u6392\u5e8f\u6743\u91cd'<\/span>,<\/span>
              \n PRIMARY<\/span> KEY<\/span> (<\/span>id)<\/span>,<\/span>
              \n KEY<\/span> idx_parent (<\/span>parent_id)<\/span>
              \n)<\/span> ENGINE<\/span>=<\/span>InnoDB<\/span> COMMENT<\/span>=<\/span>'\u5546\u54c1\u5206\u7c7b\u8868'<\/span>;<\/span><\/p>\n

              CREATE<\/span> TABLE<\/span> products (<\/span>
              \n id BIGINT<\/span> UNSIGNED<\/span> AUTO_INCREMENT<\/span> COMMENT<\/span> '\u5546\u54c1ID'<\/span>,<\/span>
              \n category_id BIGINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u5206\u7c7bID（\u903b\u8f91\u5916\u952e）'<\/span>,<\/span>
              \n name VARCHAR<\/span>(<\/span>100<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u5546\u54c1\u540d'<\/span>,<\/span>
              \n price DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u552e\u4ef7'<\/span>,<\/span>
              \n stock INT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> COMMENT<\/span> '\u5e93\u5b58'<\/span>,<\/span>
              \n sales INT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> COMMENT<\/span> '\u7d2f\u8ba1\u9500\u91cf'<\/span>,<\/span>
              \n status<\/span> TINYINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 1<\/span> COMMENT<\/span> '1\u4e0a\u67b6 0\u4e0b\u67b6'<\/span>,<\/span>
              \n created_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>,<\/span>
              \n updated_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> ON<\/span> UPDATE<\/span> CURRENT_TIMESTAMP<\/span>,<\/span>
              \n PRIMARY<\/span> KEY<\/span> (<\/span>id)<\/span>,<\/span>
              \n KEY<\/span> idx_category (<\/span>category_id)<\/span>,<\/span>
              \n KEY<\/span> idx_status_created (<\/span>status<\/span>,<\/span> created_at)<\/span>
              \n)<\/span> ENGINE<\/span>=<\/span>InnoDB<\/span> COMMENT<\/span>=<\/span>'\u5546\u54c1\u8868'<\/span>;<\/span><\/p>\n

              CREATE<\/span> TABLE<\/span> orders (<\/span>
              \n id BIGINT<\/span> UNSIGNED<\/span> AUTO_INCREMENT<\/span> COMMENT<\/span> '\u8ba2\u5355ID'<\/span>,<\/span>
              \n order_no VARCHAR<\/span>(<\/span>32<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u4e1a\u52a1\u8ba2\u5355\u53f7'<\/span>,<\/span>
              \n user_id BIGINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u4e0b\u5355\u7528\u6237（\u903b\u8f91\u5916\u952e）'<\/span>,<\/span>
              \n total_amount DECIMAL<\/span>(<\/span>12<\/span>,<\/span>2<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u8ba2\u5355\u603b\u989d'<\/span>,<\/span>
              \n status<\/span> TINYINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> COMMENT<\/span> '0\u5f85\u652f\u4ed8 1\u5df2\u652f\u4ed8 2\u5df2\u53d1\u8d27 3\u5df2\u5b8c\u6210 4\u5df2\u53d6\u6d88'<\/span>,<\/span>
              \n pay_time DATETIME<\/span> NULL<\/span> COMMENT<\/span> '\u652f\u4ed8\u65f6\u95f4'<\/span>,<\/span>
              \n created_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> COMMENT<\/span> '\u4e0b\u5355\u65f6\u95f4'<\/span>,<\/span>
              \n updated_at DATETIME<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> ON<\/span> UPDATE<\/span> CURRENT_TIMESTAMP<\/span>,<\/span>
              \n PRIMARY<\/span> KEY<\/span> (<\/span>id)<\/span>,<\/span>
              \n UNIQUE<\/span> KEY<\/span> uk_order_no (<\/span>order_no)<\/span>,<\/span>
              \n KEY<\/span> idx_user_created (<\/span>user_id,<\/span> created_at)<\/span>,<\/span>
              \n KEY<\/span> idx_status_created (<\/span>status<\/span>,<\/span> created_at)<\/span>
              \n)<\/span> ENGINE<\/span>=<\/span>InnoDB<\/span> COMMENT<\/span>=<\/span>'\u8ba2\u5355\u8868'<\/span>;<\/span><\/p>\n

              CREATE<\/span> TABLE<\/span> order_items (<\/span>
              \n id BIGINT<\/span> UNSIGNED<\/span> AUTO_INCREMENT<\/span> COMMENT<\/span> '\u660e\u7ec6ID'<\/span>,<\/span>
              \n order_id BIGINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u8ba2\u5355ID（\u903b\u8f91\u5916\u952e）'<\/span>,<\/span>
              \n product_id BIGINT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u5546\u54c1ID（\u903b\u8f91\u5916\u952e）'<\/span>,<\/span>
              \n quantity INT<\/span> UNSIGNED<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 1<\/span> COMMENT<\/span> '\u8d2d\u4e70\u6570\u91cf'<\/span>,<\/span>
              \n price DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span> NOT<\/span> NULL<\/span> COMMENT<\/span> '\u6210\u4ea4\u5355\u4ef7（\u4e0b\u5355\u65f6\u5feb\u7167）'<\/span>,<\/span>
              \n PRIMARY<\/span> KEY<\/span> (<\/span>id)<\/span>,<\/span>
              \n KEY<\/span> idx_order (<\/span>order_id)<\/span>,<\/span>
              \n KEY<\/span> idx_product (<\/span>product_id)<\/span>
              \n)<\/span> ENGINE<\/span>=<\/span>InnoDB<\/span> COMMENT<\/span>=<\/span>'\u8ba2\u5355\u660e\u7ec6\u8868'<\/span>;<\/span><\/p>\n

              \u4e09\u4e2a\u8bbe\u8ba1\u51b3\u7b56\u503c\u5f97\u505c\u4e0b\u6765\u60f3\u4e00\u60f3，\u5b83\u4eec\u662f\u6240\u6709\u4e1a\u52a1\u8868\u8bbe\u8ba1\u7684\u901a\u7528\u5957\u8def：<\/p>\n

                \n
              • order_items.price \u5b58\u300c\u5feb\u7167\u300d：\u5546\u54c1\u4ef7\u683c\u4f1a\u53d8，\u5386\u53f2\u8ba2\u5355\u5fc5\u987b\u8bb0\u5f53\u65f6\u7684\u6210\u4ea4\u4ef7，\u4e0d\u80fd JOIN products \u73b0\u4ef7\u6765\u7b97\u2014\u2014\u8d26\u5355\u4f1a\u9519；<\/li>\n
              • orders.total_amount \u5b58\u300c\u5197\u4f59\u300d：\u660e\u660e\u80fd SUM(\u660e\u7ec6) \u7b97\u51fa\u6765，\u4f46\u8ba2\u5355\u9875\u662f\u9ad8\u9891\u8bfb，\u5197\u4f59\u4e00\u5217\u6362\u53d6\u514d\u805a\u5408，\u8bfb\u591a\u5199\u5c11\u573a\u666f\u7684\u6807\u51c6\u53d6\u820d；<\/li>\n
              • \u53ea\u6709\u903b\u8f91\u5916\u952e：\u5b57\u6bb5\u4e0a\u52a0\u4e86\u7d22\u5f15（\u5982 idx_user_created），\u4f46\u4e0d\u5efa FOREIGN KEY \u7ea6\u675f，\u7406\u7531\u89c1 2.6\u3002<\/li>\n<\/ul>\n

                3.2 DDL：ALTER \u7684\u5751\u4e0e 8.0 \u7684\u751c<\/h4>\n

                ALTER<\/span> TABLE<\/span> products ADD<\/span> COLUMN<\/span> subtitle VARCHAR<\/span>(<\/span>100<\/span>)<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> ''<\/span> COMMENT<\/span> '\u526f\u6807\u9898'<\/span>;<\/span>
                \n— 8.0 \u52a0\u5217\u662f INSTANT \u7b97\u6cd5：\u53ea\u6539\u5143\u6570\u636e，\u5343\u4e07\u884c\u5927\u8868\u4e5f\u662f\u79d2\u7ea7<\/span><\/p>\n

                ALTER<\/span> TABLE<\/span> products ADD<\/span> INDEX<\/span> idx_name (<\/span>name)<\/span>;<\/span> — INPLACE，\u53ef\u5728\u7ebf\u52a0<\/span>
                \nALTER<\/span> TABLE<\/span> products DROP<\/span> INDEX<\/span> idx_name;<\/span>
                \nALTER<\/span> TABLE<\/span> products
                \n MODIFY<\/span> COLUMN<\/span> subtitle VARCHAR<\/span>(<\/span>200<\/span>)<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> ''<\/span> COMMENT<\/span> '\u526f\u6807\u9898'<\/span>;<\/span>
                \n— \u6ce8\u610f：\u6539\u5217\u7c7b\u578b\/\u957f\u5ea6\u65e0\u6cd5 INSTANT，\u5927\u8868\u4f1a\u9501\u6216\u957f\u65f6\u95f4 INPLACE，\u52a1\u5fc5\u4f4e\u5cf0\u64cd\u4f5c<\/span><\/p>\n

                \u6613\u9519\u70b9：\u7ebf\u4e0a ALTER \u524d\u5148\u770b\u7b97\u6cd5\u3002 \u6267\u884c\u524d\u7528 ALTER TABLE … , ALGORITHM=INSTANT \u8bd5\u63a2：\u652f\u6301\u5c31\u79d2\u56de；\u4e0d\u652f\u6301\u4f1a\u76f4\u63a5\u62a5\u9519\u800c\u4e0d\u662f\u9ed8\u9ed8\u9501\u8868\u3002\u5927\u8868\u53d8\u66f4\u63a8\u8350 gh-ost \/ pt-online-schema-change \u5de5\u5177\u3002<\/p>\n

                3.3 DML：\u589e\u5220\u6539\u7684\u56db\u4e2a\u7ec6\u8282<\/h4>\n

                — \u2460 \u6279\u91cf\u63d2\u5165：\u4e00\u6761 SQL \u63d2\u591a\u884c，\u6bd4\u5faa\u73af\u5355\u63d2\u5feb\u4e00\u4e2a\u6570\u91cf\u7ea7<\/span>
                \nINSERT<\/span> INTO<\/span> users (<\/span>username,<\/span> email)<\/span> VALUES<\/span>
                \n (<\/span>'alice'<\/span>,<\/span> 'alice@test.com'<\/span>)<\/span>,<\/span>
                \n (<\/span>'bob'<\/span>,<\/span> 'bob@test.com'<\/span>)<\/span>;<\/span><\/p>\n

                — \u2461 \u5b58\u5728\u5219\u66f4\u65b0（8.0.20+ \u884c\u522b\u540d\u5199\u6cd5，VALUES() \u5199\u6cd5\u5df2\u5e9f\u5f03）<\/span>
                \nINSERT<\/span> INTO<\/span> products (<\/span>id,<\/span> name,<\/span> price,<\/span> stock)<\/span> VALUES<\/span>
                \n (<\/span>1<\/span>,<\/span> 'iPhone 17'<\/span>,<\/span> 6999.00<\/span>,<\/span> 100<\/span>)<\/span> AS<\/span> new
                \nON<\/span> DUPLICATE<\/span> KEY<\/span> UPDATE<\/span> stock =<\/span> stock +<\/span> new.<\/span>stock;<\/span><\/p>\n

                — \u2462 UPDATE \u6761\u4ef6\u5199\u5728 WHERE \u91cc，\u987a\u624b\u9632\u8d85\u5356（\u7b2c 7 \u7ae0\u5c55\u5f00）<\/span>
                \nUPDATE<\/span> products SET<\/span> stock =<\/span> stock –<\/span> 1<\/span> WHERE<\/span> id =<\/span> 1<\/span> AND<\/span> stock >=<\/span> 1<\/span>;<\/span><\/p>\n

                — \u2463 \u5b89\u5168\u5e26：\u5f00\u542f\u540e，\u6ca1\u5199 WHERE \u6216 WHERE \u6ca1\u8d70\u7d22\u5f15\u5217\u7684 UPDATE\/DELETE \u76f4\u63a5\u62a5\u9519<\/span>
                \nSET<\/span> SQL_SAFE_UPDATES =<\/span> 1<\/span>;<\/span><\/p>\n\n\u7ef4\u5ea6DELETETRUNCATE<\/tr>\n\n\n\n\n\n\n
                WHERE \u6761\u4ef6<\/td>\n\u652f\u6301，\u9010\u884c\u5220<\/td>\n\u4e0d\u652f\u6301，\u6e05\u7a7a\u5168\u8868<\/td>\n<\/tr>\n
                \u4e8b\u52a1\u56de\u6eda<\/td>\n\u53ef\u4ee5（\u8bb0\u5f55 undo）<\/td>\n\u5c5e\u4e8e DDL，\u4e0d\u53ef\u56de\u6eda<\/td>\n<\/tr>\n
                \u81ea\u589e\u503c<\/td>\n\u4fdd\u7559<\/td>\n\u91cd\u7f6e\u4e3a 1<\/td>\n<\/tr>\n
                \u89e6\u53d1\u5668<\/td>\n\u89e6\u53d1 DELETE \u89e6\u53d1\u5668<\/td>\n\u4e0d\u89e6\u53d1<\/td>\n<\/tr>\n
                \u901f\u5ea6<\/td>\n\u6162（\u9010\u884c + binlog）<\/td>\n\u5feb（\u91cd\u5efa\u8868）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                3.4 DCL：\u7ed9\u5e94\u7528\u8d26\u53f7\u6700\u5c0f\u6743\u9650<\/h4>\n

                CREATE<\/span> USER<\/span> 'mall_app'<\/span>@'192.168.1.%'<\/span> IDENTIFIED BY<\/span> '\u8bf7\u7528\u5f3a\u5bc6\u7801'<\/span>;<\/span>
                \nGRANT<\/span> SELECT<\/span>,<\/span> INSERT<\/span>,<\/span> UPDATE<\/span>,<\/span> DELETE<\/span> ON<\/span> mall.<\/span>*<\/span> TO<\/span> 'mall_app'<\/span>@'192.168.1.%'<\/span>;<\/span>
                \nSHOW<\/span> GRANTS FOR<\/span> 'mall_app'<\/span>@'192.168.1.%'<\/span>;<\/span> — \u68c0\u67e5\u6388\u4e88\u7ed3\u679c<\/span>
                \nREVOKE<\/span> DELETE<\/span> ON<\/span> mall.<\/span>*<\/span> FROM<\/span> 'mall_app'<\/span>@'192.168.1.%'<\/span>;<\/span> — \u6536\u56de\u8bef\u6388\u6743\u9650<\/span><\/p>\n

                  \n
                • \u5e94\u7528\u8d26\u53f7\u53ea\u7ed9\u8868\u7684\u589e\u5220\u6539\u67e5，\u6c38\u8fdc\u4e0d\u7ed9 DROP \/ ALTER \/ GRANT；<\/li>\n
                • \u62a5\u8868\/\u770b\u677f\u8d26\u53f7\u7ed9\u53ea\u8bfb GRANT SELECT；<\/li>\n
                • GRANT\/REVOKE \u7acb\u5373\u751f\u6548，\u4e0d\u9700\u8981 FLUSH PRIVILEGES（\u90a3\u662f\u76f4\u63a5\u6539 mysql.user \u8868\u65f6\u7684\u8001\u89c4\u77e9）；<\/li>\n
                • \u8d26\u53f7\u6309\u300c\u7528\u9014 + \u6765\u6e90\u7f51\u6bb5\u300d\u6536\u7d27：mall_app \u53ea\u5141\u8bb8\u4ece\u5e94\u7528\u7f51\u6bb5\u8fde\u3002<\/li>\n<\/ul>\n

                  3.5 \u5e38\u7528\u51fd\u6570\u901f\u67e5<\/h4>\n\n\u7c7b\u522b\u51fd\u6570\u4e00\u53e5\u8bdd\u8bf4\u660e<\/tr>\n\n\n\n\n\n\n\n
                  \u5b57\u7b26\u4e32<\/td>\nCONCAT \/ SUBSTRING \/ UPPER \/ TRIM<\/td>\n\u62fc\u63a5\u3001\u622a\u53d6\u3001\u5927\u5c0f\u5199\u3001\u53bb\u7a7a\u683c<\/td>\n<\/tr>\n
                  \u65e5\u671f<\/td>\nNOW() \/ DATE_FORMAT \/ DATE_ADD \/ DATEDIFF<\/td>\n\u5f53\u524d\u65f6\u95f4\u3001\u683c\u5f0f\u5316\u3001\u504f\u79fb\u3001\u5dee\u503c<\/td>\n<\/tr>\n
                  \u63a7\u5236\u6d41<\/td>\nIFNULL \/ CASE WHEN<\/td>\nNULL \u515c\u5e95\u3001\u591a\u6761\u4ef6\u5206\u652f<\/td>\n<\/tr>\n
                  \u805a\u5408<\/td>\nCOUNT \/ SUM \/ AVG \/ MIN \/ MAX<\/td>\n\u7b2c 4 \u7ae0\u914d\u5408 GROUP BY \u4f7f\u7528<\/td>\n<\/tr>\n
                  \u6570\u503c<\/td>\nROUND \/ CEIL \/ FLOOR \/ ABS<\/td>\n\u56db\u820d\u4e94\u5165\u3001\u4e0a\u4e0b\u53d6\u6574\u3001\u7edd\u5bf9\u503c<\/td>\n<\/tr>\n
                  JSON<\/td>\n-> \/ ->> \/ JSON_EXTRACT<\/td>\n\u8def\u5f84\u53d6\u503c，->> \u987a\u5e26\u53bb\u5f15\u53f7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                  SELECT<\/span> DATE_FORMAT(<\/span>NOW<\/span>(<\/span>)<\/span>,<\/span> '%Y-%m-%d %H:%i'<\/span>)<\/span>;<\/span> — '2026-09-25 14:30'<\/span>
                  \nSELECT<\/span> DATE_ADD(<\/span>NOW<\/span>(<\/span>)<\/span>,<\/span> INTERVAL<\/span> 7<\/span> DAY<\/span>)<\/span>;<\/span> — \u4e00\u5468\u540e<\/span>
                  \nSELECT<\/span> DATEDIFF(<\/span>'2026-09-25'<\/span>,<\/span> '2026-09-01'<\/span>)<\/span>;<\/span> — 24（\u53ea\u6309\u65e5\u671f\u7b97\u5929\u6570）<\/span>
                  \nSELECT<\/span> IFNULL(<\/span>NULL<\/span>,<\/span> '\u9ed8\u8ba4\u503c'<\/span>)<\/span>;<\/span> — '\u9ed8\u8ba4\u503c'<\/span>
                  \nSELECT<\/span> CASE<\/span> WHEN<\/span> status<\/span> =<\/span> 1<\/span> THEN<\/span> '\u6b63\u5e38'<\/span> ELSE<\/span> '\u7981\u7528'<\/span> END<\/span> FROM<\/span> users;<\/span><\/p>\n

                  \n

                  \u56db\u3001\u67e5\u8be2\u8fdb\u9636：JOIN\u3001\u7a97\u53e3\u51fd\u6570\u4e0e\u7ecf\u5178\u4e1a\u52a1\u9898<\/h3>\n

                  \u4e1a\u52a1 SQL \u7684\u590d\u6742\u5ea6 80% \u6765\u81ea\u300c\u591a\u8868 + \u5206\u7ec4 + \u6392\u540d\u300d\u3002\u8fd9\u4e00\u7ae0\u628a JOIN\u3001\u5b50\u67e5\u8be2\u3001\u5206\u7ec4\u3001\u7a97\u53e3\u51fd\u6570\u4e00\u53e3\u6c14\u8fc7\u5b8c，\u6700\u540e\u7528\u56db\u9053\u7ecf\u5178\u4e1a\u52a1\u9898\u6536\u5c3e\u2014\u2014\u6bcf\u4e00\u9053\u90fd\u662f\u9762\u8bd5\u548c\u5b9e\u6218\u7684\u9ad8\u9891\u5ba2\u3002<\/p>\n

                  4.1 JOIN：INNER \u548c LEFT \u5c31\u591f\u7528\u4e86<\/h4>\n

                  — INNER JOIN：\u53d6\u4ea4\u96c6\u3002\u5df2\u652f\u4ed8\u8ba2\u5355 + \u4e0b\u5355\u7528\u6237\u540d<\/span>
                  \nSELECT<\/span> o.<\/span>order_no,<\/span> u.<\/span>username,<\/span> o.<\/span>total_amount
                  \nFROM<\/span> orders o
                  \nINNER<\/span> JOIN<\/span> users u ON<\/span> o.<\/span>user_id =<\/span> u.<\/span>id
                  \nWHERE<\/span> o.<\/span>status<\/span> >=<\/span> 1<\/span>;<\/span><\/p>\n

                  — LEFT JOIN：\u4fdd\u7559\u5de6\u8868\u5168\u90e8\u3002\u6240\u6709\u5546\u54c1\u5404\u81ea\u7684\u9500\u91cf（\u5305\u62ec\u4e00\u4ef6\u6ca1\u5356\u51fa\u53bb\u7684）<\/span>
                  \nSELECT<\/span> p.<\/span>id,<\/span> p.<\/span>name,<\/span> IFNULL(<\/span>SUM<\/span>(<\/span>oi.<\/span>quantity)<\/span>,<\/span> 0<\/span>)<\/span> AS<\/span> sold
                  \nFROM<\/span> products p
                  \nLEFT<\/span> JOIN<\/span> order_items oi ON<\/span> oi.<\/span>product_id =<\/span> p.<\/span>id
                  \nGROUP<\/span> BY<\/span> p.<\/span>id,<\/span> p.<\/span>name;<\/span><\/p>\n

                  RIGHT JOIN \u5728\u5b9e\u9645\u4ee3\u7801\u91cc\u51e0\u4e4e\u4e0d\u51fa\u73b0\u2014\u2014\u628a\u4e24\u5f20\u8868\u6362\u4e2a\u4f4d\u7f6e\u5199 LEFT \u5c31\u884c\u3002\u771f\u6b63\u5bb9\u6613\u51fa\u9519\u7684\u662f\u4e0b\u9762\u8fd9\u4e2a\u7ec6\u8282：<\/p>\n

                  \u6613\u9519\u70b9：LEFT JOIN \u7684\u8fc7\u6ee4\u6761\u4ef6\u5199 ON \u8fd8\u662f WHERE？<\/p>\n

                  \u5bf9\u53f3\u8868\u7684\u8fc7\u6ee4\u6761\u4ef6\u5199\u5728 ON \u91cc：\u5de6\u8868\u884c\u5168\u4fdd\u7559，\u4e0d\u5339\u914d\u7684\u8865 NULL（\u300c\u6bcf\u4e2a\u5546\u54c1\u53ca\u5176\u9ad8\u4ef7\u8ba2\u5355，\u6ca1\u5356\u8fc7\u9ad8\u4ef7\u7684\u663e\u793a NULL\u300d）\u3002\u5199\u5728 WHERE \u91cc：NULL \u884c\u4f1a\u88ab\u8fc7\u6ee4\u6389，LEFT JOIN \u6084\u6084\u9000\u5316\u6210 INNER JOIN\u3002\u8fd9\u662f\u7edf\u8ba1 SQL \u51fa\u300c\u6570\u636e\u53d8\u5c11\u300dbug \u7684\u5934\u53f7\u539f\u56e0\u3002<\/p>\n

                  — ON \u8fc7\u6ee4\u53f3\u8868：\u4fdd\u7559\u6240\u6709\u5546\u54c1（\u6b63\u786e\u59ff\u52bf）<\/span>
                  \nFROM<\/span> products p LEFT<\/span> JOIN<\/span> order_items oi
                  \n ON<\/span> oi.<\/span>product_id =<\/span> p.<\/span>id AND<\/span> oi.<\/span>price ><\/span> 100<\/span><\/p>\n

                  — WHERE \u8fc7\u6ee4：\u5356\u4e0d\u51fa\u9ad8\u4ef7\u7684\u5546\u54c1\u76f4\u63a5\u6d88\u5931（LEFT \u767d\u5199\u4e86）<\/span>
                  \nFROM<\/span> products p LEFT<\/span> JOIN<\/span> order_items oi
                  \n ON<\/span> oi.<\/span>product_id =<\/span> p.<\/span>id
                  \nWHERE<\/span> oi.<\/span>price ><\/span> 100<\/span><\/p>\n

                  4.2 \u5b50\u67e5\u8be2：IN \u8fd8\u662f EXISTS<\/h4>\n

                  — \u4e0b\u8fc7\u5355\u7684\u7528\u6237：IN \u5199\u6cd5<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> users u
                  \nWHERE<\/span> u.<\/span>id IN<\/span> (<\/span>SELECT<\/span> user_id FROM<\/span> orders)<\/span>;<\/span><\/p>\n

                  — EXISTS \u5199\u6cd5<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> users u
                  \nWHERE<\/span> EXISTS<\/span> (<\/span>SELECT<\/span> 1<\/span> FROM<\/span> orders o WHERE<\/span> o.<\/span>user_id =<\/span> u.<\/span>id)<\/span>;<\/span><\/p>\n

                  \u7ecf\u5178\u7ed3\u8bba\u662f**\u300c\u5c0f\u8868\u9a71\u52a8\u5927\u8868\u300d**：\u5b50\u67e5\u8be2\u7ed3\u679c\u96c6\u5c0f\u7528 IN（\u5916\u5c42\u5927\u8868\u88ab\u5c0f\u96c6\u5408\u8fc7\u6ee4）；\u5916\u8868\u5c0f\u3001\u5185\u5c42\u5927\u4e14\u5185\u5c42\u8fde\u63a5\u5217\u6709\u7d22\u5f15\u65f6 EXISTS \u66f4\u5feb（\u5916\u8868\u6bcf\u884c\u53bb\u5185\u5c42\u7d22\u5f15\u63a2\u6d4b\u4e00\u6b21）\u30028.0 \u7684\u4f18\u5316\u5668\u5728\u591a\u6570\u573a\u666f\u4f1a\u81ea\u52a8\u6539\u5199\u4e24\u8005，\u4f18\u5148\u4fdd\u8bc1\u8bed\u4e49\u6e05\u6670，\u522b\u4e3a\u60f3\u8c61\u4e2d\u7684\u6027\u80fd\u727a\u7272\u53ef\u8bfb\u6027\u3002<\/p>\n

                  4.3 GROUP BY \u4e0e HAVING：\u9500\u552e\u65e5\u62a5<\/h4>\n

                  SELECT<\/span> DATE<\/span>(<\/span>o.<\/span>pay_time)<\/span> AS<\/span> day<\/span>,<\/span>
                  \n COUNT<\/span>(<\/span>*<\/span>)<\/span> AS<\/span> order_cnt,<\/span>
                  \n SUM<\/span>(<\/span>o.<\/span>total_amount)<\/span> AS<\/span> gmv,<\/span>
                  \n ROUND<\/span>(<\/span>AVG<\/span>(<\/span>o.<\/span>total_amount)<\/span>,<\/span> 2<\/span>)<\/span> AS<\/span> avg_amount
                  \nFROM<\/span> orders o
                  \nWHERE<\/span> o.<\/span>status<\/span> >=<\/span> 1<\/span> — \u53ea\u7edf\u8ba1\u6709\u6548\u8ba2\u5355<\/span>
                  \nGROUP<\/span> BY<\/span> DATE<\/span>(<\/span>o.<\/span>pay_time)<\/span> — \u6309\u652f\u4ed8\u65e5\u5206\u7ec4<\/span>
                  \nHAVING<\/span> SUM<\/span>(<\/span>o.<\/span>total_amount)<\/span> ><\/span> 10000<\/span> — \u53ea\u8981 GMV \u8fc7\u4e07\u7684\u65e5\u5b50<\/span>
                  \nORDER<\/span> BY<\/span> day<\/span> DESC<\/span>;<\/span><\/p>\n

                  \u8bb0\u5fc6\u951a\u70b9：\u4e00\u6761 SELECT \u7684\u6267\u884c\u987a\u5e8f\u2014\u2014FROM \u2192 WHERE \u2192 GROUP BY \u2192 HAVING \u2192 SELECT \u2192 ORDER BY \u2192 LIMIT\u3002 WHERE \u8fc7\u6ee4\u300c\u884c\u300d（\u4e0d\u80fd\u7528\u805a\u5408\u51fd\u6570），HAVING \u8fc7\u6ee4\u300c\u7ec4\u300d（\u53ef\u4ee5\u7528\u805a\u5408\u51fd\u6570）\u3002\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48 SELECT \u91cc\u8d77\u7684\u522b\u540d\u5728 WHERE \u91cc\u7528\u4e0d\u4e86\u3001\u5728 ORDER BY \u91cc\u80fd\u7528\u3002<\/p>\n

                  \u6613\u9519\u70b9：ONLY_FULL_GROUP_BY\u3002 8.0 \u9ed8\u8ba4\u5f00\u542f\u8be5 sql_mode：SELECT \u91cc\u51fa\u73b0\u7684\u975e\u805a\u5408\u5217\u5fc5\u987b\u51fa\u73b0\u5728 GROUP BY \u91cc，\u5426\u5219\u62a5 1055 \u9519\u8bef\u3002\u522b\u6025\u7740\u5173\u5b83\u2014\u2014\u5b83\u9632\u7684\u662f\u300c\u4e00\u884c\u7ed3\u679c\u6df7\u5165\u522b\u7684\u884c\u7684\u503c\u300d\u8fd9\u79cd\u903b\u8f91\u70b8\u5f39\u3002<\/p>\n

                  4.4 \u7a97\u53e3\u51fd\u6570（8.0）：\u6392\u540d\u795e\u5668<\/h4>\n

                  \u7a97\u53e3\u51fd\u6570 = \u300c\u4e0d\u5408\u5e76\u884c\u7684 GROUP BY\u300d：\u5206\u7ec4\u805a\u5408\u4f1a\u628a N \u884c\u538b\u6210 1 \u884c，\u7a97\u53e3\u51fd\u6570\u5728\u6bcf\u884c\u65c1\u8fb9\u9644\u4e0a\u4e00\u4e2a\u8ba1\u7b97\u7ed3\u679c\u3002\u5148\u8ba4\u4e09\u5144\u5f1f：<\/p>\n

                  SELECT<\/span> name,<\/span> score,<\/span>
                  \n ROW_NUMBER(<\/span>)<\/span> OVER<\/span> (<\/span>ORDER<\/span> BY<\/span> score DESC<\/span>)<\/span> AS<\/span> rn,<\/span> — 1 2 3 4（\u5e76\u5217\u4e5f\u786c\u6392）<\/span>
                  \n RANK(<\/span>)<\/span> OVER<\/span> (<\/span>ORDER<\/span> BY<\/span> score DESC<\/span>)<\/span> AS<\/span> rk,<\/span> — 1 2 2 4（\u5e76\u5217\u540c\u540d，\u8df3\u53f7）<\/span>
                  \n DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>ORDER<\/span> BY<\/span> score DESC<\/span>)<\/span> AS<\/span> dr — 1 2 2 3（\u5e76\u5217\u540c\u540d，\u4e0d\u8df3\u53f7）<\/span>
                  \nFROM<\/span> students;<\/span><\/p>\n

                  \u914d PARTITION BY \u5c31\u662f\u300c\u5206\u7ec4\u5185\u6392\u540d\u300d\u2014\u2014\u76f4\u63a5\u89e3\u6389\u6700\u7ecf\u5178\u7684\u4e1a\u52a1\u9898：<\/p>\n

                  — \u7ecf\u5178\u9898 1：\u6bcf\u4e2a\u5206\u7c7b\u9500\u91cf Top3 \u7684\u5546\u54c1<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> (<\/span>
                  \n SELECT<\/span> p.<\/span>id,<\/span> p.<\/span>name,<\/span> p.<\/span>category_id,<\/span> p.<\/span>sales,<\/span>
                  \n ROW_NUMBER(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION<\/span> BY<\/span> p.<\/span>category_id ORDER<\/span> BY<\/span> p.<\/span>sales DESC<\/span>)<\/span> AS<\/span> rn
                  \n FROM<\/span> products p
                  \n)<\/span> t
                  \nWHERE<\/span> t.<\/span>rn <=<\/span> 3<\/span>;<\/span><\/p>\n

                  — \u7ecf\u5178\u9898 2：\u6bcf\u65e5 GMV \u4e0e\u7d2f\u8ba1 GMV（\u5148\u6309\u5929\u805a\u5408，\u518d\u5bf9\u805a\u5408\u7ed3\u679c\u5f00\u7a97）<\/span>
                  \nSELECT<\/span> DATE<\/span>(<\/span>pay_time)<\/span> AS<\/span> day<\/span>,<\/span>
                  \n SUM<\/span>(<\/span>total_amount)<\/span> AS<\/span> daily,<\/span>
                  \n SUM<\/span>(<\/span>SUM<\/span>(<\/span>total_amount)<\/span>)<\/span> OVER<\/span> (<\/span>ORDER<\/span> BY<\/span> DATE<\/span>(<\/span>pay_time)<\/span>)<\/span> AS<\/span> cumulative
                  \nFROM<\/span> orders
                  \nWHERE<\/span> status<\/span> >=<\/span> 1<\/span>
                  \nGROUP<\/span> BY<\/span> DATE<\/span>(<\/span>pay_time)<\/span>;<\/span><\/p>\n

                  \u7b2c\u4e8c\u9898\u91cc SUM(SUM(…)) OVER \u770b\u7740\u5413\u4eba：\u5185\u5c42 SUM \u5148\u5b8c\u6210\u300c\u6309\u5929\u5206\u7ec4\u300d，\u5916\u5c42\u7a97\u53e3\u5bf9\u5206\u7ec4\u7ed3\u679c\u505a\u300c\u6309\u5929\u7d2f\u52a0\u300d\u3002\u7a97\u53e3\u51fd\u6570\u5728 GROUP BY \u4e4b\u540e\u6267\u884c，\u6240\u4ee5\u805a\u5408\u51fd\u6570\u53ef\u4ee5\u6574\u4f53\u4f5c\u4e3a\u7a97\u53e3\u7684\u8f93\u5165\u3002<\/p>\n

                  4.5 \u9012\u5f52 CTE：\u67e5\u6574\u68f5\u5206\u7c7b\u6811<\/h4>\n

                  categories \u662f\u6811\u5f62\u7ed3\u6784（parent_id \u6307\u5411\u7236\u8282\u70b9），\u67e5\u51fa\u300c\u67d0\u5206\u7c7b\u7684\u6240\u6709\u5b50\u5b59\u300d\u6216\u6574\u68f5\u6811，8.0 \u4e00\u4e2a\u9012\u5f52 CTE \u641e\u5b9a：<\/p>\n

                  WITH<\/span> RECURSIVE tree AS<\/span> (<\/span>
                  \n — \u951a\u70b9：\u9876\u7ea7\u5206\u7c7b<\/span>
                  \n SELECT<\/span> id,<\/span> name,<\/span> parent_id,<\/span> 1<\/span> AS<\/span> lvl,<\/span> CAST(<\/span>id AS<\/span> CHAR<\/span>(<\/span>200<\/span>)<\/span>)<\/span> AS<\/span> path
                  \n FROM<\/span> categories WHERE<\/span> parent_id =<\/span> 0<\/span>
                  \n UNION<\/span> ALL<\/span>
                  \n — \u9012\u5f52：\u4e0d\u65ad\u627e\u4e0a\u4e00\u5c42\u8282\u70b9\u7684\u5b69\u5b50<\/span>
                  \n SELECT<\/span> c.<\/span>id,<\/span> c.<\/span>name,<\/span> c.<\/span>parent_id,<\/span> tree.<\/span>lvl +<\/span> 1<\/span>,<\/span> CONCAT(<\/span>tree.<\/span>path,<\/span> '-'<\/span>,<\/span> c.<\/span>id)<\/span>
                  \n FROM<\/span> categories c
                  \n INNER<\/span> JOIN<\/span> tree ON<\/span> c.<\/span>parent_id =<\/span> tree.<\/span>id
                  \n)<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> tree ORDER<\/span> BY<\/span> path;<\/span> — path \u524d\u7f00\u5929\u7136\u6309\u6811\u5f62\u6392\u5e8f<\/span><\/p>\n

                  4.6 \u4e24\u9053\u538b\u8f74\u5b9e\u6218\u9898<\/h4>\n

                  \u538b\u8f74 1：\u6df1\u5206\u9875\u4e3a\u4ec0\u4e48\u6162\u3001\u600e\u4e48\u6551<\/p>\n

                  — \u6162：LIMIT 1000000, 20 \u8981\u53d6 1000020 \u884c\u3001\u4e22\u6389\u524d 100 \u4e07\u884c<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> orders ORDER<\/span> BY<\/span> id LIMIT<\/span> 1000000<\/span>,<\/span> 20<\/span>;<\/span><\/p>\n

                  — \u4f18\u5316 A：\u6e38\u6807\/\u4e66\u7b7e\u7ffb\u9875\u2014\u2014\u8bb0\u4f4f\u4e0a\u4e00\u9875\u6700\u540e\u7684 id（\u8981\u6c42\u6392\u5e8f\u952e\u8fde\u7eed\u9012\u589e）<\/span>
                  \nSELECT<\/span> *<\/span> FROM<\/span> orders WHERE<\/span> id ><\/span> 1000000<\/span> ORDER<\/span> BY<\/span> id LIMIT<\/span> 20<\/span>;<\/span><\/p>\n

                  — \u4f18\u5316 B：\u5ef6\u8fdf\u5173\u8054\u2014\u2014\u5148\u7528\u8986\u76d6\u7d22\u5f15\u628a 20 \u4e2a id \u627e\u51fa\u6765，\u518d\u56de\u8868\u53d6\u6574\u884c<\/span>
                  \nSELECT<\/span> o.<\/span>*<\/span>
                  \nFROM<\/span> orders o
                  \nINNER<\/span> JOIN<\/span> (<\/span>SELECT<\/span> id FROM<\/span> orders ORDER<\/span> BY<\/span> id LIMIT<\/span> 1000000<\/span>,<\/span> 20<\/span>)<\/span> t
                  \n ON<\/span> o.<\/span>id =<\/span> t.<\/span>id;<\/span><\/p>\n

                  \u538b\u8f74 2：\u627e\u51fa\u8fde\u7eed\u767b\u5f55 \u2265 3 \u5929\u7684\u7528\u6237<\/p>\n

                  \u601d\u8def\u4e00\u773c\u96be\u3001\u4ee3\u7801\u4e00\u884c\u900f：\u8fde\u7eed\u7684\u65e5\u671f\u51cf\u53bb\u8fde\u7eed\u7684\u5e8f\u53f7，\u5dee\u503c\u6052\u5b9a\u2014\u2014\u5dee\u503c\u76f8\u540c\u7684\u65e5\u5b50\u5fc5\u7136\u8fde\u6210\u4e00\u6bb5\u3002<\/p>\n

                  — \u8868：users_login(user_id, login_date)<\/span>
                  \nSELECT<\/span> user_id,<\/span> MIN<\/span>(<\/span>login_date)<\/span> AS<\/span> streak_start,<\/span> COUNT<\/span>(<\/span>*<\/span>)<\/span> AS<\/span> days
                  \nFROM<\/span> (<\/span>
                  \n SELECT<\/span> user_id,<\/span> login_date,<\/span>
                  \n DATE_SUB(<\/span>login_date,<\/span> INTERVAL<\/span> ROW_NUMBER(<\/span>)<\/span> OVER<\/span> (<\/span>
                  \n PARTITION<\/span> BY<\/span> user_id ORDER<\/span> BY<\/span> login_date)<\/span> DAY<\/span>)<\/span> AS<\/span> grp
                  \n FROM<\/span> (<\/span>SELECT<\/span> DISTINCT<\/span> user_id,<\/span> login_date FROM<\/span> users_login)<\/span> d — \u5148\u53bb\u91cd<\/span>
                  \n)<\/span> t
                  \nGROUP<\/span> BY<\/span> user_id,<\/span> grp
                  \nHAVING<\/span> COUNT<\/span>(<\/span>*<\/span>)<\/span> >=<\/span> 3<\/span>;<\/span>
                  \n— grp \u76f8\u540c\u7684\u884c\u5373\u4e00\u6bb5\u8fde\u7eed\u767b\u5f55；\u8981 \u2265N \u5929\u5c31\u6539 HAVING \u6761\u4ef6<\/span><\/p>\n

                  \n

                  \u4e94\u3001\u7d22\u5f15：\u4ece B+ \u6811\u5230 EXPLAIN \u5b9e\u6218<\/h3>\n

                  \u7d22\u5f15\u662f MySQL \u6027\u80fd\u95ee\u9898\u7684\u7b2c\u4e00\u73b0\u573a：\u4e5d\u6210\u6162 SQL \u7684\u75c5\u56e0\u548c\u836f\u65b9\u90fd\u5728\u7d22\u5f15\u4e0a\u3002\u8fd9\u4e00\u7ae0\u4ece\u300c\u4e3a\u4ec0\u4e48\u504f\u504f\u662f B+ \u6811\u300d\u8bb2\u8d77，\u4e00\u8def\u5230 EXPLAIN \u5b9e\u6218\u6392\u969c，\u662f\u5168\u6587\u6700\u503c\u5f97\u53cd\u590d\u770b\u7684\u4e00\u7ae0\u3002<\/p>\n

                  5.1 \u4e3a\u4ec0\u4e48\u662f B+ \u6811：\u4e00\u9053\u6392\u9664\u9898<\/h4>\n

                  \u56de\u5fc6\u7d22\u5f15\u7ed3\u6784，\u6700\u5feb\u7684\u529e\u6cd5\u662f\u628a\u5019\u9009\u65b9\u6848\u6328\u4e2a\u6392\u9664：<\/p>\n

                    \n
                  • \u54c8\u5e0c\u8868：\u7b49\u503c\u67e5\u8be2 O(1) \u5f88\u9999，\u4f46\u5b8c\u5168\u4e0d\u652f\u6301\u8303\u56f4\u67e5\u8be2\u548c\u6392\u5e8f\u2014\u2014WHERE id > 100 \u76f4\u63a5\u6b47\u83dc；<\/li>\n
                  • \u4e8c\u53c9\u641c\u7d22\u6811：\u6570\u636e\u9012\u589e\u63d2\u5165\u4f1a\u9000\u5316\u6210\u94fe\u8868，O(n)；<\/li>\n
                  • \u7ea2\u9ed1\u6811\/AVL：\u89e3\u51b3\u4e86\u9000\u5316，\u4f46\u4e8c\u53c9\u610f\u5473\u7740\u300c\u767e\u4e07\u6570\u636e 20+ \u5c42\u300d，\u6bcf\u5c42\u90fd\u662f\u4e00\u6b21\u78c1\u76d8 IO；<\/li>\n
                  • B \u6811：\u591a\u53c9\u77ee\u80d6，\u65b9\u5411\u5bf9\u4e86；\u4f46\u6bcf\u4e2a\u8282\u70b9\u90fd\u5b58\u6574\u884c\u6570\u636e，\u4e00\u9875 16KB \u88c5\u4e0d\u4e86\u51e0\u4e2a\u952e，\u6811\u8fd8\u662f\u504f\u9ad8；<\/li>\n
                  • B+ \u6811：\u975e\u53f6\u5b50\u8282\u70b9\u53ea\u5b58\u300c\u952e + \u6307\u9488\u300d，\u53f6\u5b50\u8282\u70b9\u5b58\u6570\u636e\u5e76\u4e14\u6309\u5e8f\u7ec4\u6210\u53cc\u5411\u94fe\u8868\u2014\u2014\u77ee\u3001\u4e14\u8303\u56f4\u67e5\u8be2\u987a\u85e4\u6478\u74dc\u3002<\/li>\n<\/ul>\n

                    #mermaid-svg-iAP2EhnRiZiIZRBv{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-iAP2EhnRiZiIZRBv .error-icon{fill:#552222;}#mermaid-svg-iAP2EhnRiZiIZRBv .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-iAP2EhnRiZiIZRBv .marker{fill:#333333;stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .marker.cross{stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv svg{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-iAP2EhnRiZiIZRBv p{margin:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .label{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label text{fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label span{color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label span p{background-color:transparent;}#mermaid-svg-iAP2EhnRiZiIZRBv .label text,#mermaid-svg-iAP2EhnRiZiIZRBv span{fill:#333;color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .node rect,#mermaid-svg-iAP2EhnRiZiIZRBv .node circle,#mermaid-svg-iAP2EhnRiZiIZRBv .node ellipse,#mermaid-svg-iAP2EhnRiZiIZRBv .node polygon,#mermaid-svg-iAP2EhnRiZiIZRBv .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .rough-node .label text,#mermaid-svg-iAP2EhnRiZiIZRBv .node .label text,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label,#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label{text-anchor:middle;}#mermaid-svg-iAP2EhnRiZiIZRBv .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .rough-node .label,#mermaid-svg-iAP2EhnRiZiIZRBv .node .label,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label,#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label{text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .node.clickable{cursor:pointer;}#mermaid-svg-iAP2EhnRiZiIZRBv .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .arrowheadPath{fill:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-iAP2EhnRiZiIZRBv .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster text{fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster span{color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-iAP2EhnRiZiIZRBv .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv rect.text{fill:none;stroke-width:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape p,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label rect,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-iAP2EhnRiZiIZRBv .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-iAP2EhnRiZiIZRBv :root{–mermaid-font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;}<\/p>\n

                    <\/span><\/p>\n

                    <\/span><\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u53cc\u5411\u94fe\u8868<\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u53cc\u5411\u94fe\u8868<\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u6839\u8282\u70b9（\u5185\u90e8\u9875）\u53ea\u5b58\u952e\u548c\u6307\u9488：15 \u00b7 35<\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u53f6\u5b50\u9875\u24605 \u00b7 10 \u00b7 15<\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u53f6\u5b50\u9875\u246120 \u00b7 25 \u00b7 30<\/p>\n

                    <\/span><\/p>\n

                    <\/p>\n

                    \u53f6\u5b50\u9875\u246240 \u00b7 45 \u00b7 50<\/p>\n

                    <\/span><\/p>\n

                    \u8457\u540d\u7684\u300c2000 \u4e07\u884c\u53ea\u9700 3 \u5c42\u300d\u63a8\u5bfc：\u4e3b\u952e BIGINT 8 \u5b57\u8282 + \u9875\u6307\u9488 6 \u5b57\u8282 = 14 \u5b57\u8282，\u4e00\u9875 16KB \u7ea6 1170 \u4e2a\u8def\u724c；\u5047\u8bbe\u6bcf\u884c\u6570\u636e 1KB，\u4e00\u9875\u53f6\u5b50\u5b58 16 \u884c\u3002\u4e8e\u662f 3 \u5c42\u6811\u5bb9\u91cf = 1170 \u00d7 1170 \u00d7 16 \u2248 2190 \u4e07\u884c\u3002\u6839\u548c\u4e2d\u95f4\u5c42\u5e38\u5e74\u9a7b\u7559\u5185\u5b58，\u4e00\u6b21\u4e3b\u952e\u67e5\u8be2\u901a\u5e38\u53ea\u6709 1 \u6b21\u78c1\u76d8 IO\u3002<\/p>\n

                    5.2 \u805a\u7c07\u7d22\u5f15\u4e0e\u4e8c\u7ea7\u7d22\u5f15：\u56de\u8868\u7684\u7531\u6765<\/h4>\n
                      \n
                    • \u805a\u7c07\u7d22\u5f15（\u4e3b\u952e\u7d22\u5f15）：\u53f6\u5b50\u8282\u70b9\u5b58\u6574\u884c\u6570\u636e\u3002InnoDB \u8868\u672c\u8eab\u5c31\u662f\u300c\u6309\u4e3b\u952e\u7ec4\u7ec7\u300d\u7684 B+ \u6811，\u6240\u4ee5\u4e5f\u53eb\u7d22\u5f15\u7ec4\u7ec7\u8868；\u8868\u6ca1\u6709\u4e3b\u952e\u65f6，InnoDB \u4f1a\u6311\u4e00\u4e2a\u552f\u4e00\u975e\u7a7a\u7d22\u5f15\u9876\u66ff，\u518d\u6ca1\u6709\u5c31\u751f\u6210\u9690\u85cf\u7684 row_id\u3002<\/li>\n
                    • \u4e8c\u7ea7\u7d22\u5f15（\u666e\u901a\u7d22\u5f15）：\u53f6\u5b50\u8282\u70b9\u5b58\u300c\u7d22\u5f15\u5217\u7684\u503c + \u4e3b\u952e\u503c\u300d，\u4e0d\u5b58\u884c\u6570\u636e\u2014\u2014\u4e0d\u662f\u4e0d\u60f3\u5b58，\u662f\u884c\u6570\u636e\u4f1a\u968f\u9875\u5206\u88c2\u79fb\u52a8，\u5b58\u5730\u5740\u4f1a\u5931\u6548，\u5b58\u7a33\u5b9a\u7684\u4e3b\u952e\u503c\u624d\u53ef\u9760\u3002<\/li>\n<\/ul>\n

                      \u4e8e\u662f\u4e00\u6b21\u4e8c\u7ea7\u7d22\u5f15\u67e5\u8be2\u7684\u5b8c\u6574\u8def\u5f84\u662f：\u5148\u5728\u4e8c\u7ea7\u7d22\u5f15\u6811\u627e\u5230\u4e3b\u952e\u503c，\u518d\u62ff\u4e3b\u952e\u503c\u53bb\u805a\u7c07\u7d22\u5f15\u6811\u67e5\u4e00\u904d\u6574\u884c\u2014\u2014\u7b2c\u4e8c\u6b21\u67e5\u627e\u5c31\u53eb\u56de\u8868\u3002\u5982\u679c\u547d\u4e2d\u884c\u6570\u5f88\u5927（\u6bd4\u5982 10 \u4e07\u884c），\u5c31\u662f 10 \u4e07\u6b21\u56de\u8868，\u8fd9\u5c31\u662f\u5f88\u591a\u300c\u660e\u660e\u6709\u7d22\u5f15\u8fd8\u662f\u6162\u300d\u7684\u771f\u76f8\u3002<\/p>\n

                      \u8bb0\u5fc6\u951a\u70b9：\u4e3b\u952e\u4e3a\u4ec0\u4e48\u63a8\u8350\u81ea\u589e\u3002 \u81ea\u589e\u4e3b\u952e\u6c38\u8fdc\u8ffd\u52a0\u5230 B+ \u6811\u6700\u53f3\u4fa7，\u9875\u5199\u6ee1\u624d\u5f00\u65b0\u9875，\u987a\u5e8f\u53cb\u597d；UUID \u8fd9\u7c7b\u968f\u673a\u4e3b\u952e\u4f1a\u8ba9\u65b0\u6570\u636e\u968f\u673a\u63d2\u5165，\u9891\u7e41\u9875\u5206\u88c2\u3001\u5185\u5b58\u547d\u4e2d\u7387\u4e0b\u964d\u3001\u6811\u8d8a\u6765\u8d8a\u9ad8\u3002\u975e\u8981\u7528\u5206\u5e03\u5f0f ID，\u7528\u8d8b\u52bf\u9012\u589e\u7684\u96ea\u82b1 ID\u3002<\/p>\n

                      5.3 \u8986\u76d6\u7d22\u5f15\u4e0e\u7d22\u5f15\u4e0b\u63a8：\u7701\u6389\u56de\u8868\u7684\u4e24\u677f\u65a7<\/h4>\n

                      — \u8986\u76d6\u7d22\u5f15：SELECT \u7684\u5217\u5168\u90e8\u5305\u542b\u5728\u7d22\u5f15\u91cc \u2192 \u4e0d\u7528\u56de\u8868，EXPLAIN \u7684 Extra \u663e\u793a Using index<\/span>
                      \nSELECT<\/span> id,<\/span> category_id FROM<\/span> products WHERE<\/span> category_id =<\/span> 3<\/span>;<\/span> — idx_category(category_id)<\/span><\/p>\n

                      — \u7d22\u5f15\u4e0b\u63a8（ICP，5.6+）：\u628a WHERE \u91cc\u80fd\u7528\u7d22\u5f15\u5224\u65ad\u7684\u6761\u4ef6"\u4e0b\u63a8"\u7ed9\u5b58\u50a8\u5f15\u64ce\u5148\u8fc7\u6ee4<\/span>
                      \n— \u5047\u8bbe\u7d22\u5f15 idx_name_age(name, age)：<\/span>
                      \nSELECT<\/span> *<\/span> FROM<\/span> users WHERE<\/span> name LIKE<\/span> '\u5f20%'<\/span> AND<\/span> age =<\/span> 10<\/span>;<\/span>
                      \n— \u6ca1\u6709 ICP：\u5f15\u64ce\u6309 '\u5f20%' \u627e\u5230 1000 \u884c\u5168\u90e8\u56de\u8868，Server \u5c42\u518d\u6309 age=10 \u8fc7\u6ee4\u5269 10 \u884c<\/span>
                      \n— \u6709 ICP： \u5f15\u64ce\u5728\u7d22\u5f15\u91cc\u5c31\u628a age != 10 \u7684 990 \u884c\u6254\u6389，\u53ea\u56de\u8868 10 \u884c<\/span><\/p>\n

                      5.4 \u6700\u5de6\u524d\u7f00\u539f\u5219：\u8054\u5408\u7d22\u5f15\u7684\u7075\u9b42<\/h4>\n

                      \u8054\u5408\u7d22\u5f15 (a, b, c) \u7684\u6392\u5e8f\u89c4\u5219\u662f\u300c\u5148\u6309 a \u6392，a \u76f8\u540c\u6309 b \u6392，\u518d\u76f8\u540c\u6309 c \u6392\u300d\u2014\u2014\u7535\u8bdd\u7c3f\u5148\u59d3\u540e\u540d\u3002\u6240\u4ee5\u67e5\u8be2\u6761\u4ef6\u5fc5\u987b\u4ece\u6700\u5de6\u5217\u5f00\u59cb\u8fde\u7eed\u547d\u4e2d：<\/p>\n\nWHERE \u6761\u4ef6\u7d22\u5f15\u4f7f\u7528\u60c5\u51b5\u539f\u56e0<\/tr>\n\n\n\n\n\n\n\n
                      a = 1<\/td>\n\u7528\u4e0a a<\/td>\n\u4ece\u6700\u5de6\u5217\u5f00\u59cb<\/td>\n<\/tr>\n
                      a = 1 AND b = 2<\/td>\n\u7528\u4e0a a\u3001b<\/td>\n\u8fde\u7eed\u547d\u4e2d<\/td>\n<\/tr>\n
                      a = 1 AND b = 2 AND c = 3<\/td>\n\u5168\u7528\u4e0a<\/td>\n\u5b8c\u7f8e\u547d\u4e2d<\/td>\n<\/tr>\n
                      a = 1 AND b > 2 AND c = 3<\/td>\n\u53ea\u7528 a\u3001b<\/td>\nb \u662f\u8303\u56f4\u67e5\u8be2，c \u65ad\u4e86<\/td>\n<\/tr>\n
                      b = 2 AND c = 3<\/td>\n\u7528\u4e0d\u4e0a<\/td>\n\u7f3a\u4e86\u6700\u5de6\u5217 a<\/td>\n<\/tr>\n
                      a = 1 AND c = 3<\/td>\n\u53ea\u7528 a<\/td>\nb \u7f3a\u5e2d，c \u63a5\u4e0d\u4e0a（8.0.13+ \u90e8\u5206\u573a\u666f\u6709 Skip Scan，\u522b\u4f9d\u8d56）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                        \n
                      • \u8bbe\u8ba1\u53e3\u8bc0：\u7b49\u503c\u6761\u4ef6\u7684\u5217\u653e\u524d\u9762，\u8303\u56f4\u6761\u4ef6\u7684\u5217\u653e\u6700\u540e；\u533a\u5206\u5ea6（\u4e0d\u91cd\u590d\u6bd4\u4f8b）\u9ad8\u7684\u5217\u653e\u524d\u9762；\u591a\u79df\u6237\u7cfb\u7edf\u628a tenant_id \u653e\u6700\u524d；<\/li>\n
                      • \u5df2\u5efa (a,b) \u5c31\u4e0d\u5fc5\u5355\u5efa (a)：\u6700\u5de6\u524d\u7f00\u8ba9\u5b83\u517c\u804c\u4e86 a \u7684\u7d22\u5f15\u2014\u2014\u300c\u51cf\u5c11\u5197\u4f59\u7d22\u5f15\u300d\u662f DDL \u8bc4\u5ba1\u7684\u56fa\u5b9a\u52a8\u4f5c\u3002<\/li>\n<\/ul>\n

                        5.5 \u7d22\u5f15\u5931\u6548\u573a\u666f\u6e05\u5355（\u9010\u6761\u5bf9\u7167）<\/h4>\n

                        — \u2460 \u7d22\u5f15\u5217\u53c2\u4e0e\u8fd0\u7b97\u6216\u5957\u51fd\u6570<\/span>
                        \nWHERE<\/span> YEAR<\/span>(<\/span>created_at)<\/span> =<\/span> 2026<\/span> — \u2717 \u7ed9\u5217\u5957\u4e86\u51fd\u6570，\u7d22\u5f15\u62a5\u5e9f<\/span>
                        \nWHERE<\/span> created_at >=<\/span> '2026-01-01'<\/span>
                        \n AND<\/span> created_at <<\/span> '2027-01-01'<\/span> — \u2713 \u6539\u5199\u6210\u8303\u56f4\u6761\u4ef6<\/span><\/p>\n

                        — \u2461 \u9690\u5f0f\u7c7b\u578b\u8f6c\u6362（\u5b57\u7b26\u4e32\u5217\u7528\u6570\u5b57\u67e5）<\/span>
                        \nWHERE<\/span> order_no =<\/span> 20260925001<\/span> — \u2717 \u7b49\u4ef7\u4e8e\u7ed9 order_no \u5957 CAST \u51fd\u6570<\/span>
                        \nWHERE<\/span> order_no =<\/span> '20260925001'<\/span> — \u2713 \u7c7b\u578b\u5bf9\u9f50<\/span><\/p>\n

                        — \u2462 \u524d\u5bfc\u6a21\u7cca\u5339\u914d<\/span>
                        \nWHERE<\/span> name LIKE<\/span> '%\u624b\u673a%'<\/span> — \u2717 \u5f00\u5934\u4e0d\u786e\u5b9a，B+ \u6811\u6ca1\u6cd5\u5b9a\u4f4d<\/span>
                        \nWHERE<\/span> name LIKE<\/span> '\u82f9\u679c%'<\/span> — \u2713 \u76f8\u5f53\u4e8e\u8303\u56f4\u67e5\u8be2 [\u82f9\u679c, \u82f9\u679c+1)<\/span><\/p>\n

                        — \u2463 OR \u8fde\u63a5\u4e86\u6ca1\u6709\u7d22\u5f15\u7684\u5217<\/span>
                        \nWHERE<\/span> category_id =<\/span> 3<\/span> OR<\/span> subtitle =<\/span> 'x'<\/span> — \u2717 subtitle \u65e0\u7d22\u5f15 \u2192 \u6574\u6761 SQL \u5168\u8868\u626b\u63cf<\/span><\/p>\n

                        — \u2464 \u8054\u5408\u7d22\u5f15\u4e0d\u6ee1\u8db3\u6700\u5de6\u524d\u7f00（\u89c1 5.4）<\/span><\/p>\n

                        — \u2465 \u4f18\u5316\u5668\u300c\u4e3b\u52a8\u653e\u5f03\u300d：\u9884\u4f30\u547d\u4e2d\u884c\u6570\u592a\u591a，\u56de\u8868\u6210\u672c > \u5168\u8868\u626b\u63cf<\/span>
                        \n— （\u4e25\u683c\u8bf4\u8fd9\u4e0d\u53eb\u5931\u6548，\u662f\u6210\u672c\u4f30\u7b97；FORCE INDEX \u53ef\u5f3a\u5236，\u4f46\u5148\u60f3\u6e05\u695a\u8981\u4e0d\u8981）<\/span><\/p>\n

                        \u7ea0\u504f：\u8001\u6587\u7ae0\u7684\u4e24\u5927\u8bef\u4f20\u3002\u300c!= \u548c NOT IN \u4e00\u5b9a\u5931\u6548\u300d\u2014\u2014\u4e0d\u5bf9，MySQL \u4f1a\u6309\u6210\u672c\u4f30\u7b97\u51b3\u5b9a\u8d70 range \u8fd8\u662f\u5168\u8868，\u5f88\u591a\u65f6\u5019\u7167\u6837\u8d70\u7d22\u5f15；\u300cIS NULL \u7528\u4e0d\u4e86\u7d22\u5f15\u300d\u2014\u2014\u4e5f\u4e0d\u5bf9，NULL \u503c\u540c\u6837\u5b58\u5728 B+ \u6811\u91cc\u3002\u5224\u65ad\u6807\u51c6\u6c38\u8fdc\u662f EXPLAIN，\u4e0d\u662f\u53e3\u8bc0\u3002<\/p>\n

                        5.6 EXPLAIN：\u6162 SQL \u7684\u542c\u8bca\u5668<\/h4>\n

                        EXPLAIN<\/span> SELECT<\/span> *<\/span> FROM<\/span> orders WHERE<\/span> user_id =<\/span> 88<\/span> AND<\/span> status<\/span> =<\/span> 1<\/span>
                        \nORDER<\/span> BY<\/span> created_at DESC<\/span> LIMIT<\/span> 20<\/span>;<\/span><\/p>\n\n\u5b57\u6bb5\u770b\u4ec0\u4e48<\/tr>\n\n\n\n\n\n\n
                        id<\/td>\n\u6267\u884c\u5e8f\u53f7，\u76f8\u540c\u4e3a\u4e00\u7ec4\u4ece\u4e0a\u5f80\u4e0b\u6267\u884c；\u5b50\u67e5\u8be2\/UNION \u4f1a\u4ea7\u751f\u65b0 id，\u5927\u7ed3\u679c\u96c6\u5728\u540e<\/td>\n<\/tr>\n
                        type<\/td>\n\u8bbf\u95ee\u7c7b\u578b，\u6027\u80fd\u4ece\u597d\u5230\u5dee（\u89c1\u4e0b\u65b9\u68af\u5ea6）<\/td>\n<\/tr>\n
                        key \/ key_len<\/td>\n\u5b9e\u9645\u7528\u4e86\u54ea\u4e2a\u7d22\u5f15\u3001\u7528\u4e86\u51e0\u5217（\u5224\u65ad\u8054\u5408\u7d22\u5f15\u7528\u5230\u7b2c\u51e0\u5217）<\/td>\n<\/tr>\n
                        rows<\/td>\n\u9884\u4f30\u626b\u63cf\u884c\u6570，\u6570\u91cf\u7ea7\u6bd4\u7cbe\u786e\u503c\u91cd\u8981<\/td>\n<\/tr>\n
                        Extra<\/td>\n\u9644\u52a0\u52a8\u4f5c，\u91cd\u70b9\u76ef Using filesort \/ Using temporary<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                        type \u68af\u5ea6（\u4ece\u597d\u5230\u5dee）：const \u2192 eq_ref \u2192 ref \u2192 range \u2192 index \u2192 ALL\u3002\u65e5\u5e38\u6807\u51c6：\u81f3\u5c11 range，\u7b49\u503c\u67e5\u8be2\u5e94\u5230 ref，JOIN \u88ab\u9a71\u52a8\u8868\u5e94\u5230 eq_ref；\u51fa\u73b0 ALL（\u5168\u8868\u626b\u63cf）\u5c31\u8981\u8b66\u60d5\u3002<\/p>\n

                          \n
                        • const：\u4e3b\u952e\/\u552f\u4e00\u952e\u7b49\u503c，\u6700\u591a\u4e00\u884c；<\/li>\n
                        • eq_ref：JOIN \u65f6\u88ab\u9a71\u52a8\u8868\u8d70\u4e3b\u952e\u6216\u552f\u4e00\u7d22\u5f15；<\/li>\n
                        • ref：\u666e\u901a\u4e8c\u7ea7\u7d22\u5f15\u7b49\u503c；<\/li>\n
                        • range：\u7d22\u5f15\u8303\u56f4（BETWEEN\u3001>\u3001IN \u7b49）；<\/li>\n
                        • index：\u626b\u6574\u68f5\u7d22\u5f15\u6811（\u5e38\u89c1\u4e8e\u8986\u76d6\u7d22\u5f15\u6216 MIN\/MAX）；<\/li>\n
                        • ALL：\u5168\u8868\u626b\u63cf\u3002<\/li>\n<\/ul>\n

                          Extra \u9ad8\u9891\u503c：Using index \u8986\u76d6\u7d22\u5f15（\u597d）；Using index condition \u7d22\u5f15\u4e0b\u63a8（\u597d）；Using filesort \u9700\u8981\u989d\u5916\u6392\u5e8f（\u4f18\u5316\u4fe1\u53f7）；Using temporary \u5efa\u4e86\u4e34\u65f6\u8868（GROUP BY \/ DISTINCT \u5e38\u89c1，\u91cd\u70b9\u4f18\u5316）\u3002<\/p>\n

                          5.7 \u5b9e\u6218：\u8ba2\u5355\u5217\u8868\u9875\u6162\u67e5\u8be2\u4f18\u5316\u5168\u8fc7\u7a0b<\/h4>\n

                          \u573a\u666f：\u7528\u6237\u4e2d\u5fc3\u300c\u6211\u7684\u8ba2\u5355\u300d\u9875，\u7ebf\u4e0a\u5e73\u5747 1.8s\u3002SQL \u4e0e\u4f18\u5316\u524d EXPLAIN：<\/p>\n

                          SELECT<\/span> *<\/span> FROM<\/span> orders
                          \nWHERE<\/span> user_id =<\/span> 88<\/span> AND<\/span> status<\/span> =<\/span> 1<\/span>
                          \nORDER<\/span> BY<\/span> created_at DESC<\/span>
                          \nLIMIT<\/span> 20<\/span>;<\/span><\/p>\n

                          — \u4f18\u5316\u524d（\u8868\u4e0a\u53ea\u6709 idx_user_created(user_id, created_at)）：<\/span>
                          \n— type: ref | key: idx_user_created | rows: 8972 | Extra: Using index condition<\/span>
                          \n— \u8bca\u65ad：status \u4e0d\u5728\u7d22\u5f15\u91cc \u2192 8972 \u884c\u5168\u90e8\u56de\u8868\u518d\u8fc7\u6ee4，\u884c\u6570\u8d8a\u591a\u8d8a\u6162<\/span><\/p>\n

                          \u5206\u6790：\u67e5\u8be2\u6761\u4ef6\u662f user_id \u7b49\u503c + status \u7b49\u503c + created_at \u6392\u5e8f\u3002\u6309\u300c\u7b49\u503c\u5217\u5728\u524d\u3001\u8303\u56f4\/\u6392\u5e8f\u5217\u5728\u540e\u300d\u91cd\u5efa\u8054\u5408\u7d22\u5f15：<\/p>\n

                          ALTER<\/span> TABLE<\/span> orders ADD<\/span> INDEX<\/span> idx_user_status_created (<\/span>user_id,<\/span> status<\/span>,<\/span> created_at)<\/span>;<\/span>
                          \nALTER<\/span> TABLE<\/span> orders DROP<\/span> INDEX<\/span> idx_user_created;<\/span><\/p>\n

                          — \u4f18\u5316\u540e：<\/span>
                          \n— type: ref | key: idx_user_status_created | rows: 41 | Extra: Backward index scan<\/span>
                          \n— \u4e09\u5217\u5168\u90e8\u547d\u4e2d：\u5148\u6309 user_id+status \u7cbe\u786e\u5b9a\u4f4d，created_at \u5728\u7d22\u5f15\u91cc\u5929\u7136\u6709\u5e8f \u2192 \u514d\u6392\u5e8f<\/span>
                          \n— \u7ebf\u4e0a\u5b9e\u6d4b 1.8s \u2192 12ms<\/span><\/p>\n

                          \u8bb0\u5fc6\u951a\u70b9：\u7d22\u5f15\u4f18\u5316\u4e09\u6b65\u6cd5\u3002 \u2460 EXPLAIN \u770b type\/key\/rows\/Extra \u56db\u4ef6\u5957 \u2192 \u2461 \u5224\u65ad\u300c\u6761\u4ef6\u5217\u6709\u6ca1\u6709\u90fd\u8fdb\u7d22\u5f15\u3001\u6392\u5e8f\u80fd\u4e0d\u80fd\u514d\u6389\u300d\u2192 \u2462 \u6309\u300c\u7b49\u503c\u5728\u524d\u3001\u8303\u56f4\u5728\u540e\u300d\u8c03\u6574\u8054\u5408\u7d22\u5f15\u5217\u987a\u5e8f\u3002\u4e5d\u6210\u6162 SQL \u8d70\u4e0d\u5b8c\u8fd9\u4e09\u6b65\u5c31\u89e3\u51b3\u4e86\u3002<\/p>\n

                          \n

                          \u516d\u3001\u4e8b\u52a1\u4e0e MVCC<\/h3>\n

                          \u8f6c\u8d26\u662f\u6700\u7ecf\u5178\u7684\u5f00\u573a：A \u6263 100\u3001B \u52a0 100，\u4e24\u6b65\u5fc5\u987b\u540c\u751f\u5171\u6b7b\u3002\u4e8b\u52a1\u89e3\u51b3\u7684\u6b63\u662f\u300c\u591a\u4e2a\u64cd\u4f5c\u6253\u5305\u6210\u4e00\u4e2a\u4e0d\u53ef\u5206\u5272\u7684\u5355\u5143\u300d，\u800c MVCC \u662f InnoDB \u5b9e\u73b0\u300c\u8bfb\u5199\u4e0d\u4e92\u76f8\u963b\u585e\u300d\u7684\u72ec\u95e8\u529f\u592b\u3002<\/p>\n

                          6.1 ACID：\u56db\u4e2a\u7279\u6027\u5404\u9760\u4ec0\u4e48\u5b9e\u73b0<\/h4>\n\n\u7279\u6027\u542b\u4e49InnoDB \u9760\u4ec0\u4e48<\/tr>\n\n\n\n\n\n
                          \u539f\u5b50\u6027 Atomicity<\/td>\n\u8981\u4e48\u5168\u505a\u3001\u8981\u4e48\u5168\u4e0d\u505a<\/td>\nundo log（\u56de\u6eda\u65e5\u5fd7）<\/td>\n<\/tr>\n
                          \u4e00\u81f4\u6027 Consistency<\/td>\n\u6570\u636e\u4ece\u4e00\u4e2a\u5408\u6cd5\u72b6\u6001\u5230\u53e6\u4e00\u4e2a\u5408\u6cd5\u72b6\u6001<\/td>\n\u5b83\u662f\u76ee\u7684，\u7531 A\u3001I\u3001D \u5171\u540c\u4fdd\u969c + \u5e94\u7528\u5c42\u7ea6\u675f<\/td>\n<\/tr>\n
                          \u9694\u79bb\u6027 Isolation<\/td>\n\u5e76\u53d1\u4e8b\u52a1\u4e92\u4e0d\u5e72\u6270<\/td>\nMVCC + \u9501<\/td>\n<\/tr>\n
                          \u6301\u4e45\u6027 Durability<\/td>\n\u63d0\u4ea4\u4e86\u5c31\u6c38\u4e45\u751f\u6548，\u5b95\u673a\u4e5f\u4e0d\u4e22<\/td>\nredo log（WAL）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                          （\u8fd9\u4e00\u5217\u5c31\u662f\u7b2c 7\u30018 \u7ae0\u7684\u76ee\u5f55\u3002）<\/p>\n

                          6.2 \u5e76\u53d1\u4e09\u95ee\u9898\u4e0e\u56db\u4e2a\u9694\u79bb\u7ea7\u522b<\/h4>\n

                          \u4e24\u4e2a\u4e8b\u52a1\u5e76\u53d1\u8dd1，\u4f1a\u51fa\u73b0\u4e09\u79cd\u300c\u810f\u300d\u73b0\u8c61：\u810f\u8bfb（\u8bfb\u5230\u4e86\u522b\u4eba\u6ca1\u63d0\u4ea4\u7684\u6570\u636e）\u3001\u4e0d\u53ef\u91cd\u590d\u8bfb（\u540c\u4e00\u4e8b\u52a1\u91cc\u4e24\u6b21\u8bfb\u540c\u4e00\u884c，\u503c\u53d8\u4e86）\u3001\u5e7b\u8bfb（\u540c\u4e00\u4e8b\u52a1\u91cc\u4e24\u6b21\u6309\u540c\u6837\u6761\u4ef6\u67e5，\u884c\u6570\u53d8\u4e86）\u3002\u9694\u79bb\u7ea7\u522b\u5c31\u662f\u300c\u5141\u8bb8\u54ea\u79cd\u73b0\u8c61\u300d\u7684\u53d6\u820d：<\/p>\n\n\u9694\u79bb\u7ea7\u522b\u810f\u8bfb\u4e0d\u53ef\u91cd\u590d\u8bfb\u5e7b\u8bfb<\/tr>\n\n\n\n\n\n
                          READ UNCOMMITTED \u8bfb\u672a\u63d0\u4ea4<\/td>\n\u53ef\u80fd<\/td>\n\u53ef\u80fd<\/td>\n\u53ef\u80fd<\/td>\n<\/tr>\n
                          READ COMMITTED \u8bfb\u5df2\u63d0\u4ea4<\/td>\n\u4e0d\u53ef\u80fd<\/td>\n\u53ef\u80fd<\/td>\n\u53ef\u80fd<\/td>\n<\/tr>\n
                          REPEATABLE-READ \u53ef\u91cd\u590d\u8bfb（\u9ed8\u8ba4）<\/td>\n\u4e0d\u53ef\u80fd<\/td>\n\u4e0d\u53ef\u80fd<\/td>\n\u90e8\u5206\u89e3\u51b3（\u89c1 6.5）<\/td>\n<\/tr>\n
                          SERIALIZABLE \u4e32\u884c\u5316<\/td>\n\u4e0d\u53ef\u80fd<\/td>\n\u4e0d\u53ef\u80fd<\/td>\n\u4e0d\u53ef\u80fd（\u8bfb\u4e5f\u52a0\u9501，\u5e76\u53d1\u6700\u5dee）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                          \u5b9e\u6218\u6f14\u7ec3：\u8f6c\u8d26\u4e8b\u52a1 + \u9694\u79bb\u7ea7\u522b<\/p>\n

                          SELECT<\/span> @@transaction_isolation<\/span>;<\/span> — \u67e5\u770b\u5f53\u524d\u9694\u79bb\u7ea7\u522b，\u9ed8\u8ba4 REPEATABLE-READ<\/span>
                          \nSET<\/span> SESSION<\/span> TRANSACTION<\/span> ISOLATION<\/span> LEVEL<\/span> READ<\/span> COMMITTED<\/span>;<\/span> — \u53ea\u6539\u5f53\u524d\u4f1a\u8bdd<\/span><\/p>\n

                          — \u8f6c\u8d26：\u4e24\u4e2a\u7ec8\u7aef（\u4f1a\u8bdd A \u6263\u6b3e\u3001\u4f1a\u8bdd B \u89c2\u5bdf）\u6f14\u793a<\/span>
                          \nBEGIN<\/span>;<\/span>
                          \nUPDATE<\/span> accounts SET<\/span> balance =<\/span> balance –<\/span> 100<\/span> WHERE<\/span> id =<\/span> 1<\/span>;<\/span> — A：\u53ea\u6539\u4e0d\u63d0\u4ea4<\/span>
                          \nUPDATE<\/span> accounts SET<\/span> balance =<\/span> balance +<\/span> 100<\/span> WHERE<\/span> id =<\/span> 2<\/span>;<\/span>
                          \nCOMMIT<\/span>;<\/span> — \u6216 ROLLBACK; \u64a4\u9500\u4e00\u5207<\/span>
                          \n— \u4f1a\u8bdd B \u5728 A \u63d0\u4ea4\u524d\u67e5\u8be2：RU \u7ea7\u522b\u80fd\u8bfb\u5230\u672a\u63d0\u4ea4\u6570\u636e（\u810f\u8bfb）；RC\/RR \u8bfb\u5230\u7684\u4ecd\u662f\u65e7\u503c<\/span><\/p>\n

                          — SAVEPOINT：\u4e8b\u52a1\u91cc\u7684"\u5b58\u6863\u70b9"<\/span>
                          \nBEGIN<\/span>;<\/span>
                          \nUPDATE<\/span> products SET<\/span> stock =<\/span> stock –<\/span> 1<\/span> WHERE<\/span> id =<\/span> 1<\/span>;<\/span>
                          \nSAVEPOINT<\/span> sp1;<\/span>
                          \nUPDATE<\/span> products SET<\/span> stock =<\/span> stock –<\/span> 1<\/span> WHERE<\/span> id =<\/span> 2<\/span>;<\/span> — \u8fd9\u6b65\u505a\u9519\u4e86<\/span>
                          \nROLLBACK<\/span> TO<\/span> sp1;<\/span> — \u53ea\u64a4\u9500\u7b2c\u4e8c\u6b65，\u7b2c\u4e00\u6b65\u4fdd\u7559<\/span>
                          \nCOMMIT<\/span>;<\/span><\/p>\n

                          6.3 MVCC \u4e09\u4ef6\u5957：\u9690\u85cf\u5217\u3001\u7248\u672c\u94fe\u3001ReadView<\/h4>\n

                          RR \u7ea7\u522b\u4e0b\u300c\u522b\u4eba\u6539\u4e86\u4f46\u6ca1\u63d0\u4ea4，\u6211\u8bfb\u5230\u7684\u8fd8\u662f\u65e7\u503c\u300d\u2014\u2014\u4e0d\u662f\u9501\u7b49\u5f85，\u662f **MVCC（\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236）**\u5728\u6084\u6084\u5de5\u4f5c\u3002\u5b83\u7531\u4e09\u4ef6\u5957\u7ec4\u6210：<\/p>\n

                            \n
                          • \u9690\u85cf\u5217：\u6bcf\u884c\u6570\u636e\u90fd\u6709\u4e24\u4e2a\u9690\u5f62\u5b57\u6bb5 DB_TRX_ID（\u6700\u540e\u4fee\u6539\u5b83\u7684\u4e8b\u52a1 ID）\u548c DB_ROLL_PTR（\u56de\u6eda\u6307\u9488，\u6307\u5411 undo log \u91cc\u7684\u4e0a\u4e00\u7248\u672c）；<\/li>\n
                          • undo log \u7248\u672c\u94fe：\u6bcf\u6b21\u4fee\u6539\u90fd\u628a\u65e7\u7248\u672c\u5b58\u8fdb undo log，\u4e00\u884c\u6570\u636e\u7684\u5386\u53f2\u7248\u672c\u4e32\u6210\u94fe\u8868；<\/li>\n
                          • ReadView（\u8bfb\u89c6\u56fe）：\u4e8b\u52a1\u53d1\u8d77\u5feb\u7167\u8bfb\u65f6\u751f\u6210\u7684\u300c\u540d\u5355\u300d，\u8bb0\u5f55\u6b64\u523b\u54ea\u4e9b\u4e8b\u52a1\u6d3b\u8dc3\u3001\u54ea\u4e9b\u5df2\u63d0\u4ea4\u3002<\/li>\n<\/ul>\n

                            #mermaid-svg-6dnQ4a4ygnKdUD0D{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-6dnQ4a4ygnKdUD0D .error-icon{fill:#552222;}#mermaid-svg-6dnQ4a4ygnKdUD0D .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-6dnQ4a4ygnKdUD0D .marker{fill:#333333;stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .marker.cross{stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D svg{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-6dnQ4a4ygnKdUD0D p{margin:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .label{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label text{fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label span{color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label span p{background-color:transparent;}#mermaid-svg-6dnQ4a4ygnKdUD0D .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D span{fill:#333;color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node rect,#mermaid-svg-6dnQ4a4ygnKdUD0D .node circle,#mermaid-svg-6dnQ4a4ygnKdUD0D .node ellipse,#mermaid-svg-6dnQ4a4ygnKdUD0D .node polygon,#mermaid-svg-6dnQ4a4ygnKdUD0D .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .rough-node .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label{text-anchor:middle;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .rough-node .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label{text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node.clickable{cursor:pointer;}#mermaid-svg-6dnQ4a4ygnKdUD0D .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .arrowheadPath{fill:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster text{fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster span{color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-6dnQ4a4ygnKdUD0D .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D rect.text{fill:none;stroke-width:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape p,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label rect,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-6dnQ4a4ygnKdUD0D :root{–mermaid-font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;}<\/p>\n

                            <\/p>\n

                            roll_pointer<\/p>\n

                            <\/span><\/p>\n

                            <\/p>\n

                            roll_pointer<\/p>\n

                            <\/span><\/p>\n

                            <\/p>\n

                            \u5f53\u524d\u7248\u672cbalance = 900trx_id = 300<\/p>\n

                            <\/span><\/p>\n

                            <\/p>\n

                            undo \u7248\u672c 2balance = 1000trx_id = 200<\/p>\n

                            <\/span><\/p>\n

                            <\/p>\n

                            undo \u7248\u672c 1（\u521d\u59cb）balance = 1000trx_id = 100<\/p>\n

                            <\/span><\/p>\n

                            \u53ef\u89c1\u6027\u5224\u65ad\u89c4\u5219（\u7b80\u5316\u8bb0\u5fc6\u7248）\u2014\u2014\u62ff\u7248\u672c\u7684 trx_id \u548c ReadView \u6bd4\u5927\u5c0f：<\/p>\n

                          • \u7b49\u4e8e\u300c\u6211\u81ea\u5df1\u300d\u7684 trx_id \u2192 \u53ef\u89c1（\u81ea\u5df1\u6539\u7684\u5f53\u7136\u80fd\u770b）；<\/li>\n
                          • \u5c0f\u4e8e\u6d3b\u8dc3\u4e8b\u52a1\u4e0b\u9650 \u2192 \u53ef\u89c1（\u6211\u62cd\u7167\u524d\u65e9\u5c31\u63d0\u4ea4\u4e86）；<\/li>\n
                          • \u5927\u4e8e\u7b49\u4e8e\u6d3b\u8dc3\u4e8b\u52a1\u4e0a\u9650 \u2192 \u4e0d\u53ef\u89c1（\u6211\u62cd\u7167\u4e4b\u540e\u624d\u5f00\u7684\u4e8b\u52a1）；<\/li>\n
                          • \u843d\u5728\u533a\u95f4\u5185：\u5728\u6d3b\u8dc3\u540d\u5355\u91cc \u2192 \u4e0d\u53ef\u89c1（\u8fd8\u6ca1\u63d0\u4ea4）；\u4e0d\u5728\u540d\u5355\u91cc \u2192 \u53ef\u89c1（\u5df2\u63d0\u4ea4）\u3002<\/li>\n

                            \u4e0d\u53ef\u89c1\u5c31\u987a\u7740 roll_pointer \u8d70\u5230\u4e0a\u4e00\u7248\u672c，\u91cd\u590d\u5224\u65ad，\u76f4\u5230\u627e\u5230\u53ef\u89c1\u7248\u672c\u6216\u94fe\u5c3e\u3002\u4e00\u53e5\u53e3\u8bc0：\u300c\u5c0f\u4e8e\u4e0b\u9650\u53ef\u89c1，\u8d85\u8fc7\u4e0a\u9650\u4e0d\u53ef\u89c1，\u4e2d\u95f4\u67e5\u540d\u5355\u300d\u3002<\/p>\n

                            \u8bb0\u5fc6\u951a\u70b9：RC \u548c RR \u7684\u672c\u8d28\u533a\u522b\u53ea\u6709\u4e00\u53e5\u8bdd\u3002 RC：\u6bcf\u6b21\u5feb\u7167\u8bfb\u90fd\u751f\u6210\u65b0 ReadView \u2192 \u80fd\u770b\u5230\u522b\u7684\u4e8b\u52a1\u65b0\u63d0\u4ea4\u7684\u6570\u636e \u2192 \u4e0d\u53ef\u91cd\u590d\u8bfb\u3002 RR：\u53ea\u5728\u4e8b\u52a1\u7b2c\u4e00\u6b21\u67e5\u8be2\u65f6\u751f\u6210 ReadView，\u4e4b\u540e\u4e00\u76f4\u590d\u7528 \u2192 \u6574\u4e2a\u4e8b\u52a1\u770b\u5230\u7684\u4e16\u754c\u5b9a\u683c\u5728\u5f00\u5934 \u2192 \u53ef\u91cd\u590d\u8bfb\u3002 \u6240\u6709\u9694\u79bb\u7ea7\u522b\u5dee\u5f02，\u90fd\u662f\u8fd9\u4e00\u53e5\u8bdd\u7684\u63a8\u8bba\u3002<\/p>\n

                            6.4 \u5feb\u7167\u8bfb\u4e0e\u5f53\u524d\u8bfb<\/h4>\n
                              \n
                            • \u5feb\u7167\u8bfb：\u666e\u901a SELECT，\u8d70 MVCC，\u4e0d\u52a0\u9501，\u8bfb\u7684\u662f\u5feb\u7167；<\/li>\n
                            • \u5f53\u524d\u8bfb：SELECT … FOR UPDATE\u3001SELECT … FOR SHARE（8.0 \u5199\u6cd5，\u65e7\u7248 LOCK IN SHARE MODE）\u4ee5\u53ca\u6240\u6709 UPDATE \/ DELETE\u2014\u2014\u8bfb\u7684\u662f\u6700\u65b0\u7248\u672c，\u5e76\u52a0\u9501\u3002<\/li>\n<\/ul>\n

                              \u4e3a\u4ec0\u4e48 UPDATE \u5fc5\u987b\u300c\u5f53\u524d\u8bfb\u300d？\u5982\u679c\u4e0d\u8bfb\u6700\u65b0\u503c，\u4e24\u4e2a\u4e8b\u52a1\u57fa\u4e8e\u540c\u4e00\u4e2a\u65e7\u5feb\u7167\u5404\u81ea +1，\u7ed3\u679c\u4f1a\u4e22\u4e00\u6b21\u66f4\u65b0\u3002\u6539\u6570\u636e\u6c38\u8fdc\u4ee5\u6700\u65b0\u7248\u672c\u4e3a\u51c6\u3002<\/p>\n

                              6.5 RR \u5230\u5e95\u6709\u6ca1\u6709\u89e3\u51b3\u5e7b\u8bfb<\/h4>\n

                              \u6807\u51c6\u7b54\u6848：InnoDB \u7684 RR \u5728\u5f88\u5927\u7a0b\u5ea6\u4e0a\u89e3\u51b3\u4e86\u5e7b\u8bfb，\u4f46\u6ca1\u5835\u6b7b：<\/p>\n

                                \n
                              • \u5feb\u7167\u8bfb：\u9760 MVCC，\u4e8b\u52a1\u5185\u591a\u6b21\u67e5\u8be2\u7ed3\u679c\u4e00\u81f4，\u5929\u7136\u65e0\u5e7b\u8bfb；<\/li>\n
                              • \u5f53\u524d\u8bfb：\u9760**\u4e34\u952e\u9501（Next-Key Lock）**\u9501\u4f4f\u300c\u5df2\u6709\u8bb0\u5f55 + \u8bb0\u5f55\u95f4\u7684\u95f4\u9699\u300d，\u522b\u7684\u4e8b\u52a1\u63d2\u4e0d\u8fdb\u6765（\u7b2c 7 \u7ae0\u5c55\u5f00）；<\/li>\n
                              • \u6f0f\u6d1e\u573a\u666f：\u4e8b\u52a1\u5148\u5feb\u7167\u8bfb（\u672a\u52a0\u9501），\u522b\u7684\u4e8b\u52a1\u63d2\u5165\u4e00\u884c\u5e76\u63d0\u4ea4，\u672c\u4e8b\u52a1\u518d\u5f53\u524d\u8bfb\u2014\u2014\u8fd9\u6279\u300c\u5e7b\u5f71\u884c\u300d\u5c31\u51fa\u73b0\u4e86\u3002\u8981\u5f7b\u5e95\u907f\u514d，\u7b2c\u4e00\u6b21\u67e5\u8be2\u5c31\u7528 FOR UPDATE\u3002<\/li>\n<\/ul>\n
                                \n

                                \u4e03\u3001\u9501：\u4ece MDL \u5230\u95f4\u9699\u9501<\/h3>\n

                                \u9501\u662f\u9694\u79bb\u6027\u7684\u53e6\u4e00\u534a\u3002MVCC \u8ba9\u300c\u8bfb\u300d\u4e0d\u963b\u585e，\u9501\u8d1f\u8d23\u300c\u5199\u4e0e\u5199\u300d\u6392\u961f\u3002\u8fd9\u4e00\u7ae0\u6309\u7c92\u5ea6\u4ece\u5927\u5230\u5c0f\u8fc7\u4e00\u904d，\u6700\u540e\u7528\u6b7b\u9501\u590d\u73b0\u548c\u300c\u8d85\u5356\u300d\u4e09\u6bb5\u5f0f\u6536\u5c3e\u2014\u2014\u90fd\u662f\u771f\u5b9e\u7ebf\u4e0a\u4f1a\u75bc\u7684\u6848\u4f8b\u3002<\/p>\n

                                7.1 \u9501\u7684\u5168\u666f：\u4e00\u5f20\u8868\u770b\u5168<\/h4>\n\n\u7c92\u5ea6\u9501\u4e00\u53e5\u8bdd\u804c\u8d23<\/tr>\n\n\n\n\n\n\n\n\n\n
                                \u5168\u5c40<\/td>\nFTWRL \u5168\u5c40\u8bfb\u9501<\/td>\n\u6574\u5e93\u53ea\u8bfb，\u903b\u8f91\u5907\u4efd\u7528<\/td>\n<\/tr>\n
                                \u8868\u7ea7<\/td>\nLOCK TABLES \u8868\u9501<\/td>\n\u663e\u5f0f\u9501\u8868，\u884c\u9501\u65f6\u4ee3\u57fa\u672c\u4e0d\u7528<\/td>\n<\/tr>\n
                                \u8868\u7ea7<\/td>\nMDL \u5143\u6570\u636e\u9501<\/td>\nDDL \u4e0e DML \u4e92\u65a5\u7684\u9690\u5f62\u4fdd\u9556，\u6700\u5bb9\u6613\u51fa\u7ebf\u4e0a\u4e8b\u6545<\/td>\n<\/tr>\n
                                \u8868\u7ea7<\/td>\n\u610f\u5411\u9501 IS \/ IX<\/td>\n\u884c\u9501\u524d\u5148\u5728\u8868\u4e0a\u300c\u6302\u53f7\u300d，\u907f\u514d\u9010\u884c\u68c0\u67e5\u51b2\u7a81<\/td>\n<\/tr>\n
                                \u8868\u7ea7<\/td>\nAUTO-INC \u9501<\/td>\n\u4fdd\u8bc1\u81ea\u589e\u503c\u5206\u914d\u4e0d\u91cd\u53f7<\/td>\n<\/tr>\n
                                \u884c\u7ea7<\/td>\nRecord Lock \u8bb0\u5f55\u9501<\/td>\n\u9501\u4f4f\u7d22\u5f15\u4e0a\u7684\u5355\u6761\u8bb0\u5f55<\/td>\n<\/tr>\n
                                \u884c\u7ea7<\/td>\nGap Lock \u95f4\u9699\u9501<\/td>\n\u9501\u4f4f\u4e24\u6761\u8bb0\u5f55\u4e4b\u95f4\u7684\u7a7a\u9699，\u4e13\u9632\u63d2\u5165<\/td>\n<\/tr>\n
                                \u884c\u7ea7<\/td>\nNext-Key Lock \u4e34\u952e\u9501<\/td>\n\u8bb0\u5f55\u9501 + \u95f4\u9699\u9501，RR \u9632\u5e7b\u8bfb\u7684\u4e3b\u529b<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                7.2 \u5168\u5c40\u9501：\u5907\u4efd\u7684\u6b63\u786e\u59ff\u52bf<\/h4>\n

                                FLUSH TABLES<\/span> WITH<\/span> READ<\/span> LOCK<\/span>;<\/span> — \u5168\u5e93\u53ea\u8bfb：MyISAM \u65f6\u4ee3\u7684\u5907\u4efd\u65b9\u6cd5<\/span>
                                \nUNLOCK<\/span> TABLES<\/span>;<\/span><\/p>\n

                                — InnoDB \u65f6\u4ee3：\u9760 MVCC \u5feb\u7167\u62ff\u5230\u4e00\u81f4\u6027\u89c6\u56fe，\u5b8c\u5168\u4e0d\u963b\u585e\u4e1a\u52a1<\/span>
                                \nmysqldump –single-transaction -uroot -p mall > mall_$(date +%F).sql<\/span><\/p>\n

                                7.3 MDL \u9501：\u6700\u9690\u853d\u7684\u7ebf\u4e0a\u4e8b\u6545<\/h4>\n

                                \u5bf9\u8868\u505a\u589e\u5220\u6539\u67e5\u65f6\u81ea\u52a8\u52a0 MDL\u8bfb\u9501，\u505a ALTER \u65f6\u9700\u8981 MDL\u5199\u9501，\u8bfb\u5199\u4e92\u65a5\u3002\u7ecf\u5178\u7ffb\u8f66\u73b0\u573a：<\/p>\n

                                \u4f1a\u8bdd A：BEGIN; SELECT * FROM users WHERE id = 1; — \u62ff\u5230 MDL \u8bfb\u9501，\u4e8b\u52a1\u6302\u7740\u6ca1\u63d0\u4ea4
                                \n\u4f1a\u8bdd B：ALTER TABLE users ADD COLUMN vip INT; — \u9700\u8981 MDL \u5199\u9501 \u2192 \u6392\u961f\u7b49 A
                                \n\u4f1a\u8bdd C：SELECT * FROM users WHERE id = 2; — \u53ea\u8981 MDL \u8bfb\u9501，\u5374\u88ab B \u6321\u5728\u540e\u9762
                                \n— \u7ed3\u679c：\u8fd9\u5f20\u8868\u7684\u6240\u6709\u540e\u7eed\u67e5\u8be2\u5168\u90e8\u5806\u79ef，\u4e1a\u52a1"\u5047\u6b7b"<\/p>\n

                                  \n
                                • \u9884\u9632\u4e00：DDL \u524d\u5148\u67e5\u957f\u4e8b\u52a1，\u6709\u6302\u7740\u7684\u4e8b\u52a1\u5c31\u522b\u52a8\u8868：SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;<\/li>\n
                                • \u9884\u9632\u4e8c：\u628a MDL \u7b49\u5f85\u4e0a\u9650\u8c03\u5c0f，\u522b\u7528\u9ed8\u8ba4\u503c（\u9ed8\u8ba4\u7ea6\u4e00\u5e74）：SET GLOBAL lock_wait_timeout = 10;\u2014\u2014\u62ff\u4e0d\u5230\u5199\u9501\u5c31\u5931\u8d25\u64a4\u9000，\u800c\u4e0d\u662f\u628a\u8868\u5835\u6b7b；<\/li>\n
                                • \u9884\u9632\u4e09：\u4f4e\u5cf0\u671f\u505a DDL，\u5e76\u5148\u5728\u9884\u53d1\u73af\u5883\u6f14\u7ec3\u3002<\/li>\n<\/ul>\n

                                  7.4 \u884c\u9501\u4e09\u5144\u5f1f：\u52a0\u5728\u7d22\u5f15\u4e0a\u7684\u9501<\/h4>\n

                                  \u5047\u8bbe\u8868\u91cc\u6709 id \u4e3a 1\u30015\u300110\u300115 \u56db\u884c（id \u6709\u7d22\u5f15），RR \u7ea7\u522b\u4e0b\u7684\u52a0\u9501\u89c4\u5219：<\/p>\n\n\u8bed\u53e5\u573a\u666f\u52a0\u4ec0\u4e48\u9501\u9501\u4f4f\u7684\u8303\u56f4<\/tr>\n\n\n\n\n\n\n
                                  \u552f\u4e00\u7d22\u5f15\u7b49\u503c，\u547d\u4e2d（id = 10）<\/td>\n\u8bb0\u5f55\u9501<\/td>\n\u53ea\u6709 id=10 \u8fd9\u4e00\u884c<\/td>\n<\/tr>\n
                                  \u552f\u4e00\u7d22\u5f15\u7b49\u503c，\u672a\u547d\u4e2d（id = 7）<\/td>\n\u95f4\u9699\u9501<\/td>\n(5, 10) \u4e4b\u95f4\u7684\u7a7a\u9699，\u7981\u6b62\u63d2\u5165<\/td>\n<\/tr>\n
                                  \u666e\u901a\u7d22\u5f15\u7b49\u503c（idx \u4e0a age = 10）<\/td>\n\u4e34\u952e\u9501 + \u95f4\u9699\u9501<\/td>\n(5, 10] \u53ca\u4e0b\u4e00\u4e2a\u95f4\u9699，\u9632\u5e7b\u8bfb<\/td>\n<\/tr>\n
                                  \u8303\u56f4\u6761\u4ef6（id > 10）<\/td>\n\u4e34\u952e\u9501<\/td>\n(10, 15]\u3001(15, +\u221e) \u4e00\u8def\u9501\u8fc7\u53bb<\/td>\n<\/tr>\n
                                  \u6761\u4ef6\u5217\u6ca1\u6709\u7d22\u5f15<\/td>\n\u5168\u90e8\u8bb0\u5f55\u52a0\u4e34\u952e\u9501<\/td>\n\u6548\u679c \u2248 \u9501\u5168\u8868（\u6700\u5371\u9669）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                  \u6613\u9519\u70b9：\u884c\u9501\u52a0\u5728\u7d22\u5f15\u4e0a，\u7d22\u5f15\u5931\u6548\u7b49\u4e8e\u9501\u5168\u8868\u3002 UPDATE orders SET status = 4 WHERE order_no = 'X1';\u2014\u2014order_no \u6ca1\u7d22\u5f15\u65f6，InnoDB \u5168\u8868\u626b\u63cf，\u628a\u6bcf\u4e00\u884c\u90fd\u9501\u4f4f，\u5176\u4ed6\u4e8b\u52a1\u7684\u4efb\u4f55\u66f4\u65b0\u5168\u90e8\u6392\u961f\u3002\u300c\u5220\u4e86\u4e00\u4e2a\u6ca1\u8d70\u7d22\u5f15\u7684 WHERE\u300d\u548c\u300c\u9501\u5168\u8868\u300d\u662f\u540c\u4e00\u4ef6\u4e8b，\u7ebf\u4e0a\u5927\u6279\u91cf UPDATE \u524d\u5148 EXPLAIN \u4e00\u904d\u3002<\/p>\n

                                  7.5 \u6b7b\u9501\u590d\u73b0\u4e0e\u6392\u67e5<\/h4>\n

                                  — \u4f1a\u8bdd A — \u4f1a\u8bdd B
                                  \nBEGIN; BEGIN;
                                  \nUPDATE products SET stock = stock-1 WHERE id = 1;
                                  \n UPDATE products SET stock = stock-1 WHERE id = 2;
                                  \nUPDATE products SET stock = stock-1 WHERE id = 2; — A \u7b49 B \u653e id=2
                                  \n UPDATE products SET stock = stock-1 WHERE id = 1; — B \u7b49 A \u653e id=1
                                  \n — ERROR 1213 (40001): Deadlock found …（B \u88ab\u9009\u4e3a\u727a\u7272\u8005\u56de\u6eda）<\/p>\n

                                  SHOW<\/span> ENGINE<\/span> INNODB<\/span> STATUS<\/span>\\\\G
                                  \n— \u627e "LATEST DETECTED DEADLOCK" \u6bb5：\u4e24\u4e2a\u4e8b\u52a1\u5404\u6301\u6709\u4ec0\u4e48\u9501\u3001\u5728\u7b49\u4ec0\u4e48\u9501\u3001\u6700\u540e\u6267\u884c\u7684 SQL<\/span><\/p>\n

                                    \n
                                  • InnoDB \u9ed8\u8ba4\u5f00\u542f\u6b7b\u9501\u68c0\u6d4b（innodb_deadlock_detect=ON），\u53d1\u73b0\u56de\u73af\u7acb\u5373\u56de\u6eda\u4ee3\u4ef7\u5c0f\u7684\u4e00\u65b9，\u901a\u5e38\u6beb\u79d2\u7ea7\u89e3\u5f00；<\/li>\n
                                  • \u62ff\u4e0d\u5230\u9501\u7684\u7b49\u5f85\u7531 innodb_lock_wait_timeout（\u9ed8\u8ba4 50s）\u63a7\u5236，\u8d85\u65f6\u62a5 ERROR 1205；<\/li>\n
                                  • \u9632\u6b7b\u9501\u56db\u62db：\u591a\u884c\u66f4\u65b0\u56fa\u5b9a\u987a\u5e8f（\u5982\u6309\u4e3b\u952e\u5347\u5e8f）\u3001\u4e8b\u52a1\u5c3d\u91cf\u77ed\u3001\u7528\u6761\u4ef6\u66f4\u65b0\u4ee3\u66ff\u300c\u5148\u67e5\u540e\u6539\u300d\u3001\u70ed\u70b9\u884c\u7528\u961f\u5217\/Redis \u524a\u5cf0\u800c\u4e0d\u662f\u786c\u603c\u6570\u636e\u5e93\u3002<\/li>\n<\/ul>\n

                                    7.6 \u5b9e\u6218：\u8d85\u5356\u95ee\u9898\u7684\u4e09\u79cd\u89e3\u6cd5<\/h4>\n

                                    \u79d2\u6740\u6263\u5e93\u5b58，\u540c\u4e00\u4e2a\u5546\u54c1 1000 \u4e2a\u8bf7\u6c42\u540c\u65f6\u6765，\u5e93\u5b58\u53ea\u6709 100\u2014\u2014\u4e09\u6bb5\u6f14\u8fdb：<\/p>\n

                                    — \u7248\u672c 1：\u5148\u67e5\u540e\u6539（\u5fc5\u8d85\u5356）<\/span>
                                    \n— stock = SELECT stock FROM products WHERE id = 1; \u2192 \u5e94\u7528\u5224\u65ad stock >= 1<\/span>
                                    \n— UPDATE products SET stock = stock – 1 WHERE id = 1;<\/span>
                                    \n— \u5e76\u53d1\u4e0b\u4e24\u4e2a\u8bf7\u6c42\u540c\u65f6\u8bfb\u5230 stock=1\u3001\u540c\u65f6\u901a\u8fc7\u5224\u65ad \u2192 \u5356\u51fa 2 \u4ef6，\u5e93\u5b58\u53ea\u6709 1<\/span><\/p>\n

                                    — \u7248\u672c 2：\u6761\u4ef6\u66f4\u65b0（\u63a8\u8350，\u672c\u8d28\u662f\u4e50\u89c2\u9501\u601d\u60f3）<\/span>
                                    \nUPDATE<\/span> products SET<\/span> stock =<\/span> stock –<\/span> 1<\/span> WHERE<\/span> id =<\/span> 1<\/span> AND<\/span> stock >=<\/span> 1<\/span>;<\/span>
                                    \n— affected_rows = 0 \u2192 \u6ca1\u62a2\u5230，\u8fd4\u56de"\u5df2\u552e\u7f44"；\u5224\u65ad\u8fdb\u5165 WHERE，\u9760\u884c\u9501\u4fdd\u8bc1\u539f\u5b50\u6027<\/span><\/p>\n

                                    — \u7248\u672c 3：\u60b2\u89c2\u9501 SELECT … FOR UPDATE（\u9002\u5408\u8fd8\u8981\u8054\u52a8\u591a\u8868\u7684\u590d\u6742\u6263\u51cf）<\/span>
                                    \nBEGIN<\/span>;<\/span>
                                    \nSELECT<\/span> stock FROM<\/span> products WHERE<\/span> id =<\/span> 1<\/span> FOR<\/span> UPDATE<\/span>;<\/span> — \u9501\u4f4f，\u522b\u4eba\u8fdb\u4e0d\u6765<\/span>
                                    \n— … \u5e94\u7528\u5c42\u4e1a\u52a1\u6821\u9a8c\u3001\u5199\u8ba2\u5355\u3001\u5199\u660e\u7ec6 …<\/span>
                                    \nUPDATE<\/span> products SET<\/span> stock =<\/span> stock –<\/span> 1<\/span> WHERE<\/span> id =<\/span> 1<\/span>;<\/span>
                                    \nCOMMIT<\/span>;<\/span><\/p>\n

                                    — \u4e50\u89c2\u9501\u901a\u7528\u5f62\u6001：version \u53f7<\/span>
                                    \nUPDATE<\/span> docs SET<\/span> content =<\/span> '\u65b0\u5185\u5bb9'<\/span>,<\/span> version =<\/span> version +<\/span> 1<\/span>
                                    \nWHERE<\/span> id =<\/span> 1<\/span> AND<\/span> version =<\/span> 5<\/span>;<\/span> — \u547d\u4e2d 0 \u884c = \u522b\u4eba\u5148\u6539\u4e86，\u91cd\u8bfb\u91cd\u8bd5<\/span><\/p>\n

                                    \u8bb0\u5fc6\u951a\u70b9：\u4ec0\u4e48\u65f6\u5019\u7528\u54ea\u79cd\u9501\u3002 \u51b2\u7a81\u5c11 \u2192 \u4e50\u89c2\u9501（version \/ \u6761\u4ef6\u66f4\u65b0），\u65e0\u9501\u9ad8\u6027\u80fd，\u5931\u8d25\u91cd\u8bd5；\u51b2\u7a81\u6fc0\u70c8 \u2192 \u60b2\u89c2\u9501（FOR UPDATE），\u4e00\u6b21\u9501\u5230\u4f4d\u907f\u514d\u53cd\u590d\u91cd\u8bd5\u6253\u7206 CPU；\u79d2\u6740\u7ea7\u70ed\u70b9 \u2192 \u6570\u636e\u5e93\u9501\u90fd\u4e0d\u5408\u9002，\u4e0a Redis \u9884\u6263 + MQ \u5f02\u6b65\u843d\u5e93\u3002<\/p>\n

                                    \n

                                    \u516b\u3001\u65e5\u5fd7\u7cfb\u7edf：redo \/ undo \/ binlog<\/h3>\n

                                    \u7b2c 6 \u7ae0\u8868\u8bf4\u8fc7：\u539f\u5b50\u6027\u9760 undo\u3001\u6301\u4e45\u6027\u9760 redo\u3002\u8fd9\u4e00\u7ae0\u628a\u4e09\u672c\u65e5\u5fd7\u5f7b\u5e95\u8bb2\u900f，\u518d\u56de\u7b54\u90a3\u4e2a\u7ecf\u5178\u95ee\u9898\u2014\u2014\u4e3a\u4ec0\u4e48\u66f4\u65b0\u4e00\u6761\u6570\u636e\u8981\u5199\u4e24\u6b21\u65e5\u5fd7（\u4e24\u9636\u6bb5\u63d0\u4ea4）\u3002<\/p>\n

                                    8.1 \u4e09\u672c\u65e5\u5fd7\u5404\u7ba1\u4ec0\u4e48<\/h4>\n\n\u7ef4\u5ea6redo logundo logbinlog<\/tr>\n\n\n\n\n\n
                                    \u6240\u5c5e\u5c42<\/td>\nInnoDB<\/td>\nInnoDB<\/td>\nServer \u5c42（\u6240\u6709\u5f15\u64ce\u90fd\u6709）<\/td>\n<\/tr>\n
                                    \u6838\u5fc3\u4f5c\u7528<\/td>\n\u5d29\u6e83\u6062\u590d，\u4fdd\u6301\u4e45\u6027<\/td>\n\u56de\u6eda + MVCC \u7248\u672c\u94fe<\/td>\n\u4e3b\u4ece\u590d\u5236\u3001\u6570\u636e\u5f52\u6863\u6062\u590d<\/td>\n<\/tr>\n
                                    \u8bb0\u5f55\u5185\u5bb9<\/td>\n\u7269\u7406\u65e5\u5fd7：\u300c\u67d0\u9875\u67d0\u4f4d\u7f6e\u6539\u6210\u4e86\u4ec0\u4e48\u300d<\/td>\n\u903b\u8f91\u65e5\u5fd7：\u53cd\u5411\u64cd\u4f5c（INSERT \u8bb0 DELETE）<\/td>\n\u903b\u8f91\u65e5\u5fd7：SQL \u8bed\u53e5\u6216\u884c\u53d8\u66f4\u955c\u50cf<\/td>\n<\/tr>\n
                                    \u5199\u5165\u65b9\u5f0f<\/td>\n\u56fa\u5b9a\u5927\u5c0f，\u5faa\u73af\u5199<\/td>\n\u968f\u4e8b\u52a1\u4ea7\u751f，\u63d0\u4ea4\u540e\u53ef\u6e05\u7406<\/td>\n\u8ffd\u52a0\u5199，\u5199\u6ee1\u6362\u4e0b\u4e00\u4e2a\u6587\u4ef6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                    8.2 redo log：WAL \u7684\u7cbe\u9ad3<\/h4>\n

                                    \u4e3a\u4ec0\u4e48\u6539\u6570\u636e\u4e0d\u76f4\u63a5\u5237\u76d8？\u6570\u636e\u9875\u5728\u78c1\u76d8\u4e0a\u968f\u673a\u5206\u6563，\u5237\u9875\u662f\u968f\u673a IO；\u800c\u65e5\u5fd7\u662f\u987a\u5e8f\u8ffd\u52a0，\u5feb\u51e0\u4e2a\u6570\u91cf\u7ea7\u3002\u6240\u4ee5 InnoDB \u7684\u9009\u62e9\u662f：\u5148\u5728\u5185\u5b58（Buffer Pool）\u6539\u9875\u3001\u540c\u65f6\u628a\u300c\u8fd9\u4e00\u9875\u6539\u4e86\u4ec0\u4e48\u300d\u987a\u5e8f\u5199\u8fdb redo log，\u810f\u9875\u7531\u540e\u53f0\u7ebf\u7a0b\u6162\u6162\u5237\u3002\u8fd9\u5957\u987a\u5e8f\u5199\u6362\u968f\u673a\u5199\u7684\u601d\u8def\u5c31\u662f WAL（Write-Ahead Logging）\u3002<\/p>\n

                                      \n
                                    • \u5faa\u73af\u5199：redo log \u662f\u56fa\u5b9a\u5927\u5c0f\u7684\u73af（\u5982 4 \u4e2a\u6587\u4ef6\u5404 1GB），write pos \u8ffd\u7740 checkpoint \u8dd1；\u5199\u6ee1\u5c31\u5fc5\u987b\u505c\u4e0b\u6765\u63a8\u8fdb checkpoint（\u5f3a\u5236\u5237\u810f\u9875），\u6b64\u65f6\u4e1a\u52a1\u66f4\u65b0\u4f1a\u88ab\u963b\u585e\u2014\u2014\u7ebf\u4e0a\u5076\u53d1\u7684\u300cMySQL \u5361\u4e00\u4e0b\u300d，\u67e5\u67e5 redo log \u5927\u5c0f；<\/li>\n
                                    • \u843d\u76d8\u65f6\u673a\u7531 innodb_flush_log_at_trx_commit \u63a7\u5236：<\/li>\n<\/ul>\n\n\u503c\u884c\u4e3a\u98ce\u9669<\/tr>\n\n\n\n\n
                                      0<\/td>\n\u6bcf\u79d2\u5237\u76d8\u4e00\u6b21<\/td>\n\u5d29\u6e83\u4e22\u6700\u540e\u4e00\u79d2\u7684\u4e8b\u52a1<\/td>\n<\/tr>\n
                                      1（\u9ed8\u8ba4）<\/td>\n\u6bcf\u6b21\u63d0\u4ea4\u90fd fsync \u843d\u76d8<\/td>\n\u6700\u5b89\u5168，IO \u538b\u529b\u6700\u5927<\/td>\n<\/tr>\n
                                      2<\/td>\n\u63d0\u4ea4\u5199\u5230 OS \u7f13\u5b58，\u6bcf\u79d2 fsync<\/td>\nMySQL \u6302\u4e86\u4e0d\u4e22，\u4e3b\u673a\u65ad\u7535\u4e22\u4e00\u79d2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                      8.3 undo log：\u56de\u6eda\u7684\u5e95\u6c14<\/h4>\n

                                      \u6bcf\u6b21\u4fee\u6539\u524d\u5148\u8bb0\u300c\u53cd\u5411\u64cd\u4f5c\u300d：INSERT \u8bb0\u4e00\u6761 DELETE，UPDATE \u8bb0\u65e7\u503c，DELETE \u8bb0\u6574\u884c\u3002\u4e24\u4e2a\u7528\u9014：\u4e00\u662f ROLLBACK \u65f6\u9010\u6761\u6267\u884c\u53cd\u5411\u64cd\u4f5c；\u4e8c\u662f\u4f5c\u4e3a MVCC \u7684\u7248\u672c\u94fe（\u7b2c 6 \u7ae0\u56fe）\u3002\u957f\u4e8b\u52a1\u7684\u9690\u85cf\u6210\u672c\u5c31\u5728\u8fd9：\u7248\u672c\u94fe\u4e0d\u80fd\u6e05\u7406，undo \u8d8a\u79ef\u8d8a\u957f，\u56de\u6eda\u6bb5\u81a8\u80c0\u2014\u2014\u53c8\u4e00\u4e2a\u300c\u522b\u5f00\u957f\u4e8b\u52a1\u300d\u7684\u7406\u7531\u3002<\/p>\n

                                      8.4 binlog：\u590d\u5236\u4e0e\u5f52\u6863\u7684\u751f\u547d\u7ebf<\/h4>\n\n\u683c\u5f0f\u8bb0\u5f55\u5185\u5bb9\u4f18\u70b9\u98ce\u9669<\/tr>\n\n\n\n\n
                                      statement<\/td>\nSQL \u539f\u6587<\/td>\n\u65e5\u5fd7\u91cf\u5c0f<\/td>\nnow()\u3001uuid()\u3001limit \u987a\u5e8f\u90fd\u53ef\u80fd\u5bfc\u81f4\u4e3b\u4ece\u4e0d\u4e00\u81f4<\/td>\n<\/tr>\n
                                      row（\u9ed8\u8ba4）<\/td>\n\u6bcf\u884c\u53d8\u66f4\u7684\u524d\u540e\u955c\u50cf<\/td>\n\u7cbe\u786e，\u4e3b\u4ece\u5f3a\u4e00\u81f4<\/td>\n\u6279\u91cf\u66f4\u65b0 100 \u4e07\u884c\u5c31\u662f 100 \u4e07\u6761\u8bb0\u5f55，\u65e5\u5fd7\u66b4\u6da8<\/td>\n<\/tr>\n
                                      mixed<\/td>\n\u4e00\u822c statement，\u6709\u98ce\u9669\u81ea\u52a8\u5207 row<\/td>\n\u6298\u4e2d<\/td>\n\u5207\u6362\u903b\u8f91\u590d\u6742，\u6392\u67e5\u95ee\u9898\u7ed5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                      SHOW<\/span> BINLOG EVENTS IN<\/span> 'binlog.000123'<\/span> LIMIT<\/span> 10<\/span>;<\/span> — \u4eba\u8089\u770b\u4e00\u773c binlog \u5185\u5bb9<\/span>
                                      \nSHOW<\/span> MASTER STATUS<\/span>;<\/span> — \u5f53\u524d\u5199\u5165\u5230\u54ea\u4e2a binlog \u6587\u4ef6\u3001\u4f4d\u7f6e<\/span><\/p>\n

                                      8.5 \u4e24\u9636\u6bb5\u63d0\u4ea4：\u4e3a\u4ec0\u4e48\u66f4\u65b0\u8981\u5199\u4e24\u6b21\u65e5\u5fd7<\/h4>\n

                                      redo log（InnoDB）\u548c binlog（Server）\u662f\u4e24\u4e2a\u72ec\u7acb\u7684\u65e5\u5fd7\u7cfb\u7edf\u3002\u5047\u8bbe\u4e0d\u7528\u4e24\u9636\u6bb5\u3001\u7b80\u5355\u5730\u300c\u5148 redo \u540e binlog\u300d，\u4e2d\u95f4\u5d29\u6e83：\u6062\u590d\u540e\u4e3b\u5e93\u6709\u8fd9\u6761\u6570\u636e（redo \u5df2\u843d\u76d8），binlog \u91cc\u5374\u6ca1\u6709 \u2192 \u4ece\u5e93\u6c38\u8fdc\u5c11\u8fd9\u6761 \u2192 \u4e3b\u4ece\u4e0d\u4e00\u81f4\u3002\u53cd\u8fc7\u6765\u5148 binlog \u540e redo，\u5219\u4ece\u5e93\u591a\u4e00\u6761\u3002\u4e24\u9636\u6bb5\u63d0\u4ea4\u5c31\u662f\u8ba9\u4e24\u672c\u65e5\u5fd7\u300c\u8981\u4e48\u90fd\u7b97\u6570，\u8981\u4e48\u90fd\u4e0d\u7b97\u300d：<\/p>\n

                                      InnoDB<\/p>\n

                                      \u6267\u884c\u5668<\/p>\n

                                      InnoDB<\/p>\n

                                      \u6267\u884c\u5668<\/p>\n

                                      #mermaid-svg-1aTCO8TcWnj9kMl7{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-1aTCO8TcWnj9kMl7 .error-icon{fill:#552222;}#mermaid-svg-1aTCO8TcWnj9kMl7 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-1aTCO8TcWnj9kMl7 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .marker.cross{stroke:#333333;}#mermaid-svg-1aTCO8TcWnj9kMl7 svg{font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-1aTCO8TcWnj9kMl7 p{margin:0;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-1aTCO8TcWnj9kMl7 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .sequenceNumber{fill:white;}#mermaid-svg-1aTCO8TcWnj9kMl7 #sequencenumber{fill:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageText{fill:#333;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 .labelText,#mermaid-svg-1aTCO8TcWnj9kMl7 .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .loopText,#mermaid-svg-1aTCO8TcWnj9kMl7 .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-1aTCO8TcWnj9kMl7 .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-1aTCO8TcWnj9kMl7 .noteText,#mermaid-svg-1aTCO8TcWnj9kMl7 .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actorPopupMenu{position:absolute;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 \/ 0.4));}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-man circle,#mermaid-svg-1aTCO8TcWnj9kMl7 line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-1aTCO8TcWnj9kMl7 :root{–mermaid-font-family:\\”trebuchet ms\\”,verdana,arial,sans-serif;}<\/p>\n

                                      UPDATE orders SET status = 1 WHERE id = 10<\/p>\n

                                      \u5199 undo log（\u8bb0\u5f55\u65e7\u503c）<\/p>\n

                                      Buffer Pool \u4e2d\u4fee\u6539\u6570\u636e\u9875（\u810f\u9875）<\/p>\n

                                      \u8fd4\u56de\u5f71\u54cd\u884c\u6570<\/p>\n

                                      COMMIT<\/p>\n

                                      \u2460 redo log \u5199\u5165，\u72b6\u6001 = prepare<\/p>\n

                                      \u53ef\u4ee5\u63d0\u4ea4<\/p>\n

                                      \u2461 binlog \u5199\u5165\u5e76\u843d\u76d8<\/p>\n

                                      \u2462 \u4e8b\u52a1\u63d0\u4ea4<\/p>\n

                                      redo log \u72b6\u6001 = commit，\u5b8c\u6210<\/p>\n

                                      \u8bb0\u5fc6\u951a\u70b9：\u5d29\u6e83\u6062\u590d\u7684\u88c1\u51b3\u89c4\u5219\u3002 \u91cd\u542f\u540e\u626b\u63cf redo log：\u72b6\u6001\u662f commit \u2192 \u76f4\u63a5\u63d0\u4ea4；\u72b6\u6001\u662f prepare \u2192 \u53bb\u67e5 binlog：\u8fd9\u6761\u4e8b\u52a1\u7684 binlog \u5b8c\u6574（\u6709\u5bf9\u5e94 XID）\u2192 \u63d0\u4ea4；\u4e0d\u5b8c\u6574 \u2192 \u56de\u6eda\u3002\u4e00\u53e5\u8bdd：\u300credo \u8bf4\u6539\u4e86，\u8fd8\u5f97 binlog \u62cd\u677f\u300d\u3002<\/p>\n

                                      \u4e0e\u4e24\u9636\u6bb5\u63d0\u4ea4\u914d\u5957\u7684\u8fd8\u6709 binlog \u7684\u843d\u76d8\u53c2\u6570 sync_binlog：0（\u4ea4\u7ed9 OS，\u53ef\u80fd\u4e22）\u30011（\u6bcf\u6b21\u63d0\u4ea4 fsync，\u6700\u5b89\u5168）\u3001N（\u6512 N \u4e2a\u4e8b\u52a1\u518d fsync）\u3002\u751f\u4ea7\u6807\u51c6\u914d\u7f6e\u662f\u300c\u53cc 1\u300d：innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1\u2014\u2014\u94b1\u76f8\u5173\u7684\u7cfb\u7edf\u522b\u7701\u8fd9\u4e2a IO\u3002<\/p>\n

                                      \n

                                      \u4e5d\u3001\u6027\u80fd\u4f18\u5316\u5b9e\u6218<\/h3>\n

                                      \u524d\u9762\u516b\u7ae0\u7684\u77e5\u8bc6，\u6700\u7ec8\u90fd\u6c47\u5230\u8fd9\u4e00\u7ae0\u4f7f\u7528\u3002\u4f18\u5316\u7684\u6b63\u786e\u987a\u5e8f\u6c38\u8fdc\u662f：\u5148\u627e\u5230\u6162\u7684 SQL（\u6162\u67e5\u8be2\u65e5\u5fd7），\u518d\u770b\u5b83\u4e3a\u4ec0\u4e48\u6162（EXPLAIN），\u6700\u540e\u5bf9\u75c7\u4e0b\u836f（\u7d22\u5f15\/SQL \u6539\u5199\/\u53c2\u6570\/\u67b6\u6784）\u3002\u53cd\u8fc7\u6765\u300c\u51ed\u611f\u89c9\u52a0\u7d22\u5f15\u300d\u662f\u65b0\u4eba\u6700\u5e38\u89c1\u7684\u5f2f\u8def\u3002<\/p>\n

                                      9.1 \u7b2c\u4e00\u6b65：\u8ba9\u6162 SQL \u81ea\u5df1\u73b0\u8eab<\/h4>\n

                                      — \u5f00\u542f\u6162\u67e5\u8be2\u65e5\u5fd7：\u8bb0\u5f55\u6240\u6709\u8d85\u8fc7 1 \u79d2\u7684 SQL（\u4ee5\u53ca\u6ca1\u8d70\u7d22\u5f15\u7684\u5168\u8868\u626b\u63cf）<\/span>
                                      \nSET<\/span> GLOBAL<\/span> slow_query_log =<\/span> ON<\/span>;<\/span>
                                      \nSET<\/span> GLOBAL<\/span> long_query_time =<\/span> 1<\/span>;<\/span> — \u7ebf\u4e0a\u4e00\u822c\u4ece 1s \u8d77\u6b65，\u9010\u6b65\u6536\u7d27\u5230 0.5 \/ 0.2<\/span><\/p>\n

                                      — \u627e\u5230\u65e5\u5fd7\u4f4d\u7f6e<\/span>
                                      \nSHOW<\/span> VARIABLES LIKE<\/span> 'slow_query_log_file'<\/span>;<\/span><\/p>\n

                                      — 8.0 \u76f4\u63a5\u67e5 performance_schema，\u805a\u5408\u51fa Top \u6162 SQL<\/span>
                                      \nSELECT<\/span> DIGEST_TEXT AS<\/span> sql_pattern,<\/span>
                                      \n COUNT_STAR AS<\/span> exec_count,<\/span>
                                      \n ROUND<\/span>(<\/span>AVG_TIMER_WAIT\/<\/span>1<\/span>e12,<\/span> 3<\/span>)<\/span> AS<\/span> avg_sec,<\/span>
                                      \n ROUND<\/span>(<\/span>SUM_TIMER_WAIT\/<\/span>1<\/span>e12,<\/span> 3<\/span>)<\/span> AS<\/span> total_sec
                                      \nFROM<\/span> performance_schema.<\/span>events_statements_summary_by_digest
                                      \nORDER<\/span> BY<\/span> SUM_TIMER_WAIT DESC<\/span>
                                      \nLIMIT<\/span> 5<\/span>;<\/span>
                                      \n— \u6309 total_sec \u6392\u5e8f：\u603b\u8017\u65f6\u6700\u591a\u7684\u5148\u6cbb\u7406；\u6309 avg_sec \u6392\u5e8f：\u5355\u6b21\u6700\u75bc\u7684\u5148\u6cbb\u7406<\/span><\/p>\n

                                      9.2 \u770b\u6574\u4f53\u5065\u5eb7\u5ea6<\/h4>\n

                                      SHOW<\/span> GLOBAL<\/span> STATUS<\/span> LIKE<\/span> 'Threads_running'<\/span>;<\/span> — \u5f53\u524d\u6d3b\u8dc3\u7ebf\u7a0b，\u98d9\u9ad8 = \u5e76\u53d1\u538b\u529b\u6216\u9501\u7b49\u5f85<\/span>
                                      \nSHOW<\/span> GLOBAL<\/span> STATUS<\/span> LIKE<\/span> 'Innodb_row_lock%'<\/span>;<\/span> — \u884c\u9501\u7edf\u8ba1<\/span>
                                      \n— Innodb_row_lock_waits \/ Innodb_row_lock_time_avg \u9ad8 \u2192 \u7b2c 7 \u7ae0\u7684\u9501\u95ee\u9898<\/span><\/p>\n

                                      — Buffer Pool \u547d\u4e2d\u7387：\u4f4e\u4e8e 99% \u8bf4\u660e\u5185\u5b58\u4e0d\u591f\u6216\u5b58\u5728\u5168\u8868\u626b\u63cf<\/span>
                                      \nSHOW<\/span> GLOBAL<\/span> STATUS<\/span> LIKE<\/span> 'Innodb_buffer_pool_read%'<\/span>;<\/span>
                                      \n— \u547d\u4e2d\u7387 = 1 – Innodb_buffer_pool_reads \/ Innodb_buffer_pool_read_requests<\/span><\/p>\n

                                      9.3 \u5b9e\u6218\u6848\u4f8b\u4e00：\u6df1\u5206\u9875\u62d6\u57ae\u540e\u53f0\u5bfc\u51fa<\/h4>\n

                                      \u75c7\u72b6：\u8fd0\u8425\u540e\u53f0\u5bfc\u51fa\u8ba2\u5355，\u7ffb\u5230\u51e0\u767e\u9875\u540e\u6bcf\u9875\u8981 5s+，\u671f\u95f4\u6574\u5e93 RT \u4e0a\u5347\u3002<\/p>\n

                                      — \u539f\u59cb SQL：LIMIT 500000, 20 \u2014\u2014 \u7d22\u5f15\u80fd\u5b9a\u4f4d\u8d77\u70b9，\u4f46\u4f18\u5316\u5668\u4ecd\u8981\u6570\u8fc750\u4e07\u884c\u5e76\u56de\u8868<\/span>
                                      \nSELECT<\/span> *<\/span> FROM<\/span> orders ORDER<\/span> BY<\/span> id LIMIT<\/span> 500000<\/span>,<\/span> 20<\/span>;<\/span><\/p>\n

                                      — \u5904\u65b9（\u6e38\u6807\u7ffb\u9875，4.6 \u8282\u8bb2\u8fc7）：WHERE id > \u4e0a\u9875\u6700\u540eid，\u4e00\u6b65\u8df3\u5230\u4f4d<\/span>
                                      \nSELECT<\/span> *<\/span> FROM<\/span> orders WHERE<\/span> id ><\/span> 500000<\/span> ORDER<\/span> BY<\/span> id LIMIT<\/span> 20<\/span>;<\/span> — 5s \u2192 8ms<\/span>
                                      \n— \u5bfc\u51fa\u573a\u666f\u6ca1\u6709"\u4e0a\u4e00\u9875"\u6309\u94ae，\u5929\u7136\u9002\u5408\u6e38\u6807；\u9875\u9762\u8df3\u8f6c\u573a\u666f\u7528\u5ef6\u8fdf\u5173\u8054\u515c\u5e95<\/span><\/p>\n

                                      9.4 \u5b9e\u6218\u6848\u4f8b\u4e8c：\u4e00\u6761\u7edf\u8ba1 SQL \u62d6\u57ae\u6574\u4e2a\u670d\u52a1<\/h4>\n

                                      \u75c7\u72b6：\u665a\u9ad8\u5cf0\u8ba2\u5355\u521b\u5efa\u5168\u90e8\u8d85\u65f6，\u4f46\u8ba2\u5355\u8868 QPS \u5e76\u4e0d\u9ad8\u3002<\/p>\n

                                      — \u7f6a\u9b41：\u8fd0\u8425\u5199\u4e86\u6761\u7edf\u8ba1 SQL，WHERE \u91cc\u7684\u5217\u5957\u4e86\u51fd\u6570<\/span>
                                      \nSELECT<\/span> COUNT<\/span>(<\/span>*<\/span>)<\/span> FROM<\/span> orders WHERE<\/span> DATE<\/span>(<\/span>created_at)<\/span> =<\/span> '2026-09-24'<\/span>;<\/span>
                                      \n— EXPLAIN: type=ALL, rows=\u5168\u8868 \u2192 \u5168\u8868\u626b\u63cf<\/span><\/p>\n

                                      — \u66f4\u7cdf\u7684\u662f\u540c\u4e00\u6761\u8fde\u63a5\u968f\u540e\u6267\u884c\u4e86：<\/span>
                                      \nUPDATE<\/span> orders SET<\/span> status<\/span> =<\/span> 4<\/span> WHERE<\/span> DATE<\/span>(<\/span>created_at)<\/span> <<\/span> '2026-09-01'<\/span>;<\/span>
                                      \n— \u6ca1\u8d70\u7d22\u5f15\u7684 UPDATE = \u5168\u8868\u9010\u884c\u52a0\u9501（7.4 \u8282）\u2192 \u6240\u6709\u8ba2\u5355\u66f4\u65b0\u6392\u961f \u2192 \u4e1a\u52a1\u96ea\u5d29<\/span><\/p>\n

                                      — \u5904\u65b9：\u6539\u6210\u8303\u56f4\u6761\u4ef6，\u7d22\u5f15\u590d\u6d3b<\/span>
                                      \nSELECT<\/span> COUNT<\/span>(<\/span>*<\/span>)<\/span> FROM<\/span> orders
                                      \nWHERE<\/span> created_at >=<\/span> '2026-09-24'<\/span> AND<\/span> created_at <<\/span> '2026-09-25'<\/span>;<\/span> — \u8d70 idx_status_created<\/span>
                                      \nUPDATE<\/span> orders SET<\/span> status<\/span> =<\/span> 4<\/span>
                                      \nWHERE<\/span> created_at <<\/span> '2026-09-01'<\/span>;<\/span> — \u8d70\u7d22\u5f15，\u53ea\u9501\u65e7\u884c<\/span>
                                      \n— \u6559\u8bad：\u51fd\u6570\u5305\u5217\u4e0d\u4ec5\u6162\u67e5\u8be2，\u6279\u91cf UPDATE \u65f6\u8fd8\u4f1a\u5347\u7ea7\u6210\u9501\u5168\u8868<\/span><\/p>\n

                                      9.5 \u5b9e\u6218\u6848\u4f8b\u4e09：COUNT(*) \u592a\u6162\u600e\u4e48\u6551<\/h4>\n

                                      \u75c7\u72b6：\u5546\u54c1\u5217\u8868\u9875\u8981\u663e\u793a\u603b\u6570，SELECT COUNT(*) FROM products \u8981 2s（200 \u4e07\u884c）\u3002<\/p>\n

                                      — \u8ba4\u6e05\u4e8b\u5b9e：InnoDB \u7684 COUNT(*) \u5fc5\u987b\u771f\u6570（MVCC \u4e0b\u6bcf\u4e2a\u4e8b\u52a1\u53ef\u89c1\u884c\u6570\u4e0d\u540c），\u6ca1\u6709\u6377\u5f84<\/span>
                                      \n— \u4f46\u53ef\u4ee5\u7ed9 COUNT \u52a0\u8fc7\u6ee4\u6761\u4ef6，\u8ba9\u5b83\u8d70\u8986\u76d6\u7d22\u5f15\u514d\u56de\u8868：<\/span>
                                      \nSELECT<\/span> COUNT<\/span>(<\/span>*<\/span>)<\/span> FROM<\/span> products WHERE<\/span> category_id =<\/span> 3<\/span>;<\/span> — \u8d70 idx_category，\u53ea\u626b\u7d22\u5f15\u6811<\/span><\/p>\n

                                      — \u9875\u9762\u4e0d\u8981\u6c42\u7cbe\u786e\u65f6\u7684\u4e09\u5c42\u65b9\u6848：<\/span>
                                      \n— \u2460 \u663e\u793a"\u7ea6 N \u4e07"：explain \u7684 rows \u4f30\u7b97\u503c\u76f4\u63a5\u62ff\u6765\u7528<\/span>
                                      \nEXPLAIN<\/span> SELECT<\/span> *<\/span> FROM<\/span> products;<\/span> — rows \u5217 \u2248 \u884c\u6570，\u6beb\u79d2\u7ea7<\/span>
                                      \n— \u2461 \u8ba1\u6570\u8868：\u8ba1\u6570\u9891\u7e41\u7684\u573a\u666f，\u5355\u72ec\u7ef4\u62a4 counter \u8868\u6216 Redis \u8ba1\u6570<\/span>
                                      \n— \u2462 \u6c47\u603b\u8868：\u79bb\u7ebf\/\u5b9a\u65f6\u4efb\u52a1\u6bcf 5 \u5206\u949f\u805a\u5408\u4e00\u6b21\u5199\u8fdb stats \u8868，\u524d\u7aef\u8bfb\u5b83<\/span><\/p>\n

                                      9.6 \u53c2\u6570\u8c03\u4f18\u901f\u67e5（8.0 \u9ed8\u8ba4\u914d\u7f6e\u57fa\u7840\u4e0a）<\/h4>\n\n\u53c2\u6570\u9ed8\u8ba4\u5efa\u8bae\u4e3a\u4ec0\u4e48<\/tr>\n\n\n\n\n\n\n\n
                                      innodb_buffer_pool_size<\/td>\n128M<\/td>\n\u7269\u7406\u5185\u5b58\u7684 50%~70%<\/td>\n\u6570\u636e\u9875\u7f13\u5b58，\u547d\u4e2d\u7387\u662f\u4e00\u5207\u6027\u80fd\u7684\u5730\u57fa<\/td>\n<\/tr>\n
                                      innodb_flush_log_at_trx_commit<\/td>\n1<\/td>\n1（\u6838\u5fc3）\/ 2（\u53ef\u5bb9\u5fcd\u79d2\u7ea7\u4e22\u5931）<\/td>\n\u7b2c 8 \u7ae0<\/td>\n<\/tr>\n
                                      sync_binlog<\/td>\n1<\/td>\n1<\/td>\n\u4e0e\u4e0a\u6761\u7ec4\u6210\u300c\u53cc 1\u300d，\u4e3b\u4ece\u4e00\u81f4\u6027\u57fa\u77f3<\/td>\n<\/tr>\n
                                      max_connections<\/td>\n151<\/td>\n\u6309\u8fde\u63a5\u6c60 \u00d7 \u5b9e\u4f8b\u6570\u8bc4\u4f30<\/td>\n\u4e0d\u662f\u8d8a\u5927\u8d8a\u597d：\u6bcf\u4e2a\u8fde\u63a5\u8017\u5185\u5b58，\u8fc7\u591a\u4e0a\u4e0b\u6587\u5207\u6362<\/td>\n<\/tr>\n
                                      innodb_log_file_size（redo）<\/td>\n48M<\/td>\n1~4G<\/td>\n\u592a\u5c0f\u9891\u7e41\u89e6\u53d1 checkpoint \u5237\u810f，\u4e1a\u52a1\u5361\u987f<\/td>\n<\/tr>\n
                                      slow_query_log + long_query_time<\/td>\n\u5173<\/td>\n\u5f00 + 1s \u8d77\u6b65<\/td>\n\u6ca1\u6709\u6162\u65e5\u5fd7\u7684\u7ebf\u4e0a\u5e93\u7b49\u4e8e\u76f2\u98de<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                      9.7 \u4f18\u5316\u5fc3\u6cd5：\u4e00\u5f20\u5730\u56fe<\/h4>\n

                                      \u8bb0\u5fc6\u951a\u70b9：\u6027\u80fd\u4f18\u5316\u7684\u4f18\u5148\u7ea7\u3002 SQL \u4e0e\u7d22\u5f15（\u6210\u672c\u6700\u4f4e\u6536\u76ca\u6700\u5927）\u2192 \u8868\u7ed3\u6784（\u5197\u4f59\/\u62c6\u5206\/\u51b7\u70ed\u5206\u79bb）\u2192 \u53c2\u6570（Buffer Pool\u3001\u65e5\u5fd7）\u2192 \u67b6\u6784（\u8bfb\u5199\u5206\u79bb\u3001\u7f13\u5b58\u3001\u5206\u5e93\u5206\u8868）\u3002 \u524d\u4e24\u5c42\u89e3\u51b3 90% \u7684\u95ee\u9898，\u6c38\u8fdc\u4ece\u5b83\u4eec\u5f00\u59cb；\u5206\u5e93\u5206\u8868\u662f\u6700\u540e\u7684\u5927\u62db，\u4e0d\u662f\u7b2c\u4e00\u53cd\u5e94\u3002<\/p>\n

                                      \n

                                      \u5341\u3001\u901f\u67e5\u624b\u518c\u4e0e\u590d\u4e60\u8def\u7ebf<\/h3>\n

                                      \u6700\u540e\u4e00\u7ae0\u662f\u300c\u8003\u524d\u51b2\u523a\u300d：20 \u4e2a\u9ad8\u9891\u95ee\u9898\u7684\u4e00\u53e5\u8bdd\u7b54\u6848，\u9002\u5408\u9762\u8bd5\u524d\u6216\u6392\u969c\u65f6\u5feb\u901f\u5524\u9192\u8bb0\u5fc6\u3002\u7b54\u6848\u91cc\u62ec\u53f7\u5185\u7684\u7ae0\u8282\u53f7，\u5c31\u662f\u9700\u8981\u5c55\u5f00\u56de\u5fc6\u7684\u5165\u53e3\u3002<\/p>\n

                                      10.1 20 \u95ee\u901f\u7b54<\/h4>\n\n\u95ee\u9898\u4e00\u53e5\u8bdd\u7b54\u6848<\/tr>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
                                      1. MySQL 8.0 \u4e3a\u4ec0\u4e48\u5220\u4e86\u67e5\u8be2\u7f13\u5b58？<\/td>\n\u8868\u66f4\u65b0\u5373\u5931\u6548\u3001\u547d\u4e2d\u7387\u6781\u4f4e，\u5f97\u4e0d\u507f\u5931（1.2）<\/td>\n<\/tr>\n
                                      2. InnoDB \u548c MyISAM \u7684\u672c\u8d28\u533a\u522b？<\/td>\n\u4e8b\u52a1 + \u884c\u7ea7\u9501 + \u805a\u7c07\u7d22\u5f15 + \u5d29\u6e83\u6062\u590d（2.1）<\/td>\n<\/tr>\n
                                      3. \u91d1\u989d\u5b57\u6bb5\u7528\u4ec0\u4e48\u7c7b\u578b？<\/td>\nDECIMAL(10,2) \u6216\u5206\u5355\u4f4d BIGINT，\u7981\u6d6e\u70b9（2.2）<\/td>\n<\/tr>\n
                                      4. utf8 \u548c utf8mb4 \u7684\u533a\u522b？<\/td>\nutf8 \u662f\u6700\u591a 3 \u5b57\u8282\u7684\u9609\u5272\u7248，\u5b58\u4e0d\u4e86 emoji（2.6）<\/td>\n<\/tr>\n
                                      5. \u4e3a\u4ec0\u4e48\u4e3b\u952e\u63a8\u8350\u81ea\u589e\u6574\u578b？<\/td>\n\u987a\u5e8f\u63d2\u5165\u4e0d\u9875\u5206\u88c2\u3001\u6811\u77ee\u3001\u4e8c\u7ea7\u7d22\u5f15\u53f6\u5b50\u5c0f（5.2）<\/td>\n<\/tr>\n
                                      6. B+ \u6811\u548c B \u6811\u7684\u533a\u522b？<\/td>\n\u975e\u53f6\u5b50\u8282\u70b9\u4e0d\u5b58\u6570\u636e（\u66f4\u77ee）+ \u53f6\u5b50\u5c42\u6709\u5e8f\u94fe\u8868（\u8303\u56f4\u67e5\u8be2）（5.1）<\/td>\n<\/tr>\n
                                      7. \u4ec0\u4e48\u662f\u56de\u8868？\u600e\u4e48\u907f\u514d？<\/td>\n\u4e8c\u7ea7\u7d22\u5f15\u67e5\u5230\u4e3b\u952e\u518d\u67e5\u805a\u7c07\u7d22\u5f15\u53d6\u6574\u884c；\u8986\u76d6\u7d22\u5f15\u907f\u514d（5.2 \/ 5.3）<\/td>\n<\/tr>\n
                                      8. \u8054\u5408\u7d22\u5f15 (a,b,c)，WHERE b=2 \u8d70\u7d22\u5f15\u5417？<\/td>\n\u4e0d\u8d70，\u7f3a\u6700\u5de6\u5217 a（5.4）<\/td>\n<\/tr>\n
                                      9. LIKE \u2018%xx%\u2019 \u4e3a\u4ec0\u4e48\u4e0d\u8d70\u7d22\u5f15？<\/td>\n\u524d\u5bfc\u4e0d\u786e\u5b9a，B+ \u6811\u65e0\u6cd5\u5b9a\u4f4d\u8d77\u70b9；\u2018xx%\u2019 \u76f8\u5f53\u4e8e\u8303\u56f4\u67e5\u8be2（5.5）<\/td>\n<\/tr>\n
                                      10. EXPLAIN \u91cc type=ALL \u8bf4\u660e\u4ec0\u4e48？<\/td>\n\u5168\u8868\u626b\u63cf，\u4f18\u5148\u6392\u67e5\u7d22\u5f15\u7f3a\u5931\u6216\u5931\u6548（5.6）<\/td>\n<\/tr>\n
                                      11. ACID \u5404\u9760\u4ec0\u4e48\u5b9e\u73b0？<\/td>\nA \u9760 undo\u3001D \u9760 redo\u3001I \u9760 MVCC+\u9501\u3001C \u662f\u524d\u4e09\u8005\u7684\u76ee\u7684（6.1）<\/td>\n<\/tr>\n
                                      12. RR \u548c RC \u7684\u672c\u8d28\u533a\u522b？<\/td>\nReadView \u751f\u6210\u65f6\u673a：RR \u7b2c\u4e00\u6b21\u67e5\u8be2\u751f\u6210\u540e\u590d\u7528，RC \u6bcf\u6b21\u67e5\u8be2\u91cd\u65b0\u751f\u6210（6.3）<\/td>\n<\/tr>\n
                                      13. MVCC \u662f\u600e\u4e48\u5b9e\u73b0\u7684？<\/td>\n\u9690\u85cf\u5217（trx_id\/roll_ptr）+ undo \u7248\u672c\u94fe + ReadView \u53ef\u89c1\u6027\u5224\u65ad（6.3）<\/td>\n<\/tr>\n
                                      14. RR \u5b8c\u5168\u89e3\u51b3\u5e7b\u8bfb\u4e86\u5417？<\/td>\n\u5feb\u7167\u8bfb\u9760 MVCC\u3001\u5f53\u524d\u8bfb\u9760\u4e34\u952e\u9501；\u5148\u5feb\u7167\u8bfb\u540e\u5f53\u524d\u8bfb\u4ecd\u53ef\u80fd\u5e7b\u8bfb（6.5）<\/td>\n<\/tr>\n
                                      15. \u884c\u9501\u52a0\u5728\u4ec0\u4e48\u4e0a\u9762？<\/td>\n\u7d22\u5f15\u4e0a；\u6761\u4ef6\u5217\u65e0\u7d22\u5f15 = \u9501\u5168\u8868（7.4）<\/td>\n<\/tr>\n
                                      16. \u600e\u4e48\u9632\u6b7b\u9501？<\/td>\n\u56fa\u5b9a\u52a0\u9501\u987a\u5e8f + \u77ed\u4e8b\u52a1 + \u6761\u4ef6\u66f4\u65b0 + \u70ed\u70b9\u884c\u51fa\u961f（7.5）<\/td>\n<\/tr>\n
                                      17. \u8d85\u5356\u600e\u4e48\u9632？<\/td>\n\u628a\u5e93\u5b58\u5224\u65ad\u5199\u8fdb UPDATE \u7684 WHERE（\u6761\u4ef6\u66f4\u65b0\/\u4e50\u89c2\u9501）（7.6）<\/td>\n<\/tr>\n
                                      18. \u4e3a\u4ec0\u4e48\u8981\u4e24\u9636\u6bb5\u63d0\u4ea4？<\/td>\n\u4fdd\u8bc1 redo log \u4e0e binlog \u7684\u539f\u5b50\u6027，\u5426\u5219\u5d29\u6e83\u540e\u4e3b\u4ece\u4e0d\u4e00\u81f4（8.5）<\/td>\n<\/tr>\n
                                      19. \u300c\u53cc 1\u300d\u914d\u7f6e\u662f\u4ec0\u4e48？<\/td>\ninnodb_flush_log_at_trx_commit=1 + sync_binlog=1，\u6700\u5b89\u5168\u843d\u76d8（8.5）<\/td>\n<\/tr>\n
                                      20. \u6df1\u5206\u9875\u6162\u600e\u4e48\u4f18\u5316？<\/td>\n\u6e38\u6807\u7ffb\u9875（WHERE id > \u4e0a\u9875\u672b\u5c3e）\u6216\u5ef6\u8fdf\u5173\u8054（4.6 \/ 9.3）<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                                      10.2 \u5e38\u7528\u8fd0\u7ef4\u547d\u4ee4\u8d34<\/h4>\n

                                      — \u770b\u72b6\u6001<\/span>
                                      \nSHOW<\/span> PROCESSLIST;<\/span> — \u5f53\u524d\u8fde\u63a5\u4e0e\u6b63\u5728\u6267\u884c\u7684 SQL<\/span>
                                      \nSHOW<\/span> ENGINE<\/span> INNODB<\/span> STATUS<\/span>\\\\G — \u6b7b\u9501\/\u9501\u7b49\u5f85\/\u7f13\u51b2\u6c60\u5168\u666f<\/span>
                                      \nSELECT<\/span> *<\/span> FROM<\/span> information_schema.<\/span>innodb_trx;<\/span> — \u6d3b\u8dc3\u4e8b\u52a1（DDL \u524d\u5fc5\u67e5）<\/span><\/p>\n

                                      — \u770b\u8868<\/span>
                                      \nSHOW<\/span> TABLE<\/span> STATUS<\/span> FROM<\/span> mall;<\/span> — \u5f15\u64ce\u3001\u884c\u6570\u3001\u5e73\u5747\u884c\u957f<\/span>
                                      \nSHOW<\/span> INDEX<\/span> FROM<\/span> orders;<\/span> — \u7d22\u5f15\u6e05\u5355<\/span>
                                      \nSHOW<\/span> CREATE<\/span> TABLE<\/span> orders\\\\G — \u5b8c\u6574\u5efa\u8868\u8bed\u53e5<\/span><\/p>\n

                                      — \u770b\u6267\u884c<\/span>
                                      \nEXPLAIN<\/span> ANALYZE<\/span> SELECT<\/span> .<\/span>.<\/span>.<\/span>;<\/span> — 8.0 \u7684 ANALYZE \u4f1a\u771f\u7684\u6267\u884c\u5e76\u7ed9\u771f\u5b9e\u8017\u65f6<\/span><\/p>\n

                                      — \u5907\u4efd\u4e0e\u6062\u590d<\/span>
                                      \nmysqldump –single-transaction -uroot -p mall > mall_backup.sql<\/span>
                                      \nmysql –<\/span>uroot –<\/span>p mall <<\/span> mall_backup.<\/span>sql<\/span><\/p>\n

                                      10.3 \u4e09\u8f6e\u590d\u4e60\u8def\u7ebf<\/h4>\n
                                        \n
                                      • \u7b2c\u4e00\u8f6e \u00b7 \u4e32\u4e3b\u7ebf（\u4eca\u5929）：\u53ea\u8bfb\u6bcf\u7ae0\u7684\u300c\u8bb0\u5fc6\u951a\u70b9\u300d\u548c\u56db\u5f20\u56fe，\u628a\u300cSQL \u751f\u547d\u5468\u671f \u2192 \u7d22\u5f15 \u2192 MVCC \u2192 \u9501 \u2192 \u65e5\u5fd7\u300d\u8fd9\u6761\u7ebf\u8fde\u8d77\u6765，\u76ee\u6807\u662f\u5408\u4e0a\u9875\u9762\u80fd\u590d\u8ff0\u8fd9\u6761\u7ebf；<\/li>\n
                                      • \u7b2c\u4e8c\u8f6e \u00b7 \u8dd1\u5b9e\u6218（\u672c\u5468）：\u628a mall \u4e94\u5f20\u8868\u5efa\u51fa\u6765，\u4eb2\u624b\u8dd1\u7b2c 3\u30014 \u7ae0\u7684\u6bcf\u4e00\u6bb5 SQL，\u505a\u4e00\u904d 5.7 \u8282\u7684\u7d22\u5f15\u4f18\u5316\u5b9e\u9a8c\u548c 7.5 \u8282\u7684\u6b7b\u9501\u590d\u73b0\u2014\u2014\u624b\u8fc7\u4e00\u904d\u9876\u770b\u5341\u904d；<\/li>\n
                                      • \u7b2c\u4e09\u8f6e \u00b7 \u5237\u901f\u67e5（\u9762\u8bd5\u524d\u591c）：\u53ea\u770b 10.1 \u7684 20 \u95ee，\u7b54\u4e0d\u4e0a\u6765\u7684\u6309\u62ec\u53f7\u91cc\u7684\u7ae0\u8282\u53f7\u56de\u53bb\u8865\u90a3\u4e00\u6bb5\u3002<\/li>\n<\/ul>\n

                                        \u6700\u540e\u4e00\u5757\u62fc\u56fe：\u6240\u6709 MySQL \u77e5\u8bc6\u6700\u7ec8\u6536\u655b\u6210\u4e00\u53e5\u8bdd\u2014\u2014\u4e00\u6761 SQL \u8fdb\u6765，\u670d\u52a1\u5c42\u89e3\u6790\u4f18\u5316，\u5f15\u64ce\u5c42\u7528 B+ \u6811\u627e\u6570\u636e，\u9760 MVCC \u8ba9\u8bfb\u5199\u4e0d\u6253\u67b6，\u9760\u9501\u8ba9\u5199\u5199\u6392\u961f，\u9760 redo\/undo\/binlog \u4fdd\u8bc1\u4e0d\u4e22\u4e0d\u9519\u3002\u80fd\u9ed8\u5199\u8fd9\u53e5\u8bdd，\u4f60\u5c31\u6ca1\u5fd8\u3002<\/p>\n

                                        \n

                                        MySQL \u77e5\u8bc6\u5168\u666f\u56de\u987e \u00b7 \u57fa\u4e8e MySQL 8.0 \/ InnoDB \u00b7 2026-09 \u6574\u7406 \u00b7 \u6b22\u8fce\u6536\u85cf\u590d\u4e60，\u8f6c\u8f7d\u8bf7\u6ce8\u660e\u51fa\u5904<\/p>\n","protected":false},"excerpt":{"rendered":"

                                        \u8fd9\u7bc7\u7b14\u8bb0\u6cbf\u7740\u300c\u4e00\u6761 SQL \u8bed\u53e5\u5728 MySQL \u91cc\u7684\u5b8c\u6574\u65c5\u7a0b\u300d\u8fd9\u6761\u4e3b\u7ebf，\u628a\u67b6\u6784\u3001\u5b58\u50a8\u5f15\u64ce\u3001\u7d22\u5f15\u3001\u4e8b\u52a1\u3001MVCC\u3001\u9501\u3001\u65e5\u5fd7\u4e0e\u6027\u80fd\u4f18\u5316\u5168\u90e8\u4e32\u8d77\u6765\u2014\u2014\u6bcf\u7ae0\u90fd\u914d\u53ef\u8dd1\u7684\u5b9e\u6218 SQL（\u7535\u5546\u5e93 mall），\u7ed3\u5c3e\u9644 20 \u95ee\u901f\u7b54\u3002\u5fd8\u4e86\u6ca1\u5173\u7cfb，\u8ddf\u7740\u4e3b\u7ebf\u8d70\u4e00\u904d，\u77e5\u8bc6\u70b9\u4f1a\u81ea\u5df1\u300c\u957f\u300d\u56de\u8111\u5b50\u91cc\u3002 \u9002\u7528\u7248\u672c：MySQL 8.0 \u00b7 InnoDB \u00b7 10 \u7ae0 \u00b7 40 \u6bb5\u5b9e\u6218 SQL \u00b7 20 \u95ee\u901f\u67e5 \u76ee\u5f55<\/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":[99,114,768,190],"topic":[],"class_list":["post-110006","post","type-post","status-publish","format-standard","hentry","category-server","tag-java","tag-mysql","tag-sql","tag-190"],"yoast_head":"\n\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \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\/110006.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u8fd9\u7bc7\u7b14\u8bb0\u6cbf\u7740\u300c\u4e00\u6761 SQL \u8bed\u53e5\u5728 MySQL \u91cc\u7684\u5b8c\u6574\u65c5\u7a0b\u300d\u8fd9\u6761\u4e3b\u7ebf，\u628a\u67b6\u6784\u3001\u5b58\u50a8\u5f15\u64ce\u3001\u7d22\u5f15\u3001\u4e8b\u52a1\u3001MVCC\u3001\u9501\u3001\u65e5\u5fd7\u4e0e\u6027\u80fd\u4f18\u5316\u5168\u90e8\u4e32\u8d77\u6765\u2014\u2014\u6bcf\u7ae0\u90fd\u914d\u53ef\u8dd1\u7684\u5b9e\u6218 SQL（\u7535\u5546\u5e93 mall），\u7ed3\u5c3e\u9644 20 \u95ee\u901f\u7b54\u3002\u5fd8\u4e86\u6ca1\u5173\u7cfb，\u8ddf\u7740\u4e3b\u7ebf\u8d70\u4e00\u904d，\u77e5\u8bc6\u70b9\u4f1a\u81ea\u5df1\u300c\u957f\u300d\u56de\u8111\u5b50\u91cc\u3002 \u9002\u7528\u7248\u672c：MySQL 8.0 \u00b7 InnoDB \u00b7 10 \u7ae0 \u00b7 40 \u6bb5\u5b9e\u6218 SQL \u00b7 20 \u95ee\u901f\u67e5 \u76ee\u5f55\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/110006.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-09-25T07:40:44+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=\"34 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/110006.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/110006.html\",\"name\":\"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-09-25T07:40:44+00:00\",\"dateModified\":\"2026-09-25T07:40:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/110006.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/110006.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/110006.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d\"}]},{\"@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":"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \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\/110006.html","og_locale":"zh_CN","og_type":"article","og_title":"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u8fd9\u7bc7\u7b14\u8bb0\u6cbf\u7740\u300c\u4e00\u6761 SQL \u8bed\u53e5\u5728 MySQL \u91cc\u7684\u5b8c\u6574\u65c5\u7a0b\u300d\u8fd9\u6761\u4e3b\u7ebf，\u628a\u67b6\u6784\u3001\u5b58\u50a8\u5f15\u64ce\u3001\u7d22\u5f15\u3001\u4e8b\u52a1\u3001MVCC\u3001\u9501\u3001\u65e5\u5fd7\u4e0e\u6027\u80fd\u4f18\u5316\u5168\u90e8\u4e32\u8d77\u6765\u2014\u2014\u6bcf\u7ae0\u90fd\u914d\u53ef\u8dd1\u7684\u5b9e\u6218 SQL（\u7535\u5546\u5e93 mall），\u7ed3\u5c3e\u9644 20 \u95ee\u901f\u7b54\u3002\u5fd8\u4e86\u6ca1\u5173\u7cfb，\u8ddf\u7740\u4e3b\u7ebf\u8d70\u4e00\u904d，\u77e5\u8bc6\u70b9\u4f1a\u81ea\u5df1\u300c\u957f\u300d\u56de\u8111\u5b50\u91cc\u3002 \u9002\u7528\u7248\u672c：MySQL 8.0 \u00b7 InnoDB \u00b7 10 \u7ae0 \u00b7 40 \u6bb5\u5b9e\u6218 SQL \u00b7 20 \u95ee\u901f\u67e5 \u76ee\u5f55","og_url":"https:\/\/www.wsisp.com\/helps\/110006.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-09-25T07:40:44+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"34 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/110006.html","url":"https:\/\/www.wsisp.com\/helps\/110006.html","name":"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-09-25T07:40:44+00:00","dateModified":"2026-09-25T07:40:44+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/110006.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/110006.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/110006.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"\u4ece\u4e00\u6761 SQL \u7684\u751f\u547d\u5468\u671f\u5f00\u59cb\uff0c\u628a MySQL \u4e00\u6b21\u6027\u60f3\u660e\u767d"}]},{"@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\/110006","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=110006"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/110006\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=110006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=110006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=110006"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=110006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}