Mysql数据库的索引

一、MYSQL的索引概述

1.1 索引的作用

• 设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率。
• 特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍。
• 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本
• 通过创建唯一性索引保证数据表数据的唯一性
• 可以加快表与表之间的连接
• 在使用分组和排序时，可大大减少分组和排序时间

1.2 索引的分类

• 普通索引
  • 这是最基本的索引类型，而且它没有唯一性之类的限制。
• 唯一性索引
  • 这种索引和前面的”普通索引”基本相同，但有一个区别: 索引列的所有值都只能出现一次，即必须唯一。
• 主键
  • 主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。

注意：一个表中只能有一个主键，但可以在多个字段，一个主键对应多个字段。

• 全文索引
  • 在MySQL中，全文索引的索引类型为FULLTEXT，全文索引可以在VARCHAR或者TEXT类型的列上创建。
• 单列索引与多列索引
  • 索引可以是单列上创建的索引，也可以是在多列上创建的索引

1.3 创建索引的条件

• 表的主键，外键必须有索引
• 数据量超过 300 行的 表 应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 经常出现在Where子句中的字段，特别是大表的字段,应该建立索引
• 索引应该建在选择性高的字段上

---

• 唯一性太差的字段不适合建立索引
• 更新太频繁地字段不适合创建索引
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

二、如何创建索引

• 根据企业需求选择了合适的索引之后，可使用 create index 创建索引
• create index 加上各个索引关键字，便可创建各个类型的索引。
• 创建索引有三种方式：
  • 1、创建表时直接定义。
  • 2、create index 索引名称 on 表名(列名1，列名2) ;
  • 3、alter table 表名 add index 索引名称 (列名)；

2.1 普通索引

create index <索引的名字> on tablename (列的列表);
例如：
1、 create table stu02(id int(4) not null,name char(10) not null,score int (4),index index_score(score));

2、create index index_score on stu01(score);

3、alter table stu01 add index index_name(name);

• 创表时直接创建

• 直接创建

修改表结构方式创建

2.2 唯一性索引

与“普通索引”基本相同
与普通索引的区别是索引列的所有值只能出现一次，即必须唯一

格式：create unique index <索引的名字> on tablename (列的列表);
例：
create unique index index_name on stu01(name);

alter table stu02 add unique index index_name(name);

• 创建表时创建
  
• 直接创建
  
• 修改表结构方式创建
  
  唯一索引：
  

2.3 主键索引

#创建主键索引有两种方法。
1、create table 表名 ( [..]... primary key (列的列表) );  #在创建表时设定的

2、alter table 表名 add primary key (列的列表);  

是一种特殊的唯一索引，指定为“primary key”

一个表只能有一个主键，不允许有空值（非空且唯一）

创建主键索引的方式

• 创建表时创建

• 修改表时创建
  

2.4 组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引
最左原则，从左往右依次执行
创建组合索引的方式

2.5 全文索引

格式：create fulltext index 索引名 on 表名(字段);
create fulltext index full_index_name on stu02(name);

• 创建表时创建全文索引

• 在已存在的表上创建索引

• 通过 sql语句alter table创建全文索引

三、查看索引

索引查看
show index from 表名;
show keys from 表名;
例：
show index from stu02;
show keys from stu02;

四、删除索引

4.1 删除索引

格式：drop index 索引名 on 表名;
或者 alter table 表名 drop index 索引名;

• drop index index_name on table_name

• alter table table_name drop index index_name

4.2 删除主键索引

格式：alter table 表名 drop primary key;
例子：
alter table stu02 drop primary key;

五、两张表之间建立内联

mysql> create table user(id int(3) not null auto_increment primary key,name varchar(64) not null,age int(3) not null,hobby int(2) not null);
mysql> insert into user values(1,'ben',14,1),(2,'david',17,3),(3,'jack',19,2);

mysql> create table hob(id int(3) not null primary key,hob_name varchar(32) not null);
mysql> insert into hob values(1,'football'),(2,'volleyball'),(3,'basketball');

mysql> select * from user inner join hob on user.hobby=hob.id;

mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;