Используйте разреженные столбцы и множества столбцов с помощью mssql-python

Разреженные столбцы — это оптимизация хранения в Microsoft SQL для значений NULL в таблицах с большим количеством столбцов, допускающих значения NULL. Клиентские приложения рассматривают разрежённые столбцы как обычные столбцы. Драйвер mssql-python читает и записывает их как любой другой столбец без специальной обработки.

Функция	Описание
Разреженные столбцы	Значения NULL используют нулевую память
Наборы столбцов	XML-представление всех разрежённых столбцов
Широкие таблицы	Поддержка до 30 000 колонок

Замечание

Разрежённые столбцы — это функция сервера. Драйвер mssql-python не требует специальной конфигурации или API для работы с разрежёнными столбцами. Единственное различие, видимое для клиента: при использовании множества столбцов они возвращают XML-представление разреженных значений столбцов.

Лучше всего подходит для:

Таблицы с 20–50% и более значений NULL.
Хранение документов с переменными атрибутами.
Паттерны EAV (Entity-Attribute-Value).
Данные датчиков с множеством дополнительных показаний.

Создание разрежённых столбцов

Определите разрежённые столбцы в схеме таблицы, добавив SPARSE NULL модификатор к столбцам, которые часто содержат значения NULL.

Базовая таблица разреженных столбцов

Создайте таблицу с разрежёнными столбцами для необязательных атрибутов.

