Mysql 索引

索引概念

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的

索引优优缺点

优点

• 可以大大加快数据的检索速度
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点

• 创建索引和维护索引要耗费时间，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增删改的执行效率；
• 索引需要占物理空间

索引基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表

原理：把无序的数据变成有序的进行查询

• 把创建了索引的列的内容进行排序

• 对排序结果生成倒排表

• 在倒排表内容上拼上数据地址链

• 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

创建索引的原则

根据索引的优缺点，并不是所有场景都适合使用索引
还有一些使用规则需要注意

• 1） 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
• 2）较频繁作为查询条件的字段才去创建索引
• 3）更新频繁字段不适合创建索引
• 4）若是不能有效区分数据的列不适合做索引列，(如性别，男女未知，最多也就三种，区分度实在太低)
• 5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
• 6）表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
• 7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
• 8）对于定义为text、image和bit的数据类型的列不要建立索引
• 9）记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能

索引的分类及创建

索引基本分为，普通索引，唯一索引，主键索引，全文索引，而普通和唯一索引还可以创建组合索引

普通索引

最基本的索引类型，没有唯一性之类的限制

• 直接创建索引

create index 索引名 on 表名 (列名(length));
(列名(length))：
length是可选项，下同。如果忽略length的值，则使用整个列的值作为索引。如果指定，使用列的前length个字符来创建索引，这样有利于减小索引文件的大小
索引名建议以“index"结尾

查看索引

• 修改表方式创建索引

alter table 表名 add index 索引名 (列名);

• 创建表时指定索引

create table 表名(字段 类型，字段 类型，…，index 索引名 (列名));

删除索引

• 直接删除索引

drop index 索引名 on 表名；

• 修改表方式删除

alter table 表名 drop index 索引名；

• 删除主键索引

alter table 表名 drop primary key；

唯一索引

创建方式与普通索引类似 但区别是唯一索引列的每个值都唯一
唯一索引允许有空值(null) 但如果是用组合索引创建 则列值的组合必须唯一
添加唯一键将自动创建唯一索引

• 直接创建唯一索引

create unique index 索引名 on 表名 (列名);

• 修改表方式创建唯一索引

alter table 表名 add unqiue 索引名 (列名);

• 创建表时直接指定唯一索引

create table 表名(字段 类型，字段 类型，…，unique 索引名 (列名));

添加唯一键将自动创建唯一索引

主键索引

一种特殊的唯一索引，必须指定为“PRIMARY KEY”
一个表只能有一个主键，不允许有空值
添加主键将自动创建主键索引

• 创建表时指定主键将自动创建主键索引

create table 表名(字段 类型，字段 类型，…，primary key (列名));

• 添加主键将自动创建主键索引

alter table 表名 add primary key (列名);

组合索引

create table 表名(字段 类型，字段 类型，…，index 索引名 (列名，列名));

全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息
全文索引可以在 char、varchar 或者 text 类型的列上创建
每个表只允许有一个全文索引

• 直接创建索引

create fulltext index 索引名 on 表名 (列名);

• 修改表方式创建

alter table 表名 add fulltext 索引名 (列名);

• 创建表的时候指定索引

create table 表名 (字段 类型,fulltext 索引名 (列名));