数据库原理之一索引

这里的数据库主要指的是MySQL数据库,以InnoDB为主。

索引的目的

通过建立索引,能够提升查找数据的速度

索引的基本原理

在这个问题上这篇文章讲的不错,它由链表=> 二叉树 => 平衡二叉树 => B树 => B+树的顺序讲过来。

(并非所有的索引都使用B+树,但B+树是最常用的一种方式)

二叉树

二叉树

红黑树

二叉查找树极大的提升了链表查找数据的效率,不过我们又发现了一个问题,就是二叉查找树的“高度”不可控,一旦树的节点插入变成线性结构,效率就大打折扣了,于是有了平衡二叉树,以红黑树为代表。

红黑树

B树

影响这棵树的查询效率的决定性因素就是树的高度,只要树的层级越少,那么树的查询效率就越高,本着这个原则我们就思考每个节点能不能多存点 数据,只要每个数节点的数据保存的越多,那么我们树的层级就会越少。B树相对平衡二叉树最大的一个改变,就是B树的每个节点可存储的关键字增多了。

B树

B+树

B+树在B树的基础上 又做了一些优化,B+树主要做了下面几点的优化。

  1. B+跟B树不同,B+树的非叶子节点不保存实际的数据,只保存索引key,所有的数据都会保存到叶子节点

  2. B+树所有的叶子节点数据构成了一个有序链表,这样在进行数据排序和询范围大小查询数据的时候更方便,数据紧密性也更高

B+树

索引的种类

  • 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。

    1. 普通索引:最基本的索引,没有任何约束。
    2. 唯一索引:与普通索引类似,但具有唯一性约束。
    3. 主键索引:特殊的唯一索引,不允许有空值
    4. 复合索引:将多个列组合在一起创建索引,可以覆盖多个列。
    5. 外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。
    6. 全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。
  • 从实现上说,分成 2 种:聚簇索引和辅助索引(也叫二级索引)

    主要说一下聚簇索引,InnoDB采用一下方式建立聚簇索引。

    1. InnoDB对主键建立聚簇索引。
    2. 如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。
    3. 如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。

    **聚簇索引:**节点页只包含了索引列,叶子页包含了行的全部数据。聚簇索引“就是表”,因此可以不需要独立的行存储。采用的是B+树的索引方式。

    **二级索引:**叶子节点保存的不是指行的物理位置的指针,而是行的主键值。

    聚簇索引

索引的使用

最左前缀

最左前缀

关于主键

InnoDB默认使用聚簇索引来组织数据,如果你用InnoDB,而且不需要特殊的聚簇索引,一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列,这会保证记录按照顺序插入,而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键,例如字符串主键就是一个不好的选择,它使得插入操作变得随机。

建立索引的时机

一般来说,在WHERE和JOIN中出现的列需要建立索引,因为MySQL的B-Tree只对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE才会使用索引。尽量不要使用NOT IN、<>、!= 操作

索引设计

  • 索引字段尽量使用数字型(简单的数据类型)

    若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了

  • 尽量不要让字段的默认值为NULL

    索引不会包含有NULL值的列,只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。

    所以我们在数据库设计时尽量不要让字段的默认值为NULL,应该指定列为NOT NULL,除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值

  • 使用组合索引代替多个列索引

    一个多列索引(组合索引)与多个列索引MySQL在解析执行上是不一样的,如果在explain中看到有索引合并(即MySQL为多个列索引合并优化),应该好好检查一下查询的表的索引是不是已经最优。

  • 注意重复/冗余的索引、不使用的索引

    对于重复/冗余、不使用的索引:可以直接删除这些索引。因为这些索引需要占用物理空间,并且也会影响更新表的性能。

  • like语句不要以通配符开头

    对于LIKE:在以通配符%和_开头作查询时,MySQL不会使用索引

# MySQL 

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×