Index Giúp Tăng Hiệu Năng Thực Hiện Như Thế Nào

Vũ Huy Tâm

Index là phương tiện rất mạnh để tăng hiệu năng thực hiện của câu lệnh. Bài post này sẽ cung cấp một ví dụ cho bạn thấy bên trong SQL Server sử dụng index để  tăng hiệu năng như thế nào. Ở đây tôi dùng database AdventureWork là database mẫu đi kèm với SQL Server (bạn có thể dowload database này về và cài vào nếu chưa có sẵn).

Trước hết ta hãy dùng bảng Sale.Customer để tạo ra hai bảng mới là Sale.Customer_noIndex và Sale.Customer_Index, đồng thời tạo 1 index trên trường CustomerID cho bảng Sale.Customer_Index:

SELECT *
INTO Sales.Customer_NoIndex
FROM Sales.Customer
 
SELECT *
INTO Sales.Customer_Index
FROM Sales.Customer
GO
CREATE INDEX Idx_Customer_Index_CustomerID ON Sales.Customer_Index(CustomerID)

Nay ta có hai câu lệnh SELECT sau để truy vấn hai bảng:

-- #1
SELECT CustomerID, CustomerType
FROM Sales.Customer_NoIndex
WHERE CustomerID = 11001
-- #2
SELECT CustomerID, CustomerType
FROM Sales.Customer_Index
WHERE CustomerID = 11001

Hai câu lệnh này sẽ cho cùng kết quả, khác biệt duy nhất là câu lệnh thứ hai truy vấn bảng Sales.Customer_Index có index trên trường cần tìm (CustomerID). Ta sẽ xem hai câu lệnh trên được thực hiện như thế nào bằng cách nhìn vào kế hoạch thực thi (execution plan) của chúng. Khi bắt đầu thực hiện một câu lệnh, SQL Server lên một kế hoạch gồm các bước sẽ tiến hành để thực thi câu lệnh đó, gọi là kế hoạch thực thi.Trên hàng công cụ bạn hãy bấm vào nút “Include Actual Execution Plan”. Khi đó, mỗi lần bạn chạy câu lệnh hệ thống sẽ vừa thực hiện câu lệnh vừa đồng thời trả lại kế hoạch thực thi mà nó đã dùng để thực hiện câu lệnh đó.

Bạn hãy bôi đen câu lệnh thứ nhất và thực hiện nó, ở tab “Execution plan” hiện ra kế hoạch thực thi như thế này:

Như vậy ta thấy hệ thống sẽ thực thi câu lệnh bằng cách duyệt qua cả bảng (table scan) và tìm ra các bản ghi thỏa mãn yêu cầu tìm kiếm. Thao tác duyệt bảng có nghĩa là hệ thống cần phải đọc tuần tự từng bản ghi từ đầu đến cuối để tìm ra kết quả. Trong trường hợp này, nó phải đọc toàn bộ 19 185 bản ghi và tìm ra bản ghi có CustomerID=11011. Đây là một thao tác rất chậm vì nó phải xử lý tất cả các bản ghi trong bảng. Nên nhớ hệ thống sẽ không dừng lại khi nó tìm được bản ghi đầu tiên có CustomerID=11011, vì nó không biết liệu còn bản ghi nào khác có giá trị CustomerID tương tự hay không, cho nên để chắc chắn trả lại kết quả đầy đủ hệ thống vẫn phải tiếp tục đọc các bản ghi còn lại. Ta có thể nhận xét thấy chi phí của thao tác duyệt bảng tăng tuyến tính cùng với số lượng bản ghi trong bảng (độ phức tạp là O(n)).

Giờ ta hãy thực hiện câu lệnh thứ hai, lần này kế hoạch thực thi sẽ như sau:

Lần này ta không thấy thao tác table scan nữa, mà thay vào đó là index seek và RID lookup. Index seek là khi hệ thống có thể nhảy đến được node trên cây index chứa khóa thỏa mãn yêu cầu tìm kiếm. Index là một cấu trúc dữ liệu có dạng B-tree, nên nó rất thích hợp với các thao tác tìm kiếm theo kiểu key=value, chỉ cần vài phép so sánh là hệ thống định vị được node chứa khóa cần tìm. Node này chứa khóa (trường được index, ở đây là giá trị của CustomerID) và RID là ID của bản ghi tương ứng trong bảng (đây là giá trị nội bộ chỉ dùng bên trong hệ thống, ta không truy cập được giá trị này). Vì thế bước tiếp theo là dùng RID này để nhảy đến bản ghi tương ứng trong bảng (RID lookup) để lấy các trường dữ liệu cần thiết. Với index seek, độ phức tạp giảm xuống thành O(logn), một bước tiến vượt bậc so với table scan.

