阅读 351

初识MySQL索引

索引的本质

索引是帮助MySQL高效地获取数据的排好序数据结构,索引是基于数据库表创建的。

MySQL中InnDB存储引擎存储索引采用的数据结构

MySQL中InnoDB存储引擎中存储索引采用的数据结构是:B+ 树

  1. 为什么不采用二叉树、B树、红黑树、
  • 二叉树:如果存储索引的数据结构采用二叉树、当数据库表中的索引字段是一个字段递增的,那么由该索引字段组成的二叉树就会变成一条如链表的二叉树,如下图,当通过索引去查找数据的时候,会造成全表扫描,每遍历一个字段,都将进行一次磁盘IO,所以采用二叉树作为存储索引的数据结构是不合适的。

image.png

  • 红黑树:其实红黑树就是一种平衡二叉树,如果存储索引的数据结构采用红黑树的确会比采用二叉树效率更高,但是当数据表中数据量大时,会造成红黑树的高度太高,如果查找的数据在红黑树的最底层则需要遍历到最底层,仍然需要进行多次的磁盘IO。

image.png

  • B树:平衡二叉树没能充分利用磁盘预读功能,而B树是为了充分利用磁盘预读功能来而创建的一种数据结构 ,B树的每个节点可以存储多个索引,它将节点大小设置为磁盘页的大小,充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多的索引,树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少,更多的是在内存中对读取进来的数据进行查找,但它仍不是存储索引的首选数据结构。

image.png

  • B+树 :B+树是B树的一种变种,B+树中的叶子结点由索引及data数据组成,非叶子结点是冗余的,只用来存储索引(不存储data,是为了腾出空间来存储更多的索引),帮助构建B+树,其中叶子结点包含了所有索引字段,每个叶子结点都有指向相邻叶子节点的双向指针(叶子结点之间有了指针,则可以支持范围查找),且索引字段从左到右依次递增。 数据库系统的设计者巧妙利用了磁盘预读原理 ,将一个节点的大小设为等于一个页,这样每个节点(每个结点包括多个索引字段)需要一次I/O就可以完全载入内存,如果在载入内存的数据中找不到,则根据指针的指向将下一个分叉结点加载到内存中进行折半查找。

image.png

MyISAM存储引擎与InoDB存储引擎的区别

  • MyISAM不支持事务,但是每次操作都是原子的,支持表级锁,每次操作都是对整个表进行加锁,存储表的总行数,一个MyISAM表有三个文件:索引文件、表结构文件、数据文件。InnoDB支持ACID事务,支持行级锁级外键约束,因此支持并发写,不存储总行数。
  • MyISAM存储引擎中索引文件.MYI和数据文件.MYD是分离的(非聚集索引),即B+树中叶子结点存放的是索引以及该索引所对应的行记录在磁盘中的地址(索引文件的数据域存储的指向数据文件的指针)。MyISAM不支持数据库事务,也不支持行锁和外键,因此但对数据库表进行插入或者修改update时会锁住整个表,效率较低。
  • InnoDB存储引擎中,存储索引的B+树叶子结点存储的是完整的数据记录,即聚集索引。InnoDB一定有主键,主键一定是聚集索引,如果不手动设置、则会使用unique唯一索引,如果没有unique唯一索引,则会使用数据库内部的一个行的隐藏id来当做主键索引。在聚集索引上建立的索引称之为辅助索引,辅助索引访问数据需要回表查找(使用辅助索引检索时,会通过叶节点找到聚集索引的键,然后通过聚集索引找到完整的行记录),非聚集索引都是辅助索引。InnoDB中非主键索引的叶子结点存储的不是数据行的地址而是索引以及当前表的主键字段的值。 🚨注意:并非所有的非聚集索引或者说是辅助索引需要回表查询,例如通过覆盖索引【当一个索引包含需要查询的所有字段的值时,称之为覆盖索引,查询数据时只需从索引中就能够取得到,而不必从数据表中读取】也可以只通过查询一次就获取数据,无需回表。例如表m中有一个普通索引m_index(name),主键索引对应的字段为id。当我们通过SQL语句:select id from m where name = 'pro';能够命中name索引,m_index索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,而如果查询的语句为select id,sex from m where name = 'pro';则需要回表。

