Uma cena muito comum é esta: logo ao lançar a primeira versão de uma nova aplicação (ou de uma nova feature), ela funcionava como um tiro, respondia muito rápido. Depois de um tempo, com o aumento do uso dessa feature, você começa a perceber no seu software de APM (ou pior, os usuários começam a reclamar) que o tempo de resposta aumentou muito. Pesquisa mais um pouco e descobre que a culpada é uma query que está levando muito tempo para executar.
Uma das formas de resolver o problema de performance da query é criando um ou mais índices. Mas a pergunta que fica é:
Qual índice criar para otimizar minha query?
A primeira tentação é criar todos os índices que possam estar envolvidos na query, mas em muitos casos isso, além de não resolver o problema, acaba introduzindo outros: a diminuição de performance nas operações de DML (INSERT, UPDATE e DELETE em SQL, insertOne/insertMany, updateOne/updateMany, deleteOne/deleteMany no MongoDB), uma vez que, além de alterar a tabela/collection, é necessário atualizar os índices da mesma; e o desperdício de espaço em disco, já que o índice estaria ocupando espaço sem necessidade.
Tá, então como eu descubro quais índices criar?
A melhor forma de analisar uma query para saber quais índices criar é através da análise dos filtros da query (cláusulas WHERE em SQL, o primeiro parâmetro do método find() do MongoDB ou o argumento $match do método aggregate() do MongoDB) o do seu plano de execução. Com essas informações em mãos, você consegue saber quais colunas estão sendo filtradas e quantas linhas estão sendo analisadas.
Para consultar o plano de execução de uma query, execute um dos comandos abaixo, de acordo com o banco de dados utilizado:
-- MySQL e PostgreSQL:
EXPLAIN [sua query]
-- MySQL com saída em formato JSON (contém informações adicionais)
EXPLAIN FORMAT=json [sua query]
-- Oracle:
EXPLAIN PLAN FOR [sua query]
-- SQL Server:
SET SHOWPLAN_XML ON;
GO
[sua query]
GO
SET SHOWPLAN_XML OFF;
GO
-- MongoDB:
db.[nome da collection].find({[filtros]}).explain()
-- Exemplo em MySQL/Postgres:
EXPLAIN SELECT * FROM cidades WHERE uf = 'ES';
-- Exemplo em MongoDB:
db.cidades.find({ "uf": "ES" }).explain()
Suponha, por exemplo, a tabela pedidos, que possui 100.000 linhas e a seguinte estrutura (banco de dados MySQL 5.7):
mysql> desc pedidos; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | id_cliente | int(11) | NO | | NULL | | | status | varchar(20) | NO | MUL | NULL | | | valor | decimal(7,2) | NO | | NULL | | +------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
A coluna status possui 2 valores: ok (97% dos registros) e cancelado (3% dos registros).
A query que está com lentidão é essa:
SELECT * FROM pedidos WHERE status = 'cancelado';
Para analisar o plano de execução da query, rodamos o comando abaixo e recebemos o seguinte plano de execução:
mysql> EXPLAIN FORMAT=json SELECT * FROM pedidos WHERE status = 'cancelado';
+---------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------+
{
"query_block": {
"select_id": 1,
"cost_info": { "query_cost": "19746.00" },
"table": {
"table_name": "pedidos",
"access_type": "ALL",
"rows_examined_per_scan": 97605,
"rows_produced_per_join": 9760,
"filtered": "10.00",
"cost_info": {
"read_cost": "17793.90",
"eval_cost": "1952.10",
"prefix_cost": "19746.00",
"data_read_per_join": "381K"
},
"used_columns": [ "id", "id_cliente", "status", "valor" ],
"attached_condition": "(`teste`.`pedidos`.`status` = 'cancelado')"
}
}
}
+---------------------------------------------------------------------------+
O valor ALL no campo access_type indica que não foi utilizado um índice e o campo rows_examined_per_scan indica que serão consultadas aproximadamente 97.605 linhas (é uma estimativa, não o valor real). O campo attached_condition indica o filtro utilizado na tabela (teste.pedidos.status = ‘cancelado’).
A partir desses dados, descobrimos que o índice deve ser criado na coluna status da tabela:
mysql> create index idx_pedidos_status on pedidos (status); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
Executando novamente o plano de execução da query, temos o seguinte resultado:
mysql> EXPLAIN FORMAT=json SELECT * FROM pedidos WHERE status = 'cancelado';
+---------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------+
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1275.00"
},
"table": {
"table_name": "pedidos",
"access_type": "ref",
"possible_keys": [
"idx_pedidos_status"
],
"key": "idx_pedidos_status",
"used_key_parts": [
"status"
],
"key_length": "22",
"ref": [
"const"
],
"rows_examined_per_scan": 3000,
"rows_produced_per_join": 3000,
"filtered": "100.00",
"cost_info": {
"read_cost": "675.00",
"eval_cost": "600.00",
"prefix_cost": "1275.00",
"data_read_per_join": "117K"
},
"used_columns": [
"id",
"id_cliente",
"status",
"valor"
]
}
}
}
+---------------------------------------------------------------------------+
No exemplo acima, podemos ver no campo key que o índice idx_pedidos_status que acabamos de criar foi utilizado e que a quantidade de linhas percorridas (rows_examined_per_scan)reduziu de 97605 para 3000 (97% de redução), permitindo que a leitura desses dados seja consideravelmente mais rápida.
Nos próximos artigos eu falarei de estatísticas de tabelas e outras funcionalidades dos bancos de dados que afetam o uso de índices. Até lá!