يك روش براي حل مشكل سرعت دستور LIMIT توي كوئري هامون

[Reza1607]

سلام
دوستاني كه با جدول ها با ركورد بالا كار كردن مي دونند كه وقتي بخوان 20 تا ركورد رو از اين جدول واكشي كنند چه زمان طولاني طول ميكشه (خيلي طول ميكشه :لبخند:)

امروز كه داشتم درباره اين موضوف تحقيق مي كردم به اين صفحه برخوردم (خيلي روشش باحال:لبخند:ه)
http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause

خوب من به زبان پارسي ميگم شايد دوستان كه آماتور هستن مثل خودم بتونن سرعت اجرا برنامه هاشون رو بالا ببرن


براي اين روش يك جدول به صورت زير درست مي كنيم
CREATE TABLE seq (
seq_no int not null auto_increment,
id int not null,
primary key(seq_no),
unique(id)
);

خوب اين جدول براي اين هست كه ما ترتيب ركورد هامون رو داخل قرار بديم ( يه جور ايندكس گذاري هستش)
براي پر كردنش هم بايد اين كار رو بكنيم
TRUNCATE seq;
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;
يعني يك بار جدول رو خالي مي كنيم بعد ميايم دوباره جدول رو براساس فيلد كليد جدول اصليمون پرش مي كنيم
اين كوئري دوم به سرور فشار مياره ولي بعدها خيلي بهمون حال ميده
اين دو خط كوئري رو زماني اجرا مي كنيم كه از جدول اصلي يك ركورد پاك بشه و زماني كه يه ركورد در جدول اصلي درج شد هم مي تونيم از كوئري بالا استفاده كنيم ولي من توصيه نمي كنم بهتره به جاش از دستور زير استفاده كنيم
INSERT INTO seq(id) VALUES($lastid)
كه $lastid همون شناسه آخرين ركورد درج شده در جدول اصلي هستش ( با دستور mysql_insert_id مي تونيد اين شناسه رو بدست بيارين)
و بعد وقتي مي خوايم تعدادي ركورد محدودي رو فراخواني بكنيم ميايم از JOIN استفاده مي كنيم
كه كوئريش ميشه اين
SELECT mytable.*
FROM mytable
INNER JOIN seq USING(id)
WHERE seq.seq_no BETWEEN 1000000 AND 1000999;

يعني ميايم تعداد 1000 روكورد رو از ركورد 1000000 به بعد از جدول ميگيريم
خوب يه توضيح هم درباره اين كوئري بدم
تو اين كوئري ما ميايم به جاي شرط گذاشتن رو جدول اصلي رو جدول ايندكس(seq) شرط ميزاريم و چون جدول ايندكسمون مرتب هستش شرطي كه گذاشتيم باعث ميشه همون كار LIMIT شبيه سازي بشه و در انتها با دستور JOIN ميايم اطلاعات اصلي رو فراخواني مي كنيم

[ahsaya]

بجز تشكر كردن واقعا جاي تبريك داره . تشكر

[ali2k5]

ببخشید ولی این چه کاریه ؟ شما روی فیلدی که میخواید به این صورت شرط تعریف کنید کافیه یک ایندکس تعریف کنید دیگه مشکلی در سرعت ندارید.
اگر ایندکس روی فیلد تعریف کنید سرعت 1000 برابر سریعتر خواهد بود اگر ایندکس تعریف نکنید تمام اطلاعات جدول یکبار مقایسه میشوند تا نتیجه برای کوئری شما ایجاد شود که طبیعتا خیلی کند خواهد بود. قطعا اوایل کار که جدول خالی هست متوجه این قضیه نمی شوید بعدا که حجم اطلاعات بالا میره متوجه کندی خواهید شد که طبیعتا با ایندکس گذاری صحیح حل میشه .

[ali2k5]

Showing rows 4029666 - 4029765 ( 100 total, Query took 0.7708 sec)
این هم مثال عملی روی یکی از سرور ها الان براتون تست کردم

روی یک جدول با 4 میلیون رکورد حجم 2.3 گیگ اطلاعات کوئری با یک ایندکس ساده در 0.7 ثانیه اجرا میشه دیگه چه کاریه بیای این کارها رو انجام بدی عزیزم.

[Unique]

روی یک جدول با 4 میلیون رکورد حجم 2.3 گیگ اطلاعات کوئری با یک ایندکس ساده در 0.7 ثانیه اجرا میشه دیگه چه کاریه بیای این کارها رو انجام بدی عزیزم.

راستش حرف شما درست نیست ! دستور limit روی جداول وقتی بزرگ میشن مخصوصا وقتی offset میگذارین خیلی کند میشه ! ایندکس گذاری تاثیر داره اما نه اینطور که شما میگین ! در ضمن اینکه جدول innodbباشه یا myisam هم خیلی تاثیر گذاره. روشی که رضا میگه بهش میگن temp table که روش بدی نیست و قابل استفاده و به قول شما کار الکی هم نیست ! قبلا توی انجمن بحث شده و از memory table ها هم میشه استفاده کرد که جناب شفیعی توی تاپیک مورد نظر من اشاره کردند و روش مناسبیه !

