Laravel Eloquent：15 种数据库索引策略，打造闪电般的查询速度 ​

// database/migrations/2025_07_27_create_orders_table.php
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->decimal('total', 10, 2);
    $table->timestamp('created_at')->useCurrent();

    // 针对常见查询模式的复合索引
    $table->index(['user_id', 'created_at']);
});

// 查询：
Order::where('status', 'completed')
     ->whereBetween('created_at', [now()->subMonth(), now()])
     ->orderBy('total', 'desc')
     ->get();

// 最优复合索引：
// 注意范围列与排序列的放置顺序
$table->index(['status', 'created_at', 'total']);

// database/migrations/2025_07_27_create_partial_indexes.php
public function up()
{
    DB::statement('CREATE INDEX idx_orders_high_value ON orders (total) WHERE total > 1000');
    DB::statement('CREATE INDEX idx_products_featured ON products (category_id) WHERE featured = true');
}

// 查询：
Order::select('id', 'total')
     ->where('user_id', 123)
     ->where('status', 'completed')
     ->get();

// 最优覆盖索引：
$table->index(['user_id', 'status', 'total', 'id']);

// 通过虚拟列实现可索引
$table->unsignedInteger('shipping_country')->virtualAs('JSON_EXTRACT(shipping_info, "$.country")');
$table->index('shipping_country');

// 或直接对 JSON 路径建索引（MySQL 5.7+）
DB::statement('ALTER TABLE users ADD INDEX idx_preferences_theme ((preferences->"$.theme"))');

// 不推荐：
User::whereRaw("JSON_EXTRACT(preferences, '$.theme') = ?", ['dark'])->get();

// 推荐利用可索引语法：
User::where('preferences->theme', 'dark')->get();

// 为 created_at 游标分页建索引
$table->index(['created_at', 'id']);

// 使用方式：
Post::where('published', true)
    ->orderBy('created_at', 'desc')
    ->orderBy('id', 'desc')
    ->cursorPaginate(15);

// 针对传统分页
$table->index(['category_id', 'created_at']);

// 查询：
Product::where('category_id', 5)
        ->orderBy('created_at', 'desc')
        ->offset(50000)
        ->limit(25)
        ->get();

// 外键一律索引
$table->foreignId('category_id')->constrained()->index();

// 例如 Country → Users → Orders
$table->index(['country_id', 'created_at']);
$table->index(['user_id', 'country_id']); // 反向检索

$table->string('commentable_type');
$table->unsignedBigInteger('commentable_id');
$table->index(['commentable_type', 'commentable_id']);

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained()->index();
    $table->foreignId('role_id')->constrained()->index();

    // 复合唯一约束
    $table->unique(['user_id', 'role_id']);

    // 反向检索的额外索引
    $table->index(['role_id', 'user_id']);
});

// app/Console/Commands/AnalyzeIndexes.php
public function handle()
{
    $tables = DB::select('SHOW TABLES');

    foreach ($tables as $table) {
        $tableName = reset((array)$table);
        $this->analyzeTableIndexes($tableName);
    }
}

protected function analyzeTableIndexes(string $table)
{
    $indexes = DB::select("SHOW INDEX FROM {$table}");

    foreach ($indexes as $index) {
        // 低基数索引预警
        if ($index->Seq_in_index == 1 && $index->Cardinality < 100) {
            $this->warn("Low cardinality index on {$table}.{$index->Column_name}");
        }

        // 可扩展：重复索引检测（对比索引定义）
    }
}

// 迁移
$table->text('content');
$table->fullText('content');

// 使用 whereFullText
Post::whereFullText('content', 'Laravel')->get();

// 面向时间范围的查询作用域
public function scopeLastWeek(Builder $query)
{
    return $query->whereBetween('created_at', [now()->subWeek(), now()]);
}
// 需对 created_at 建索引

// 迁移
$table->point('location')->spatialIndex();

// 查询
Location::whereRaw('ST_Distance(location, POINT(?, ?)) < ?', [
    $lat, $lng, 5000 // 5km
])->get();

// 在相关索引中包含 deleted_at
$table->index(['user_id', 'deleted_at']);

// 仅针对未删除数据的部分索引
DB::statement('CREATE INDEX idx_orders_active ON orders (user_id) WHERE deleted_at IS NULL');

// 常见查询模式的复合索引
$table->index(['status', 'deleted_at', 'created_at']);

// 不建议：
Order::withTrashed()->where('user_id', 123)->get();

// 建议：
Order::where('user_id', 123)->get(); // 默认排除已软删
// 需要 user_id, deleted_at 上的索引配合

// app/Providers/AppServiceProvider.php
public function boot()
{
    if ($this->app->isProduction()) {
        DB::listen(function ($query) {
            if (str_contains($query->sql, 'WHERE') && $query->time > 50) {
                Log::warning('Slow query detected', [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time' => $query->time
                ]);

                // 可扩展：对该表触发索引分析
                $this->analyzeQueryTable($query->sql);
            }
        });
    }
}

// 查询：
Product::whereHas('orders', function ($query) {
    $query->where('status', 'completed');
})->get();

// 需要在 orders 表上建立索引：
// [product_id, status] 支撑子查询
// [id] 支撑连接

// 联合查询
$active = User::where('active', true);
$premium = User::where('is_premium', true);

$users = $active->union($premium)->get();

// 需分别具备：
// idx_users_active (active)
// idx_users_premium (is_premium)

// app/Services/IndexAdvisor.php
class IndexAdvisor
{
    protected $queryLog = [];

    public function registerQuery(string $sql, array $bindings, float $time)
    {
        $this->queryLog[] = [
            'sql' => $sql,
            'bindings' => $bindings,
            'time' => $time,
            'normalized' => $this->normalizeQuery($sql)
        ];
    }

    public function generateRecommendations(): array
    {
        $patterns = $this->analyzeQueryPatterns();
        $recommendations = [];

        foreach ($patterns as $pattern) {
            if ($pattern['avg_time'] > 50 && $pattern['count'] > 100) {
                $recommendations[] = $this->recommendIndexForPattern($pattern);
            }
        }

        return $recommendations;
    }

    protected function normalizeQuery(string $sql): string
    {
        // 移除具体值，标准化空白
        return preg_replace('/\'.*?\'|\d+/', '?', $sql);
    }
}

// 结合 Laravel 查询监听
DB::listen(function ($query) {
    app(IndexAdvisor::class)->registerQuery(
        $query->sql,
        $query->bindings,
        $query->time
    );
});