1.什么是索引
1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2.让获取的数据更有目的性，从而提高数据库检索数据的性能。
2.索引的种类
1.BTREE: B+树索引（Btree，B+tree，B*tree）
2.HASH：HASH索引（memery存储引擎支持）
3.FULLTEXT：全文索引（myisam存储引擎支持）
4.RTREE：R树索引
3.索引根据算法分类
索引是建立在数据库字段上面的
当where条件后面接的内容有索引的时候，会提高速度
1.主键索引（聚集索引）
# 创建表的时候创建主键索引
mysql> create table test(id int not null auto_increment primary key comment ‘学号‘);
Query OK, 0 rows affected (0.04 sec)
mysql> create table test1(id int not null auto_increment,primary key(id));
Query OK, 0 rows affected (0.04 sec)
# 查看索引命令
mysql> show index from test;
# 已经有表时添加主键索引
mysql> alter table student add primary key pri_id(id);
2.唯一键索引
# 创建表的时候创建唯一键索引
mysql> create table test2(id int not null auto_increment unique key comment ‘学号‘);
Query OK, 0 rows affected (0.04 sec)
# 已经有表时添加唯一键索引
mysql> alter table student add unique key uni_key(name);
# 注意：创建唯一建索引或主键索引的列不能有重复数据
判断一列能否做唯一建索引
1.查询数据总量
mysql> select count(name) from city;
2.去重查看该列数据总量
mysql> select count(distinct(name)) from city;
# 以上两个值相等则可以设置唯一建索引
例：
# 1.查看列的总数据量
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
| 239 |
+-------------+
1 row in set (0.00 sec)
# 2.查看去重后数据量
mysql> select count(distinct(name)) from country;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 239 |
+-----------------------+
1 row in set (0.00 sec)
# 3.创建唯一建索引
mysql> alter table country add unique key uni_key(name);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.普通索引（辅助索引）
mysql> alter table city add index inx_name(name);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index index_District on city(District);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.全文索引
mysql> create table txt(id int,bookname varchar(12),wenzhang text,fulltext(wenzhang));
Query OK, 0 rows affected (0.20 sec)
mysql> select * from txt where match(wenzhang) against(‘查询的内容‘);
#实例
mysql> create table text(id int,bookname varchar(12) charset utf8,wenzhang text charset utf8,fulltext(wenzhang));
Query OK, 0 rows affected (0.21 sec)
mysql> insert into text values(1,‘红楼梦‘,‘上回书说到张飞长坂坡三打白骨精救出宋江‘);
Query OK, 1 row affected (0.01 sec)
mysql> select * from text;
+------+-----------+-----------------------------------------------------------+
| id | bookname | wenzhang |
+------+-----------+-----------------------------------------------------------+
| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |
+------+-----------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from text where match(wenzhang) against(‘上回书说到张飞长坂坡三打白骨精救出宋江‘);
+------+-----------+-----------------------------------------------------------+
| id | bookname | wenzhang |
+------+-----------+-----------------------------------------------------------+
| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |
+------+-----------+-----------------------------------------------------------+
5.查看索引
方式一：
mysql> show index from city;
#方式二：
mysql> desc city;
+-----+
| Key |
+-----+
| PRI | #主键索引
| MUL | #普通索引
| UNI | #唯一键索引
| MUL |
+-----+
6.删除索引
mysql> alter table city drop index index_District;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除普通索引
mysql> alter table city drop priary key;
# 删除主键索引