Показатель подсчета Mysql на очень больших таблицах

У меня есть таблица с более чем 100 миллионами строк в Innodb.

Я должен знать, есть ли более 5000 строк, где внешний ключ = 1. Мне не нужен точный номер.

Я провел несколько тестов:

SELECT COUNT(*) FROM table WHERE fk = 1 = > 16 секунд
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 = > 16 секунд
SELECT primary FROM table WHERE fk = 1 = > 0,6 секунды

У меня будет большая сеть и время обработки, но это может быть перегрузка в 15,4 секунды!

У вас есть идея?

Спасибо

Изменить: [Добавлены соответствующие релевантные комментарии]

Я попробовал SELECT SQL_NO_CACHE COUNT (fk) FROM table WHERE fk = 1, но потребовалось 25 секунд

Mysql был настроен для Innodb с тюнером Mysql.

CREATE TABLE table ( pk bigint(20) NOT NULL AUTO_INCREMENT,
fk tinyint(3) unsigned DEFAULT '0', 
PRIMARY KEY (pk), KEY idx_fk (fk) USING BTREE ) 
ENGINE=InnoDB AUTO_INCREMENT=100380914 DEFAULT CHARSET=latin1

Содержимое базы данных:

'have_innodb', 'YES' 'ignore_builtin_innodb', 'OFF' 'innodb_adaptive_hash_index', 'ON'    
'innodb_additional_mem_pool_size', '20971520' 'innodb_autoextend_increment', '8' 
'innodb_autoinc_lock_mode', '1' 'innodb_buffer_pool_size', '25769803776' 
'innodb_checksums', 'ON' 'innodb_commit_concurrency', '0',
'innodb_concurrency_tickets', '500' 'innodb_data_file_path',
'ibdata1:10M:autoextend' 'innodb_data_home_dir', '', 'innodb_doublewrite', 'ON'     
'innodb_fast_shutdown', '1' 'innodb_file_io_threads', '4' 
'innodb_file_per_table', 'OFF', 'innodb_flush_log_at_trx_commit', '1' 
'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 
'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_buffer_size', '8388608' 
'innodb_log_file_size', '26214400' 'innodb_log_files_in_group', '2' 
'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90'     
'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', 
'300' 'innodb_rollback_on_timeout', 'OFF' 'innodb_stats_on_metadata', 'ON' 
'innodb_support_xa', 'ON' 'innodb_sync_spin_loops', '20' 'innodb_table_locks', 'ON' 
'innodb_thread_concurrency', '8' 'innodb_thread_sleep_delay', '10000'      
'innodb_use_legacy_cardinality_algorithm', 'ON'

Обновить '15: Я использовал тот же метод до сих пор с 600 миллионами строк и 640 000 новых строк в день. Он все еще работает нормально.

Ответ 1

Наконец, самым быстрым был запрос первых X строк с использованием С# и подсчет числа строк.

Мое приложение обрабатывает данные партиями. Количество времени между двумя партиями зависит от количества строк, которые нужно обрабатывать.

SELECT pk FROM table WHERE fk = 1 LIMIT X

Я получил результат через 0,9 секунды.

Спасибо всем за ваши идеи!

Ответ 2

Таблицы счетчиков или другой механизм кеширования - это решение:

InnoDB не сохраняет внутренний счет строк в таблице, потому что одновременные транзакции могут "видеть" разные числа строк одновременно. Чтобы обработать инструкцию SELECT COUNT (*) FROM t, InnoDB сканирует индекс таблицы, что занимает некоторое время, если индекс не полностью находится в пуле буферов. Если ваша таблица не меняется часто, использование кеша запросов MySQL является хорошим решением. Чтобы получить быстрый счет, вам нужно использовать таблицу счетчиков, которую вы создаете, и позволить вашему приложению обновлять ее в соответствии с вставками и удаляет ее. Если приблизительное количество строк достаточно, можно использовать SHOW TABLE STATUS. См. Раздел 14.3.14.1, "Советы по настройке производительности InnoDB" .

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

Ответ 3

Вы не заинтересованы в фактическом подсчете, поэтому попробуйте:

SELECT 1 FROM table WHERE fk = 1 LIMIT 5000, 1

Если строка возвращается, у вас есть 5000 и более записей. Я предполагаю, что столбец fk индексируется.

Ответ 4

Я должен добавить еще один ответ. У меня есть много исправлений/дополнений к комментариям и ответам.

Для MyISAM SELECT COUNT(*) без WHERE считается мертвым - очень быстро. Все остальные ситуации (включая InnoDB в Вопросе) должны учитывать либо данные BTree, либо индекс BTree для получения ответа. Таким образом, нам нужно увидеть, сколько можно пересчитать.

InnoDB кэширует данные и блоки индексов (по 16 КБ). Но когда данные таблицы или индекс BTree больше, чем innodb_buffer_pool_size, вы гарантированно попадете на диск. Нажатие на диск почти всегда является самой медленной частью любого SQL.

Кэш запросов, когда он задействован, обычно приводит к времени запроса около 1 миллисекунды; это, по-видимому, не проблема с какими-либо указанными таймингами. Поэтому я не буду останавливаться на этом.

Но... Часто повторяется тот же запрос дважды в строке:

Первый запуск: 10 секунд
Второй запуск: 1 секунда

