Vấn đề:
Chúng tôi có một trang web xã hội nơi các thành viên có thể đánh giá lẫn nhau về khả năng tương thích hoặc phù hợp. Bảng user_match_ratings
này chứa hơn 220 triệu hàng [9 dữ liệu hợp đồng hoặc gần 20 chỉ số trong các chỉ mục]. Các truy vấn đối với bảng này thường xuyên hiển thị trong slow.log [ngưỡng> 2 giây] và là truy vấn chậm được ghi lại thường xuyên nhất trong hệ thống:
Query_time: 3 Lock_time: 0 Rows_sent: 3 Rows_examined: 1051
"select rating, count[*] as tally from user_match_ratings where rated_user_id = 395357 group by rating;"
Query_time: 4 Lock_time: 0 Rows_sent: 3 Rows_examined: 1294
"select rating, count[*] as tally from user_match_ratings where rated_user_id = 4182969 group by rating;"
Query_time: 3 Lock_time: 0 Rows_sent: 3 Rows_examined: 446
"select rating, count[*] as tally from user_match_ratings where rated_user_id = 630148 group by rating;"
Query_time: 5 Lock_time: 0 Rows_sent: 3 Rows_examined: 3788
"select rating, count[*] as tally from user_match_ratings where rated_user_id = 1835698 group by rating;"
Query_time: 17 Lock_time: 0 Rows_sent: 3 Rows_examined: 4311
"select rating, count[*] as tally from user_match_ratings where rated_user_id = 1269322 group by rating;"
Phiên bản MySQL:
- Phiên bản giao thức: 10
- Phiên bản: 5.0.77-log
- Phiên bản BDB: Phần mềm Sleepycat: Berkeley DB 4.1.24: [ngày 29 tháng 1 năm 2009]
- Máy biên dịch phiên bản: x86_64 phiên bản_compile_os: redhat-linux-gnu
Thông tin bảng:
SHOW COLUMNS FROM user_match_ratings;
Gives:
╔═══════════════╦════════════╦════╦═════╦════════╦════════════════╗
║ id ║ int[11] ║ NO ║ PRI ║ NULL ║ auto_increment ║
║ rater_user_id ║ int[11] ║ NO ║ MUL ║ NULL ║ ║
║ rated_user_id ║ int[11] ║ NO ║ MUL ║ NULL ║ ║
║ rating ║ varchar[1] ║ NO ║ ║ NULL ║ ║
║ created_at ║ datetime ║ NO ║ ║ NULL ║ ║
╚═══════════════╩════════════╩════╩═════╩════════╩════════════════╝
Truy vấn mẫu:
select * from mutual_match_ratings where id=221673540;
Giving:
╔═══════════╦═══════════════╦═══════════════╦════════╦══════════════════════╗
║ id ║ rater_user_id ║ rated_user_id ║ rating ║ created_at ║
╠═══════════╬═══════════════╬═══════════════╬════════╬══════════════════════╣
║ 221673540 ║ 5699713 ║ 3890950 ║ N ║ 2013-04-09 13:00:38 ║
╚═══════════╩═══════════════╩═══════════════╩════════╩══════════════════════╝
Chỉ mục
Bảng có 3 chỉ mục được thiết lập:
- Chỉ mục duy nhất trên
rated_user_id
- Chỉ số tổng hợp trên
rater_user_id
và
0SHOW COLUMNS FROM user_match_ratings;
- Chỉ số tổng hợp trên
rated_user_id
vàrater_user_id
show index from user_match_ratings;
gives:
╔════════════════════╦════════════╦═══════════════════════════╦══════════════╦═══════════════╦═══════════╦═════════════╦══════════╦════════╦═════════════════════════╦════════════╦══════════════════╗
║ Table ║ Non_unique ║ Key_name ║ Seq_in_index ║ Column_name ║ Collation ║ Cardinality ║ Sub_part ║ Packed ║ Null ║ Index_type ║ Comment ║
╠════════════════════╬════════════╬═══════════════════════════╬══════════════╬═══════════════╬═══════════╬═════════════╬══════════╬════════╬═════════════════════════╬════════════╬══════════════════╣
║ user_match_ratings ║ 0 ║ PRIMARY ║ 1 ║ id ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index1 ║ 1 ║ rater_user_id ║ A ║ 11039059 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index1 ║ 2 ║ created_at ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index2 ║ 1 ║ rated_user_id ║ A ║ 4014203 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index2 ║ 2 ║ rater_user_id ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index3 ║ 1 ║ rated_user_id ║ A ║ 2480687 ║ NULL ║ NULL ║ BTREE ║ ║ ║
╚════════════════════╩════════════╩═══════════════════════════╩══════════════╩═══════════════╩═══════════╩═════════════╩══════════╩════════╩═════════════════════════╩════════════╩══════════════════╝
Ngay cả với các chỉ mục, các truy vấn này vẫn chậm.
Câu hỏi của tôi:
Việc phân tách bảng/dữ liệu này cho một cơ sở dữ liệu khác trên máy chủ có đủ RAM để lưu trữ dữ liệu này trong bộ nhớ liệu điều này có tăng tốc các truy vấn này không? Có bất cứ điều gì trong dù sao các bảng/chỉ mục được thiết lập mà chúng ta có thể cải thiện để làm cho các truy vấn này nhanh hơn không?
Hiện tại chúng tôi có 16GB bộ nhớ; Tuy nhiên, chúng tôi đang xem xét nâng cấp máy hiện có lên 32GB hoặc thêm một máy mới với ít nhất là nhiều ổ đĩa trạng thái rắn.