MYSQL--索引

smithForest
181 阅读6分钟

索引

索引分类

  • 按数据结构划分主要有：B+树索引、HASH索引、FULL-TEXT全文索引
  • 按物理存储划分主要有：聚簇索引（主键和完整数据），二级索引（指定列和主键）
  • 按字段特性划分主要有：主键索引，唯一索引，普通索引，前缀索引（长列只包含前缀关键部分）
  • 按字段个数划分有：单列索引，联合索引（多个指定字段+主键）

INNODB的索引是什么？

B+树和FULL-TEXT索引

MYSQL为什么使用B+树？

  • 相比较于平衡二叉树和红黑树，B+树是多叉树，它需要规定关键字叉树最少n/2，所以B+树更短，磁盘I/O次数也会更少，调整树形结构也会效率更高
  • 相比较于跳表，跳表在极端情况下会退化成链表，跳表需要更多的内存，MYSQL优化器需要计算成本的，【REDIS使用跳表是因为红黑树和B+树需要调整树形结构，跳表实现简单，而且REDIS存储的是短平快的热点数据，多级概率分层，一般不会出现跳表退化成链表的情况】
  • 相比较于B树，首先B树非叶子节点也存放了数据，可能会导致内存难以放下所有的非叶子节点，从而失去索引的效果；并且B+树叶子节点之间构成双向链表，支持范围查询，方便加锁等操作。
  • 想比较于哈希表，哈希表虽然支持随机查询，但是哈希表不支持排序和范围查询，而且哈希表需要处理哈希冲突。

聚簇索引和二级索引有什么区别？

  • 聚簇索引包含主键值和完整的数据记录，二级索引只包含索引值和主键值
  • 如果用到二级索引，但查询的是非索引列和主键，就需要在二级索引中找到对应的主键值，再用主键值去主键索引里回表查询，需要扫描两次B+树

普通索引和唯一索引有什么区别？

  • 普通索引支持重复字段，唯一索引不支持重复字段
  • 普通所索引更新效率很更高一点，因为MYSQL更新是先更新到CHANGE_BUFFER中，然后定期刷脏，如果是普通索引，不要进行重复检查，直接更新CHAGE_BUFFER就行了，对于唯一索引，如果缓存中没有，就需要到磁盘中去读取出来，判断唯一性后，再更新，所以普通索引更新效率会更高。但是重复性很高的数据失去建立索引的价值

怎么设置主键，如果没有设置主键，主键索引如何形成？

  • 给字段加上PRIMARY KEY标识索引
  • 如果没有设置索引，会默认使用第一个非NULL的列作为主键索引项
  • 如果既没有主键，也没有非空值，会使用默认的ROW_ID列来作为主键索引项

为什么要建索引?

  • 缩短查找时间，没有建索引就需要全表扫描，时间复杂度O（N），如果建立了索引，时间复杂度O（LOG2N）
  • 索引在磁盘上按顺序存放，所以可以让随机I/O变成顺序I/O，提高空间局部性
  • 索引可以帮助快速定位到数据，并且自带增序，所以可以减少外部排序和内部临时表的使用

INSERT操作对B+树的改变是怎样的？

  • 如果主键值是递增的，那么每次插入就只需要插入到叶子节点的最右边，执行插入操作，效率是很高的
  • 如果主键值是非递增的，那么插入就可能在中间进行插入，那么就需要优化B+树的树形结构，可能会发生页分裂，进行数据页的复制，效率就会很低，并且造成内存碎片。

一般选择什么字段来建立索引？

  • 频繁使用WHERE、GROUP BY、ORDER BY的字段
  • 区分度不高的字段不使用索引

索引越多越好吗？

  • 索引不是越多越好，因为每见一个索引就会维护一个B+树，会影响写性能，所以读少写多的场景不适合建索引
  • 区分度不高的字段或者重复高的字段不适合索引

怎么优化索引？

  • 覆盖索引优化，对于WHERE、GROUP BY、 ORDER BY常用的不频繁的字段建立联合索引进行覆盖优化，联合索引要符合最左匹配原则。

  • 主键索引最好是自增的，提高INSERT效率

  • 前缀索引可以减少索引项的大小

  • 避免索引失效

    • 左模糊/右模糊：LIKE %XXX或者LIKE%XXX%
    • 在WHERE 、ORDER BY、GROUP BY 索引字段进行计算、函数、隐式转换等操作的时候 会失效
    • 联合索引要符合最左匹配原则
    • WHERE字段，如果OR之前的条件是索引，OR之后的条件不是索引，索引就会失效

建立了索引,一定会走索引吗?

  • 如果走索引的成本高,优化器就会决定不走索引

  • 索引失效

    • 联合索引不符合最左匹配
    • WHERE字段OR之前的是索引,OR之后的不是索引
    • 索引字段进行了隐式转换(比如字符串转化成了时间或者数字),计算,函数等操作

WHERE TIME=20191108 ,TIME是VARCHAR,会走索引吗?

不会走索引,实际上用了CAST转换函数进行了隐式转换

MYSQL新版本解决了什么问题?

  • 索引跳跃扫描,联合索引可以不遵循最左匹配原则
  • 函数索引,新调用函数可以走索引了

什么是最左匹配原则?

  • 比如有一个(a,b,c)联合索引,索引排序是先按A排序,再按B排序,再按C排序,左右符合这个顺序才能走索引覆盖

    • 最左匹配原则下,如果b>1这种范围查询或者模糊查询后面的字段也无法走索引[只有等值查询后面的字段才能走索引]

  • 索引下推,例子:通常情况下，数据库系统会首先使用 user_id 索引来定位符合条件的记录，然后再对每条记录应用 name = 'John' 的条件进行过滤。而索引下推则可以将 name = 'John' 的条件推送到索引层级，在索引层级就可以进行过滤，而不需要检索所有符合 user_id = 123 的记录再进行过滤。

建立联合索引有什么原则?

将区分度大的字段放在前面,因为如果第一个字段区分度不大的话,很可能就回表了

WHERE A>1 AND B=2 AND C<3怎么建立索引?

建立BAC或者BCA索引,字啊优化器优化下保证两个字段走索引,一个字段进行索引下推

SELECT ID,NAME FROM TABLE WHERE AGE>10 AND NAME LIKE 'XXX%',有(name和age的联合索引)

name走索引,age进行索引下推,B+树是先根据name排序然后再根据age排序的,以为name走了右模糊索引查询,所以age不是有序的了.age只能进行索引下推,也不需要回表.

avatar
文章
阅读
粉丝