MySQL索引简析

什么是索引?

索引就像我们一本书的目录,对于我们快速查找数据和定位数据位置,有着十分重要的作用。 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 同时索引又是一种很耗资源的数据。如果给每个字段都创建索引,相对的就变得冗余,反而会降低数据库的性能。

MySQL数据库中B+树索引分为聚集索引(clustered index)和非聚集索引(secondary index)。这两种索引的共同点是内部都是B+树,高度都是平衡的,叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。

    聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

关于索引数据结构B+树方面的理解可以参考 这篇文章另一篇大佬的文章

    Innodb存储引擎表是索引组织表,即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引(一个主键)。聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。

    聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引,特别是随机字符串,会使得系统进行大量的移动操作),且一个表只能有一个聚簇索引。因为由存储引擎实现索引,所以,并不是所有的存储引擎都支持聚簇索引。目前,只有solidDB和InnoDB支持。

  • 主键排序查找:由于B+树是双项链表,我们可以迅速找到最后一个页,并取出需要的记录;
  • 主键范围查找:如果要通过主键查找某一范围内的数据,通过叶节点的上层中间节点就能得到页的范围,之后直接读取数据页即可

索引的优缺点:

索引优点:

  • 可以加快数据的检索速度,提高查询速度。
  • 所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段建立索引。
  • 全文检索字段进行搜索优化。

索引缺点:

  •  创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
  • 当对表中的数据进行增加、删除、修改的时候,索引也需要动态维护,降低了数据的维护速度。
  • 索引也需要占用物理存储空间(数据库目录:/var/lib/mysql)。
  • 我们知道数据表中的数据也会有最大上线设置的,如果有大量的索引,索引文件可能会比数据文件更快达到上线值。

使用原则:

  • 索引不是越多越好,并不是每个字段都设置索引就好,而是需要自己合理的使用。
  • 对经常更新的表就避免对其进行过多的索引,对经常用于查询的字段应该创建索引,不要对经常变动的数据加索引。
  • 数据量小的表最好不要使用索引,因为数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
  • 在一同值少的列上(字段上)不要建立索引,比如在学生表的”性别”字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可以建立索引。

什么时候添加索引:

  • 在 where、order by 子句中经常使用的字段。
  • 字段的值是多个(例如性别字段则不适合)。
  • 字段内容不是经常变化的,经常变化的字段,添加索引反而降低性能。
  • 不宜过多添加索引,每添加一条索引都会占用磁盘空间。
  • 在大批量添加插入数据的时候,如果插入的时候添加索引,会极其影响插入性能,因为每次修改都会去重新构建索引,所以一般我们在插入数据的时候,删除索引,在数据插入完毕之后再重构索引。

索引的分类

  • 普通索引:加速查找
  • 唯一索引:加速查找+约束 (唯一)
  • 主键索引:加速查找+约束(不为空且唯一)
  • 外键索引:  
  • 组合索引:primary key(id,name):联合主键索引,unique(id,name):联合唯一索引,index(id,name):联合普通索引
  • 全文索引:用于搜索很长一篇文章的时候,效果最好。(full text)
  • 空间索引:了解就好,几乎不用。(spatial)

普通索引:

使用规则
1.一个表中可以有多个index字段
2.字段的值可以有重复,也可以为null值。字段值无约束
3.经常把做查询条件的字段设置为index字段
4.index字段的key标志为:mul

创建普通索引(有三种方法)
方法1:创建表时创建索引index
create table student(
    id int,
    name varchar(25),
    score float(5,2),
    index(name),              # 创建name索引
    index(score)              # 创建score索引
);
方法2:创建表后创建索引index:索引名一般和字段名一样,只要自己能认出就行,可以随便起名。
mysql> create index 索引名 on 表名(字段名)
mysql> create index name on student(name); 
方法3:创建表后创建索引index
mysql> alter table 表名 add index 索引名(字段名);
mysql> alter table student add index(name);
mysql> alter table student add index name6(name);

查看普通索引:key一列就是索引的列,我们会发现在name和score有值MUL。
mysql> desc 表名;  
mysql> desc student;                          # Key标志为:MUL
mysql> show index from 表名;                   # Key_name值为索引名
mysql> show index from studentG;             # 如果字段名过多,就添加一个G。

删除普通索引:删除普通索引只能一个一个删除
mysql> drop index 索引名 on 表名;
mysql> drop index name6 on student;

唯一索引(unique)

使用规则
1.一个表中可以有多个unique字段
2.unique字段的值不允许重复,可以为空值null
3.unique的key标志是UNI

创建唯一索引(基本等同index创建)
方法1:创建表时创建索引
create table student(
    id int,
    name varchar(25),
    score float(5,2), 
    unique(name),
    unique(score)
);   
方法2:创建表后创建索引:索引名一般和字段名一样,只要自己能认出就行,可以随便起名。
mysql> create unique index 索引名 on 表名(字段名);
方法3:创建表后创建索引
mysql> alter table student add unique(name);
mysql> alter table student add unique name6(name);
mysql> alter table student add unique index(name);
mysql> alter table student add unique index name6(name);

查看唯一索引
mysql> desc 表名; 
mysql> show index from 表名;

删除唯一索引
mysql> drop index 索引名 on 表名;

主键索引(primary key)

外键索引(foreign key)

全文索引(fulltext)

使用须知
1.在MySQL5.6以下,只有MyISAM引擎表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
2.在MySQL5.6以下,目前只支持英文字符的全文搜索,不支持中文全文索引,原因很简单:与英文不同,中文的文字是连着一起写的,中间没有MySQL能找到分词的地方。
3.相应字段建立FULLTEXT索引,只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。
4.预设搜寻是不分大小写,若要分大小写,columne 的 character set要从utf8改成utf8_bin。
5.MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。
6.全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行。

创建全文索引
方法1:创建表时创建全文索引
create table article (      
    id int auto_increment not null primary key,   
    title varchar(200),
    body text,
    fulltext(title, body)
) engine=myisam;
方法2:创建表后创建全文索引
mysql> alter table 表名称 add fulltext index 索引名称  (字段名1,字段名2)
mysql> alter table article add fulltext index(body);
mysql> alter table article add fulltext index(title, body);
方法3:创建表后创建全文索引
mysql> create fulltext index 索引名称 on 表名称(字段名1,字段名2)

使用全文索引
mysql> select * from 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1*')
mysql> select * from 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1' IN BOOLEAN MODE)
mysql> select * from article where match(title) against('中华日报') ;
mysql> select * from article where match(title,body) against('杜月笙');

删除全文索引
mysql> drop index 索引名称 on 表名称;

重建索引:在数据库运行了较长时间后,索引都有损坏的可能
mysql> repair table 表名称 quick;

全文索引变量值查询
mysql> show variables like 'ft%';

发表评论

邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据