Otimização MySQL É NULL

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 personscom três colunas id, namee age:

CREATE TABLE persons (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX age_index (age)
);

A personstabela possui um índice na coluna idade com o nome age_index.

Segundo, insira algumas linhas na personstabela:

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 personstabela com os valores na agecoluna 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 NULLcom o IS NULLoperador 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, EXPLAINmostra ref_or_nullquando a otimização é aplicada.

Às vezes, o MySQL pode não usar índices de forma eficaz com ORcondiçõ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.