mysql explain filtered что значит

Что говорит мне «фильтрованный» столбец в MySQL EXPLAIN, и как я могу его использовать?

Столбец filtered указывает приблизительный процент строк таблицы, которые будут отфильтрованы по условию таблицы. То есть rows показывает приблизительное количество проверенных строк, а rows × filtered / 100 показывает количество строк, которые будут объединены с предыдущими таблицами.

И это EXPLAIN план для запроса:

Примерный набор данных этой таблицы содержит 1000 строк, и для всех из них release_year установлено значение 2006. Используя формулу в документации MySQL:

rows x filtered / 100 = «количество строк, которые будут объединены с предыдущими таблицами

1,000 x 10 / 100 = 100 = «100 строк будут объединены с предыдущими таблицами»

А? Что за «предыдущая таблица»? Здесь JOIN не происходит.

Может, это странно, потому что на release_year нет индекса? Итак, я создал один:

Теперь EXPLAIN выглядит так:

И, поскольку я решил запутать себя еще дальше:

Итак, оценка 501 строки будет отфильтрована по условию таблицы и «объединена с предыдущими таблицами»?

Я просто не понимаю.

Является ли filtered вообще полезным для определения того, можно ли оптимизировать запрос дальше, или как оптимизировать его дальше, или это просто «шум», который можно игнорировать?

5 ответов

… Количество строк, которые будут объединены с предыдущими таблицами…

Я полагаю, что при отсутствии каких-либо объединений это может означать количество строк.

Взять каждый из ваших примеров по очереди

Здесь движок ожидает посещения 1000 строк и возвращает около 10% из них.

Возможно, в отсутствие дополнительной информации механизм ожидает, что любое простое условие = уменьшит результирующий набор до 10% доступных строк.

Здесь двигатель ожидает посетить 500 строк и ожидает вернуть все из них

Теперь запрос использует новый индекс, движок может делать более точные прогнозы. Он может очень быстро увидеть, что 500 строк соответствуют условию, и нужно будет только посетить и именно эти, чтобы удовлетворить запрос

Здесь двигатель ожидает посетить 1000 строк и вернуть 50,10% из них

Механизм, однако, сделал довольно точный прогноз относительно того, сколько из этих посещенных строк будет возвращено. Я не знаю, откуда берется 0,10%, но, возможно, движок использовал индекс или результаты предыдущих запросов, чтобы признать, что около 50% строк будут соответствовать условию

Это немного темное искусство, но значение filtered дает вам довольно полезную информацию и некоторое представление о том, почему движок принял определенные решения.

Если число строк большое, а оценка отфильтрованных строк низкая (и точная), это может быть хорошим показателем того, что тщательно примененный индекс может ускорить запрос

Отфильтрованный (имя JSON: отфильтрованный)

Отфильтрованный столбец показывает приблизительный процент строк таблицы, которые будут отфильтрованы по условию таблицы. Максимальное значение равно 100, что означает, что фильтрация строк не выполнялась. Значения, уменьшающиеся от 100, указывают на увеличение количества фильтрации. Строки показывают приблизительное количество проверенных строк, а строки × отфильтрованные показывают количество строк, которые будут объединены со следующей таблицей. Например, если количество строк равно 1000, а фильтрация равна 50,00 (50%), число строк, которые должны быть объединены со следующей таблицей, равно 1000 × 50% = 500.

Таким образом, вы должны написать один из них, чтобы понять, но оценка основана не на содержании, а на метаданных о содержании и статистике.

Позвольте мне привести вам конкретный пример, я не говорю, что любая платформа SQL делает то, что я описываю здесь, это просто пример:

В этом случае он вернул бы 1000 и 10.

Я считаю «отфильтрованный» столбец бесполезным.

Более точный показатель того, какая работа была проделана, может быть получен после факта через значения STATUS «Handler%». Я обсуждаю это плюс простые методы оптимизации в http://mysql.rjweb.org/doc.php / index_cookbook_mysql.

