Tối ưu hóa mysql

Question

Có rất nhiều cách khác nhau để tối ưu hóa câu lệnh SQL nhằm nâng cao hiệu suất. Một trong số đó là đánh chỉ mục cho các trường của bảng

I. Index is what

index is việc cấu trúc dữ liệu, lưu trữ theo một cơ chế nào đó để tìm ra các bản ghi một cách nhanh chóng. Chỉ mục là rất cần thiết để tăng hiệu suất và ngày càng trở nên quan trọng hơn nếu dữ liệu trong cơ sở dữ liệu của bạn ngày càng lớn, đối với các hệ thống nhỏ thì việc đánh chỉ mục hầu như không có ý nghĩa, nhưng một khi dữ liệu trong db . Hiểu sai về chỉ mục có thể dẫn đến nguyên nhân làm cho hiệu suất không những không được cải thiện mà còn trở nên ì hơn. cách đơn giản nhất để hiểu về cơ chế làm việc của chỉ mục trong MySQL đó là nghĩ về việc đánh số trang trong một cuốn sách. Để tìm một chủ đề cụ thể trong cuốn sách, bạn thường tìm đến mục lục và tìm các trang số thứ bao nhiêu trong cuốn sách theo chủ đề mà bạn muốn. Trong MySQL có một cơ chế lưu trữ chỉ mục theo cách tương tự. Giải thích khi chạy câu lệnh MySQL sau

mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

Chỉ mục sẽ được đánh trên cột actor_id, MySQL sẽ sử dụng chỉ mục để tìm ra những dòng mà actor_id của nó là 5. Hay nói cách khác nó sẽ thực hiện công việc tìm kiếm dữ liệu trên chỉ mục giá trị và trả về bất kỳ dòng dữ liệu nào có chứa dữ liệu đầu vào. Một chỉ mục chứa dữ liệu từ một hoặc nhiều cột khác nhau trong bảng. Nếu bạn đánh chỉ mục cho nhiều cột thì cột thứ tự là rất quan trọng bởi vì MySQL chỉ có thể tìm kiếm hiệu quả về tiền tố bên trái của chỉ mục (sẽ nói rõ hơn qua các ví dụ bên dưới). Việc tạo chỉ mục trên 2 cột không giống với việc tạo chỉ mục trên 2 cột riêng rẽ

** Các loại chỉ mục** Mỗi loại được thiết kế cho các mục đích khác nhau, Chỉ mục được thực hiện thì ở tầng lưu trữ dữ liệu, do đó nó không có tiêu chuẩn nào cả. Có rất nhiều kiểu chỉ mục mà điển hình là chỉ mục cây B, chỉ mục băm, chỉ mục cây R, chỉ mục toàn văn

II. Chỉ mục B-Tree

Thông thường khi ta nói đến chỉ mục mà không chỉ rõ loại chỉ mục thì đó là chỉ mục chỉ mục B-Tree, nó sử dụng cấu trúc dữ liệu B-Tree để lưu trữ dữ liệu. Hầu hết các bộ lưu trữ dữ liệu của MySQL đều hỗ trợ B-Tree index. Ý tưởng chính của B-Tree đó là tất cả các giá trị được lưu theo một trật tự. và mỗi lá nút sẽ có khoảng cách chung với gốc. Một chỉ mục B-Tree có thể tăng tốc độ truy vấn nhanh vì bộ lưu trữ dữ liệu máy chủ sẽ không quét dữ liệu trên toàn bộ bảng để tìm dữ liệu cần có. Thay vào đó nó sẽ bắt đầu từ nút gốc, mỗi điểm trên nốt gốc sẽ lưu giữ các con trỏ trỏ tới các nốt con và bộ máy lưu trữ sẽ dựa trên các con trỏ này để quét dữ liệu. Nó sẽ tìm con trỏ bên phải bằng cách nhìn vào dữ liệu ở các trang nút, các trang nút có chứa dữ liệu của các nút con. Bộ lưu trữ sẽ xác định dữ liệu có tồn tại hay không hoặc dữ liệu được tìm thấy ở trang lá

B-Tree lưu trữ các cột được lập chỉ mục theo một thứ tự vì vậy chúng rất hữu ích cho việc tìm kiếm một khoảng dữ liệu. Ví dụ chỉ mục được đánh cho một trường dữ liệu kiểu văn bản, nếu tìm kiếm theo tên bắt đầu bằng một chữ cái nào đó thì việc tìm kiếm sẽ rất hiệu quả Giả sử có bảng

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f')not null, key(last_name, first_name, dob) );

chỉ mục sẽ chứa giá trị của last_name, first_name, và dob cho mỗi dòng dữ liệu,

Chú thích. Index sắp xếp dữ liệu thông qua thứ tự cột index trong lệnh tạo bảng

key(last_name, first_name, dob)

II. 1. Các loại truy vấn có thể sử dụng chỉ mục B-Tree

