虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,
都会调整因为更新所带来的键值变化后的索引信息
B+树索引
Hash索引
Full-Text全文索引
R-Tree索引
从物理存储角度
聚集索引(clustered index)
非聚集索引(non-clustered index),也叫辅助索引(secondary index)
聚集索引和非聚集索引都是B+树结构
从逻辑角度主键索引:主键索引是一种特殊的唯一索引,不允许有空值
普通索引或者单列索引:每个索引只包含单个列,一个表可以有多个单列索引
多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
唯一索引或者非唯一索引
空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
为什么MySQL 索引中用B+tree,不用B-tree 或者其他树,为什么不用 Hash 索引
聚簇索引/非聚簇索引,MySQL 索引底层实现,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方?
使用索引查询一定能提高查询的性能吗?为什么?
MySQL索引结构首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。
B+Tree索引MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。
先了解下 B-Tree 和 B+Tree 的区别
B-TreeB-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K,在 MySQL 中可通过如下命令查看页的大小:show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree,首先定义一条记录为一个二元组[key, data] ,key为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。
一棵m阶的B-Tree有如下特性:
每个节点最多有m个孩子
除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点,则至少有2个孩子
所有叶子节点都在同一层,且不包含其它关键字信息
每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
关键字的个数n满足:ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字,且关键字升序排序
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki,但都大于k(i-1)
B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree: