next up previous contents
Next: Sequence 序列 Up: KEY 及 INDEX Previous: Index 索引鍵   Contents   DYWANG_HOME

組合索引鍵

  1. 多鍵組合索引又比單鍵索引的搜尋速度快,如果常搜尋兩個欄位的話,最好設定成組會索引鍵。
  2. 產生測試資料表 indextab。
    mariadb root@localhost:dyw> create table indextab(id int unsigned not null,lname
                                 varchar(20),fname varchar(20),phone varchar(20));  
    Query OK, 0 rows affected
    Time: 0.015s
    
  3. 查看資料表 indextab 的欄位特徵。
    mariadb root@localhost:dyw> desc indextab;                                      
    +-------+------------------+------+-----+---------+-------+
    | Field | Type             | Null | Key | Default | Extra |
    +-------+------------------+------+-----+---------+-------+
    | id    | int(10) unsigned | NO   |     | <null>  |       |
    | lname | varchar(20)      | YES  |     | <null>  |       |
    | fname | varchar(20)      | YES  |     | <null>  |       |
    | phone | varchar(20)      | YES  |     | <null>  |       |
    +-------+------------------+------+-----+---------+-------+
    
    4 rows in set
    Time: 0.027s
    
  4. 設定 lname 及 fname 組合成名為 name 的索引鍵。
    mysql root@localhost:dyw> alter table indextab add index name (lname, fname);    
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.305s
    
  5. 新增兩筆資料。
    mariadb root@localhost:dyw> insert into indextab(id,lname,fname,phone) values (1
                                ,'wang','dy','09123456'),(2,'lin','da','09234561'); 
    Query OK, 2 rows affected
    Time: 0.005s
    
  6. 使用 name 索引鍵查詢,但本範例僅兩筆資料,無法比對有無索引的搜尋速度差別。
    mariadb root@localhost:dyw> select * from indextab use index(name) where lname='
                                wang' and fname='dy';                               
    +----+-------+-------+----------+
    | id | lname | fname | phone    |
    +----+-------+-------+----------+
    | 1  | wang  | dy    | 09123456 |
    +----+-------+-------+----------+
    
    1 row in set
    Time: 0.025s
    
  7. 如果資料表中設定多個索引,搜尋時可用 ignore 指定不使用某個索引鍵查詢,例如:不使用 name 索引鍵查詢。
    mariadb root@localhost:dyw> select * from indextab ignore index(name) where lnam
                                e='wang' and fname='dy';                            
    +----+-------+-------+----------+
    | id | lname | fname | phone    |
    +----+-------+-------+----------+
    | 1  | wang  | dy    | 09123456 |
    +----+-------+-------+----------+
    
    1 row in set
    Time: 0.025s
    



De-Yu Wang 2020-04-07