Resumo: neste tutorial você aprenderá como otimizar consultas que envolvem a condição IS NULL.
Configurando estrutura e índice da tabela
Antes de otimizar consultas que envolvem IS NULL, você precisa ter um índice na coluna que pretende usar com a condição IS NULL porque a indexação pode melhorar significativamente o desempenho da consulta.
Primeiro, crie uma tabela chamada persons
com três colunas id
, name
e age
:
CREATE TABLE persons (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
INDEX age_index (age)
);
A persons
tabela possui um índice na coluna idade com o nome age_index
.
Segundo, insira algumas linhas na persons
tabela:
INSERT INTO persons (id, name, age)
VALUES
(1, 'John Doe', 30),
(2, 'Jane Smith', NULL),
(3, 'Michael Johnson', 35),
(4, 'Sarah Williams', 28),
(5, 'Robert Brown', NULL),
(6, 'Emily Davis', 29),
(7, 'David Lee', NULL),
(8, 'Olivia Clark', 24),
(9, 'James Taylor', 31),
(10, 'Emma Anderson', 27);
Linguagem de código: PHP ( php )
Exemplo de otimização MySQL IS NULL
A consulta a seguir recupera linhas da persons
tabela com os valores na age
coluna são NULL
:
SELECT * FROM persons
WHERE age IS NULL;
Linguagem de código: PHP ( php )
Saída:
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 2 | Jane Smith | NULL |
| 5 | Robert Brown | NULL |
| 7 | David Lee | NULL |
+----+--------------+------+
3 rows in set (0.02 sec)
Linguagem de código: PHP ( php )
O MySQL usa o índice quando pesquisa NULL
com o IS NULL
operador conforme mostrado na seguinte consulta EXPLAIN:
EXPLAIN
SELECT
*
FROM
persons
WHERE
age IS NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | persons | NULL | ref | age_index | age_index | 5 | const | 3 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)
Linguagem de código: JavaScript ( javascript )
O MySQL também pode otimizar para a combinação col = value OR col IS NULL
, veja o exemplo a seguir:
EXPLAIN
SELECT
*
FROM
persons
WHERE
age = 12
OR age IS NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | persons | NULL | ref_or_null | age_index | age_index | 5 | const | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
Linguagem de código: JavaScript ( javascript )
Neste exemplo, EXPLAIN
mostra ref_or_null
quando a otimização é aplicada.
Às vezes, o MySQL pode não usar índices de forma eficaz com OR
condições complexas. Por exemplo:
EXPLAIN
SELECT
*
FROM
persons
WHERE
name = "Jane Smith"
OR age IS NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, o MySQL não conseguiu aproveitar o índice:
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | persons | NULL | ALL | age_index | NULL | NULL | NULL | 10 | 19.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Linguagem de código: PHP ( php )
Resumo
- O MySQL procura um índice na coluna que usa IS NULL para otimizar a consulta.