Question di tabella di sistema utili in database relazionali

Panoramica

Le tabelle di sistema sono gestite dal DBMS (sistema di gestione del database), che fornisce visibilità su come funzionano i database internamente. In questo articolo, esaminerò le tabelle di sistema e i loro casi d’uso per i database SQL Server, Oracle, PostgreSQL e MySQL.

Domande mysql

1. Vedi quanto è grande ogni tabella in termini di righe per identificare set di dati di grandi dimensioni o hotspot di utilizzo.

SELECT 
    table_schema AS 'Database',
    table_name AS 'Desk',
    table_rows AS 'Row Depend',
    ROUND((data_length + index_length) / (1024 * 1024), 2) AS 'Whole Dimension (MB)'
FROM
    information_schema.TABLES
WHERE
    table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema')
ORDER BY
    table_rows DESC;

2. Esaminare come sono indicizzate le tabelle, il che è la chiave per l’ottimizzazione delle question. È possibile identificare indici duplicati o mancanti.

SELECT
table_schema,      -- Database/schema
table_name,        -- Desk title
index_name,        -- Identify of the index
column_name,       -- Column included within the index
non_unique
FROM
information_schema.statistics
WHERE
table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema')
ORDER BY
table_name, index_name, seq_in_index;

3. Comprendi le relazioni di tabella, i vincoli di integrità e le regole a cascata nel design del tuo schema.

SELECT 
    constraint_name,            -- Identify of the FK constraint
    table_name,                 -- Baby desk
    column_name,                -- Column in little one desk
    referenced_table_name,      -- Dad or mum desk
    referenced_column_name      
FROM 
    information_schema.key_column_usage
WHERE 
    referenced_table_name IS NOT NULL   -- Solely FKs (ignores PKs and distinctive keys)
ORDER BY 
    table_name, constraint_name;

4. Rilevare le question di lunga durata o potenzialmente bloccante per il monitoraggio delle prestazioni.

SELECT 
    id,             -- Session/connection ID
    consumer,           -- Question runner
    host,           -- From which host
    db,             -- Which database is being accessed
    command,        -- Kind of command (Question, Sleep, and so forth.)
    time,           -- Seconds the thread has been in its present state
    state,          -- What it is doing (e.g., Sending information)
    data            -- The precise SQL question
FROM 
    information_schema.processlist
WHERE 
    command != 'Sleep'   -- Ignore idle connections
ORDER BY 
    time DESC;

5. Audit chi ha accesso a ciò che è essenziale per le revisioni della sicurezza e Applicazione del minimo privilegio.

SELECT 
    grantee,            -- Consumer or position
    table_schema,       -- Returns schema
    table_name,         -- Returns desk title
    privilege_type      -- Kind of privilege (SELECT, INSERT, and so forth.)
FROM 
    information_schema.schema_privileges
UNION
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM 
    information_schema.table_privileges
ORDER BY 
    grantee, table_name;

Domande Oracle

1. Identificare le tabelle più grandi per la conservazione, l’indicizzazione o l’archiviazione. NUM_ROWS dipende dall’ultimo ANALYZE O DBMS_STATS.

SELECT 
    proprietor,             -- Schema/proprietor of the desk
    table_name,        -- Desk title
    num_rows           -- Estimated variety of rows (from statistics)
FROM 
    all_tables
WHERE 
    proprietor NOT IN ('SYS', 'SYSTEM')  -- Exclude system tables
ORDER BY 
    num_rows DESC;

2. Aiuto nell’ottimizzazione e nella convalida dell’indice, mostrando quali indici esistono e quali colonne coprono.

SELECT 
    ind.proprietor,          -- Schema/proprietor
    ind.index_name,     -- Index title
    ind.table_name,     -- Listed desk
    col.column_name,    -- Listed column
    col.column_position -- Order in index (for composite indexes)
FROM 
    all_indexes ind
