MYSQL INDEX Ai có kinh nghiệm không?

English | Vietnamese

Rules

Main Forum

Portal

Member Listing

Statistics

Search

Reading Room
[Register]

Login [ | https ]

Forum Index

Thảo luận hệ điều hành Windows

MYSQL INDEX Ai có kinh nghiệm không?

[Question] MYSQL INDEX Ai có kinh nghiệm không?	24/09/2007 10:15:45 (+0700) \| #1 \| 86556

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Code:

KEY forumid (forumid),
KEY startdate (enddate, forumid, startdate)

Vẫn chưa hiểu lắm, sao lại có cái thì một, cái thì từ 2 trở lên, tác dụng của nó có giống nhau không?

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	26/09/2007 07:11:55 (+0700) \| #2 \| 87027

SuperChicken
Elite Member

Joined: 11/07/2006 18:31:27
Messages: 635
Location: bottom of hell
Offline

Tui vẫn chỉ hiểu MySQL INDEX ở mức lý thuyết, chưa ứng dụng nhiều, theo tui hiểu thì INDEX giúp cho quá trình SELECT dữ liệu nhanh hơn, chẳng hạn bạn thường SELECT dựa theo điều kiện của enddate và startdate thì bạn tạo INDEX cho 2 column này. Nhưng hình như từ khóa để tạo INDEX là chứ đâu phải KEY?
Code:

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

MySQL Manual wrote:

All MySQL data types can be indexed. Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations.

MySQL Manual wrote:

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 15 columns.

MySQL Manual wrote:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	27/09/2007 13:21:19 (+0700) \| #3 \| 87332

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Code:

| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)

Lý thuyết là vậy, nhưng ngồi cả buổi để explain mà không thấy một chút Using INDEX, toàn là Using where và Using filesort

Ngoài ra nếu index có nhiều cột, lúc query phải query đúng thứ tự thì mới sử dụng index, lý thuyết là vậy nhưng khi làm ra toàn là Using where. Hay là InnoDB có vấn đề với index nhỉ?

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	27/09/2007 15:39:29 (+0700) \| #4 \| 87340

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

Bạn post lên full cái table structure của bạn và câu lệnh explain của bạn luôn đi. Chứ không biết cái table của bạn nó ra sao và câu lệnh explain query của bạn nó thế nào thì khó nói lắm.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	28/09/2007 00:18:14 (+0700) \| #5 \| 87417

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Code:

CREATE TABLE `test` (
  `rowID` smallint(5) unsigned NOT NULL auto_increment,
  `lastname` varchar(35) collate latin1_general_ci NOT NULL,
  `firstname` varchar(35) collate latin1_general_ci NOT NULL,
  `email` varchar(55) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`rowID`),
  UNIQUE KEY `email` (`email`),
  KEY `name` (`lastname`,`firstname`)
) ENGINE=MyISAM ;

INSERT INTO `test` (`rowID`, `lastname`, `firstname`, `email`) VALUES 
(5, 'a', 'b', 'ab@email.com'),
(6, 'c', 'd', 'cd@email.com');

Query
Code:

EXPLAIN SELECT * FROM test WHERE lastname = 'a' AND firstname = 'b'

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	test 	ref 	name 	name 	74 	const,const 	1 	Using where

Khi nào không lấy rowID mà chỉ lấy lastname, firstname thì mới Using INDEX. Vậy hóa INDEX ra vô dụng, vì hầu như lúc nào cũng phải lấy rowID

Code:

EXPLAIN SELECT lastname, firstname, email FROM test WHERE lastname = 'a' AND firstname = 'b'

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	test 	ref 	name 	name 	74 	const,const 	1 	Using where

=================================================

Code:

EXPLAIN SELECT lastname, firstname FROM test WHERE lastname = 'a' AND firstname = 'b'

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	test 	ref 	name 	name 	74 	const,const 	1 	Using where; Using index

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	28/09/2007 04:30:28 (+0700) \| #6 \| 87472

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

