SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 13.04.2008 10:28:32

NetWork
Участник
Зарегистрирован: 13.04.2008
Сообщений: 8

Оптимизация при больших объёмах записей

У меня есть таблица статистики типа ЛОГ. В него складывается вся статистика ротатора баннеров с сайтах компании.
Дошло до того, что записей в базе стало 3 000 000 smile И Даже обычный COUNT() перестал летать как раньше.
Вот таблица лога

CREATE TABLE `log` (
  `id` int(19) NOT NULL auto_increment,
  `id_account` int(11) NOT NULL default '0',
  `id_banner` int(11) NOT NULL default '0',
  `date` int(11) NOT NULL default '0',
  `action` varchar(5) NOT NULL,
  `ip` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `date` (`date`),
  KEY `id_banner` (`id_banner`),
  KEY `ip` (`ip`),
  KEY `action` (`action`),
  KEY `idaccount_idbanner` (`id_account`,`id_banner`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Подскажите как ещё можно оптимизировать эту часть.

А я быстро сделал CRON который запускается каждый час и вносит в другую таблицу уже более быструю для выбора статистику.

Вот таблица для CRON

CREATE TABLE `stat_cron` (
  `views` int(11) NOT NULL default '0',
  `u_views` int(11) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `u_clicks` int(11) NOT NULL default '0',
  `accountid` int(11) NOT NULL default '0',
  `bannerid` smallint(6) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `hour` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`accountid`,`bannerid`,`date`,`hour`),
  KEY `accountid_day` (`accountid`,`date`),
  KEY `bannerid_day` (`accountid`,`bannerid`,`date`),
  KEY `views` (`views`),
  KEY `u_views` (`u_views`),
  KEY `clicks` (`clicks`),
  KEY `u_clicks` (`u_clicks`),
  KEY `hour` (`hour`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

А из этой таблицы как мне кажеться будут данные быстро выводиться

SUM(views) AS views
FROM stat_cron
WHERE accountid = 5
AND date = сегодня

Помогите пожалуйста. Ото сервак скоро вешаться будет sad

Неактивен

 

#2 13.04.2008 13:26:56

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Оптимизация при больших объёмах записей

Вы не указали, какие именно запросы к первой таблице наиболее часто происходят. Посмотрите SHOW FULL PROCESSLIST, когда сервер замедляется и включите журнал медленных запросов. Оптимизируется именно приложение, а не просто таблица.

Хранение преобработанной статистики - правильный путь, Вы можете обновлять вторую таблицу по триггеру BEFORE INSERT в первой таблице, а не по cron, например.

Кстати, почему в первой таблице не было ключа   KEY `accountid_day` (`accountid`,`date`)?

Неактивен

 

#3 13.04.2008 14:41:34

NetWork
Участник
Зарегистрирован: 13.04.2008
Сообщений: 8

Re: Оптимизация при больших объёмах записей

Вот я переработал структуру таблиц.

#1

CREATE TABLE `log_acc` (
  `id` int(19) NOT NULL auto_increment,
  `id_account` int(11) NOT NULL default '0',
  `id_banner` int(11) NOT NULL default '0',
  `date` int(11) NOT NULL default '0',
  `action` varchar(5) NOT NULL,
  `ip` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id_banner` (`id_banner`),
  KEY `ip` (`ip`),
  KEY `action` (`action`),
  KEY `date_idaccount_ip` (`date`,`id_account`,`ip`),
  KEY `id_account` (`id_account`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3 ;

# 2

CREATE TABLE `stat_acc_cron` (
  `views` int(11) NOT NULL default '0',
  `u_views` int(11) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `u_clicks` int(11) NOT NULL default '0',
  `accountid` int(11) NOT NULL default '0',
  `bannerid` smallint(6) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `hour` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`date`,`hour`,`accountid`,`bannerid`),
  KEY `views` (`views`),
  KEY `u_views` (`u_views`),
  KEY `clicks` (`clicks`),
  KEY `u_clicks` (`u_clicks`),
  KEY `hour` (`hour`),
  KEY `bannerid_date` (`bannerid`,`date`),
  KEY `accountid_date` (`accountid`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

И теперь вот такие запросы чаще всего идут в MySQL

# в первую таблицу

INSERT INTO log_acc (id_account,id_banner,date,action,ip)
VALUES (
'21',211,'3212321','click','127.0.0.1'
)

SELECT COUNT(*) AS count
FROM log_acc
WHERE date = '3212321'
AND accountid = '21'
AND ip = '127.0.0.1'

# во вторую таблицу

SELECT COUNT(*) AS count
FROM stat_acc_cron
WHERE date = '2008-04-12'
AND hour = '13'
AND accountid = '21'

SELECT COUNT(*) AS count
FROM stat_acc_cron
WHERE date = '2008-04-12'
AND hour = '13'
AND accountid = '21'
AND bannerid = '211'

И вот такой вот

SELECT SUM(views) AS count
FROM stat_acc_cron
WHERE date = '2008-04-12'

Иногда к этому запросу добавляется AND accountid = '21'

Вот. МОжет так будет лучше для совета по оптимизации.

Неактивен

 

#4 13.04.2008 17:06:16

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Оптимизация при больших объёмах записей

Сделайте EXPLAIN SELECT COUNT(*) AS count
FROM log_acc
WHERE date = '3212321'
AND accountid = '21'
AND ip = '127.0.0.1'

Увидите, что он полностью ключ   KEY `date_idaccount_ip` (`date`,`id_account`,`ip`),
Посмторите SHOW STATUS LIKE 'key%' как используется key_buffer - он значительно ускоряет запросы, работающие по ключу.
Другие приведенные запросы также должны использовать ключи, проверьте это через EXPLAIN.

Что именно тормозит сейчас? Где узкое место? (см также С чего начинать оптимизацию MySQL )

Неактивен

 

#5 13.04.2008 17:17:45

NetWork
Участник
Зарегистрирован: 13.04.2008
Сообщений: 8

Re: Оптимизация при больших объёмах записей

rgbeast написал:

Увидите, что он полностью ключ   KEY `date_idaccount_ip` (`date`,`id_account`,`ip`),

Так это же хорошо?

P.S. Тут статью я уже читал.

Неактивен

 

#6 13.04.2008 17:32:03

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Оптимизация при больших объёмах записей

Так это же хорошо?

Да, это хорошо. Поэтому я и спросил какие именно запросы работают медленно. Вставка не должна тормозить, запросы по ключу быстрые по своей природе - здесь как раз важно помещается ли ключ в кэш. Так как ключей много, то все в кэш могут не поместиться, поэтому рекомендую создать именованный кэш и хранить в нем наиболее востребованные 1-2 ключа, см. http://sqlinfo.ru/articles/info/3.html

P.S. статья про оптимизацию будет продолжена, точнее по каждому отдельному пункту предполагается написать отдельный материал

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson