Например, фильтр, возвращающий сотню договоров, работает сейчас уже не мгновенно:
- Код: Выделить всё
SELECT SQL_CALC_FOUND_ROWS
k.id as id,
concat(DATE_FORMAT(k.AGREEMENT_INDATE, '%y' ) , '.', f.num, '.') as AGREEMENT_PRENUM,
concat(DATE_FORMAT(k.AGREEMENT_INDATE, '%y' ) , '.', f.num, '.',k.AGREEMENT_NUM) as AGREEMENT_NUM_REAL,
AGREEMENT_NUM as AGREEMENT_NUM,
dkt.name as dktname,
AGREEMENT_CONCLUSION_DATE as AGREEMENT_CONCLUSION_DATE,
AGREEMENT_COMPLETE_DATE as AGREEMENT_COMPLETE_DATE,
AGREEMENT_INDATE as AGREEMENT_INDATE,
AGREEMENT_OUTDATE as AGREEMENT_OUTDATE,
concat(cust.surname,' ',cust.firname,' ',cust.patname) as customer,
concat(stud.surname,' ',stud.firname,' ',stud.patname) as student,
f.name as fname,
qualif.name as qname,
dp.code as code,
pt.name as totype,
k.privelege as privelege,
k.addinfo as addinfo,
PRICE as PRICE,
s as s,
COALESCE(cast(s/price*100 as decimal),0.0) as ss,
PRICE2 as PRICE2,
s2 as s2,
COALESCE(cast(s2/price2*100 as decimal),0.0) as ss2,
PRICE3 as PRICE3,
s3 as s3,
COALESCE(cast(s3/price3*100 as decimal),0.0) as ss3,
PRICE4 as PRICE4,
s4 as s4,
COALESCE(cast(s4/price4*100 as decimal),0.0) as ss4,
PRICE5 as PRICE5,
s5 as s5,
COALESCE(cast(s5/price5*100 as decimal),0.0) as ss5,
PRICE6 as PRICE6,
s6 as s6,
COALESCE(cast(s6/price6*100 as decimal),0.0) as ss6,
PAYMENT1 as PAYMENT1,
PAYMENT1_DATE as PAYMENT1_DATE,
k.creator as creator,
k.creatorTS as creatorTS,
k.updator as updator,
k.updatorTS as updatorTS
FROM
dtpeople cust,
dicorgunit f,
dtpeople stud,
dicqualif qualif
inner join dicprof dp on dp.id=qualif.to1prof
inner join dicprof_type pt on pt.id=dp.totype,
dickontracttype dkt,
kontracts k,
(
SELECT
k.id as kid,
COALESCE(convert(sum(pay.payment)/(count(*)/count(distinct pay.id)),decimal),0.0) as s,
COALESCE(convert(sum(pay2.payment)/(count(*)/count(distinct pay2.id)),decimal),0.0) as s2,
COALESCE(convert(sum(pay3.payment)/(count(*)/count(distinct pay3.id)),decimal),0.0) as s3,
COALESCE(convert(sum(pay4.payment)/(count(*)/count(distinct pay4.id)),decimal),0.0) as s4,
COALESCE(convert(sum(pay5.payment)/(count(*)/count(distinct pay5.id)),decimal),0.0) as s5,
COALESCE(convert(sum(pay6.payment)/(count(*)/count(distinct pay6.id)),decimal),0.0) as s6
FROM
kontracts k
left join
payments pay
on pay.tokontract=k.id and pay.kontractYear=1
left join
payments pay2
on pay2.tokontract=k.id and pay2.kontractYear=2
left join
payments pay3
on pay3.tokontract=k.id and pay3.kontractYear=3
left join
payments pay4
on pay4.tokontract=k.id and pay4.kontractYear=4
left join
payments pay5
on pay5.tokontract=k.id and pay5.kontractYear=5
left join
payments pay6
on pay6.tokontract=k.id and pay6.kontractYear=6
GROUP BY
k.id
) as pays
WHERE
cust.id=toPeopleCust and
stud.id=toPeopleStud and toDicQualif=qualif.id and qualif.toorgunit=f.id
and AGREEMENT_TYPE=dkt.id and pays.kid=k.id
GROUP BY
k.id
ORDER BY
AGREEMENT_PRENUM ASC, AGREEMENT_NUM ASC
limit 0,100
На все про все уходит до 3.5 секунд, что начинает раздражать пользователей системы. Анализ и профилирование этого и многих других запросов результата уже не дает. Попытки использования временных таблиц вместо подзапросов, даже с учетом их индексирования, и уж тем более попытки свести все в один запрос проблему не решают - количество обрабатываемых данных слишком велико. Упрощение запросов для частных случаев - вполне возможно, но тогда уйдет в прошлое вся мощь системы, позволяющей фильтровать данные по любому из существующих полей и произвольной их комбинации без потери скорости. Можно пытаться решать проблему наращиванием мощности железа, но это лишь отодвинет проблему чуть дальше во времени. Можно добавить поддержку кэша в саму систему, но это - время и немалое, особенно на тестирование. А хочется - дешево и сердито.
Оказалось, решение есть. Не панацея, но в большинстве случаев проблема решаема. В MySQL имеется поддержка кэширования результатов запросов. Т.е. результат выполнения select сохраняется в кэше, индексируемый хешем (md5) строки-запроса. При повторном запросе сервер возвращает сохраненный результат, не выполняя сам запрос. При этом любой insert, delete, update и т.п. удаляет из кэша запросы, ссылающиеся на изменяемые таблицы. По умолчанию кэш выключен. Включить просто:
в my.cnf прописать
query_cache_size=сколько памяти под кэш
Лично у меня теперь query_cache_size=64M
За утро в кэше 940 результатов запросов, 5555 хитов и всего 2МБ задействованной RAM:
- Код: Выделить всё
mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 161 |
| Qcache_free_memory | 65743968 |
| Qcache_hits | 5555 |
| Qcache_inserts | 3295 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 21024 |
| Qcache_queries_in_cache | 940 |
| Qcache_total_blocks | 2097 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Более 20000 некэшированных запросов объясняется тем, что на этом-же сервере крутится realtime база данных журнала доступа портала shgpi.edu.ru и ее статистический обработчик.