索引

//创建普通索引
mysql> CREATE TABLE x1
-> (
-> id INT,
-> name VARCHAR(20),
-> score FLOAT,
-> INDEX(id)
-> );
Query OK, 0 rows affected

//查看表的结构
mysql> SHOW CREATE TABLE x1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x1 | CREATE TABLE `x1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`score` float DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

//创建单列索引
mysql> CREATE TABLE x2
-> (
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> INDEX single_name(name(20))
-> );
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE x2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| x2 | CREATE TABLE `x2` (
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
KEY `single_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

//创建唯一索引
mysql> CREATE TABLE x3
-> (
-> id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> UNIQUE INDEX unique_id(id ASC)
-> );
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE x3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x3 | CREATE TABLE `x3` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

//创建全文索引
mysql> CREATE TABLE x4
-> (
-> id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> FULLTEXT INDEX fulltext_name(name)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE x4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x4 | CREATE TABLE `x4` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
FULLTEXT KEY `fulltext_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

//创建多列索引
mysql> CREATE TABLE x5
-> (
-> id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> INDEX multi(id,name(20))
-> );
Query OK, 0 rows affected

 

mysql> SHOW CREATE TABLE x5;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x5 | CREATE TABLE `x5` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
KEY `multi` (`id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

//查看索引是否被使用
mysql> EXPLAIN SELECT * FROM x1 WHERE id=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set

//空间索引
mysql> CREATE TABLE x6
-> (
-> id INT,space GEOMETRY NOT NULL,
-> SPATIAL INDEX sp(space)
-> )ENGINE=MYISAM;//表的存储引擎为MYISAM
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE x6;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| x6 | CREATE TABLE `x6` (
`id` int(11) DEFAULT NULL,
`space` geometry NOT NULL,
SPATIAL KEY `sp` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

2.使用CREATE INDEX语句在已有的表上创建索引

普通索引
CREATE INDEX 索引名称 ON 表名 （字段名）;
唯一索引
CREATE UNIQUE INDEX 索引名称 ON 表名 （字段名）;
单列索引
CREATE INDEX 索引名称 ON 表名 （字段名）;
多列索引
CREATE INDEX 索引名称 ON 表名 （字段名，字段名）;
全文索引
CREATE FULLTEXT INDEX 索引名称 ON 表名 （字段名）;
空间索引
CREATE SPATIAL INDEX 索引名称 ON 表名 （字段名）;

3.使用ALTER TABLE语句在已经存在表上创建索引

普通索引
ALTER TABLE 表名 ADD INDEX 索引名称 （字段名）;
唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称 （字段名）;
单列索引
ALTER TABLE 表名 ADD INDEX 索引名称 （字段名）；
多列索引
ALTER TABLE 表名 ADD INDEX 索引名称 （字段名）;
全文索引
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名称 （字段名);
空间索引
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名称 （字段名）；

删除索引
一、使用ALTER TABLE删除索引:ALTER TABLE 表名 DROP INDEX 索引名；
二、使用DROP INDEX删除索引:DROP INDEX 索引名 ON 表名;