聚集索引与非聚集索引的区别

  • 聚集索引:将数据与索引存储在一起,并且是按照一定顺序组织在一起的,找到索引就找到了数据。通过聚集索引可以直接获取数据,相比非聚集索引需要第二次查询效率要高。聚集索引对于范围查找的效率很高,因为其数据是按照大小顺序排序的。

  • 非聚集索引:叶子结点不存储数据,存储的是对应表的主键索引字段(这就是为什么InnoDB的表都需要设置一个主键)。检索数据时,先在非聚集索引中找到对应的主键,再根据主键索引,查找对应的行数据,等于走了两遍B+树进行搜索。

索引的分类

  1. 普通索引。 普通索引是最基本的索引,没有限制。创建语句:CREATE INDEX 索引名称 ON (索引字段)

  2. 联合索引。 在一个表的多个字段上创建的索引,遵循最左匹配原则(下面会介绍最左匹配原则)。创建语句:CREATE INDEX 索引名称 ON (索引字段1,字段2..)

  3. 唯一索引[unique]。 唯一索引要求索引列的值必须唯一,不能存在重复的值,但允许存在null值。创建语句:CREATE UNIQUE INDEX 索引名称 ON (索引字段)

  4. 主键索引。 主键索引是一种特殊的唯一索引,一个表中只能存在一个主键索引,用于唯一标识一条记录,不允许存在null值。创建语句:Alter table 表名 add primary key(列名)

  5. 全文索引[fulltext] 全文索引是用于检索字段中是否包含或不包含指定的关键字,其内部的索引结构采用的是与搜索引擎相同的倒排索引结构,其原理是对字段中的文本进行分词,然后为每一个出现的单词记录一个索引项,这个索引项中保存了所有出现过该单词的记录的信息,也就是说在索引中找到这个单词后,就知道哪些记录的字段中包含这个单词,全文索引只能用于存储引擎为InnoDB或MyISAM的表(MySQL5.6之前的版本只有MyISAM支持全文索引),只能为CHAR、VARCHAR、TEXT类型的列创建全文索引。

联合索引

  • 联合索引也被称为复合索引,是指对表上的多个列进行索引,当经常要用到多个字段的多条件查询,可以考虑建立联合索引。
  • 最左匹配原则:对于联合索引,Mysql从左到右使用索引中的字段,一个查询可以只使用索引中的一部份,但必须包含最左侧部分。如索引为key index (a,b,c). 可以支持(a)或 (a,b)或 (a,b,c)这3种组合进行查找,但不支持 (b,c)进行查找。

联合索引测试:

CREATE TABLE `user2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(225) DEFAULT NULL,
  `pwd` varchar(225) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `union_index` (`id`,`name`,`pwd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


复制代码
  • (id),走索引
EXPLAIN SELECT * FROM USER2 WHERE  id=2  
复制代码

image.png

  • (id,name),走索引
EXPLAIN SELECT * FROM USER2 WHERE  id=2  AND NAME='admin' 
复制代码

image.png

  • (id,name,pwd),走索引
EXPLAIN SELECT * FROM USER2 WHERE  id=2  AND NAME= 'root' AND pwd='root'
复制代码

image.png

  • 跳过id (name,pwd),索引失效
EXPLAIN SELECT * FROM USER WHERE  user.name='cheng'  AND user.pwd='admin'
复制代码

image.png

  • 跳过name (id,pwd),id走索引,pwd不走索引
EXPLAIN SELECT * FROM USER2 WHERE  id=2  AND pwd='admin'
复制代码

image.png

  • 注意:当联合索引的中的所有字段都包含表中的所有字段时,无论是否满足最左匹配原则,都会走索引,大家可以自行测试一下。

参考文章www.cnblogs.com/aspirant/p/…

文章分类
后端
文章标签