1.Mysql索引方面常见问题
1.索引的优点和缺点
优点:
- 可以大大加快数据的检索速度
缺点:
- 时间方面:创建索引和维护索引需要消耗时间
- 空间方面:索引需要占物理空间
2.哪些情况下需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 多表关联查询中的关联字段
- 排序的字段
- 频繁查找的字段,需要覆盖索引
- 查询中统计或者分组字段
3.哪些情况下不需要创建索引
- 表记录太少
- 经常进行增删改查操作的字段
- where条件里使用频率不高的字段
4.创建索引的几种方式
- 创建表时添加索引
- 使用alter table命令
- 使用create index命令
5. mysql有几种索引类型
- fulltext 全文索引
- hash 哈希索引
- btree b树索引
- rtree r树索引
6.为什么索引结构默认使用b+tree,而不是btree,二叉树,红黑树?
- btree:因为btree不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少,指针少的情况下要保存大量的数据,只能增加数的高度,导致IO操作变多,查询性能贬低
- 二叉树:树的高度不均匀,不能自平衡,查找效率和树的高度有关,IO代价高
- 红黑树:树的高度随着数据量的增加而增加,IO代价高
- 总的来说,影响mysql查找性能的主要还是磁盘IO的次数,大部分是刺头移动到指定磁道的时间花费,树越高,IO代价越高,查询时间越长
7.hash索引和btree索引的优缺点
优点
- 单条数据查询时,hash索引查询时间复杂度为O(1),b数索引时间复杂度为O(logN)。
缺点
- hash索引只适合等值查询,但是无法进行范围查询
- 哈希索引没有办法利用索引完成排序
- 哈希索引不支持联合索引的最左匹配规则
- 如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞
8.索引种类
- 普通索引:提高查询速度
- 唯一索引:提高查询速度 + 列值唯一 + 可以为null
- 主键索引:提高查询速度 + 列值唯一 + 不可以为null
- 组合索引:多个字段组合的索引,效率大于索引合并
- 全文索引:对文本内容分词搜索
9.为什么建议使用主键自增索引?
由于b+树是有序的,如果不是自增,那么需要将下面的叶子节点进行移动,腾出位置来插入数据,这样就会比较耗时间,如果刚好数据满了,还需要进行页分裂操作。
但是如果我们的主键是自增的,每次插入的ID都会比前面的大,那么我们每次只需要在后面插入就行,不需要移动位置,分裂等操作,这样就可以提高性能
10.b+tree的叶子节点可以存哪些东西?两者的区别是什么?
innodb的b+tree可能存储的是整行数据,也有可能是主键的值。
如果索引的叶子节点存储了整行数据的主键索引,也被称为聚簇索引。
如果索引的叶子节点存储了主键的值的非主键索引,也被称之为非聚簇索引。
11.那么聚簇索引(主键索引)和非聚簇索引(非主键索引),在查询数据的时候有区别吗?为什么?
聚簇索引查询更快,因为聚簇索引的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值之后,还需再通过主键的值进行一次查询。
12.非主键索引是否一定需要回表查询?
如果是覆盖索引就不需要回表。覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取,也可以称之为实现了索引覆盖。
13.何时使用聚簇索引和非聚簇索引?
动作 | 使用聚簇索引 | 使用非聚簇索引 |
---|---|---|
列经常被分组排序 | Y | Y |
返回某范围内的数据 | Y | N |
一个或极少不同值 | N | N |
小数目的不同值 | Y | N |
大数目的不同值 | N | Y |
频繁更新的列 | N | Y |
外键列 | Y | Y |
主键列 | Y | Y |
14.创建联合索引的时候,联合索引多个字段顺序是如何选择的呢?为什么?
按照识别度由高到低一次排序,为了最左前缀匹配。
当创建一个key1、key2、key3的联合索引时,相当于创建了key1、key1-key2、key1-key2-key3三个索引。
15.mysql5.6对索引做什么优化?
index Condition Pushdown 索引下推
16.如何知道有没有走索引查询?
可以通过explain查询sql语句的执行计划,通过执行计划来分析索引使用情况。
17.explain执行计划中你关注哪些字段?
- type:表的链接类型(const > eq_ref > ref > range > index > all)
- key: 表实际使用的索引
- key_len:索引字段的字节数,字符长度*3 ,变长类型+2,可null+1
- rows:扫描出来的行数(估算的行数)
- extra:执行情况和描述说明
18.什么情况下明明创建了索引,但是执行的时候并没有通过索引?
- like通配符在前面
- or左右两边有非索引列
- 使用了not、not in 、not like等负向查询
- 索引字段可以为null,查询使用了is null 或者is not null
- 隐式类型转换
- 索引列使用了内置函数
- 对索引列直接运算
- 违背联合索引最左匹配原则
- mysql优化器的最终选择
2.mysql四种索引类型的区别以及适用场景
1.fulltext
即全文索引,目前只有MyISAM引擎支持。其可以在ceate table、alter table、create index使用,不过目前只有char、varchar、text列上可以创建全文索引。值得一提的是,在数据量较大的时候,先将数据放入一个没有全局索引的表中,然后再用create index 创建fulltext索引,要比先为一张表建立fulltext索引然后再将数据写入的速度快很多。
2.hash
hash是一种key->value形式的键值对,如数学中的函数映射,允许多个key对应相同的value,但不允许一个key对应多个value。正式由于这个特性,hash很适合做索引,为某一列或几列建立hash索引,就会利用这一列或几列的值通过一定的算法计算出一个hash值,对应一行或几行的数据。在java语言中,每个类都有自己的hashcode方法,没有显示定义的都继承自object类。该方法使得每一个对象都是唯一的,在进行对象间equals比较,和序列化传输中起到了很重要的作用。hash的生成方法有很多种,足可以保证hash码的唯一性。
由于hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。那为什么还需要其他的树形索引呢?
hash索引有以下问题
- hash索引仅仅能满足 = ,in , <=>等精确查询,不能使用范围查询。
由于hash索引比较的是进行hash运算之后的hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的hash算法处理之后的hash值的大小关系,并不能保证和hash运算前完全一样。 - hash索引无法被用来避免数据的排序操作。
由于hash索引中存放的是经过hash计算之后的hash值,而且hash值的大小关系并不一定和hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算 - hash索引不能利用部分索引键查询
对于组合索引,hash索引在计算hash值的时候是组合索引键合并后再一起计算hash值,而不是单独计算hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,hash所以也无法被利用。 - hash索引在任何时候都不能避免表扫描
hash索引是将索引键通过hash运算之后,将hash运算结果的hash值和所对应的行指针信息存放于一个hash表中,由于不同索引键存在相同的hash值,所以即使取满足某个hash键值的数据的记录条数,也无法从hash索引中直接完成查询。还是要通过访问表中的实际数据进行相应的比较,并得到响应的结果。 - hash索引遇到大量hash值相等的情况后性能并不一定就会比btree索引高。
对于选择性比较低的索引键,如果创建hash索引,那么将会存在大量记录指针信息存于同一个hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能地下。
3.btree
btree索引就是一种将索引值按一定的算法,存入一个树形的数据结构中。每次查询都是从数的入口root开始,依次遍历node,获取叶节点。
btree在myisam里的形式和innodb稍有不同。
在innodb里,有两种形态:一是primary key形态,其叶节点里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其叶节点和普通的btree差不多,只是还存放了指向主键的信息。
在myisam里,主键和其他的并没有太大的区别。不过叶节点里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息
4.rtree
rtree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有mysiam、bdb、innodb、ndb、Archive几种。
相对于btree、rtree的优势在于范围查找。
5.各种索引的使用情况
- 对于btree这种mysql默认的索引类型,具有普遍的适用性
- 由于fulltest对中文支持不是很好,在没有插件的情况下,最好不要使用。其实,一些小的博客应用,只需要在数据采集时,为其建立关键字列表,通过关键字索引,也是一个不错的方法。
- 对于一些搜索引擎级别的应用来说,fulltext同样不是一个好的处理方法,mysql的全文索引建立的文件还是比较大的,而且效率不是很高,即便是使用了中文分词插件,对中文分词支持也只是一般。真要碰到这种问题,Apache的Lucene或许是好的选择。
- 正式因为hash表在处理较小数据量时具有无可比拟的优势,所以hash索引很适合做缓存。
3.索引下推(Index Condition Pushdown)
不使用索引条件下推优化时的查询过程:
获取下一行,首先读取索引信息,然后根据索引将整行数据读取出来。然后通过where条件判断当前数据是否符合条件,符合返回数据
使用索引下推优化时的查询过程
获取下一行的索引信息。 检查索引中存储的列信息是否符合索引条件,如果符合将整行数据读取出来,如果不符合跳过读取下一行。 用剩余的判断条件,判断此行数据是否符合要求,符合返回数据。
使用场景
- 非聚簇索引,聚簇索引已经有整行数据,不需要回表
- 减少回表次数,特别对于like通配符来说,减少效果明显
如何判断
使用explain进行分析时,如果Extra显示Using index condition,说明使用了索引下推
4.图解为什么建议使用自增长主键作为索引
结合b+tree的特点,自增主键时连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少的一部分。并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。
插入连续的数据:
插入不连续的数据