Cần giúp đỡ về sắp xếp database MySQL

English | Vietnamese

Rules

Main Forum

Portal

Member Listing

Statistics

Search

Reading Room
[Register]

Login [ | https ]

Forum Index

Thảo luận bảo mật

Cần giúp đỡ về sắp xếp database MySQL

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	10/04/2014 08:16:23 (+0700) \| #1 \| 280248

henryhuy
Member

Joined: 06/04/2014 22:28:32
Messages: 4
Offline

Mình đang quản trị 1 trang web về tin tức, mỗi ngày lượng tin lưu vào dữ liệu khoảng 1000 record, nên càng ngày table chứa tin tức ngày càng phình ra và dường như gây chậm hệ thống đáng kể.
Table chứa tin tức của mình cũng gồm các column đơn giản như tuade, tomtat, noidung, hinh anh, ngaythem, khi mình select ra vẫn có limit nhưng mình thấy dường như không cải thiện được tình hình, xin các bác cho mình 1 vài ý kiến để nâng cao hiệu suất hoạt động của web bằng cách sắp xếp lại database thế nào cho hợp lý.
Xin cảm ơn.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	10/04/2014 16:01:02 (+0700) \| #2 \| 280270

myquartz
Member

Joined: 04/01/2005 04:58:30
Messages: 563
Offline

select ra gồm những tình huống nào? và điều kiện dự định là gì? Thì tổ chức bảng tin theo cách đó và đánh index như thế.
Ví dụ:
1. đọc 1 tin bài cụ thể, thì điều kiện where là id của tin/bài đó.
2. liệt kê 10 tin mới nhất, nghĩa là order theo thời gian và limit 10 cái. Nhưng order là phép toán khá chậm nếu không cẩn thận, do đó bạn nên thêm điều kiện >= time nào đó tính ra được, gần đúng thôi, hoặc id > = max_id - 10 chẳng hạn => là 10 tin cuối cùng (giả sử id là số thứ tự tin tăng dần).

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	23/04/2014 11:06:59 (+0700) \| #3 \| 280419

henryhuy
Member

Joined: 06/04/2014 22:28:32
Messages: 4
Offline

Cảm ơn bác đã phản hồi, mình muốn hỏi thêm là nếu 1 table có 1000 record và 1 table 10000 record thì tốc độ load dữ liệu có khác nhau không nếu mình có limit và order ?

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	23/04/2014 14:23:29 (+0700) \| #4 \| 280422

keq9
Member

Joined: 07/07/2007 22:59:15
Messages: 28
Offline

Vấn đề bạn gặp khá phổ biến, bạn có thể tìm hiểu thêm về index trên MySQL để index và tối ưu cho các truy vấn của mình.

Về lý thuyết, nếu các fields trong điều kiện where và order của bạn nếu không có index thì tốc độ khác nhau 10 lần. Vì mysql phải quét qua tất cả các bản ghi.

Nên dùng cách như myquartz nói vì việc tìm kiếm dữ liệu theo field id sẽ thực hiện trên bảng index nên tốc độ lấy dữ liệu ở hai trường hợp ra sẽ không có chênh lệch lớn.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	24/04/2014 17:43:14 (+0700) \| #5 \| 280431

myquartz
Member

Joined: 04/01/2005 04:58:30
Messages: 563
Offline

henryhuy wrote:

Cảm ơn bác đã phản hồi, mình muốn hỏi thêm là nếu 1 table có 1000 record và 1 table 10000 record thì tốc độ load dữ liệu có khác nhau không nếu mình có limit và order ?

Khác nhau 10 lần, nói chung đa số trường hợp nếu câu chỉ có limit và order thôi thì sẽ không sử dụng đến index (kể cả order trên cột đã index).

MySQL luôn order trước (tức nó lấy hết 1000 hay 1 vạn, 1 triệu ra sort), rồi limit. Phép toán sort là phép toán đắt tiền nên sẽ chậm.

Chỉ cần nhớ đến 1 điều này: index chỉ được đụng đến khi cột nó trong where và tên cột được index đó đứng duy nhất một mình bên vế so sánh (=, <, > hoặc between), không có hàm hoặc bất cứ công thức nào, cũng không có cột nào ở vế còn lại.
Ví dụ id=xx hoặc id<=xx sẽ dùng index, nhưng left(id,5) = xx thì không (do id không đứng một mình ở vế đó).
Hoặc id (là số), ta viết thế này: id >= 5 (cái này có) thì khác hoàn toàn với id - 5 >= 0 (cái này không dùng index).
Thế này cũng không index: id >= max_id - 5 (trong đó 2 cột id và max_id cùng 1 bảng, nếu max_id ở bảng khác thì phải có điều kiện where với bảng đó nữa).

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	25/04/2014 14:23:49 (+0700) \| #6 \| 280442

henryhuy
Member

