Hướng dẫn mysql query 1 million records - truy vấn mysql 1 triệu bản ghi

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:

1. Chỉ mục duy nhất trên rated_user_id
2. Chỉ số tổng hợp trên rater_user_id và

   SHOW COLUMNS FROM user_match_ratings;
   

   0
3. 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.

MySQL có thể xử lý 1 triệu hồ sơ không?

MySQL có thể lưu trữ hàng triệu hồ sơ không?

Làm thế nào để bạn xử lý hàng triệu hồ sơ trong SQL?

Cách nhanh nhất để thực hiện truy vấn với hàng triệu hồ sơ là gì?