Thống kê truy vấn mysql

SQL là một ngôn ngữ không còn xa lạ với mọi lập trình viên và đối lập với lập trình viên phụ trợ việc làm chủ được SQL là một điều rất quan trọng. Trong quá trình làm việc của mình với SQL mình đã tham khảo rất nhiều nguồn để tối ưu hóa câu hỏi truy vấn được hoạt động của SQL. Các bài nói về tối ưu với SQL trên mạng thì rất nhiều nhưng mình chưa tìm được bài nào tổng hợp các kỹ thuật nên dùng để tối ưu với SQL. Bài viết này mình sẽ chia sẻ những kỹ thuật mình đang sử dụng để tối ưu hệ thống của mình với SQL cụ thể hơn là Mysql vì nhiều kiến ​​thức mình chưa thử ở các loại SQL khác

Bài này sẽ dựa trên kinh nghiệm của cá nhân nên mong được sự đóng góp của mọi người. Mình cập nhật bài viết này liên tục để cập nhật những kỹ thuật mới nhất mình sử dụng

Chỉ số là gì?

Index là một khái niệm rất quan trọng trong SQL, ai nghe cũng thấy nó và sử dụng nó hằng ngày nhưng chắc chắn mọi người đều có thể hiểu được cách một Database xây dựng index. Do đó khi một câu truy vấn của mọi người bị chậm thì rất khó có thể tạo ra chỉ mục tối ưu

Theo mình tìm hiểu thì cơ sở dữ liệu SQL thường sẽ tổ chức chỉ mục dưới 2 định dạng

• cây B. dựa theo kiến ​​trúc của cây cân bằng. Hỗ trợ truy vấn đa dạng hơn
• Băm. based on data structure Hash-Table. Định dạng này sẽ hỗ trợ định dạng 

   SELECT first_name, last_name
   FROM employees
   WHERE employee_id between 123 and 500 
     and subsidiary_id = 30
  

  5 rất tốt nhưng lại không hỗ trợ định dạng 

   SELECT first_name, last_name
   FROM employees
   WHERE employee_id between 123 and 500 
     and subsidiary_id = 30
  

  6

Và có thêm một dạng chỉ mục nữa cho tìm kiếm toàn văn là chỉ mục ngược tại bài viết này mình xin phép không nói đến dạng này ạ

Hash ít được sử dụng hiện nay và mình cũng chưa từng sử dụng nó trong dự án thực tế nên bài này mình chỉ nói đến dạng B-tree

Các hình ảnh dưới đây mình lấy từ SQL Performance đã giải thích. Mọi người mua sách ủng hộ tác giả nhé. Cuốn sách rất hay dạy chúng ta mọi thứ liên quan đến chỉ mục

Cấu trúc dữ liệu chỉ mục

Để hiểu được chỉ mục chúng ta cần phải biết được cấu trúc dữ liệu của một chỉ mục. Giả sử ta tạo một chỉ mục trên cột 2 định mức. Cơ sở dữ liệu sẽ tạo ra một dạng cấu trúc dữ liệu B-tree dựa trên các dữ liệu có trong cột 2 và các dữ liệu này sẽ được sắp xếp như hình bên dưới

Nút lá

Trong hình bên trên chúng ta sẽ chú ý đến các Nút Lá các Nút này sẽ cung cấp cho chúng ta địa chỉ để đọc dữ liệu từ bảng được lưu trong cơ sở dữ liệu

Tất nhiên sẽ có các Nút lá sách không được nút Nhánh chuyển tới, các nút Lá này sẽ được liên kết với nhau bằng danh liên kết đôi để đảm bảo được việc duyệt dữ liệu trong chỉ mục cây

Truyền chỉ mục

Tiếp theo, chúng ta sẽ xem xét cách duyệt dữ liệu trên chỉ mục để biết được tại sao nó lại nhanh hơn với công việc scan bảng rất nhiều

Giả sử chúng ta tìm thấy bản ghi dữ liệu có giá trị là 57. Cơ sở dữ liệu sẽ bắt đầu duyệt từ gốc của cây chỉ mục qua các Nút nhánh khác nhau cho đến khi đến đầu của Nút lá. Tiếp tục duyệt qua các Nút lá để tìm được giá trị 57

