Salom. Agar kimdakim DB (Database, Ma'lumotlar bazasi) bilan ishlagan bo'lsa, demak ko'pchiligik sarlash (sortirovka)ga duch kelgan. Oddiy SQL tilida ORDER BY. Lekin bilamizki bu ORDER BY juda sekin ishlaydi. Buni tezlashtirish yo'llari ham mavjud. Keling shu haqida gaplashsak.

Mani yozmoqchi bo'lganlarim MySQL misolida. Lekin bu usulni boshqa DB larga ham o'tkazsa bo'ladi. Keling man misol sifatida social network (sotsial tarmoq) ni oliy. Ya'ni undagi ichidagi funksiyalarning bazada strukturasi haqida gaplashamiz.

Endi asosiy maqsadga o'tkas, ORDER BY ni tez ishlaydigan holatga olib kelish.

1-holat. MySQL ning o'zida ORDER BY ni tezroq ishlatish uchun index lardan foydalansa bo'ladi. Ya'ni ayrim hollarda, jumlada:

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

Misollar dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html dan olindi. Ya'ni saralanishi lozim bo'lgan ustun index ko'rinishida yaratiladi. Lekin bunda ham qandaydir chegaralar mavjud. Ya'ni tepada ko'rsatilgan holatlardagina INDEX dan foydalaniladi. Aks holda oldingiday holatda ishlatiladi.

Bu haqida MySQL saytlarida va ya'na bir qancha internetdagi resurslardan qidirib topsangiz bo'ladi.

Keling endi man qanday yo'l tutganim haqida yozsam. Yaqinda ayrim sabablarga ko'ra social network yozishni boshladim. Man ishni boshlashdan oldin manga juda tez ishlaydigan DB kerak bo'ldi. Qidirib qidirib ya'na MySQL da qoldim. Chunki MySQL da opennet.uz/itnews/mysql-memcache-ni-ham-orqada-qoldirdi.html maqolada yozganimda HandlerSocket pluginini topdim. Bu yerdagi eng katta hususiyat INDEX larda juda katta tezlikda ma'lumotni qidirb topib beradi. Demak DB shunday tuzish kerakki barcha jadvallar INDEX lardan iborat bo'lishi va manga kerak holatda ishlashi lozim edi.

Birinchi chiqqan muammo lenta novosti. Ya'ni o'zingiz yozgan post sizning lentangizda va do'stlaringizning letasida chiqishi kerak. Chiqqandayam qo'shilgan vaqti bo'yicha kamayish tartibida bo'lishi lozim. Umuman

SELECT * FROM user_newsfeed WHERE id_user = {id} ORDER BY datetime_added DESC LIMIT {n},{m}

Bu oddiy SQL tilida yozilishi, lekin man topgan plugin ORDER BY ni qo'llamaydi. Man boshqacharoq yo'lga o'tishim kerak bo'lib qoldi. Ochig'i ko'p o'yladim va quyidagi yechimni topdim.

users jadvaliga user_post_count, newsfeed_post_count ustunlarini qo'shdim. Birinchi ustun user nechta post qo'shganligi haqidagi ikkinchisi userning lentasida netchta post borligini ko'rsatadi.

Va qolgan jadvallar:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` bigint(20) unsigned NOT NULL,	
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `text` text NOT NULL,
  `post_info` text,
  `datetime_added` datetime NOT NULL,
  `user_post_counter` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user_newsfeed` (
  `id_user` bigint(20) NOT NULL,
  `counter` bigint(20) NOT NULL,
  `id_post` bigint(20) NOT NULL,
  `id_from` bigint(20) unsigned NOT NULL,
  `datetime_added` datetime NOT NULL,
  KEY `POST_COUNTER` (`id_user`,`counter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `user_posts` (
  `id_user` bigint(20) unsigned NOT NULL,
  `counter` bigint(20) unsigned NOT NULL,
  `id_post` bigint(20) unsigned NOT NULL,
  `datetime_added` datetime NOT NULL,
  PRIMARY KEY (`id_user`,`counter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Ushbu jadvallarga izoh keltirsam.

`posts`:
id — bu tushinarli, takrorlanmas son ya'ni post id si
id_user — postni kim qo'shgani

`user_newsfeed` — asosiysi
id_user — kimga qo'shilgan (ya'ni ID=1 bo'lgan user letasidagi postlar)
counter — ushbu userning nechanchi posti (batafsil pastroqda)
id_post — qaysi post, post id
id_from — kim tomonidan

`user_posts`:
id_user — kim qo'shganligi
counter — ushbu userning qo'shgan postlariga qarab INCREMENT bo'ladi. Agar 10 ta post qo'shgan bo'lsa, 1-10 gacha qiymat oladi.
id_post — post id si

Qolganlari qo'shimcha ma'lumotlar. Jadvallarga e'tibor bering, ayniqsa KEY va PRIMARY KEY larga.

Endi tasavvur qiling sizning 10 ta do'stingiz bor. Agar siz bitta post yozsangiz, siz yozgan post birinchi `posts` jadvaliga tushadi, u yerda ID si olinadi va `user_post_count` qiymati bittaga oshirilib `user_posts` jadvaliga yoziladi `user_post_count` oshgandagi qiymati `counter` ga yoziladi va `user_newsfeed` jadvaliga quyidagicha qo'shiladi.

sizni va do'stlaringizni ID lari bitta o'zgaruvchi ($ids = array(1, 2, 3, ..., 11) ga olinadi va har bir userning `newsfeed_post_count` qiymati bittaga oshirilib `user_newsfeed` jadvaliga qo'shiladi. Bunda har biriga mos holda `counter` o'zgaruvchisi `newsfeed_post_count` ning oxirgi qiymatini saqlaydi. Misol tariqasida: id_post = 11 bo'lsin

Userlar

id=1, newsfeed_post_count = 5
id=2, newsfeed_post_count = 9

bo'lsa shu ikkalasiga `user_newsfeed` quyidagi ko'rinishda bo'ladi

id_user, counter, id_post
1	6	11
2	10	11

va userlar

id=1, newsfeed_post_count = 6
id=2, newsfeed_post_count = 10

ko'rinishga o'tadi.

Bu yerda ya'na bitta muammo bor.

Endi tasavvur qiling, uch kichi bir biri bilan do'st. ID lari 1,2,3 bo'lgan userlar o'zaro do'st. Ya'ni 1-2,3 bilan, 2-1,3 va 3-1,2 bilan. Agar mani holatimda uchala user birdaniga post yozsa nima bo'ladi?

Kelin sekin qadamma-qadam tushintiray. Umuman PHP dagi kod quyidagicha

public static function insertPost($text, $values = array())
{
	//o'zining letasiga qo'shish
	list($pid, $post_count) = self::insertPostPerUser(Yii::app()->user->user, Yii::app()->user->id, $text, $values);

	//do'stlarini olish
	$friends = UserFriendsModel::hsFindAllByIndex(Yii::app()->user->id, 'id_user2,status', 0x00FFFFFF);

	if ($friends)
	{
		$values['post_id'] = $pid;

		foreach($friends as $friend)
		{
			//agar do'sti bo'lmasa qo'shmaydi
			if ($friend->status != 2)
				continue;

			//do'stlariga qo'shish
			self::insertPostPerUser($friend->id_user2, Yii::app()->user->id, $text, $values);
		}
	}

	//user_posts ga qo'shish
	$lockKey = "user_post_".Yii::app()->user->id;
	if (MutexHelpers::lock($lockKey, 5))
	{
		$user = UsersModel::hsFindByIndex(Yii::app()->user->id, "id,user_post_count");
		$user_post_count = $user->user_post_count + 1;

		UsersModel::hsUpdate($user->id, array(
			"user_post_count" => $user_post_count
		));

		UserPostsModel::hsInsert(array(
			"id_user" => $user->id,
			"counter" => $user_post_count,
			"id_post" => $pid
		));

		MutexHelpers::releaseLock();
	}

	return $post_count;
}

//har bitta user uchun post qo'shadi
public static function insertPostPerUser($id, $id_from, $text, $values)
{
	$post_count = false;
	
	$lockKey = "user_".$id;
	if (MutexHelpers::lock($lockKey, 5))
	{
		$user = UsersModel::hsFindByIndex($id, "newsfeed_post_count");
		$post_count = $user->newsfeed_post_count;

		$post_count += 1;

		UsersModel::hsUpdate($id, array(
			"newsfeed_post_count" => $post_count
		));

		MutexHelpers::releaseLock();
	}

	$values['id_user'] = $id;
	$values['text'] = $text;

	//birinchi savar qo'shilganda `posts` ga qo'shadi
	if ( ! isset($values['post_id']))
		$pid = PostsModel::hsInsert($values);
	else
		$pid = $values['post_id'];

	UserNewsfeedModel::hsInsert(array(
		"id_user" => $id,
		"counter" => $post_count,
		"id_post" => $pid,
		"id_from" => $id_from
	));

	return array($pid, $post_count);
}

Agar kodni o'rgangan bo'lsangiz MutexHelpers mavjud, bu haqida sal pastroqda tushuntiraman. Endi muammoga qaytamiz, ya'ni uchala user birdaniga post yozgandi. Birdaniga bu, soati, sekundi, milli sekundi bir vaqtda bo'lgan vaqtdi. Bu holat ehtimoldan ancha uzoq lekin do'stlaringiz ko'paygani sari bu ehtimol oshib boradi. Biz dasturchilar esa 0 dan katta ehtimollikka ega har qanday vaziyatni oldini olish lozim.

Demak uchala user birdaniga post yozishdi va birdaniga ENTER ni bosishdi. PHP kod qatorma-qator ishlab bizni quyidagi kodga kelishdi (uchala sessiya ham)

...
1.	$lockKey = "user_".$id; //uchala sessiya ham shu yerda turibdi
2.	$user = UsersModel::hsFindByIndex($id, "newsfeed_post_count");
3.	$post_count = $user->newsfeed_post_count;
	...

Hozircha MutexHelpers bizga shartmas. Endi uchala sessiya uchun ham DEBUG qilgandan qadamma-qadam pastga tushsak, 2-qatorda $id bo'yicha userning `newsfeed_post_count` qiymatini olamiz. Uchalasi bir biri bilan do'stligi uchun $id bir xil bo'lish ehtimoli bor. Demak $id uchla sessiya uchun ham bir xil bo'lsin. Shudan `newsfeed_post_count` qiyma uchala sessiya uchun ham bir xil qiymat ya'ni `users` jadvalidagi `newsfeed_post_count` qiymatni olishadi va uchlasi ham bittaga oshirib yana `users` jadvalga `newsfeed_post_count` + 1 (bir marotaba oshgan) qiymatni yozishadi. Aslida $id=1 bo'lgan userga 3 ta post qo'shilgandi. Bizni holatda `user_newsfeed`.`counter` ustunida dublikat paydo bo'lardi.

Endi bu muammoni oldini olish uchun LOCK tushunchasidan foydalanamiz. Ya'ni koddagi

$lockKey = "user_".$id;
if (MutexHelpers::lock($lockKey, 5))

kodni qo'shamiz. Ushbu kodni quyidagicha tushuntirsam. Bizni holatda $id=1 edi, demak uchala sessiyada ham $lockKey = «user_1» bo'ladi. MutexHelpers::lock bo'lsa, «user_1» bo'yicha birinchi sessiyani (umuman ketma ketlikdagi birinchi sessiyani) ga ruxsat berib ikkinchi va uchinchi sessiyalar «user_1» bo'lganligi uchun shu yerda kutib turishadi. To 1-sessiya

MutexHelpers::releaseLock();

ni chaqirmaguncha. Ya'ni shu joyga kelganda $lockKey ga qarab $id lar bir xil bo'lishidan qatiy nazar `counter` o'zgaruvchisi takrolanmasdan INCREMENT bo'lib ketaveradi. MutexHelpers esa quyidagicha

class MutexHelpers
{
    public static $lockKeyStack = array();
    public static function lock($key, $timeout)
    {
        self::$lockKeyStack[] = $key;
        return Yii::app()->db->createCommand("SELECT COALESCE(GET_LOCK('$key', $timeout), 0)")->queryScalar();
    }

    public static function releaseLock($key = false)
    {
        if ($key === false)
            $key = array_pop(self::$lockKeyStack);

        return Yii::app()->db->createCommand("SELECT RELEASE_LOCK('$key')")->queryScalar();
    }
}

Hozircha PHP da multi session lock bo'yicha faqat shu usulni ishlayapman. Bundan tashqari va tezroq ishlaydigan biblatekalar mavjud. PHP Semaphore, PHP shmop lardan foydalanib ham shu natijaga erishsa bo'ladi.

Keling endi asosiy muammiz ORDER BY ga qaytamiz. Agar maqolani e'tibor bilan o'qigan bo'lsangiz manimcha allaqachon tushinib bo'lgansiz. Endi man lentani o'qishda quyidagicha usuldan foydalandim.
Birinchi = `users` jadvalidan `newsfeed_post_count` ni qiymatini olaman va $pageSize = 10 (ya'ni o'ntadan kor'satish kerak bo'lsa) va $start = $newsfeed_post_count, $end = $newsfeed_post_count — $pageSize. Bundan

for($i = $start; $i > $end && $i > 0; $i--)
{
	$user_newsfeed = UserNewsfeed::hsFindByIndex(array($id_user, $i)); //$i - bu yerda counter qiymati
	$post = PostsModel::hsFindByIndex($user_newsfeed->id_post);
	...
}

Ko'rib turganingizday sartirovkani oldindan belgilab ketdik. Ya'ni o'zimiz o'sib boradigan o'zgaruvchi yaratdik. Shu usuldan foydalanib boshqa muamolaringizni ham hal qilsangiz bo'ladi. Bu usulni bitta kamchiligi. Agar `user_newsfeed` jadvalidan ma'lumotlar o'chiriladigan bo'lsa `counter` qiymati orasida bo'shliq paydo bo'ladi. Ya'ni 1,2,3,5,8,11 ko'rinishida bo'lishi mumkin.

Lekin facebook, odnoklassnikida o'zini statusini o'zgartirgan yoki post yozgan inson deyarli shu postni o'chirmaydi. Shuni e'tiborga olib ushbu usul yordamida muammoni hal qildim. Va HandlerSocket ning ORDER BY ni yo'qligini hisobga olib ham shuning bor imkoniyatindan to'liq foydalandim va foydalanyapman ham.

Ya'na HandlerSocket ni ishlatmasdan ham shu usulda, ya'ni INSERT kam bo'ladigan lekin SELECT ko'p ishlatiladigan jadvallarda ORDER BY ni ishlatmasdan

SELECT * FROM {table} WHERE id_user = 1 AND `counter` IN (1, 2, 3, ..., 10)

Ko'rinishida yozib PHP da sort, ksort lardan foydalansaigiz yoki MySQL ning o'zidagi ORDER BY FIELD(`counter`, 1, 2, 3,… 10) dan foydalansangiz bo'ladi. Bu ancha tez ishlaydi. Chunki ORDER BY butun boshli jadvalni sartirovka qilmaydi. Agar jadvalda ma'lumot juda katta bo'lasa albatta.

Ochig'i oxirgi kunlarda 2-3 ta proyekt qilib DB da har qanday muammoni hal qilish mumkinligiga ishonch hosil qilyapman. Faqat hamma jadvallarni to'g'ri yarata olish lozim.

Agar tushunmovchiliklar bo'lsa kutaman.