Скорость В ключевом слове в MySQL/PostgreSQL
Я добавил метод DataFrame#flattenSchema в открытый проект spark-daria .
Вот как вы можете использовать функцию с вашим кодом.
import com.github.mrpowers.spark.daria.sql.DataFrameExt._
df.flattenSchema().show()
+-------+-------+---------+----+---+
|foo.bar|foo.baz| x| y| z|
+-------+-------+---------+----+---+
| this| is|something|cool| ;)|
+-------+-------+---------+----+---+
Вы также можете указать различные разделители имен столбцов с помощью метода flattenSchema().
df.flattenSchema(delimiter = "_").show()
+-------+-------+---------+----+---+
|foo_bar|foo_baz| x| y| z|
+-------+-------+---------+----+---+
| this| is|something|cool| ;)|
+-------+-------+---------+----+---+
Этот параметр разделителя удивительно важен. Если вы выравниваете свою схему для загрузки таблицы в Redshift, вы не сможете использовать точки в качестве разделителя.
Вот полный фрагмент кода для генерации этого вывода.
val data = Seq(
Row(Row("this", "is"), "something", "cool", ";)")
)
val schema = StructType(
Seq(
StructField(
"foo",
StructType(
Seq(
StructField("bar", StringType, true),
StructField("baz", StringType, true)
)
),
true
),
StructField("x", StringType, true),
StructField("y", StringType, true),
StructField("z", StringType, true)
)
)
val df = spark.createDataFrame(
spark.sparkContext.parallelize(data),
StructType(schema)
)
df.flattenSchema().show()
Базовый код аналогичен коду Дэвида Гриффина (в случае, если вы не хотите добавлять зависимость spark-daria в ваш проект).
object StructTypeHelpers {
def flattenSchema(schema: StructType, delimiter: String = ".", prefix: String = null): Array[Column] = {
schema.fields.flatMap(structField => {
val codeColName = if (prefix == null) structField.name else prefix + "." + structField.name
val colName = if (prefix == null) structField.name else prefix + delimiter + structField.name
structField.dataType match {
case st: StructType => flattenSchema(schema = st, delimiter = delimiter, prefix = colName)
case _ => Array(col(codeColName).alias(colName))
}
})
}
}
object DataFrameExt {
implicit class DataFrameMethods(df: DataFrame) {
def flattenSchema(delimiter: String = ".", prefix: String = null): DataFrame = {
df.select(
StructTypeHelpers.flattenSchema(df.schema, delimiter, prefix): _*
)
}
}
}
7 ответов
В PostgreSQL именно то, что вы здесь получите, зависит от базовой таблицы, поэтому вам следует использовать EXPLAIN ANALYZE в некоторых образцах запросов к полезному подмножеству ваших данных, чтобы точно выяснить, что оптимизатор будет работать (убедитесь, что таблицы, с которыми вы работаете, тоже были АНАЛИЗИРОВАНЫ). IN можно обрабатывать несколькими разными способами, поэтому вам нужно взглянуть на некоторые образцы, чтобы выяснить, какая альтернатива используется для ваших данных. На ваш вопрос нет простого универсального ответа.
Что касается конкретного вопроса, который вы добавили в свою ревизию, в сравнении с тривиальным набором данных без задействованных индексов, вот пример двух планов запроса, которые вы получите:
postgres=# explain analyze select * from x where s in ('123','456');
Seq Scan on x (cost=0.00..84994.69 rows=263271 width=181) (actual time=0.015..1819.702 rows=247823 loops=1)
Filter: (s = ANY ('{123,456}'::bpchar[]))
Total runtime: 1931.370 ms
postgres=# explain analyze select * from x where s='123' or s='456';
Seq Scan on x (cost=0.00..90163.62 rows=263271 width=181) (actual time=0.014..1835.944 rows=247823 loops=1)
Filter: ((s = '123'::bpchar) OR (s = '456'::bpchar))
Total runtime: 1949.478 ms
Те две среды выполнения по существу идентичны, потому что в реальном времени обработки преобладает последовательное сканирование по таблице; многократный запуск показывает, что разница между ними ниже допустимой погрешности. Как видите, PostgreSQL преобразует регистр IN в использование фильтра ANY, который всегда должен выполняться быстрее, чем последовательность операций OR. Опять же, этот тривиальный случай не обязательно отражает то, что вы увидите в серьезном запросе, в котором задействованы индексы и т.п. Тем не менее, ручная замена IN последовательностью операторов OR никогда не должна быть быстрее, потому что оптимизатор знает, что лучше всего делать здесь, если у него есть хорошие данные для работы.
В общем, PostgreSQL знает больше уловок для оптимизации сложных запросов, чем оптимизатор MySQL, но он также сильно зависит от того, что вы предоставили оптимизатору достаточно данных для работы. Первые ссылки по теме «Оптимизация производительности»
Взгляните на siwapp.org , это приложение для выставления счетов с открытым исходным кодом на основе Symphony Framework, оно находится на ранней стадии бета-тестирования, но очень многообещающее.
- Константа из строки запроса
- Столбец константной или системной таблицы из того же соединения
- Результат некоррелированного подзапроса
- Любое выражение, составленное полностью из подвыражений предыдущих типов
Однако , этот запрос:
SELECT *
FROM table
WHERE id = 1
OR id = (SELECT id FROM other_table WHERE unique_condition)
будет использовать индекс по id , а этот:
SELECT *
FROM table
WHERE id IN (1, (SELECT id FROM other_table WHERE unique_condition))
будет использовать полное сканирование.
I. е. есть разница, когда одно из значений представляет собой однострочный подзапрос.
Я недавно отправил его как ошибку 45145 в MySQL ( он оказался специфичным для 5.2 , отсутствовал в 5.1 и исправлен в 6.0 )
Использование IN не обязательно является медленным, это то, как вы создаете параметры IN, значительно замедлит работу. Слишком часто люди используют SELECT ... WHERE x IN (SELECT ..., который может быть очень плохо оптимизирован (т. Е. Совсем не оптимизирован). Выполните поиск по «коррелированному подзапросу», чтобы увидеть, насколько плохим он может стать.
Часто вам вообще не нужно использовать IN, вместо этого можно использовать JOIN и воспользоваться производными таблицами.
SELECT * FROM table1 WHERE x IN (SELECT y FROM table2 WHERE z=3)
Можно перефразировать следующим образом
SELECT * FROM table1 JOIN (SELECT y FROM table2 WHERE z=3) AS table2 ON table1.x=table2.y
Если синтаксис IN медленный, синтаксис JOIN часто будет намного больше быстрее. Вы можете использовать EXPLAIN, чтобы увидеть, как каждый запрос будет оптимизирован по-разному. Это упрощенный пример, и ваша база данных может отображать один и тот же путь запроса, но более сложные запросы обычно показывают что-то другое.
Думаю, вы получили ответы, которые хотели выше. Просто хотел добавить одну вещь.
Вам нужно оптимизировать IN и использовать его правильно. В процессе разработки я всегда настраиваю раздел отладки внизу страницы в любое время, когда есть запрос, и он автоматически запускает EXPLAIN EXTENDED для каждого SELECT, а затем SHOW WARNINGS, чтобы увидеть (вероятный) способ, которым оптимизатор запросов MySQL перепишет запрос внутри. Из этого можно многому научиться, как убедиться, что IN работает на вас.
IN с подзапросом часто работает медленно. IN со списком значений не должен быть медленнее, чем someColumn = value1 OR someColumn = value2 OR someColumn = value3 и т. Д. Это достаточно быстро, если количество значений в норме.
IN с подзапросом работает медленно, когда оптимизатор не может найти хороший способ выполнить запрос и должен использовать очевидный метод построения полного результата подзапроса. Например:
SELECT username
FROM users
WHERE userid IN (
SELECT userid FROM users WHERE user_first_name = 'Bob'
)
будет намного медленнее, чем
SELECT username FROM users WHERE user_first_name = 'Bob'
, если оптимизатор не сможет понять, что вы имели в виду.
Скорость ключевого слова IN действительно будет зависеть от сложности вашего подзапроса. В приведенном вами примере вы просто хотите увидеть, находится ли значение someColumns в заданном списке значений, причем довольно коротком. Так что я могу предположить, что в этом случае стоимость производительности будет минимальной.
В документации говорится, что IN очень быстро работает в MySQL, но я не могу найти источник в настоящий момент.