Do tree index đã được sắp xếp nên việc duyệt này sẽ rất nhanh và phụ thuộc vào độ sâu của cây và lượng dữ liệu có trong 1 Node (và KB và không thay đổi)

Với các thuật toán phát triển cây chỉ mục thì cây chỉ mục sẽ được gọi là đứa trẻ ngừng hoạt động so với mức độ phát triển của các Nút lá vì vậy duyệt cây chỉ mục để chúng ta có tốc độ quét bảng nhanh hơn

Theo ví dụ bên trên thì một Nút nhánh sẽ chứa 4 giá trị vì vậy ta sẽ tính toán độ sâu của cây theo công thức sau.

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

7

Cơ sở dữ liệu sẽ tối ưu hóa việc chọn số lượng phần tử sẽ tham gia vào cây chỉ mục sao cho cây có độ sâu thấp nhất

Ta có bảng dữ liệu sau để biết rõ hơn độ lớn của cây sẽ phát triển chậm như thế nào nếu mỗi Nút nhánh có 4 phần tử

Tạo Index để tối ưu truy vấn

Chúng ta đã hiểu index là gì và tại sao khi sử dụng index lại cho chúng ta kết quả tốt hơn. Việc tiếp theo được áp dụng vào các trường hợp thực tế

Có một lưu ý là trường bạn dùng để đánh chỉ mục thì nên giảm giá trị 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

8 vì giá trị này gây khó khăn cho cơ sở dữ liệu trong quá trình tạo cây chỉ mục

Mệnh đề ở đâu

truy vấn1

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123

Với câu truy vấn này, chúng ta chỉ cần tạo chỉ mục theo trường 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 để đạt được tốc độ tối ưu cho câu truy vấn

truy vấn2

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

Giả sử 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 không còn là duy nhất nữa (trường hợp nhất 2 công ty giả hạn) thì việc đánh chỉ số trên một trường 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 sẽ không phải là cách tối ưu nhất dành cho câu hỏi trên

Tại đây chúng ta cần đánh chỉ mục trên 2 trường 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 và 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

03

Truy vấn đánh trên >=2 trường khác nhau được gọi là chỉ mục nối. Việc đánh chỉ mục bù cơ sở dữ liệu sắp xếp dữ liệu theo trường đứng trước sau đó sẽ sắp xếp theo trường thứ 2

Vì vậy, việc lựa chọn các trường thứ tự trong chỉ mục dạng này là rất quan trọng ảnh hưởng trực tiếp đến chức năng của chỉ mục

Lời giới thiệu của mình cũng như sách mình đọc và nghiên cứu thì hãy chọn trường có chọn lọc nhất đứng đầu. Vì các trường có tính chọn bộ lọc cao (chọn lọc) nên khi tạo chỉ mục cây sẽ có mức độ thấp hơn. Điều này đúng cho các loại cơ sở dữ liệu có khái niệm chỉ mục nối

truy vấn3

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

Tương tự như câu query2 tại đây việc làm 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 là duy nhất thì chúng ta cần phải đánh chỉ mục trên cả 2 trường. Và ưu tiên điều kiện bằng 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

5 trước như thế nào sẽ tối ưu hóa câu truy vấn được ưu tiên

truy vấn4

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

0

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

Chúng ta có 2 truy vấn thì chúng ta chỉ cần tạo 1 index cho 2 trường 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 và 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

03 với 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 đầu. Mặc dù cách đánh chỉ mục như truy vấn3 mới là tối ưu nhất nhưng cũng nên tạo ít chỉ mục hơn

Vì dữ liệu sẽ được sắp xếp theo 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 nên chúng ta sẽ sử dụng chỉ mục này cho 2 câu truy vấn

Đến đây mọi người tự suy luận tiếp về đánh chỉ số cho lớn hơn 2 trường nhé chỉ cần nhớ là chỉ số là cây sắp xếp và duyệt chỉ mục giống cây đọc là có thể suy luận ra thôi

truy vấn5

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

0

Việc sử dụng các chức năng trong câu truy vấn thì phải đánh chỉ mục cả chức năng chứ không đánh vào trường được

Tại đây chúng ta tạo chỉ mục như sau 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

0

truy vấn6

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

2

Nếu câu truy vấn dạng này thì không thể đánh 1 chỉ mục để thỏa mãn 2 phạm vi truy vấn được

Vì vậy tại đây mình sẽ chọn trường đánh chỉ mục bất kỳ khả năng nào sau khi lọc bằng chỉ mục xong ít phải lọc trong cơ sở dữ liệu nhất có thể. Hoặc có thể đánh 2 chỉ mục cho cả 2 trường để cơ sở dữ liệu thực hiện hợp nhất dữ liệu và được gọi là Hợp nhất chỉ mục

Câu hỏi nếu cùng một câu truy vấn sử dụng 1 chỉ mục có thể giúp bạn quét hết trường hợp và tạo 2 chỉ mục để cơ sở dữ liệu gộp lại thì chọn cái nào?

chỉ số chậm

Lập chỉ mục cho câu truy vấn thực hiện nhanh hơn điều này đã quá quen rồi nhưng bạn có thể tạo chỉ mục cho truy vấn thực hiện chậm hơn hay không?

Câu trả lời là có nhé. Việc tạo chỉ mục không cẩn thận không hề giúp câu chọn của bạn nhanh hơn ngược lại khiến nó chậm hơn và hiển thị chỉ mục sẽ khiến cho chèn, cập nhật cũng chậm hơn dẫn đến hỏng cả 2 đường

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

3

Tại đây nếu bạn không đảm bảo về chỉ mục có thể bạn sẽ tạo chỉ mục trên trường 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

1

Với câu truy vấn này và trong bảng người dùng lớn và chỉ chứa 90% là 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

2 điều kiện này sẽ tạo ra truy vấn này nếu sử dụng chỉ mục sẽ chậm hơn với việc quét toàn bộ bảng

Lý do khiến câu truy vấn trên chậm mặc dù đã sử dụng chỉ mục là vì khi cơ sở dữ liệu sử dụng chỉ mục trên thì nó sẽ nhận được các địa chỉ ngẫu nhiên không nằm cạnh nhau trong bảng. Tiếp theo nó sẽ phải lân cận vào từng địa chỉ này để lọc dữ liệu

Điều này sẽ chậm hơn rất nhiều công việc scan bảng từ đầu đến cuối vì công việc scan sẽ chỉ là đọc các bản ghi liên tiếp

Tất nhiên thực tế sẽ không ai đánh chỉ mục kiểu thế cả nhưng mọi người cần phải chú ý trường hợp sau khi lọc qua chỉ mục rồi mà vẫn phải duyệt quá nhiều tài liệu trong bảng. Tuân theo nguyên tắc chọn trường chọn lọc đánh chỉ mục là một cách tránh trường hợp này

theo thứ tự, theo nhóm

Việc đánh chỉ mục cũng giúp ích rất nhiều cho các câu truy vấn sắp xếp theo nhóm hay bởi vì làm trên tập sắp xếp lúc nào tiết kiệm hơn

Xem thêm tin tuyển dụng trình SQL tại đây

Tham gia

Một trong các phép quan trọng hay được sử dụng nữa là phép tính Tham gia và nắm giữ là một số nguyên tắc cơ bản để giúp tham gia nhanh hơn là điều kiện cần thiết

Có 2 thuật toán chính được các cơ sở dữ liệu sử dụng khi thực hiện tham gia 2 bảng dữ liệu đó là

• Hash Join (Mysql phiên bản 8 trở lên mới Hỗ trợ thuật toán tham gia này)
• Tham gia vòng lặp lồng nhau

Tham gia vòng lặp lồng nhau

Đây là thuật toán biến phổ biến nhất và được áp dụng trong rất nhiều loại cơ sở dữ liệu khác nhau, vì vậy chúng ta sẽ tìm cách tối thiểu hóa các phép tính tham gia trước đây. Nghe tên chắc các bạn cũng đoán ra được là nó sẽ thực hiện 2 vòng lặp để kiểm tra dữ liệu của 2 bảng

Ta có truy vấn sau

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

6

And they ta create 2 index

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

7

Chúng ta sẽ có kế hoạch truy vấn sau

Ở đây ta thấy Cơ sở dữ liệu đã sử dụng cả 2 chỉ mục để thực hiện cho phép tham gia sẽ nhanh hơn

• emp_up_name để truy cập và lọc trường last_name
• sales_emp để thực hiện cho phép tìm kiếm tất cả các bản ghi được tìm thấy sau khi 

   SELECT first_name, last_name
   FROM employees
   WHERE employee_id between 123 and 500 
     and subsidiary_id = 30
  

  3 trong bảng 

   SELECT first_name, last_name
   FROM employees
   WHERE employee_id between 123 and 500 
     and subsidiary_id = 30
  

  4

Như thế kết luận rằng đánh chỉ mục trên trường được sử dụng để tham gia sẽ làm cho truy vấn câu tham gia sẽ nhanh hơn

Vậy thì chúng ta đánh 3 chỉ số liệu rằng có giúp câu truy vấn nhanh hơn không?

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

0

Câu trả lời là hoàn toàn không?

Ví dụ Khi thực hiện tham gia bảng A,B bằng trường c thì ta chỉ cần đánh chỉ mục trường c của bảng B mà thôi. Nhưng thực tế cơ sở dữ liệu không nhất thiết sẽ dữ liệu trở lại thứ tự tham gia cho bạn vì khi thay đổi thứ tư tham gia thì vẫn giữ được kết quả cuối cùng nhưng sẽ ảnh hưởng rất nhiều đến hiệu suất của Cơ sở dữ liệu

Có một quy tắc chung là Cơ sở dữ liệu sẽ luôn sắp xếp sao cho nó ít phải tra cứu bảng còn lại càng ít càng tốt. Theo truy vấn bên trên dù ta đảo ngược vị trí của bảng 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

5 và bảng 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

4 thì cơ sở dữ liệu vẫn sẽ đảo ngược thứ tư để đưa ra phương thức tham gia tốt nhất

Lời khuyên là nếu bạn không chắc chắn biết được bảng nào sẽ là bảng bên phải thì hãy dũng cảm giải thích truy vấn. Và đừng tham gia nhiều bảng quá sẽ không bộ tối ưu hóa của Cơ sở dữ liệu sẽ hơi khó chịu với lựa chọn thứ tự cho bạn đấy

Vòng lặp lồng nhau Tham gia với ORM

Thực tế hiện nay chúng ta thường xuyên sử dụng ORM để thao tác với Cơ sở dữ liệu vậy nên chúng ta nên biết ORM sẽ thực hiện phương pháp Nested Loop Join này như thế nào

Nếu không cẩn thận thì khi sử dụng ORM rất có thể bạn sẽ gặp phải 1 vấn đề là N+1 vấn đề chọn. Thay vì gửi 1 câu lệnh Tham gia lên cơ sở dữ liệu để nhận lại kết quả thì ORM bạn đang sử dụng sẽ gửi N+1 câu truy vấn khác nhau để lấy dữ liệu về

Ví dụ như câu truy vấn trên rất có thể loại ORM của bạn sẽ thực hiện các phép tính sau

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

1

Đầy tiên sẽ truy vấn lấy ra các bản ghi nhân viên trước sau đó với từng bản ghi nhân viên sẽ thực hiện 1 câu chọn nữa để lấy kết quả. Thực tế đây đúng là điều mà cơ sở dữ liệu thực sự sẽ làm khi bản gửi một câu tham gia truy vấn đầy đủ với thuật toán Nested Loop Join nhưng nó sẽ tiết kiệm điện rất nhiều thời gian giao tiếp giữa máy chủ và máy khách

Vì vậy, các bạn nên thoát khỏi trường hợp này nếu bạn thật sự muốn tham gia 2 bảng. Hãy hiển thị sql của ORM bạn đang sử dụng ra để kiểm tra và tìm kiếm trên mạng cách phục vụ khác nhé

Băm tham gia

Đây là một thuật toán giúp ta tiết kiệm thời gian tham gia hơn rất nhiều so với Nested Loop Join vì nó sẽ sử dụng cấu trúc dữ liệu Hash Table để lưu dữ liệu của 1 bảng lại. Nhưng không có gì là hoàn hảo cả vì sử dụng bảng băm nên nó sẽ rất khó nhớ

Ta has query after

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

2

Ta sẽ có kế hoạch truy vấn cho truy vấn này

Cơ sở dữ liệu sẽ tải hết dữ liệu 1 bảng vào bảng băm trước như ở đây ta thấy nó sẽ tải bảng nhân viên vì nó có kích thước nhỏ hơn. Với hình thức Tham gia này chúng ta chỉ cần đánh chỉ mục tại nơi vì khi thực hiện tham gia nó đã sử dụng bảng băm nên việc chỉ mục cho trường cần tham gia là không cần thiết

