![\"\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2025\/05\/20250506091725-6819d3a51c646.png\")
<\/p>\n\u00a0\u601d\u7ef4\u5bfc\u56fe:<\/p>\n
<\/p>\n
\u00a0<\/p>\n
\u00a0 \u5728 MySQL \u6570\u636e\u5e93\u7ba1\u7406\u4e2d，\u6162\u67e5\u8be2\u662f\u5f71\u54cd\u7cfb\u7edf\u6027\u80fd\u7684\u5e38\u89c1\u75db\u70b9\u3002\u968f\u7740 MySQL 8 \u7248\u672c\u7684\u666e\u53ca，\u5176\u65b0\u589e\u7279\u6027（\u5982 CTE\u3001\u9690\u85cf\u7d22\u5f15\u3001JSON \u683c\u5f0f\u6267\u884c\u8ba1\u5212\u7b49）\u4e3a\u6162\u67e5\u8be2\u4f18\u5316\u63d0\u4f9b\u4e86\u66f4\u5f3a\u5927\u7684\u5de5\u5177\u3002\u672c\u6587\u7ed3\u5408 MySQL 8 \u7684\u7279\u6027，\u901a\u8fc7\u4ee3\u7801\u793a\u4f8b\u8be6\u89e3\u6162\u67e5\u8be2\u7684\u5b9a\u4f4d\u3001\u5206\u6790\u4e0e\u4f18\u5316\u5168\u6d41\u7a0b，\u4ece\u800c\u63d0\u9ad8\u6570\u636e\u5e93\u7684\u6027\u80fd\u3002<\/p>\n<\/p>\n
\u4fee\u6539 MySQL 8 \u7684\u914d\u7f6e\u6587\u4ef6my.cnf（Linux）\u6216my.ini（Windows），\u6dfb\u52a0\u4ee5\u4e0b\u6838\u5fc3\u914d\u7f6e：\u4fee\u6539\u540e\u9700\u91cd\u542f MySQL \u670d\u52a1\u4f7f\u914d\u7f6e\u751f\u6548\u3002<\/p>\n
[mysqld]
\nslow_query_log = 1 # \u542f\u7528\u6162\u67e5\u8be2\u65e5\u5fd7（8.0+\u9ed8\u8ba4\u5173\u95ed）
\nslow_query_log_file = \/var\/log\/mysql\/mysql-slow.log # \u65e5\u5fd7\u6587\u4ef6\u8def\u5f84
\nlong_query_time = 1.0 # \u6162\u67e5\u8be2\u9608\u503c（\u8d85\u8fc71\u79d2\u8bb0\u5f55，\u5efa\u8bae\u6839\u636e\u4e1a\u52a1\u8c03\u6574）
\nlog_queries_not_using_indexes = 1 # \u8bb0\u5f55\u672a\u4f7f\u7528\u7d22\u5f15\u7684\u67e5\u8be2（\u5173\u952e\u4f18\u5316\u7ebf\u7d22）
\nlog_slow_admin_statements = 1 # \u8bb0\u5f55\u6162\u7ba1\u7406\u8bed\u53e5（\u5982ALTER TABLE，8.0\u65b0\u589e） <\/p>\n
\u914d\u7f6e\u5b8c\u6bd5\u4e4b\u540e，\u901a\u8fc7\u4ee5\u4e0b\u6307\u4ee4\u91cd\u65b0\u542f\u52a8MySQL\u670d\u52a1\u5668\u8fdb\u884c\u6d4b\u8bd5，\u67e5\u770b\u6162\u65e5\u5fd7\u6587\u4ef6\u4e2d\u8bb0\u5f55\u7684\u4fe1\u606f\/var\/lib\/mysql\/localhost-slow.log\u3002\u5982\u56fe\u6240\u793a：<\/p>\n
![\"\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2025\/05\/20250506091725-6819d3a5497af.png\")
<\/p>\n<\/p>\n
\u00a0<\/p>\n
\u901a\u8fc7 SQL \u547d\u4ee4\u5b9e\u65f6\u5f00\u542f\u6162\u65e5\u5fd7（\u9700SUPER\u6743\u9650）：<\/p>\n
SET GLOBAL slow_query_log = ON;
\nSET GLOBAL long_query_time = 1; — \u9608\u503c\u8bbe\u4e3a1\u79d2
\nSET GLOBAL log_queries_not_using_indexes = ON; <\/p>\n
\u5173\u952e\u6307\u6807\u89e3\u8bfb：<\/p>\n
\u5206\u6790\u5de5\u5177： \u539f\u751f\u5de5\u5177：\u4f7f\u7528mysqldumpslow\u8fc7\u6ee4\u65e5\u5fd7（\u8bed\u6cd5\u4e0e 5.5 \u517c\u5bb9）： \u00a0<\/p>\n
# \u6309\u8017\u65f6\u6392\u5e8f，\u83b7\u53d6\u6700\u6162\u76845\u6761\u67e5\u8be2
\nmysqldumpslow -s t -t 5 \/var\/log\/mysql\/mysql-slow.log <\/p>\n
# \u6309\u626b\u63cf\u884c\u6570\u6392\u5e8f，\u5b9a\u4f4d\u626b\u63cf\u884c\u6570\u6700\u591a\u7684\u67e5\u8be2
\nmysqldumpslow -s r -t 5 \/var\/log\/mysql\/mysql-slow.log<\/p>\n
\u53ef\u89c6\u5316\u5de5\u5177：\u63a8\u8350\u4f7f\u7528pt-query-digest（Percona Toolkit \u7ec4\u4ef6），\u751f\u6210\u5305\u542b\u6267\u884c\u9891\u7387\u3001\u5e73\u5747\u8017\u65f6\u3001\u7d22\u5f15\u4f7f\u7528\u60c5\u51b5\u7684\u8be6\u7ec6\u62a5\u544a：<\/p>\n
pt-query-digest \/var\/log\/mysql\/mysql-slow.log > slow_query_analysis.txt <\/p>\n
\u793a\u4f8b\u65e5\u5fd7\u7247\u6bb5：<\/p>\n
# Time: 2025-05-05T15:00:00+08:00
\n# User@Host: app_user[app_user] @ 192.168.1.100 []
\n# Query_time: 2.865912 Lock_time: 0.000045 Rows_sent: 20 Rows_examined: 500000
\n# EXPLAIN for: SELECT * FROM orders WHERE status = 'processing' LIMIT 20; <\/p>\n
\u00a0\u95ee\u9898：\u626b\u63cf 50 \u4e07\u884c\u4ec5\u8fd4\u56de 20 \u884c，status\u5b57\u6bb5\u672a\u4f7f\u7528\u7d22\u5f15，\u89e6\u53d1\u5168\u8868\u626b\u63cf\u3002<\/p>\n<\/p>\n
MySQL 8 \u5f15\u5165EXPLAIN ANALYZE<\/span>\u547d\u4ee4，\u53ef\u83b7\u53d6\u66f4\u7cbe\u786e\u7684\u6267\u884c\u7edf\u8ba1\u4fe1\u606f（\u9700\u5f00\u542foptimizer_switch<\/span>\u4e2d\u7684derived_merge=off<\/span>\u4ee5\u907f\u514d\u4f18\u5316\u5668\u5408\u5e76\u5b50\u67e5\u8be2）\u3002\u6838\u5fc3\u5173\u6ce8\u5b57\u6bb5：<\/p>\n<\/p>\n \u6848\u4f8b：<\/p>\n \u5168\u8868\u626b\u63cf\u4f18\u5316（MySQL 8 \u4e13\u5c5e JSON \u683c\u5f0f\u8f93\u51fa）\u539f\u67e5\u8be2（\u672a\u52a0\u7d22\u5f15）：\u00a0<\/p>\n EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com'; <\/p>\n JSON \u6267\u884c\u8ba1\u5212\u7247\u6bb5（\u7b80\u5316\u540e）：<\/p>\n { \u4f18\u5316\u540e（\u6dfb\u52a0\u7d22\u5f15\u5e76\u4f7f\u7528\u8986\u76d6\u7d22\u5f15）：<\/p>\n ALTER TABLE users ADD INDEX idx_email_cover (email, name, phone); — \u8986\u76d6\u7d22\u5f15 \u6267\u884c\u8ba1\u5212：<\/p>\n { \u6548\u679c：\u626b\u63cf\u884c\u6570\u4ece 10 \u4e07\u964d\u81f3 1，\u67e5\u8be2\u65f6\u95f4\u4ece 2.5 \u79d2\u7f29\u77ed\u81f3 0.01 \u79d2\u3002<\/p>\n<\/p>\n \u9677\u9631 1：Like'%\u524d\u7f00'<\/font>\u5bfc\u81f4\u7d22\u5f15\u5931\u6548<\/p>\n \u9519\u8bef\u5199\u6cd5（\u5168\u8868\u626b\u63cf）：<\/p>\n SELECT * FROM products WHERE name LIKE '%\u7b14\u8bb0\u672c'; — \u4ee5%\u5f00\u5934，\u7d22\u5f15\u5931\u6548 <\/p>\n \u4f18\u5316（\u524d\u7f00\u5339\u914d，\u7d22\u5f15\u6709\u6548）：<\/p>\n SELECT * FROM products WHERE name LIKE '\u7b14\u8bb0\u672c%'; — \u5339\u914d\u201c\u7b14\u8bb0\u672c\u201d\u5f00\u5934\u7684\u5b57\u7b26\u4e32 <\/p>\n \u9519\u8bef\u5199\u6cd5（\u7d22\u5f15\u5931\u6548）：<\/p>\n SELECT * FROM orders WHERE user_id = 100 OR address LIKE '%\u4e0a\u6d77'; — user_id\u6709\u7d22\u5f15，address\u65e0\u7d22\u5f15 <\/p>\n \u4f18\u5316\u65b9\u6848：<\/p>\n ALTER TABLE orders ADD INDEX idx_address (address(20)); — \u524d\u7f00\u7d22\u5f15（MySQL 8\u652f\u6301） <\/p>\n SELECT * FROM orders WHERE user_id = 100 \u7d22\u5f15\u5b9a\u4e49：ALTER TABLE sales ADD INDEX idx_date_prod (order_date, product_id); \u6709\u6548\u67e5\u8be2（\u4f7f\u7528\u7d22\u5f15）：<\/p>\n — \u4ec5\u7528\u7b2c\u4e00\u5217（\u8303\u56f4\u67e5\u8be2） \u65e0\u6548\u67e5\u8be2（\u8df3\u8fc7\u7b2c\u4e00\u5217，\u7d22\u5f15\u5931\u6548）：<\/p>\n SELECT * FROM sales WHERE product_id = 101; — \u4ec5\u7528\u7b2c\u4e8c\u5217，\u4e0d\u4f7f\u7528\u7d22\u5f15 <\/p>\n \u901a\u8fc7INVISIBLE\u5173\u952e\u5b57\u4e34\u65f6\u9690\u85cf\u7d22\u5f15，\u6d4b\u8bd5\u5176\u5bf9\u67e5\u8be2\u7684\u5f71\u54cd：<\/p>\n ALTER TABLE users ALTER INDEX idx_email INVISIBLE; — \u9690\u85cf\u7d22\u5f15 MySQL 8 \u652f\u6301\u964d\u5e8f\u7d22\u5f15，\u907f\u514dDESC\u6392\u5e8f\u65f6\u7684\u6587\u4ef6\u6392\u5e8f：<\/p>\n ALTER TABLE orders ADD INDEX idx_order_date_desc (order_date DESC); \u573a\u666f：user_info\u8868\u5305\u542b 30 \u4e2a\u5b57\u6bb5，\u5176\u4e2dresume（\u7b80\u5386）\u548ccertificate（\u8bc1\u4e66）\u5b57\u6bb5\u6781\u5c11\u4f7f\u7528，\u5bfc\u81f4\u5168\u8868\u626b\u63cf\u7f13\u6162\u3002<\/p>\n<\/p>\n \u4f18\u5316\u6b65\u9aa4：<\/p>\n CREATE TABLE user_basic ( \u00a0<\/p>\n \u00a0 \u00a0 2.\u521b\u5efa\u4f4e\u9891\u5b57\u6bb5\u8868user_extend（\u901a\u8fc7\u5916\u952e\u5173\u8054）：<\/p>\n CREATE TABLE user_extend ( \u6548\u679c：\u9ad8\u9891\u67e5\u8be2（\u5982\u767b\u5f55\u3001\u7528\u6237\u5217\u8868）\u4ec5\u8bbf\u95eeuser_basic，\u626b\u63cf\u884c\u6570\u51cf\u5c11 60%\u3002<\/p>\n<\/p>\n \u573a\u666f：\u9891\u7e41\u7edf\u8ba1 \u201c\u7528\u6237\u8fd1 30 \u5929\u8ba2\u5355\u91d1\u989d\u201d，\u9700\u5173\u8054users\u3001orders\u3001order_items\u4e09\u5f20\u8868，\u6267\u884c\u65f6\u95f4\u8d85\u8fc7 3 \u79d2\u3002<\/p>\n \u4f18\u5316\u65b9\u6848：\u521b\u5efa\u7edf\u8ba1\u4e2d\u95f4\u8868user_order_stats，\u6bcf\u65e5\u5b9a\u65f6\u540c\u6b65\u6570\u636e：<\/p>\n<\/p>\n CREATE TABLE user_order_stats ( — \u66ff\u4ee3\u590d\u6742\u5173\u8054\u67e5\u8be2（\u539f\u97003\u8868JOIN） \u6548\u679c：\u67e5\u8be2\u65f6\u95f4\u4ece 3 \u79d2\u964d\u81f3 0.1 \u79d2，\u6d88\u9664\u591a\u8868 JOIN \u5f00\u9500\u3002<\/p>\n<\/p>\n \u95ee\u9898：LIMIT 100000, 20\u9700\u626b\u63cf 100020 \u884c，\u4e22\u5f03\u524d 100000 \u884c，\u6548\u7387\u6781\u4f4e\u3002<\/p>\n<\/p>\n MySQL 8 \u4f18\u5316\u65b9\u6848：<\/p>\n<\/p>\n — \u5148\u83b7\u53d6\u4e3b\u952e（\u51cf\u5c11\u626b\u63cf\u5b57\u6bb5） — \u57fa\u4e8e\u4e0a\u6b21\u6700\u5927ID\u5206\u9875，\u907f\u514d\u504f\u79fb\u91cf\u7d2f\u79ef \u00a0<\/p>\n \u539f\u590d\u6742\u67e5\u8be2（3 \u8868 JOIN，\u6267\u884c\u65f6\u95f4 2 \u79d2）：<\/p>\n SELECT u.username, o.order_id, p.product_name \u4f18\u5316\u6b65\u9aa4：<\/p>\n SELECT id, username FROM users WHERE country = 'China'; — \u5355\u8868\u67e5\u8be2，0.05\u79d2 <\/p>\n \u00a0 \u00a0 \u00a02. \u67e5\u8be2\u8ba2\u5355 ID \u4e0e\u4ea7\u54c1 ID：<\/p>\n SELECT order_id, product_id FROM orders WHERE user_id IN (1,2,3) AND status = 'paid'; — 0.1\u79d2 <\/p>\n \u00a0 \u00a0 3.\u67e5\u8be2\u4ea7\u54c1\u540d\u79f0：<\/p>\n SELECT product_id, product_name FROM products WHERE product_id IN (101, 102); — 0.03\u79d2 <\/p>\n \u603b\u8017\u65f6\u964d\u81f3 0.18 \u79d2，\u76f8\u6bd4\u539f\u67e5\u8be2\u63d0\u5347 10 \u500d\u4ee5\u4e0a\u3002<\/p>\n<\/p>\n — \u539f\u5d4c\u5957\u5b50\u67e5\u8be2（\u53ef\u8bfb\u6027\u5dee，\u6613\u89e6\u53d1\u5168\u8868\u626b\u63cf） — \u6539\u7528CTE（\u6e05\u6670\u4e14\u4fbf\u4e8e\u4f18\u5316\u5668\u5206\u6790） \u901a\u8fc7AUTO_RECALCULATE\u53c2\u6570\u81ea\u52a8\u66f4\u65b0\u7d22\u5f15\u7edf\u8ba1\u4fe1\u606f，\u907f\u514d\u56e0\u7edf\u8ba1\u4fe1\u606f\u9648\u65e7\u5bfc\u81f4\u7684\u6267\u884c\u8ba1\u5212\u504f\u5dee：<\/p>\n ALTER TABLE users ALTER INDEX idx_email AUTO_RECALCULATE DEFAULT ON; <\/p>\n \u5b9a\u671f\u5ba1\u8ba1\u6d41\u7a0b：<\/p>\n \u7d22\u5f15\u8bbe\u8ba1\u4e09\u539f\u5219：<\/p>\n \u76d1\u63a7\u4e0e\u9884\u8b66：<\/p>\n \u603b\u7ed3<\/p>\n MySQL 8 \u7684\u6162\u67e5\u8be2\u4f18\u5316\u9700\u8981\u7ed3\u5408\u7248\u672c\u7279\u6027\u4e0e\u7ecf\u5178\u4f18\u5316\u7406\u8bba，\u4ece \u201c\u65e5\u5fd7\u5206\u6790\u2192\u6267\u884c\u8ba1\u5212\u8bca\u65ad\u2192\u7d22\u5f15\u4f18\u5316\u2192\u7ed3\u6784\u8c03\u6574\u201d \u56db\u4e2a\u5c42\u9762\u9010\u5c42\u6df1\u5165\u3002\u901a\u8fc7EXPLAIN ANALYZE\u7684\u7cbe\u51c6\u5206\u6790\u3001\u8986\u76d6\u7d22\u5f15\u7684\u5408\u7406\u8bbe\u8ba1\u3001\u4ee5\u53ca\u5927\u8868\u62c6\u5206\u7b49\u67b6\u6784\u4f18\u5316，\u591a\u6570\u6162\u67e5\u8be2\u95ee\u9898\u53ef\u8fce\u5203\u800c\u89e3\u3002\u8bb0\u4f4f：\u4f18\u5316\u7684\u6838\u5fc3\u662f\u8ba9\u6570\u636e\u5e93 \u201c\u5c11\u5e72\u6d3b\u201d\u2014\u2014 \u51cf\u5c11\u626b\u63cf\u884c\u6570\u3001\u907f\u514d\u4e34\u65f6\u8868\u4e0e\u6587\u4ef6\u6392\u5e8f\u3001\u5229\u7528\u7d22\u5f15\u8986\u76d6\u67e5\u8be2\u3002\u6301\u7eed\u8fed\u4ee3\u4f18\u5316\u7b56\u7565\u5e76\u7ed3\u5408\u76d1\u63a7\u4f53\u7cfb，\u624d\u80fd\u786e\u4fdd MySQL \u5728\u9ad8\u5e76\u53d1\u573a\u666f\u4e0b\u7a33\u5b9a\u9ad8\u6548\u8fd0\u884c\u3002<\/p><\/p>\n","protected":false},"excerpt":{"rendered":" \u6587\u7ae0\u6d4f\u89c8\u9605\u8bfb581\u6b21\uff0c\u70b9\u8d5e4\u6b21\uff0c\u6536\u85cf15\u6b21\u3002\u601d\u7ef4\u5bfc\u56fe: \u5728 MySQL \u6570\u636e\u5e93\u7ba1\u7406\u4e2d\uff0c\u6162\u67e5\u8be2\u662f\u5f71\u54cd\u7cfb\u7edf\u6027\u80fd\u7684\u5e38\u89c1\u75db\u70b9\u3002\u968f\u7740 MySQL 8 \u7248\u672c\u7684\u666e\u53ca\uff0c\u5176\u65b0\u589e\u7279\u6027\uff08\u5982 CTE\u3001\u9690\u85cf\u7d22\u5f15\u3001JSON \u683c\u5f0f\u6267\u884c\u8ba1\u5212\u7b49\uff09\u4e3a\u6162\u67e5\u8be2\u4f18\u5316\u63d0\u4f9b\u4e86\u66f4\u5f3a\u5927\u7684\u5de5\u5177\u3002\u672c\u6587\u7ed3\u5408 MySQL 8 \u7684\u7279\u6027\uff0c\u901a\u8fc7\u4ee3\u7801\u793a\u4f8b\u8be6\u89e3\u6162\u67e5\u8be2\u7684\u5b9a\u4f4d\u3001\u5206\u6790\u4e0e\u4f18\u5316\u5168\u6d41\u7a0b\uff0c\u4ece\u800c\u63d0\u9ad8\u6570\u636e\u5e93\u7684\u6027\u80fd\u3002\u4fee\u6539 MySQL 8 \u7684\u914d\u7f6e\u6587\u4ef6my.cnf\uff08Linux\uff09\u6216my.ini\uff08Windows\uff09\uff0c\u6dfb\u52a0\u4ee5\u4e0b\u6838\u5fc3\u914d\u7f6e\uff1a\u4fee\u6539\u540e\u9700\u91cd\u542f MySQL \u670d\u52a1\u4f7f\u914d\u7f6e\u751f\u6548\u3002\u914d\u7f6e\u5b8c\u6bd5\u4e4b\u540e\uff0c\u901a\u8fc7\u4ee5\u4e0b\u6307\u4ee4\u91cd\u65b0\u542f\u52a8MySQL\u670d<\/p>\n","protected":false},"author":2,"featured_media":35826,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[114,3060,3061,100,2174],"topic":[],"class_list":["post-35828","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-server","tag-mysql","tag-mysql8","tag-sql","tag-100","tag-2174"],"yoast_head":"\n1.\u00a0type（\u8fde\u63a5\u7c7b\u578b，\u6548\u7387\u4f18\u5148\u7ea7）<\/h4>\n
\n
\u7c7b\u578b\u8bf4\u660e\u4f18\u5316\u76ee\u6807<\/tr>\n \n\n system<\/td>\n \u5355\u8868\u4e14\u4ec5\u6709\u4e00\u884c\u6570\u636e（\u7279\u6b8aconst）<\/td>\n \u7406\u60f3\u72b6\u6001，\u65e0\u9700\u4f18\u5316<\/td>\n<\/tr>\n \n const<\/td>\n \u4e3b\u952e \/ \u552f\u4e00\u7d22\u5f15\u7cbe\u51c6\u5339\u914d<\/td>\n \u4f18\u5148\u901a\u8fc7\u4e3b\u952e \/ \u552f\u4e00\u7d22\u5f15\u67e5\u8be2<\/td>\n<\/tr>\n \n range<\/td>\n \u7d22\u5f15\u8303\u56f4\u67e5\u8be2（\u5982BETWEEN\/IN）<\/td>\n \u5408\u7406\u4f7f\u7528\u7d22\u5f15，\u65e0\u9700\u5f3a\u5236\u4f18\u5316<\/td>\n<\/tr>\n \n ALL<\/td>\n \u5168\u8868\u626b\u63cf（\u5fc5\u987b\u4f18\u5316！）<\/td>\n \u6dfb\u52a0\u7d22\u5f15\u6216\u6539\u5199\u67e5\u8be2\u6761\u4ef6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 2. key\u4e0ekey_len<\/h4>\n
\n
3. Extra（\u5173\u952e\u4f18\u5316\u4fe1\u53f7）<\/h4>\n
\n
\n "query_block": {
\n "table": {
\n "table_name": "users",
\n "access_type": "ALL", — \u5168\u8868\u626b\u63cf
\n "rows_examined_per_scan": 100000 — \u626b\u63cf10\u4e07\u884c
\n }
\n }
\n} <\/p>\n
\nEXPLAIN ANALYZE SELECT name, phone FROM users WHERE email = 'user@example.com'; <\/p>\n
\n "query_block": {
\n "table": {
\n "table_name": "users",
\n "access_type": "ref", — \u7d22\u5f15\u5f15\u7528
\n "rows_examined_per_scan": 1,
\n "using_index": true — \u8986\u76d6\u7d22\u5f15，\u65e0\u9700\u56de\u8868
\n }
\n }
\n} <\/p>\n\u4e09\u3001\u7d22\u5f15\u4f18\u5316：MySQL 8 \u7684\u65b0\u7279\u6027\u4e0e\u7ecf\u5178\u89c4\u5219<\/h3>\n
<\/h5>\n
1. \u907f\u5f00\u7d22\u5f15\u5931\u6548\u7684 5 \u5927 \u201c\u9677\u9631\u201d<\/h4>\n<\/p>\n
\u9677\u9631 2：OR\u6761\u4ef6\u6df7\u5408\u65e0\u7d22\u5f15\u5217<\/h5>\n
\n
\n
\nUNION ALL
\nSELECT * FROM orders WHERE address LIKE '%\u4e0a\u6d77'; <\/p>\n\u9677\u9631 3：\u591a\u5217\u7d22\u5f15\u672a\u9075\u5faa \u201c\u6700\u5de6\u5339\u914d\u201d<\/h5>\n
\nSELECT * FROM sales WHERE order_date > '2025-01-01';
\n— \u7528\u524d\u4e24\u5217（\u7cbe\u51c6\u5339\u914d）
\nSELECT * FROM sales WHERE order_date = '2025-01-01' AND product_id = 101; <\/p>\n2. MySQL 8 \u4e13\u5c5e\u4f18\u5316\u6280\u5de7<\/h4>\n
<\/h5>\n
（1）\u9690\u85cf\u7d22\u5f15（\u6d4b\u8bd5\u7d22\u5f15\u6709\u6548\u6027）<\/h5>\n
\nEXPLAIN SELECT * FROM users WHERE email = 'test@example.com'; — \u89c2\u5bdf\u662f\u5426\u8d70\u5168\u8868\u626b\u63cf
\nALTER TABLE users ALTER INDEX idx_email VISIBLE; — \u6062\u590d\u7d22\u5f15 <\/p>\n（2）\u964d\u5e8f\u7d22\u5f15（\u4f18\u5316\u5012\u5e8f\u6392\u5e8f）<\/h5>\n
\n— \u4f18\u5316\u540e\u65e0\u9700\u6587\u4ef6\u6392\u5e8f
\nSELECT * FROM orders ORDER BY order_date DESC LIMIT 10; <\/p>\n\u56db\u3001\u6570\u636e\u5e93\u7ed3\u6784\u4f18\u5316：\u4ece\u8bbe\u8ba1\u5c42\u51cf\u5c11\u67e5\u8be2\u538b\u529b<\/h3>\n
1. \u62c6\u5206\u5927\u8868：\u5206\u79bb\u9ad8\u9891\u4e0e\u4f4e\u9891\u5b57\u6bb5<\/h4>\n
\n id BIGINT PRIMARY KEY AUTO_INCREMENT,
\n username VARCHAR(50) NOT NULL,
\n email VARCHAR(100) UNIQUE,
\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP
\n); <\/p>\n
\n id BIGINT PRIMARY KEY,
\n resume TEXT,
\n certificate JSON, — MySQL 8\u652f\u6301\u539f\u751fJSON\u7c7b\u578b
\n FOREIGN KEY (id) REFERENCES user_basic(id)
\n); <\/p>\n2. \u4e2d\u95f4\u8868\u4f18\u5316\u590d\u6742\u5173\u8054\u67e5\u8be2<\/h4>\n
\n user_id BIGINT PRIMARY KEY,
\n total_amount DECIMAL(10, 2),
\n order_count INT,
\n last_update DATE,
\n INDEX idx_last_update (last_update) — \u6309\u65f6\u95f4\u67e5\u8be2\u7d22\u5f15
\n);<\/p>\n
\nSELECT total_amount FROM user_order_stats WHERE user_id = 123 AND last_update = CURDATE(); <\/p>\n\u4e94\u3001\u5b9e\u6218\u6280\u5de7：\u5904\u7406\u9ad8\u9891\u6162\u67e5\u8be2\u573a\u666f<\/h3>\n
1. \u6df1\u5ea6\u5206\u9875\u4f18\u5316（LIMIT offset, size\u6027\u80fd\u95ee\u9898）<\/h4>\n
\n
\nSELECT id FROM orders ORDER BY create_time LIMIT 100000, 20;
\n— \u518d\u901a\u8fc7\u4e3b\u952e\u6279\u91cf\u67e5\u8be2（IN\u64cd\u4f5c\u6bd4\u5b50\u67e5\u8be2\u9ad8\u6548）
\nSELECT * FROM orders WHERE id IN (100001, 100002, …, 100020); <\/p>\n\n
\nSELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20; <\/p>\n2. \u5206\u89e3\u5173\u8054\u67e5\u8be2：\u5e94\u7528\u5c42\u7ec4\u88c5\u7ed3\u679c（\u9002\u5408\u9ad8\u5e76\u53d1）<\/h4>\n<\/p>\n
\nFROM users u
\nJOIN orders o ON u.id = o.user_id
\nJOIN products p ON o.product_id = p.id
\nWHERE u.country = 'China' AND o.status = 'paid'; <\/p>\n\u516d\u3001MySQL 8 \u65b0\u589e\u7279\u6027\u52a9\u529b\u4f18\u5316<\/h3>\n
1. CTE（\u516c\u5171\u8868\u8868\u8fbe\u5f0f）\u7b80\u5316\u590d\u6742\u5b50\u67e5\u8be2<\/h4>\n<\/p>\n
\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE score > 80);<\/p>\n
\nWITH high_score_users AS (
\n SELECT id FROM users WHERE score > 80
\n)
\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM high_score_users); <\/p>\n<\/p>\n2. \u7d22\u5f15\u7edf\u8ba1\u4fe1\u606f\u81ea\u52a8\u66f4\u65b0（8.0.17+）<\/h4>\n
\u4e03\u3001\u6700\u4f73\u5b9e\u8df5：\u5efa\u7acb\u6162\u67e5\u8be2\u4f18\u5316\u95ed\u73af<\/h3>\n
\n
\n
\n