Гистограммы существуют в 8.0 и 10.0; они обеспечат большую точность. Они, вероятно, помогают сделать «фильтрованный» полезным.

Большие числа (в идеале filtered: 100.00 ) указывают, что запрос использует «хороший» индекс, иначе индекс будет бесполезным.

Рассмотрим таблицу со столбцом deleted_at TIMESTAMP NULL (мягкое удаление) без индекса, и, как 99% строк, содержат NULL (не удаляются). Теперь с запросом, как

В этом случае индекс deleted_at был бы бесполезен из-за затрат на второй поиск (поиск отфильтрованных строк в кластеризованном индексе). В худшем случае индекс может даже снизить производительность, если оптимизатор решит его использовать.

Но если вы запрашиваете «удаленные» строки с

Вы должны получить что-то вроде

Я бы сказал: что-нибудь> = 10% не стоит создавать индекс. Это по крайней мере для условий с одним столбцом.

Другая история, когда у вас есть условие для нескольких столбцов, таких как

Источник

What is the «filtered» column in MySQL EXPLAIN telling me, and how can I make use of it?

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables.

To attempt to understand this better, I tried it out on a query using the MySQL Sakila Sample Database. The table in question has the following structure:

And this is the EXPLAIN plan for the query:

This table’s sample dataset has 1,000 total rows, and all of them have release_year set to 2006. Using the formula in the MySQL documentation:

rows x filtered / 100 = «number of rows that will be joined with previous tables

1,000 x 10 / 100 = 100 = «100 rows will be joined with previous tables»

Huh? What «previous table»? There is no JOIN going on here.

Now the EXPLAIN looks like this:

And, since I decided to confuse myself even further:

So, an estimate of 501 rows will be filtered by the table condition and «joined with previous tables»?

I simply do not understand.

Is filtered at all useful in determining if a query can be optimized further, or how to optimize it further, or is it generally just «noise» that can be ignored?

5 Answers 5

…number of rows that will be joined with previous tables…

In the absence of any joins, I believe this can be taken to mean number of rows

To take each of your examples in turn

1000 rows, all from 2006, no index…

Here the engine expects to visit 1000 rows, and expects to return around 10% of these

As the query is not using an index, it makes sense to predict that every row will be checked, but unfortunately the filtered estimate is inaccurate. I don’t know how the engine makes this prediction, but as it doesn’t know all the rows are from 2006 (until it checks them).. it’s not the craziest thing in the world

Perhaps in the absence of further information, the engine expects any simple = condition to reduce the result set to 10% of the available rows

1000 rows, half from 2006, with index…

Here the engine expects to visit 500 rows and expects to return all of them

Now the query is using the new index, the engine can make more accurate predictions. It can very quickly see that 500 rows match the condition, and will have to visit only and exactly these to satisfy the query

Here the engine expects to visit 1000 rows and return 50.10% of them

It’s a bit of a dark art, but the filtered value does give you some fairly useful information, and some insight into why the engine has made certain decisions

If the number of rows is high and the filtered rows estimate is low (and accurate), it may be a good indication that a carefully applied index could speed up the query

filtered (JSON name: filtered)

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. rows shows the estimated number of rows examined and rows × filtered shows the number of rows that will be joined with the following table. For example, if rows is 1000 and filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.

High numbers (ideally filtered: 100.00 ) indicate, that the query is using a «good» index, or an index would be useless.

Consider a table with a deleted_at TIMESTAMP NULL column (soft deletion) without an index on it, and like 99% of rows contain NULL (are not deleted). Now with a query like

In this case an index on deleted_at would be useless, due to the overhead of a second lookup (finding the filtered rows in the clustered index). In worst case the index might even hurt the performance, if the optimizer decides to use it.

But if you query for «deleted» rows with

you should get something like

I would say: Anything >= 10% is not worth creating an index. That at least for single-column conditions.

A different story, is when you have a condition on multiple columns like

Источник

Какой смысл фильтровать в MySQL объяснить?

Как описано здесь в документации по MySQL :

Отфильтрованный столбец показывает приблизительный процент строк таблицы, которые будут отфильтрованы по условию таблицы. То есть, строки показывают приблизительное количество проверенных строк, а строки × отфильтрованные / 100 показывают количество строк, которые будут объединены с предыдущими таблицами. До MySQL 5.7.3 этот столбец отображается, если вы используете EXPLAIN EXTENDED. Начиная с MySQL 5.7.3 расширенный вывод включен по умолчанию, и ключевое слово EXTENDED не требуется.

Я до сих пор не понимаю. Что означает «фильтрованный» здесь? Какую информацию мы можем получить из этой колонки?

Например, когда я начинаю запрашивать, некоторые запросы показывают 100, а другие показывают 18 или меньше 100.

Какой главный пункт мы можем сделать из этого значения?

Это говорит о том, что колонка отфильтрована только на 18%? Или, чем ниже оценка, тем лучше индекс / запрос?

Я использую MySQL 5.7

Хотя, очевидно, лучше иметь 32 строки вместо 174 (если, например, вам придется join их с другой таблицей), «идеальный» индекс дал бы вам эти 32 строки непосредственно из первоначального поиска, что сэкономило бы вам время на просмотр. и отфильтровать 82% всех потенциальных строк.

Таким образом, низкое значение может указывать на то, что может быть лучший индекс: например, полное сканирование таблицы с rows=1000 и filtered=0.1% может стать поиском по индексу, rows=1 и filtered=100% если вы добавите хороший индекс.

Поэтому не принимайте это значение слишком серьезно: это не 100 означает, что ваши индексы хороши, и более низкое значение не обязательно указывает на плохие индексы. type это гораздо лучший показатель для этого.

Источник

Использование EXPLAIN. Улучшение запросов

Когда вы выполняете какой-нибудь запрос, оптимизатор запросов MySQL пытается придумать оптимальный план выполнения этого запроса. Вы можете посмотреть этот самый план используя запрос с ключевым словом EXPLAIN. EXPLAIN – это один из самых мощных инструментов, предоставленных в ваше распоряжение для понимания MySQL-запросов и их оптимизации, но печальным фактом является то, что многие разработчики редко его используют. В данной статье вы узнаете о том, какие данные предлагает EXPLAIN на выходе и ознакомитесь с примером того, как использовать его для оптимизации запросов.

Что предлагает EXPLAIN?

Использовать оператор EXPLAIN просто. Его необходимо добавлять в запросы перед оператором SELECT. Давайте проанализируем вывод, чтобы познакомиться с информацией, возвращаемой командой.

Вывод может не выглядеть точь-в-точь так, тем не менее, в нем будут содержаться те же 10 столбцов. Что же это за возвращаемые столбцы?

После EXPLAIN в запросе вы можете использовать ключевое слово EXTENDED и MySQL покажет вам дополнительную информацию о том, как выполняется запрос. Чтобы увидеть эту информацию, вам нужно сразу после запроса с EXTENDED выполнить запрос SHOW WARNINGS. Наиболее полезно смотреть эту информацию о запросе, который выполнялся после каких-либо изменений сделанных оптимизатором запросов.

Поиск и устранение проблем с производительностью с помощью EXPLAIN.

Теперь давайте посмотрим на то, как мы может оптимизировать не очень шустрый запрос, анализируя вывод команды EXPLAIN. Несомненно, что в действующих рабочих приложениях существует ряд таблиц со многими связями между ними, но иногда сложно предвидеть наиболее оптимальный способ написания запроса.

