Tối ưu hóa so sánh trùng csdl

[mattroi_viendong]

Xin phép mod tí, subject này đáng nhẽ phải ở bên hệ csdl, nhưng e đã đăng bài bên đấy nhưng ko có ai respond cả, vấn đề của e lại đang rất cấp thiết, thấy bên này giải quyết vấn đề csdl rất nhiều nên nhờ mod tí nhé.

e đang làm code lấy dữ liệu từ rss các báo. (ví dụ:http://vnexpress.net/rss/gl/xa-hoi.rss)

khi insert tin vào csdl , em so sánh title (ví dụ: "Quý bà đâm ôtô vào trường mầm non, một người chết") đã có trong cơ sở dữ liệu chưa em dùng như sau:

(bảng chứa tin gồm 4 cột id, title , description, date)

$sql_check="SELECT id FROM `news` WHERE title='$title' and date >= DATE_SUB(CURDATE(), INTERVAL 100 DAY)";
$query_check=mysql_query($sql_check);

//nếu $query rỗng ~ tin chưa có trong csdl thì insert
if(@mysql_num_rows($query_check) == "" ) {
$sql= "insert into news (title,description,date) values ('$title','$description',now)";
mysql_query($sql);
}

code chạy gần một tuần thì rất tốt. Nhưng khi dữ liệu đã nhiều. Khi row đã lên tới 100.000 thì việc so sánh này khá là nặng, đặc biệt biệt là khi câu lệnh so sánh và insert này được đặt trong vòng lặp while, vì lúc bóc dữ liệu từ một list các rss khác nhau, mỗi title sẽ được so sánh một lần.(mỗi lần lướt qua cái list rss nó phải so sánh cỡ khoảng 15.000 cái title,nghĩa là 1 lần chạy file sẽ check 15.000 title vs 100.000 row hiện có trong bảng, số một 100.000 này sẽ tăng theo thời gian).

Trong thời gian chạy thử, em đã làm host sập 3 lần. Thấy chủ nó kêu ghê quá nên chuyển host. Chuyển sang host mới đc hơn một tuần host mới die luôn.

Qua tìm hiểu em cũng tìm thấy một số cách tối ưu hóa sau đây, cả nhà chỉ cho em cách nào là optimize nhất nhé:

vấn đề 1. Tạo thêm cột title_md5 để chứa title mã hóa dưới dạng md5, khi so sánh có lẽ sẽ nhẹ hơn.

2. cột lưu trữ mã hóa md5 thì nên để kiểu dữ liệu gì varchar(?bao nhiêu ký tự), hay text....?

3. Set full text search, unique index cho cột title_md5 để truy vấn cho nhanh (vấn đề là khi truy vấn dữ liệu dạng này thi nên set unique index hay full text search hay cả hai).

4. có phải nếu dùng unique index thì nó sẽ ko cho insert title,description khi mà md5_title đã tồn tại trong csdl, nếu đc như vậy thì quá tốt vì đỡ phải so sánh mất thêm một query so sánh đúng ko ạ ?

5. mỗi lần insert dữ liệu vào bảng có phải thêm lệnh create index lại cho bảng ko hay chỉ cần create index một lần, về sau khi insert bảng sẽ tự đánh chỉ mục lại ?

6. cái này có đúng ko:việc dùng chức năng fulltextsearch ngốn nhiều tài nguyên máy ( khoảng 100K dòng là dể bị overload server lắm).vậy trường hợp này nên dùng creat unique index và bỏ full text search? E đã tìm đọc rất nhiều nhưng ko thể tìm đc kết quả trường hợp này dùng create index và full text search cái nào sẽ nhanh hơn ?
Mọi người lưu ý sẽ là data vài triệu dòng nhé. Vài phút cronjob sẽ chạy một lần nên tính ra truy vấn so sánh sẽ nhiều hơn rất nhiều so với insert: truy vấn so sánh từ 14000 đến 15000 một lần chạy cron, còn insert chỉ vài trăm vì mình chạy rà soát các rss liên tục.

7. sử dụng câu kiểm tra như thế này có nhanh hơn là câu lệnh bên trên:
IF EXISTS (SELECT id FROM table_name WHERE title = '***')

8. Với câu này kiểm tra này thì sao:
SELECT count(*) FROM table_name WHERE MATCH (fi) AGAINST ('value');

9. Trường hợp này có nên sử dụng mysql_pconnect() thay cho mysql_connect().

10. Có ai biết cách đo thời gian thực thi một câu lệnh query thì cho e xin mấy dòng code đấy nhé.

PS: Ngoài ra thấy mọi người thấy còn cách nào hay hơn thì cho e xin gợi ý. Thanks cả nhà!

[ngoc_viet08]

mình chưa từng làm qua công việc này. nhưng mình nghĩ bản thân vnexpress đã unique tin tức, thì bạn phải tìm cách đọc dữ liệu để khỏi phải so sánh bất kì hoặc chỉ so sánh 1 số rất nhỏ cái tin nào như vậy .

Ở đây cần nghiên cứu môi trường thực tế của vnexpress, cách lưu tin của nó như thế nào để xử lý phù hợp.

http://vnexpress.net/rss/gl/xa-hoi.rss

ở đây mình ko hiểu làm sao để có thể xem các rss cũ hơn ?

[mattroi_viendong]

cái này có ba lý do:

thứ nhất là e lấy tin từ nhiều báo

thứ hai là các rss có khoản 10 cái tin. Lần đầu tiên mình chạy nó đã lấy hết. Nhưng 10 phút sau mình chạy thì cái rss nó vẫn thế hoặc chỉ thêm 1,2 tin mới bỏ đi 1,2 tin cũ cuối cùng. Nên nếu mình ko xem xét cái nào có rồi cài nào chưa thì nó sẽ insert vào csdl 20, 30 lần mỗi tiêu đề tin, hàng ngày.

thứ ba là nhiều mục tin có lượng tin mới ít, rss của nó lưu tin từ 4 tháng trước đến tận bây giờ. nếu ko so sánh nhét nó vào csdl luôn thì nó thành tin mới. đăng tin 4 tháng trước vào mục tin mới thì nguy hiểm.

(code của e chỉ điểm tin: hiển thị tiêu đề, mô tả và ảnh minh họa, phần đọc chi tết bài viết sẽ được link đến thẳng bài viết của báo gốc, chứ ko lấy toàn bộ bài viết của các báo về thành của mình như mấy báo khác, nên các bác yên tâm là ko tiếp tay cho tội phạm)

[manlivo]

mình chưa từng làm qua công việc này. nhưng mình nghĩ bản thân vnexpress đã unique tin tức, thì bạn phải tìm cách đọc dữ liệu để khỏi phải so sánh bất kì hoặc chỉ so sánh 1 số rất nhỏ cái tin nào như vậy .

Ý của chủ topic là lấy dữ liệu từ các báo diện tử chứ ko chỉ riêng vnexpress, mà 1 tin thì bị copy lại của nhau rất nhiều, vì vậy trùng title là điều sẽ xảy ra. Hơn nữa chẳng ai muốn phí tài nguyên để chứa những tin giống nhau hay nặng hơn là chịu sự trừng phạt của google vì dulicate title.
@mattroi_viendong: Mình thấy bạn đề cập tới vấn đề dùng cột field để chứa mã md5 của title. Mình thấy liệu có phải đã là giải pháp hay khi mà số phép so sánh ko thay đổi mà mất thêm việc xử lý mã hóa title????

[mattroi_viendong]

Việc lọc title gần tương tự nhau là rất khó, vì nhiều khi các báo, kể cả báo lớn luộc lại bài của nhau nhưng lại thêm thắt, sửa sang chút ít cái title nên lọc theo % cũng ko đc.

Hiện tại trước mắt e chỉ quan tâm đến việc lọc các title giống hệt nhau. Việc mã hóa md5 sẽ khiến title dài hay ngắn đều thành 32 ký tự liền nhau , ko dấu tiếng việt, ko ký tự đặc biệt. Như vậy là đã thấy nó có vẻ ổn hơn rồi.
Còn việc xử lý mã hóa mất bao nhiêu giây thì đang chờ mọi người chỉ cho code đo thời gian thực thi sql và thời gian thực thi hàm php để đo. Nhưng thường thì title một bài báo ko dài nên mã hóa chắc cũng ko tốn nhiều tài nguyên và thời gian thực hiện.

[thuyduongcd]

Dùng md5 cho trường hợp này là hoàn toàn sai lầm. Vì chuỗi md5 giống nhau chưa chắc title nó đã giống nhau.

title1=title2 -> md5(title1)=md5(title2) : đúng
md5(title1)=md5(title2) -> title1=title2 : sai

Vấn đề của bạn mình xin đưa 1 giải pháp. Thông thường các RSS sắp xếp tin theo date từ mới đến cũ. Vậy để tránh lấy những tin trùng nhau có thể dựa vào đó dược.
Ví dụ lần đầu đọc RSS được 10 tin, lấy lưu vào DB và ghi lại thời gian của tin mới nhất. Lần sau cũng đọc RSS đó 10 tin (có hoặc không có tin mới) thì chỉ lọc ra những tin nào mà ngày đăng tin lớn hơn giá trị đã lưu thì đó là tin mới còn lại bỏ đi.

Giả sử lần đầu đọc được:

Title 1 - 15/01/2011
Title 2 - 13/01/2011
...
Title 10 - 02/01/2011

Lần thứ 2 đọc được:
Title 0 - 16/01/2011
Title 1 - 15/01/2011
Title 2 - 13/01/2011
...
Title 9 - 04/01/2011

Vậy thì rõ ràng chỉ có những Title nào mà có date > 15/01/2011 mới là tin mới.
Và lần thứ 3 chỉ lấy những tin có date > 16/01/2011

[mattroi_viendong]

Vụ này có chút trở ngại vì một số báo rss chỉ có tiêu đề và link, một số ngày tháng dạng 16 February 2011, một số 16 February 2011, 4:01pm, một số dùng định dạng ngày tháng năm khác. Khá là bát nháo. Nên lọc theo ngày tháng có vẻ ko ổn.

[manlivo]

Đúng như bạn thuyduongcd nói, 2 title khác nhau nhưng có thể xảy ra trường hợp băm thành 1 chuỗi md5 giống nhau. Nhưng mình ko nghĩ là sai lầm, vì điều này chỉ xảy ra với tỷ lệ % nhỏ.
Mình nghĩ là bạn nên giới hạn số lượng so sánh lại, dù bạn có tối ưu kiểu gì nhưng nếu làm bạn tối ưu thành công vói 10k rows nhưng điều gì đảm bảo khi server bạn sẽ hoạt động tốt khi mà số phép so sánh tăng theo số rows trong CSDL của bạn???? Mà chưa kẻ tới thời gian truy vấn lấy ra số rows đó. Nó lên 50k, 100k, ... thì lúc đấy liệu còn cách tối ưu nào khác thì ngoài việc đi mua 1 siêu máy tính
Theo mình bạn chỉ giới hạn số phép so sánh với khoảng 5k bản ghi dữ liệu mới nhất đc lấy ra thôi.

Vụ này có chút trở ngại vì một số báo rss chỉ có tiêu đề và link, một số ngày tháng dạng 16 February 2011, một số 16 February 2011, 4:01pm, một số dùng định dạng ngày tháng năm khác. Khá là bát nháo. Nên lọc theo ngày tháng có vẻ ko ổn.

Format nó về định dạng ngày tháng mà bạn sử dụng

[thuyduongcd]

Vụ này có chút trở ngại vì một số báo rss chỉ có tiêu đề và link, một số ngày tháng dạng 16 February 2011, một số 16 February 2011, 4:01pm, một số dùng định dạng ngày tháng năm khác. Khá là bát nháo. Nên lọc theo ngày tháng có vẻ ko ổn.

strtotime() là một lựa chọn nếu bạn dùng php.

Còn nếu bạn không thích sự thay đổi thì có một gợi ý thế này. Để ý trong câu sql bạn có đoạn

date >= DATE_SUB(CURDATE(), INTERVAL 100 DAY)";

Nghĩa là chỉ giới hạn trong 100 ngày trở lại. Vậy có thể hạn chế số row bằng cách tạo 1 view gồm những tin trong vòng 100 ngày trở lại đây. Sau đó mới check title trong View này

Nhưng mình ko nghĩ là sai lầm, vì điều này chỉ xảy ra với tỷ lệ % nhỏ.

Nhỏ nhưng không có nghĩa là không có. Đành rằng đôi khi phải chấp nhận rủi ro nhưng chỉ khi nào sự rủi ro đó đánh đổi bằng lợi ích khác. Đằng này nó không có lợi ích nào đáng kể, còn làm nặng DB thêm (mình chưa thấy cái DB nào mà Unique index field 32 byte cả)

[mattroi_viendong]

việc format về một định dạng chắc là khó vì nhiều thằng nó còn ko có ngày tháng.

e vẫn thiên về hướng mã hóa thành md5 vì e đang nghĩ tới việc trùng nhưng xác xuất thấp. Ở mức chấp nhận được.

e sẽ có gắng để việc so sánh chỉ ở mức < 250.000 row bằng cách nhóm lại chỉ lấy những cái có date >= DATE_SUB(CURDATE(), INTERVAL 100 DAY). Date này là mình tự thêm curdate() khi insert mỗi tin vào csdl.Nên đảm bảo 100% đều có
Tuy nhiên, việc so sánh làm sao để 15.000 tin x 250.000 rows trong csdl chạy mượt mà là cái e đang cần tìm hiểu.
Ko biết việc unique index, full text search, tối ưu câu query dạng như đề cập ở trên có tác dụng gì ko