Trong trường hợp của bạn thì không phải là MySQL nó không dùng index đâu mà nó là như vầy:

- Bạn chỉ cần nhìn vào 2 column chính là type và key. Trong trường hợp của bạn type=ref, đây là 1 câu lệnh "tốt" có sử dụng key và/hoặc index); key=name cho thấy mysql có thực sự sử dụng key/index.
- Ở cột extra, "Using index" cho biết là data bạn cần lấy đã nằm sẵn trong index nên không cần phải đọc table trên đĩa nữa. Cụ thể: lastname=a, firstname=b, mysql lưu trong index giá trị a và b. Nên khi bạn select chỉ có 2 column này thì đầu tiên mysql đọc index trước, mysql đã lấy được giá trị a và b trong idnex rồi nên không đọc table nữa.
Còn khi không có dòng "Using index" cho biết là data cần lấy phải đọc thêm từ table. Rõ ràng là đúng vì MySQL lúc này đang tìm trong key name, key name thì không chứa index của email nên mysql phải đọc thêm 1 row trong table để lấy giá trị của email.

Bạn có thể tham khảo thêm: http://dev.mysql.com/doc/refman/4.1/en/explain.html

Thân,

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	05/10/2007 03:44:40 (+0700) \| #7 \| 88711

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Có 2 trường hợp:

Index tách riêng: name, pass, email
Code:

mysql> EXPLAIN SELECT * FROM `t_user` LEFT JOIN `t_info` ON t_user.id = t_info.u_id WHERE name = 'admin' AND pwd= '123456' AND email = 'admin@localhost';
+----+-------------+--------+------+----------------+------+---------+-----------------+------+-------------+
| id | select_type | table  | type | possible_keys  | key  | key_len | ref             | rows | Extra       |
+----+-------------+--------+------+----------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | t_user | ref  | name,pwd,email | name | 257     | const           |    1 | Using where |
|  1 | SIMPLE      | t_info | ref  | u_id           | u_id | 4       | test.t_user.id |    1 |             |
+----+-------------+--------+------+----------------+------+---------+-----------------+------+-------------+

Index gộp chung: name+pass+email
Code:

mysql> EXPLAIN SELECT * FROM `t_user` LEFT JOIN `t_info` ON t_user.id = t_info.u_id WHERE name = 'admin' AND pwd= '123456' AND email = 'admin@localhost';
+----+-------------+--------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t_user | ref  | name          | name | 546     | const,const,const |    1 | Using where |
|  1 | SIMPLE      | t_info | ref  | u_id          | u_id | 4       | test.t_user.id   |    1 |             |
+----+-------------+--------+------+---------------+------+---------+-------------------+------+-------------+

Hic, không biết cái nào tốt hơn cái nào smilie

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	05/10/2007 09:54:49 (+0700) \| #8 \| 88774

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

Với dữ liệu nhỏ thì không khác biệt gì nhiều. Nhưng với dữ liệu lớn thì trường hợp 1 sẽ tốt hơn vì 2 lý do:
smilie

- chỉ có 1 index, trong khi trường hợp 2 db phải đọc nhiều (3) index.
- Key length của trường hợp một chỉ có 257, trường hợp 2 là 546 --> gấp đôi.!

Edit: nên hiểu là trường hợp 1 thì nhiều khi chỉ cần đọc 1 index đầu tiên là đã đủ (vì name có vẻ như là unique index) - trường hợp tốt nhất, còn trong trường hợp 2 thì lượng dữ liệu phải đọc "gấp 3 lần".

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	05/10/2007 12:10:54 (+0700) \| #9 \| 88817

thesirius114
Member

Joined: 04/01/2007 11:43:11
Messages: 10
Offline

Thông thường cái khóa chính là một index gồi, nếu trường hợp vì một lí do gì đấy, mình muốn tạo thêm index khác (non-cluster) thì nên, nên hạn chế, chỉ lấy 1 cột và thêm một index thôi.
Như anh nbthanh có nói, nếu trường hợp pé tí thì không sao, nhưng nếu là database hàng trăm ngàn record, thì nếu nó cứ đọc tới đọc lui mấy cái index, thì nó ko bị crash cũng uổng.
Còn nếu để chơi thì ...^^ hem sao, chừng 249 index (nếu được)

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	06/10/2007 06:43:46 (+0700) \| #10 \| 88950

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Không hiểu sao cái Phorum mà MySQL đang dùng lại tạo rất nhiều index gộp, còn tạo trùng index nữa. Không lẽ MySQL lại chọn bừa forum?

@thesirius114: Để chơi thì cứ index bừa, chẳng cần phải tìm hiểu rõ làm gì. Đọc ở index chắc đỡ bị crash hơn là scan full table.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	06/10/2007 06:49:49 (+0700) \| #11 \| 88951

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

114v wrote:

Không hiểu sao cái Phorum mà MySQL đang dùng lại tạo rất nhiều index gộp, còn tạo trùng index nữa. Không lẽ MySQL lại chọn bừa forum?

Chắc gì bản mà mysql đang dùng giống với cái bản public download smilie

Thử so sánh: "bản JForum mà HVA dùng lỗi tùm lum, chả lẽ HVA lại chọn bừa forum?". Nhưng trên thực tế thì bản JForum trên server của HVA khác xa bản JForum trên website của JForum.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	06/10/2007 08:07:01 (+0700) \| #12 \| 88963

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Vậy JForum trên HVA khác xa chỗ nào? Có khác xa về database structure hay chỉ thêm mod, việt hóa?

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	06/10/2007 09:19:01 (+0700) \| #13 \| 88970

thesirius114
Member

Joined: 04/01/2007 11:43:11
Messages: 10
Offline

Oài, cái đó thì ai mà trả lời được, dĩ nhiên đó là khía cạnh "ngầm", chứ nếu biết HVA đã thay đổi source JForum như thía nào thì .... "tính an toàn" sẽ bị mất đi hẳn đấy chứ. Có thể ko thay đổi nhiều, nhưng thay đổi đủ để fix các bug, và tránh để "ai đó" đoán được source để mà "thực hiện mưu đồ". ^^
@ Scan full table thì dĩ nhiên là dễ crash hơn hẳn rồi.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	06/10/2007 10:16:13 (+0700) \| #14 \| 88986

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

114v wrote:

Vậy JForum trên HVA khác xa chỗ nào? Có khác xa về database structure hay chỉ thêm mod, việt hóa?

Khác nhiều lắm, có thể hiểu đại khái là HVA lấy 1 version của JForum về và phát triển tiếp theo hướng nâng cao tính bảo mật.

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	09/10/2007 01:13:23 (+0700) \| #15 \| 89475

114v
Member

Joined: 08/07/2006 23:27:00
Messages: 191
Offline

Vấn đề chính ở đây là khác về database structure, cụ thể hơn là các indexes. Đang bàn về tối ưu hóa CSDL mà tự nhiên nói qua bảo mật trời!!!!!!!!!!

[Question] Re: MYSQL INDEX Ai có kinh nghiệm không?	09/10/2007 06:30:37 (+0700) \| #16 \| 89549

nbthanh
HVA Friend

Joined: 21/12/2001 14:51:51
Messages: 429
Offline

114v wrote:

Vấn đề chính ở đây là khác về database structure, cụ thể hơn là các indexes. Đang bàn về tối ưu hóa CSDL mà tự nhiên nói qua bảo mật trời!!!!!!!!!!

DOS có liên quan đến bảo mật không?
DB thiết kế dở làm cho người bên ngoài khai thác, query 1 trang liên tục làm cho db "chết" dẫn đến tình trạng bị DOS chẳng hạn.
Vậy thì performance có liên quan đến bảo mật không? smilie

Go to:

Users currently in here
1 Anonymous