12/08/2018, 00:47

1-11 Làm nhanh hơn những thao tác trong SQL

Những perfomance tunning dễ dàng Perfomance tunning chính là một trong những vấn đề lớn đối với kĩ sư DB. Tại chương này sẽ giới thiệu những Tips có thể tiến hành một cách đơn giản và mang tính thực dụng cao. Mở đầu Perfomance tunning chính là một trong những vấn đề quan trọng mà kĩ sư ...

Những perfomance tunning dễ dàng Perfomance tunning chính là một trong những vấn đề lớn đối với kĩ sư DB. Tại chương này sẽ giới thiệu những Tips có thể tiến hành một cách đơn giản và mang tính thực dụng cao.

Mở đầu

Perfomance tunning chính là một trong những vấn đề quan trọng mà kĩ sư DB phải đối mặt. Ví dụ trong những nghiệp vị cần sự trả lời nhanh chóng như trong các dịch vụ web thì perfomance của SQL chính là vấn đề sinh tử đối với hệ thống.

Vậy, trong chương này, chuyển hướng quan tâm từ những thủ thuật để sử dụng những tính năng của SQL thì sẽ giới thiệu những kĩ thuật perfomance tunning để tốc hoá những thao tác của SQL cũng như để tốn ít bộ nhớ hơn.

Để thực hiện perfomance tunning một cách cẩn thận thì không thể thiếu được những kiến thức về những đặc trưng, tính năng của thực hiện đang sử dụng, những nguyên nhân làm chậm chương trình lại chắc chắn là không thể nằm mỗi ở SQL. Có nhiều lý do khác nhau làm nên việc đó như sự phân phối bộ nhớ không tốt, cấu thành của file không tốt. Vậy nên những phương pháp được giới thiệu ở đây không phải là thuốc vạn năng. Nhưng thực tế thì nguyên nhân việc perfomance của SQL là không tốt nằm chủ yếu ở sự không mang tính hiệu quả của SQL.

Tại chương này sẽ là tập hợp những phương pháp nhẹ nhàng chỉ để nhìn lại SQL. Nếu nó trở thành kim chỉ nam có ich cho những lúc trong nghiệp vụ thường ngày khi mà ta có cảm giác là SQL dạo này chậm quá thì thật là tốt.

Sử dụng tìm kiếm hiệu quả tốt

Trong SQL có tồn tại nhiều cách viết khác nhau để ra cùng một kết quả. Nếu sau này những code để đưa ra cùng một kết quả thì đều có perfomance giống nhau thì tốt nhưng thật tiếc rằng với kế hoạch thực hiện tối ưu hoá hiện nay thì ở một mức độ nào đó vẫn bị điều chỉnh bởi cấu tạo ngoài của code. Như vậy, trong trường hợp muốn đòi hỏi về perfomance thì nhất thiết phải có kiến thức về cách viết chỉ thị truy cập tối ưu hoá một cách hiệu quả.

Trong trường hợp lấy đầu vào là subquery thì so với IN thì dùng EXISTS

Vị từ IN từ sự tiện lợi và sự dễ hiểu trong code thì nó là một tool được dùng với tần số rất cao. Nhưng mặt trái của sự tiện lợi, nếu nhìn vào mặt perfomance của IN thì nó mang theo mình một sự nguy hiểm đối với sự tắc nghẽn. Vì được dùng nhiều trong code nên có rất nhiều trường hợp chỉ cần chỉnh lại vị từ IN một chút thôi thì cũng có thể hi vọng một sự cải thiện rõ rệt trong perfomance.

Nếu đầu vào của vị từ IN chỉ là list những giá trị như (1, 2, 3) thôi thì không cần phải để ý nhưng nếu đầu vào của IN là subquery thì cần thiết phải có sự chú ý trong sử dụng ở đây.

[NOT]IN và [NOT]EXISTS thì hầu hết trong những trường hợp sẽ trả lại kết quả giống nhau nhưng cả 2 bên nếu tạo subquery thì EXISTS thao tác nhanh hơn rất nhiều.

Ví dụ đây là bảng quản lý 2 lớp mà chúng ta đã gặp rất nhiều cho đến nay.

ClassA

id	name
1	Tanaka
2	Suzuki
3	Iseiin

id	name
1	Tanaka
2	Suzuki
3	Seionji

Câu hỏi là hãy chọn những học sinh tham gia lớp học tồn tại ở cả ClassA và ClassB. 2 SQL được viết dưới đây đều cho kết quả giống nhau nhưng EXISTS thì cho động tác nhanh hơn hẳn.

--Chậm
SELECT *
  FROM ClassA
 WHERE id IN (SELECT id
                FROM ClassB)

--Nhanh
SELECT *
  FROM ClassA A
 WHERE EXISTS
       (SELECT *
          FROM ClassB B
         WHERE A.id = B.id);

Kết quả

id       name
---      -------
1        Tanaka
2        Suzuki

Có 2 lý do có thể chờ đợi sự nhanh hơn của EXISTS

Nếu key có dán index (ở đây là id) thì thực hiện ở bảng ClassB sẽ không được thực hiện mà chỉ cần tham chiếu index là xong.
EXISTS tại mỗi dòng nếu tìm được sự đúng điều kiện trong mỗi dòng thì sự tìm kiếm sẽ dừng lại ngay ở đó, không nhất thiết phải tìm kiếm toàn bảng như IN. Điều này cũng giống như trong trường hợp NOT EXISTS.

Trong trường hợp argument của IN là subquery thì DB đầu tiên sẽ thực hiện từ subquery, kết quả đó sẽ được đưa vào bảng tính (infra view) sau đó sẽ chạy toàn bộ view. Cái này trong nhiều trường hợp sẽ tốn rất nhiều tài nguyên nhưng nếu là EXISTS thì sẽ không tạo nên những bảng tính như vậy nữa.

Như về độ dễ đọc trong code nguồn thì IN chắc chắn sẽ thằng EXISTS. Những code viết bằng IN thì nhìn qua cũng có thể hiểu được ý nghĩa. Vì vậy nếu sử dụng IN mà vẫn có được thời gian đối ứng đủ nhanh thì đối với SQL thì không nhất thiết phải viết lại sang EXISTS.

Mặt khác, đối với những DB gần đây thì người ta đã có tính toán cải thiện để ngay cả dùng IN cũng có thể cải thiện được perfomance. Có thể có một ngày nào đó mà kể cả ta dùng IN hay EXISTS cũng có thể có được perfomance giống nhau.

Trường hợp lấy argument là subquery thì so với dùng IN thì nên dùng kết hợp

Để cải thiện perfomance của IN thì không chỉ có EXISTS mà chúng ta cũng có thể có cách viết thay thế là kết hợp như dưới đây.

--Thay thế IN bằng kết hợp
SELECT A.id, B.id
  FROM ClassA A INNER JOIN ClassB B
    ON A.id = B.id;

Chỉ cần làm như thế này thì ít nhất chúng ta có thể sử dụng dãy id của 1 trong 2 bảng rồi có thể tạo bảng trung gian không cần qua subquery. Không thể so sánh việc này với việc đổi sang viết EXISTS cái nào tốt hơn nhưng trong trường hợp không có index thì chắc chúng ta nên dùng kết hợp hơn. Mặt khác, chúng ta sẽ nhìn sua nhưng cũng có những trường hợp so với dùng kết hợp thì dùng EXISTS sẽ tốt hơn nhiều.

Tránh sort

SQL khác với ngôn ngữ lập trình thủ tục, không phải user sẽ ra lệnh cho DB những phép toán sort. Tư tưởng thiết kế nên SQL cũng chính là giấu đi cái thủ tục đó từ người dùng.

Tuy nhiên đó không có nghĩa là trong DB không diễn ra sort. Ngược lại sort được thực hiện liên tục một cách trầm lặng. Chính vì vậy, kết cục thì người dùng cũng nên ý thức rằng trong phép toán nào thì sort cũng bị phát sinh. (Con đường đến với lý tưởng giấu đi những thủ tục đó chắc còn xa.)

Những phép toán đại diện làm phát sinh sort chính là.

GROUP BY
ORDER BY
Những hàm tập hợp (SUM, AVG, COUNT, MAX, MIN)
DISTINCT
Dấu phép toán tập hợp (UNION, INTERSECT, EXCEPT)
Hàm OLAP (RANK, ROW_NUMBER,...)

Sort chỉ được thực hiện trên bộ nhớ logic thôi thì vẫn còn không sao, nhưng không những thế nếu cũng tiến hành sort sử dụng bộ nhớ vật lý thì cùng với việc sẽ có những tiếng truy cập đĩa đặc biệt thì về mặt perfomance cũng bị giảm đáng kể. (Không có con số chính thức nhưng nó bị nói là chậm hơn 1 triệu lần so với bộ nhớ logic). Như vậy chúng ta đặt mục tiêu tránh (hoặc giảm nhẹ) những sort không cần thiết.

Sử dụng thành thạo option ALL trong dấu của phép toán tập hợp

Trong SQL có 3 dấu của phép toán tập hợp là UNION, INTERSECT, EXCEPT. Những dấu này khi sử dụng bình thường chắc chắn sẽ diễn ra sort để tiến hành xoá những dòng trùng.

SELECT * FROM ClassA
 UNION
SELECT * FROM ClassB;

id  name
--  --------
1   Tanaka
2   Suzuki
3   Ishuin
4   Seionji

Trong những trường hợp không cần để ý đến dòng trùng, hoặc trong những trường hợp chắc chắn từ trước sẽ không diễn ra sự trùng lặp dòng thì thay cho UNION thì hãy dùng UNION ALL. Như vậy thì sort sẽ không diễn ra.

SELECT * FROM ClassA
 UNION ALL
SELECT * FROM ClassB;

id  name
--  --------
1   Tanaka
2   Suzuki
3   Ishuin
1   Tanaka
2   Suzuki
3   Seionji

Trường hợp này cũng giống như trong INTERSECT và EXCEPT.

Option ALL này làm perfomance tunning thì rất tiện lợi nhưng có những không đồng bộ trong tình trạng thực hiện là một điểm khó khăn tuỳ theo DB. Dưới đây là bảng tham khảo đề ra tóm tắt tình trạng tại thời điểm hiện tại.

Tình trạng thực hiện của option ALL đối với dấu phép toán tập hợp

	Oracle	DB2	SQLServer	PostgreSQL	MySQL
UNION	○	○	○	○	○
INTERSECT	×	○	×	○	-
EXCEPT	×	○	×	○	-

Trong Oracle thì thay cho EXCEPT thì dùng MINUS. Trong MySQL thì cả INTERSECT và EXCEPT đều chưa được thực hiện. Như vậy đây cũng là một điểm thú vị khi chúng ta phải để ý đến độ hợp của SQL thông thường đến từng DB. DB2 quả thật thông minh. PostgreSQL thì free nhưng lại có phần để ý đến từng chi tiết như thế này thì quả thật là một sự xuất hiện mang tính học thuật. MySQL và SQLServer thì còn một bước nữa, Oracle thì ... không tốt cũng chả xấu, nói chung là mang tính cá nhân. Tôi nghĩ rằng có một ngày nào đó thì chúng ta có thể sử dụng option ALL như một điều đương nhiên, nhưng với thời điểm hiện tại thì mọi người cần nhớ những hạn chế này trong thực hiện trên từng DB.

Thay DISTINCT bằng EXISTS

DISTINCT cũng thực hiện sort để xoá đi những dòng trùng. Trong trường hợp sử dụng CASE để nhất ý kết quả của kết hợp 2 bảng, thì tại đây chúng ta có thể thay thế bằng EXISTS để tránh sort.

Items

item_no	item
10	FD
20	CD-R
30	MO
40	DVD

SalesHistory

|sale_date|item_no|quantily| |--------|--------| |2007-10-01|10|4| |2007-10-01|20|10| |2007-10-01|30|3| |2007-10-03|10|32| |2007-12-03|30|12| |2007-10-04|20|22| |2007-10-04|30|7|

Chúng ta suy nghĩ để chọn những sản phẩm có trong lịch sử bán hàng SalesHistory từ Items. Nếu nói một cách dễ hiểu hơn thì đó là tìm những sản phẩm có trong danh sách bán hàng. Ở đây chúng ta dùng IN cũng được nhưng cũng như nêu ở trước dùng kết hợp sẽ thích hơn. Tiếp theo đây nếu kết hợp item_no thì...

SELECT I.item_no
  FROM Items I INNER JOIN SalesHistory SH
    ON I.item_no = SH.item_no;

Đây là kết hợp 1 đối nhiều nên sẽ có sự trùng trong item_no. Như vậy chúng ta cần phải sử dụng DISTINCT.

SELECT DISTINCT I.item_no
  FROM Items I INNER JOIN SalesHistory SH
    ON I.item_no = SH.item_no;

item_no
-------
     10
     20
     30

Tuy nhiên, nếu ở đây dùng EXISTS

SELECT item_no
  FROM Items I
 WHERE EXISTS
           (SELECT *
              FROM SalesHistory SH
             WHERE I.item_no = SH.item_no);

Như thế này thì sort sẽ không xảy ra. Tuy nhiên EXISTS so cới kết hợp thì động tác nhanh hơn rất nhiều.

Sử dụng index bằng hàm số cực trị (MIN/MAX)

SQL có 2 hàm số cực trị là MIN và MAX.

Cả 2 đều làm phát sinh sort nhưng trong trường hợp trong dãy argument có tồn tại index thì chỉ cần scan index đó là xong và tránh được sự tìm kiếm trong thực bảng. Giả sử chúng ta sử dụng bảng Items như trên.

--Cần thiết phải tra toàn bảng
SELECT MAX(item)
  FROM Items;

--Có thể sử dụng index
SELECT MAX(item_no)
  FROM Items;

item_no là một key thêm một tầng hiệu quả. Trong trường hợp index phức hợp thì nếu là dãy đầu tiên thì vẫn hữu hiệu nên ngay cả sale_date trong bảng SalesHistory cũng có thể sử dụng được.

Đây không phải chúng ta làm mất đi chính sort mà chỉ là theo sự tăng tốc hoá tìm kiếm trong đoạn trước mà giảm lượng tài nguyên sử dụng đi.

Điều kiện có thể viết bằng WHERE thì không viết câu lệnh HAVING

Ví dụ như kết quả trả về của 2 truy vấn dưới đây là giống nhau

SELECT sale_date, SUM(quantily)
  FROM SalesHistory
 GROUP BY sale_date
HAVING sale_date = '2007-10-01';

SELECT sale_date, SUM(quantily)
  FROM SalesHistory
 WHERE sale_date = '2007-10-01'
 GROUP BY sale_date;

sale_date      sum(quantity)
------------   -------------
`2007-10-01'              17

Tuy nhiên, nhìn về phía perfomance thì truy vấn đằng sau mang lại tính hiệu quả hơn hẳn. Thông thường có 2 lý do. Đầu tiên, GROUP BY sẽ tiến hành sort nên tốt hơn thì nếu được thì làm gọn dòng từ trước thì sẽ giảm nhẹ cho sort sau này. Lý do thứ 2 là nếu được thì với điều kiện của WHERE thì có thể sử dụng được index. Câu lệnh HAVING sau khi tập hợp thì sẽ thiết lập điều kiện đối với view nhưng rất tiếc rằng có nhiều trường hợp mà view sau không kế thừa được index của bảng ban đầu.

Sử dụng index của GROUP BY và ORDER BY

GROUP BY và ORDER BY thông thường sẽ tiến hành sort để sắp xếp lại, chỉ bằng việc chỉ định key cho dãy tồn tại index thì có thể tốc hoá tìm kiếm cho sort. Đặc biệt là trong trường hợp chỉ định những dãy có mang những index duy nhất thì cũng có thể tiến hành bỏ qua sort. Nhưng cần phải một lần check xem thực hiện đang có có hỗ trợ tính năng đó không.

Index đó, có thực sự được sử dụng không?

Bình thường trong những bảng lớn ở một mức độ nào đó thì đều được đính index. Nguyên lý của index có thể dễ hiểu khi nghĩ rằng nó giống như dãy điểm trong ngôn ngữ C. So với việc tìm kiếm dãy object có kích thước lớn thì việc tìm kiếm điểm có kích thước nhỏ sẽ hiệu quả hơn. Hơn nữa, trong trường hợp B tree index phổ biến nhất thì đã được công phu để có thể tìm kiếm nhanh.

Vậy thì bây giờ, chúng ta coi như có index được gán trong một dãy tên là col_1. SQL dưới đây bằng việc sử dụng index đó sẽ tìm kiếm toàn bộ bảng trong hiện tại. Trong vô thức thì bạn có hay trót viết như sau không?

Tiến hành gia công tạo dãy sắp xếp theo thứ tự chữ cái

SELECT *
  FROM SomeTable
 WHERE col_1 * 1.1 >100

Người ta hay nói là SQL không phải ngôn ngữ tính toán. Trên thực tế thì những kĩ sư DB cũng không biến đổi công thức cho như thế này. Nếu bên phải điều kiện điều tra có công thức thì index sẽ được sử dụng. Như vậy, thay vào đó thì chúng ta chỉ cần dùng điều kiện này là được.

WHERE col_1 > 100/1.1

Giống như vậy, trường hợp phía bên trái có hàm số thì index cũng không được sử dụng.

SELECT *
  FROM SomeTable
 WHERE SUBSTR(col_1, 1, 1) = 'a';

Trong trường hợp kể cả như thế nào đi chăng nữa cũng muốn thực hiện phép tính phía bên trái thì chúng ta có phương pháp sử dụng sắp xếp hàm số nhưng cũng không khuyến khích sự sử dụng không dụng ý.

Trong trường hợp sử dụng index thì phía bên trái điều kiện để trần

Sử dụng vị từ IS NULL

Thông thường vì không tồn tại NULL trong dữ liệu index nên trong trường hợp chỉ định IS NULL hay IS NOT NULL thì không thể sử dụng được index.

SELECT *
  FROM SomeTable
 WHERE col_1 IS NULL;

Tại sao index không thể được sử dụng trong NULL thì tài vì NULL không phải là giá trị. Thứ không phải la giá trị không thể bao gồm trong tập hợp giá trị được.

Trong trường hợp thế nào đi chăng nữa cũng muốn sử dụng IS NOT NULL và điều kiện đồng giá trị thì chúng ta có phương pháp sau,

--Đề án thay thế IS NOT NULL
SELECT *
  FROM SomeTable
 WHERE col_1 > 0

Nguyên lý rất đơn giản, nếu sử dung dấu bất đẳng thức chỉ định số nhỏ hơn số nhỏ nhất thì tất cả nhưng giá trị trong bảng của col_1 sẽ được chọn. Chỉ có dòng NULL sẽ không được chọn và trả lại giá trị unknown trong điều kiện [col_1 > NULL]. Cách viêt như thế này quả là phong cách lập trình đúng nên sẽ là rối loại ý nghĩa của code nên kĩ thuật này không được khuyến khích sử dụng một cách tích cực. Mọi người nên để tâm nó như một thiết bị để ứng phó khẩn với những trường hợp bắt buộc phải dùng thôi.

Sử dụng dạng phủ định

Những dạng phủ định được nêu dưới đây sẽ không dùng được index.

<>
!=
NOT IN

Như vậy code tiếp theo đây cũng sẽ tiến hành tìm kiếm toàn bảng.

SELECT *
  FROM SomeTable
 WHERE col_1 <> 100;

Đang sử dụng OR

Trong trường hợp tìm kiếm riêng biệt từng col_1 và col_2 cả trường hợp tìm kiếm tập hợp phức (col_1, col_2) hay dùng điều kiện OR để kết hợp thì cũng không thể sử dụng được index nên so với AND thì sẽ trở thành tìm kiếm kém hiệu quả hơn.

SELECT *
  FROM SomeTable
 WHERE col_1>100
    OR col_2 = 'abc'

Trong trường hợp dù thế nào cũng muốn sử dụng OR thì thì có bitmap index nhưng tại đây thì nó có điểm trừ mang theo đó chính là tốn tài nguyên để làm mới nên trước khi sử dụng cần phải suy nghĩ về ưu nhược điểm để lựa chọn.

Trong trường hợp tìm kiếm phức hợp, thứ tự của dãy bị nhầm lẫn

Chúng ta coi như đã có index phức hợp được gắn vào (col_1, col_2, col_3).

Trong trường hợp đó, thứ tự chỉ định điều kiện rất quan trọng.

○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100;
× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10;

Chắc chắn đầu tiên của dãy thì phải viết điều kiện của col_1, cũng không được thay đổi thứ tự của điều kiện. Phía bên trong thì có những DB mà thứ tự thay đổi cũng có thể sử dụng được index nhưng so với trườn hợp đúng thứ tự thì perfomance sẽ giảm.

Sử dụng LIKE với điều kiên viết sau, hoặc viết giữa

Trong trường hợp sử dụng vị từ LIKE thì chỉ có thể tìm kiếm với forward match, ví dụ, đối với từ "Tìm kiếm" đối với forward match có nghĩa rằng ta sẽ dùng "Tìm kiếm kết quả", "Tìm kiễm dãy chữ", "Tìm kiếm động cơ",...

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

Để trình dịch tự động chuyển về dạng đúng

Ví dụ trường hợp viết điều kiện đối với col_1 được định nghĩa bởi mô hình char

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

Đối với dạng chuyển này thì không những sẽ phát sinh overhead mà còn không thể sử dụng được index. Hơn nữa nó có hàng trăm cái hại mà không có bất cứ một điểm lợi nào. Đừng nói đến chuyện sẽ không có lỗi xảy ra, đừng cảm thấy rắc rối mà phải để tâm để luôn đổi sang dạng đúng. (Đừng quên việc chuyển đổi không phải ở bên vế trái mà là thực hiện bên vế phải).

Giảm bảng trung gian

Trong SQL thì kết quả của subquery sẽ được sử dụng như một bảng mới, nó cũng có thể sử dụng được trong code ngang bằng những bảng thông thường. Tính trực giao cao như vậy nên SQL được gọi là ngôn ngữ có tính mềm dẻo cao nhưng nếu sử dụng quá nhiều bảng trung gian một cách không có dụng ý thì sẽ là nguyên nhân dẫn đến sự rớt của perfomance.

Điểm vấn đề của bảng trung gian chính là sẽ tiêu hao bộ nhớ để triển khai bảng và việc sử dụng index tồn tại trong bảng ban đầu cũng khó đi. Chính vì vậy nên trong giới hạn có thể thì việc giảm sử dụng bảng trung gian vô nghĩa chính là chìa khoá để nâng cao perfomance.

Cùng sử dụng câu lệnh HAVING

Điều kiện đối với kết quả sau khi tập hợp thì xác lập sử dụng câu lệnh HAVING chính là nguyên tắc. Đối với những kĩ sư chưa sử dụng quen câu lệnh HAVING sẽ có xu hướng tạo bảng trung gian như dưới đây và nhờ vào câu lệnh WHERE.

SELECT *
  FROM (SELECT sale_date, MAX(quantity) AS mã_qty
          FROM SalesHistory
         GROUP BY sale_date) TMP
 WHERE max_qty >= 10;

sale_date     tot_qty
---------     -------
07-10-01           10
07-10-03           32
07-10-04           22

Tuy nhiên điều kiện đối với kết quả thì chúng ta không nhất thiết phải tạo bảng trung gian cũng có thể xác lập sử dụng HAVING như tiếp theo đây.

SELECT sale_date, MAX(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING MAX(quantity) >= 10;

Câu lệnh HAVING vừa tính toán vừa thực hiện động tác đối với dãy nên so với WHERE sẽ thực hiện động tác sau khi bảng trung gian được tạo ra thì có tính hiệu quả cao hơn. Hơn nữa, code cũng trông gọn gàng hơn.

Trong trường hợp trong vị từ IN lại sử dụng phức hợp những key khác thì tập hợp lại 1 chỗ

Từ SQL-92 thì tính năng so sánh giữa các dòng đã được thêm vào. Theo đây thì trong đầu vào của IN và trong những vị từ so sánh như = ,<, > chúng ta có thể lấy không phải giá trị scalar mà là chuỗi những giá trị.

Ví dụ, chúng ta cùng nghĩ đến trường hợp kết hợp vị từ IN sử dụng phức hợp key. Key chính ở đây là dãy d.

SELECT id, state, city
  FROM Address1 A1
 WHERE state IN (SELECT state
                   FROM Address2 A2
                  WHERE A1.id = A2.id)
   AND city  IN (SELECT city
                   FROM Address2 A2
                  WHERE A1.id = A2.id);

Code này sử dụng 2 subquery. Tuy nhiên, nếu sử dụng key để kết hợp thì có thể tóm tắt thành 1 block.

SELECT *
  FROM Address1 A1
 WHERE id||state||city||
    IN (SELECT id||state||city
          FROM Address2 A2);

Tại phương pháp này có 2 lợi điểm so với kết hợp dãy chữ. Đầu tiên là không cần để ý đến chuyện phải chuyển đổi mô hình trong kết hợp. Một điểm nữa là có thể sử dụng được index mà không cần tiến hành gia công trong dãy.

Tiến hành kết hợp trước khi sử dụng phép toán tập hợp

Cũng đã từng đề cập trong "Cách sử dụng OUTERJOIN" cũng đã chạm đến nhưng trong trường hợp sử dụng kết hợp phép toán tập hợp và kết hợp thì kết hợp nên tiến hành trước. Như vậy chúng ta có thể lược được sự hình thành một bảng trung gian. Có thể làm được điều đó là vì kết hợp của phép toán tập hợp có chức năng như phép nhân. Trong trường hợp duy trì quan hệ 1 đối 1 hay 1 đối nhiều thì cũng không làm tăng số dòng của kết hợp. Hơn nữa, trong xác lập thông thường thì quan hệ nhiều đối nhiều chắc chắn được phân chia thành 2 quan hệ 1 đối nhiều nên có thể sử dụng được kĩ thuật này trong hầu hết tất cả các trường hợp.

Sử dụng một cách có kế hoạch view

View là một dụng cụ rất tiện lợi nên chắc nhiều người có thói quen sử dụng hàng ngày. Tuy nhiên, việc định nghĩa những view phức tạp sẽ là điểm trừ rất lớn về mặt perfomance. Đặc biệt những trường hợp trong query định nghĩa view có những phép toán như dưới đây thì đây sẽ trở thành một SQL không hiệu quả và ngẫu nhiên mời gọi sự giảm xuống về tốc độ.

Hàm số tập hợp (AVG, COUNT, SUM, MIN, MAX,...)
Dấu phép toán tập hợp (UNION, INTERSECT, EXCEPT,...)

Về cơ bản thì khi sử dụng phép toán như trên trong view thì cần chú ý. Gần đây, để chữa những khuyết điểm này của view thì ngày nay những DBMS sử dụng kĩ thuật Materialized View đã ngày càng tăng lên. Trong trường hợp định nghĩa những view phức tạp thì đây cũng trở thành một đề án để sử dụng.

Tóm tắt

Trong chương này chúng ta đã đi và nhìn những điểm lưu ý về perfomance tunning của SQL. Chương này đã giới thiệu vào điểm nhưng điều bản chất quan trọng của tunning này chỉ có một. Đó chính là Tìm ra những nút thắt và loại trừ trọng điểm những điểm đó.

Không chỉ giới hạn trong cơ sở dữ liệu hay SQL mà đối với toàn bộ máy tính, thứ trở thành nút thắt chính là bộ nhớ vật lý có nghĩa là sự truy cập file (Chính vì vậy nên ngay cả đối với máy tính cá nhân thì sự đưa vào hard disk có khả năng truy cập tốc độ cao hay làm tăng bộ nhớ logic sẽ làm tăng hiệu quả đối với perfomance). Những công cụ như giảm sort, sử dụng index hay giản lược bảng trung gian, tất cả đều hướng đến mục đích giảm sự truy cập vào bộ nhớ vật lý. Các bạn nhất định phải để tâm đến bản chất này.

Dưới đây là những điểm chính của chương.

Trong trường hợp sử dụng subquery với IN thì viết thay thế bằng EXISTS
Trong việc sử dụng index thì cơi bản là "Phía bên trái để trần"
SQL không rõ ràng ghi sort nhưng cần chú ý đến điểm có rất nhiều những phép tính ngầm thực hiện sort
Giảm nhiều nhất có thể những bảng trung gian thừa.

Về đề tài thông tin cao độ, trong đó có perfomance tunning thì có thể tham khảo những tài liệu dưới đây.

Joe Celko, プログラマのためのSQL 第2版

Tại chương 31, về phần SQL tunning sẽ có nhiều vấn đề cơ bản được nêu ra với phạm vi rộng. Tuy nhiên với bản 2001 thì tôi có cảm giác những thông tin hơi cũ.

SEのためのOracleチューニングハンドブック(2003）

Đây là cuốn sách rất tốt tóm tắt những điểm quan trọng về tunning. Quyển sách này viết là Oracle nhưng không lệ thuộc vào đó mà có tính áp dụng thông thường cao, đây là quyến sách nên tham khảo.

Bình luận về bài viết này

Hoàng Hải Đăng

24 chủ đề

7226 bài viết