mysql 多列索引 详细说明

mysql的索引可以分为单列索引和多列索引，单列索引，请参考：添加mysql索引的3条原则

mysql可以为多个列创建一个索引，最多可以16列，多列索引可以视为包含通过连接索引列的值而创建值的排序数组。mysql多列索引适用场合：当你在where子句中为索引的第1个列指定已知的参数时，查询很快，即使你没有指定其它列的值，这里的其他列是多列索引里面，指定的其他列。

 

一，创建测试表index_test

1. mysql> CREATE TABLE `index_test` (  
2.     ->   `id` int(11) NOT NULL auto_increment,  
3.     ->   `user_id` int(11) NOT NULL,  
4.     ->   `username` varchar(20) NOT NULL,  
5.     ->   PRIMARY KEY  (`id`)  
6.     -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
7. Query OK, 0 rows affected (0.08 sec)  

二，创建多列索引

1，普通多列索引

1. mysql> ALTER TABLE index_test ADD index test( user_id, username );  
2. Query OK, 0 rows affected (0.07 sec)  
3. Records: 0  Duplicates: 0  Warnings: 0  

2，多列唯一索引

1. mysql> ALTER TABLE index_test ADD unique test( user_id, username );  
2. Query OK, 0 rows affected (0.06 sec)  
3. Records: 0  Duplicates: 0  Warnings: 0  

3，多列主键索引

1. mysql> ALTER TABLE index_test ADD primary key test( user_id, username );  
2. Query OK, 0 rows affected (0.06 sec)  
3. Records: 0  Duplicates: 0  Warnings: 0  

我们主键一般都是ID，并且是自增长的，如果有，就要先删除主键后在创建多列主键索引，不然会报错的，ERROR 1068(42000): Multiple primary key defined；

删除索引，可以用drop index test onindex_test

三，例子

1，使用多列索引的情况

例1，

查看复制打印?

1. mysql> explain select * from index_test where user_id=1\G;  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: index_test  
6.          type: ref  
7. possible_keys: test  
8.           key: test      //使用了索引test  
9.       key_len: 4  
10.           ref: const  
11.          rows: 1  
12.         Extra:  
13. 1 row in set (0.00 sec)  
14.   
15. ERROR:  
16. No query specified  

例2，

查看复制打印?

1. mysql> explain select * from index_test where user_id=1 and username='tank'\G;  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: index_test  
6.          type: ref  
7. possible_keys: test  
8.           key: test  
9.       key_len: 66  
10.           ref: const,const  
11.          rows: 1  
12.         Extra: Using where  
13. 1 row in set (0.00 sec)  
14.   
15. ERROR:  
16. No query specified  

例3，

查看复制打印?

1. mysql> explain select * from index_test where user_id=1 and (username='tank' or username='zhang')\G;  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: index_test  
6.          type: range  
7. possible_keys: test  
8.           key: test  
9.       key_len: 66  
10.           ref: NULL  
11.          rows: 2  
12.         Extra: Using where  
13. 1 row in set (0.00 sec)  
14.   
15. ERROR:  
16. No query specified  

2，不使用多列索引的情况

 

例4，

查看复制打印?

1. mysql> explain select * from index_test where user_id=1 or username='tank'\G;  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: index_test  
6.          type: ALL  
7. possible_keys: test       //列出了可能存在的索引  
8.           key: NULL       //但是并没有使用这个索引  
9.       key_len: NULL  
10.           ref: NULL  
11.          rows: 2  
12.         Extra: Using where  
13. 1 row in set (0.01 sec)  
14.   
15. ERROR:  
16. No query specified  

例5，

查看复制打印?

1. mysql> explain select * from index_test where username='tank'\G;  
2. *************************** 1. row ***************************  
3.            id: 1  
4.   select_type: SIMPLE  
5.         table: index_test  
6.          type: ALL  
7. possible_keys: NULL     //可能存在的索引都没有列出来  
8.           key: NULL     //也没有使用多列索引  
9.       key_len: NULL  
10.           ref: NULL  
11.          rows: 2  
12.         Extra: Using where  
13. 1 row in set (0.00 sec)  
14.   
15. ERROR:  
16. No query specified  

根据上面测试，多列索引的第一列很重要，以上面例子为例，就是user_id这一列。要想多列索列起作用，第一列必须要包含在内，如果要用到or，不要与第一列并行。看例4

1

转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1515.html