Ta tạo chỉ mục sau và sẽ có một kế hoạch truy vấn

Vì vậy, việc biết được cơ sở dữ liệu bạn đang sử dụng đang sử dụng thuật toán tham gia nào là điều rất cần thiết để biết cách nâng cao hiệu năng của phép tham gia

Theo như tài liệu của Mysql thì phiên bản 8. 0. 18 loại hỗ trợ mới tham gia này và nó cũng sẽ cung cấp cho bạn một số cấu hình như. tham gia_buffer_size, open_files_limit. Bạn nên tham khảo tại Link Mysql-Doc. Tại đây sẽ mô tả cho bạn biết khi nào sẽ sử dụng hash join và cách tối ưu hóa phép tính toán này

tham gia phân rã

Tham gia Phân tách là kỹ thuật sử dụng để tách một câu tham gia của bạn thành nhiều câu truy vấn khác nhau và sẽ thực hiện tham gia trên Ứng dụng của bạn để tối ưu hóa được loại bộ đệm các bạn đã lưu cũng như cơ sở dữ liệu mở rộng

Cụ thể truy vấn sau

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

3

Nếu sử dụng kỹ thuật Join Decomposition thì sẽ biến thành 3 câu query như sau

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

4

Bạn nhìn thấy lãng phí nhưng nếu bạn đã lưu bộ nhớ đệm được bảng thẻ và bảng tag_post trong bộ nhớ của Ứng dụng thì nó chỉ cần thực hiện đúng truy vấn cuối mà thôi. Điều này tận dụng tối đa bộ nhớ đệm của bạn cũng như khiến Ứng dụng nhanh hơn

Một ưu điểm nữa là ba bảng tag, tag_post, post có thể lưu ở các máy chủ khác nhau điều đó giúp mở rộng cơ sở dữ liệu dễ dàng

Trên thực tế, phương pháp này được sử dụng rất nhiều trong các ứng dụng hiệu suất cao và chúng ta cũng hoàn toàn áp dụng được vì nó thực tế trừ màn hình dành cho quản trị viên các quyền tham gia đều khá đơn giản

TRONG

Với Mysql phiên bản 5. 7 thì bạn làm ơn đừng sử dụng định dạng câu truy vấn này

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

5

Bởi vì nếu bạn muốn Mysql 5. 7 se thực hiện truy vấn phụ trong lệnh IN trước thì nó quay ngược lại truy vấn đó. Hãy nhìn cách nó làm

Nó sẽ quét hết bảng 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

7 trước sau đó với việc thực hiện so sánh với truy vấn phụ. If used Mysql 5. 7 thì bạn nên thay đổi nếu có truy vấn dạng này sang dạng tham gia. Đến phiên bản Mysql 8 thì điều này đã được cải tiến tiến trình nó sẽ thực hiện truy vấn phụ của câu trước đó

Cắt nhỏ một truy vấn

Kỹ thuật này mình sử dụng nhiều trong quá trình xóa dữ liệu với các bảng không có phân vùng theo phạm vi

Nghĩa là thay vì xóa 1 năm dữ liệu thì mình sẽ viết mã để xóa từng tuần 1 điều này khiến truy vấn của mình thực hiện nhanh hơn và khiến cơ sở dữ liệu phải khóa dữ liệu ít hơn dẫn đến cơ sở dữ liệu không bị tải cao. Trước khi biết đến điều này mình đã phải đợi hàng tiếng đồng hồ để cơ sở dữ liệu thực hiện cho phép xóa và nhiều khi mình phải Giết câu truy vấn ngu ngốc đó đi. Hy vọng các bạn chưa trải qua những nỗi đau thương như mình

Một ví dụ điển hình khác là khi thay đổi một bảng lớn trong sql. Các bạn chắc chắn đã trải qua cảm giác câu lệnh thay đổi của mình sẽ chạy mất hàng tiếng đồng hồ khi còn hơn thế nữa. Nguyên nhân của công việc này là Mysql sẽ tạo ra một bảng mới và sao chép tất cả dữ liệu của bảng cũ sang bảng mới và trong suốt quá trình này, bảng cũ sẽ bị khóa. Nếu bảng cần thay đổi có kích thước quá lớn thì đây là vấn đề

