mysql普通索引和全文_MySQL只是入门（索引）

MySQL中索引可以简单分为普通索引，唯一索引，主键索引和全文索引

1.普通索引：可加快对数据的访问，该类索引没有唯一性限制。

2.唯一索引：和普通索引类似，但该类索引中列中的值必须只能出现一次，也就是索引列值要求唯一，需要使用unique关键词

3.主键索引：专门为主键字段创建的索引，也属于唯一索引的一种，只是需要使用primarykey关键字

4.全文索引：MySQL支持全文索引，其类型为fulltext，可在varchar/text类型上创建

索引是作用在数据列上的！！！ 因此 索引可由单列组成，也可由多列组成。单列成为单列索引，多列称为组合索引。

索引的作用

1.索引可明显地加快数据检索速度

2.由于主键约束在一张表中只能有一个，那么，如果要确保表中多列唯一性，就要使用唯一索引。

3.在查询中使用order by 和 group by 子句时，索引的使用可明显减少查询时间

4.在表与表之间连接查询时，如创建了索引列，就可提高表与表之间的连接速度。

普通索引

MariaDB [zheng]> create index suoyin

-> on st (subject);

Query OK, 0 rows affected (1.64 sec)

Records: 0 Duplicates: 0 Warnings: 0

也可用alter创建索引

B [zheng]> alter table st

-> add index st (name);

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

创建唯一索引

创建唯一索引要求列中数值唯一

MariaDB [zheng]> alter table st add unique (id);

Query OK, 0 rows affected (0.27 sec)

Records: 0 Duplicates: 0 Warnings: 0

创建主键索引

iaDB [zheng]> alter table st

-> add primary key(id);

ERROR 1068 (42000): Multiple primary key defined

MariaDB [zheng]> alter table st add primary key(id(5));

ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

MariaDB [zheng]>

查看索引

MariaDB [zheng]> show index from st;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| st | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |

| st | 0 | id | 1 | id | A | 7 | NULL | NULL | | BTREE | | |

| st | 1 | suoyin | 1 | subject | A | 7 | NULL | NULL | YES | BTREE | | |

| st | 1 | st | 1 | name | A | 7 | NULL | NULL | YES | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

删除索引

Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | MUL | NULL | |

| score | decimal(4,2) | YES | | NULL | |

| subject | varchar(20) | YES | MUL | NULL | |

| teacher | varchar(20) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

5 rows in set (0.46 sec)

MariaDB [zheng]> alter table st drop key name;

ERROR 1091 (42000): Can't DROP 'name'; check that column/key exists

MariaDB [zheng]> alter table st drop key suoyin;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [zheng]> desc st;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | MUL | NULL | |

| score | decimal(4,2) | YES | | NULL | |

| subject | varchar(20) | YES | | NULL | |

| teacher | varchar(20) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec)