{"id":10020864,"date":"2025-06-29T04:02:26","date_gmt":"2025-06-28T20:02:26","guid":{"rendered":"https:\/\/itmax.cn\/?p=10020864"},"modified":"2025-06-29T04:02:26","modified_gmt":"2025-06-28T20:02:26","slug":"%e4%bd%bf%e7%94%a8-laravel-redis-%e4%bc%98%e5%8c%96-mysql-%e5%a4%9a%e5%ad%97%e6%ae%b5-distinct-%e6%9f%a5%e8%af%a2%e6%80%a7%e8%83%bd","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10020864","title":{"rendered":"\u4f7f\u7528 Laravel + Redis \u4f18\u5316 MySQL \u591a\u5b57\u6bb5 DISTINCT \u67e5\u8be2\u6027\u80fd"},"content":{"rendered":"

\u5728\u9ad8\u5e76\u53d1\u4e1a\u52a1\u573a\u666f\u4e2d\uff0c\u9891\u7e41\u6267\u884c MySQL \u7684 SELECT DISTINCT \u67e5\u8be2\u53ef\u80fd\u4f1a\u6210\u4e3a\u6027\u80fd\u74f6\u9888\u3002\u672c\u6587\u5c06\u4ecb\u7ecd\u4e00\u4e2a\u5b9e\u9645\u6848\u4f8b\uff0c\u5e76\u901a\u8fc7 Laravel + Redis \u7f13\u5b58\u673a\u5236\u5b9e\u73b0\u67e5\u8be2\u4f18\u5316\u3002<\/p>\n

\ud83d\udccc \u80cc\u666f\u9700\u6c42<\/h2>\n

\u6211\u4eec\u6709\u4e00\u4e2a MySQL \u8868\uff0c\u6570\u636e\u91cf\u7ea6\u4e3a 50 \u4e07\u6761\uff0c\u5b57\u6bb5\u7ed3\u6784\u5982\u4e0b\uff1a<\/p>\n

\u8868\u5b57\u6bb5\uff1aa, b, c, d, e, f, g<\/code><\/pre>\n
\u5b57\u6bb5\u7279\u5f81\uff1a<\/strong><\/p>\n
    \n
  • \u6bcf\u4e2a\u5b57\u6bb5\u90fd\u662f\u679a\u4e3e\u7c7b\u578b\uff0c\u679a\u4e3e\u503c\u6570\u91cf\u4ece\u51e0\u5341\u5230\u4e24\u767e\u4e0d\u7b49<\/li>\n
  • \u5b57\u6bb5 a<\/code> \u5df2\u5efa\u7acb\u7d22\u5f15<\/li>\n
  • \u6570\u636e\u91cf\uff1a500,000 \u6761\u8bb0\u5f55<\/li>\n<\/ul>\n
    \u67e5\u8be2\u9700\u6c42\uff1a<\/strong><\/p>\n
      \n
    • \u6bcf\u6b21\u67e5\u8be2\u65f6\uff0c\u5b57\u6bb5 a<\/code> \u4e00\u5b9a\u6709\u503c\uff0c\u5176\u4ed6\u5b57\u6bb5\u4e0d\u4e00\u5b9a\u6709\u503c<\/li>\n
    • \u9700\u8981\u83b7\u53d6\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u4e2d\uff0c\u5b57\u6bb5 b~g<\/code> \u7684 DISTINCT \u503c<\/li>\n
    • \u5f53\u524d\u505a\u6cd5\u662f\u5bf9\u6bcf\u4e2a\u5b57\u6bb5\u6267\u884c\u4e00\u6b21 SELECT DISTINCT \u67e5\u8be2\uff0c\u603b\u5171\u9700\u8981 7 \u6b21\u67e5\u8be2<\/li>\n
    • \u5e76\u53d1\u91cf\u5f88\u5927\uff0c\u6570\u636e\u5e93\u538b\u529b\u8f83\u9ad8<\/li>\n<\/ul>\n
      \u5f53\u524dSQL\u793a\u4f8b\uff1a<\/strong><\/p>\n
      SELECT DISTINCT b FROM table WHERE a = 'xxx' AND \u5176\u4ed6\u6761\u4ef6;\nSELECT DISTINCT c FROM table WHERE a = 'xxx' AND \u5176\u4ed6\u6761\u4ef6;\nSELECT DISTINCT d FROM table WHERE a = 'xxx' AND \u5176\u4ed6\u6761\u4ef6;\n-- ... \u7ee7\u7eed\u6267\u884c\u5176\u4ed6\u5b57\u6bb5<\/code><\/pre>\n
      \u274c \u5f53\u524d\u5b58\u5728\u7684\u95ee\u9898<\/h2>\n
        \n
      1. \u67e5\u8be2\u6b21\u6570\u8fc7\u591a<\/strong>\uff1aMySQL \u4e0d\u652f\u6301\u4e00\u6b21\u6027\u5bf9\u591a\u4e2a\u5b57\u6bb5\u5206\u522b\u505a DISTINCT<\/li>\n
      2. \u6570\u636e\u5e93\u538b\u529b\u5927<\/strong>\uff1a\u6bcf\u6b21\u67e5\u8be2\u90fd\u8981\u6267\u884c 7 \u6761 SQL<\/li>\n
      3. \u54cd\u5e94\u901f\u5ea6\u6162<\/strong>\uff1a\u591a\u6b21\u6570\u636e\u5e93\u67e5\u8be2\u5f71\u54cd\u7528\u6237\u4f53\u9a8c<\/li>\n
      4. \u9ad8\u5e76\u53d1\u74f6\u9888<\/strong>\uff1a\u5927\u91cf\u5e76\u53d1\u8bf7\u6c42\u5bfc\u81f4\u6570\u636e\u5e93\u6027\u80fd\u4e0b\u964d<\/li>\n<\/ol>\n
        \u2705 \u89e3\u51b3\u65b9\u6848\uff1aLaravel + Redis \u7f13\u5b58\u4f18\u5316<\/h2>\n
        \u6211\u4eec\u91c7\u7528 Laravel + Redis \u7684\u65b9\u5f0f\uff0c\u5c06\u5b57\u6bb5 a<\/code> \u5bf9\u5e94\u7684 b~g<\/code> \u7684\u679a\u4e3e\u503c\u9884\u5148\u7f13\u5b58\u5230 Redis \u4e2d\uff0c\u67e5\u8be2\u65f6\u4f18\u5148\u4ece Redis \u83b7\u53d6\uff0c\u6781\u5927\u63d0\u5347\u6027\u80fd\u3002<\/p>\n
        \ud83e\uddf1 Redis \u7f13\u5b58\u7ed3\u6784\u8bbe\u8ba1<\/h3>\n
        \u7f13\u5b58Key\u683c\u5f0f\uff1a<\/strong><\/p>\n
        distinct_values:{a\u503c}<\/code><\/pre>\n
        \u7f13\u5b58Value\u683c\u5f0f\uff08JSON\uff09\uff1a<\/strong><\/p>\n
        {\n  "b": ["value1", "value2", "value3"],\n  "c": ["value1", "value2"],\n  "d": ["value1", "value2", "value3"],\n  "e": ["value1"],\n  "f": ["value1", "value2"],\n  "g": ["value1", "value2", "value3"]\n}<\/code><\/pre>\n
        \ud83d\udee0 Laravel \u5b9e\u73b0\u6b65\u9aa4<\/h2>\n
        1\ufe0f\u20e3 \u521b\u5efa\u7f13\u5b58\u6784\u5efa\u547d\u4ee4<\/h3>\n
        \u521b\u5efa Artisan \u547d\u4ee4\uff1a<\/p>\n
        php artisan make:command CacheDistinctValues<\/code><\/pre>\n
        \u7f16\u8f91\u6587\u4ef6 app\/Console\/Commands\/CacheDistinctValues.php<\/code>\uff1a<\/p>\n
        <?php\n\nnamespace App\/Console\/Commands;\n\nuse Illuminate\/Console\/Command;\nuse Illuminate\/Support\/Facades\/DB;\nuse Illuminate\/Support\/Facades\/Redis;\n\nclass CacheDistinctValues extends Command\n{\n    protected $signature = 'cache:distinct-values';\n    protected $description = 'Cache distinct values of b~g grouped by a';\n\n    public function handle()\n    {\n        $this->info('\u5f00\u59cb\u6784\u5efa\u7f13\u5b58...');\n        \n        \/\/ \u83b7\u53d6\u6240\u6709 a \u503c\u5bf9\u5e94\u7684 distinct \u6570\u636e\n        $rows = DB::table('your_table_name')\n            ->select([\n                'a',\n                DB::raw('GROUP_CONCAT(DISTINCT b) AS b_values'),\n                DB::raw('GROUP_CONCAT(DISTINCT c) AS c_values'),\n                DB::raw('GROUP_CONCAT(DISTINCT d) AS d_values'),\n                DB::raw('GROUP_CONCAT(DISTINCT e) AS e_values'),\n                DB::raw('GROUP_CONCAT(DISTINCT f) AS f_values'),\n                DB::raw('GROUP_CONCAT(DISTINCT g) AS g_values')\n            ])\n            ->whereNotNull('a')\n            ->groupBy('a')\n            ->get();\n\n        $count = 0;\n        foreach ($rows as $row) {\n            $key = "distinct_values:{$row->a}";\n            $value = [\n                'b' => $this->parseValues($row->b_values),\n                'c' => $this->parseValues($row->c_values),\n                'd' => $this->parseValues($row->d_values),\n                'e' => $this->parseValues($row->e_values),\n                'f' => $this->parseValues($row->f_values),\n                'g' => $this->parseValues($row->g_values),\n            ];\n            \n            \/\/ \u8bbe\u7f6e\u7f13\u5b58\uff0c\u8fc7\u671f\u65f6\u95f410\u5206\u949f\n            Redis::setex($key, 600, json_encode($value));\n            $count++;\n        }\n\n        $this->info("\u7f13\u5b58\u6784\u5efa\u5b8c\u6210\uff0c\u5171\u5904\u7406 {$count} \u4e2a a \u503c");\n    }\n\n    private function parseValues($values)\n    {\n        if (empty($values)) {\n            return [];\n        }\n        \n        return array_filter(array_unique(explode(',', $values)), function($value) {\n            return !empty(trim($value));\n        });\n    }\n}<\/code><\/pre>\n
        2\ufe0f\u20e3 \u8bbe\u7f6e\u5b9a\u65f6\u4efb\u52a1<\/h3>\n
        \u7f16\u8f91 app\/Console\/Kernel.php<\/code>\uff1a<\/p>\n
        <?php\n\nnamespace App\/Console;\n\nuse Illuminate\/Console\/Scheduling\/Schedule;\nuse Illuminate\/Foundation\/Console\/Kernel as ConsoleKernel;\n\nclass Kernel extends ConsoleKernel\n{\n    protected function schedule(Schedule $schedule)\n    {\n        \/\/ \u6bcf5\u5206\u949f\u66f4\u65b0\u4e00\u6b21\u7f13\u5b58\n        $schedule->command('cache:distinct-values')\n                 ->everyFiveMinutes()\n                 ->withoutOverlapping();\n    }\n\n    protected $commands = [\n        Commands\/CacheDistinctValues::class,\n    ];\n}<\/code><\/pre>\n
        \u8bbe\u7f6e\u670d\u52a1\u5668\u5b9a\u65f6\u4efb\u52a1\uff08crontab\uff09\uff1a<\/p>\n
        * * * * * cd \/your\/project\/path && php artisan schedule:run >> \/dev\/null 2>&1<\/code><\/pre>\n
        3\ufe0f\u20e3 \u521b\u5efa\u67e5\u8be2\u63a7\u5236\u5668<\/h3>\n
        \u521b\u5efa\u63a7\u5236\u5668\uff1a<\/p>\n
        php artisan make:controller DistinctValuesController<\/code><\/pre>\n
        \u7f16\u8f91 app\/Http\/Controllers\/DistinctValuesController.php<\/code>\uff1a<\/p>\n
        <?php\n\nnamespace App\/Http\/Controllers;\n\nuse Illuminate\/Http\/Request;\nuse Illuminate\/Support\/Facades\/Redis;\nuse Illuminate\/Support\/Facades\/DB;\n\nclass DistinctValuesController extends Controller\n{\n    \/**\n     * \u83b7\u53d6\u6307\u5b9a a \u503c\u7684\u6240\u6709\u5b57\u6bb5 distinct \u503c\n     *\/\n    public function getDistinctValues($a, Request $request)\n    {\n        $key = "distinct_values:{$a}";\n        $cached = Redis::get($key);\n\n        if ($cached) {\n            $data = json_decode($cached, true);\n            \n            \/\/ \u6839\u636e\u5176\u4ed6\u6761\u4ef6\u8fc7\u6ee4\uff08\u5982\u679c\u9700\u8981\uff09\n            if ($request->has('filters')) {\n                $data = $this->applyFilters($data, $request->get('filters'));\n            }\n            \n            return response()->json([\n                'success' => true,\n                'data' => $data,\n                'source' => 'cache'\n            ]);\n        }\n\n        \/\/ \u7f13\u5b58\u672a\u547d\u4e2d\uff0c\u964d\u7ea7\u5230\u6570\u636e\u5e93\u67e5\u8be2\n        return $this->fallbackToDatabase($a, $request);\n    }\n\n    \/**\n     * \u7f13\u5b58\u672a\u547d\u4e2d\u65f6\u7684\u964d\u7ea7\u65b9\u6848\n     *\/\n    private function fallbackToDatabase($a, Request $request)\n    {\n        try {\n            $query = DB::table('your_table_name')->where('a', $a);\n            \n            \/\/ \u5e94\u7528\u5176\u4ed6\u8fc7\u6ee4\u6761\u4ef6\n            if ($request->has('filters')) {\n                foreach ($request->get('filters') as $field => $value) {\n                    $query->where($field, $value);\n                }\n            }\n\n            $result = [\n                'b' => $query->distinct()->pluck('b')->filter()->values()->toArray(),\n                'c' => $query->distinct()->pluck('c')->filter()->values()->toArray(),\n                'd' => $query->distinct()->pluck('d')->filter()->values()->toArray(),\n                'e' => $query->distinct()->pluck('e')->filter()->values()->toArray(),\n                'f' => $query->distinct()->pluck('f')->filter()->values()->toArray(),\n                'g' => $query->distinct()->pluck('g')->filter()->values()->toArray(),\n            ];\n\n            return response()->json([\n                'success' => true,\n                'data' => $result,\n                'source' => 'database'\n            ]);\n        } catch (\/Exception $e) {\n            return response()->json([\n                'success' => false,\n                'message' => 'Query failed',\n                'error' => $e->getMessage()\n            ], 500);\n        }\n    }\n\n    \/**\n     * \u5e94\u7528\u989d\u5916\u7684\u8fc7\u6ee4\u6761\u4ef6\n     *\/\n    private function applyFilters($data, $filters)\n    {\n        \/\/ \u8fd9\u91cc\u53ef\u4ee5\u6839\u636e\u4e1a\u52a1\u9700\u6c42\u5b9e\u73b0\u989d\u5916\u7684\u8fc7\u6ee4\u903b\u8f91\n        return $data;\n    }\n}<\/code><\/pre>\n
        4\ufe0f\u20e3 \u8bbe\u7f6e\u8def\u7531<\/h3>\n
        \u7f16\u8f91 routes\/api.php<\/code>\uff1a<\/p>\n
        <?php\n\nuse App\/Http\/Controllers\/DistinctValuesController;\nuse Illuminate\/Support\/Facades\/Route;\n\nRoute::prefix('api\/v1')->group(function () {\n    Route::get('\/distinct\/{a}', [DistinctValuesController::class, 'getDistinctValues']);\n});<\/code><\/pre>\n
        \ud83d\udd27 \u9ad8\u7ea7\u4f18\u5316<\/h2>\n
        1. \u7f13\u5b58\u9884\u70ed<\/h3>\n
        \/\/ \u5728 CacheDistinctValues \u547d\u4ee4\u4e2d\u6dfb\u52a0\u9884\u70ed\u903b\u8f91\npublic function handle()\n{\n    \/\/ \u9884\u70ed\u70ed\u70b9\u6570\u636e\n    $hotAValues = $this->getHotAValues();\n    \n    foreach ($hotAValues as $aValue) {\n        $this->buildCacheForA($aValue);\n    }\n}\n\nprivate function getHotAValues()\n{\n    \/\/ \u83b7\u53d6\u6700\u5e38\u67e5\u8be2\u7684 a \u503c\n    return DB::table('query_logs')\n        ->select('a', DB::raw('COUNT(*) as query_count'))\n        ->groupBy('a')\n        ->orderBy('query_count', 'desc')\n        ->limit(100)\n        ->pluck('a');\n}<\/code><\/pre>\n
        2. \u7f13\u5b58\u5931\u6548\u7b56\u7565<\/h3>\n
        \/\/ \u6570\u636e\u66f4\u65b0\u65f6\u6e05\u7406\u76f8\u5173\u7f13\u5b58\nclass YourModel extends Model\n{\n    protected static function booted()\n    {\n        static::saved(function ($model) {\n            $key = "distinct_values:{$model->a}";\n            Redis::del($key);\n        });\n    }\n}<\/code><\/pre>\n
        3. Redis \u96c6\u7fa4\u652f\u6301<\/h3>\n
        \/\/ config\/database.php\n'redis' => [\n    'client' => env('REDIS_CLIENT', 'phpredis'),\n    'options' => [\n        'cluster' => env('REDIS_CLUSTER', 'redis'),\n        'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),\n    ],\n    'clusters' => [\n        'default' => [\n            ['host' => '127.0.0.1', 'port' => 7000],\n            ['host' => '127.0.0.1', 'port' => 7001],\n            ['host' => '127.0.0.1', 'port' => 7002],\n        ],\n    ],\n],<\/code><\/pre>\n
        \ud83d\ude80 \u6027\u80fd\u5bf9\u6bd4<\/h2>\n\n\n\n\n\n\n
        \u65b9\u6848<\/th>\n\u67e5\u8be2\u6b21\u6570<\/th>\n\u54cd\u5e94\u65f6\u95f4<\/th>\nQPS<\/th>\n\u6570\u636e\u5e93\u538b\u529b<\/th>\n<\/tr>\n<\/thead>\n
        \u539f\u65b9\u6848<\/td>\n7\u6b21<\/td>\n50-100ms<\/td>\n~1,000<\/td>\n\u9ad8<\/td>\n<\/tr>\n
        Redis\u7f13\u5b58<\/strong><\/td>\n1\u6b21<\/strong><\/td>\n1-5ms<\/strong><\/td>\n50,000+<\/strong><\/td>\n\u6781\u4f4e<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
        \ud83d\udcca \u76d1\u63a7\u4e0e\u8c03\u8bd5<\/h2>\n
        \u6dfb\u52a0\u6027\u80fd\u76d1\u63a7<\/h3>\n
        use Illuminate\/Support\/Facades\/Log;\n\nclass DistinctValuesController extends Controller\n{\n    public function getDistinctValues($a, Request $request)\n    {\n        $startTime = microtime(true);\n        \n        \/\/ ... \u67e5\u8be2\u903b\u8f91\n        \n        $endTime = microtime(true);\n        $executionTime = ($endTime - $startTime) * 1000; \/\/ \u8f6c\u6362\u4e3a\u6beb\u79d2\n        \n        Log::info('Distinct query performance', [\n            'a_value' => $a,\n            'execution_time_ms' => $executionTime,\n            'source' => $cached ? 'cache' : 'database'\n        ]);\n        \n        return response()->json($result);\n    }\n}<\/code><\/pre>\n
        \u2705 \u603b\u7ed3<\/h2>\n
        \u901a\u8fc7 Laravel + Redis \u7684\u7ec4\u5408\u65b9\u6848\uff0c\u89e3\u51b3\u4e86\u9ad8\u5e76\u53d1\u573a\u666f\u4e0b\u7684\u591a\u5b57\u6bb5 DISTINCT \u67e5\u8be2\u6027\u80fd\u95ee\u9898\uff1a<\/p>\n
        \u6838\u5fc3\u4f18\u52bf\uff1a<\/strong><\/p>\n