青岛网站建设代理加盟,免费网站建设系统,手机软件程序开发,网站项目策划书实例常见索引
索引分类
在MySQL数据库#xff0c;将索引的具体类型主要分为以下几类#xff1a;主键索引、唯一索引、常规索引、全文索引。
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建, 只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以…常见索引
索引分类
在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建, 只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个FULLTEXT
索引按照物理实现方式索引可以分为 2 种聚簇聚集和非聚簇非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。
聚簇索引
聚集索引(ClusteredIndex)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有,而且只有一个
使用记录主键值的大小进行记录和页的排序 页内的记录是按照主键的大小顺序排成一个单向链表 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表 存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
B树的叶子节点存储的是完整的用户记录
所谓完整的用户记录就是指这个记录中存储了所有列的值包括隐藏列 聚集索引选取规则:
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
二级索引辅助索引、非聚簇索引
二级索引(SecondaryIndex) 将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个 聚集索引和二级索引的具体结构如下 聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是该字段值对应的主键值。
SQL查找过程
当我们执行如下的SQL语句时具体的查找过程是什么样子的。 ①. 由于是根据name字段进行查询所以先根据nameArm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到10对应的行row。
③. 最终拿到这一行的数据直接返回即可。 回表查询 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询。 思考题 以下两条SQL语句那个执行效率高? 为什么? A. select * from user where id 10 ; B. select * from user where name ‘Arm’ ; 备注: id为主键name字段创建的有索引 解答 A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引直接返回数据。 而B语句需要先查询name字段的二级索引然后再查询聚集索引也就是需要进行回表查询。 InnoDB主键索引的Btree高度为多高呢? 假设: 一行数据大小为1k一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间主键即使为bigint占用字节数为8。 高度为2 n * 8 (n 1) * 6 16*1024 , 算出n约为 1170 1171* 16 18736 也就是说如果树的高度为2则可以存储 18000 多条记录。 高度为3 1171 * 1171 * 16 21939856 也就是说如果树的高度为3则可以存储 2200w 左右的记录。 InnoDB的B树索引的注意事项 根页面位置万年不动内节点中目录项记录的唯一性一个页面最少存储2条记录 MyISAM中的索引方案
B树索引适用存储引擎如表所示
索引 / 存储引擎MyISAMInnoDBMemoryB-Tree索引支持支持支持
即使多个存储引擎支持同一种类型的索引但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引而Memory默认的索引是Hash索引。
MyISAM引擎使用BTree 作为索引结构叶子节点的data域存放的是数据记录的地址。
MyISAM索引的原理 如果我们在Col2上建立一个二级索引则此索引的结构如下图所示 MyISAM 与 InnoDB对比
MyISAM的索引方式都是“非聚簇”的与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别
① 在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在 MyISAM 中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引。
② InnoDB的数据文件本身就是索引文件而MyISAM索引文件和数据文件是分离的索引文件仅保存数 据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录主键的值而MyISAM索引记录的是地址。换句话说 InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通 过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。
⑤ InnoDB要求表必须有主键 MyISAM可以没有。如果没有显式指定则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐 含字段作为主键这个字段长度为6个字节类型为长整型。 索引的代价
索引是个好东西可不能乱建它在空间和时间上都会有消耗
空间上的代价 每建立一个索引都要为它建立一棵B树每一棵B树的每一个节点都是一个数据页一个页默认会占用16KB 的存储空间一棵很大的B树由许多数据页组成那就是很大的一片存储空间。
时间上的代价 每次对表中的数据进行增、删、改操作时都需要去修改各个B树索引。而且我们讲过B树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录还是内节点中的记录也就是不论是用户记录还是目录项记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需要额外的时间进行一些记录移位 页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引每个索引对应的B树都要进行相关的维护操作会给性能拖后腿。
