[PHP][AJAX] Wydajny shoutbox, shoutbox oparty na procedurach Opcje

[CuteOne]

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:
RapidShare

1. Tworzymy nowe tabele w naszej bazie danych (wrzucamy poniższe zapytania do MySQL np. za pomocą phpmyadmin)

[SQL] pobierz, plaintext 
--
-- 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);
[SQL] pobierz, plaintext 

2. W podobny sposób dodajemy procedury

[SQL] pobierz, plaintext 
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 ;
[SQL] pobierz, plaintext 

3. Opcjonalnie, możemy dodać zdarzenie, które co godzinę będzie czyściło naszą tabelę z ewentualnego nadmiaru postów

[SQL] pobierz, plaintext 
 
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 ;
[SQL] pobierz, plaintext 

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

[PHP] pobierz, plaintext 
$query = mysql_query("CALL sp_insert('".$user_id."','".$msg."');");
 
$array = array();
while($row = mysql_fetch_assoc($query)) {
   $array[] = $row;
}
echo json_encode($array);
[PHP] pobierz, plaintext 

dodanie rekordu przy pomocy zapytań pisanych w php

[PHP] pobierz, plaintext 
mysql_query("INSERT INTO `chat_posts` (`user_id`, `post_text`) VALUES ('".$user_id."','".$msg."')");
 
$query = mysql_query("SELECT COUNT(*) FROM `chat_posts`");
$row = mysql_fetch_row($query);
 
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`");
     $row = mysql_fetch_row($query);
 
     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."'");
    $postId = mysql_fetch_assoc($query);
 
    // pobranie max. post_id w celu update'u w tabeli chat_users
    $query = mysql_query("SELECT MAX(`post_id`) FROM `chat_posts`");
    $maxPostId = mysql_fetch_row($query);
 
    //update user
    mysql_query("UPDATE `chat_users` SET `post_id` = '".$maxPostId."'");
 
    // pobranie najnowszych postów
    $query = mysql_query("
    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 = array();
    while($row = mysql_fetch_assoc($query)) {
        $array[] = $row;
    }
    echo json_encode($array);
[PHP] pobierz, plaintext 

Ten post edytował CuteOne 8.02.2013, 10:34:53