Влияние типа поля на быстродействие индекса. MySQL?

Question

[terloger]

Есть таблица:

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `url` text NOT NULL,
  `status` enum('edit','order','place', 'user') DEFAULT 'edit',
  PRIMARY KEY (`id`),
  KEY `ix__status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION articles_p1 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION articles_p2 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION articles_p3 VALUES LESS THAN (150000) ENGINE = InnoDB,
 PARTITION articles_p4 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION articles_p5 VALUES LESS THAN (250000) ENGINE = InnoDB,
 PARTITION articles_p6 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION articles_p7 VALUES LESS THAN (350000) ENGINE = InnoDB,
 PARTITION articles_p8 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION articles_p9 VALUES LESS THAN (450000) ENGINE = InnoDB,
 PARTITION articles_p10 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION articles_p11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Таблица очень жирная, общий объем >10Gb. В ходе тестов тип поля url был заменен на varchar(8000). После этого запросы типа

SELECT COUNT(*) AS `count` FROM `articles`  WHERE `status` NOT IN ('edit', 'order');

стали выполняться очень медленно (около 8 сек). План выполнения:

'1', 'SIMPLE', 'Article', 'index', 'status', 'status', '2', NULL, '1229613', 'Using where; Using index'

Но в этих статусах ('edit', 'order') менее 1% всех записей, т.е. индекс в данном случае не эффективен, но, если вернуть тип поля опять на TEXT время выполнения этого же запроса уменьшается до 0.5 секунды.

План выполнения при этом не меняется.


В чем причина такого поведения innodb?

Comments

[ShouldNotSeeMe]

Интересно взглянуть на my.cnf.

Answer 1

[egorinsk]

Подозреваю, дело в том, что VARCHAR и TEXT данные хранятся разным способом:

> For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer.

dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Comments

[egorinsk]

То есть VARCHAR хранится физически там же где и остальные поля записи, а TEXT — отдельно, и с VARCHAR у вас запись занимает порядка 24 Кб, а с TEXT — запись занимает сотню байт.

[terloger]

да, пока это действительно самое реальное объяснение о том, что происходит

Answer 2

[shagguboy]

SET profiling = 1;
ваш запрос
SHOW PROFILES;
сделать для обоих вариантов таблицы, выложить сюда.

Comments

[shagguboy]

SET profiling = 1;
ваш запрос
SHOW PROFILE; — без S
извиняюсь

[terloger]

Вариант с VARCHAR:

'starting', '0.000013'
'checking query cache for query', '0.000061'
'checking permissions', '0.000006'
'Opening tables', '0.000013'
'System lock', '0.000004'
'Table lock', '0.000026'
'init', '0.000018'
'optimizing', '0.000009'
'statistics', '0.000032'
'preparing', '0.000007'
'executing', '0.000003'
'Sending data', '8.968607'
'end', '0.000032'
'query end', '0.000002'
'freeing items', '0.000043'
'storing result in query cache', '0.000005'
'logging slow query', '0.000002'
'cleaning up', '0.000002'

Вариант с TEXT:

'starting', '0.000009'
'checking query cache for query', '0.000043'
'Opening tables', '0.000009'
'System lock', '0.000004'
'Table lock', '0.000017'
'init', '0.000017'
'optimizing', '0.000009'
'statistics', '0.000021'
'preparing', '0.000007'
'executing', '0.000003'
'Sending data', '0.484201'
'end', '0.000007'
'query end', '0.000002'
'freeing items', '0.000097'
'storing result in query cache', '0.000005'
'logging slow query', '0.000001'
'cleaning up', '0.000002'

[shagguboy]

Sending data

dev.mysql.com/doc/refman/5.1/en/general-thread-states.html
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

покажите статистику по чтениям для обоих запросов.

[terloger]

«покажите статистику по чтениям для обоих запросов»

что вы имеете в виду?

[shagguboy]

logical reads и physical reads
dba.stackexchange.com/a/4102

[shagguboy]

dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
Innodb_buffer_pool_read_requests
The number of logical read requests InnoDB has done. Added in MySQL 5.0.2.

[terloger]

спасибо, будем курить маны.

Answer 3

[Melkij]

Но в этих статусах ('edit', 'order') менее 1% всех записей, т.е. индекс в данном случае не эффективен
Таки наоборот. Чем меньше записей от общей массы подпадает — тем эффективнее индекс.

Попробуйте с count(0), т.е. по константе. Есть мнение, что mysql вычитывает все подошедшие строки, а text'ы — не читает, т.к. расположены отдельно от таблицы.

Comments

[terloger]

COUNT(0) не помогает, время выполнения тоже самое.

Answer 4

[TimTowdy]

Покажите EXPLAIN запроса по таблице без VARCHAR(8000). Или попробуйте FORCE INDEX на этой таблице. Есть подозрение что индекс не используется, т.к. фулскан быстрее. Замена TEXT на VARCHAR(8000) замедляет фулскан и включается индекс, что и приводит к замедлению.

Comments

[terloger]

план приведен в тексте, в нем Using where; Using index. Ни FORCE INDEX не USE INDEX ситуацию не меняют. Обратите внимание, что в запросе NOT IN, т.е. считаются 99% записей таблицы

Answer 5

[shagguboy]

как успехи?