Оптимизация SQLМихаил Гуренков

17 октября 2008 г. Клуб Маинфо.ру

Упрощение схемы данных

Неявное соединение → ...

select *from xCompaniesToCategories cc, xCompanies c, xCompanyProperties p, xPersons pe, #addresses aj, #addresses af, xCompaniesToPersons ctp, xContracts cowhere p.id_Company = c.id and pe.id = id_Chief and aj.id = id_DejuroAddress and af.id = id_DefactoAddress and ctp.id_Company = c.id and ctp.id_Person = pe.id and co.id_Company = c.id and c.id = cc.id_Company and id_Category = 30 and p.id_Company = c.id

... → Явное соединение

select *from xCompanies c join xCompaniesToCategories cc on c.id = cc.id_Company join xCompanyProperties p on p.id_Company = c.id join xPersons pe on pe.id = id_Chief join #addresses aj on aj.id = id_DejuroAddress join #addresses af on af.id = id_DefactoAddress join xCompaniesToPersons ctp on ctp.id_Company = c.id and ctp.id_Person = pe.id join xContracts co on co.id_Company = c.idwhere id_Category = 30

Типовые запросы → представления

select * from OrderServices s join Orders o on s.id_Order = o.idwhere id_Company = 1234 ↓ select * from vOrderServices where id_Company = 1234

Инкапсуляция запросовcreate view vOrderServiceHistory asselect os1.*, os1.AffectedDate DateOn, os2.AffectedDate DateOff, os2.id_Order id_OrderOff from vOrderServices os1 left join vOrderServices os2 on (os1.id_Company = os2.id_Company and os1.id_Resource = os2.id_Resource and os2.ServiceHash = os1.ServiceHash and os2.Rang < os1.Rang and os2.Action = -1) and not exists (select * from vOrderServices os3 where os1.id_Company = os3.id_Company and os1.id_Resource = os3.id_Resource and os3.ServiceHash = os1.ServiceHash and os3.Rang > os2.Rang and os3.Rang < os1.Rang and os3.Action = -1)

where os1.OrderTypeAction = 1

Рефакторинг базы данных

Использование индексов

Оптимизаторы

Rule-based (RBO)Cost-based (CBO)

Денормализация данных

Составные тэги

ЦСКА (баскетболл, муж)

↓ «ЦСКА (баскетболл, муж)», «баскетболл», «муж»

SQL и процедурный стиль программирования

Новости по теме

select news.* from news join ( select distinct n.id from news n join taggings t1 on t1.news_id = ? join taggings t2 on t2.news_id = ? and t1.id_tag > t2.id_tag join taggings r1 on t1.tag_id = r1.tag_id and r1.news_id = n.id join taggings r2 on t2.tag_id = r2.tag_id and r2.news_id = n.id ) related on news.id = related.idwhere news.id != ?order by publishing_date desc

Публикация прайс-листа

Функции

select id, dbo.GetServiceAbonPrice(id, getdate()) as AbonPrice from vOrderServices

select id, abon.Price from vOrderServices os join dbo.GetAbonPrices() as abon on abon.ServiceHash = os.ServiceHash and abon.id_Company = os.id_Company

В заключение

Главные мысли

1. Забота о схеме данных2. Правильное использование индексов 3. SQL — не язык программирования

Мартин Грабер«SQL»

Джо Селко«SQL для профессионалов»

«Рефакторинг баз данных»

Скотт Эмблер, Прамод Садаладж

Ссылки

1. www.sql.ru2. msdn.microsoft.com3. dev.mysql.com/doc/refman/5.1/en/optimization.html 4. community.livejournal.com/ru_mysql/ 5. ...

www.dreamspark.ru

Контакты

michael@gurenkov.net www.gurenkov.net

SQL has been optimized :)