Chỉ mục B-Tree sẽ mang lại hiệu quả cho công việc tìm kiếm với đầy đủ giá trị khóa, phạm vi khóa hoặc tiền tố khóa. B-Tree chỉ thực sự hữu ích khi tìm kiếm từ phía bên trái của chỉ mục có nghĩa là. in example on index is

key(last_name, first_name, dob)

thì

select * from poeple where last_name=’Peter’ and first_name=’Smitth’ #index sẽ được sử dụng

nhưng câu dưới đây chỉ mục sẽ không được sử dụng

mysql> explain select * from People where first_name='Smitth' ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | People | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)

do not search from side-left of index key chain, tức là phải last_name trước rồi đến first_name rồi đến dob

Trường hợp dưới đây cũng không có tác dụng

mysql> explain select * from People where dob='1990' and first_name='Smitth';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | People | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set, 2 warnings (0.00 sec)

cho dù đổi lệnh trự trong điều kiện where also no effect because in the điều kiện không có last_name

Khớp toàn bộ giá trị Khớp tất cả khóa, giá trị của dữ liệu cho tất cả các cột trong chỉ mục

mysql> explain select * from People where last_name='Peter' and dob='1990' and first_name='Smitth';
+----+-------------+--------+------+---------------+--------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+---------------+--------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | People | ref  | b_tree_index  | b_tree_index | 107     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+---------------+--------------+---------+-------------------+------+-----------------------+
1 row in set, 4 warnings (0.04 sec)

Khớp một tiền tố ngoài cùng bên trái

Chỉ mục chỉ được áp dụng khi truy vấn cột đầu tiên last_name

________số 8_______

Khớp tiền tố cột

Có thể khớp với phần đầu của giá trị, nó chỉ được áp dụng cho cột đầu tiên của chỉ mục. Ví dụ tìm kiếm last_name bắt đầu bằng chữ "K" đúng là có giới hạn

mysql> explain select * from People where last_name like 'P%';
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | People | range | b_tree_index  | b_tree_index | 52      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+

Khớp với một khoảng dữ liệu

Cho phép khớp dữ liệu với một khoảng giá trị truyền vào, và nó chỉ áp dụng cho cột đầu tiên của chỉ mục

Khớp chính xác một phần và kết hợp khớp trường khác trong một khoảng dữ liệu

Chỉ mục này cho phép bạn tìm kiếm chính xác last_name sau đó tìm kiếm tên với một khoảng giá trị như first_name bắt đầu bằng "k" không có giới hạn

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f')not null, key(last_name, first_name, dob) );

0

Chỉ lập chỉ mục cho các câu truy vấn lấy dữ liệu Một lưu ý là B-Tree chỉ áp dụng cho việc truy vấn lấy dữ liệu chứ không có tác dụng cải thiện việc lưu trữ dữ liệu xuống db

Chế độ của B-Tree

Nó sẽ không hữu ích nếu việc tìm kiếm trong điều kiện câu lệnh không bắt đầu từ phía bên trái của các phím chỉ mục, như ở ví dụ trên

key(last_name, first_name, dob)

Nếu ta tìm kiếm theo first_name, dob => không có last_name, không bắt đầu từ phía bên trái của khóa nên chỉ mục sẽ không có tác dụng trong trường hợp này

Một điều nữa là chỉ mục sẽ không có tác dụng tìm kiếm một trường được kết thúc bởi một ký tự nào đó ví dụ

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f')not null, key(last_name, first_name, dob) );

2

có thể_keys là null

Chế độ tiếp theo là bạn không thể bỏ qua cột của chỉ mục, nghĩa là khi truy vấn nếu bạn không bắt đầu hoặc các trường tìm kiếm không có trong thứ tự của khóa thì chỉ mục sẽ không có tác dụng đối với toàn bộ các cột trong đó

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f')not null, key(last_name, first_name, dob) );

3

thì chỉ có cột đầu tiên last_name được sử dụng chỉ mục Một giới hạn nữa đó là khi trong điều kiện ở đâu của câu truy vấn nếu bất kỳ cột nào sử dụng điều kiện phạm vi thì những cột đứng sau nó sẽ không được lập chỉ mục ví dụ

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f')not null, key(last_name, first_name, dob) );

4

tìm kiếm họ kết thúc bằng chữ r, đây là phạm vi điều kiện. Vì vậy, khi tìm kiếm dob sẽ không sử dụng được chỉ mục, mà chỉ mục chỉ có tác dụng với 2 trường last_name và fist_name

Kết quả

Đánh index cho bảng dữ liệu khá quan trọng, nó giúp tăng hiệu năng truy vấn xuống db của hệ thống, bên cạnh đó nếu hiểu sai, áp dụng sai thì việc đánh Index trở nên vô nghĩa không có tác dụng tăng hiệu suất mà

programming mysql