JOIN 
    all_ind_columns col ON ind.index_name = col.index_name AND ind.proprietor = col.index_owner
WHERE 
    ind.proprietor NOT IN ('SYS', 'SYSTEM')
ORDER BY 
    ind.table_name, ind.index_name, col.column_position;

3. Traccia dipendenze e garantire l’integrità relazionale, particolarmente utile per la progettazione ERD o gli aggiornamenti a cascata.

SELECT 
    fk_detail.dependent_table AS source_table,               -- Desk with the overseas key
    fk_detail.dependent_column AS source_column,             -- Column containing the reference
    pk_info.independent_table AS target_table,               -- Desk being referenced
    pk_columns.independent_column AS target_column           -- Main key column being referenced
FROM 
    all_cons_columns fk_detail
INNER JOIN 
    all_constraints relationship_type 
    ON fk_detail.schema_owner = relationship_type.schema_owner 
    AND fk_detail.constraint_identifier = relationship_type.constraint_identifier
INNER JOIN 
    all_constraints pk_info 
    ON relationship_type.reference_owner = pk_info.schema_owner 
    AND relationship_type.reference_constraint = pk_info.constraint_identifier
INNER JOIN 
    all_cons_columns pk_columns 
    ON pk_info.schema_owner = pk_columns.schema_owner 
    AND pk_info.constraint_identifier = pk_columns.constraint_identifier 
    AND fk_detail.column_position = pk_columns.column_position
WHERE 
    relationship_type.constraint_category = 'R'    -- Relationships of reference sort solely
ORDER BY 
    fk_detail.dependent_table, fk_detail.constraint_identifier;

4. Tuning delle prestazioni: tenere traccia delle istruzioni SQL di lunga durata, individuare question lente o sessioni di blocco.

SELECT 
    classes.session_id,        -- Session id
    classes.user_name,         -- Who's operating it
    classes.session_status,    -- Energetic or inactive
    classes.sql_identifier,
    classes.hash_value,
    classes.elapsed_time_since_call AS runtime_seconds, -- Runtime in seconds
    sql_statements.statement_text  -- SQL question
FROM 
    v$active_sessions classes
INNER JOIN 
    v$sql_repository sql_statements 
    ON classes.sql_identifier = sql_statements.identifier
WHERE 
    classes.session_status="ACTIVE"
ORDER BY 
    classes.elapsed_time_since_call DESC;

Server SQL Domande

1. Trova le tabelle più grandi per conteggio delle righe per l’analisi delle prestazioni e dello spazio. Questo è più veloce di COUNT(*) Perché usa i metadati.

SELECT 
    t.title AS TableName,        -- Desk title
    SUM(p.rows) AS RowCount     -- Whole rows from all partitions (indexes)
FROM 
    sys.tables t
JOIN 
    sys.partitions p ON t.object_id = p.object_id
WHERE 
    p.index_id IN (0, 1)        -- 0 = heap, 1 = clustered index
GROUP BY 
    t.title
ORDER BY 
    RowCount DESC

2. Revisione indici (Escludendo le chiavi/vincoli primari) per identificare la ridondanza, gli indici mancanti o le opportunità di accordatura delle prestazioni.

SELECT 
    tbl.title AS TableName,
    idx.title AS IndexName,
    col.title AS ColumnName,
    idx.type_desc AS IndexType
FROM 
    sys.tables tbl
JOIN 
    sys.indexes idx ON tbl.object_id = idx.object_id
JOIN 
    sys.index_columns idx_col ON idx.object_id = idx_col.object_id AND idx.index_id = idx_col.index_id
JOIN 
    sys.columns col ON tbl.object_id = col.object_id AND idx_col.column_id = col.column_id
WHERE 
    idx.is_primary_key = 0 
    AND idx.is_unique_constraint = 0
ORDER BY 
    tbl.title, idx.title

3. Identificare come le tabelle sono correlate tramite chiavi estere, essenziali per comprendere le relazioni e le unità dei dati.

SELECT
fk.title ,                        -- Identify of the overseas key
parent_tbl.title , 	          -- International key outlined desk
parent_col.title ,                -- Dad or mum desk column
referenced_tbl.title , 	         -- Desk being referenced
referenced_col.title              -- Referenced desk column
FROM
sys.foreign_keys fk
JOIN
sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
JOIN
sys.tables parent_tbl
ON fkc.parent_object_id = parent_tbl.object_id
JOIN
sys.columns parent_col
ON parent_col.object_id = fkc.parent_object_id
AND parent_col.column_id = fkc.parent_column_id
JOIN
sys.tables referenced_tbl
ON fkc.referenced_object_id = referenced_tbl.object_id
JOIN
sys.columns referenced_col
ON referenced_col.object_id = fkc.referenced_object_id
AND referenced_col.column_id = fkc.referenced_column_id

4. Monitorare le question attualmente in esecuzione – utile per diagnosticare i colli di bottiglia o processi in fuga.

SELECT 
    r.standing,               -- 'operating', 'suspended', and so forth.
    r.start_time,           -- When it began
    r.command,              -- Command sort (SELECT, INSERT, and so forth.)
    t.textual content AS QueryText     -- Precise SQL textual content
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    r.standing="operating"
ORDER BY 
    r.start_time ASC

5. Auditing di sicurezza: chi ha accesso a cosa e come. Usa questo per verificare la conformità ai principi del minimo privilegio.

SELECT 
    dp.title AS DatabasePrincipal,
    dp.type_desc,                  -- Consumer, Position, and so forth.
    o.title AS ObjectName,
    p.permission_name,
    p.state_desc                   -- GRANT, DENY, and so forth.
FROM 
    sys.database_permissions p
JOIN 
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN 
    sys.objects o ON p.major_id = o.object_id
ORDER BY 
    dp.title, o.title

Question postgresql

1. Stima rapidamente le dimensioni della tabella. N_LIVE_TUP è del collettore di statistiche interne di PostgreSQL: è veloce ma approssimativo.

SELECT 
    relname AS "Desk Identify",           -- Identify of the desk
    n_live_tup AS "Estimated Rows"     -- Estimated variety of stay rows
FROM 
    pg_stat_user_tables
ORDER BY 
    n_live_tup DESC;

2. Rivela quali colonne sono indicizzate: critiche per la messa a punto delle prestazioni, specialmente su tavoli grandi.

SELECT 
    t.relname AS TableName,      -- Desk
    i.relname AS IndexName,      -- Index
    a.attname AS ColumnName      -- Column utilized in index
FROM 
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE 
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
ORDER BY 
    t.relname, i.relname;

3. Scopri i collegamenti relazionali (FK) tra le tabelle: utili per schemi di ingegneria inversa o costruzione di diagrammi ER.

SELECT
conname AS FK_Name,
conrelid::regclass AS Desk,             -- Baby desk
att.attname AS Column,                   -- Column in little one
confrelid::regclass AS ReferencedTable   -- Dad or mum desk
FROM
pg_constraint con
JOIN
pg_attribute att ON att.attrelid = con.conrelid and att.attnum = ANY(con.conkey)
WHERE
contype="f";                           -- f = fk (overseas key)

4. Elenco question in esecuzione attiva, ordinate per durata – aiuta a diagnosticare i problemi di lentezza o bloccare.

SELECT 
    pid AS process_id,                 -- Course of id
    now() - query_start AS length,   -- Run time
    question
FROM 
    pg_stat_activity
WHERE 
    state="energetic"
ORDER BY 
    length DESC;

Conclusione

L’incorporazione di queste question in regolari routine di manutenzione consente il processo decisionale basato sui dati man mano che i database crescono. Ricorda di eseguire question di sistema complesse durante le ore di punta per ridurre al minimo l’impatto delle prestazioni sui sistemi di produzione.