{"id":10020663,"date":"2025-03-03T23:44:29","date_gmt":"2025-03-03T15:44:29","guid":{"rendered":"https:\/\/itmax.cn\/?p=10020663"},"modified":"2025-03-03T23:44:29","modified_gmt":"2025-03-03T15:44:29","slug":"mysql-x-%e5%90%91%e9%87%8f%e6%95%b0%e6%8d%ae%e5%ba%93%ef%bc%9a%e5%a4%a7%e6%a8%a1%e5%9e%8b%e6%97%b6%e4%bb%a3%e7%9a%84%e9%bb%84%e9%87%91%e7%bb%84%e5%90%88%e5%ae%9e%e6%88%98%e6%8c%87%e5%8d%97","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10020663","title":{"rendered":"MySQL \u00d7 \u5411\u91cf\u6570\u636e\u5e93\uff1a\u5927\u6a21\u578b\u65f6\u4ee3\u7684\u9ec4\u91d1\u7ec4\u5408\u5b9e\u6218\u6307\u5357"},"content":{"rendered":"

\u4e00\u3001\u5927\u6a21\u578b\u65f6\u4ee3\u7684\u6570\u636e\u5b58\u50a8\u9769\u547d<\/h2>\n

1.1 \u4f20\u7edf\u67b6\u6784\u7684\u5c40\u9650\u6027<\/h3>\n
-- \u4f20\u7edf\u5546\u54c1\u8868\u7ed3\u6784\nCREATE TABLE products (\n    id INT PRIMARY KEY,\n    title VARCHAR(255),\n    description TEXT,\n    category_id INT\n);\n\n-- \u5178\u578b\u5173\u952e\u8bcd\u641c\u7d22\nSELECT * FROM products \nWHERE title LIKE '%\u667a\u80fd\u624b\u673a%' \n   OR description LIKE '%\u65d7\u8230\u673a\u578b%';<\/code><\/pre>\n
\u75db\u70b9\u5206\u6790\uff1a<\/strong>
\u274c \u8bed\u4e49\u7406\u89e3\u80fd\u529b\u7f3a\u5931
\u274c \u76f8\u4f3c\u5ea6\u8ba1\u7b97\u6548\u7387\u4f4e\u4e0b
\u274c \u591a\u6a21\u6001\u6570\u636e\u5904\u7406\u56f0\u96be<\/p>\n
\u4e8c\u3001\u6df7\u5408\u67b6\u6784\u8bbe\u8ba1\uff1a\u7ed3\u6784\u5316+\u5411\u91cf\u5316\u6570\u636e<\/h2>\n
2.1 \u6280\u672f\u6808\u7ec4\u5408\u65b9\u6848<\/h3>\n\n\n\n\n\n\n\n
\u6570\u636e\u7c7b\u578b<\/th>\n\u5b58\u50a8\u65b9\u6848<\/th>\n\u5178\u578b\u64cd\u4f5c<\/th>\n<\/tr>\n<\/thead>\n
\u7ed3\u6784\u5316\u6570\u636e<\/td>\nMySQL<\/td>\n\u4e8b\u52a1\u5904\u7406\u3001\u7cbe\u786e\u67e5\u8be2<\/td>\n<\/tr>\n
\u5411\u91cf\u6570\u636e<\/td>\nMilvus\/PGVector<\/td>\n\u76f8\u4f3c\u5ea6\u641c\u7d22\u3001\u8bed\u4e49\u7406\u89e3<\/td>\n<\/tr>\n
\u5143\u6570\u636e<\/td>\nRedis<\/td>\n\u5b9e\u65f6\u7f13\u5b58\u3001\u7279\u5f81\u6620\u5c04<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
2.2 \u6df7\u5408\u5b58\u50a8\u67b6\u6784<\/h3>\n
                   +---------------+\n                   |   \u5927\u8bed\u8a00\u6a21\u578b   |\n                   +---------------+\n                          \u2193\n+----------------+    \u7279\u5f81\u5411\u91cf\u5316    +-----------------+\n|   MySQL        | \u2190-----------\u2192 |  \u5411\u91cf\u6570\u636e\u5e93       |\n|   (\u7ed3\u6784\u5316\u6570\u636e)  |  \u5143\u6570\u636e\u6620\u5c04      | (768\u7ef4\u5411\u91cf)       |\n+----------------+               +-----------------+<\/code><\/pre>\n
\u4e09\u3001\u4e09\u5927\u6838\u5fc3\u573a\u666f\u5b9e\u6218<\/h2>\n
\u573a\u666f1\uff1a\u7535\u5546\u8bed\u4e49\u641c\u7d22<\/strong>
\u5b9e\u73b0\u6b65\u9aa4\uff1a<\/strong><\/p>\n
    \n
  1. \u5546\u54c1\u63cf\u8ff0\u5411\u91cf\u5316\u5b58\u50a8<\/li>\n
  2. \u5efa\u7acbMySQL-\u5411\u91cfID\u6620\u5c04<\/li>\n
  3. \u6df7\u5408\u67e5\u8be2\u5b9e\u73b0<\/li>\n<\/ol>\n
    -- MySQL\u6620\u5c04\u8868\nCREATE TABLE product_vectors (\n    product_id INT PRIMARY KEY,\n    vector_id VARCHAR(64),\n    FOREIGN KEY (product_id) REFERENCES products(id)\n);\n\n-- \u8054\u5408\u67e5\u8be2\u793a\u4f8b\nSELECT p.* \nFROM products p\nJOIN (\n    SELECT product_id \n    FROM product_vectors \n    WHERE vector_id IN (\n        -- \u5411\u91cf\u6570\u636e\u5e93\u67e5\u8be2\n        SELECT id FROM vectors \n        ORDER BY cosine_distance(vector, [query_vector]) \n        LIMIT 100\n    )\n) AS v ON p.id = v.product_id\nWHERE p.price < 5000;<\/code><\/pre>\n
    \u573a\u666f2\uff1a\u591a\u6a21\u6001\u63a8\u8350\u7cfb\u7edf<\/strong>
    \u67b6\u6784\u8bbe\u8ba1\uff1a<\/strong><\/p>\n
      \n
    1. \u7528\u6237\u884c\u4e3a\u6570\u636e\u5b58MySQL<\/li>\n
    2. \u5185\u5bb9\u7279\u5f81\u5b58\u5411\u91cf\u6570\u636e\u5e93<\/li>\n
    3. \n
      \u5b9e\u65f6\u6df7\u5408\u63a8\u8350<\/p>\n
      # \u4f2a\u4ee3\u7801\u793a\u4f8b\ndef hybrid_recommend(user_id):\n # \u4eceMySQL\u83b7\u53d6\u7528\u6237\u7279\u5f81\n user_data = mysql.query("SELECT * FROM users WHERE id = %s", user_id)\n \n # \u4ece\u5411\u91cf\u6570\u636e\u5e93\u83b7\u53d6\u76f8\u4f3c\u5185\u5bb9\n item_vectors = vector_db.search(\n     vector=user_data['preference_vector'],\n     top_k=50\n )\n \n # \u7ec4\u5408\u8fc7\u6ee4\u903b\u8f91\n final_results = mysql.query(f"""\n     SELECT * FROM items \n     WHERE id IN ({item_ids})\n       AND category IN ({user_data['preferred_categories']})\n     ORDER BY popularity DESC\n     LIMIT 10\n """)\n return final_results<\/code><\/pre>\n<\/li>\n<\/ol>\n
      \u573a\u666f3\uff1a\u667a\u80fd\u5ba2\u670d\u77e5\u8bc6\u5e93<\/strong>
      \u6570\u636e\u5904\u7406\u6d41\u7a0b\uff1a<\/strong><\/p>\n
      graph TD\n    A[\u539f\u59cb\u77e5\u8bc6\u6587\u6863] --> B(\u6587\u672c\u5411\u91cf\u5316)\n    B --> C{\u5411\u91cf\u6570\u636e\u5e93}\n    C --> D[\u8bed\u4e49\u68c0\u7d22]\n    D --> E[MySQL\u83b7\u53d6\u5173\u8054\u4fe1\u606f]\n    E --> F[\u5927\u6a21\u578b\u751f\u6210\u56de\u7b54]<\/code><\/pre>\n
      \u56db\u3001\u6027\u80fd\u4f18\u5316\u5173\u952e\u7b56\u7565<\/h2>\n
      4.1 \u6df7\u5408\u7d22\u5f15\u8bbe\u8ba1<\/h3>\n
      -- MySQL\u4fa7\u4f18\u5316\nALTER TABLE documents \nADD COLUMN vector_id VARCHAR(36),\nADD INDEX idx_vector_id (vector_id);\n\n-- \u5411\u91cf\u6570\u636e\u5e93\u4fa7\u4f18\u5316\nCREATE INDEX ON vectors \nUSING IVFFLAT (vector) \nWITH (lists = 100);<\/code><\/pre>\n
      4.2 \u7f13\u5b58\u7b56\u7565\u8bbe\u8ba1<\/h3>\n\n\n\n\n\n\n\n
      \u6570\u636e\u7c7b\u578b<\/th>\n\u7f13\u5b58\u65b9\u6848<\/th>\n\u6dd8\u6c70\u7b56\u7565<\/th>\n<\/tr>\n<\/thead>\n
      \u70ed\u70b9\u5411\u91cf<\/td>\nRedis    LRU<\/td>\n\u7b56\u7565<\/td>\n<\/tr>\n
      \u5173\u7cfb\u6620\u5c04<\/td>\nMemcached<\/td>\n\u8d85\u65f6\u81ea\u52a8\u6dd8\u6c70<\/td>\n<\/tr>\n
      \u5b9e\u65f6\u7279\u5f81<\/td>\n\u5185\u5b58\u6570\u636e\u5e93<\/td>\n\u4f1a\u8bdd\u7ea7\u751f\u547d\u5468\u671f<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      4.3 \u67e5\u8be2\u6027\u80fd\u5bf9\u6bd4<\/h3>\n\n\n\n\n\n\n\n
      \u65b9\u6848<\/th>\nQPS<\/th>\n\u5ef6\u8fdf<\/th>\n\u51c6\u786e\u7387<\/th>\n<\/tr>\n<\/thead>\n
      \u7eafMySQL<\/td>\n1200<\/td>\n85ms<\/td>\n62%<\/td>\n<\/tr>\n
      \u7eaf\u5411\u91cf\u6570\u636e\u5e93<\/td>\n800<\/td>\n25ms<\/td>\n89%<\/td>\n<\/tr>\n
      \u6df7\u5408\u67b6\u6784<\/td>\n1500<\/td>\n38ms<\/td>\n93%<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      \u4e94\u3001\u4f01\u4e1a\u7ea7\u89e3\u51b3\u65b9\u6848\u63a8\u8350<\/h2>\n
      5.1 \u5f00\u6e90\u65b9\u6848<\/h3>\n
      # \u90e8\u7f72\u7ec4\u5408\ndocker run -d --name mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0\ndocker run -d --name milvus -p 19530:19530 milvusdb\/milvus<\/code><\/pre>\n
      5.2 \u4e91\u670d\u52a1\u65b9\u6848<\/h2>\n