--索引
1.索引包含：
主键，唯一，普通单一，普通组合索引，全文索引，空间索引，HASH索引
2.索引创建
3.索引删除
4.索引的使用场景和约束
mysql> desc t1
-> ;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | MUL | NULL | |
| col2 | varchar(8) | YES | | NULL | |
| col3 | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--添加主键索引
mysql> alter table t1 add constraint pk_t1 primary key(col1);
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | PRI | NULL | |
| col2 | varchar(8) | YES | | NULL | |
| col3 | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 1 | a | XXX |
| 2 | b | XXX |
| 3 | c | XXX |
+------+------+------+
3 rows in set (0.00 sec)
mysql> 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 | PRIMARY | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t1 | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t2 | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t2 | 2 | col3 | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
--添加唯一索引
mysql> create index unique_idx_t1 on t1(col2);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | PRIMARY | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t1 | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t2 | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_t2 | 2 | col3 | A | 3 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | unique_idx_t1 | 1 | col2 | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.01 sec)
--删除索引
mysql> drop idx_t1 on t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'idx_t1 on t1' at line 1
mysql> drop index idx_t1 on t1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_t2 on t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | PRIMARY | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | unique_idx_t1 | 1 | col2 | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | PRI | NULL | |
| col2 | varchar(8) | YES | MUL | NULL | |
| col3 | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--创建单索引
mysql> creaet index idx_single on t1(col3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creaet index idx_single on t1(col3)' at line 1
mysql> create index idx_single on t1(col3);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | PRIMARY | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | unique_idx_t1 | 1 | col2 | A | 3 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_single | 1 | col3 | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
--创建多列组合索引
mysql> create index idx_join on t1(col1,col3);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | PRIMARY | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | unique_idx_t1 | 1 | col2 | A | 3 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_single | 1 | col3 | A | 3 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_join | 1 | col1 | A | 3 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_join | 2 | col3 | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
--新建表t2
mysql> create table t2 as select * from t1;
ERROR 1050 (42S01): Table 't2' already exists
mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 as select * from t1;
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | | NULL | |
| col2 | varchar(8) | YES | | NULL | |
| col3 | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--创建全文索引
mysql> create index fulltext on t2(col2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext on t2(col2)' at line 1
mysql> create fulltext index on t2(col2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on t2(col2)' at line 1
mysql> create fulltext full_idx on t2(col2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full_idx on t2(col2)' at line 1
mysql> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`col1` int(11) NOT NULL,
`col2` varchar(8) DEFAULT NULL,
`col3` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
--以上报错由于全文索引只支持myisam存储引擎引起，修改后重建
mysql> alter table t2 engine=myisam;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`col1` int(11) NOT NULL,
`col2` varchar(8) DEFAULT NULL,
`col3` varchar(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
--创建全文索引
mysql> create fulltext index full_idx on t2(col2);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | full_idx | 1 | col2 | NULL | NULL | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
--创建空间索引，空间索引对索引列类型要求为geometry
mysql> create spatial index idx_spa on t2(col3);
ERROR 1687 (42000): A SPATIAL index may only contain a geometrical type column
mysql> alter table t2 add col4 geometry;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | | NULL | |
| col2 | varchar(8) | YES | MUL | NULL | |
| col3 | varchar(8) | YES | | NULL | |
| col4 | geometry | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
--创建空间索引，同时要求表的存储引擎为MYISAM
mysql> create spatial index idx_spa on t2(col4);
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL
mysql> alter table t2 modify col4 geometry not null;
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | | NULL | |
| col2 | varchar(8) | YES | MUL | NULL | |
| col3 | varchar(8) | YES | | NULL | |
| col4 | geometry | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> select * from t2;
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| 1 | a | XXX | |
| 2 | b | XXX | |
| 3 | c | XXX | |
+------+------+------+------+
3 rows in set (0.00 sec)
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | full_idx | 1 | col2 | NULL | NULL | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
--由于表数据引起的创建失败，我们先将其truncate再建，先避免失败
mysql> create spatial index idx_spa on t2(col4);
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field
mysql> truncate table t2;
Query OK, 0 rows affected (0.00 sec)
mysql> create spatial index idx_spa on t2(col4);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | idx_spa | 1 | col4 | A | NULL | 32 | NULL | | SPATIAL | | |
| t2 | 1 | full_idx | 1 | col2 | NULL | NULL | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index idx_spa on t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index full_idx on t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t2;
Empty set (0.00 sec)
mysql> create table t3 as select * from t2;
ERROR 1050 (42S01): Table 't3' already exists
mysql> drop table t3;
Query OK, 0 rows affected (0.05 sec)
--创建新表T3
mysql> create table t3 as select * from t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | int(11) | NO | | NULL | |
| col2 | varchar(8) | YES | | NULL | |
| col3 | varchar(8) | YES | | NULL | |
| col4 | geometry | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
--从以下的测试报错也验证了上面一点，全文索引和空间索引不能在innodb引擎上建立而要建在MYISAM引擎上
mysql> alter table t3 add fulltext index full_idx_t3 on t3(col2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on t3(col2)' at line 1
mysql> alter table t3 add fulltext index full_idx_t3(col2);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql> alter table t3 add spatial index spa_idx (col4);
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
mysql> alter table t3 add hash index idx_hash(col1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index idx_hash(col1)' at line 1
mysql>
--测试新表T4并修改其存储引擎为MEMORY后创建HASH索引
mysql> show create table t4 \G;
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`col1` varchar(8) DEFAULT 'xxxx',
`col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table t4 enginer=memory;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'enginer=memory' at line 1
mysql> alter table t4 engine=memory;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table t4 \G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`col1` varchar(8) DEFAULT 'xxxx',
`col2` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
--注意这里是大小写引起错误
mysql> ALTER TABLE T4 ADD INDEX HASH_IDX(COL2) USING HASH;
ERROR 1146 (42S02): Table 'zbk_db.T4' doesn't exist
mysql> DESC T4;
ERROR 1146 (42S02): Table 'zbk_db.T4' doesn't exist
mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | varchar(8) | YES | | xxxx | |
| col2 | int(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--alter方式添加HASH索引
mysql> alter table t4 add index hash_idx(col2) using hash;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show index from t4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t4 | 1 | hash_idx | 1 | col2 | NULL | 0 | NULL | NULL | YES | HASH | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