Áp dụng Chipping Up a Query ta sẽ làm như sau. Tạo một bảng mới sau đó sao chép chừng một chút từ bảng mới sang bảng cũ (khoảng cách hàng 1000). Nhưng trong quá trình sao chép này sẽ có dòng thay đổi giá trị của bảng cũ như. xóa,cập nhật,… Bạn cần tạo thêm trình kích hoạt để khi có cập nhật vào bảng cũ thì sẽ cập nhật vào bảng mới luôn hoặc cập nhật ra một bảng lưu thay đổi nữa cuối cùng sẽ thực hiện cập nhật giá trị của bảng mới thay đổi vào bảng 

Có một cách khác để không phải sử dụng trình kích hoạt mình để liên kết github cho các bạn tham khảo. github. com/github/gh-ost

Vách ngăn

Cùng với việc tạo chỉ mục thì việc tạo phân vùng cũng giúp câu câu hỏi của bạn có tốc độ nhanh hơn

Phân vùng trong sql sẽ phân chia bảng của bạn thành nhiều bảng nhỏ hơn. Do đó khi câu truy vấn của bạn phù hợp với phân vùng nào thì cơ sở dữ liệu chỉ cần đọc phân vùng đó lên thôi điều này sẽ giúp câu truy vấn nhanh hơn nhiều

Phân vùng rất được chú ý trong các cơ sở dữ liệu phân tán nhất là với các loại noSql được nhắc đến rất nhiều. Vì khi chia phân vùng một cách hợp lý thì phân vùng sẽ có thể được lưu ở các máy chủ khác nhau làm cho tăng hiệu năng truy vấn vì câu truy vấn của bạn sẽ được tính toán trên nhiều máy thay vì 1 máy

Trong Sql thì mình sử dụng chủ yếu dạng phân vùng phạm vi chia dữ liệu của mình theo thời gian để thực hiện truy vấn nhanh hơn và khi cần thì xóa 1 phân vùng sẽ nhanh hơn rất nhiều việc xóa bằng lệnh xóa

Có 2 định dạng phân vùng

• Băm
• Phạm vi

Lưu ý khi chia partition

Một bảng trong Sql của bạn có nhiều phân vùng thì thực tế nó sẽ là một bảng Logic chứa nhiều bảng Vật lý bên dưới. Nghĩa là khi bạn tạo chỉ mục trên bảng có phân vùng thì nó sẽ là chỉ mục của nhiều bảng khác nhau

Do đó nên khi truy vấn dữ liệu mà nằm trên nhiều phân vùng thì mặc dù bạn đã lập chỉ mục trên trường dùng cho phân vùng nhưng nó sẽ là truy vấn trên nhiều bảng khác nhau và sử dụng nhiều cây chỉ mục khác nhau điều đó khiến câu truy vấn sẽ không tận dụng được tối đa

Vì vậy với phân vùng dạng phạm vi thì đừng chia phạm vi quá nhỏ (mỗi ngày một phân vùng) và số lượng phân vùng có thể cũng có giới hạn ví dụ Myslq sẽ có tối đa 8192 cho một bảng dữ liệu

Loại cột

Khi thiết kế cơ sở dữ liệu thì chỉ nên chọn kiểu dữ liệu vừa đủ cho cột bạn đang sử dụng. Kiểu dữ liệu càng nhỏ sẽ giúp CPU tính toán càng nhanh cũng như sử dụng dung lượng nhỏ khi lưu

Một biến sai phổ biến mà bạn thấy bạn hay thắc mắc đó là sử dụng chuỗi để lưu kiểu số. Và sử dụng các kiểu số quá lớn để lưu số nhỏ

Chèn/cập nhật hàng loạt

Chèn

Câu hỏi là bạn muốn chèn 1 triệu dữ liệu vào cơ sở dữ liệu thì cách nào sẽ là cách tối ưu nhất. Nhiều bạn sẽ trả lời ngay là sử dụng chèn hàng loạt khung của bạn hoặc sử dụng. Điều đó đúng nhưng chúng ta nên biết câu lệnh sql sẽ là gì và các bạn khi sử dụng orm framework cũng nên cẩn thận vì nó cũng có rất nhiều cú lừa

Câu lệnh chèn dữ liệu vào cơ sở dữ liệu theo cách nhanh nhất là định dạng

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

6

Một số ORM sẽ sử dụng định dạng Batch như sau để chèn vào cơ sở dữ liệu

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

