MySQL索引

本文主要是介绍MySQL索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.什么是索引

索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

2.索引的优缺点

索引的优点:
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
可以大大加快数据的检索速度，这也是创建索引的最主要的原因
可以加速表和表之间的连接

索引的缺点:
时间方面：创建索引和维护索引要耗费时间，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率
空间方面：索引需要占物理空间

3.索引的类型

主键索引: 数据列不允许重复，不允许为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);创建组合索引

全文索引： 适用于大量的文本数据检索(只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引)

4.创建索引的原则

适合创建索引条件:
1).主键自动建立唯一索引
2).频繁作为查询条件的字段应该建立索引
3).查询中与其他表关联的字段，外键关系建立索引
4).单键/组合索引的选择问题，组合索引性价比更高
5).查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率
6).查询中统计或者分组字段

不适合创建索引条件:
1).表记录少的
2).经常增删改的表或者字段
3).where条件里用不到的字段不创建索引
4).过滤性不好的不适合建索引

5.什么是最左前缀原则

1).顾名思义，就是最左优先，以最左边的为起点任何连续的索引都能匹配上.

2).最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。

3).=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

这里说下组合索引的本质:
当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引.
多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

6.索引失效的场景

1).where子句中进行的null值判断
2).对索引列使用了函数或者进行了运算符操作(+ - * / != <> %)
3).like模糊查询以%开头(like ‘%xxx’)
4).or条件查询,只有给or条件关联的每个列都加上单列索引,索引才会生效,否则都不生效
5).NOT IN 条件判断
6).如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
7).组合索引不满足最左匹配原则
8).如果查询的数据量很大,mysql底层会估算使用全表扫描是否比使用索引快,如果快的话则不走索引

7.平衡二叉树, B树和B+树的特点

1)平衡二叉树
每个节点一个元素,每个节点下最多左右两个子节点,左子树和右子树高度相差不超多1
支持范围查询,但回表查询效率低

2)B树
通过增加节点的元素个数来降低树的高度,从而减少了IO操作次数
比平衡二叉树查询效率高,也支持范围查询,但同样回表查询效率低

3)B+树
比B树多了一层叶子结点. 非叶子节点存关键字,叶子节点存key-value
支持范围查询,效率高(因为叶子结点指针顺序连接在一起,类似链表)

这里说下B+树的叶子结点存的value值: 
myisam存的是数据地址
innodb(主键索引存的是数据记录,非主键索引存的主键值)

8.B树和B+树的区别

1)B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

2)B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。

B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；

3)B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。

而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

4)B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

5)增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

6)在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

7)B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

使用B树的好处
B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。

B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。

而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

9.Hash索引和B+树区别

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

不同点：
1)hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

2)hash索引不支持使用索引进行排序，原理同上。hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。

3)hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

4)hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

10.聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储与索引分开结构，索引结构的叶子节点指向了数据行的地址或者是指向主键值，

澄清一个概念：
innodb中，在聚簇索引之外创建的索引称之为辅助索引(二级索引)，辅助索引访问数据总是需要二次查找，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

innodb引擎:
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

myisam引擎:
MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，
主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

11.聚簇索引的设定

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

12.为什么主键要自增id,聚簇索引和非聚簇索引的优缺点,前缀索引,覆盖索引

这篇关于MySQL索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！