四、索引
索引概述、索引结构、索引分类、索引语法、SQL性能分析、索引使用、索引设计原则
1. 索引概述
-
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
-
优势:提高数据检索的效率,降低数据库IO成本,通过索引排序,降低数据的排序成本,降低cpu的消耗
缺点:索引也是占数据空间的,索引大大提高了查询效率但同时降低了更新表的速度,insert,update,delete
2.索引结构
索引是在存储引擎层实现的,不同的存储引擎有不同的结构
-
B+Tree索引,最常见的索引类型,大部分引擎都支持B+树索引
-
Hash索引,底层数据结构是用哈希表实现的,只有精确匹配索引列的查询有效,不支持范围查询,
-
R-tree空间索引,空间索引是MySAM引擎的特殊引用类型,主要用于地理空间数据类型
-
Full-text全文索引,是一种通过建立倒排索引,快速匹配文档格式。类似于Luncene,Solr,es
基本所有的存储引擎都支持b+树
-
B-Tree(多路平衡查找树),每个节点可以有多个子节点,指针个数比 节点个数多1.
达到节点个数中间节点会向上分裂,(5阶每个节点最多存储4个key)
-
B+Tree,每个节点都在叶子节点中,叶子节点之间形成一个单向链表,上面非叶子节点只是起到索引作用
在MySQL中索引结构对B+Tree进行了优化,增加一个指向相邻叶子节点的链表指针,提高区间访问性能
键值,数据,指针,页/块(16k)
-
Hash索引结构和HashMap底层原理一样,只能用于查询,一次检索就可以了,效率高于B+Tree,支持Hash索引的是Memory,而InnoDB具有自适应哈市功能。
-
为什么InnoDB存储引擎选择使用B+Tree索引结构?
-
二叉树顺序插入会形成链表结构,红黑树虽然有所优化,但本质还是二叉树,
-
相对于二叉树,在一定数据的情况下,B+Tree层级更少,搜索效率高,
-
对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针也跟着减少,同样保存大量的数据,只能增加树的高度,导致性能降低,
-
相对Hash索引,B+Tree支持范围匹配及排序操作,hash只能用于单次查询。
-
3.索引分类
主键索引:针对于表中主键创建的索引,默认自动创建,只能有一个,primary
唯一索引:避免同一列中某个数据列中的重复值,可以有多个,unique
常规索引:快速定位特定数据,可以有多个
全文索引:全文索引是查找文本中的关键字,而不是索引中的值,可以有多个,fulltext
-
聚集索引:将数据与索引放到了一块,索引结构的叶子节点保存了行数据,(比如id对应的行数据都存在在聚集索引叶子节点上,可以根据id查到一行的数据)必须有,而且只有一个
-
二级索引:将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键,(name字段作为二级索引,其叶子节点存储是每个字段对应的id,然后再根据id查询)可以存在多个,
聚集索引选用规则:
如果存在主键,主键索引就是聚集索引。
如果不存在主键,将使用第一个唯一索引(unique)作为聚集索引
如果没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
InnoDB主键索引的B+Tree高度为多高呢?
假设:一行数据的大小是1k,一页中可以存16行这样的数据。InnoDB的指针占用6个字节空间,主键即使为bigint,占用字节数为8。
高度为2:根节点,n * 8 + ( n + 1) * 6 = 16 * 1024 ,n约等1170,数据个数:1171 * 16 = 18736
高度为3:1171 * 1171 * 16 = 21939856