e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 由结果可以看到id,name,age字段上已经成功建立了一个名为MultiIdx的复合索引
我们向表插入两条数据
INSERT INTO t3(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihao')
使用EXPLAIN语句查看索引使用情况
?
?
EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME='小芳'
可以看到
possible_keys和
key 为MultiIdx证明使用了复合索引
?
?
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- -------- ------- ----------- ------ -----------
1 SIMPLE t3 ref MultiIdx MultiIdx 94 const,const 1 Using where 如果我们只指定name而不指定id
EXPLAIN SELECT * FROM t3 WHERE NAME='小芳'
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -----------
1 SIMPLE t3 ALL (NULL) (NULL) (NULL) (NULL) 2 Using where
结果跟SQLSERVER一样，也是不走索引， possible_keys和key都为NULL
全文索引
FULLTEXT索引可以用于全文搜索。只有MYISAM存储引擎支持FULLTEXT索引，并且只支持CHAR、VARCHAR和TEXT类型
全文索引不支持过滤索引。
CREATE TABLE t4 (
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR (255),
FULLTEXT INDEX FulltxtIdx (info)
) ENGINE = MYISAM
由于MYSQL5.6默认存储引擎为InnoDB，这里创建表的时候要修改表的存储引擎为MYISAM，不然创建索引会出错
?
SHOW CREATE TABLE t4
Table Create Table
------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t4 CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `FulltxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
?
由结果可以看到，info字段上已经成功建立名为FulltxtIdx的FULLTEXT索引。
全文索引非常适合大型数据集合
空间索引
空间索引必须在 MYISAM类型的表中创建，而且空间类型的字段必须为非空
建表t5
CREATE TABLE t5
(g GEOMETRY NOT NULL ,SPATIAL INDEX spatIdx(g))ENGINE=MYISAM
SHOW CREATE TABLE t5
TABLE CREATE TABLE
------ ---------------------------------------------------------------------------------------------------------------
t5 CREATE TABLE `t5` (
`g` GEOMETRY NOT NULL,
SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束
并且表的存储引擎为MYISAM
已经存在的表上创建索引
在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句
1、使用ALTER TABLE语句创建索引，语法如下
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]
[index_name](col_name[length],...)[ASC|DESC]
与创建表时创建索引的语法不同，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引
在t1表中的name字段上建立NameIdx普通索引
ALTER TABLE t1 ADD INDEX NameIdx(NAME)
添加索引之后，使用SHOW INDEX语句查看指定表中创建的索引
SHOW INDEX FROM t1
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT Index_comment
------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- -------------
t1 0 UniqIdx 1 id A 0 (NULL) (NULL) BTREE
t1 1 NameIdx 1 NAME A (NULL) (NULL) (NULL) BTREE
各个参数的含义
1、TABLE：要创建索引的表
2、Non_unique：索引非唯一，1代表是非唯一索引，0代表唯一索引
3、Key_name：索引的名称
4、Seq_in_index：该字段在索引中的位置，单列索引该值为1，复合索引为每个字段在索引定义中的顺序
5、Column_name：定义索引的列字段
6、Sub_part：索引的长度
7、NULL：该字段是否能为空值
8、Index_type：索引类型
可以看到，t1表已经存在了一个唯一索引
在t3表的age和info字段上创建复合索引
ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)
使用SHOW INDEX查看表中的索引
?