MySQL hiệu suất thực tế cuộc sống Mẹo và thủ thuật

Trong thời gian gần đây thường là đủ để tối ưu hóa hiệu suất của các trang web. Và thường “chai-cổ” trong việc thực hiện của các trang web này là DB lỗi, trong kiến trúc lẫn trong khi thực hiện truy vấn. Bắt đầu từ vị trí không chính xác lập chỉ mục, hoặc để hoàn thiện của họ thiếu, sai (hiệu quả) lựa chọn loại dữ liệu trong một lĩnh vực cụ thể, để hoàn toàn vô lý cơ sở dữ liệu kiến trúc và các yêu cầu cùng một vô lý.

 

Bài viết này sẽ mô tả một số kỹ thuật đã được sử dụng cho một ứng dụng mà người sử dụng + 4 triệu với khoảng 100 triệu + số truy cập mỗi ngày, và cuối cùng sẽ mô tả nhiệm vụ mới và có thể là mnogouvažaemoe cộng đồng sẽ cung cấp cho tôi nhiệm vụ này tốt hơn so với mà tôi đến.

 

Lớn bàn trong MySQL có chứa hàng trăm ngàn, hàng triệu hồ sơ thường rất quan trọng của nhóm bởi truy vấn. Bởi vì trong hầu hết trường hợp, nếu chúng ta nhìn vào đây truy vấn, giải thích thêm lĩnh vực bằng cách sử dụng tạm thời; Bằng cách sử dụng filesort
Ví dụ:

 

explain
select
*
from
`tags`
group by
tag_text;

 

Phụ-Sử dụng chỉ mục; Bằng cách sử dụng tạm thời; Bằng cách sử dụng filesort
Nhóm sử dụng một bảng tạm thời, mà sau đó được sắp xếp, nhãn sorting xảy ra mà không có việc sử dụng các chỉ số bất kỳ.
Nói chung nếu truy vấn của bạn sử dụng nhóm bởi khoản, sau đó MySQL sẽ luôn luôn sắp xếp các kết quả. Nếu thứ tự của các kết quả không phải là quan trọng đối với chúng tôi, nó là tốt hơn để có được thoát khỏi này hoạt động (phân loại). Bạn có thể làm điều này bằng cách thêm “để bởi null” yêu cầu. Đạt được tất cả

 

explain
select
*
from
`tags`
group by
tag_text
order by null;

 

Phụ-Sử dụng chỉ mục; Bằng cách sử dụng tạm thời;
Như một quy luật, nhóm thường đi qua các loại dữ liệu chuỗi chậm, bạn có thể đạt được hiệu suất đáng kể lợi nhuận nếu phe “chính xác”, và các giá trị được nhóm chính xác bạn có thể đóng góp. Điều này có thể hữu ích trong việc thẩm định của bản ghi hoặc các báo cáo khác mà sẽ đi xuống và những con số ước tính.
Ví dụ, truy vấn này sẽ thực hiện nhanh hơn trước đây

select
*
from
`tags`
group by
crc32(tag_text)
order by null;

 

Innogda một vấn đề lớn là giới hạn trong các truy vấn, tôi sẽ không là ở đây để nói rằng một số đang kéo 100 hồ sơ, và đôi khi là 1000 nếu bạn thực sự sử dụng 10; Tôi sẽ nói những lợi ích sau đây của giới hạn là chỉ sau đó khi truy vấn sử dụng một chỉ số trên một lĩnh vực mà sắp xếp, nếu không sử dụng tạm thời; Bằng cách sử dụng filesort phủ nhận toàn bộ lợi ích của giới hạn. Cũng bạn nên tránh các giới hạn sau đây giới hạn 1000000, 25 như vẫn được chọn sẽ là 1000025 hồ sơ và chỉ sau đó 1000000 bỏ đi. Điều này thường được sử dụng cho số trang, và nhiều lập trình thường được chứng minh bằng thực tế mà người dùng sẽ vẫn chủ yếu là tới trang mới (cuối cùng theo thứ tự), mà yêu cầu giới hạn như vậy là khá hiếm… Có người dùng truy cập các trang một năm, hai năm trước đây không phải là phổ biến, nhưng nếu các trang web sẽ tìm kiếm các bot, sau đó nó đi vào mỗi trang, và các bot lập chỉ mục nội dung trang web, chúng tôi sẽ đặt máy chủ DB.
Giải pháp cho một vấn đề thường là prevyčislenie kết quả trên trang trước đó, ví dụ là các id kỷ lục cuối cuối trang và giới hạn truy vấn nào nghe một cái gì đó như
Nơi bla-bla và NODE_ID > id_from_previous_page thứ tự bởi NODE_ID giới hạn 25
tốt, hoặc một cái gì đó như thế
Nơi vị trí giữa 1000 và 1025
Những mẫu thiết kế nhanh hơn nhiều so với giới hạn 1000, 25
Giải pháp này vấn đề đề xuất % rumkin % liên quan đến vấn đề này.
Vấn đề với trang danh mục chính với không có tình yêu cho các GET hiện đại hơn, yêu cầu có thể được giải quyết như sau:

 

SELECT
*
FROM
`table`
WHERE
id>X*Y-1
LIMIT
X;

 

Nếu X là một số kết quả mỗi trang, Y là số lượng trang hiện thời. Vì vậy, chúng tôi sẽ làm mà không có predzaprosov, yêu cầu nhận được và các biến chứng khác.
Một số cung cấp để giải quyết vấn đề như vậy, sử dụng ngang
sharding (phân vùng) nhưng để giải quyết vấn đề với pagination là IMHO izvrat một lần nữa… Mặc dù sau này šardinge…
Một số nghĩ rằng thiết kế của SQL_CALC_FOUND_ROWS SQL_NO_CACHE nhanh hơn 2 x yêu cầu giới hạn c đầu tiên và thứ hai chọn bộ đếm (*)
Ở đây bạn có thể đọc một vạch trần của huyền thoại này
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-o-sql_calc_found_rows/
Về vị trí của chỉ số sẽ không viết. Rất nhiều đã được nói về điều này, tôi sẽ viết chỉ đó không phải dựa vào thuật toán chỉ số kết hợp và thay thế nó bằng composite (composite index trên nhiều lĩnh vực), bạn có thể đọc về nó ở đây đọc thêm
http://www.mysqlperformanceblog.com/2008/08/22/Multiple-Column-index-vs-Multiple-indexes/
http://dev.MySQL.com/doc/RefMan/5.0/en/index-Merge-Optimization.html
Hoặc đôi khi nó là thích hợp để thay thế trên yêu cầu với các công đoàn (và chính xác hơn với liên minh tất cả, bởi vì các công đoàn là giảm liên minh khác biệt và vì vậy, khi chúng tôi kết hợp các kết quả của hai truy vấn bằng cách sử dụng một liên minh, sáp nhập này sẽ làm việc chậm hơn, điều này là không bởi vì tất cả liên minh tương phản với các công đoàn không sử dụng tạm thời bảng thực sự sử dụng, chỉ không được công nhận (theo nghĩa của giải thích không hiển thị)Nó chỉ có thể được nhìn thấy bằng cách sử dụng trạng thái Hiển thị. Một thực tế rằng liên minh tất cả tạo ra một bảng tạm thời mà không có một khóa duy nhất, và liên minh khác biệt từ-do đó sự khác biệt) vì vậy đôi khi loại truy vấn có thể là lựa chọn tốt nhất

 

select
*
from
`table`
where
first = ‘A’

UNION ALL

select
*
from
`table`
where
second = ‘B’ AND first != ‘A’

UNION ALL

select
*
from
`table`
where
third = ‘C’ and second != ‘B’ AND first != ‘A’
* This source code was highlighted with Source Code Highlighter.

нежели

select
*
from
`table`
where
third = ‘C’ OR second = ‘B’ OR first = ‘A’

 

Có, ngay cả khi tôi viết, vì vậy đây là về bao gồm các chỉ số (bao gồm chỉ số), chính xác hơn về truy vấn sử dụng chúng.
Trong ngắn hạn, những gì chúng tôi đã làm việc trong các truy vấn được sử dụng như là một điều kiện (WHERE khoản) và trở lại (chọn) chỉ các lĩnh vực được bao gồm trong chỉ mục tổng hợp duy nhất. Tổng — tất cả đó cơ bắp để chạy xung quanh cây và trả lại kết quả. Các chỉ số trong bộ nhớ, các dữ liệu trên đĩa, chúng tôi không lên cao, tất cả mát và nhanh chóng.
Một ví dụ điển hình về điều này là các loại truy vấn

 

CHỌN user_password từ ‘người dùng’ nơi user_name = ‘maghamed’;
có một chỉ số trên các lĩnh vực (user_name, user_password)

 

Đây là một ví dụ khác thường hơn trong chủ đề này cho gắn kết vật liệu 🙂

 

   SELECT
`log`.visitor_id, `url`.url
FROM (
SELECT
id
FROM
log
WHERE
ip=”127.0.0.1”
ORDER BY
ts DESC
LIMIT 50,10
) l
JOIN log
ON (l.id=log.id)
JOIN url
ON (url.id=log.url_id)
ORDER BY
TS DESC;

Mặc dù yêu cầu và trông khủng khiếp, nhưng nếu chúng tôi có một chỉ số bao gồm trên các lĩnh vực (IP, TS, ID), có nguồn gốc truy vấn sẽ sử dụng nó và chạy rất nhanh chóng, trong đó có giới hạn, giới hạn sẽ bị tính phí cho đoạn văn cũng sử dụng chỉ mục. Sau đó, tự tham gia để kết nối các lĩnh vực còn lại của bảng (visitor_id)
Và cuối cùng mô tả một trường hợp được giải quyết gần đây.
Vì vậy có là một trang web-blogger, nơi người dùng viết bài viết, tin tức về chủ đề khác nhau, vì vậy một cái gì đó giống như Habr, chỉ là một thái độ hơn dân sự 🙂
Bạn có để tạo ra một hệ thống theo dõi cho tác giả, một cái gì đó như google analytics. Các tác giả của bài viết để xem ai, ở đâu và khi sẽ bài viết của mình. Thống kê phải trên số lượng chung quan điểm cho một thời gian nhất định, số liệu thống kê của Referrers từ trang web khác nhau và số liệu thống kê trên các tìm kiếm phổ biến nhất có người sử dụng từ công cụ tìm kiếm về bài đăng này.
Tất cả các số liệu thống kê được thu thập trong Nhật ký một và mỗi giờ cho Vương miện từ Nhật ký cho cơ sở dữ liệu, và sau đó tạo ra một tập tin đăng nhập mới và số liệu thống kê cho các giờ tiếp theo được viết có.
Tổng cộng có là:
Bài viết (Hãy gọi cho họ mục có ID riêng của họ)
referrery thu thập và xử lý cho mỗi bài viết + cụm người đến từ công cụ tìm kiếm
những báo cáo để tạo ra:
Trang lượt xem trong vòng cuối cùng giờ, 2, 6, 12, 24 h, tuần
cụm từ tìm kiếm vào mỗi bài viết cho cùng một khoảng thời gian
referrery trên mỗi bài viết cho cùng một khoảng thời gian
“phổ biến” nhất tìm kiếm cụm từ cho cùng một khoảng thời gian
“phổ biến” nhất referrery cho cùng một khoảng thời gian
Đã có một giải pháp làm việc tốt lên đến một điểm, nhưng sau khi một số lần truy cập đến trang web đã tăng lên đáng kể-công việc là chậm
Đây là cấu trúc của các bảng cho một báo cáo về các số liệu thống kê tổng thể, xấp xỉ là bảng cùng tồn tại cho các thống kê của referrers và tìm kiếm cụm từ

 

CREATE TABLE `mt_daily_entry_stats` (
`daily_entry_stats_entry_id` INTEGER(11) UNSIGNED NOT NULL,
`daily_entry_stats_views` INTEGER(11) UNSIGNED NOT NULL,
`daily_entry_stats_date` DATETIME NOT NULL,
PRIMARY KEY (`daily_entry_stats_entry_id`, `daily_entry_stats_date`),
KEY `daily_entry_stats_date` (`daily_entry_stats_date`)
)ENGINE=InnoDB

Vâng, theo bảng này, tùy thuộc vào loại truy vấn chạy thống kê:

SELECT
`stats`.`daily_entry_stats_entry_id`,
SUM(`stats`.`daily_entry_stats_views`) as `entry_stats_views`
FROM
`mt_daily_entry_stats` as `stats`
WHERE
`stats`.`daily_entry_stats_date` > NOW() — INTERVAL 24 HOUR
GROUP BY
`stats`.`daily_entry_stats_entry_id`
HAVING
`entry_stats_views` > 1000

Это решение стало работать медленно и нужно было разбираться почему.

вот Explain этого запроса

select_type: SIMPLE
table: stats
type: range
possible_keys: daily_entry_stats_date
key: daily_entry_stats_date
key_len: 8
ref: NULL
rows: 97644
Extra: Using where; Using temporary; Using filesort

1. so used Engine = InnoDB, the standard game for many masters use keys that are stored in a single file with data (unlike MyISAM where indexes are stored in a separate file), the data is sorted on the surogatnomu key and it is included in other keys, so it is very important that the PRIMARY KEY as possible and so queries against tables to InnoDB, using the PRIMARY KEY are made very quickly.
What happens with us: a composite key PRIMARY KEY (‘ daily_entry_stats_entry_id ‘,daily_entry_stats_date )
that takes 4 bytes (int) + 8 bytes (dèjttajm) = 12 bytes
2. Because the DATETIME data type takes quite a lot of space (8 bytes), it is probably more appropriate to a date that falls in the range 19702038 better represent the TIMESTAMP. But based on the lines of the manual
+ TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. IE run 2 additional operation when storing and retrieving dates. The best thing in this case the date stored in the INT
While the decision was offered many solutions to the problem. Different caching, create a temporary table containing the results of the inmemori to kverili it.
Even experimented with ‘ sharding. But in this case is untyped adapter also is ineffective, as the times reports, overlap, for example report 12:0 report includes data for 6:0. So reports for large periods also require data from multiple shards, with possibility of partišiga (degenerate sharding, fixed at the request of% andry%) supported in version 5.1, which has yet to release, so there are only attempts.
After sampling various options came to version with denormalizaciej. I.e., it was decided to set up some tables for each of the intervals of statisticsstats_hour, stats_2hour, stats_6hour, stats_12hour, stats_day
then you get rid of the GROUP BY clause. ...HAVING CLAUSE = WHERE CLAUSE.
And when you need to get the stats on any interval of time kverim the us table.
It has increased the number of tables for statistics. And data are stored redundantly. But caching (memcached) is quite serviceable.
The add/modify existing intervals is quite easy, you just need to make changes to the CZK-script that populates the tables.
My decision does not purport to be the best, it would be interesting how such a task would attack you.
I hope to read it in the comments)