\u6ca1\u6709\u7d22\u5f15\u7684\u6570\u636e\u5e93\u7cfb\u7edf\u5c31\u662f\u4e09\u8e66\u5b50<\/strong><\/div>\n
\u90a3\u4e48\uff0c\u4ec0\u4e48\u662f\u7d22\u5f15\u5462\uff1f<\/p>\n
\u7d22\u5f15\uff08Index\uff09\u662f\u5e2e\u52a9MySQL\u9ad8\u6548\u83b7\u53d6\u6570\u636e\u7684\u6570\u636e\u7ed3\u6784\u3002\u53ef\u4ee5\u5f97\u5230\u7d22\u5f15\u7684\u672c\u8d28\uff1a\u7d22\u5f15\u662f\u6570\u636e\u7ed3\u6784<\/strong>\u3002<\/p>\n
\u53ef\u4ee5\u7406\u89e3\u4e3a\u201c\u6392\u597d\u5e8f\u7684\u5feb\u901f\u67e5\u627e\u6570\u636e\u7ed3\u6784\u201d\uff0c\u6bd4\u5982\u67e5\u627e\u6811~<\/p>\n
\u5728\u6570\u636e\u4e4b\u5916\uff0c\u6570\u636e\u5e93\u7cfb\u7edf\u8fd8\u7ef4\u62a4\u7740\u6ee1\u8db3\u7279\u5b9a\u67e5\u627e\u7b97\u6cd5\u7684\u6570\u636e\u7ed3\u6784\uff0c\u8fd9\u4e9b\u6570\u636e\u7ed3\u6784\u4ee5\u67d0\u79cd\u65b9\u5f0f\u5f15\u7528\uff08\u6307\u5411\uff09\u6570\u636e,\u8fd9\u6837\u5c31\u53ef\u4ee5\u5728\u8fd9\u4e9b\u6570\u636e\u7ed3\u6784\u4e0a\u5b9e\u73b0\u9ad8\u7ea7\u67e5\u627e\u7b97\u6cd5<\/strong>\uff0c\u8fd9\u79cd\u6570\u636e\u7ed3\u6784\u5c31\u662f\u7d22\u5f15\u3002<\/p>\n
\u4f18\u52bf<\/span><\/div>\n
\u7c7b\u4f3c\u5927\u5b66\u56fe\u4e66\u9986\u5efa\u4e66\u76ee\u7d22\u5f15\uff0c\u63d0\u9ad8\u6570\u636e\u68c0\u7d22\u6548\u7387\uff0c\u964d\u4f4e\u6570\u636e\u5e93\u7684IO\u6210\u672c\u3002<\/p>\n
\u901a\u8fc7\u7d22\u5f15\u5bf9\u6570\u636e\u8fdb\u884c\u6392\u5e8f\uff0c\u964d\u4f4e\u6570\u636e\u6392\u5e8f\u7684\u6210\u672c\uff0c\u964d\u4f4e\u4e86CPU\u7684\u6d88\u8017\u3002<\/p>\n
\u52a3\u52bf<\/span><\/div>\n
\u5b9e\u9645\u4e0a\u7d22\u5f15\u4e5f\u662f\u4e00\u5f20\u8868\uff0c\u8be5\u8868\u4fdd\u5b58\u4e86\u4e3b\u952e\u4e0e\u7d22\u5f15\u5b57\u6bb5\uff0c\u5e76\u6307\u5411\u5b9e\u4f53\u8868\u7684\u8bb0\u5f55\uff0c\u6240\u4ee5\u7d22\u5f15\u5217\u4e5f\u662f\u8981\u5360\u7a7a\u95f4\u7684\u3002<\/p>\n
\u867d\u7136\u7d22\u5f15\u5927\u5927\u63d0\u9ad8\u4e86\u67e5\u8be2\u901f\u5ea6\uff0c\u540c\u65f6\u786e\u4f1a\u964d\u4f4e\u66f4\u65b0\u8868\u7684\u901f\u5ea6<\/strong>\uff0c\u5982\u5bf9\u8868\u8fdb\u884cINSERT\u3001UPDATE\u3001DELETE\u3002<\/p>\n
\u56e0\u4e3a\u66f4\u65b0\u8868\u65f6\uff0cMySQL\u4e0d\u4ec5\u8981\u4fdd\u5b58\u6570\u636e\uff0c\u8fd8\u8981\u4fdd\u5b58\u4e00\u4e0b\u7d22\u5f15\u6587\u4ef6\u6bcf\u6b21\u66f4\u65b0\u6dfb\u52a0\u4e86\u7d22\u5f15\u5217\u7684\u5b57\u6bb5\u3002<\/p>\n
\u90fd\u4f1a\u8c03\u6574\u56e0\u4e3a\u66f4\u65b0\u6240\u5e26\u6765\u7684\u952e\u503c\u53d8\u5316\u540e\u7684\u7d22\u5f15\u4fe1\u606f\u3002<\/p>\n
\u7d22\u5f15\u7684\u5206\u7c7b<\/span><\/div>\n
\u5355\u503c\uff08\u5217\uff09\u7d22\u5f15\uff1a\u5373\u4e00\u4e2a\u7d22\u5f15\u53ea\u5305\u542b\u5355\u4e2a\u5217\uff0c\u4e00\u4e2a\u8868\u53ef\u4ee5\u6709\u591a\u4e2a\u5355\u5217\u7d22\u5f15\u3002<\/p>\n
\u552f\u4e00\u7d22\u5f15\uff1a\u7d22\u5f15\u5217\u7684\u503c\u5fc5\u987b\u552f\u4e00\uff0c\u4f46\u5141\u8bb8\u6709\u7a7a\u503c\u3002 -> \u4e3b\u952e\u662f\u7279\u6b8a\u7684\u552f\u4e00\u7d22\u5f15\uff0c\u56e0\u4e3a\u5176\u4e0d\u5141\u8bb8\u6709\u7a7a\u503c\u3002<\/p>\n
\u590d\u5408\uff08\u7ec4\u5408\uff09\u7d22\u5f15\uff1a\u5373\u4e00\u4e2a\u7d22\u5f15\u5305\u542b\u591a\u4e2a\u5217\u3002<\/p>\n
\u5168\u6587\u7d22\u5f15\uff1aFULLTEXTl\u7c7b\u578b\u7d22\u5f15,\u53ef\u4ee5\u5728CHAR\uff0cVARCHAR\uff0c\u6216\u8005TEXT\u7c7b\u578b\u7684\u5217\u4e0a\u521b\u5efa\uff0c\u4ec5MyISAM\u652f\u6301\u3002<\/p>\n
\u7a7a\u95f4\u7d22\u5f15\uff1a\u5bf9\u7a7a\u95f4\u6570\u636e\u5e93\u7684\u652f\u6301\uff0cGIS\u7cfb\u7edf\u4ec0\u4e48\u7684\u3002\u3002\u3002\u3002\u54ce\u5440\uff0c\u4e0d\u770b\u8fd9\u91cc\u4e86\uff0c\u770b\u8d77\u6765\u6bd4\u8f83\u540a\u3002<\/p>\n
\u7d22\u5f15\u7ed3\u6784<\/span><\/div>\n
\u5404\u5b58\u50a8\u5f15\u64ce\u652f\u6301\u7684\u7d22\u5f15\u7ed3\u6784\u5982\u4e0b\u8868\uff08\u6458\u81eaMariaDB\u7684KB\uff09\uff1a<\/p>\n
$\"\"$ <\/p>\n
\u5176\u4e2dBTREE\u662f\u9ed8\u8ba4\u7684\u7d22\u5f15\u7ed3\u6784\uff0c\u800c\u5bf9\u4e8eMEMORY\u5b58\u50a8\u5f15\u64ce\uff0cHASH\u5219\u662f\u9ed8\u8ba4\u7684\u3002<\/p>\n
B-TREE\uff1a\u652f\u6301 >, >=, =, >=, < \u3001BETWEEN\u4ee5\u53caLIKE\u4e00\u4e2a\u5e38\u91cf\uff0c\u6bd4\u5982\"MariaDB%\"\u53ef\u4ee5\u652f\u6301B-Tree\u7d22\u5f15\uff0c\u800c\"%Maria\"\u5219\u4e0d\u652f\u6301\u3002\n\nHash\uff1a\u4ec5\u652f\u6301 =\u6216<=>\u64cd\u4f5c\u7b26\uff0c<\/p>\n
R-tree\uff1a\u7a7a\u95f4\u7d22\u5f15\u4e0a\u7684\u6570\u636e\u7ed3\u6784\uff0c\u4e0d\u770b\u4e86.....\u592a\u96be\u3002<\/p>\n
\u54ea\u4e9b\u60c5\u51b5\u9700\u8981\u521b\u5efa\u7d22\u5f15<\/span><\/div>\n
\n
\u4e3b\u952e\u81ea\u52a8\u5efa\u7acb\u552f\u4e00\u7d22\u5f15<\/li>\n
\u9891\u7e41\u4f5c\u4e3a\u67e5\u8be2\u6761\u4ef6\u7684\u5b57\u6bb5\u5e94\u8be5\u521b\u5efa\u7d22\u5f15<\/li>\n
\u67e5\u8be2\u4e2d\u4e0e\u5176\u4ed6\u8868\u5173\u8054\u7684\u5b57\u6bb5\uff0c\u5916\u952e\u5173\u7cfb\u5efa\u7acb\u7d22\u5f15<\/li>\n
\u9891\u7e41\u66f4\u65b0\u7684\u5b57\u6bb5\u4e0d\u9002\u5408\u5efa\u7acb\u7d22\u5f15\uff0c\u56e0\u4e3a\u6bcf\u6b21\u66f4\u65b0\u4e0d\u5355\u5355\u662f\u66f4\u65b0\u4e86\u8bb0\u5f55\u8fd8\u4f1a\u66f4\u65b0\u7d22\u5f15<\/li>\n
WHERE\u6761\u4ef6\u91cc\u7528\u4e0d\u5230\u7684\u5b57\u6bb5\u4e0d\u521b\u5efa\u7d22\u5f15<\/li>\n
\u5355\u952e\/\u7ec4\u5408\u7d22\u5f15\u7684\u9009\u62e9\u95ee\u9898\uff0cwho?(\u5728\u9ad8\u5e76\u53d1\u4e0b\u503e\u5411\u521b\u5efa\u7ec4\u5408\u7d22\u5f15)<\/li>\n
\u67e5\u8be2\u4e2d\u6392\u5e8f\u7684\u5b57\u6bb5\uff0c\u6392\u5e8f\u7684\u5b57\u6bb5\u82e5\u901a\u8fc7\u7d22\u5f15\u53bb\u8bbf\u95ee\u5c06\u5927\u5927\u63d0\u9ad8\u6392\u5e8f\u901f\u5ea6<\/li>\n
\u67e5\u8be2\u4e2d\u7edf\u8ba1\u6216\u8005\u5206\u7ec4\u5b57\u6bb5<\/li>\n<\/ol>\n
\u54ea\u4e9b\u60c5\u51b5\u4e0d\u8981\u521b\u5efa\u7d22\u5f15<\/span><\/div>\n
\n
\u8868\u8bb0\u5f55\u592a\u5c11<\/li>\n
\u7ecf\u5e38\u589e\u5220\u6539\u7684\u8868<\/li>\n<\/ol>\n
\u63d0\u9ad8\u4e86\u67e5\u8be2\u901f\u5ea6\uff0c\u540c\u65f6\u5374\u4f1a\u964d\u4f4e\u66f4\u65b0\u8868\u7684\u901f\u5ea6\uff0c\u5982\u5bf9\u8868\u8fdb\u884cINSERT\u3001UPDATE\u3001\u548cDELETE\u3002<\/p>\n
\u56e0\u4e3a\u66f4\u65b0\u8868\u65f6\uff0cMySQL\u4e0d\u4ec5\u8981\u4fdd\u5b58\u6570\u636e\uff0c\u8fd8\u8981\u4fdd\u5b58\u4e00\u4e0b\u7d22\u5f15\u6587\u4ef6\u3002<\/p>\n
\u6570\u636e\u91cd\u590d\u4e14\u5206\u5e03\u5e73\u5747\u7684\u8868\u5b57\u6bb5\uff0c\u56e0\u6b64\u5e94\u8be5\u53ea\u4e3a\u6700\u7ecf\u5e38\u67e5\u8be2\u548c\u6700\u7ecf\u5e38\u6392\u5e8f\u7684\u6570\u636e\u5efa\u7acb\u7d22\u5f15\u3002<\/p>\n
\u6ce8\u610f\uff0c\u5982\u679c\u67d0\u4e2a\u6570\u636e\u5217\u5305\u542b\u8bb8\u591a\u91cd\u590d\u7684\u5185\u5bb9\uff0c\u4e3a\u5b83\u5efa\u7acb\u7d22\u5f15\u5c31\u6ca1\u6709\u592a\u5927\u7684\u5b9e\u9645\u6548\u679c\u3002<\/p>\n
\u6700\u5de6\u524d\u7f00\u539f\u5219<\/span><\/div>\n
\u8fd9\u91cc\u627e\u5230\u4e00\u7bc7\u597d\u6587\u7ae0\uff0c\u6458\u81ea\u77e5\u4e4e\uff1amysql\u7d22\u5f15\u6700\u5de6\u5339\u914d\u539f\u5219\u7684\u7406\u89e3?<\/a> \u4f5c\u8005\uff1a\u6c88\u6770<\/p>\n
\u8868\u7ed3\u6784\u5982\u4e0b\uff1a\u6709\u4e09\u4e2a\u5b57\u6bb5\uff0c\u5206\u522b\u662fid,name,cid <\/p>\n
\r\nCREATE TABLE `student` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `name` varchar(255) DEFAULT NULL,\r\n `cid` int(11) DEFAULT NULL,\r\n PRIMARY KEY (`id`),\r\n KEY `name_cid_INX` (`name`,`cid`),\r\n) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8<\/pre>\n
\u7d22\u5f15\u65b9\u9762\uff1aid\u662f\u4e3b\u952e\uff0c(name,cid)\u662f\u4e00\u4e2a\u591a\u5217\u7d22\u5f15\u3002<\/p>\n
\u4e0b\u9762\u662f\u4f60\u6709\u7591\u95ee\u7684\u4e24\u4e2a\u67e5\u8be2\uff1a<\/p>\n
EXPLAIN SELECT * FROM student WHERE cid=1;<\/pre>\n
$\"\"$ <\/p>\n
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='\u5c0f\u7ea2';<\/pre>\n
$\"\"$ <\/p>\n
\u4f60\u7684\u7591\u95ee\u662f\uff1asql\u67e5\u8be2\u7528\u5230\u7d22\u5f15\u7684\u6761\u4ef6\u662f\u5fc5\u987b\u8981\u9075\u5b88\u6700\u5de6\u524d\u7f00\u539f\u5219\uff0c\u4e3a\u4ec0\u4e48\u4e0a\u9762\u4e24\u4e2a\u67e5\u8be2\u8fd8\u80fd\u7528\u5230\u7d22\u5f15\uff1f<\/strong><\/p>\n
\u8bb2\u4e0a\u9762\u95ee\u9898\u4e4b\u524d\uff0c\u6211\u5148\u8865\u5145\u4e00\u4e9b\u77e5\u8bc6\uff0c\u56e0\u4e3a\u6211\u89c9\u5f97\u4f60\u5bf9\u7d22\u5f15\u7406\u89e3\u662f\u72ed\u9698\u7684\uff1a
\n\u4e0a\u8ff0\u4f60\u7684\u4e24\u4e2a\u67e5\u8be2\u7684explain\u7ed3\u679c\u4e2d\u663e\u793a\u7528\u5230\u7d22\u5f15\u7684\u60c5\u51b5\u7c7b\u578b\u662f\u4e0d\u4e00\u6837\u7684\u3002,\u53ef\u89c2\u5bdfexplain\u7ed3\u679c\u4e2d\u7684type\u5b57\u6bb5\u3002<\/p>\n
\u4f60\u7684\u67e5\u8be2\u4e2d\u5206\u522b\u662f\uff1a
\n1. type: index
\n2. type: ref<\/p>\n
\u89e3\u91ca\uff1a
\nindex\uff1a\u8fd9\u79cd\u7c7b\u578b\u8868\u793a\u662fmysql\u4f1a\u5bf9\u6574\u4e2a\u8be5\u7d22\u5f15\u8fdb\u884c\u626b\u63cf\u3002\u8981\u60f3\u7528\u5230\u8fd9\u79cd\u7c7b\u578b\u7684\u7d22\u5f15\uff0c\u5bf9\u8fd9\u4e2a\u7d22\u5f15\u5e76\u65e0\u7279\u522b\u8981\u6c42\uff0c\u53ea\u8981\u662f\u7d22\u5f15\uff0c\u6216\u8005\u67d0\u4e2a\u590d\u5408\u7d22\u5f15\u7684\u4e00\u90e8\u5206\uff0cmysql\u90fd\u53ef\u80fd\u4f1a\u91c7\u7528index\u7c7b\u578b\u7684\u65b9\u5f0f\u626b\u63cf\u3002\u4f46\u662f\u5462\uff0c\u7f3a\u70b9\u662f\u6548\u7387\u4e0d\u9ad8\uff0cmysql\u4f1a\u4ece\u7d22\u5f15\u4e2d\u7684\u7b2c\u4e00\u4e2a\u6570\u636e\u4e00\u4e2a\u4e2a\u7684\u67e5\u627e\u5230\u6700\u540e\u4e00\u4e2a\u6570\u636e\uff0c\u76f4\u5230\u627e\u5230\u7b26\u5408\u5224\u65ad\u6761\u4ef6\u7684\u67d0\u4e2a\u7d22\u5f15\u3002<\/p>\n
\u6240\u4ee5\uff1a\u5bf9\u4e8e\u4f60\u7684\u7b2c\u4e00\u6761\u8bed\u53e5\uff1a<\/p>\n
EXPLAIN SELECT * FROM student WHERE cid=1;<\/pre>\n
\u5224\u65ad\u6761\u4ef6\u662fcid=1,\u800ccid\u662f(name,cid)\u590d\u5408\u7d22\u5f15\u7684\u4e00\u90e8\u5206\uff0c\u6ca1\u6709\u95ee\u9898\uff0c\u53ef\u4ee5\u8fdb\u884cindex\u7c7b\u578b\u7684\u7d22\u5f15\u626b\u63cf\u65b9\u5f0f\u3002explain\u663e\u793a\u7ed3\u679c\u4f7f\u7528\u5230\u4e86\u7d22\u5f15\uff0c\u662findex\u7c7b\u578b\u7684\u65b9\u5f0f\u3002<\/p>\n
ref\uff1a\u8fd9\u79cd\u7c7b\u578b\u8868\u793amysql\u4f1a\u6839\u636e\u7279\u5b9a\u7684\u7b97\u6cd5\u5feb\u901f\u67e5\u627e\u5230\u67d0\u4e2a\u7b26\u5408\u6761\u4ef6\u7684\u7d22\u5f15\uff0c\u800c\u4e0d\u662f\u4f1a\u5bf9\u7d22\u5f15\u4e2d\u6bcf\u4e00\u4e2a\u6570\u636e\u90fd\u8fdb\u884c\u4e00 \u4e00\u7684\u626b\u63cf\u5224\u65ad\uff0c\u4e5f\u5c31\u662f\u6240\u8c13\u4f60\u5e73\u5e38\u7406\u89e3\u7684\u4f7f\u7528\u7d22\u5f15\u67e5\u8be2\u4f1a\u66f4\u5feb\u7684\u53d6\u51fa\u6570\u636e\u3002\u800c\u8981\u60f3\u5b9e\u73b0\u8fd9\u79cd\u67e5\u627e\uff0c\u7d22\u5f15\u5374\u662f\u6709\u8981\u6c42\u7684\uff0c\u8981\u5b9e\u73b0\u8fd9\u79cd\u80fd\u5feb\u901f\u67e5\u627e\u7684\u7b97\u6cd5\uff0c\u7d22\u5f15\u5c31\u8981\u6ee1\u8db3\u7279\u5b9a\u7684\u6570\u636e\u7ed3\u6784\u3002\u7b80\u5355\u8bf4\uff0c\u4e5f\u5c31\u662f\u7d22\u5f15\u5b57\u6bb5\u7684\u6570\u636e\u5fc5\u987b\u662f\u6709\u5e8f\u7684\uff0c\u624d\u80fd\u5b9e\u73b0\u8fd9\u79cd\u7c7b\u578b\u7684\u67e5\u627e\uff0c\u624d\u80fd\u5229\u7528\u5230\u7d22\u5f15\u3002<\/p>\n
\u6709\u4e9b\u4e86\u89e3\u7684\u4eba\u53ef\u80fd\u4f1a\u95ee\uff0c\u7d22\u5f15\u4e0d\u90fd\u662f\u4e00\u4e2a\u6709\u5e8f\u6392\u5217\u7684\u6570\u636e\u7ed3\u6784\u4e48\u3002\u4e0d\u8fc7\u7b54\u6848\u8bf4\u7684\u8fd8\u4e0d\u591f\u5b8c\u5584\uff0c\u90a3\u53ea\u662f\u9488\u5bf9\u5355\u4e2a\u7d22\u5f15\uff0c\u800c\u590d\u5408\u7d22\u5f15\u7684\u60c5\u51b5\u6709\u4e9b\u540c\u5b66\u53ef\u80fd\u5c31\u4e0d\u592a\u4e86\u89e3\u4e86\u3002<\/p>\n
\u4e0b\u9762\u5c31\u8bf4\u4e0b\u590d\u5408\u7d22\u5f15\uff1a
\n\u4ee5\u8be5\u8868\u7684(name,cid)\u590d\u5408\u7d22\u5f15\u4e3a\u4f8b,\u5b83\u5185\u90e8\u7ed3\u6784\u7b80\u5355\u8bf4\u5c31\u662f\u4e0b\u9762\u8fd9\u6837\u6392\u5217\u7684\uff1a<\/p>\n
$\"\"$ <\/p>\n
mysql\u521b\u5efa\u590d\u5408\u7d22\u5f15\u7684\u89c4\u5219\u662f\u9996\u5148\u4f1a\u5bf9\u590d\u5408\u7d22\u5f15\u7684\u6700\u5de6\u8fb9\u7684\uff0c\u4e5f\u5c31\u662f\u7b2c\u4e00\u4e2aname\u5b57\u6bb5\u7684\u6570\u636e\u8fdb\u884c\u6392\u5e8f\uff0c\u5728\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u7684\u6392\u5e8f\u57fa\u7840\u4e0a\uff0c\u7136\u540e\u518d\u5bf9\u540e\u9762\u7b2c\u4e8c\u4e2a\u7684cid\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\u3002\u5176\u5b9e\u5c31\u76f8\u5f53\u4e8e\u5b9e\u73b0\u4e86\u7c7b\u4f3c order by name cid\u8fd9\u6837\u4e00\u79cd\u6392\u5e8f\u89c4\u5219\u3002<\/p>\n
\u6240\u4ee5\uff1a\u7b2c\u4e00\u4e2aname\u5b57\u6bb5\u662f\u7edd\u5bf9\u6709\u5e8f\u7684\uff0c\u800c\u7b2c\u4e8c\u5b57\u6bb5\u5c31\u662f\u65e0\u5e8f\u7684\u4e86\u3002\u6240\u4ee5\u901a\u5e38\u60c5\u51b5\u4e0b\uff0c\u76f4\u63a5\u4f7f\u7528\u7b2c\u4e8c\u4e2acid\u5b57\u6bb5\u8fdb\u884c\u6761\u4ef6\u5224\u65ad\u662f\u7528\u4e0d\u5230\u7d22\u5f15\u7684\uff0c\u5f53\u7136\uff0c\u53ef\u80fd\u4f1a\u51fa\u73b0\u4e0a\u9762\u7684\u4f7f\u7528index\u7c7b\u578b\u7684\u7d22\u5f15\u3002\u8fd9\u5c31\u662f\u6240\u8c13\u7684mysql\u4e3a\u4ec0\u4e48\u8981\u5f3a\u8c03\u6700\u5de6\u524d\u7f00\u539f\u5219\u7684\u539f\u56e0\u3002<\/p>\n
\u90a3\u4e48\u4ec0\u4e48\u65f6\u5019\u624d\u80fd\u7528\u5230\u5462?\u5f53\u7136\u662fcid\u5b57\u6bb5\u7684\u7d22\u5f15\u6570\u636e\u4e5f\u662f\u6709\u5e8f\u7684\u60c5\u51b5\u4e0b\u624d\u80fd\u4f7f\u7528\u54af\uff0c\u4ec0\u4e48\u65f6\u5019\u624d\u662f\u6709\u5e8f\u7684\u5462\uff1f\u89c2\u5bdf\u53ef\u77e5\uff0c\u5f53\u7136\u662f\u5728name\u5b57\u6bb5\u662f\u7b49\u503c\u5339\u914d\u7684\u60c5\u51b5\u4e0b\uff0ccid\u624d\u662f\u6709\u5e8f\u7684\u3002\u53d1\u73b0\u6ca1\u6709\uff0c\u89c2\u5bdf\u4e24\u4e2aname\u540d\u5b57\u4e3a c \u7684cid\u5b57\u6bb5\u662f\u4e0d\u662f\u6709\u5e8f\u7684\u5462\u3002\u4ece\u4e0a\u5f80\u4e0b\u5206\u522b\u662f4 5\u3002
\n\u8fd9\u4e5f\u5c31\u662fmysql\u7d22\u5f15\u89c4\u5219\u4e2d\u8981\u6c42\u590d\u5408\u7d22\u5f15\u8981\u60f3\u4f7f\u7528\u7b2c\u4e8c\u4e2a\u7d22\u5f15\uff0c\u5fc5\u987b\u5148\u4f7f\u7528\u7b2c\u4e00\u4e2a\u7d22\u5f15\u7684\u539f\u56e0\u3002\uff08\u800c\u4e14\u7b2c\u4e00\u4e2a\u7d22\u5f15\u5fc5\u987b\u662f\u7b49\u503c\u5339\u914d\uff09\u3002<\/strong><\/p>\n
\u6240\u4ee5\u5bf9\u4e8e\u4f60\u7684\u8fd9\u6761sql\u67e5\u8be2\uff1a<\/p>\n
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='\u5c0f\u7ea2';<\/pre>\n
\u6ca1\u6709\u9519\uff0c\u800c\u4e14\u590d\u5408\u7d22\u5f15\u4e2d\u7684\u4e24\u4e2a\u7d22\u5f15\u5b57\u6bb5\u90fd\u80fd\u5f88\u597d\u7684\u5229\u7528\u5230\u4e86\uff01\u56e0\u4e3a\u8bed\u53e5\u4e2d\u6700\u5de6\u9762\u7684name\u5b57\u6bb5\u8fdb\u884c\u4e86\u7b49\u503c\u5339\u914d\uff0c\u6240\u4ee5cid\u662f\u6709\u5e8f\u7684\uff0c\u4e5f\u53ef\u4ee5\u5229\u7528\u5230\u7d22\u5f15\u4e86\u3002<\/p>\n
\u4f60\u53ef\u80fd\u4f1a\u95ee\uff1a\u6211\u5efa\u7684\u7d22\u5f15\u662f(name,cid)\u3002\u800c\u6211\u67e5\u8be2\u7684\u8bed\u53e5\u662fcid=1 AND name='\u5c0f\u7ea2'; \u6211\u662f\u5148\u67e5\u8be2cid\uff0c\u518d\u67e5\u8be2name\u7684\uff0c\u4e0d\u662f\u5148\u4ece\u6700\u5de6\u9762\u67e5\u7684\u5440\uff1f<\/p>\n
\u597d\u5427\uff0c\u6211\u518d\u89e3\u91ca\u4e00\u4e0b\u8fd9\u4e2a\u95ee\u9898\uff1a\u9996\u5148\u53ef\u4ee5\u80af\u5b9a\u7684\u662f\u628a\u6761\u4ef6\u5224\u65ad\u53cd\u8fc7\u6765\u53d8\u6210\u8fd9\u6837 name='\u5c0f\u7ea2' and cid=1; \u6700\u540e\u6240\u67e5\u8be2\u7684\u7ed3\u679c\u662f\u4e00\u6837\u7684\u3002\u90a3\u4e48\u95ee\u9898\u4ea7\u751f\u4e86\uff1f\u65e2\u7136\u7ed3\u679c\u662f\u4e00\u6837\u7684\uff0c\u5230\u5e95\u4ee5\u4f55\u79cd\u987a\u5e8f\u7684\u67e5\u8be2\u65b9\u5f0f\u6700\u597d\u5462\uff1f\u6240\u4ee5\uff0c\u800c\u6b64\u65f6\u90a3\u5c31\u662f\u6211\u4eec\u7684mysql\u67e5\u8be2\u4f18\u5316\u5668\u8be5\u767b\u573a\u4e86\uff0cmysql\u67e5\u8be2\u4f18\u5316\u5668\u4f1a\u5224\u65ad\u7ea0\u6b63\u8fd9\u6761sql\u8bed\u53e5\u8be5\u4ee5\u4ec0\u4e48\u6837\u7684\u987a\u5e8f\u6267\u884c\u6548\u7387\u6700\u9ad8\uff0c\u6700\u540e\u624d\u751f\u6210\u771f\u6b63\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>\n
\u6240\u4ee5\uff0c\u5f53\u7136\u662f\u6211\u4eec\u80fd\u5c3d\u91cf\u7684\u5229\u7528\u5230\u7d22\u5f15\u65f6\u7684\u67e5\u8be2\u987a\u5e8f\u6548\u7387\u6700\u9ad8\u54af\uff0c\u6240\u4ee5mysql\u67e5\u8be2\u4f18\u5316\u5668\u4f1a\u6700\u7ec8\u4ee5\u8fd9\u79cd\u987a\u5e8f\u8fdb\u884c\u67e5\u8be2\u6267\u884c\u3002<\/p>\n
\u8d5e\u4e00\u4e2a~\u4e0d\u8bf7\u81ea\u8f6c\uff0c\u611f\u8c22\u539f\u4f5c\u8005\u6c88\u6770\uff0c\u5982\u6709\u4fb5\u6743\u8bf7\u544a\u77e5\u3002<\/p>\n
\u521b\u5efa\u7d22\u5f15\u7684\u8bed\u6cd5<\/strong><\/div>\n\u521b\u5efa\u8868\u7684\u65f6\u5019\u521b\u5efa\u7d22\u5f15<\/span><\/div>\n
\r\nCREATE TABLE tbl_name(Column_def1,Column_def2,Columndef3,.....index_def);\r\n\r\nindex_def:\r\n {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...\r\n | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...\r\n | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...\r\n | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...\r\n | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition\r\n\r\n\r\n<\/pre>\n
\u4f8b\u5b50\uff0c\u521b\u5efa\u4e00\u5f20\u8868\uff0c\u4e09\u4e2a\u5b57\u6bb5\uff1aid\uff0cname\uff0cgid \u4e3aname\u521b\u5efa\u7d22\u5f15\uff1a<\/p>\n
\r\nMariaDB [mydb]> CREATE TABLE test(\r\n -> id int(10) PRIMARY KEY AUTO_INCREMENT,\r\n -> name VARCHAR(10) NOT NULL,\r\n -> gid int(3),\r\n -> INDEX name_idx (name(5))\r\n -> );\r\n<\/pre>\n
\u5176\u4e2d\u9700\u8981\u8bf4\u660e\u7684\u662f\uff0cname(5)\u5c31\u662f\u4e3aname\u5217\u521b\u5efa\u7d22\u5f15\uff0c\u4e14\u4ec5\u53d6\u524d5\u4e2a\u5b57\u7b26\u8fdb\u884c\u7d22\u5f15\u3002<\/p>\n
\u66f4\u591a\u521b\u5efa\u8868\u65f6\u7684SQL\u8bed\u6cd5\u53ef\u53c2\u8003\uff1ahttps:\/\/mariadb.com\/kb\/en\/library\/create-table\/#indexes<\/p>\n
\u4e3a\u5b58\u5728\u7684\u8868\u521b\u5efa\u7d22\u5f15<\/span><\/div>\n
CREATE INDEX\u8bed\u6cd5\u5982\u4e0b\uff1a<\/p>\n
\r\nCREATE [OR REPLACE] [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX \r\n [IF NOT EXISTS] index_name\r\n [index_type]\r\n ON tbl_name (index_col_name,...)\r\n [WAIT n | NOWAIT]\r\n [index_option]\r\n [algorithm_option | lock_option] ...\r\n\r\nindex_col_name:\r\n col_name [(length)] [ASC | DESC]\r\n\r\nindex_type:\r\n USING {BTREE | HASH | RTREE}\r\n\r\nindex_option:\r\n KEY_BLOCK_SIZE [=] value\r\n | index_type\r\n | WITH PARSER parser_name\r\n | COMMENT 'string'\r\n\r\nalgorithm_option:\r\n ALGORITHM [=] {DEFAULT|INPLACE|COPY}\r\n\r\nlock_option:\r\n LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}\r\n<\/pre>\n
\u5148\u67e5\u770b\u4e0b\u4e0a\u9762\u521b\u5efa\u7684\u7d22\u5f15\uff1a<\/p>\n
\r\nMariaDB [mydb]> SHOW INDEX FROM test\\G;\r\n*************************** 1. row *************************** \/\/\u4e3b\u952e\u4f1a\u521b\u5efa\u4e00\u4e2a\u7d22\u5f15\r\n Table: test\r\n Non_unique: 0\r\n Key_name: PRIMARY\r\n Seq_in_index: 1\r\n Column_name: id\r\n Collation: A\r\n Cardinality: 0\r\n Sub_part: NULL\r\n Packed: NULL\r\n Null: \r\n Index_type: BTREE\r\n Comment: \r\nIndex_comment: \r\n*************************** 2. row *************************** \/\/\u8fd9\u4e2a\u662f\u6211\u4eec\u81ea\u5df1\u521b\u5efa\u7684\u7d22\u5f15\r\n Table: test\r\n Non_unique: 1\r\n Key_name: name_idx\r\n Seq_in_index: 1\r\n Column_name: name\r\n Collation: A\r\n Cardinality: 0\r\n Sub_part: 5\r\n Packed: NULL\r\n Null: \r\n Index_type: BTREE\r\n Comment: \r\nIndex_comment: \r\n2 rows in set (0.00 sec)\r\n<\/pre>\n
\u73b0\u5728\uff0c\u6211\u4eec\u628a\u4e4b\u524d\u521b\u5efa\u7684\u7d22\u5f15\u5220\u6389\uff0c\u7136\u540e\u521b\u5efa\u4e00\u4e2a\u591a\u5217\u7d22\u5f15(name,gid)<\/p>\n
\r\nDROP INDEX name_idx ON test;\/\/\u5220\u9664\u7d22\u5f15\r\nCREATE INDEX name_gid_idx ON test (name(5),gid DESC); \/\/\u521b\u5efa\u4e86\u591a\u5217\u7d22\u5f15\uff0c\u4e14name\u53d6\u524d5\uff0c\u800cgid\u5219\u5012\u53d9\u6392\u5217\r\nCREATE OR REPLACE INDEX name_gid_idx ON test(name(5),gid); \/\/\u4fee\u6539\u4e86\u4e0a\u9762\u521b\u5efa\u7684\u8fd9\u4e2a\u7d22\u5f15\r\n\r\n<\/pre>\n
\u552f\u4e00\u7d22\u5f15<\/span><\/div>\n
\u521b\u5efa\u552f\u4e00\u7ea6\u675f\u65f6\u4f1a\u81ea\u52a8\u521b\u5efa\u552f\u4e00\u7d22\u5f15\uff0c\u4f46\u521b\u5efa\u552f\u4e00\u7d22\u5f15\u65f6\u5219\u4e0d\u4f1a\u521b\u5efa\u552f\u4e00\u7ea6\u675f\uff0c\u4e14\u552f\u4e00\u7d22\u5f15\u80fd\u505a\u5230\u548c\u552f\u4e00\u7ea6\u675f\u4e00\u6837\u7684\u6548\u679c\u3002<\/p>\n
\n
\u539f\u6587\u6765\u81ea\uff1ahttps:\/\/www.54371.net\/linux\/mariadb-study-index.html<\/a><\/p>\n
\u672c\u6587\u5730\u5740\uff1a https:\/\/gulass.cn\/mariadb-study-index.html<\/a>\u7f16\u8f91\uff1a\u6e05\u84b8github\uff0c\u5ba1\u6838\u5458\uff1a\u9004\u589e\u5b9d<\/span><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
\u6ca1\u6709\u7d22\u5f15\u7684\u6570\u636e\u5e93\u7cfb\u7edf\u5c31\u662f\u4e09\u8e66\u5b50 \u90a3\u4e48\uff0c\u4ec0\u4e48\u662f\u7d22\u5f15\u5462\uff1f \u7d22\u5f15\uff08Index\uff09\u662f\u5e2e\u52a9MySQL\u9ad8\u6548\u83b7\u53d6\u6570\u636e\u7684\u6570\u636e\u7ed3\u6784 […]<\/p>\n","protected":false},"author":317,"featured_media":100417,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-111863","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/posts\/111863","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/users\/317"}],"replies":[{"embeddable":true,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/comments?post=111863"}],"version-history":[{"count":5,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/posts\/111863\/revisions"}],"predecessor-version":[{"id":112673,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/posts\/111863\/revisions\/112673"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/media\/100417"}],"wp:attachment":[{"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/media?parent=111863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/categories?post=111863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gulass.cn\/wp-json\/wp\/v2\/tags?post=111863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}