Индексы в MySQL

Многие из нас используя различные технологии не утруждают себя доскональным их изучением. Почему? Например еще не все знают об индексах в MySQL и других СУБД. Наверное только потому, что в рунете мало кто об этом писал.

Что ж…. Тогда придется и мне добавить свои пять копеек к этой теме.

Я не буду детально описывать типы индексов - такие как hash и btree - об этом много писали, хоть и не относительно СУБД MySQL, но относительно алгоритмов работы индексирования вообще. Я просто покажу действие индексов на практике.

На одном из форумов увидел вопрос - ответом и стала эта статья об использовании индексов в MySQL

mysql> use test;

Database changed
mysql> create table big1 (id int(16) unsigned primary key auto_increment, fig int(1) unsigned default 0) engine=myisam;
Query OK, 0 rows affected (0.14 sec)

mysql>

Думаю что такого кол-ва записей достаточно:

mysql> select count(*) from big1 ;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (0.00 sec)

поля fig во всей таблице заполнены нулями…

mysql> select count(*) from big1 where fig=0;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (10.90 sec)

mysql> select count(*) from big1 where fig=0;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from big1 where fig=0;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (0.00 sec)

Делаем вывод - MySQL кэширует результаты? (буду благодарен за разъяснения)

mysql> select count(*) from big1 where fig!=0;

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (4.96 sec)

mysql> select count(*) from big1 where fig!=0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Видимо кэширует…

mysql> select count(*) from big1 where fig>0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1.01 sec)

mysql> select count(*) from big1 where fig>6;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.94 sec)

mysql> select count(*) from big1 where fig>689;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.95 sec)

При изменении параметров фокус с кэшированием не проходит, значит идем дальше!

mysql> create index myindex using hash on big1 (fig);
Query OK, 7132484 rows affected (1 min 20.71 sec)
Records: 7132484  Duplicates: 0  Warnings: 0

mysql>  

Ну очень долго…

И в это время mysqld съедает все процессорное время, но если бы индекс создавался на пустой таблице - просто вставка новой записи занимала бы чуть больше времени, чем вставка без индекса. Это происходит потому что индекс обновляется при вставке или обновлении записей в таблице.

Следует помнить, что операция выборки используется гораздо чаще , чем операция вставки - так что временем вставки можно пожертвовать.

ЗАТО ТЕПЕРЬ…

mysql> select count(*) from big1 where fig<324;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (7.27 sec)

mysql> select count(*) from big1 where fig>8324;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from big1 where fig>83;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from big1 where fig<83;
+----------+
| count(*) |
+----------+
|  7132484 |
+----------+
1 row in set (7.27 sec)

Становится понятно, что время запроса напрямую зависит от кол-ва результирующих записей.

Вывод: расставляйте индексы!