اما به نظر من اگه هدف از pageing رفتن به یک یا دو صفحه اینطرف اونطرف تره اجرا کردن این روش روی جداول بزرگ جالب نیست ! و میشه با دکمه های قبلی و بعدی بدون Offset با سرعت زیاد به تعداد مورد نظر صفحه ها را به کاربر نشون داد و افت سرعت هم نداشت. فکر کنم توی تاپیکی که قبلا اشاره کردم بیشتر توضیح دادم ! یک جستجوی بزنید پیدا میکنید. کاربر اگه میخواد چیزی را پیدا کنه بهتره جستحو کنه تا جدول را صفحه صفحه چک کنه ! شما مگه توی این انجمن ۱۰ صفحه ۱۰ صفحه چک میکنید ؟ غیر از اینه ؟ صفحه به صفحه ! حتی gmail هم اینطوریه ! تازه میشه برای کاربر امکانی گذاشت که بجای ۳۰ تا توی یک صفحه ۱۰۰ تا ببیه یا بیشتر ! pagination به روش معمول را بنده نمی پسندم اما گاهی مجبورم اجرا کنم.

[ali2k5]

راستش حرف شما درست نیست ! دستور limit روی جداول وقتی بزرگ میشن مخصوصا وقتی offset میگذارین خیلی کند میشه ! ایندکس گذاری تاثیر داره اما نه اینطور که شما میگین ! در ضمن اینکه جدول innodbباشه یا myisam هم خیلی تاثیر گذاره. روشی که رضا میگه بهش میگن temp table که روش بدی نیست و قابل استفاده و به قول شما کار الکی هم نیست ! قبلا توی انجمن بحث شده و از memory table ها هم میشه استفاده کرد که جناب شفیعی توی تاپیک مورد نظر من اشاره کردند و روش مناسبیه !

اما به نظر من اگه هدف از pageing رفتن به یک یا دو صفحه اینطرف اونطرف تره اجرا کردن این روش روی جداول بزرگ جالب نیست ! و میشه با دکمه های قبلی و بعدی بدون Offset با سرعت زیاد به تعداد مورد نظر صفحه ها را به کاربر نشون داد و افت سرعت هم نداشت. فکر کنم توی تاپیکی که قبلا اشاره کردم بیشتر توضیح دادم ! یک جستجوی بزنید پیدا میکنید. کاربر اگه میخواد چیزی را پیدا کنه بهتره جستحو کنه تا جدول را صفحه صفحه چک کنه ! شما مگه توی این انجمن ۱۰ صفحه ۱۰ صفحه چک میکنید ؟ غیر از اینه ؟ صفحه به صفحه ! حتی gmail هم اینطوریه ! تازه میشه برای کاربر امکانی گذاشت که بجای ۳۰ تا توی یک صفحه ۱۰۰ تا ببیه یا بیشتر ! pagination به روش معمول را بنده نمی پسندم اما گاهی مجبورم اجرا کنم.


بنظر من راه های ابداعی هم خوبه ولی روش های استاندارد بهتره به اینجا سر بزنید:

http://devoluk.com/mysql-limit-offset-performance.html

تاپیک های قبلی هم اصلاح کنید که بقیه از روش های استاندارد استفاده کنند.

[rezaonline.net]

روشی که من روی جداول بزرگ و میشه گفت درحال حاضر روی تمامی داده ها استفاده میکنم به این صورته
در صفحه ای که لیست اطلاعات نشون داده میشه فرض کنید 20 آیتم نشون داده میشه از آیتم با شناسه 30 تا شناسه 60
صفحه بعدی رو به این صورت اجرا میکنم که
select * from tbl where id<60 limit 30
و صفحه قبلی رو
select * from tbl where id>30 limit 30
یه خورده پیچیده تر هست اما چند خاصیت مهم داره و اونم اینکه سرعت به شدت بالا میره چون فقط 30 رکورد جستجو میشه و نیازی نیست تمام رکوردها استخراج بشه تا به رکورد مثلا 60 تا 90 رسید برای صفحه بعد .
خاصیت بعدی هم شاید در زمانی که شما دارید این صفحه اطلاعات رو مشاهده میکنید داده به جدول اضافه بشه ، لذا در روش قبلی مثلا صفحه 3 ادامه صفحه 2 نیست چون تعداد آیتم ها تغییر کرده پس محتوای هر صفحه هم تغییر میکنه که در روشی که من پیاده میکنم چون از id انتخابی شروع به شمارش میشه این مشکل وجود نداره

[Unique]

