Witam,
ponieważ wystąpiły pewne komplikacje z edycją poprzedniego tematu, ponownie zamieszczam przykład wykorzystania procedur w celu zwiększenia wydajności naszych aplikacji. Przykład jak sam tytuł wskazuje, odnosi się do najbardziej zasobożernego elementu większości stron - shoutboxie opartym na AJAX'ie.
Poniżej przedstawiam archiwum z przykładem wydajnego shoutboxa. W przeciwieństwie do innych topików w tym dziale nie jest to gotowiec a sposób w jaki możemy wykorzystać wbudowane elementy MySQL'a. Przydatne na serwerach współdzielonych, które domyślnie nie obsługują memcache, a mają narzucone restrykcje typu "użyj czegoś co zamuli serwer a będziemy musieli się pożegnać".
Link do archiwum:
RapidShare1. Tworzymy nowe tabele w naszej bazie danych (wrzucamy poniższe zapytania do MySQL np. za pomocą phpmyadmin)
--
-- Struktura tabeli dla tabeli `chat_posts`
--
DROP TABLE IF EXISTS `chat_posts`;
CREATE TABLE IF NOT EXISTS `chat_posts` (
`post_id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(4) UNSIGNED NOT NULL,
`post_text` varchar(300) NOT NULL,
`date_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`,`user_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Struktura tabeli dla tabeli `chat_users`
--
DROP TABLE IF EXISTS `chat_users`;
CREATE TABLE IF NOT EXISTS `chat_users` (
`user_id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_name` varchar(15) NOT NULL,
`user_avatar` varchar(50) NOT NULL,
`post_id` int(4) UNSIGNED NOT NULL,
`flag_status` tinyint(1) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Zrzut danych tabeli `chat_users`
--
INSERT INTO `chat_users` (`user_id`, `user_name`, `user_avatar`, `post_id`, `flag_status`) VALUES
(1, 'Songo', 'default.jpg', 0, 1),
(2, 'Vegeta', 'default.jpg', 22, 1),
(3, 'Gohan', 'gohan.gif', 22, 1),
(4, 'Cell', 'cell.jpg', 22, 1);
2. W podobny sposób dodajemy procedury
DELIMITER $$
--
-- Procedury
--
CREATE PROCEDURE `sp_clean`()
BEGIN
DECLARE cnt, cur INT;
SET cnt = (SELECT COUNT(*) FROM `chat_posts`);
IF cnt > 20 THEN
SET cur = (SELECT `post_id` FROM `chat_posts` ORDER BY `post_id` DESC LIMIT 19,1);
DELETE FROM `chat_posts` WHERE `post_id` < cur;
END IF;
END$$
CREATE PROCEDURE `sp_connect`(IN `pUserId` INT(4))
BEGIN
DECLARE userName VARCHAR(15);
DECLARE postId INT;
SET userName = (SELECT `user_name` FROM `chat_users` WHERE `user_id`=pUserId);
#udate post_id
UPDATE `chat_users` SET `post_id`=(SELECT MAX(`post_id`) FROM `chat_posts`);
#insert connect message
CALL sp_insert(pUserId, CONCAT(userName, ' dołączył do czatu'));
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_disconnect`(IN `pUserId` INT(4))
BEGIN
DECLARE userName VARCHAR(15);
SET userName = (SELECT `user_name` FROM `chat_users` WHERE `user_id`=pUserId);
#udate post_id
UPDATE `chat_users` SET `post_id`=0 WHERE `user_id`=pUserId;
#insert disconnect message
CALL sp_insert(pUserId, CONCAT(userName, ' odłączył się od czatu'));
END$$
CREATE PROCEDURE `sp_fetch`(IN `pUserId` INT(4))
BEGIN
DECLARE postId, maxPostId INT;
SET postId = (SELECT `post_id` FROM `chat_users` WHERE `user_id`=pUserId);
SET maxPostId = (SELECT MAX(`post_id`) FROM `chat_posts`);
CALL sp_update_id(pUserId, maxPostId);
SELECT
`p`.`post_text`, TIME(`p`.`date_create`) AS time_create,
`u`.`user_id`, `u`.`user_name`, `u`.`user_avatar`
FROM
`chat_posts` `p`,
`chat_users` `u`
WHERE
`p`.`user_id` = `u`.`user_id` AND
`p`.`post_id` > postId
ORDER BY `p`.`post_id` ASC
LIMIT 10;
END$$
CREATE PROCEDURE `sp_insert`(IN `pUserId` INT(4), IN `pPostText` VARCHAR(300) CHARSET utf8)
BEGIN
DECLARE insertId INT;
INSERT INTO `chat_posts` (`user_id`, `post_text`) VALUES (pUserId, pPostText);
SET insertId = LAST_INSERT_ID();
#CALL sp_update_id(pUserId, insertId-1);
CALL sp_remove();
CALL sp_fetch(pUserId);
END$$
CREATE PROCEDURE `sp_remove`()
BEGIN
DECLARE cnt, postId INT;
SET cnt = (SELECT COUNT(*) FROM `chat_posts`);
IF cnt > 20 THEN
SET postId = (SELECT MIN(`post_id`) FROM `chat_posts`);
DELETE FROM `chat_posts` WHERE `post_id` = postId;
END IF;
END$$
CREATE PROCEDURE `sp_update_id`(IN `pUserId` INT(4), IN `pPostId` INT(4))
BEGIN
UPDATE `chat_users` SET `post_id`=pPostId WHERE `user_id`=pUserId;
END$$
DELIMITER ;
3. Opcjonalnie, możemy dodać zdarzenie, które co godzinę będzie czyściło naszą tabelę z ewentualnego nadmiaru postów
DELIMITER $$
--
-- Zdarzenia
--
CREATE EVENT `task_clean` ON SCHEDULE EVERY 1 HOUR STARTS '2013-01-01 00:00:00' ENDS '2020-01-01 03:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL sp_clean()$$
DELIMITER ;
4. Wypakuj wcześniej ściągnięte archiwum i w pliku ajax.php ustaw dane do połączenia z bazą MySQL.
5. Odpal skrypt index.php?id=x gdzie x to user_id z tabeli chat_users
Do poprawnego działania należy zmodyfikować plik ajax.php aby obsługiwał Twój system autoryzacji użytkowników.
------------------------------------------------------------------------------
Poniżej przewaga procedur nad zapytaniami pisanymi w PHP
dodanie rekordu przy pomocy procedury
$query = mysql_query("CALL sp_insert('".$user_id."','".$msg."');");
$array[] = $row;
}
echo json_encode
($array);
dodanie rekordu przy pomocy zapytań pisanych w php
mysql_query("INSERT INTO `chat_posts` (`user_id`, `post_text`) VALUES ('".$user_id."','".$msg."')");
$query = mysql_query("SELECT COUNT(*) FROM `chat_posts`");
if($row[0] > 20) { // jeżeli rekordów jest więcej niż 20, usuń stary wpis
$query = mysql_query("SELECT MIN(`post_id`) FROM `chat_posts`");
mysql_query("DELETE FROM `chat_posts` WHERE `post_id` = '".$row[0]."'"); }
// pobranie aktualnego id postu w celu pobrania nowszych rekordów
$query = mysql_query("SELECT `post_id` FROM `chat_users` WHERE `user_id`='".$user_id."'");
// pobranie max. post_id w celu update'u w tabeli chat_users
$query = mysql_query("SELECT MAX(`post_id`) FROM `chat_posts`");
//update user
mysql_query("UPDATE `chat_users` SET `post_id` = '".$maxPostId."'");
// pobranie najnowszych postów
SELECT
`p`.`post_text`, TIME(`p`.`date_create`) as time_create,
`u`.`user_id`, `u`.`user_name`, `u`.`user_avatar`
FROM
`chat_posts` `p`,
`chat_users` `u`
WHERE
`p`.`user_id` = `u`.`user_id` AND
`p`.`post_id` > '".$postId."'
ORDER BY `p`.`post_id` ASC
LIMIT 10");
$array[] = $row;
}
echo json_encode
($array);
Ten post edytował CuteOne 8.02.2013, 10:34:53