昨天已經轉入100萬筆的測試資料了,今天開始來探討索引.
昨天已經轉入100萬筆的測試資料了,今天開始來探討索引.
(1)有無索引的差異
先來建立一個沒有索引的對照Table.

-- 複製結構
CREATE TABLE no_index_words
  LIKE million_words;
Query OK, 0 rows affected (0.28 sec)

-- 刪掉 index
ALTER TABLE no_index_words
 DROP INDEX word;

Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 複製資料
INSERT INTO no_index_words
SELECT *
  FROM million_words;
Query OK, 1000000 rows affected (39.89 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

-- 沒INDEX的Table,查詢一筆資料,所需花費的時間
SELECT *
  FROM no_index_words
 WHERE word = 'Oracle';

+--------+--------+
| id     | word   |
+--------+--------+
| 267038 | oracle |
+--------+--------+
1 row in set (0.22 sec)

-- 有INDEX的Table,查詢一筆資料,所需花費的時間
SELECT *
  FROM million_words
 WHERE word = 'Oracle';
+--------+--------+
| id     | word   |
+--------+--------+
| 267038 | oracle |
+--------+--------+
1 row in set (0.00 sec)

可以看出兩者有顯著的差異.

(2)MyISAM所使用的資料檔,索引檔及其內容

昨天有建立一個使用MyISAM引擎的Table,用來轉入文字檔,
叫source_words.
來看看MyISAM所使用的檔案及其大小

-rw-rw----. 1 mysql mysql 8.4K 2013-10-15 10:34 source_words.frm
-rw-rw----. 1 mysql mysql 9.2M 2013-10-15 10:36 source_words.MYD
-rw-rw----. 1 mysql mysql 7.8M 2013-10-15 10:36 source_words.MYI

frm 是定義檔, MYD是資料檔, MYI是索引檔.
可以看到索引檔也不小.

接著建立一個欄位少且資料量小的Table,來觀察資料檔與索引檔的內容.

CREATE TABLE ithelp1016a(
name CHAR(10) NOT NULL,
item CHAR(10) NOT NULL
)ENGINE=MyISAM;

--輸入資料
INSERT INTO ithelp1016a(name, item) VALUES
('bunko', 'bala'),
('akina', 'bala'),
('kiraran', 'bala'),
('yua', 'bala'),
('riku', 'bala'),
('ruri', 'bala'),
('kaori', 'bala');

--查看資料檔裡面資料的情形.
# od -c ithelp1016a.MYD

0000000 377   b   u   n   k   o                                        
0000020                                                               b
0000040   a   l   a                                                    
0000060                                                     377   a   k
0000100   i   n   a                                                    
0000120                                                   b   a   l   a
0000140                                                                
0000160                                         377   k   i   r   a   r
0000200   a   n                                                        
0000220                                       b   a   l   a            
0000240                                                                
0000260                             377   y   u   a                    
0000300                                                                
0000320                           b   a   l   a                        
0000340                                                                
0000360                 377   r   i   k   u                            
0000400                                                                
0000420               b   a   l   a                                    
0000440                                                                
0000460     377   r   u   r   i                                        
0000500                                                                
0000520   b   a   l   a                                                
0000540                                                         377   k
0000560   a   o   r   i                                                
0000600                                                       b   a   l
0000620   a                                                            
0000640                                            
0000653

可以看到是依照輸入的原始順序放置.
接著建立索引.

ALTER TABLE ithelp1016a
  ADD INDEX (name);

用OD 觀察 ithelp1016a.MYI 裡的部份內容:

0002000  \0   T 005   a   k   i   n   a  \0  \0  \0  \0  \0 001 005   b
0002020   u   n   k   o  \0  \0  \0  \0  \0  \0 005   k   a   o   r   i
0002040  \0  \0  \0  \0  \0 006 201 006   i   r   a   r   a   n  \0  \0
0002060  \0  \0  \0 002 004   r   i   k   u  \0  \0  \0  \0  \0 004 201
0002100 003   u   r   i  \0  \0  \0  \0  \0 005 003   y   u   a  \0  \0
0002120  \0  \0  \0 003  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0002140  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0

可以看到依照我們指定的name欄位,依照升冪順序排列.

或是看下圖,可以更清楚的了解.