Ta có thể so sánh chi phí của hai câu lệnh trên bằng cách thực hiện cả hai cùng nhau:

Ta thấy câu lệnh thứ nhất chiếm tới 95% tổng chi phí, trong khi câu lệnh thứ hai chỉ chiếm có 5%. Nói cách khác, index trên trường CustomerID đã giúp cho câu lệnh thực hiện nhanh lên đến 19 lần. Index đã giúp cho lượng dữ liệu hệ thống cần xử lý để tìm ra kết quả giảm xuống đến mức tối thiểu, và điều đó đã tạo ra bước nhảy về tốc độ. Từ đây ta rút ra một bài học quan trọng: Các trường thường được dùng trong mệnh đề WHERE là các ứng cử viên đầu tiên cần được tạo index.




Tags: , ,

35 Comments
Posted on 18/4/2010 | Categories: Index, Performance tuning

Các bài viết tương tự

Comments
  • Đức Anh (22/09/2010 5:52 pm)

    Cho em hỏi, lúc em tạo Table thì trong phần Indexes nó đã có sẵn Clustered Index của bảng đó rồi. Ví dụ như table CMS_Article thì nó có sẵn Indexes là PrK_Article_Article_ID (Clustered).

    Như vậy có phải là index luôn luôn được tạo tự động áp dụng vs khóa chính của bảng không?

    Nếu đã có index đấy thì coi như khi SELECT bản ghi có Id = abc thì tự động nó sẽ thực thi index seek và RID lookup phải ko anh :)

  • Vũ Huy Tâm (22/09/2010 6:59 pm)

    Chào bạn,

    Đúng rồi khi tạo khóa chính thì nó sẽ tự động tạo ra một clustered index, trừ khi bạn chỉ định index là nonclustered. Khi đã có index đấy rồi thì với SELECT dạng đó chỉ cần index seek là đủ (không cần lookup nữa). Ở ví dụ trên, index là nonclustered nên mới có thêm RID lookup. Có một bài này tôi vừa đăng có liên quan đến vấn đề bạn nêu:
    http://www.sqlviet.com/blog/clustered-index

  • TỐNG TUẤN ĐOAN (10/11/2010 12:36 am)

    CÁM ƠN BẠN RẤT NHIỀU, BÀI VIẾT RẤT CÓ ÍCH

  • H2o (12/11/2010 8:47 pm)

    Không ngờ độ chênh lệch lớn quá. Bạn đã làm mình hiểu rõ thêm về index.

  • Vuong (09/12/2010 10:17 am)

    Những bài viết của bạn rất hay, đọc quên thời gian luôn.

    Bạn có thể post 1 bài về fulltext search ko? Thanks nhiều.

  • Vuong (09/12/2010 10:22 am)

    ah còn nữa, phân đoạn bảng theo chiều ngang thì xử lý thế nào? Mình có đọc bài phân đoạn theo chiều dọc rất hay nhưng có vẽ hiếm khi áp dụng.

  • Vũ Huy Tâm (10/12/2010 5:23 am)

    @Vuong: hiện đã có một bài về phân đoạn ngang:
    http://www.sqlviet.com/blog/table-partitioning-tr...
    Lúc nào có điều kiện tôi sẽ viết thêm các bài khác về chủ đề này

  • Nguyen Hai (21/04/2011 10:55 am)

    Các bạn ơi có bạn nào biết cách nào vô hiệu hóa các thuộc tính không cần thiết trong sql không vậy chì mình với

    VD mình có 1 table

    CREATE TABLE test(

    user int PRIMARY KEY,

    email varchar(50) NOT NULL

    )

    ASP.NET -> INSERT INTO test(user, email) VALUES (123456, 'email@yahoo.com')

    Trước khi dùng lệnh INSERT trong code asp.net mình đã kiểm tra xem user và email đã có ai tạo chưa, song rồi mới gửi lệnh cho sql thực thi.

    Đến lượt thằng sql nó lại làm một lần kiểm tra xem user đã tồn tại chứ, email có phải null hay không quả là quá lạng phí

    Có bạn nào biết cách vô hiệu hóa mấy cái kiểm tra đó thì chỉ mình với nhé thank

    Như kiểu dùng NOCHECK Constraint để vô hiệu các rằng buộc ý [B]^v^[/B]

  • tunglam (29/08/2011 7:08 am)

    Bài viết rất thực tế. Cảm ơn bạn nhiều. :)

  • luu son (14/11/2011 9:38 pm)

    Bạn ơi mình dang tim hiểu về các thuật toán Thực thi câu lệnh JOIN trong SQL sever nhưng tai liệu tiếng việt có rất ít, bạn có tài liệu về:
    ∗ Nested-loop join
    ∗ Index join
    ∗ Sort-merge join
    ∗ Hash join
    mình xin với! Cảm ơn bạn nhiều!

  • Đỗ Đức Diện (01/01/2012 1:06 pm)

    các anh có thể cho em hỏi 0(n) và 0(log) là j ko à. Thanks

    • Thế anh (07/04/2013 2:17 am)

      O(n),O(log n) đó là đánh giá độ phức tạp việc thực hiện,

  • Đỗ Đức Diện (01/01/2012 1:20 pm)

    Rất cảm ơn anh Huy Tâm. Bài viết của anh thật sự bổ ích và dễ hiểu. Một thằng rốt như em và chưa biết j cũng đã hiểu đc, heheh. Thanks anh!

  • Đỗ Đức Diện (01/01/2012 1:30 pm)

    Anh Tâm làm ơn trả lời thắc mắc này với.
    “Sales.Customer” thì “Sales” hay chỉ là một cách anh tự đặt thôi à.
    Thanks anh.

    • Đỗ Đức Diện (01/01/2012 1:30 pm)

      Anh Tâm làm ơn trả lời thắc mắc này với.
      “Sales.Customer” thì “Sales” lài j hay chỉ là một cách anh tự đặt thôi à.
      Thanks anh.
      hjx cái trên em viết thiếu chữ.

  • Đỗ Đức Diện (01/01/2012 1:50 pm)

    :( Anh ơi cho em hỏi tại sao của em dùng bản Standad mà ko có cái phần “Execution plan” anh nhỉ

    • Đỗ Đức Diện (03/01/2012 1:25 pm)

      Hjx. ko đọc kỹ, hóa ra là ấn ở trên thanh công cụ

  • Vũ Huy Tâm (03/01/2012 9:30 am)

    - O(n): độ phức tạp tăng tuyến tính cùng với kích thước dữ liệu. O(logn): độ phức tạp chỉ tăng logn so với kích thước dữ liệu.
    - Trong “Sales.Customer” thì “Sales” là tên của schema.
    - “Execution plan” có trong bản Standard, có thể nút bấm không hiện ở hàng menu do một thiết lập nào đó.

  • TuyetHuong (23/05/2012 10:54 pm)

    Thank anh Tâm !!! Tất cả bài viết của anh đều rất có ích .

  • Trần Hoàng Thành (27/08/2012 10:21 am)

    Bác Tâm cho em hỏi 1 tí -

    Hiện nay em dự tính làm 1 bảng bao gồm tất cả các giao dịch phát sinh theo ngày, dự kiến 1 tháng có khoảng 12 triệu dòng, 12 tháng ~ 144 triệu dòng. Nên để cải thiện hiệu năng thì em chỉ nghĩ được đến tạo clustered index và nc index kết hợp với partitioning.

    Tuy nhiên, có một số thắc mắc nhờ bác giải đáp giúp về phần index:
    - Clustered index thì 1 bảng chỉ có duy nhất 1 cái, nên tạo 1 clustered index bao gồm 4 cột hay nên tạo clustered index cho 1 cột và tạo nc index cho 3 cột kia? Cái nào thì hơn?
    - Khi tạo non-clustered index, thì tạo cho 4 trường 1 lúc, có khác so với việc tạo 4 nc index cho 4 trường?

    Em vẫn hơi mơ hồ về hiệu năng của việc tạo 1 index cho 4 trường hay nên tạo 4 index, mỗi index cho 1 trường?

    Ngoài index và partitioning, thì cần thêm gì (t-SQL) để cải thiện hiệu năng truy vấn không bác?

    • Red Devilic (28/08/2012 9:52 pm)

      + Nếu trong hệ thống OLTP, tốt nhất nên tạo Clustered Index cho 1 cột và tạo non-cluster index cho 3 cột còn lại
      + Nếu để ý kĩ và tìm hiểu thêm, bạn sẽ thấy việc tạo Index trên 4 cột (A,B,C,D) thì khi truy vấn Index chỉ có tác dụng khi ở mệnh đề WHERE có các điều kiện liên quan đến cột A, (A,B), (A,B,C) hoặc (A,B,C,D) theo đúng thứ tự lúc tạo Index.
      Còn nếu truy vấn chỉ có 1 phần như liên quan đến B, C, hoặc (B,C) thì Index không thể sử dụng được

      Bạn xem lại logic trong truy vấn và tự đánh giá, tự test để xem cách nào là phù hợp nhất cho hệ thống của bạn

      + NGoài Index, Partitioning thì có rất nhiều cách khác để tăng hiệu năng truy vấn, nhưng cần nhất vẫn là kinh nghiệm :D

    • Vũ Huy Tâm (30/08/2012 11:30 am)

      - tạo index trên từng trường thường cung cấp nhiều lựa chọn hơn cho bộ Optimizer. Còn tạo 1 index gồm nhiều trường (composite index) chỉ có tác dụng khi bạn truy vấn theo đúng các trường được index (hoặc 1 phần nhưng phải chứa trường đầu tiên như Red Devilic đã nói). Thường bạn tạo composite index khi cần thực thi 1 ràng buộc (như ràng buộc duy nhất)
      - index là chìa khóa chính để tăng hiệu năng. Partitioning phát huy tác dụng trong một số tình huống nhất định (lượng dữ liệu đủ lớn, pattern truy cập dữ liệu, hệ thống đĩa…). Cách viết code cũng quan trọng (một số bài trong blog này đã chỉ ra). Ở mức kiến trúc hệ thống, bạn có thể tạo reporting server để chia xẻ tải thông qua các kỹ thuật như replication hay log shipping.

      “+ NGoài Index, Partitioning thì có rất nhiều cách khác để tăng hiệu năng truy vấn, nhưng cần nhất vẫn là kinh nghiệm”
      ^^^^
      @Red Devilic, rất mong bạn chia xẻ kinh nghiệm với mọi người thay vì nói khơi khơi như vậy ;)

  • Thịnh Phan (22/01/2013 11:57 pm)

    Chào anh Huy Tâm,

    Anh cho em hỏi
    Em có 1 table ‘t_table1′ ENGINE MyISAM trong đó có 3 field được khai báo như sau :

    `field1` tinyint(1) unsigned default NULL,
    `field2` tinyint(1) unsigned default NULL,
    `field3` tinyint(1) unsigned NOT NULL default ’0′,

    và em tạo 1 index:

    KEY `t_table1_index1` (`field1`,`field2`,`field3`),

    Khi em chạy câu SQL:

    EXPLAIN
    SELECT *
    FROM table1 AS c
    WHERE
    c.field1 = 1
    AND
    c.field2 = 0
    AND
    c.field3 = 0

    Thì nhận được kết quả:

    Select type: Simple
    tyle: All
    possible key: t_table1_index1
    key: NULL
    key_len: NULL
    rows: 1042
    extra: Using where

    Em không hiểu vì sao trường hợp này index không sử dụng được ?
    Ngoài ra với table này và câu SQL này có thể làm cách nào để tăng thêm perform không ?

    Cảm ơn anh.

  • Red Devilic (23/01/2013 3:19 am)

    Thông tin này là chưa đủ

    - Bảng ít dòng thì SQL Engine cũng không dùng Index mà sẽ scan cả bảng
    - Có vẻ Selectivity của các cột là quá cao nên B-Tree Index cũng không được sử dụng

    • Thịnh Phan (24/01/2013 2:35 am)

      @Red Devilic:

      - Bảng ít dòng thì SQL Engine cũng không dùng Index mà sẽ scan cả bảng
      => có 1776 dòng trong bảng này. Vì câu sql thứ 2 đã dùng index nên mình nghĩ đây không phải là nguyên nhân

      - Có vẻ Selectivity của các cột là quá cao nên B-Tree Index cũng không được sử dụng
      => câu SQL 1 cho ra kết quả trả về 10 records, SQL 2 trả về 0 record.
      Bạn giải thích rõ hơn về ý này được không ? Mình chưa hiểu lắm. Nói cụ thể bằng 2 câu SQL trên nhé.

      Thanks !

  • Thảo (11/02/2014 7:12 am)

    Cảm ơn bạn thật nhiều! Thật là bài viết giúp mình hiểu hơn về index! ^^

  • Nickel (25/09/2014 3:34 am)

    Vậy mấy bác cho hỏi
    Nếu bảng không có clustered index thì một giá trị RID nội bộ được dùng.
    Nếu bảng có clustered index thì mình thấy có giá trị Key Lookup được dùng

    Các bạn cho hỏi thì cái nào chạy tối ưu hơn vậy?

  • khoatnt10 (20/07/2015 12:45 am)

    A cho e hỏi khi tạo index có kèm theo include(column1,column2,…). Vậy include có tác dụng gì vậy ?

    • Banhxe0 (28/07/2015 3:34 am)

      include la bao gồm một danh sach column.Vd cặp User và pass thường đi kèm nên Index include(User,pass).

  • Trung Nguyễn (25/08/2015 11:43 pm)

    Hi anh,
    Cho em hỏi, hiện tại em đang có 1 vấn đề, trong Table của em hiện tại có 200,000 dòng, chạy ra thời gian ước lượng khoảng 12 giây, em đã dùng index.
    Vậy anh cho em hỏi, có cách nào để Table của e chạy nhanh hơn thời gian là 12 giây không ạ.
    Em cảm ơn.

  • thùy dung (21/09/2016 10:52 am)

    hiện tại là e lấy ví dụ rồi mà có vẫn table scan

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>