Il Database di Wordpress: guida completa

Ogni sviluppatore che lavora con questo CMS dovrebbe avere un certo livello di conoscenza del database di WordPress . Vediamo insieme la sua struttura, come funziona ed altre pratiche conoscenze per usarlo al meglio.

Il Database di WordPress

Una parte essenziale dei CMS, almeno dei più famosi e utilizzati, è il database ed un sito web WordPress non fa eccezione. WordPress utilizza il sistema di gestione del database MySQL e richiede MySQL versione 5.6 e successive.

Funziona anche su MariaDB versione 10.1 o successive, sappiamo che il progetto MySQL ha preso due strade una commerciale ed una con licenza GPL, quindi è stato creato un fork di MySQL che si chiama appunto MariaDB e che in realtà è compatibile e può essere utilizzato come sostituto. In pratica in tutte le nuove versioni delle varie distro di Linux viene fornito MariaDB, non ci soffermeremo sulle differenze tra i due dal momento che molti articoli ed analisi approfondite si possono trovare in rete su questo argomento.

MySQL Storage Engines

La parte del database responsabile della lettura e della scrittura dei dati è detta Storage Engine (motore di archiviazione). Dalla versione di MySQL 5.5 in poi il motore di archiviazione predefinito è InnoDB ed è in pratica diventato il motore di archiviazione usato più comunemente dato che ha il blocco a livello di riga invece del blocco a livello di tabella, cosa importante quando si esegue un’esportazione/backup con mysqldump e poi ha il supporto per le transazioni, consentendo quindi di eseguire il commit e il rollback SQL ed inoltre ha il pieno supporto per le chiavi esterne e i vincoli di relazione.

In passato il motore di archiviazione utilizzato da WordPress era MyISAM e facilmente esistono ancora siti WordPress più vecchi che lo hanno in esecuzione nel database. In realtà poi ci sono casi in cui alcuni siti potrebbero anche avere un mix di tabelle che utilizzano i motori di archiviazione MyISAM e InnoDB.

Suggerimento: la conversione delle tabelle MyISAM in InnoDB può migliorare le prestazioni del database.

A volte durante la migrazione da un server a un altro del database di WordPress è possibile riscontrare problemi di codifica dei caratteri e quindi nasce spontanea la domanda: perchè e cosa sono sono questi set di caratteri e regole di confronto?

Un set di caratteri di MySQL è un insieme di caratteri consentiti in una stringa. In un alfabeto ci sono 26 caratteri – dalla a alla z. Ad ogni lettera viene assegnato un numero, ad esempio a = 1, b = 2, c = 3 e così via. La lettera è il simbolo e il numero associato è la codifica.

La combinazione di tutte le lettere dalla a alla z e le codifiche dei numeri corrispondenti è un set di caratteri. MySQL supporta molti set di caratteri che consentono di memorizzare quasi tutti i caratteri in una stringa.

La “Collation MySQL” è un insieme di regole che vengono utilizzate per confrontare i caratteri stessi in un particolare set. Quindi il database per confrontare le stringhe utilizza i numeri di codifica dei caratteri. Un semplice esempio di regola di confronto (collation rule) è il confronto cosiddetto “case insensitive” ovvero senza distinzione tra maiuscole e minuscole, ma poi le “collations” possono diventare piuttosto complicate, come nel caso di lingue estere, la documentazione di MySQL descrive cosi il problema:

la maggior parte delle collations ha molte regole, non solo per distinguere le lettere maiuscole, ma anche per distinguere gli accenti (un "accento" è un segno attaccato a un carattere come in tedesco Ö) e per le mappature di più caratteri (come la regola che definisce Ö = OE in una delle due collations in tedesco).

MySQL da la possibilità di specificare set di caratteri (character sets) e collations a quattro livelli: il server, il database, le tabelle e le colonne.

Per i siti WordPress, il set di caratteri consigliato è utf8mb4 e la collation raccomandata è utf8mb4_unicode_ci. A partire da WordPress 4.2 le tabelle sono state aggiornate dal set di caratteri utf8 a utf8mb4, consentendo la memorizzazione di caratteri a 4 byte, il che significa che qualsiasi carattere Unicode può essere memorizzato nel database.

Struttura del database di WordPress

Il prefisso di default per le tabelle del database nelle installazioni di WordPress fai-da-te è “wp_” però per motivi di sicurezza è consigliabile mettere un prefisso diverso, le riporterò da qui in poi precedute dal segno “_” (underscore) dal momento che il prefisso è libero e sempre diverso.

Se proprio si vuole avere una descrizione della struttura delle tabelle, con i tipi di dati delle colonne e gli indici, allora, dato che la cosa sarebbe un po’ lunga da riportare in questo articolo, si può fare riferimento al Codex di WordPress.

_post

La tabella _post è probabilmente la tabella più importante nel database di WordPress. Il suo nome a volte può trarre in inganno facendo pensare che contenga esclusivamente i post sul blog. Ma, anche se il nome non qualifica perfettamente il contenuto, è una tabella estremamente potente che memorizza vari tipi di contenuti ivi inclusi post, pagine, voci di menu, allegati multimediali e qualsiasi tipo di custom post utilizzato in un sito WordPress.

La flessibilità del contenuto della tabella viene definita nella colonna ‘post_type’ che indica se la riga è un post, una pagina, un allegato, un nav_menu_item o altro. Ma se da un verso è flessibile dall’altro è un po’ complessa da descrivere. Principalmente questa tabella contiene righe di oggetti di contenuto di tipi diversi, ma per semplicità, in questo articolo farò riferimento alle righe come “post”.

ID – ID numerico univoco assegnato a ciascun post.
post_author – l’ID dell’utente che lo ha creato. (Riferimento alla tabella _users.)
post_date – ora e data di creazione.
post_date_gmt – Ora GMT e data di creazione. L’ora e la data GMT vengono archiviate in modo che non vi sia alcuna dipendenza dal fuso orario di un sito in futuro.
post_content : contiene tutto il contenuto del post, inclusi HTML, shortcode e altri contenuti.
post_title – titolo del post.
post_excerpt – introduzione personalizzata o versione breve del contenuto.
post_status – stato del post, ad esempio ‘bozza’, ‘in attesa’, ‘privato’, ‘pubblico’.
comment_status – indica se i commenti sono consentiti.
ping_status – indica se il post consente ping e trackback .
post_password – password opzionale utilizzata per visualizzare il post.
post_name – slug friendly URL del titolo del post.
to_ping : un elenco di URL a cui WordPress dovrebbe inviare i pingback quando viene aggiornato.
ping : un elenco di URL a cui WordPress ha inviato i pingback quando è stato aggiornato.
post_modified – ora e data dell’ultima modifica.
post_modified_gmt – Ora GMT e data dell’ultima modifica.
post_content_filtered – utilizzato dai plugin per memorizzare nella cache una versione di post_content in genere passata attraverso il filtro ‘the_content’. Non utilizzato dal core di WordPress stesso.
post_parent – utilizzato per creare una relazione tra questo post e un altro quando questo post è una revisione, un allegato o un altro tipo.
guid – Identificatore univoco globale, l’URL permanente del post, non la versione permalink.
menu_order – contiene il numero di visualizzazione per le pagine e altri tipi non di post.
post_type – l’identificatore del tipo di contenuto.
post_mime_type – utilizzato solo per gli allegati, il tipo MIME del file caricato.
comment_count – numero totale di commenti, pingback e trackback.

_postmeta

Questa è la tabella che contiene tutte le informazioni aggiuntive sui singoli post. È una tabella “verticale” che utilizza coppie chiave/valore per archiviare i dati, una tecnica che WordPress impiega su diverse tabelle nel il database e che consente al core, ai plugin e ai temi di WordPress di archiviare dati praticamente illimitati.

meta_id – è l’ID numerico univoco assegnato a ciascuna riga della tabella.
post_id : l’ID del post a cui si riferiscono i dati. (Riferimento alla tabella _posts.)
meta_key – una chiave di identificazione per la parte di dati.
meta_value : il dato effettivo.

_comments

Qualsiasi articolo (post) che consente una discussione può avere dei commenti collegati insieme. Questa tabella memorizza appunti questi commenti e alcuni dati specifici su di essi. Ulteriori informazioni possono essere memorizzate in _commentmeta.

comment_ID – ID numerico univoco assegnato a ciascun commento.
comment_post_ID – ID del post a cui si riferisce questo commento. (Riferimento alla tabella _posts.)
comment_author – nome dell’autore del commento.
comment_author_email – Email dell’autore del commento.
comment_author_url – URL per l’autore del commento.
comment_author_IP – Indirizzo IP dell’autore del commento.
comment_date – ora e data in cui è stato pubblicato il commento.
comment_date_gmt – Ora GMT e data in cui è stato pubblicato il commento.
comment_content – il testo del commento effettivo.
comment_karma – non utilizzato dal core di WordPress, può essere utilizzato dai plugin per aiutare a gestire i commenti.
comment_approved – indica se il commento è stato approvato.
comment_agent – indica da dove è stato pubblicato il commento, ad es. browser, sistema operativo ecc.
comment_type – tipo di commento: commento, pingback o trackback.
comment_parent – fa riferimento a un altro commento quando questo commento è una risposta.
user_id – ID dell’autore del commento se è un utente registrato al sito. (Riferimento alla tabella _users.)

_commentmeta

Tabella dove vengono memorizzate tutte le ulteriori informazioni relative a un commento.

meta_id – ID numerico univoco assegnato a ciascuna riga della tabella.
comment_id : l’ID del post a cui si riferiscono i dati. (Riferimento alla tabella _comments.)
meta_key – una chiave di identificazione per la parte di dati.
meta_value : il dato effettivo.

_terms

I terms sono elementi di una tassonomia che vengono utilizzati per classificare gli oggetti. Tassonomia??? Che significa? WordPress consente di classificare elementi come post e custom post types in vari modi. Per esempio, creando un articolo in WordPress, per impostazione predefinita si può aggiungere una categoria e alcuni tag. Ebbene sia la “Categoria” che i “Tag” sono degli esempi di cosa sia una tassonomia, praticamente è un modo per raggruppare insieme le cose dandogli una classificazione.

Ad esempio per classificare questo articolo potrei collegarlo ad una categoria chiamata “Guida” o “Tutorials” e potrei aggiungere dei tags, tipo “database” e “mysql”. La categoria e i tags che avrei inserito sarebbero terms contenuti in questa tabella.

term_id – ID numerico univoco assegnato a ciascun terms.
nome – il nome del terms.
slug : lo slug friendly URL del nome.
term_group – la possibilità per temi o plugin di raggruppare i terms per usare alias. Non popolato dal core di WordPress stesso.

_termmeta

La versione di WordPress 4.4 ha introdotto una nuova tabella per memorizzare i metadati dei terms: _termmeta. È possibile accedere al termmeta in modo simile al postmeta con le cosiddette helper functions add_term_meta , get_term_meta , update_term_meta e delete_term_meta.

_termmeta consente poi agli sviluppatori di archiviare dati personalizzati sui terms in modo standard, cioè, ad esempio, si potrebbe voler allegare un’immagine a una categoria di post da visualizzare poi nella pagina dell’archivio delle categorie.

meta_id – ID numerico univoco assegnato a ciascuna riga della tabella.
term_id – l’ID del terms correlato. (Riferimento alla tabella _terms.)
meta_key – una chiave di identificazione per la parte di dati.
meta_value : il dato effettivo.

_term_taxonomy

Seguendo l’esempio di _terms sopra indicato, i termini ‘Guida’, ‘database’ e ‘mysql’ archiviati in _terms non esistono ancora come ‘Categoria’ e come ‘Tag’ a meno che non venga fornito loro un contesto. A ogni terms viene assegnata una tassonomia utilizzando questa tabella.

La struttura di questa tabella consente di utilizzare lo stesso termine in diverse tassonomie. Per esempio, “Database” potrebbe essere utilizzato come categoria per i post e come termine di una tassonomia personalizzata per un tipo di post personalizzato (pensiamo a portfolio_category per degli elementi di un portfolio). Il termine di Database esisterebbe una volta in _terms, ma ci sarebbero due righe in _term_taxonomy per ogni tassonomia.

term_taxonomy_id – ID numerico univoco assegnato a ciascuna riga della tabella.
term_id – l’ID del terms correlato. (Riferimento alla tabella _terms.)
tassonomia : lo slug della tassonomia. Questa può essere la tassonomia incorporata o qualsiasi altra tassonomia registrata utilizzando la funzione WordPress register_taxonomy() .
description – descrizione del termine in questa tassonomia.
genitore – ID di un termine genitore. Utilizzato per tassonomie gerarchiche tipo le Categorie.
count – numero di oggetti post a cui è stato assegnato il termine per questa tassonomia.

_term_relationships

Finora abbiamo visto come i termini e le loro tassonomie sono archiviati nel database, ma dobbiamo ancora vedere come WordPress memorizza i dati critici quando si tratta di utilizzare le tassonomie. Il post esiste in _posts e quando assegniamo effettivamente la categoria e i tag questa è la tabella di collegamento che memorizza tali informazioni. Ogni riga definisce una relazione tra un post (oggetto) in _posts e un termine di una certa tassonomia in _term_taxonomy.

object_id – l’ID dell’oggetto del post. (Riferimento alla tabella _posts .)
term_taxonomy_id – l’ID della coppia termine/tassonomia. (Riferimento alla tabella _term_taxonomy .)
term_order : consente di ordinare i termini per un oggetto.