CREATE TABLE ProductAttributes (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Sparse columns for optional attributes
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

С набором столбцов

Добавьте набор столбцов, чтобы обеспечить XML-доступ ко всем разрежённым столбцам одновременно.

CREATE TABLE ProductAttributesWithSet (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Column set provides XML access to all sparse columns
    SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    -- Sparse columns
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Вставить данные в разреженный столбец

Вставляйте данные в разрежённые столбцы по имени, точно так же, как в обычные столбцы.

Вставьте отдельные столбцы

Вставьте продукты с определёнными значениями разрежённых столбцов.

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create the table with sparse columns
cursor.execute("DROP TABLE IF EXISTS ProductAttributes")
cursor.execute("""
    CREATE TABLE ProductAttributes (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert with some sparse columns populated
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Color, Size)
    VALUES (%(id)s, %(name)s, %(color)s, %(size)s)
""", {"id": 1, "name": "T-Shirt", "color": "Blue", "size": "Large"})

# Insert with different sparse columns
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Weight, Material)
    VALUES (%(id)s, %(name)s, %(weight)s, %(material)s)
""", {"id": 2, "name": "Coffee Mug", "weight": 0.35, "material": "Ceramic"})

conn.commit()

Вставить через набор столбцов (XML)

Вставьте несколько значений разрежённых столбцов одновременно, передавая XML в набор столбцов.

# Create the table with a column set
cursor.execute("DROP TABLE IF EXISTS ProductAttributesWithSet")
cursor.execute("""
    CREATE TABLE ProductAttributesWithSet (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert using column set XML
cursor.execute("""
    INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
    VALUES (%(id)s, %(name)s, %(xml)s)
""", {
    "id": 3,
    "name": "Laptop Bag",
    "xml": "<Color>Black</Color><Size>Medium</Size><Material>Nylon</Material><Warranty>24</Warranty>"
})
conn.commit()

Динамическая вставка атрибутов

Создайте функцию, которая принимает динамические атрибуты в виде словаря и автоматически строит XML.

def insert_with_attributes(cursor, product_id: int, name: str, attributes: dict):
    """Insert product with dynamic sparse column attributes."""
    # Build XML for column set
    xml_parts = [f"<{key}>{value}</{key}>" for key, value in attributes.items()]
    attributes_xml = "".join(xml_parts)
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": attributes_xml or None})

# Usage
insert_with_attributes(cursor, 4, "Headphones", {
    "Color": "Silver",
    "Warranty": 12,
    "Manufacturer": "AudioTech"
})
conn.commit()

Запрос по разрежённым столбцам

Запросите разрежённые столбцы по названию или извлекайте все разреженные значения сразу через набор столбцов.

Запрос отдельных столбцов

Запросы к специфическим разрежённым столбцам с использованием стандартного синтаксиса SELECT.

# Query specific sparse columns
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size
    FROM ProductAttributes
    WHERE Color IS NOT NULL
""")

for row in cursor:
    print(f"{row.ProductName}: {row.Color}, {row.Size}")

Запрос через набор столбцов

Извлечь все значения разрежённых столбцов в виде XML из набора столбцов.

# Get column set XML
cursor.execute("""
    SELECT ProductID, ProductName, SparseAttributes
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
print(f"Product: {row.ProductName}")
print(f"Attributes XML: {row.SparseAttributes}")

Разбор множества столбцов XML в Python

Разберите XML из набора столбцов, чтобы преобразовать его в словарь Python для упрощения дальнейшей обработки.

from xml.etree import ElementTree as ET

def get_product_attributes(cursor, product_id: int) -> dict:
    """Get product with parsed sparse attributes."""
    cursor.execute("""
        SELECT ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        return None
    
    result = {"ProductName": row.ProductName}
    
    # Parse XML column set
    if row.SparseAttributes:
        # Wrap in root element for parsing
        xml_str = f"<root>{row.SparseAttributes}</root>"
        root = ET.fromstring(xml_str)
        
        for elem in root:
            result[elem.tag] = elem.text
    
    return result

# Usage
product = get_product_attributes(cursor, 3)
print(product)
# {'ProductName': 'Laptop Bag', 'Color': 'Black', 'Size': 'Medium', 'Material': 'Nylon', 'Warranty': '24'}

Запрос с помощью SELECT *

Когда вы используете SELECT *, набор столбцов возвращается как один XML-столбец вместо отдельных разрежённых столбцов.

# SELECT * returns column set instead of individual sparse columns
cursor.execute("""
    SELECT * FROM ProductAttributesWithSet WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
# Returns: ProductID, ProductName, SparseAttributes (not individual columns)
print(f"Columns: {[col[0] for col in cursor.description]}")

Явно указывайте отдельные столбцы

Чтобы получить отдельные разрежённые столбцы из таблицы с набором столбцов, указывайте их явно в клаузе SELECT.

# To get individual sparse columns with column set table, list them explicitly
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size, Weight, Material, Warranty, Manufacturer
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

# Now each sparse column is available as separate property
row = cursor.fetchone()
print(f"Color: {row.Color}, Material: {row.Material}")

Обновление разрежённых столбцов

Обновите отдельные разрежённые столбцы по имени или сразу замените все разрежённые значения через XML набора столбцов.

Обновление отдельных колонок

Обновите определённые значения разрежённых столбцов с использованием стандартного SQL-синтаксиса UPDATE .

cursor.execute("""
    UPDATE ProductAttributes
    SET Color = %(color)s, Weight = %(weight)s
    WHERE ProductID = %(id)s
""", {"id": 1, "color": "Red", "weight": 0.2})
conn.commit()

Обновление с помощью набора столбцов

Замените все значения разрежённых столбцов, обновив напрямую XML набора столбцов.

# Replace all sparse column values via column set
cursor.execute("""
    UPDATE ProductAttributesWithSet
    SET SparseAttributes = %(xml)s
    WHERE ProductID = %(id)s
""", {
    "id": 3,
    "xml": "<Color>Navy</Color><Size>Large</Size><Material>Leather</Material>"
})
conn.commit()
# Note: This clears any sparse columns not included in the XML

Частичное обновление через набор столбцов

Обновляйте только отдельные разреженные атрибуты, сохраняя значения других атрибутов, не включённых в обновление.

# To update only specific attributes, merge with existing
def update_attributes(cursor, product_id: int, updates: dict):
    """Update specific sparse attributes while preserving others."""
    # Get current attributes
    cursor.execute("""
        SELECT Color, Size, Weight, Material, Warranty, Manufacturer
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        raise ValueError(f"Product {product_id} not found")
    
    # Merge updates
    current = {
        "Color": row.Color,
        "Size": row.Size,
        "Weight": row.Weight,
        "Material": row.Material,
        "Warranty": row.Warranty,
        "Manufacturer": row.Manufacturer
    }
    
    for key, value in updates.items():
        current[key] = value
    
    # Build XML with non-null values
    xml_parts = []
    for key, value in current.items():
        if value is not None:
            xml_parts.append(f"<{key}>{value}</{key}>")
    
    cursor.execute("""
        UPDATE ProductAttributesWithSet
        SET SparseAttributes = %(xml)s
        WHERE ProductID = %(id)s
    """, {"id": product_id, "xml": "".join(xml_parts) or None})

# Usage
update_attributes(cursor, 3, {"Color": "Brown", "Warranty": 36})
conn.commit()

Динамические паттерны столбцов

Создайте гибкие запросы, которые динамически ищут по разрежённым столбцам, используя списки разрешений для проверки имён столбцов и предотвращения атак SQL-инъекций.

Запрос к данным в стиле EAV

Ищите продукты по конкретному имени атрибута и значению с помощью сопоставления шаблонов Entity-Attribute-Value (EAV).

def find_products_by_attribute(cursor, attribute_name: str, attribute_value: str) -> list:
    """Find products with specific attribute value."""
    # Validate column name against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    if attribute_name not in allowed_columns:
        raise ValueError(f"Invalid attribute: {attribute_name}")
    
    cursor.execute(f"""
        SELECT ProductID, ProductName, {attribute_name}
        FROM ProductAttributesWithSet
        WHERE {attribute_name} = %(value)s
    """, {"value": attribute_value})
    
    return cursor.fetchall()

# Find all blue products
blue_products = find_products_by_attribute(cursor, "Color", "Blue")

Гибкий поиск по атрибутам

Ищите продукты, соответствующие нескольким необязательным атрибутам одновременно.

def search_by_attributes(cursor, **attributes) -> list:
    """Search products by multiple optional attributes."""
    # Validate column names against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    invalid = set(attributes.keys()) - allowed_columns
    if invalid:
        raise ValueError(f"Invalid attributes: {invalid}")
    
    conditions = ["1=1"]  # Always true base condition
    params = {}
    
    for i, (key, value) in enumerate(attributes.items()):
        if value is not None:
            conditions.append(f"{key} = %(attr_{i})s")
            params[f"attr_{i}"] = value
    
    query = f"""
        SELECT ProductID, ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE {' AND '.join(conditions)}
    """
    
    cursor.execute(query, params)
    return cursor.fetchall()

# Search by multiple attributes
results = search_by_attributes(cursor, Color="Black", Material="Nylon")

Вопросы производительности

Оценивайте, когда разрежённые колонны приносят пользу для хранения, оценивайте накладные расходы и используйте мониторинг производительности для оптимизации дизайна разрежённых колонн.

Когда использовать разрежённые столбцы

Хорошие кандидаты для редких колонок включают:

Столбцы, содержащие более 60–70 % значений NULL.
Широкие таблицы с множеством необязательных столбцов.
Рабочие нагрузки, где оптимизация хранения является приоритетом.

Избегайте использования разрежённых столбцов, когда:

Большинство строк имеют значения (каждое не-NULL значение добавляет 4 байта накладных расходов).
Столбец часто используется в предложениях WHERE.
Столбец является частью кластерного индекса.

Проверьте экономию на хранении

Сравните размер хранилища в разрежённых и неразрежённых версиях одной таблицы.

-- Compare storage with and without sparse
EXEC sp_spaceused 'ProductAttributes';
EXEC sp_spaceused 'ProductAttributesWithoutSparse';

Соображения индекса

Можно индексировать разрежённые столбцы. Фильтрованные индексы хорошо работают для разреженных данных, потому что пропускают NULL строки:

CREATE INDEX IX_Products_Color
ON ProductAttributes(Color)
WHERE Color IS NOT NULL;

пакетные операции

Оптимизировать вставку данных для нескольких продуктов с разрежёнными столбцами, формируя XML набора столбцов в Python перед передачей строк в bulkcopy().

Пакетная вставка с разрежёнными столбцами

Используйте массовое копирование для эффективной вставки нескольких товаров с редкими атрибутами.

def bulk_insert_with_attributes(conn, products: list[dict]):
    """Bulk insert products with sparse attributes."""
    rows = []
    for product in products:
        attrs = product.get("attributes", {})
        xml = "".join(f"<{k}>{v}</{k}>" for k, v in attrs.items()) or None
        rows.append((product["id"], product["name"], xml))
    
    cursor = conn.cursor()
    result = cursor.bulkcopy("ProductAttributesWithSet", rows)
    return result["rows_copied"]

# Usage
products = [
    {"id": 100, "name": "Widget A", "attributes": {"Color": "Red", "Size": "Small"}},
    {"id": 101, "name": "Widget B", "attributes": {"Weight": 1.5, "Material": "Steel"}},
    {"id": 102, "name": "Widget C", "attributes": {}},  # No sparse attributes
]
bulk_insert_with_attributes(conn, products)
conn.commit()

Лучшие практики

Следуйте шаблонам валидации, используйте наборы столбцов для гибкости и контролируйте разрежённость столбцов, чтобы убедиться, что дизайн разрежённого столбца соответствует целям производительности и обслуживания.

Проверка значений разрежённых столбцов

Проверьте, что все разреженные атрибуты соответствуют допустимому набору перед вставкой данных.

# Sparse columns have the same constraints as regular columns
# The SPARSE keyword only affects storage

def validate_and_insert(cursor, product_id: int, name: str, attributes: dict):
    """Insert with validation."""
    allowed_attributes = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    
    invalid = set(attributes.keys()) - allowed_attributes
    if invalid:
        raise ValueError(f"Unknown attributes: {invalid}")
    
    xml_parts = [f"<{k}>{v}</{k}>" for k, v in attributes.items()]
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": "".join(xml_parts) or None})

Используйте наборы столбцов для гибкости

Наборы столбцов упрощают работу с разрежёнными столбцами:

Добавляйте новые разрежённые столбцы без изменений кода.
Сохраняйте динамические атрибуты.
Автоматическая сериализация и десериализация XML.

Без набора столбцов вам нужны явные списки столбцов. При наличии набора столбцов XML-столбец автоматически обрабатывает динамические атрибуты.

Отслеживайте процент значений NULL

Проанализируйте процент NULL для столбца, чтобы определить, подходит ли он для оптимизации с разрежёнными столбцами.

def analyze_sparseness(cursor, table: str, column: str) -> float:
    """Check if column is a good sparse candidate."""
    # Validate identifiers to prevent SQL injection
    import re
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', column):
        raise ValueError(f"Invalid column name: {column}")

    cursor.execute(f"""
        SELECT 
            COUNT(*) AS TotalRows,
            SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END) AS NullRows
        FROM {table}
    """)
    
    row = cursor.fetchone()
    null_percentage = (row.NullRows / row.TotalRows * 100) if row.TotalRows > 0 else 0
    
    print(f"Column {column}: {null_percentage:.1f}% NULL")
    print(f"Recommendation: {'Good sparse candidate' if null_percentage > 60 else 'Keep as regular column'}")
    
    return null_percentage

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-07-22