如何在Django中结合Datatables快速处理500万条大数据（无指定依赖）

Let's break down why your page is taking 12 seconds to load and fix it step by step—your main issue right now is that you're trying to send 5 million records all at once, even though you've enabled server-side processing in DataTables (with a typo that was probably making it not work). Here's how to optimize this without using django-datatables-view or DRF:

1. Fix the DataTables Frontend Configuration #

First, correct the camelCase typos in your DataTables settings (JavaScript is case-sensitive) and ensure the AJAX call properly passes server-side parameters:

{% block js%}
<script>
$(document).ready(function () {
    var t0 = performance.now();
    degerleriGetir('test/json');
    var t1 = performance.now();
    console.log("Load time: " + (t1 - t0) + "ms");
});

function degerleriGetir(dataUrl) {
    $('#myTable2').DataTable({
        serverSide: true, // Fixed lowercase
        processing: true, // Fixed lowercase
        orderClasses: false,
        bSortClasses: false,
        bDeferRender: true,
        scrollCollapse: true,
        scrollY: 500,
        stateSave: true,
        scroller: { loadingIndicator: true },
        ajax: {
            url: dataUrl,
            type: 'GET',
            data: function(d) {
                // Pass all DataTables server-side parameters to the backend
                return d;
            }
        },
        destroy: true,
        columns: [
            { data: 'Id' },
            { data: 'Kod' },
            { data: 'Aciklama1' },
            { data: 'Aciklama2' },
            { data: 'GrupKod' },
            { data: 'SayKod' }
        ]
    });
};
</script>
{% endblock js %}

2. Implement Server-Side Pagination, Filtering & Sorting in Django View #

The biggest win comes from only returning a small subset of data per request. Update your test_json view to handle DataTables' server-side parameters:

from django.db.models import Q

def test_json(request):
    # Extract DataTables server-side parameters
    draw = int(request.GET.get('draw', 1))
    start = int(request.GET.get('start', 0))
    length = int(request.GET.get('length', 10))
    search_value = request.GET.get('search[value]', '')
    order_col_idx = int(request.GET.get('order[0][column]', 0))
    order_dir = request.GET.get('order[0][dir]', 'asc')

    # Map frontend column indexes to model field names
    column_mapping = {
        0: 'id',
        1: 'Kod',
        2: 'Aciklama1',
        3: 'Aciklama2',
        4: 'GrupKod',
        5: 'SayKod'
    }
    order_field = column_mapping.get(order_col_idx, 'id')
    if order_dir == 'desc':
        order_field = '-' + order_field

    # Base queryset: only fetch fields we need (skip unused Tip foreign key)
    queryset = SysTest.objects.only('id', 'Kod', 'Aciklama1', 'Aciklama2', 'GrupKod', 'SayKod')

    # Apply global search filter if provided
    if search_value:
        queryset = queryset.filter(
            Q(Kod__icontains=search_value) |
            Q(Aciklama1__icontains=search_value) |
            Q(Aciklama2__icontains=search_value) |
            Q(GrupKod__icontains=search_value)
            # Add SayKod if you need numeric search (adjust to __gte/__lte if needed)
        )

    # Calculate record counts for DataTables
    total_records = SysTest.objects.count()
    filtered_records = queryset.count()

    # Apply sorting and pagination
    paginated_data = queryset.order_by(order_field)[start:start+length]

    # Convert to dict directly (way faster than model instance + to_dict_json)
    data = list(paginated_data.values(
        'id', 'Kod', 'Aciklama1', 'Aciklama2', 'GrupKod', 'SayKod'
    ))
    # Rename 'id' to 'Id' to match frontend expectations
    for item in data:
        item['Id'] = item.pop('id')

    # Return response in DataTables required format
    response = {
        'draw': draw,
        'recordsTotal': total_records,
        'recordsFiltered': filtered_records,
        'data': data
    }

    return JsonResponse(response, safe=False, content_type="application/json")

3. Optimize Database Performance with Indexes #

Add indexes to fields used in filtering, sorting, and searching to speed up database queries:

class SysTest(models.Model):
    class Meta:
        db_table = 'sysTest'
        indexes = [
            models.Index(fields=['Kod']),
            models.Index(fields=['GrupKod']),
            models.Index(fields=['Aciklama1']),
            # Add more indexes based on your common search/sort patterns
        ]
    
    # ... rest of your model fields ...

    # You can remove the to_dict_json method now (we use values() directly)
    # def to_dict_json(self):
    #     return{
    #         'Id':self.id,
    #         'Kod':self.Kod,
    #         'Aciklama1':self.Aciklama1,
    #         'Aciklama2':self.Aciklama2,
    #         'GrupKod':self.GrupKod,
    #         'SayKod':self.SayKod,
    #     }

4. Additional Quick Wins #

• Skip model instance creation: Using values() instead of instantiating SysTest objects cuts down on memory usage and processing time significantly.
• Avoid N+1 queries: Since you're not using the Tip foreign key in your frontend, we explicitly exclude it with only() to prevent unnecessary joins.
• Limit page size: Keep the default length value (10-50) to minimize data transfer per request.

Why This Works #

Instead of sending 5 million records in one go, we now send only a small chunk (e.g., 10-50 records) per page. Combined with database indexes and optimized querying, your page load time should drop from 12 seconds to under 100ms for each page request.

内容的提问来源于stack exchange，提问作者selcukildem