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:
RapidShare
1. 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
$array[] = $row; }
dodanie rekordu przy pomocy zapytań pisanych w php
mysql_query("INSERT INTO `chat_posts` (`user_id`, `post_text`) VALUES ('".$user_id."','".$msg."')"); if($row[0] > 20) { // jeżeli rekordów jest więcej niż 20, usuń stary wpis } // pobranie aktualnego id postu w celu pobrania nowszych rekordów // pobranie max. post_id w celu update'u w tabeli chat_users //update user // 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; }