Hi, I am

Ngô Tôn

I am a programmer.

Home / Programming / Database / Tăng tốc độ truy vấn MySQL

Tăng tốc độ truy vấn MySQL

1. Giới thiệu

Một trong những nhân tố ảnh hưởng lớn đến performance của hệ thống là truy vấn CSDL. Ở bài viết này sẽ đề cập đến một vài kỹ thuật tăng tốc MySQL

2. Các kỹ thuật

Chỉ SELECT những trường bạn cần

Thông thường ta hay sử dụng SELECT() để chọn tất cả các trường trong 1 row. Tuy nhiên, một dòng SELECT() có thể gây chậm khi chạy truy vấn, đặc biệt là trên các bảng dữ liệu lớn. Vì vậy, thay vì chọn tất cả mọi trường, chỉ cần chọn các trường bạn cần. Nó chiếm ít bộ nhớ trên máy chủ của bạn và do đó cho phép máy chủ CSDL của bạn sử dụng bộ nhớ trống để xử lý các truy vấn và các tiến trình khác.

Hơn nữa là dùng SELECT(*) trả về mọi trường sẽ gây cản trở việc sử dụng index.

Đánh index hợp lý

Trong cấu trúc dữ liệu và giải thuật, ta có các giải thuật tìm kiếm nhanh, đó luôn là phương pháp tìm kiếm nhanh nhất.

Các trường được thiết lập ở dạng index sẽ được sắp xếp trên một file riêng, khi chúng ta truy vấn dữ liệu thông qua các trường index, các giải thuật tìm kiếm sẽ phát huy tính hiệu quả tối đa của nó, đặc biệt là các trường index dạng số.

Với các trường hay được sử dụng để trong câu WHERE hay các trường FOREIGN_KEY, PRIMARY_KEY nên được đánh index, để MYSQL không phải duyệt qua toàn bộ database để tìm dữ liệu ta cần.

Với các cột được dùng để UPDATE thì không nên đánh index, vì khi UPDATE sẽ thực hiện update lại toàn bộ index nên câu UPDATE sẽ bị chậm, index chỉ nên sử dụng cho lệnh SELECT.

Sử dụng EXPLAIN để phân tích query giúp cho việc đánh index hợp lý hơn.

Sử dụng EXPLAIN để phân tích câu query

EXPLAIN là lệnh mà mọi lập trình viên sử dụng MySQL nên cần phải biết, nó là lệnh rất hữu ích trong MySQL, nó có thể cung cấp một số chi tiết tuyệt vời về các truy vấn được chạy, những chỉ số được sử dụng, bao nhiêu hàng cần kiểm tra thông qua, bảng tạm thời và những điều khó chịu khác mà bạn muốn tránh.

Có rất nhiều bài viết nói về cách sử dụng EXPLAIN, bạn nên dành nhiều thời gian để tìm hiểu về nó, sẽ rất hữu ích khi sử dụng MySQL

Hạn chế sử dụng DISTINCT

Lệnh DISTINCT làm chậm truy vấn dữ liệu, vậy nên nếu như không thật cần thiết thì không nên sử dụng DISTINCT

Chấp nhận dư thừa dữ liệu

Khi thiết kế Database, nếu thường tạo nhiều bảng cho các nhóm dữ liệu là điều ta đã được dạy, điều đó sẽ cho ra thiết kế đẹp mắt, dễ nhìn, tuy nhiên khi truy vấn dữ liệu ta phải xới tung nhiều bảng có quan hệ với nhau đế lấy ra vài dữ liệu, điều đó làm ảnh hưởng lớn đến tốc độ hệ thống

Vì vậy, nếu không thật cần thiết, thì ta có thể gộp vào 1 bảng, giảm bớt số bảng phải truy vấn, truy vấn trên 1 bảng sẽ luôn luôn nhanh hơn rất nhiều khi truy vấn trên nhiều bảng.

Tốt nhất là 1 câu query, không nên phải duyệt qua quá 3 bảng.

Hạn chế sử dụng nhiều INSERT cho nhiều row

Khi cần INSERT nhiều row, thay vì dùng lệnh INSERT cho từng row, ta có thể chỉ dùng 1 lệnh INSERT cho tất cả row, điều đó sẽ tăng tốc khá nhiều

Kết hợp SELECT khi cần INSERT với điều kiện gì đó thay vì sử dụng SELECT để lấy dữ liệu rồi chạy INSERT với từng record

Sử dụng kiểu dữ liệu của trường chính xác

Có rất nhiều các kiểu trường khác nhau trong MySQL, nhưng ta nên sử dụng những loại quen thuộc với chuỗi(string) và số(numeric) là tốt nhất cho dữ liệu.

Ví dụ, nếu chỉ lưu trữ các số 1, 2 và 3 trong một trường, hãy sử dụng TINYINT tốt hơn INTERGER. Nó chiếm ít không gian lưu trữ trên máy chủ.

Một vài thủ thuật khác

  • Nên hạn chế sử dụng các toán tử sau trong mệnh đề WHERE: “IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%abc'”, vì các toán tử này sẽ không sử dụng đặc tính của index mà sẽ quét toàn bộ bảng.

Ví dụ: nên sử dụng default cho các trường thay vì để null

  • Trong câu truy vấn có nhiều OR, thì nên dùng UNION ALL thì sẽ giúp tăng tốc độ truy vấn
  • Nếu như cần xác nhận sự tồn tại của record, thì không nên sử dụng COUNT(*) vì không hiệu quả, gây lãng phí bộ nhớ mà nên dùng EXISTS sẽ nhanh hơn, vì lệnh EXISTS sẽ quét nếu tìm thấy 1 record thỏa mãn thì dùng duyệt database.
  • Sử dụng CASE – WHEN trong lệnh UPDATE.

Giả sử ta cần update các record nhưng theo các trường hợp update khác nhau, nếu điều kiện cho các trường hợp không quá phức tạp thì thay vì xử lý bằng code web cho từng trường hợp rồi gọi lệnh UPDATE của mysql thì ta dùng CASE – WHEN trong lệnh UPDATE.

Ví dụ:

Ta có thể dùng với Mysql

Có rất nhiều lý do để tập luyện cho mình quen với lối viết code tối ưu. Điều đó sẽ giúp bạn tránh được rủi ro có thể xảy ra khi cơ sở dữ liệu thay đổi, đồng thời cải thiện hiệu suất làm việc của máy chủ nhờ giảm thiểu lưu lượng truyền tải qua mạng.

1. Giới thiệu Một trong những nhân tố ảnh hưởng lớn đến performance của hệ thống là truy vấn CSDL. Ở bài viết này sẽ đề cập đến một vài kỹ thuật tăng tốc MySQL 2. Các kỹ thuật Chỉ SELECT những trường bạn cần Thông thường ta hay sử dụng SELECT() để chọn tất cả các trường trong 1 row. Tuy nhiên, một dòng SELECT() có thể gây chậm khi chạy truy vấn, đặc biệt là trên các bảng dữ liệu lớn. Vì vậy, thay vì chọn tất cả mọi trường, chỉ cần chọn các trường bạn cần. Nó…

User Rating: 4.65 ( 1 votes)

About ngoton

Ngô Tôn is a programmer. He is crazy about new gadgets and up to date with latest technology trends. Use comments to notify any issues you are facing.

Leave a Reply

Your email address will not be published. Required fields are marked *