索引

//创建普通索引
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 h
-> (
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> INDEX single_name(name(20))//单列
-> );
Query OK, 0 rows affected
//查看表的结构
mysql> SHOW CREATE TABLE h;
±------±----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------+
| h | CREATE TABLE h (
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 x2
-> (
-> 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 x2;
±------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x2 | CREATE TABLE x2 (
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 x3
-> (
-> 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 x3;
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x3 | CREATE TABLE x3 (
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 h2
-> (
-> 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 h2;
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| h2 | CREATE TABLE h2 (
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 h3
-> (
-> id INT,space GEOMETRY NOT NULL,
-> SPATIAL INDEX sp(space)
-> )ENGINE=MYISAM;//表的存储引擎为MYISAM
Query OK, 0 rows affected

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