Я создал тестовую базу данных для приложения электронной торговли, которая не имеет никаких индексов или первичных ключей, и продемонстрирую влияние такого не очень хорошего способа создания таблиц при помощи “страшных” запросов. Дамп это таблицы вы можете скачать здесь — github.com/phpmasterdotcom/UsingExplainToWriteBetterMySQLQueries

Если вы посмотрите на результат (на него вам придется посмотреть только в примере ниже, по ссылке выше лежит дамп с уже добавленными ключами), то увидите все симптомы плохого запроса.

UPDATE. Здесь лежит исправленный дамп без индексов. В оригинальном авторском дампе индексы почему-то изначально добавлены.

Но даже если я напишу запрос получше, результат будет тем же самым, пока я не добавлю индексов. Указанный тип соединения ALL (худший), что означает, что MySQL не смог определить ни одного ключа, который бы мог использоваться при соединении. Отсюда следует и то, что possible_keys и key имеют значение NULL. Самым важным является то, что поле rows показывает, что MySQL сканирует все записи каждой таблицы для запроса. Это означает, что она просканирует 7 × 110 × 122 × 326 × 2996 = 91,750,822,240 записей, чтобы найти подходящие четыре (уберите из запроса EXPLAIN, проверьте сами). Это очень нехорошо и количество этих записей будет экспоненциально увеличиваться по мере роста базы данных.

Теперь давайте добавим очевидные индексы, такие, как первичный ключ для каждой таблицы, и выполним запрос еще раз. Взяв это за основное правило, в качестве кандидатов для добавления ключей вы можете использовать те столбцы которые используются в JOIN’ах, т.к. MySQL всегда сканирует их для нахождения соответствующих записей.

Давайте выполним наш прежний запрос после добавления индексов. Вы увидите это:

После добавления индексов, число считанных записей упало до 1 × 1 × 4 × 1 × 1 = 4 Для каждой записи order_number = 10101 в таблице orderdetails – это значит, что MySQL смогла найти соответствующие записи во всех других таблицах с использованием индексов и не стала прибегать к полному сканированию таблицы.

В первом выводе вы можете что использован тип соединения – “const”, который является самым быстрым типом соединения для таблиц с более, чем одной записью. MySQL смогла использовать PRIMARY KEY как индекс. В поле “ref” отображается “const”, что есть ни что иное, как значение 10101, указанное в запросе после ключевого слова WHERE.

Смотрим на еще один запрос. В нем мы выбираем объединение двух таблиц, products и productvariants, каждая объединена с productline. productvariants, которая состоит из разных вариантов продуктов с полем productCode – ссылкой на их цены.

Вы можете заметить ряд проблем в этом запросе. Он сканирует все записи в products и productvarians. Т.к. в этих таблицах нет индексов для столбцов productLine и buyPrice, в полях possible_keys и key отображаются значения NULL. Статус таблиц products и productlines проверяется после UNION’а, поэтому перемещение их внутри UNION’а уменьшит число записей. Добавим индексы.

Источник

What is the meaning of filtered in MySQL explain?

As described here in the MySQL docs:

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. Before MySQL 5.7.3, this column is displayed if you use EXPLAIN EXTENDED. As of MySQL 5.7.3, extended output is enabled by default and the EXTENDED keyword is unnecessary.

I still don’t get it. What is the meaning of «filtered» here? What information we can get from this column?

For example, when I start querying, some query will show 100, and some others show 18 or any lower than 100.

What the main point we can conclude from this value?

1 Answer 1

While it is obviously better to have 32 rows instead of 174 (if you e.g. have to later join them with another table), a «perfect» index would have given you these 32 rows directly from the initial search, saving you the time to look at and filter out 82% of all potential rows.

So a low value might indicate that there could be a better index: e.g. a full table scan with rows=1000 and filtered=0.1% could become an index lookup with rows=1 and filtered=100% if you add a good index.

So don’t take that value too seriously: neither does 100 mean your indexes are good, nor does a lower value necessarily indicate bad indexes. type is a much better indicator for that.

Источник