mysql的索引可以分为单列索引和多列索引,单列索引,请参考:添加mysql索引的3条原则
mysql可以为多个列创建一个索引,最多可以16列,多列索引可以视为包含通过连接索引列的值而创建值的排序数组。mysql多列索引适用场合:当你在where子句中为索引的第1个列指定已知的参数时,查询很快,即使你没有指定其它列的值,这里的其他列是多列索引里面,指定的其他列。
一,创建测试表index_test
- mysql>
CREATE TABLE `index_test` ( -
-> `id` int(11) NOT NULL auto_increment, -
-> `user_id` int(11) NOT NULL, -
-> `username` varchar(20) NOT NULL, -
-> PRIMARY KEY (`id`) -
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; - Query
OK, 0 rows affected (0.08 sec)
二,创建多列索引
1,普通多列索引
- mysql>
ALTER TABLE index_test ADD index test( user_id, username ); - Query
OK, 0 rows affected (0.07 sec) - Records:
0 Duplicates: 0 Warnings: 0
2,多列唯一索引
- mysql>
ALTER TABLE index_test ADD unique test( user_id, username ); - Query
OK, 0 rows affected (0.06 sec) - Records:
0 Duplicates: 0 Warnings: 0
3,多列主键索引
- mysql>
ALTER TABLE index_test ADD primary key test( user_id, username ); - Query
OK, 0 rows affected (0.06 sec) - Records:
0 Duplicates: 0 Warnings: 0
我们主键一般都是ID,并且是自增长的,如果有,就要先删除主键后在创建多列主键索引,不然会报错的,ERROR 1068 (42000): Multiple primary key defined;
删除索引,可以用drop index test on index_test
三,例子
1,使用多列索引的情况
例1,
- mysql>
explain select * from index_test where user_id=1\G; - ***************************
1. row *************************** -
id: 1 -
select_type: SIMPLE -
table: index_test -
type: ref - possible_keys:
test -
key: test //使用了索引test -
key_len: 4 -
ref: const -
rows: 1 -
Extra: - 1
row in set (0.00 sec) -
- ERROR:
- No
query specified
例2,
- mysql>
explain select * from index_test where user_id=1 and username='tank'\G; - ***************************
1. row *************************** -
id: 1 -
select_type: SIMPLE -
table: index_test -
type: ref - possible_keys:
test -
key: test -
key_len: 66 -
ref: const,const -
rows: 1 -
Extra: Using where - 1
row in set (0.00 sec) -
- ERROR:
- No
query specified
例3,
- mysql>
explain select * from index_test where user_id=1 and (username='tank' or username='zhang')\G; - ***************************
1. row *************************** -
id: 1 -
select_type: SIMPLE -
table: index_test -
type: range - possible_keys:
test -
key: test -
key_len: 66 -
ref: NULL -
rows: 2 -
Extra: Using where - 1
row in set (0.00 sec) -
- ERROR:
- No
query specified
2,不使用多列索引的情况
例4,
- mysql>
explain select * from index_test where user_id=1 or username='tank'\G; - ***************************
1. row *************************** -
id: 1 -
select_type: SIMPLE -
table: index_test -
type: ALL - possible_keys:
test //列出了可能存在的索引 -
key: NULL //但是并没有使用这个索引 -
key_len: NULL -
ref: NULL -
rows: 2 -
Extra: Using where - 1
row in set (0.01 sec) -
- ERROR:
- No
query specified
例5,
- mysql>
explain select * from index_test where username='tank'\G; - ***************************
1. row *************************** -
id: 1 -
select_type: SIMPLE -
table: index_test -
type: ALL - possible_keys:
NULL //可能存在的索引都没有列出来 -
key: NULL //也没有使用多列索引 -
key_len: NULL -
ref: NULL -
rows: 2 -
Extra: Using where - 1
row in set (0.00 sec) -
- ERROR:
- No
query specified
根据上面测试,多列索引的第一列很重要,以上面例子为例,就是user_id这一列。要想多列索列起作用,第一列必须要包含在内,如果要用到or,不要与第一列并行。看例4
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1515.html