精华内容
下载资源
问答
  • MySQL索引原理

    2018-11-15 09:35:55
    MySQL索引原理讲解,数据结构说明,进一步理解mysql数据。
  • MySQL 索引原理

    2021-02-22 20:20:53
    MySQL 索引原理 索引可以提升查询速度,会影响 where 查询,以及 order by 排序等。 索引类型 MySQL 索引类型如下: 从索引存储结构划分: B Tree索引、Hash索引、FULLTEXT 全文索引、R Tree索引 从应用层次划分...

    MySQL 索引原理

    索引可以提升查询速度,会影响 where 查询,以及 order by 排序等。

    索引类型

    MySQL 索引类型如下:

    • 从索引存储结构划分:

      B Tree索引、Hash索引、FULLTEXT 全文索引、R Tree索引

    • 从应用层次划分:

      普通索引、唯一索引、主键索引、复合索引

    • 从索引键值类型划分:

      主键索引、辅助索引(二级索引)

    • 从数据存储和索引键值逻辑关系划分:

      聚簇索引(聚集索引)、非聚簇索引(非聚集索引)

    普通索引

    这是最基本的索引类型,基于普通字段建立的索引,没有任何限制。

    创建普通索引的方法如下:

    CREATE INDEX <索引的名字> ON tablename (字段名);
    ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
    CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
    

    唯一索引

    与普通索引类似,不同的就是:索引字段的值必须唯一,但允许有空值。

    CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
    ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
    CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;
    

    主键索引

    它是一种特殊的唯一索引,不允许有空值,每个表只能有一个主键。

    CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
    ALTER TABLE tablename ADD PRIMARY KEY (字段名);
    

    复合索引

    单一索引是指索引列为一列的情况,即新建索引的语句只作用在一列上;用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。复合索引可以代替多个单一索引,相比多个单一索引,复合索引所需的开销更小。

    索引同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为 1-2 列的索引,宽索引也就是索引列超过2列的索引,设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。

    CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2...);
    ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2...);
    CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1,字段名2...) );
    

    复合索引使用注意事项:

    • 何时使用复合索引,要根据 where 条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
    • 如果表已经建立了(col1,col2),就没有必要再单独建立(col1);如果现在有(col1)索引,如果查询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。

    全文索引

    查询操作在数据量比较少时,可以使用like模糊查询,但是对于大量的文本数据检索,效率很低。如果使用全文索引,查询速度会比like快很多倍。在MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引,从MySQL 5.6开始 MyISAM 和 InnoDB 存储引擎均支持。

    创建全文索引的方法如下:

    CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
    ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名); 
    CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;
    

    和常用的like模糊查询不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如

    select * from user where match(name) against('aaa');
    

    全文索引使用注意事项:

    • 全文索引必须在字符串、文本字段上建立。
    • 全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;myisam:4-84)
    • 全文索引字段值要进行切词处理,按syntax字符进行切割,例如b+aaa,切分成b和aaa 全文索引匹配查询,默认使用的是等值匹配,例如a匹配a,不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

    二分查找法

    二分查找法也叫作折半查找法,它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀,缺点是更新数据、新增数据、删除数据维护成本高。

    • 首先定位 left 和 right 两个指针
    • 计算 (left + right)/2
    • 判断除以2后索引位置值与目标值的大小对比
    • 索引位置的值大于目标值则索引位置-1,right 移动;小于则 +1,left 移动

    举个例子,下面的有序数组有17个值,查找的目标值是 7 ,过程如下:

    • 第一次查找:

    在这里插入图片描述

    • 第二次查找

    在这里插入图片描述

    • 第三次查找

      image-20210222151745196

    • 第四次查找

      image-20210222151804950

    Hash 结构

    Hash 底层实现是由 Hash表来实现的,是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值,也就是单个key查询,或者说等值查询。其结构如下所示:

    在这里插入图片描述

    从上面结构可以看出,Hash索引可以方便的提供等值查询,但是对于范围查询就需要全表扫描了。 Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

    InnoDB 提供的自适应哈希索引功能强大,接下来重点描述下InnoDB 自适应哈希索引。

    InnoDB 自适应哈希索引是为了提升查询效率,InnoDB存储引擎会监控表上各个索引页的查询,当 InnoDB注意到某些索引值访问非常频繁时,会在内存中基于B+Tree索引再创建一个哈希索引,使得内存中的 B+Tree 索引具备哈希索引的功能,即能够快速定值访问频繁访问的索引页。

    InnoDB自适应哈希索引:在使用Hash索引访问时,一次性查找就能定位数据,等值查询效率要优于 B+Tree。

    自适应哈希索引的建立使得 InnoDB 存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外 InnoDB 自适应哈希索引的功能,用户只能选择开启或关闭功能,无法进行人工干涉。

    show engine innodb status \G;
    show variables like '%innodb_adaptive%';
    

    B+Tree 结构

    MySQL数据库索引采用的是B+Tree结构,在B-Tree结构上做了优化改造。

    B-Tree 结构

    • 索引值和 data 数据分布在整棵树结构中
    • 每个节点可以存放多个索引值及对应的 data 数据
    • 树节点中的多个索引值从左到右升序排列

    在这里插入图片描述

    B-Tree 的搜索:从根节点开始,对节点内的索引值序列采用二分查找法查找,如果命中就结束查找。没有命中会进入子节点重复查找过程,直到对应的节点指针为空,或已经是叶子节点了才结束。

    B+Tree 结构

    • 非叶子节点不存储 data 数据,只存储索引值,这样便于存储更多的索引值
    • 叶子节点包含了所有的索引值和 data 数据
    • 叶子节点用指针连接,提高区间的访问性能

    image-20210222173632496

    相比 B-Tree,B+Tree 进行范围查找时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针进行遍历即可。而 B-Tree 需要遍历范围内的所有节点和数据,显然 B+Tree 效率高。

    聚簇索引

    聚簇索引和非聚簇索引:

    B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。

    在InnoDB引擎中,主键索引采用的就是聚簇索引结构存储。

    • 聚簇索引

      聚簇索引是一种数据存储方式,InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录,行记录和主键存储在一起。这也意味着 InnoDB 的主键索引就是数据表本身,它按主键顺序存放了整张表的数据,占用的空间就是整个表数据的大小。

    • 辅助索引

      InnoDB辅助索引,也叫作二级索引,是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多, 通常创建辅助索引就是为了提升查询效率。一个 InnoDB 表只能创建一个聚簇索引,但可以创建多个辅助索引。

    在这里插入图片描述

    • 非聚簇索引

      与InnoDB表存储不同,MyISAM数据表的索引文件和数据文件是分开的,被称为非聚簇索引结构。

    在这里插入图片描述

    展开全文
  • mysql索引原理

    2019-03-12 17:44:25
    mysql索引原理 mysql 索引主要分为非聚合索引和聚合索引。 查找数据原理是b+tree,二叉树原理。 非聚合索引: 数据文件(b+tree方式构建)和索引文件是分开的。 查询过程先在索引文件找到对应的索引。然后通过索引的...

    mysql索引原理

    mysql 索引主要分为非聚合索引和聚合索引。
    查找数据原理是b+tree,二叉树原理。
    非聚合索引:
    数据文件(b+tree方式构建)和索引文件是分开的。
    查询过程先在索引文件找到对应的索引。然后通过索引的地址找到数据文件中的域值
    集合索引:
    数据文件本身就是索引文件
    可以直接通过索引找到数据

    展开全文
  • Mysql索引原理

    2017-12-06 11:30:51
    Mysql 索引原理》 http://blog.codinglabs.org/articles/theory-of-mysql-index.html   3. 《Mysql inner join left join》 ...

    《Mysql 索引原理》 http://blog.codinglabs.org/articles/theory-of-mysql-index.html

     

    3. 《Mysql inner join left join》

    http://justcode.ikeepstudying.com/2016/08/mysql-%E5%9B%BE%E8%A7%A3-inner-join%E3%80%81left-join%E3%80%81right-join%E3%80%81full-outer-join%E3%80%81union%E3%80%81union-all%E7%9A%84%E5%8C%BA%E5%88%AB/

    4. 《Mysql MyIsam引擎》http://hudeyong926.iteye.com/blog/1489929

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,968
精华内容 3,187
关键字:

mysql索引原理

mysql 订阅