hello.最近不少学生在面试的过程中经常性的被问到mysql中的索引,而在咱们黑马的课程体系中,对我们mysql的索引这块并没有涉及到,所以呢,在这给大家整理了一个帖子来给大家好好的聊下我们mysql的索引相关知识点~
什么是索引,索引的优缺点?
1)什么是索引?
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。说白了就是咱们小学的时候查中华字典的时候,在找具体汉字的时候一般都是通过拼音或者部首来查找把~不然的话就只能一页页的翻咯.所以,通过索引,就能让我们提高检索数据的效率
2)索引的优缺点:
索引最大的好处就是使查询变的更加高效,且通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。但是,索引也是有缺点的:索引需要额外的维护成本;因为索引文件是单独存在的文件,对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。
索引怎么创建呢?
1) 使用ALTER TABLE语句创建索引。
语法如下:
alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;
其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。
2) 使用ALTER TABLE命令去增加索引
3) 使用CREATE INDEX命令创建
注意:能够增加普通索引和UNIQUE索引两种。其格式如下:
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ; 说明:table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
索引的基本原理
索引的原理很简单,就是把无序的数据变成有序的查询
1、把创建了索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址链
4、在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
索引的数据结构(btree,hash)
1)btree索引
mysql通过存储引擎取数据,InnoDB的索引类型目前只有两种:BTREE索引和HASH索引。BTREE索引是Mysql数据库中使用最频繁的索引类型,基本所有存储引擎都支持BTREE索引。
查询方式:
主键索引区:PI(关联保存的时数据的地址)按主键查询
普通索引区:si(关联的id的地址,然后再到达上面的地址)。
所以按主键查询,速度最快B+tree性质:
1.)n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。
2.)所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
3.)所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。
4.)B+ 树中,数据对象的插入和删除仅在叶节点上进行。
5.)B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点
2)哈希索引
类似于数据结构中HASH表一样,当我们在mysql中用哈希索引时,主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。当然这只是简略模拟图。
创建索引的原则
什么时候使用索引呢?
a.在经常需要搜索的列上,可以加快搜索的速度; b.在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; c.在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度; d.在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; e.在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; f.在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。。 什么时候不建议使用索引呢? a. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
b. 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
c. 对于那些定义为text, image和bit数据类型列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。
d. 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。
在删除大批量数据的时候注意点
由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率,所以应该通过先删除索引,再删数据最后重建索引,这样就能提高效率
|