Это симптоматично для первого запуска, который должен извлечь большинство блоков с диска, а второй нашел все в ОЗУ (buffer_pool). Я подозреваю, что некоторые из перечисленных таймингов являются фиктивными из-за того, что они не понимают эту проблему кэширования. (16 сек. Против 0,6 с может быть объяснено этим).

Я буду описывать "образы дисков" или "блоки, которые нужно трогать", как реальную метрику, из которой SQL быстрее.

COUNT(x) проверяет x на IS NOT NULL перед подсчетом. Это добавляет крошечный объем обработки, но не меняет количество обращений к диску.

В предлагаемой таблице есть ПК и второй столбец. Интересно, что это реальная таблица? Это имеет значение -

Если Оптимизатор решает прочитать данные - то есть сканировать в порядке PRIMARY KEY - он будет считывать данные BTree, которые обычно (но не в этом хромом примере) намного шире, чем вторичный индекс BTrees.
Если Оптимизатор решает прочитать вторичный индекс (но не нужно делать сортировку), будет меньше блоков для касания. Следовательно, быстрее.

Комментарии к исходным запросам:

SELECT COUNT(*) FROM table WHERE fk = 1 => 16 seconds
    -- INDEX(fk) is optimal, but see below
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16 seconds
    -- the LIMIT does nothing, since there is only one row in the result
SELECT primary FROM table WHERE fk = 1 => 0.6 seconds
    -- Again INDEX(fk), but see below

WHERE fk = 1 запрашивает INDEX(fk, ...), желательно просто INDEX(fk). Обратите внимание, что в InnoDB каждый вторичный индекс содержит копию pk. То есть INDEX(fk) эффективно INDEX(fk, primary). Следовательно, третий запрос может использовать это как "покрытие" и не должен касаться данных.

Если таблица действительно является только двумя столбцами, то, вероятно, вторичный индекс BTree будет более толстым, чем данные BTree. Но в реалистичных таблицах вторичный индекс будет меньше. Следовательно, сканирование индексов будет быстрее (меньше блоков для касания), чем сканирование таблицы.

Третий запрос также предоставляет большой набор результатов; это может привести к тому, что запрос займет много времени - но он не будет включен в указанное время; это время сети, а не время запроса.

innodb_buffer_pool_size = 25,769,803,776 Я бы предположил, что таблица и ее вторичный индекс (от FK) составляют примерно 3-4 ГБ. Таким образом, любой момент времени может потребоваться для загрузки большого количества материала. Тогда второй запуск будет полностью кэширован. (Конечно, я не знаю, сколько строк имеет fk=1, предположительно меньше всех строк?)

Но... В столбцах 600M таблица и ее индекс приближаются к 25GB buffer_pool. Таким образом, скоро может наступить день, когда он станет привязанным к I/O - это заставит вас вернуться к 16 (или 25) секундам; но вы не сможете. Затем мы можем поговорить об альтернативах выполнению COUNT.

SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000,1 - Проанализируем это. Он будет сканировать индекс, но он остановится после 5000 строк. Из всего, что вам нужно, "больше 5K", это лучший способ получить его. Он будет последовательно быстрым (касаясь только десятка блоков), независимо от общего количества строк в таблице. (Он по-прежнему зависит от характеристик buffer_pool_size и кеширования системы. Но дюжина блоков занимает гораздо меньше секунды, даже с холодным кешем.)

MariaDB LIMIT ROWS_EXAMINED может стоить посмотреть. Без этого вы могли бы сделать

SELECT COUNT(*) AS count_if_less_than_5K
    FROM ( SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000 );

Это может быть быстрее, чем доставка строк клиенту; он должен будет собирать строки внутри таблицы tmp, но доставлять только COUNT.

Замечание: 640K строк вставлены в день - это приближается к пределу для однострочного INSERTs в MySQL с вашими текущими настройками на жестком диске (не SDD). Если вам нужно обсудить потенциальную катастрофу, откройте еще один вопрос.

Нижняя строка:

Обязательно избегайте кеша запросов. (используя SQL_NO_CACHE или выключив QC)
Запустить любой запрос синхронизации дважды; используйте второй раз.
Понимать структуру и размер задействованных BTree (s).
Не используйте COUNT(x), если вам не нужна нулевая проверка.
Не используйте интерфейс PHP mysql_*; переключитесь на mysqli_* или PDO.

Ответ 5

Если вы используете PHP, вы можете сделать mysql_num_rows по результату, полученному от SELECT primary FROM table WHERE fk = 1 => 0.6 seconds, я думаю, что это будет эффективно.

Но зависит от того, какой серверный язык вы используете

Ответ 6

Если вам не интересно знать количество строк, и вы просто хотите проверить значение COUNT на некоторое значение, вы можете использовать стандартный script ниже:

SELECT 'X'
FROM mytable
WHERE myfield='A'
HAVING COUNT(*) >5

Это приведет к возврату одной строки или никакой строки вообще, в зависимости от условия выполнения.

Этот script совместим с ANSI и может быть полностью запущен без оценки полного значения COUNT (*). Если бы MySQL реализовала оптимизацию, чтобы остановить оценку строк после выполнения какого-либо условия (я действительно надеюсь, что это так), то вы получите повышение производительности. К сожалению, я не могу проверить это поведение самостоятельно, потому что у меня нет большой базы данных MySQL. Если вы выполните этот тест, пожалуйста, поделитесь результатами здесь:)