У меня около 500 000 записей личного профиля в базе данных MySQL, содержащих столбец с датой рождения (dob). Поскольку мне приходилось получать возраст для каждого профиля, мне нужно было рассчитать его динамически, что я могу делать с помощью PHP (date_diff(date_create($dob), date_create('today'))->y)
или через SQL ('SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age')
. Какой из двух быстрее или предпочтительнее, особенно если у меня есть сотни тысяч строк?
Вот тест:
Создайте таблицу со случайными датами в 100K
drop table if exists birthdays;
create table birthdays (
id int auto_increment primary key,
dob date
);
insert into birthdays (dob)
select '1950-01-01' + interval floor(rand(1)*68*365) day as dob
from information_schema.COLUMNS c1
, information_schema.COLUMNS c2
, information_schema.COLUMNS c3
limit 100000
;
Запустите этот скрипт PHP
<?php
header('Content-type: text/plain');
$db = new PDO("mysql:host=localhost;dbname=test", "test","");
### SQL
$starttime = microtime(true);
$stmt = $db->query("SELECT id, dob, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM birthdays");
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
$runtime = microtime(true) - $starttime;
echo "SQL: $runtime \n";
### PHP
$starttime = microtime(true);
$stmt = $db->query("SELECT id, dob FROM birthdays");
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($data as $row) {
$row->age = date_diff(date_create($row->dob), date_create('today'))->y;
}
$runtime = microtime(true) - $starttime;
echo "PHP: $runtime \n";
Результат:
SQL: 0.19094109535217
PHP: 1.203684091568
Похоже, что решение SQL в 6 раз быстрее. Но это не совсем так. Если мы удалим код, который вычисляет возраст из обоих решений, мы получим что-то вроде 0.1653790473938. Это означает, что накладные расходы для SQL составляют 0,025 с, а для PHP - 1,038 сек. Поэтому в этом тесте SQL в 40 раз быстрее.
Примечание. Существуют более быстрые способы расчета возраста в PHP. Например
$d = date('Y-m-d');
$row->age = substr($d, 0, 4) - substr($row->dob, 0, 4) - (substr($row->dob, 5) > substr($d, 5) ? 1 : 0);
как в четыре раза быстрее - в то время как date('Ym-d')
потребляет более 80% времени. Если вы обнаружите способ избежать любой функции даты, вы можете приблизиться к производительности MySQL.
В общем, наилучшим подходом является выполнение таких расчетов на сервере.
Идеальный подход заключается в использовании созданного столбца. Это было доступно с MySQL 5.7.5 и было бы выражено как:
alter table t add age unsigned as
(TIMESTAMPDIFF(YEAR, dob, CURDATE()));
Увы, вы можете использовать детерминированные функции только для генерируемых столбцов. curdate()
и now()
не являются детерминированными, поскольку их значения могут меняться при каждом вызове.
Следующее лучшее - использовать представление:
create view v_t as
select t.*,
TIMESTAMPDIFF(YEAR, dob, CURDATE())
from t;
Затем, когда вы запрашиваете представление, у вас будет age
. Это верно независимо от того, где вы запрашиваете его. И везде такая же логика.
Единственное предостережение в выполнении расчетов на сервере заключается в том, что оно использует время сервера, а не время локального приложения. Если это проблема, то это сильный аргумент для локального вычисления.
если вы хотите получить все 500 000 записей, вы должны сделать это в MySql, потому что производительность лучше, чем PHP
но, если вы хотите получить некоторые из этих данных (например, 10 записей), сделайте это с PHP, с которым лучше справиться. и производительность не отличается