创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。创建索引有3种方式,这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将详细讲解这3种创建索引的方法。 CREATE TABLE index1 (id INT , name VARCHAR(20) , sex BOOLEAN , INDEX ( id) ); 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index1 \G *************************** 1. row *************************** Table: index1 Create Table: CREATE TABLE `index1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` tinyint(1) DEFAULT NULL, KEY `index1_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,id字段上已经建立了一个名为index1_id的索引。使用EXPLAIN语句可以查看索引是否被使用,SQL代码如下: mysql> EXPLAIN SELECT * FROM index1 where id=1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index1 type: ref possible_keys: index1_id key: index1_id key_len: 5 ref: const rows: 1 Extra: 1 row in set (0.00 sec) 上面结果显示,possible_keys和key处的值都为index1_id。说明index1_id索引已经存在,而且已经开始起作用。 CREATE TABLE index2 (id INT UNIQUE , name VARCHAR(20) , UNIQUE INDEX index2_id ( id ASC) ); 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index2 \G *************************** 1. row *************************** Table: index2 Create Table: CREATE TABLE `index2` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `index2_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,id字段上已经建立了一个名为index2_id的唯一性索引。这里的id字段可以没有进行唯一性约束,也可以在该字段上成功创建唯一性索引。但是,这样可能达不到提高查询速度的目的。 CREATE TABLE index3 (id INT , info VARCHAR(20) , FULLTEXT INDEX index3_info ( info ) )ENGINE=MyISAM; 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index3 \G *************************** 1. row *************************** Table: index3 Create Table: CREATE TABLE `index3` ( `id` int(11) DEFAULT NULL, `info` varchar(20) DEFAULT NULL, FULLTEXT KEY `index3_info` (`info`) ) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,info字段上已经建立了一个名为index3_info的全文索引。如果表的存储引擎不是MyISAM存储引擎,系统会提示"ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes"。 CREATE TABLE index4 (id INT , subject VARCHAR(30) , INDEX index4_st ( subject(10) ) ); 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index4 \G *************************** 1. row *************************** Table: index4 Create Table: CREATE TABLE `index4` ( `id` int(11) DEFAULT NULL, `subject` varchar(30) DEFAULT NULL, KEY `index4_st` (`subject`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,subject字段上已经建立了一个名为index4_st的单列索引。细心的读者可能会发现,subject字段长度为20,而index4_st索引的长度只有10。这样做的目的还是为了提高查询速度。对于字符型的数据,可以不用查询全部信息,而只查询其前面的若干字符信息。 CREATE TABLE index5 (id INT , name VARCHAR(20) , sex CHAR(4) , INDEX index5_ns ( name, sex ) ); 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index5 \G *************************** 1. row *************************** Table: index5 Create Table: CREATE TABLE `index5` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` char(4) DEFAULT NULL, KEY `index5_ns` (`name`,`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,name和sex字段上已经建立了一个名为index5_ns的单列索引。多列索引中,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。用EXPLAIN语句可以查看索引的使用情况。如果只是有name字段作为查询条件进行查询,显示结果如下: mysql> EXPLAIN select * from index5 where name='hjh' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index5 type: ref possible_keys: index5_ns key: index5_ns key_len: 83 ref: const rows: 1 Extra: Using index condition 1 row in set (0.00 sec) 结果显示,possible_keys和key的值都是index5_ns。额外信息(Extra)显示正在使用索引。这说明使用name字段进行索引时,索引index5_ns已经被使用。如果只使用sex字段作为查询条件进行查询,显示结果如下: mysql> EXPLAIN select * from index5 where sex='n' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: index5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec) 此时的结果显示,possible_keys和key的值都为NULL。额外信息(Extra)显示正在使用Where条件查询,而未使用索引。 CREATE TABLE index6 (id INT , space GEOMETRY NOT NULL, SPATIAL INDEX index6_sp (space ) )ENGINE=MyISAM; 运行结果显示创建成功,使用SHOW CREATE TABLE语句查看表的结构。显示如下: mysql> SHOW CREATE TABLE index6 \G *************************** 1. row *************************** Table: index6 Create Table: CREATE TABLE `index6` ( `id` int(11) DEFAULT NULL, `space` geometry NOT NULL, SPATIAL KEY `index6_sp` (`space`) ) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec) 结果可以看到,space字段上已经建立了一个名为index6_sp的空间索引。值得注意的是,space字段是非空的,而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到。 什 么是索引? CODE: CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL ); 后,我们完全随机把1000个不同name值插入到people表。 CODE: 创建索引,例如 CREATE INDEX <索引的名字>; ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); 创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) ); 唯一性索引 CODE: 创建索引,例如CREATE UNIQUE INDEX <索引的名字>; ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); 创建表 的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) ); 主 键 CODE: CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) ); 下面是我们插入到这个people表的数据: CODE: ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age); 由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后 再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录! CODE: firstname,lastname,age firstname,lastname firstname 从 另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及 (firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引: CODE: SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The following queries cannot use the index at all: SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17'; 选择索引 列 CODE: SELECT age ## 不使用索引 FROM people WHERE firstname='Mike' ## 考虑使用索引 AND lastname='Sullivan' ## 考虑使用索引 这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列 选择操作。因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子: CODE: SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ON people.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 AND lastname='Sullivan' ##考虑使用索引 与前面的例 子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid 列出现在join子句中,因此我们需要考虑创建该列的索引。 CODE: EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; 这个命令将返回下面这种分析结果: |
|