MySQL数据库索引

一、索引的概念

目的：数据库 利用各种快速定位技术，能够大大加快查询速度

1.索引的概念

• 索引是一个排序的列表，在这个列表中存储着缩影的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）
• 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引找到该行数据对应的物理地址，然后访问相应的数据，因此能加快数据库的查询速度。
• 索引就好比时一本书的目录，可以根据目录中的页码快速找到所需的内容
• 索引是表中一列或者若干列排序的方法
• 建立索引的目的就是加快对表中记录的查找或排序
• 需要额外的磁盘空间

2.索引优点和缺点

2.1索引的优点

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要原因
• 当表很大或查询涉及到多个表时，使用索引可以成千上万的提高查询速度
• 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
• 通过创建唯一键性索引保证数据表数据的唯一性
• 可以加快表与表之间的连接
• 在使用分组和排序时，可大大减少分组和排序时间

总结：提高查询速度，降低IO成本、加快表与表的连接减少分组排序时间。

2.2索引的缺点

• 索引需要占用额外的磁盘空间

对于myisam引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址，而innode引擎的表数据文件本身就是索引文件

• 在插入和修改数据时需要花费更多的时间，因为索引也要随之变动
• 数据库在执行select这类的查询语句时，会先遍历索引表，如果无效、无用的索引，反而会在索引表数据的查询时间消耗。

3.创建索引的原则依据

索引虽然可以提升数据库查询的速度，但是并不是任何情况下都适合创建索引，因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进性索引查询，然后定位到具体的数据行，如果索引使用不当，反而还会增加数据库的负担

• 表的主键、外键必须有索引，因为主键具有唯一性，外键关联的是子表的主键，查询时，可以快速定位
• 记录数超过300行的表应该有索引，如果没有索引，需要把表遍历一边，会严重影响数据库的性能
• 经常与其它表进行连接的表，在连接字段上应该建立索引

以上三条总结：①具有唯一性，②被查询的频次较高的字段，③表的记录超过300行以上的，适合创建字段

• 索引应该建立在选择性高的字段上。（经常被查询的字段）
• 经常出现where子句中的字段，特别是大表的字段，应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新太平频繁的字段不适合创建索引
• 索引应该建在小字段上，对于大的文本字段，甚至超长字段，不要建立索引

创建索引的目的本身就是一种mysql优化方式，根据以上创建索引的原则依据，总结一下适合创建索引的字段场景

• 小字段
• 唯一性强的字段
• 更行不频繁，但查询率很高的字段
• 表记录超过300+行
• 主键、外键、唯一键

二、索引的分类和创建

以下的操作都基于此模板表来进行操作

create database moban;
use moban
create table yun (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);

insert into yun values(1,'zhangsan','123','111111','nanjin','this is vip');
insert into yun values(2,'lisi','1234','222222','nanjin','this is novip');
insert into yun values(3,'wangwu','12345','333333','beijing','this is svip');
insert into yun values(4,'zhaoliu','123456','444444','sahnghai','this is ssvip');
insert into yun values(5,'qianqi','1234567','555555','suzhou','this is novip');

1.普通索引

1.1直接创建索引

格式: create index 索引名 on 表名 （列名[（length）]）;

• 索引名建议以“ _index”结尾
• 列名： length是可选项，如果忽略length的值，则使用猪呢个股列的值作为索引，如果指定使用列前的length个字符来创建索引，这样有利于减小索引文件的大小。

create index phone_index on yun(phone);
select phone from yun;
show create table yun;

1.2修改表方式创建索引

alter table yun add index id_index(id);

select id from yun;

show create table yun;

1.3创建表的时候指定索引

create table text(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index(id));

show create table text;

2.唯一索引

与普通索引类似，但是区别是唯一索引 列的每个值都唯一

唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列表的组合必须唯一，添加唯一键将自动创建唯一索引。

2.1直接创建索引

create unique index id_index on test01(id);
#创建唯一性索引

show create table test01;
#查看是否创建索引

2.2修改表的方式创建索引

格式: alter table 表名 add unique 索引名(列名);

alter table test01 add unique cardid_index(cardid);

show create table test01;

2.3创建表的时候指定

格式: create table 表名 (字段1 数据类型，字段2 数据类型 […]，unique 索引 (列名));

create table test(id int(4),name varchar(20),unique id_index(id));
#创建新表test，直接创建唯一键id;

show create table test;
#查看索引

3.主键索引

是一种特殊的唯一索引，必须指定为"primary key"，一个表只有一个主键，不允许有空值，添加主键将自动创建主键索引。

3.1 创建表的时候指定

create table test1(id int primary key,name varchar(20));
#创建表的时候创建主键（系统在创建主键的时候会自动创建索引）
或
create table test2(id int,name varchar(20),primary key(id));
#创建表的时候，加入主键索引，指向id。

show create table test1;
show create table tset2;
#查询表的内容

3.2修改表方式创建

alter table test2 add primary key(name);

4.组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引

对查看的顺序有要求，反过来享受不到索引的快速

create table test01(id int not null,name varchar(20),caridid varchar(20),index index_test(id,name));
#创建一个表，定义字段和组合索引

show create table test01;
查看表的内容

insert into test01 values(1,'zhangsan','123123');
插入一条数据

select * from test01 where name='zhangsan' and id=1;
组合查看数据内容

5.全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息

在mysql5.6版本以前fulltext索引可用于myisam引擎，在5.6版本之后innodb引擎也支持fulltext索引，全文索引可以在char、varchar或text类型的列上创建，每个表只允许有一个全文索引。

5.1直接创建索引

select * from test01;
create fulltext index remark_inde on test01(remark);
show create table test01;

5.2修改表方式创建

alter table test01 add fulltext remark_index (name);
show create table test01;

5.3 创建表的方式创建索引

create table test01(id int(4),name char(10),genter char(2), age int(2),address char(20),fulltext index suoyin_index(address));

show create  table test01;

5.4 使用全文索引查询

select * from test01 where match(name) against('zhangsan');

或者

select * from test01 where name='lisi';

三、查看索引

show index from 表名；

show index from 表明\G；竖向显示表索引信息

show keys from 表名；

show keys from 表名\G；

各字段含义如下:

table	含义
Non_unique	如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。
Column_name	列名称。
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality	索引中唯一值数目的估计值。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment	备注。

四、删除索引

1.直接删除索引

drop index id_index on test01;
desc test01;

2.修改表方式删除索引

alter table test01 drop index id_index;