MySQL Select Оптимизация Справка?

Question

MySQL Select Оптимизация Справка?

0

Это широкий пример запроса SELECT, который я часто использую на одном из моих сайтов. У нас очень плохие проблемы с медленной загрузкой страниц с нашего хоста, поэтому я стараюсь сделать все возможное, чтобы оптимизировать каждый бит кода, который использует сайт. Я не эксперт, когда дело доходит до MySQL, поэтому я надеюсь, что некоторые из вас могут помочь. Вот запрос, который я пытаюсь оптимизировать немного больше -

Select ID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 From table_1
Where Active = '1' And Col2 LIKE '%Cat%' And Col3 <> 'blah' And Col3 <> 'blah1'  And Col3 <> 'blah2' And Col3 <> 'blah3' And Col3 <> 'blah4' And Col3 <> 'blah5' And Col3 <> 'blah6'
And ID Not In (Select t2ID From table_2 Where table_2.t2ID = table_1.ID And table_2.Col1 = '1' And table_2.Col2 = '1')
And ID Not In (Select t3ID From table_3 Where table_3.t3ID = table_1.ID And table_3.Col1 = '1')
And ID Not In (Select t4ID From table_4 Where table_4.t4ID = table_1.ID And table_4.Col1 = '1')

В основном он проверяет 1 таблицу (table_1) и вытягивает все строки, которые соответствуют, которые не найдены в table_2, table_3 и table_4. Я уверен, что есть гораздо более эффективный способ сделать это, кроме нескольких подборов. Любая помощь очень ценится! Спасибо заранее:)

ОБНОВЛЕНИЕ: В основном все, что мне интересно узнать, есть ли что-то более быстрое, чем множественные подвыборы в запросе? Я уверен, что есть какой-то способ получить результаты из одной таблицы, которые не существуют в нескольких других таблицах, что намного эффективнее, чем выбор суббайтов... Единственная общность между таблицами заключается в том, что идентификатор из таблицы_1 идентичный другому столбцу в каждой из трех других таблиц (это то, что я сейчас проверяю, используя теперь подвыборы). К сожалению, я просто не могу понять, что более эффективным способом сделать этот запрос является... Спасибо всем за вклад до сих пор!

ТАБЛИЧ. LAYOUT

mysql> show create table campaigns\G  
*************************** 1. row ***************************  
Table: campaigns  
Create Table: CREATE TABLE `campaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`AddedBy` varchar(75) default NULL,  
`pCampaignName` varchar(255) default NULL,  
`CampaignName` varchar(255) default NULL,  
`CampaignValue` decimal(65,2) default '0.00',  
`CampaignPayout` decimal(65,2) NOT NULL default '0.00',  
`CampaignT` double NOT NULL default '0',  
`CampaignSD` double NOT NULL default '0',  
`ReportingTime` varchar(255) default NULL,  
`CampaignExpiration` varchar(100) default NULL,  
`DurationType` varchar(100) default NULL,  
`Countries` varchar(100) default NULL,  
`CampaignDescription` longtext,  
`CampaignRequirements` longtext,  
`CampaignType` varchar(50) default NULL,  
`CampaignID` varchar(255) default NULL,  
`BannerImageWidth` int(10) NOT NULL default '0',  
`BannerImageHeight` int(10) NOT NULL default '0',  
`BannerImageURL` varchar(255) default NULL,  
`BannerImageAlternateText` varchar(255) default NULL,  
`DisplayBanner` int(1) NOT NULL default '0',  
`CampaignCode` longtext,  
`CampaignURL` longtext,  
`CampaignActive` int(1) NOT NULL default '0',  
`Status` varchar(255) default NULL,  
`Affiliate` varchar(255) default NULL,  
`NewOfferEmailSent` int(1) unsigned NOT NULL default '0',  
`NumberApproved` double(65,2) NOT NULL default '0.00',  
`NumberLeads` double NOT NULL default '0',  
`ThumbsUp` double NOT NULL default '0',  
`ThumbsDown` double NOT NULL default '0',  
`CampaignPoints` double NOT NULL default '0',  
`UserRatingUp` double NOT NULL default '0',  
`UserRatingDown` double NOT NULL default '0',  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=1608 DEFAULT CHARSET=utf8  
1 row in set (0.09 sec)  

mysql> show indexes from campaigns\G  
*************************** 1. row ***************************  
Table: campaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 1596  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.10 sec)  

mysql>  
mysql> show create table acampaigns\G  
*************************** 1. row ***************************  
Table: acampaigns  
Create Table: CREATE TABLE `acampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`CampaignName` varchar(255) default NULL,  
`CampaignRequirements` longtext,  
`CampaignURL` longtext,  
`CampaignValue` decimal(65,2) NOT NULL,  
`CampaignPayout` decimal(65,2) NOT NULL,  
`CampaignReferralCommissionTier1` decimal(65,2) NOT NULL default '0.20',  
`CampaignReferralCommissionTier2` decimal(65,2) NOT NULL default '0.10',  
`CampaignT` double NOT NULL default '0',  
`CampaignSD` double NOT NULL default '0',  
`CampaignType` varchar(255) default NULL,  
`CampaignID` varchar(100) default NULL,  
`CampaignExpiration` varchar(100) default NULL,  
`CampaignReturnStatus` varchar(100) default NULL,  
`CampaignStatus` varchar(255) default NULL,  
`pCampaignID` int(11) NOT NULL,  
`pCampaignName` varchar(255) default NULL,  
`pUserID` int(11) NOT NULL,  
`pUsername` varchar(75) default NULL,  
`pUserIPAddress` varchar(30) default NULL,  
`ApprovedOn` datetime NOT NULL,  
`MarkedDone` int(1) NOT NULL default '0',  
`Notes` longtext,  
`PaidOn` datetime default NULL,  
`cBonus` decimal(65,2) NOT NULL default '0.00',  
`ReversedReason` varchar(255) default NULL,  
`CampaignPoints` double NOT NULL default '0',  
`Affiliate` varchar(255) default NULL,  
`RC1Paid` int(1) unsigned NOT NULL default '0',  
`RC2Paid` int(1) unsigned NOT NULL default '0',  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=10996 DEFAULT CHARSET=utf8  
1 row in set (0.44 sec)  

mysql> show indexes from acampaigns\G  
*************************** 1. row ***************************  
Table: acampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 8936  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.09 sec)  

mysql>  
mysql> show create table bcampaigns\G  
*************************** 1. row ***************************  
Table: bcampaigns  
Create Table: CREATE TABLE `bcampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`pCampaignID` int(11) NOT NULL,  
`ReportedByUserID` int(11) NOT NULL,  
`Status` varchar(255) default NULL,  
`Notes` longtext,  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=375 DEFAULT CHARSET=utf8  
1 row in set (0.08 sec)  

mysql> show indexes from bcampaigns\G  
*************************** 1. row ***************************  
Table: bcampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 0  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.08 sec)  

mysql>  
mysql> show create table icampaigns\G  
*************************** 1. row ***************************  
Table: icampaigns  
Create Table: CREATE TABLE `icampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`pCampaignID` int(11) default NULL,  
`IgnoredByUserID` int(11) default NULL,  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=567 DEFAULT CHARSET=utf8  
1 row in set (0.09 sec)  

mysql> show indexes from icampaigns\G  
*************************** 1. row ***************************  
Table: icampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 532  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.40 sec)  

mysql>
mysql> explain Select ID, CreatedOn, pCampaignName, 
CampaignName, CampaignRequirements, CampaignURL, Countries, 
CampaignPayout, CampaignPoints, CampaignT, CampaignSD, CampaignType, 
ReportingTime, NumberApproved, NumberLeads
From campaigns
-> Where CampaignActive = '1' And CampaignType LIKE 'Cat%' 
And CampaignType <> 'DS' And CampaignType <> 'CC'  And CampaignType <> 'PC' 
And CampaignType <> 'PC2' And CampaignType <> 'GCC' And CampaignType <> 'G' 
And CampaignType <> 'R'
-> And ID Not In (Select pCampaignID From acampaigns  
Where campaigns.ID = acampaigns.pCampaignID And MarkedDone = '1' And campaigns.pUserID = '1')
-> And ID Not In (Select pCampaignID From bcampaigns  
Where bcampaigns.pCampaignID = campaigns.ID And  bcampaigns.ReportedByUserID = '1')
-> And ID Not In (Select pCampaignID From icampaigns  
Where icampaigns.pCampaignID = campaigns.ID And icampaigns.IgnoredByUserID = '1')

Jay 25 авг. 2010, в 01:01

Источник

Теги:

mysql

3 ответа

Ещё вопросы

Anil Soman · Answer 1 · 2010-08-25T03-11-00.000Z

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

Этот запрос будет выполняться тысячами раз в день тысячами участников, поэтому в первую очередь необходима оптимизация. Возможно, я ошибаюсь, но не разделит ли этот запрос еще больше ресурсов на сервере базы данных?

Jon Black · Answer 2 · 2010-08-25T02-14-00.000Z

IDFMA

пожалуйста, опубликуйте результаты следующего script, чтобы мы могли подробно проанализировать ваш запрос. если вы также можете использовать результаты в http://pastie.org/, это тоже было бы здорово.

спасибо

show create table table_1\G
show indexes from table_1\G

show create table table_2\G
show indexes from table_2\G

show create table table_3\G
show indexes from table_3\G

show create table table_4\G
show indexes from table_4\G

explain
Select 
 ID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, 
 Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 
From 
 table_1
Where 
 Active = '1' And 
 Col2 LIKE '%Cat%' And Col3 <> 'blah' And Col3 <> 'blah1'  And Col3 <> 'blah2' And Col3 <> 'blah3' And 
 Col3 <> 'blah4' And Col3 <> 'blah5' And Col3 <> 'blah6' And 
 ID Not In (Select t2ID From table_2 Where table_2.t2ID = table_1.ID And table_2.Col1 = '1' And table_2.Col2 = '1') And 
 ID Not In (Select t3ID From table_3 Where table_3.t3ID = table_1.ID And table_3.Col1 = '1') And 
 ID Not In (Select t4ID From table_4 Where table_4.t4ID = table_1.ID And table_4.Col1 = '1');

Этот код выдавал ошибки, указывающие на «show create table table_1 \ G» и «show indexes from table_1 \ G».
Главное сообщение было отредактировано с результатами, которые вы запросили.

Brian Hooper · Answer 3 · 2010-08-25T01-11-00.000Z

Вы пытались ОБЪЯСНИТЬ ваш запрос. Если вы не знакомы с EXPLAIN, он создаст отчет, показывающий, как выполняется запрос, и особенно какие индексы используются, и может появиться там, где индекс желателен, но не присутствует.

В прошлом я также обнаружил, что NOT IN иногда может быть ужасно медленным. Возможно, переписывание NOT IN, поскольку NOT EXISTS может ускорить процесс.

NOT EXISTS (SELECT *
                FROM table_3
                WHERE table_3.t3ID = table_1.ID AND
                      table_3.Col1 = '1')

Изменение NOT IN на NOT EXISTS кажется примерно одинаковой скоростью (без существенной разницы). Кроме того, я объяснил запрос, и он вернул в основном NULL. table_1 читает чуть менее 1500 строк, table_2 читает чуть более 7000 строк, остальные 2 таблицы меньше 500 строк. Все остальное, кроме имен таблиц и выбора типа, равно NULL. Я попытался создать запрос JOIN, который будет извлекать те же данные, просто чтобы увидеть, будет ли он быстрее, но, похоже, не может получить запрос, который не выдает ошибок и захватывает те же данные. Спасибо за помощь до сих пор!