索引 index

#### 创建索引
index：普通索引
索引优点：加快数据的查询速度，通过创建唯一索引保证数据库表中每一行数据的唯一性	 
索引缺点：占用物理空间，当表中的数据进行增删改查时索引也要动态调整降低数据的维护速度
排队信息：在/var/lib/mysql/库名/*.frm  *.ibd
```asp
create table db2.t2(name char(10), #创建字段指定索引排队
    -> age int,
    -> class char(7),
    -> email char(30),
    -> index(name),index(age)
);
```
```asp
desc t2;							#查看索引信息key键MUL（简单查看）
```
```asp
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | MUL | NULL    |       |
| age   | int(11)  | YES  | MUL | NULL    |       |
| class | char(7)  | YES  |     | NULL    |       |
| email | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
```
```asp
show index from db2.t2;			#查看索引信息详细查（横向）
```
```asp
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | age      |            1 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
```
```asp
show index from db2.t2 \G;					#查看（\G可以竖列显示）（纵向）
```
```asp
**************** 1. row ***************
        Table: t2
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
******************* 2. row ************
        Table: t2
   Non_unique: 1
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
```
#### 添加索引
```asp
desc t111;								#查看无索引
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
+-------+---------------------+------+-----+---------+-------+
```
```asp
create index xxx on db2.t111(name);   	#name字段设置成索引（xxx可以是任何其他名字通常和字段名字一致即可）
```
```asp
desc t111;								#查看
```
```asp
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(15)         | NO   | MUL |         |       |
| sex   | enum('man','woman') | YES  |     | man     |       |
+-------+---------------------+------+-----+---------+-------+
```
#### 删除索引
```asp
drop index age on db2.t2;	#删除索引
```
```asp
desc t2;					 #查看age的key标记小时了
```
```asp
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | MUL | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
| class | char(7)  | YES  |     | NULL    |       |
| email | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
```
```asp
show  index from db2.t2;	 #详细查看（横向）
```
```asp
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
```
```asp
show  index from db2.t2\G;   #详细查看（纵向）
```
```asp
**************** 1. row ***************
        Table: t2
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE	
      Comment: 
Index_comment: 
```