7

Đây sẽ là gửi nhiều câu chèn lên một lúc không phải định dạng chèn hàng loạt. Để giải quyết vấn đề này, bạn nên sử dụng cấu hình của jdbc là 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

8. Trước khi gửi lên máy chủ sql thì jdbc sẽ thực hiện viết lại truy vấn của bạn thành dạng có thể chèn nhanh nhất

Cập nhật

Bây giờ mong muốn cập nhật 1 triệu dữ liệu vào cơ sở dữ liệu thì cách nào cũng nhanh nhất. Cũng tương tự như chèn các cơ sở dữ liệu cung cấp cho chúng ta truy vấn sau để thực hiện cập nhật nhanh nhất

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

8

Có thể sử dụng thêm lệnh IF của SQL để xác định cập nhật giá trị mới hoặc giữ nguyên giá trị cũ khi thực hiện cập nhật

Bạn tham khảo tại Link sau. nhà phát triển. mysql. com/doc/refman/8. 0/vi/chèn-trên-d

Phân trang trong SQL

Khi học trong trường đại học và các bài hướng dẫn dành cho người mới mình từng đọc cũng như các framework nổi tiếng đang thực hiện phân trang bằng truy vấn này

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id = 123 and subsidiary_id = 30

9

Điều này sẽ trở nên vô cùng tốn phí nếu bù của bạn quá lớn giả sử GIỚI HẠN 10000, 20 thì cơ sở dữ liệu sẽ thực hiện tạo ra 10020 câu truy vấn và sau đó đi đến 10000 kết quả

Mình đề nghị tất cả mọi người nên chuyển sang một định dạng truy vấn khác như

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

0

Trong this, you need a field unique. Mình sẽ liên kết đến mọi người tham khảo tốc độ truy vấn sẽ tốt hơn như thế nào khi chuyển sang định dạng này

Với định dạng phân trang theo kiểu giới hạn,đặt hàng theo mình thường xuyên sử dụng trong các công việc tạo báo cáo vì nó giúp mình lấy hết dữ liệu ra nhưng không gây hết bộ nhớ cũng như nhanh hơn kiểu giới hạn,bù rất nhiều vì dữ liệu cần lấy

Một điểm tối ưu nữa của phương pháp này là rất có thể đặt hàng theo chỉ mục sẽ tận dụng được

Truy vấn – Chiến lược

Khi thực hiện chọn Mysql cung cấp cho bạn 3 loại truy vấn khác nhau

• thông thường
• con trỏ
• dòng

Thật sự cách đây một năm rồi, mình không biết được là Mysql cung cấp cho chúng ta 3 loại truy vấn khác nhau để khi mình gặp bài toán cần phải lấy một lượng lớn dữ liệu trong Mysql ra một cách nhanh nhất và không gây ra . Trước đó mình đã sử dụng định dạng cải tiến của Paging như trình bày ở bên trên nhưng nó vẫn chưa phải là cách nhanh nhất

Mình sẽ đính kèm Liên kết về dạng truy vấn này. https. // nhà phát triển. mysql. com/doc/connector-j/8. 0/en/connector-j-reference-implementation-notes. html

Bình thường

Bạn cần lấy hết dữ liệu của bảng phim để lưu xuống tệp. You done the question after

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

1

Khi bạn sử dụng ORM để lấy dữ liệu ra và nó sẽ tải hết dữ liệu vào RAM của bạn và nếu bảng phim đến quá thì nó sẽ gây ra bộ nhớ

Tại đây, bạn nghĩ mình nên sử dụng ứng dụng trực tiếp jdbc để lấy dữ liệu ra vì nó trả kết quả cho các Bộ kết quả và có hàm 

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

9 để lấy dữ liệu và bạn hy vọng nó sẽ lưu vào bộ nhớ đệm 1 phần dưới Ứng dụng và khi nào sẽ thực hiện hết . Bạn vui vẻ thực hiện và kết quả của vấn đề là hết bộ nhớ

Khi bạn không config gì đặc biệt thì jdbc sẽ sử dụng định dạng truy vấn này cho bạn. Nó sẽ lấy hết dữ liệu từ máy chủ sql xuống lưu trong RAM và bạn sẽ lấy kết quả từ RAM ra