_users

Quello che rende ottimo WordPress come CMS e Framework di applicazioni è la completa gestione degli utenti, chiaramente è una delle sue caratteristiche più forti. Questa tabella è la forza trainante che ci sta dietro.

ID – ID numerico univoco assegnato a ciascun utente.
user_login – nome utente univoco.
user_pass – hash della password dell’utente.
user_nicename – nome dell’utente visualizzato.
user_email – indirizzo email dell’utente.
user_url – URL dell’utente.
user_registered – ora e data di registrazione dell’utente.
user_activation_key – utilizzato per reimpostare le password.
user_status – è stato utilizzato in Multisite pre WordPress 3.0 per indicare un utente spam.
display_name – il nome scelto per utilizzare pubblicamente nel sito, può essere user_login, user_nicename, nome o cognome definiti in _usermeta.

_usermeta

In questa tabella viene memorizzata ogni ulteriore informazione relativa agli utenti. Vedrai altri campi del profilo utente nella dashboard che sono archiviati qui.

umeta_id – ID numerico univoco assegnato a ciascuna riga della tabella.
user_id – ID dell’utente correlato. (Riferimento alla tabella _users .)
meta_key – una chiave di identificazione per la parte di dati.
meta_value : il dato effettivo.

_options

La tabella _options è quella dove viene archiviata tutta la configurazione del sito, inclusi i dati sul tema, i plug-in attivi, i widget e i dati temporanei memorizzati nella cache. In genere in questa tabella anche i plugin ed i temi memorizzano le loro impostazioni.

E’ anche un altro esempio di una tabella con coppie chiave/valore che consente di archiviare tutti i tipi di dati per tutta una varietà di scopi e casistiche.

option_id – ID numerico univoco assegnato a ciascuna riga della tabella.
option_name – una chiave di identificazione per la parte di dati.
valore_opzione – il dato effettivo. I dati sono spesso serializzati quindi è necessario gestirli con attenzione.
autoload – controlla se l’opzione viene caricata automaticamente dalla funzione wp_load_alloptions() (che mette le opzioni nella cache degli oggetti ad ogni caricamento della pagina).

_links

Tempo fa era molto di moda, dato anche l’aumento della popolarità dei blog, avere un blogroll (collegamenti ad altri siti), ma in realtà anche ora è una cosa che si usa fare e questa è la tabella che contiene tutti questi link.

Però dato che i blogroll vengono utilizzati sempre meno, a partire da WordPress 3.5 l’amministrazione dei collegamenti è stata rimossa dall’interfaccia utente di amministrazione. La tabella però rimane nel database per la compatibilità con le versioni precedenti ed è possibile utilizzare la vecchia interfaccia utente di gestione dei collegamenti utilizzando un plug-in apposito.

link_id – ID numerico univoco assegnato a ciascuna riga della tabella.
link_url – URL del collegamento.
link_name – nome del collegamento.
link_image – URL di un’immagine relativa al collegamento.
link_target – il frame di destinazione per il collegamento. es. _vuoto, _superiore, _nessuno.
link_description – descrizione del collegamento.
link_visible – controlla se il collegamento è pubblico o privato.
link_owner – ID dell’utente che ha creato il collegamento. (Riferimento alla tabella _users .)
link_rating : aggiungi una valutazione compresa tra 0 e 10 per il collegamento.
link_updated – ora e data di aggiornamento del collegamento.
link_rel – relazione del collegamento.
link_notes – note sul collegamento.
link_rss – Indirizzo RSS per il collegamento.

Esiste un utile diagramma di relazione tra entità per spiegare le relazioni tra tutte le tabelle e si può trovare sul codex di WordPress . Questo è stato creato per la versione 3.8 ma la struttura è ancora valida ed attuale:

Connessione al database WordPress

Normalmente si usa phpMyAdmin per connettersi al database di WordPress, uno strumento di amministrazione scritto in PHP che utilizza il browser per questa operazione. Praticamente tutti i web hosting consentono di usarlo e viene usato anche in ambiente di sviluppo locale ad esempio con MAMP, ma ovviamente esistono tanti altri strumenti e tools per connettersi ad un database MySQL, ognuno utilizza quello con cui si trova meglio e se si ha la possibilità di connettersi via SSH si possono utilizzare i comandi Linux per eseguire direttamente tutte le operazioni che servono.

Ottimizzazione del database e delle query di WordPress