http://devoluk.com/mysql-limit-offset-performance.html
راستش روشی که توی این صفحه گفته خیلی خیلی مشکوکه که جواب بده ! این داره میگه جدول را روی limit خودش join کن ! حالا یا سواد من کمه و join در این حالت مانع از واکشی کل اطلاعات جدول میشه ! که خیلی بعیده و من حتما این موضوع را چک میکنم.

اما حرف من دقیقا همینی هست که rezaonline عزیز توضیح داد و در این حالت دیگه ما Offset نداریم که کند کنه query را ! خیلی راحت با نگه داشتن آخرین id و استفاده زا بزرگتر و کوچکتر و limit بدون offset میتونیم بین صفحات حرکت کنیم !


تاپیک های قبلی هم اصلاح کنید که بقیه از روش های استاندارد استفاده کنند.
والا من خیلی در زمینه Pagination چه توی mssql و چه توی mysql تحقیق کردم و تا دلت بخواد روش دیدم ! راه های خیلی زیادی هست اما موضوع استانداردی وجود نداره و مثال شما هم با حرف پستی که زدین و گفتین با یک index گذاری روی فیلد های where و order by همه چیز حله نمیخونه و خودتون حرف پست اول را نفی کردین !

[ali2k5]

راستش روشی که توی این صفحه گفته خیلی خیلی مشکوکه که جواب بده ! این داره میگه جدول را روی limit خودش join کن ! حالا یا سواد من کمه و join در این حالت مانع از واکشی کل اطلاعات جدول میشه ! که خیلی بعیده و من حتما این موضوع را چک میکنم.

اما حرف من دقیقا همینی هست که rezaonline عزیز توضیح داد و در این حالت دیگه ما Offset نداریم که کند کنه query را ! خیلی راحت با نگه داشتن آخرین id و استفاده زا بزرگتر و کوچکتر و limit بدون offset میتونیم بین صفحات حرکت کنیم !


والا من خیلی در زمینه Pagination چه توی mssql و چه توی mysql تحقیق کردم و تا دلت بخواد روش دیدم ! راه های خیلی زیادی هست اما موضوع استانداردی وجود نداره و مثال شما هم با حرف پستی که زدین و گفتین با یک index گذاری روی فیلد های where و order by همه چیز حله نمیخونه و خودتون حرف پست اول را نفی کردین !


روش که جواب میده مشکوک نیست این یک ساب کوئری هست که از مفاهیم پایه اس کیو ال حساب میشه داخل همان صفحه هم امده نمودار کشیده و گفته چقدر تفاوت داره ... پس روش اوکی هست راحت میتونید برید داخل یکی از جدول های دیتابیس تست کنید.

در مورد روش rezaonline عملا شما دیگه offset ندارید و بازم میگم روش ابداعی خوبه ولی بهترین نیست !

من نمیدونم در مورد عملکرد مای اس کیو ال چقدر اشنایی دارید ولی اگر از روش ایندکس که گفتم استفاده کنید این اتفاق میوفته


id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 30
1 PRIMARY com eq_ref PRIMARY PRIMARY 3 t.id 1
2 DERIVED com index NULL PRIMARY 3 NULL 400030 Using index
به ترتیب از ایندکس فقط برای پیداکردن ایدی ها استفاده میشه و فقط روی تعداد رکورد افست نه همه رکوردها در مرحله دوم تعداد محدود ایدی ها که توسط لیمیت مشخص شدن برابر کلید اصلی قرار میگیرند و در مرحله سوم فقط همان رکوردهایی که خواستید بازگردونده میشن

ولی در روش rezaonline علاوه بر از دست دادن offset و داستان های مرتب به این قضیه این اتفاق میوفته

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE com range PRIMARY PRIMARY 3 NULL 3924501 Using where

اول اینکه روی تمام رکوردها این مقایسه انجام میشه یک مرحله where هم اضافه میشه که اگر اینجا کلید اصلی نبود یعنی همون ایندکسی که صحبتش هست اینجا یک file sort هم اضافه میشد.
این روش صرفا به خاطر این سریع هست که شما offset رو حذف کردید یا به عبارتی یه تیکه از صورت مسئله را پاک کردید.

چیزی که من در پست قبلی گفتم تناقضی با کوئری که معرفی کردم نداره یک جدول هست که ایندکس گذاری شده و روی خودش join شده چه تناقضی داره ؟

[rezaonline.net]

روش بالا جواب میده ، جوین روی خود جدول مشکلی نداره اما در کل روی رکوردهای چند میلیونی بازم ارزش نداره این مقدار رکورد محاسبه بشه .
مسلما همه صفحه بندی های ما براساس یک سورت بندی نیست ، حتما چندین شرط در کوئری لحاظ میشه که این خودش در عمل نشون میده بازم سایر فیلدهای جدول رو به اجبار درگیر میکنه .
روش روش خوبیه اما بازم رغبتی برای استفاده ازش نیست ، خود جوین هم قاتل سرعت هست .
روشی که بنده گفتم به قول شما با حذف آفست به راحتی میشه روی n تعداد رکورد مانور داد .