Thời trang này sẽ phù hợp với hầu hết như mọi yêu cầu của bạn trừ trường hợp bạn muốn lấy quá nhiều dữ liệu. Nếu muốn lấy quá nhiều dữ liệu mà không muốn ra khỏi bộ nhớ thì hãy sử dụng Paging kết nối với định dạng truy vấn này

con trỏ

also title as on but with a number config small as after

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

2

Khi thực hiện câu truy vấn

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

1

thì nó sẽ không tìm nạp hết dữ liệu về Ứng dụng cho bạn nữa và chỉ giới hạn 100 kết quả một lần nó sẽ không làm tăng bộ nhớ cho hệ thống của bạn

Nhưng khi sử dụng dạng truy vấn thì nó có các nhược điểm sau

• Tăng tải lên máy chủ sql. Máy chủ sẽ phải thực hiện tính toán câu truy vấn sau đó tạo vùng nhớ tạm thời để lưu kết quả của câu truy vấn sau khi lưu hết kết quả vào vùng nhớ tạm thời đó thì mới trả lại cho khách hàng cuối cùng sẽ xóa vùng nhớ đó đi. This thing is real cost that
• Kết quả sẽ lâu hơn vì phải đợi máy chủ lưu kết quả xuống ổ đĩa xong thì mới có thể trả kết quả cho khách hàng

Bản thân tôi không nên sử dụng loại này

Dòng

Đây là dạng truy vấn nhanh nhất mà bạn có thể sử dụng để lấy dữ liệu từ mysql ra và không gây hết bộ nhớ

Cũng là bài của loại bình thường với các cấu hình sau jdbc và máy chủ sẽ thực hiện kết quả trả về dưới dạng luồng

 SELECT first_name, last_name
 FROM employees
 WHERE employee_id between 123 and 500 
   and subsidiary_id = 30

4

Với loại ORM từng nhau sẽ có cách cho các bạn config khác nhau nên ở đây mình chỉ config cho jdbc

Với định dạng này, khi bạn thực sự cần dữ liệu, nó mới tìm nạp dữ liệu đó từ máy chủ về cho bạn

Nếu câu truy vấn của bạn quá phức tạp và việc sử dụng phân trang với định dạng bình thường mất quá nhiều thời gian thì bạn mới suy nghĩ chuyển sang định dạng luồng này vì nó cũng có một số điểm sau

• Khi nhận được kết quả thì kết nối không thể sử dụng để thực hiện truy vấn mới được nếu bạn trả kết nối vào nhóm thì sẽ hỏng nếu hệ thống sử dụng kết nối đó truy vấn tiếp tục
• Phải trả phí duy trì kết nối đến khi lấy hết dữ liệu và nếu không nó có thể bị mất kết nối thì sẽ có lỗi hỏng ra

Tổng kết

Bài này tổng hợp tất cả các kiến ​​thức của mình đã thực hiện sự việc đã được sử dụng trong các dự án dữ liệu để nâng cao hiệu suất tổng thể khi làm việc với mysql. Mình có thể cập nhật chúng vào bài viết này nếu mình tìm được một điều gì đó hay hơn hoặc là mình nhớ ra điều đó

Vì đây là bài viết tổng hợp nên có rất nhiều phần mình chỉ mím gón lại cách sử dụng chứ chưa nói cụ thể tại sao lại như thế nên các bạn nên tìm kiếm các tài liệu khác để nắm rõ hơn những phần đó hay ủng hộ mình . Hãy bình chọn sao vì nó miễn phí

Kiến thức mình có là do đồng nghiệp cung cấp khi làm các bài toán và từ 2 quyển sách rất tuyệt sau

• Giải thích hiệu suất SQL. Cảm ơn thầy Trần Việt Trung đã giúp em có kiến ​​thức đầu đời này
• Mysql hiệu suất cao. Cảm ơn anh Lâm đồng nghiệp đã cho em thêm 1 tài liệu hay

Mình khuyến khích các bạn đọc bài viết này của mình là có chỗ nào không hiểu thì đừng kích hoạt tin những gì mình viết hãy thực hiện luôn điều đó và biến nó thành kiến ​​thức của các bạn mình cũng đã có nhiều bài hướng dẫn về mạng lừa đảo nhiều rồi . Lập trình là môn thực thi

Bài này rất dài hy vọng các bạn có thể đọc hết và vote một sao trên github để ủng hộ mình nếu có ích cho các bạn