Ci sono molti siti WordPress, tra i quali ci potrebbe essere anche il tuo, che sono molto visitati e aggiornano i contenuti molto frequentemente, in questo caso il tuo database WordPress eseguirà molte istruzioni UPDATE e DELETE che, con il tempo, possono portare alla frammentazione dei file di dati MySQL. Questo può causare dimensioni del database inutilmente grandi e può influire sulle prestazioni del database.

Le tabelle possono essere ottimizzate utilizzando l’istruzione MySQL OPTIMIZE TABLE oppure attraverso phpMyAdmin che ha utilità per questa operazione. Per le tabelle che utilizzano il motore di archiviazione InnoDB, la query ricostruisce la tabella per aggiornare le statistiche dell’indice e liberare spazio inutilizzato. Per esempio:

Ma ci sono altre cose da poter fare per mantenere le dimensioni del database di WordPress a una dimensione ragionevole e di conseguenza anche mantenerlo veloce.

Una di queste è il ridimensionamente della funzionalità delle revisioni di WordPress, che in realtà è utile lavorando nella modifica dei contenuti, però ha una impostazione predefinita per la quale viene memorizzata una quantità infinita di revisioni e questo generalmente non è necessario, quindi e può essere limitata impostando la seguente costante nel file wp-config.php:

define( 'WP_POST_REVISIONS', 10 );

Un’altra interessante funzionalità è quella che consente a WordPress di salvare automaticamente anche post, pagine e custom post type ogni minuto. Forse allungare questo intervallo e non interrogare continuamente il database può incidere sulle prestazioni e si può fare con questa costante, sempre da impostare nel file wp-config.php:

define( 'AUTOSAVE_INTERVAL', 300 );

Tra l’altro WordPress memorizza anche i post eliminati nel cestino per minimo 30 giorni prima di eliminarli. Questo intervallo può essere ridotto con la seguente costante, sempre nel file wp-config.php:

define( 'EMPTY_TRASH_DAYS', 3 );

Indici

Uno dei fattori più importanti per la velocità e le prestazioni del database è un indice del database. È possibile creare un indice in una o più colonne questo consente al database di cercare rapidamente i dati nelle colonne, nonché di ordinarle in modo efficiente.

Il database WordPress predefinito ha indici su un certo numero di colonne nelle sue tabelle. Ad esempio, la tabella _postmeta ha la colonna post_id che contiene un riferimento al post a cui si riferisce la parte di dati (meta).

Durante la ricerca di tutti i post meta per un ID post specifico, questo indice rende la query il più veloce possibile. Questo è importante su tabelle come la tabella _postmeta, che potrebbe avere anche milioni di righe di dati.

Gli indici dovrebbero essere considerati attentamente quando si vogliono creare tabelle WordPress personalizzate, soprattutto se fanno parte di un plug-in che hai in previsione di distribuire. Quando si progettano le strutture delle tabelle, è necessario considerare le prestazioni delle tabelle e delle query SQL su siti su larga scala. Non tutti lo fanno, per questo plugin non efficienti o progettati male incidono notevolmente nelle prestazioni del database WordPress.

Migrazione di database WordPress

WordPress memorizza l’URL del sito e il percorso dei files all’interno del database in diversi punti e questo è uno dei motivi per cui lo spostamento da server diversi o la migrazione del proprio sito WordPress dal sito di sviluppo a un URL di un sito live può essere complicato.

L’URL del sito è archiviato nella tabella _options nei campi home e site_url e anche in qualsiasi punto in cui sono archiviati gli URL assoluti per immagini e altre risorse.

In realtà, fortunatamente, esistono degli strumenti per rendere la migrazione di un database WordPress un processo semplice e veloce. Per questo si può cercare nel repository dei plugin WordPress e se ne troveranno molti, quindi è possibile scegliere quello con cui si è più a proprio agio.

Sicurezza del database

Sicuramente ogni sviluppatore ha un piano di emergenza per avere a disposizione le copie del database e dei files da poter usare per la migrazione perché è a conoscenza dell’importanza dei backup del database. Infatti questa è una operazione che dovrebbe essere eseguito regolarmente (insieme alle cartelle dei files) per assicurarsi di poter tornare a una versione precedente se ci sono problemi o se il database viene danneggiato o violato.

Un aspetto importante di questo sono i backup fuori sede, cioè conservati presso un provider cloud dato che se il proprio server non funziona più diventa estremamente importante avere accesso a un backup recente archiviato in modo sicuro altrove.

E’ per altro necessario di testare regolarmente i backup, perché un backup non ha valore se non può essere ripristinato correttamente ed al momento del ripristino non piacerebbe a nessuno trovarsi con in mano un pugno di mosche invece dei dati del proprio sito WordPress.

Il Database di WordPress: guida completa