A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© 大山哥哥 黑马粉丝团   /  2018-10-1 12:04  /  546 人查看  /  0 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

        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命令去增加索引

alter索引.jpg

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索引。
btree.jpg
查询方式:
        主键索引区: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键下以链表形式存储。当然这只是简略模拟图。


hash.jpg

创建索引的原则
什么时候使用索引呢?
     a.在经常需要搜索的列上,可以加快搜索的速度;
     b.在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
     c.在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
    d.在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
    e.在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
    f.在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。。
什么时候不建议使用索引呢?
a. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
      b. 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
      c. 对于那些定义为text, image和bit数据类型列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。
      d. 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。

在删除大批量数据的时候注意点


由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率,所以应该通过先删除索引,再删数据最后重建索引,这样就能提高效率




0 个回复

您需要登录后才可以回帖 登录 | 加入黑马