組合索引鍵

1. 多鍵組合索引又比單鍵索引的搜尋速度快，如果常搜尋兩個欄位的話，最好設定成組會索引鍵。登入 mysql。
   

   [root@kvm6 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe
   mysql 5.5.49
   mycli 1.19.0
   Chat: https://gitter.im/dbcli/mycli
   Mail: https://groups.google.com/forum/#!forum/mycli-users
   Home: http://mycli.net
   Thanks to the contributor - Phil Cohen
   

2. 使用 dyw 資料庫。
   

   mysql root@localhost:(none)> use dyw;                                           
   You are now connected to database "dyw" as user "root"
   Time: 0.001s
   

3. 產生測試資料表 indextab。
   

   mysql 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.028s
   

4. 查看資料表 indextab 的欄位特徵。
   

   mysql 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.031s
   

5. 設定 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
   

6. 新增兩筆資料。
   

   mysql 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.198s
   

7. 使用 name 索引鍵查詢，但本範例僅兩筆資料，無法比對有無索引的搜尋速度差別。
   

   mysql root@localhost:dyw> select * from indextab use index(name) where lname='wan
                             g' and fname='dy';                                     
   +----+-------+-------+----------+
   | id | lname | fname | phone    |
   +----+-------+-------+----------+
   | 1  | wang  | dy    | 09123456 |
   +----+-------+-------+----------+
   1 row in set
   Time: 0.028s
   

8. 如果資料表中設定多個索引，搜尋時可用 ignore 指定不使用某個索引鍵查詢，例如：不使用 name 索引鍵查詢。
   

   mysql root@localhost:dyw> select * from indextab ignore index(name) where lname='
                             wang' and fname='dy';                                  
   +----+-------+-------+----------+
   | id | lname | fname | phone    |
   +----+-------+-------+----------+
   | 1  | wang  | dy    | 09123456 |
   +----+-------+-------+----------+
   1 row in set
   Time: 0.029s