索引
一、索引
索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。
索引有哪些优缺点?
索引的优点
可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引的缺点
时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
空间方面:索引需要占物理空间。
主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
唯一索引:
数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引
普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引
全文索引: 是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
三、 索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。
索引的原理很简单,就是把无序的数据变成有序的查询
1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
四、索引的使用
创建索引的三种方式 第一种方式:在执行CREATE TABLE时创建索引
第二种方式:使用ALTER TABLE命令去增加索引
第三种方式:使用CREATE INDEX命令创建
创建索引的原则
索引虽好,但也不是无限制的使用,最好符合一下几个原则
1) 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立 (a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2)较频繁作为查询条件的字段才去创建索引
3)更新频繁字段不适合创建索引
4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)
5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
6)定义有外键的数据列一定要建立索引。
7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
8)对于定义为text、image和bit的数据类型的列不要建立索引。