{"id":20,"date":"2026-09-07T11:49:45","date_gmt":"2026-09-07T11:49:45","guid":{"rendered":"http:\/\/127.0.0.1:8080\/index.php\/2026\/09\/07\/chien-luoc-toi-uu-hoa-co-so-du-lieu-mysql-cho-website-quy-mo-lon\/"},"modified":"2026-09-07T11:49:45","modified_gmt":"2026-09-07T11:49:45","slug":"chien-luoc-toi-uu-hoa-co-so-du-lieu-mysql-cho-website-quy-mo-lon","status":"publish","type":"post","link":"https:\/\/zunliz.com\/index.php\/2026\/09\/07\/chien-luoc-toi-uu-hoa-co-so-du-lieu-mysql-cho-website-quy-mo-lon\/","title":{"rendered":"Chi\u1ebfn l\u01b0\u1ee3c T\u1ed1i \u01b0u h\u00f3a C\u01a1 s\u1edf D\u1eef li\u1ec7u MySQL cho Website Quy m\u00f4 L\u1edbn"},"content":{"rendered":"<p>C\u01a1 s\u1edf d\u1eef li\u1ec7u th\u01b0\u1eddng l\u00e0 \u0111i\u1ec3m ngh\u1ebdn (Bottleneck) l\u1edbn nh\u1ea5t c\u1ee7a b\u1ea5t k\u1ef3 \u1ee9ng d\u1ee5ng web n\u00e0o khi ch\u1ecbu t\u1ea3i cao. Vi\u1ec7c c\u1ea5u h\u00ecnh sai b\u1ed9 nh\u1edb \u0111\u1ec7m ho\u1eb7c thi\u1ebfu Index th\u00edch h\u1ee3p c\u00f3 th\u1ec3 khi\u1ebfn th\u1eddi gian ph\u1ea3n h\u1ed3i c\u1ee7a truy v\u1ea5n t\u0103ng t\u1eeb v\u00e0i mili-gi\u00e2y l\u00ean \u0111\u1ebfn h\u00e0ng ch\u1ee5c gi\u00e2y.<\/p>\n\n\n<h2 class=\"wp-block-heading\">1. Nguy\u00ean t\u1eafc \u0110\u00e1nh Index (Ch\u1ec9 m\u1ee5c) Hi\u1ec7u qu\u1ea3<\/h2>\n\n<p>Ch\u1ec9 m\u1ee5c trong MySQL t\u01b0\u01a1ng t\u1ef1 nh\u01b0 m\u1ee5c l\u1ee5c c\u1ee7a m\u1ed9t cu\u1ed1n s\u00e1ch. N\u1ebfu kh\u00f4ng c\u00f3 Index, MySQL bu\u1ed9c ph\u1ea3i th\u1ef1c hi\u1ec7n Full Table Scan (qu\u00e9t t\u1eebng d\u00f2ng trong \u1ed5 c\u1ee9ng):<\/p>\n<ul>\n  <li><strong>Composite Index (Ch\u1ec9 m\u1ee5c k\u1ebft h\u1ee3p):<\/strong> Tu\u00e2n th\u1ee7 quy t\u1eafc ti\u1ec1n t\u1ed1 b\u00ean tr\u00e1i (Leftmost Prefix Rule). V\u00ed d\u1ee5: Index tr\u00ean <code>(status, created_at)<\/code> s\u1ebd h\u1ed7 tr\u1ee3 t\u1ed1t cho c\u00e1c truy v\u1ea5n l\u1ecdc tr\u1ea1ng th\u00e1i v\u00e0 s\u1eafp x\u1ebfp theo ng\u00e0y.<\/li>\n  <li><strong>Tr\u00e1nh Over-Indexing:<\/strong> M\u1ed7i Index t\u1ea1o th\u00eam chi ph\u00ed ghi khi th\u1ef1c hi\u1ec7n l\u1ec7nh <code>INSERT<\/code>, <code>UPDATE<\/code>, <code>DELETE<\/code>. H\u00e3y ch\u1ec9 t\u1ea1o Index cho c\u00e1c c\u1ed9t th\u01b0\u1eddng xuy\u00ean xu\u1ea5t hi\u1ec7n trong m\u1ec7nh \u0111\u1ec1 <code>WHERE<\/code>, <code>JOIN<\/code> ho\u1eb7c <code>ORDER BY<\/code>.<\/li>\n<\/ul>\n\n\n<h2 class=\"wp-block-heading\">2. Ph\u00e2n t\u00edch Truy v\u1ea5n Ch\u1eadm v\u1edbi EXPLAIN PLAN<\/h2>\n\n<p>S\u1eed d\u1ee5ng l\u1ec7nh <code>EXPLAIN ANALYZE<\/code> \u0111\u1ec3 ki\u1ec3m tra ch\u00ednh x\u00e1c c\u00e1ch MySQL th\u1ef1c thi c\u00e2u truy v\u1ea5n:<\/p>\n\n<pre><code class=\"language-sql\">EXPLAIN ANALYZE \nSELECT p.id, p.post_title, u.display_name \nFROM wp_posts p\nINNER JOIN wp_users u ON p.post_author = u.ID\nWHERE p.post_status = 'publish' \n  AND p.post_type = 'post'\nORDER BY p.post_date DESC \nLIMIT 10;<\/code><\/pre>\n\n<p>C\u00e1c ch\u1ec9 s\u1ed1 c\u1ea7n \u0111\u1eb7c bi\u1ec7t l\u01b0u \u00fd:<\/p>\n<ul>\n  <li><strong>type = ALL:<\/strong> C\u1ea3nh b\u00e1o nguy hi\u1ec3m &#8211; MySQL \u0111ang qu\u00e9t to\u00e0n b\u1ed9 b\u1ea3ng d\u1eef li\u1ec7u.<\/li>\n  <li><strong>type = ref ho\u1eb7c range:<\/strong> MySQL \u0111ang s\u1eed d\u1ee5ng Index \u0111\u1ec3 tr\u1ecf tr\u1ef1c ti\u1ebfp \u0111\u1ebfn v\u00f9ng d\u1eef li\u1ec7u c\u1ea7n thi\u1ebft.<\/li>\n  <li><strong>Using filesort \/ Using temporary:<\/strong> C\u1ea7n xem x\u00e9t l\u1ea1i Index tr\u00ean c\u00e1c c\u1ed9t c\u1ee7a m\u1ec7nh \u0111\u1ec1 <code>ORDER BY<\/code> v\u00e0 <code>GROUP BY<\/code>.<\/li>\n<\/ul>\n\n\n<h2 class=\"wp-block-heading\">3. C\u1ea5u h\u00ecnh Tham s\u1ed1 InnoDB Buffer Pool trong my.ini \/ my.cnf<\/h2>\n\n<p><code>innodb_buffer_pool_size<\/code> l\u00e0 tham s\u1ed1 quan tr\u1ecdng nh\u1ea5t c\u1ee7a MySQL. \u0110\u1ed1i v\u1edbi m\u00e1y ch\u1ee7 c\u01a1 s\u1edf d\u1eef li\u1ec7u chuy\u00ean d\u1ee5ng, gi\u00e1 tr\u1ecb n\u00e0y n\u00ean \u0111\u01b0\u1ee3c \u0111\u1eb7t \u1edf m\u1ee9c <strong>60% &#8211; 75%<\/strong> t\u1ed5ng dung l\u01b0\u1ee3ng RAM v\u1eadt l\u00fd:<\/p>\n\n<pre><code class=\"language-ini\">[mysqld]\n# C\u1ea5p ph\u00e1t 4GB RAM cho b\u1ed9 \u0111\u1ec7m d\u1eef li\u1ec7u InnoDB\ninnodb_buffer_pool_size = 4G\ninnodb_buffer_pool_instances = 4\n\n# T\u1ed1i \u01b0u h\u00f3a ghi log giao d\u1ecbch\ninnodb_flush_log_at_trx_commit = 2\ninnodb_log_buffer_size = 64M\n\n# Qu\u1ea3n l\u00fd k\u1ebft n\u1ed1i\nmax_connections = 300\nconnect_timeout = 10\nwait_timeout = 60<\/code><\/pre>","protected":false},"excerpt":{"rendered":"<p>C\u01a1 s\u1edf d\u1eef li\u1ec7u th\u01b0\u1eddng l\u00e0 \u0111i\u1ec3m ngh\u1ebdn (Bottleneck) l\u1edbn nh\u1ea5t c\u1ee7a b\u1ea5t k\u1ef3 \u1ee9ng d\u1ee5ng web n\u00e0o khi ch\u1ecbu t\u1ea3i cao. Vi\u1ec7c c\u1ea5u h\u00ecnh sai b\u1ed9 nh\u1edb \u0111\u1ec7m ho\u1eb7c thi\u1ebfu Index th\u00edch h\u1ee3p c\u00f3 th\u1ec3 khi\u1ebfn th\u1eddi gian ph\u1ea3n h\u1ed3i c\u1ee7a truy v\u1ea5n t\u0103ng t\u1eeb v\u00e0i mili-gi\u00e2y l\u00ean \u0111\u1ebfn h\u00e0ng ch\u1ee5c gi\u00e2y. 1. Nguy\u00ean [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,3],"tags":[],"class_list":["post-20","post","type-post","status-publish","format-standard","hentry","category-bao-mat-devops","category-cong-nghe-lap-trinh"],"_links":{"self":[{"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/posts\/20","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/comments?post=20"}],"version-history":[{"count":0,"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/posts\/20\/revisions"}],"wp:attachment":[{"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/media?parent=20"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/categories?post=20"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zunliz.com\/index.php\/wp-json\/wp\/v2\/tags?post=20"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}