• 0 رای - 0 میانگین
  • 1
  • 2
  • 3
  • 4
  • 5
دنبال راه حلی برای حجم داده زیاد و کندی کارها
#1
سلام دوستان

بنده یه سایت نوشتم که یکی از جداولش الان حدود 13 میلیون رکورد داره ولی کار کردن باهاش کند شده . بهش ایندکس هم اضافه کردم ولی فرقی نکرد هیچ بدتر هم شد .
می خواستم بدونم راه بهینه کردنش چیه به نظرتون ؟ پارتیشن کردن جدول ؟ یا استفاده از nosql و این چیزا ؟

ساختار جدول :

کد:
--
-- Table structure for table `members`
--

CREATE TABLE `members` (
  `id` int(10) UNSIGNED NOT NULL,
  `clan_id` int(10) UNSIGNED DEFAULT NULL,
  `tag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `role` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `expLevel` int(11) NOT NULL,
  `league` int(10) UNSIGNED NOT NULL,
  `trophies` int(11) NOT NULL,
  `attackWins` int(11) NOT NULL,
  `defenseWins` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `previousRank` int(11) NOT NULL,
  `clanRank` int(11) NOT NULL,
  `previousClanRank` int(11) NOT NULL,
  `donations` int(11) NOT NULL,
  `donationsReceived` int(11) NOT NULL,
  `isTopPlayer` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `members`
--
ALTER TABLE `members`
  ADD PRIMARY KEY (`id`),
  ADD KEY `members_clan_id_foreign` (`clan_id`),
  ADD KEY `members_league_foreign` (`league`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `members`
--
ALTER TABLE `members`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `members`
--
ALTER TABLE `members`
  ADD CONSTRAINT `members_clan_id_foreign` FOREIGN KEY (`clan_id`) REFERENCES `clans` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `members_league_foreign` FOREIGN KEY (`league`) REFERENCES `leagues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

پیشاپیش از راهنمایی تون ممنونم .
  پاسخ
تشکر شده توسط :
#2
شما هر تغییری در دیتای بازیکن رو آنی در دیتابیس ثبت می کنی؟
وبلاگ: Yousha.Blog.ir


 کد کمتر => خطای کمتر => قابل فهمتر => خوانایی بالاتر => نگهداری بهتر

  پاسخ
تشکر شده توسط : IRANPHP
#3
نه آنی ولی هر شب دیتاهای همه بازیکن ها رو با cron job ذخیره می کنم .
  پاسخ
تشکر شده توسط :
#4
در انتخاب دیتاتایپ ها کمی بیشتر دقت کنید
فیلد های int رو بصورت unsigned بذارید

به عقیده من مثلا `isTopPlayer` int(11) NOT NULL, به جای int میتونست tinyint باشه !
یا حتی BIT یا boolean !

نقل قول:الان حدود 13 میلیون رکورد داره ولی کار کردن باهاش کند شده
13 میلیون زیاد نیست به شرطی که ببینیم چه عملیاتی روش انجام میشه
کار کردن یعنی چی ؟ select کند هست ؟ insert کند هست ؟؟
کند یعنی چی ؟ یعنی مثلا هر دستور sql چند ثانیه تایم میبره؟؟
شاید پردازشت بالاست !! سرورت قوی نیست

اگر select کند هست باید روی ستون هایی که شرط زیاد میخوره ایندکس بذاری
اگر insert,update کند هست به عقیده من یکی از علتهاش میتونه کلید خارجی زیاد باشه !

بهرحال شما یه تیبل گذاشتید و گفتید کنده ، روی این اطلاعات فقط میشه چشم بسته غیب گفت
وبلاگ rezaonline.net/blog
سفارش برنامه نویسی reza.biz
Php , mysql , postgresql , redis , Yii and ... Cool
  پاسخ
تشکر شده توسط : Y.P.Y ayoubsys IRANPHP
#5
ممنونم.درسته در دیتا تایپ باید بیشتر دقت کنم .

منظور از کندی بیشتر نمایش و select هست . در موارد استفاده از join یا group by مخصوصا افت سرعت زیاد هست . سعی کردم به جای اینکه کل نتایج رو group by کنم یه بخشی رو اول بدست بیارم بعد اون بخش رو group by کنم کمی بهتر شد ولی مشکلش اینه دیگه کل جدول دستت نیست و فقط مثلا نتایج اخیر دسترسی داری. یعنی این کد اولیه رو :

کد:
SELECT * FROM members
INNER JOIN (select name,tag, max(id) as maxid from members group by tag) as b
INNER JOIN (select id, name as league_name, tiny as league_icon from leagues) as leagues
WHERE isTopPlayer = 1
ORDER BY members.trophies DESC
LIMIT 0,100

تبدیل کردم به این کد :

کد:
SELECT * FROM  (
                                                SELECT mbrs.* FROM members mbrs
                                                WHERE mbrs.isTopPlayer = 1
                                                ORDER BY trophies DESC
                                                LIMIT 0, 100
                                            ) AS main
                                            INNER JOIN (
                                                SELECT id, name as league_name, tiny AS league_icon FROM leagues
                                            ) AS leagues
                                            GROUP BY main.tag
                                            ORDER BY main.trophies DESC

در مورد ایندکس از همین جدول یه کپی گرفتم برای مقایسه و برای فیلدهای مهمش ایندکس اضافه کردم ولی تاثیر زیادی نداشت گاهی بدتر هم می شد نتیجه . کد جدول دوم :

کد:
CREATE TABLE `members_new` (
  `id` int(10) UNSIGNED NOT NULL,
  `clan_id` int(10) UNSIGNED DEFAULT NULL,
  `tag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `role` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `expLevel` int(11) NOT NULL,
  `league` int(10) UNSIGNED NOT NULL,
  `trophies` int(11) NOT NULL,
  `attackWins` int(11) NOT NULL,
  `defenseWins` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `previousRank` int(11) NOT NULL,
  `clanRank` int(11) NOT NULL,
  `previousClanRank` int(11) NOT NULL,
  `donations` int(11) NOT NULL,
  `donationsReceived` int(11) NOT NULL,
  `isTopPlayer` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `members_new`
--
ALTER TABLE `members_new`
  ADD PRIMARY KEY (`id`),
  ADD KEY `members_clan_id_foreign` (`clan_id`),
  ADD KEY `members_league_foreign` (`league`),
  ADD KEY `id` (`id`,`tag`,`expLevel`,`trophies`,`isTopPlayer`,`created_at`,`updated_at`),
  ADD KEY `id_2` (`id`,`tag`,`expLevel`,`trophies`,`isTopPlayer`,`created_at`,`updated_at`),
  ADD KEY `id_3` (`id`,`tag`,`expLevel`,`trophies`,`rank`,`isTopPlayer`,`created_at`,`updated_at`);

برای پیدا کردن کوئری های کند Slow Query Log رو فعال کرده بودم که این نتایجش بود :

کد:
# Time: 160801 19:41:23
# [email protected]: XXXXXX_4qWl[XXXXXX_4qWl] @ localhost []
# Thread_id: 1  Schema: XXXXXX_XXX  QC_hit: No
# Query_time: 16697.235394  Lock_time: 0.522025  Rows_sent: 0  Rows_examined: 8838735
SET timestamp=1470080483;
SELECT * FROM members
INNER JOIN (select name,tag, max(id) as maxid from members group by tag) as b
INNER JOIN (select id, name as league_name, tiny as league_icon from leagues) as leagues
WHERE isTopPlayer = 1
ORDER BY members.trophies DESC LIMIT 0, 25;

# [email protected]: XXXXXX_4qWl[XXXXXX_4qWl] @ localhost []
# T# Time: 160801 19:41:22
# [email protected]: XXXXXX_4qWl[XXXXXX_4qWl] @ localhost []
# Thread_id: 34  Schema: XXXXXX_XXX  QC_hit: No
# Query_time: 16128.218756  Lock_time: 0.000026  Rows_sent: 0  Rows_examined: 0
SET timestamp=1470080482;
select count(*) as aggregate from `members` inner join (select name,tag, max(id) as maxid from members group by tag) as b on `b`.`maxid` = `members`.`id` inner join (select id, name as league_name, tiny as league_icon from leagues) as league on `league`.`id` = `members`.`league` where `isTopPlayer` = '1';

# [email protected]: XXXXXX_4qWl[XXXXXX_4qWl] @ localhost []
# Thread_id: 66  Schema: XXXXXX_XXX  QC_hit: No
# Query_time: 14837.185655  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
SET timestamp=1470080453;
select name,tag, max(id) as maxid from members group by tag LIMIT 100;

# [email protected]: XXXXXX_4qWl[XXXXXX_4qWl] @ localhost []
# Thread_id: 62  Schema: XXXXXX_XXX  QC_hit: No
# Query_time: 15198.010596  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
SET timestamp=1470080453;
select name,tag, max(id) as maxid from members group by tag LIMIT 100;
  پاسخ
تشکر شده توسط :
#6
کد:
ADD KEY `id` (`id`,`tag`,`expLevel`,`trophies`,`isTopPlayer`,`created_at`,`updated_at`),
  ADD KEY `id_2` (`id`,`tag`,`expLevel`,`trophies`,`isTopPlayer`,`created_at`,`updated_at`),
  ADD KEY `id_3` (`id`,`tag`,`expLevel`,`trophies`,`rank`,`isTopPlayer`,`created_at`,`updated_at`​);
Dodgy

اینا ایندکس های چند گانه/ترکیبی هستن ، و زمانی بهشون رجوع میشه که کلیه فیلدهای داخل ایندکس در شرط بصورت and با هم اجرا بشن !!!

در اصل هیچ کدوم از این ایندکس هات هیچ وقت استفاده نمیشه بدترشم میکنه چون حجم رو بشدت میبره بالا!!
http://dev.mysql.com/doc/refman/5.7/en/m...dexes.html

همچنین جوین هات فوق العاده عجیب غریبه Big Grin
خودت سر در میاری چی نوشتی ؟؟؟
سعی کن چند تا ویو بسازی ، تا جوین هات ساده تر بشه .
http://dev.mysql.com/doc/refman/5.7/en/view-syntax.html
وبلاگ rezaonline.net/blog
سفارش برنامه نویسی reza.biz
Php , mysql , postgresql , redis , Yii and ... Cool
  پاسخ
تشکر شده توسط : IRANPHP Y.P.Y
#7
نقل قول:اینا ایندکس های چند گانه/ترکیبی هستن ، و زمانی بهشون رجوع میشه که کلیه فیلدهای داخل ایندکس در شرط بصورت and با هم اجرا بشن !!!
نمی دونستم ممنون حتما تست می کنم نتیجه رو عرض می کنم.

نقل قول:همچنین جوین هات فوق العاده عجیب غریبه Big Grin
خودت سر در میاری چی نوشتی ؟؟؟
Smile
به نظر پیچیده است دقت کنین خیلی هم ساده است .فقط group by و order by رو آوردم آخر کوئری که روی آخرین نتایج بدست آمده که محدودن اعمال بشن نه داخل ساب کوئری ها روی همه رکورد ها
  پاسخ
تشکر شده توسط :
#8
تعداد رکوردهای هر جدول رو جدا جدا بزار. برای اینکه instert و update هم سریعتر بشه بهتره که کلید های خارجی رو حذف کنی. شاید از لحاظ طراحی دیتابیس اشتباه باشه ولی از لحاظ پرفورمنس مطمئن باش تاثیر زیادی روی instert update و delete داره و بهتره cascade ها رو خودت دستی هندل کنی
  پاسخ
تشکر شده توسط : ayoubsys
#9
inner join هات چرا اصلا ON نداره؟
  پاسخ
تشکر شده توسط :
#10
با سلام دوستان
چند وقتی هست فشار روی سرور زیاد شده.
ما دستور slow log query رو فعال کردیم . و الان وقتی خروجی می گیریم بشکل زیر میاد :
کد:
کد:
[[email protected]******** ~]# mysqldumpslow -t 5 -s at /var/log/slow.log

Reading mysql slow query log from /var/log/slow.log
Count: 1  Time=162.18s (162s)  Lock=0.00s (0s)  Rows_sent=52.0 (52), Rows_examined=124.0 (124), userali_jahan[userali_jahan]@localhost
  SELECT folder AS type, element AS name, params
  FROM j3gan_extensions
  WHERE enabled >= N AND type ='S' AND state >= N AND access IN (N,N)
  ORDER BY ordering

حالا ما چطور مشکل این دیتابیس رو حل کنیم ؟
ممنون از همکاریتان
  پاسخ
تشکر شده توسط :


پرش به انجمن:


کاربران در حال بازدید این موضوع: 1 مهمان