MySql中的索引基础

索引相当于书本上的目录

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

优点：

1、所有列（字段）都可以被索引
2、大大提高查询速度

缺点：

1、索引会降低更新表的速度（更新表时MySql不仅需保存数据还要保存索引文件）
2、建立索引会占用磁盘空间
3、数据越大索引维护越耗时

使用原则：

1、对经常更新的表避免建立过多索引，对经常用于查询的字段应创建索引
2、数据量小尽量不用索引
3、在唯一性强的列上适合创建索引

索引的分类：

从逻辑角度

分为四类：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引

1）单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

普通索引：MySQL中基本索引类型没有什么限制，允许在定义索引的列中插入重复值和空值

唯一索引：索引列中的值必须是唯一的，但是允许为空值

主键索引：是一种特殊的唯一索引，不允许有空值

2）组合索引：
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合

3）全文索引：
只有在MyISAM引擎上才能使用，只能在CHAR,TEXT,VARCHAR类型字段上使用。索引类型为FULLTEXT
允许有重复值和空值，全文索引需要配合match against操作使用。

4）空间索引：
对空间数据类型的字段建立的索引，MySQL中的
空间数据类型有四种，GEOMETRY、POLYGON、LINESTRING、POINT。在创建空间索引时，使用SPATIAL关键字。
要求：引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL

从物理存储角度

分为聚集索引和非聚集索引。
1）聚集索引：
数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。像新华字典的拼音检索。

2）非聚集索引：
索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。像新华字典的偏旁检索。

从数据结构角度

1）Hash索引
2）B+tree索引

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

创建索引的sql语句：

直接创建索引：

普通索引
create index index_name 
on table_name (column(length));

唯一索引
create unique index index_name 
on table_name (column(length));

修改表结构添加索引：

普通索引
alter table table_name
add index index_name 
on (column(length));

唯一索引
alter table table_name 
add index unique index index_name
on (column(length));

组合索引
alter table table_name
add index index_name(column1,column2,...);

全文索引
alter table table_name
add fulltext index_name(column);

创建表时创建普通索引：

普通索引
create table table_name(
...
...
index index_name(column(length)));

唯一索引
create table table_name(
...
...
unique index index_name(column(length)));

主键索引
create table table_name(
...
...
primary key(column));

删除索引:

drop index index_name on table_name;

显示索引信息：

show index from table_name;\G

引用：
添加链接描述
添加链接描述
添加链接描述
添加链接描述