Lembrei-me de partilhar um teste que fiz há tempos. O que se pretendia era demonstrar que podemos percorrer uma tabela MyISAM (e só neste caso específico) de forma
controlada (ie, prever a ordem pela qual os registos são devolvidos) e se esse percurso se mantém em outras operações para além do SELECT, como no caso do DELETE. É fácil pensar numa aplicação: se eu quiser, por exemplo, transladar blocos de registos de uma tabela para outra, torna-se fundamental que a operação DELETE também obedeça ao expectável já que, como sabemos, o MyISAM não é transaccional e, se alguma coisa falhar, queremos poder ter acesso aos registos desta forma determinística para saber o que reverter.
Para alguns pode parecer óbvio, mas sem olhar para o código nunca vamos ter a certeza. Para além disso, o código pode mudar, por isso mais vale termos a certeza :-) Não vamos sequer tentar extrapolar as conclusões para InnoDB porque internamente trabalha de forma completamente diferente. Aliás, um único aspecto da sua arquitectura - o famoso
clustered index, que pode ou não ser interno, mas existe sempre! - dá logo para desconfiar que o comportamento seja completamente diferente.
Portanto, na prática, o que se pretende mesmo é ter a certeza que sabemos que registos vão surgindo em várias iterações, e se essa certeza se extrapola para DELETEs (e, eventualmente, para UPDATEs) - ie, tornar o nosso processo determinístico.
Vamos começar com uma tabela simples:
[mysql]
CREATE TABLE `teste` (
`_int` int(11) NOT NULL DEFAULT '0',
`_char` varchar(5) NOT NULL DEFAULT '',
KEY `idx_int` (`_int`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[/mysql]
E inserimos alguns dados:
[mysql]
mysql> INSERT INTO teste VALUES (2,'c'), (1,'e'), (1,'b'), (1,'z'), (2,'b'), (2,'d'),(3,'m');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_NO_CACHE * FROM teste;
+------+-------+
| _int | _char |
+------+-------+
| 2 | c |
| 1 | e |
| 1 | b |
| 1 | z |
| 2 | b |
| 2 | d |
| 3 | m |
+------+-------+
7 rows in set (0.00 sec)
[/mysql]
A ordem pela qual foram inseridos os registos é fundamental. Podemos observar que este
table scan é feito de forma natural, também segundo o Query Optimizer:
[mysql]
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | teste | ALL | NULL | NULL | NULL | NULL | 7 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
[/mysql]
Perfeito. Estou a pedir os campos todos de cada linha, onde se inclui
_char, que não é indexado, e o Optimizer comporta-se como suposto. Mas agora vejamos uma query ligeiramente diferente:
[mysql]
mysql> EXPLAIN SELECT SQL_NO_CACHE _int FROM teste;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | teste | index | NULL | idx_int | 4 | NULL | 7 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
[/mysql]
É interessante como o Optimizer reconheceu que, se eu só quero um campo que por acaso até está indexado, então posso obtê-lo directamente do índice (e é isso que significa
Using index no campo
Extra) evitando ir aos datafiles. Porém isto significa que iremos obter os registos pela ordem do índice:
[mysql]
mysql> SELECT SQL_NO_CACHE _int FROM teste;
+------+
| _int |
+------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
+------+
7 rows in set (0.00 sec)
[/mysql]
Isto é mais importante que o que possa parecer para este teste. Se eu for obrigado a requisitar mais campos do que esse, o Optimizer vai voltar ao
table scan. E não basta colocar um ORDER BY...
[mysql]
mysql> explain SELECT SQL_NO_CACHE * FROM teste ORDER BY _int;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | teste | ALL | NULL | NULL | NULL | NULL | 7 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
[/mysql]
... porque o Optimizer pode querer usar uma tabela temporária para fazer a ordenação, que é que nos diz
Using filesort no campo
Extra. Isto pode parecer uma falha do Optimizer, mas a verdade é que o Optimizer é inteligente o suficiente para determinar que um ''full table scan'' pode ser mais eficiente que percorrer o índice por uma ordem e ir buscar os restantes dados aos data files com localização (nos discos) completamente desordenada, provavelmente aleatória (o I/O manifestar-se-ia imediatamente) - claro que nesta tabela talvez não seja o caso, mas para tabelas muito grandes pode ser desastroso. Assim sendo, teríamos que forçar explicitamente a utilização do índice, já que, pelo menos no meu caso, nem a pista USE INDEX ajudava:
[mysql]
mysql> explain SELECT SQL_NO_CACHE * FROM teste FORCE INDEX(idx_int) ORDER BY _int;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | teste | index | NULL | idx_int | 4 | NULL | 7 | |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
[/mysql]
De facto, o Optimizer é tão teimoso que mesmo forçando a utilização do índice ele descarta-o se não usarmos o ORDER BY, pois sabe que, para um
table scan a ordem dos registos é indiferente e, como tal, não precisa do índice para nada. Deve haver uma explicação para este comportamento - que vou ter que pesquisar - mas este comportamento interessa-nos e muito: se o Optimizer pegasse ao acaso um índice que
lhe parecesse bem, seria difícil obter os registos pela ordem natural sem testar com um EXPLAIN primeiro. Parece-me interessante salientar o seguinte:
[mysql]
mysql> SELECT SQL_NO_CACHE * FROM teste FORCE INDEX(idx_int) ORDER BY _int;
+------+-------+
| _int | _char |
+------+-------+
| 1 | e |
| 1 | b |
| 1 | z |
| 2 | c |
| 2 | b |
| 2 | d |
| 3 | m |
+------+-------+
7 rows in set (0.00 sec)
[/mysql]
Ou seja, dentro do índice, quando há colisões, elas são simplesmente adicionadas no fim. Isto significa que, após a ordenação, a ordem pela qual obtemos os registos é... a ordem natural.
Mas pronto, agora sim, podemos assumir, para já, que se percorrermos a tabela com SELECT ... LIMIT 1, podemos ir obtendo registo a registo quer pela ordem natural, quer pela ordem do índice que quisermos. Mas o meu grande problema era na remoção, pois não temos EXPLAIN para o DELETE. Qual dos dois métodos o DELETE utiliza?
[mysql]
mysql> DELETE FROM teste LIMIT 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT SQL_NO_CACHE * FROM teste;
+------+-------+
| _int | _char |
+------+-------+
| 1 | e |
| 1 | b |
| 1 | z |
| 2 | b |
| 2 | d |
| 3 | m |
+------+-------+
6 rows in set (0.00 sec)
[/mysql]
Bem, para já, parece ser a ordem natural. Claro que se eu especificar um
ORDER BY _int o próximo registo a apagar deveria ser (1,e) - porque é o primeiro no índice idx_int, e sabemos nós que o valor no campo _char será o da ordem natural - resta saber se o Optimizer não pensa que precisa duma tabela temporária para essa ordenação. Eu estou convencido que não, pois como não há selecção de nenhum campo específico, não há porque não utilizar o índice
idx_int;. Vamos só confirmar:
[mysql]
mysql> DELETE FROM teste ORDER BY _int LIMIT 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT SQL_NO_CACHE * FROM teste;
+------+-------+
| _int | _char |
+------+-------+
| 1 | b |
| 1 | z |
| 2 | b |
| 2 | d |
| 3 | m |
+------+-------+
5 rows in set (0.00 sec)
[/mysql]
Tudo bem, conforme previsto. Mas há mais. Raramente há tabelas só com um índice e pode acontecer que o nosso campo
_char fosse apanhado por um índice, o que tornaria as coisas um pouco diferentes:
[mysql]
mysql> ALTER TABLE teste ADD KEY idx_char(_char);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | teste | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
[/mysql]
Acho interessante porque é como se o Optimizer «
em caso de dúvida, optasse por nenhum», ou seja, como desconhece um critério para escolher um ou outro índice, não usa nenhum.
Na verdade, o Optimizer não usa nenhum índice porque não tem nenhuma pista sobre qual agarrar. Por norma irá utilizar o de maior cardinalidade, para as pistas que tiver disponíveis:
[mysql]
mysql> OPTIMIZE TABLE teste;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text
+------------+----------+----------+----------+
| test.teste | OPTIMIZE | status | OK
+------------+----------+----------+----------+
1 row in SET (0.00 sec)
mysql> SHOW INDEXES FROM teste;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| teste | 1 | idx_int | 1 | _int | A | 2 | NULL | NULL | | BTREE |
| teste | 1 | idx_char | 1 | _char | A | 5 | NULL | NULL | | BTREE |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in SET (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste WHERE _int = 1 AND _char = 'e';
+----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | teste | ref | idx_int,idx_char | idx_char | 7 | const | 1 | USING WHERE
+----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
1 row in SET (0.00 sec)
[/mysql]
Ou seja, idx_char é utilizado para a filtragem e, como tinha potencial para filtrar mais registos, é esse índice o escolhido pelo Optimizer, que nos diz ainda que vai precisar de percorrer os datafiles para filtrar o campo
idx_int (Using where).
Eu sei que o * é expandido para os campos todos pelo Optimizer; então e se houvesse um
covering index?
[mysql]
mysql> ALTER TABLE teste ADD KEY idx_int_char(_int,_char);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 WARNINGS: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | teste | index | NULL | idx_int_char | 11 | NULL | 5 | USING index
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in SET (0.00 sec)
[/mysql]
Como era de esperar, o índice será usado. Vamos ver o que acontece com o DELETE. Para testar, forçamos os registos a mudar a posição no índice (mantendo a ordem natural) e vamos criar outra tabela similar, com os mesmos registos, porque vai-nos interessar esta ordem mais abaixo:
[mysql]
mysql> UPDATE teste SET _int = 4 WHERE _int = 1 AND _char = 'b'; UPDATE teste SET _int = 5, _char = 'f' WHERE _int = 2 AND _char = 'b';
mysql> create table teste3 like teste;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into teste3 select * from teste IGNORE INDEX(idx_int_char);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from teste3 IGNORE INDEX(idx_int_char);
+------+-------+
| _int | _char |
+------+-------+
| 4 | b |
| 1 | z |
| 5 | f |
| 2 | d |
| 3 | m |
+------+-------+
5 rows in set (0.00 sec)
mysql> DELETE FROM teste3 LIMIT 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from teste3 IGNORE INDEX(idx_int_char);
+------+-------+
| _int | _char |
+------+-------+
| 1 | z |
| 5 | f |
| 2 | d |
| 3 | m |
+------+-------+
4 rows in set (0.00 sec)
[/mysql]
Ou seja, com o SELECT antes e depois temos oportunidade de comprovar que o DELETE não pegou nenhum índice, nem mesmo o
covering index.
Mas voltando atrás, à tabela
teste, será que o facto de se usar uma PRIMARY KEY (que é única e não nula) influencia os resultados? Esta pergunta provavelmente só será pertinente para quem conheça o InnoDB.
[mysql]
mysql> ALTER TABLE teste DROP KEY idx_int;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE teste ADD PRIMARY KEY(_int);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | teste | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
[/mysql]
Muito bem, nada a assinalar.
Mas nós também sabemos o que acontece com o MyISAM: após os DELETEs começam a surgir buracos nos data files (que, consequentemente, acabam por interferir com as
inserções em concorrência). Vejamos. Recriei a tabela teste com o dataset original, e fiz os dois DELETE que fizémos até aqui. A seguir:
[mysql]
mysql> SELECT @@concurrent_insert;
+---------------------+
| @@concurrent_insert
+---------------------+
| 1
+---------------------+
1 row in SET (0.00 sec)
mysql> INSERT INTO teste VALUES (6,'e'),(5,'f'),(4,'g');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 WARNINGS: 0
mysql> SELECT SQL_NO_CACHE * FROM teste;
+------+-------+
| _int | _char |
+------+-------+
| 5 | f |
| 6 | e |
| 1 | b |
| 1 | z |
| 2 | b |
| 2 | d |
| 3 | m |
| 4 | g |
+------+-------+
8 rows in SET (0.00 sec)
[/mysql]
Repare-se que os dois primeiros registos ficaram fora da ordem natural pois o MyISAM conseguiu
reciclar o espaço livre, exactamente nos mesmos sítios. O terceiro elemento calhou no único sítio onde cabia: no fim. Esta também é uma observação importante porque se houverem escritas na tabela durante as operações, então é preciso um cuidado especial. Este cuidado agrava-se pelo facto de nem conseguirmos desactivar este comportamento. Recrie-se novamente a tabela teste, com o INSERT inicial, depois os dois DELETEs feitos até agora, e depois o INSERT do teste anterior:
[mysql]
mysql> SET global concurrent_insert = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO teste VALUES (6,'e'),(5,'f'),(4,'g');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 WARNINGS: 0
mysql> SELECT SQL_NO_CACHE * FROM teste;
+------+-------+
| _int | _char |
+------+-------+
| 5 | f |
| 6 | e |
| 1 | b |
| 1 | z |
| 2 | b |
| 2 | d |
| 3 | m |
| 4 | g |
+------+-------+
8 rows in SET (0.00 sec)
[/mysql]
Exactamente o mesmo. Perde-se, portanto, a ordem natural.
Nota sobre o InnoDBA título de nota, deixa-se uma breve explicação do porquê de não analisarmos o caso do InnoDB:
[mysql]
mysql> CREATE TABLE teste2 like teste;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO teste2 SELECT * FROM teste;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 WARNINGS: 0
mysql> UPDATE teste2 SET _char = 'k' WHERE _int = 1 and _char = 'b';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 WARNINGS: 0
mysql> ALTER TABLE teste2 engine=INNODB, DROP INDEX idx_int, DROP INDEX idx_char, DROP INDEX idx_int_char;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 WARNINGS: 0
mysql> alter table teste2 add primary key (_char);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste2;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | teste2 | ALL | NULL | NULL | NULL | NULL | 5 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in SET (0.00 sec)
mysql> SELECT * FROM teste2;
+------+-------+
| _int | _char |
+------+-------+
| 2 | b |
| 2 | d |
| 1 | k |
| 3 | m |
| 1 | z |
+------+-------+
5 rows in SET (0.00 sec)
[/mysql]
O que queria que reparassem é que não há indicação de estar a ser utilizada nenhuma chave e, no entanto, os registos vêm ordenados pela PRIMARY KEY. Como disse logo no início, com InnoDB os resultados seriam diferentes, e isto deve-se ao
clustered index deste
storage engine, que armazena os registos (fiscamente nos datafiles) pela ordem da chave (a famosa
primary key order). Como consequência, não é possível ter acesso aos registos na forma natural (ie, pela ordem em que foram inseridos) - e, de forma consistente com o que tínhamos visto para MyISAM, o Optimizer prefere não usar nenhum índice, passando para o storage engine a mesma query expandida.
Penso que consegui demonstrar com alguma segurança que podemos obter registos com SELECT de uma forma controlada. Dependendo do cenário, nem sempre se torna possível usar um ou outro método (também pode não ser possível criar um novo índice, por exemplo) pelo que antes de tirar conclusões precipitadas, o melhor é usar o EXPLAIN para explorar as possibilidades!