Joined: 06/04/2014 22:28:32
Messages: 4
Offline

Xin gừi bác cấu trúc table của mình như sau :
1 id int(250)
2 content_id varchar(250)
3 content_category_id varchar(255)
4 title text
5 sub_title text
6 detail longtext
7 image varchar(250)
8 idlang varchar(250)
9 show_content int(2)
10 dateadd datetime

Câu lệnh SELECT mỗi lần lấy tin mới nhất của mình thế này : "select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd from content order by id desc limit 0,10"

Nếu muốn tạo INDEX thì mình làm thế nào, sorry mình hỏi hơi kỹ vì chưa làm INDEX lần nào cả

Mình có đọc 1 số tut trên mạng thì hướng dẫn là ALTER TABLE content ADD INDEX idx_id(id); nhưng mình không rõ là khi nào nên ALTER TABLE cả, có phải là mỗi lần select 10 tin mới ra chăng ?

Cảm ơn các bác nhiều.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	25/04/2014 15:57:39 (+0700) \| #7 \| 280445

myquartz
Member

Joined: 04/01/2005 04:58:30
Messages: 563
Offline

henryhuy wrote:

Xin gừi bác cấu trúc table của mình như sau :
1 id int(250)
2 content_id varchar(250)
3 content_category_id varchar(255)
4 title text
5 sub_title text
6 detail longtext
7 image varchar(250)
8 idlang varchar(250)
9 show_content int(2)
10 dateadd datetime

Câu lệnh SELECT mỗi lần lấy tin mới nhất của mình thế này : "select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd from content order by id desc limit 0,10"

Nếu muốn tạo INDEX thì mình làm thế nào, sorry mình hỏi hơi kỹ vì chưa làm INDEX lần nào cả

Mình có đọc 1 số tut trên mạng thì hướng dẫn là ALTER TABLE content ADD INDEX idx_id(id); nhưng mình không rõ là khi nào nên ALTER TABLE cả, có phải là mỗi lần select 10 tin mới ra chăng ?

Cảm ơn các bác nhiều.

Ợ. Vậy là bạn học lập trình SQL mà không học về ... index của CSDL, một thứ quan trọng sống còn của nó (mặc dù hệ thống vẫn chạy khi không có nó), có thể đây là sự thiếu sót không chỉ mỗi bạn mắc phải đâu.
Cột ID của bạn có phải là primary key? nếu là primary key rồi thì không cần index nữa vì nó đã được index rồi (primary key là 1 dạng index, với thuộc tính unique). Việc tạo index chỉ 1 lần kèm với tạo bảng, và thi thoảng thì phải bảo dưỡng nó thôi (analyze table), alter table là nhóm DDL như create table...

Nếu cột ID của bạn là số tăng dần, mỗi bài tin là tăng 1 đơn vị, bạn lấy 10 tin mới nhất thì sửa 1 chút thế này:

select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd from content where id >= (select max(id)-10 from content) order by id desc limit 0,10

Nếu lo ngại có sự delete trong content, gây lỗ thủng id, thiếu, thì max(id) - 10 thay bằng - 20, - 30... thậm chí 100 vẫn là rất tốt cho bảng. nó đảm bảo rằng dù bảng có tăng kích thước lên thì tốc độ vẫn không thay đổi nhiều.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	25/04/2014 17:01:03 (+0700) \| #8 \| 280447

henryhuy
Member

Joined: 06/04/2014 22:28:32
Messages: 4
Offline

Vâng, cảm ơn bác nhiều lắm, mình thấy trong phpmyadmin trên row id có nút index và mình có kích hoạt lên thì không biết như vậy có hiệu lực index trên cột id chưa vậy bác.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	15/05/2014 19:29:35 (+0700) \| #9 \| 280664

ke_lang_thang_1990
Member

Joined: 28/02/2014 03:47:31
Messages: 7
Offline

$henrryhuy: bạn vào phần structure của table, có cái +index, click vào đấy sẽ mở ra những field bạn đã index rồi, Primary cũng là một loại index. Riêng phần sử dụng như thế nào cho tốt thì bạn @myquartz đã nói ra môt vài điểm cần chú ý rồi, bạn nên đọc lại về index trong các giáo trình CSDL, ngoài ra có thể tìm hiểu cách đánh index và tác dụng của index trong các giáo trình nói về "Chỉ mục" và "lưu trữ ngoài" (chẳng hạn như giáo trình về Phân tích và thiết kế Thuật toán).
Bạn phải hiểu rõ nó thì mới thấy được tác dụng của nó

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	24/07/2014 11:34:48 (+0700) \| #10 \| 281073

tmlinhkct
Member

Joined: 06/06/2012 02:55:48
Messages: 11
Offline

myquartz wrote:

henryhuy wrote:

Xin gừi bác cấu trúc table của mình như sau :
1 id int(250)
2 content_id varchar(250)
3 content_category_id varchar(255)
4 title text
5 sub_title text
6 detail longtext
7 image varchar(250)
8 idlang varchar(250)
9 show_content int(2)
10 dateadd datetime

Câu lệnh SELECT mỗi lần lấy tin mới nhất của mình thế này : "select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd from content order by id desc limit 0,10"

Nếu muốn tạo INDEX thì mình làm thế nào, sorry mình hỏi hơi kỹ vì chưa làm INDEX lần nào cả

Mình có đọc 1 số tut trên mạng thì hướng dẫn là ALTER TABLE content ADD INDEX idx_id(id); nhưng mình không rõ là khi nào nên ALTER TABLE cả, có phải là mỗi lần select 10 tin mới ra chăng ?

Cảm ơn các bác nhiều.

Ợ. Vậy là bạn học lập trình SQL mà không học về ... index của CSDL, một thứ quan trọng sống còn của nó (mặc dù hệ thống vẫn chạy khi không có nó), có thể đây là sự thiếu sót không chỉ mỗi bạn mắc phải đâu.
Cột ID của bạn có phải là primary key? nếu là primary key rồi thì không cần index nữa vì nó đã được index rồi (primary key là 1 dạng index, với thuộc tính unique). Việc tạo index chỉ 1 lần kèm với tạo bảng, và thi thoảng thì phải bảo dưỡng nó thôi (analyze table), alter table là nhóm DDL như create table...

Nếu cột ID của bạn là số tăng dần, mỗi bài tin là tăng 1 đơn vị, bạn lấy 10 tin mới nhất thì sửa 1 chút thế này:

select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd from content where id >= (select max(id)-10 from content) order by id desc limit 0,10

Nếu lo ngại có sự delete trong content, gây lỗ thủng id, thiếu, thì max(id) - 10 thay bằng - 20, - 30... thậm chí 100 vẫn là rất tốt cho bảng. nó đảm bảo rằng dù bảng có tăng kích thước lên thì tốc độ vẫn không thay đổi nhiều.

mình xin đính chính chút là nếu cột id đã là primary key rồi thì không cần dùng WHERE... nữa mà chiến luôn:
Code:

select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd  from content order by id desc limit 0,10

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	26/07/2014 23:05:36 (+0700) \| #11 \| 281089

myquartz
Member

Joined: 04/01/2005 04:58:30
Messages: 563
Offline

tmlinhkct wrote:

mình xin đính chính chút là nếu cột id đã là primary key rồi thì không cần dùng WHERE... nữa mà chiến luôn:
Code:
select id, content_id, content_category_id, title, sub_title, image, idlang, show_content, dateadd  from content order by id desc limit 0,10

Câu lệnh này đáp ứng được tiêu chí viết gọn, đẹp nhưng đôi khi nó không chạy như thiết kế và xui xẻo mà developer thay đổi 1 chút thành "order by dateadd" (liệt kê tin từ cũ tới mới chứ không cho mới lên đầu), hoặc primary key không chỉ là id mà thêm category_id thì lúc đó sẽ rất chậm, viết xấu với mệnh đề WHERE sẽ an toàn hơn và không ảnh hưởng nhiều vì câu order by.

[Discussion] Cần giúp đỡ về sắp xếp database MySQL	30/07/2014 08:52:43 (+0700) \| #12 \| 281144

ke_lang_thang_1990
Member

Joined: 28/02/2014 03:47:31
Messages: 7
Offline

henryhuy wrote:

càng ngày table chứa tin tức ngày càng phình ra và dường như gây chậm hệ thống đáng kể.

@tmlinhkct: Chúng ta chú ý vấn đề của chủ topic nhé.
Lệnh limit ưu điểm là nhanh, dễ lập trình nhưng phải load hết dữ liệu hợp yêu cầu (load hết nếu không có where) trước khi limit. Ví dụ dữ liệu bạn có 1 triệu instance, máy tính sẽ truy xuất ra 1 triệu, rồi mới limit 10. Việc order ở đây sẽ thực hiện trên 1 triệu instance @@. Bạn @myquartz đưa ra một giải pháp hợp lý, đó là dùng where để gom lại một khoảng instance cố định để order và limit (1000 chẳng hạn), như vậy dù cho dữ liệu của bạn phình ra thêm mấy tỉ nữa thì cũng chỉ truy xuất 1000 instance thôi.
Nói thêm tí, thường lập trình viên bọn mình test chỉ khoảng vài chục đến vài trăm instance là cùng (vì làm biếng), nên code của bạn @tmlinhkct sẽ không khác bạn @myquartz mấy, nên ta thường code đơn giản để có thể chạy ngay. Nhưng khi chạy thực tế thì một thời gian sau bị phình dữ liệu, dẫn đến load chậm.

Go to:

Users currently in here
1 Anonymous