mysql索引

奇怪的玩家aaa

已于 2022-03-07 11:37:51 修改

阅读量637

点赞数

文章标签： spring java 后端

于 2022-03-06 17:27:54 首次发布

本文链接：https://blog.csdn.net/weixin_53310322/article/details/123281849

版权

1. 索引简介

索引是将关键字数据以某种数据结构的方式存储到内存，用于提升数据的检索性能。索引既有逻辑上的概念，更是一种物理存储方式，且事实存在，需要耗费一定的存储空间。索引的原理大概可以概况为以空间换时间。

1.1 分类

BTREE
Hash

1.2 索引的优缺点

优点：

大大加快了数据的减速速度，这是创建索引的最主要因素
通过建立唯一索引，保证数据的唯一性
帮助服务器避免排序和临时表
随机io可以变成顺序io

缺点：

创建索引和维护索引需要很多时间，且时间随着数据量的增加而增加
如果一个数据建立了索引，那么增删改这个数据，相应的索引也要进行动态修改，这将大大降低sql的执行效率。
占用一定物理空间

1.3 索引关键字的选取原则

表的某个字段值离散度越高越适合左索引的关键字
占用存储空间少的字段适合做索引的关键字
较频繁地作为where查询条件的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。
更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。

2.hash索引

hash索引比较的是进行hash运算之后的hash值，只能用于等值的过滤，不能用于基于范围的过滤。经过相应的hash算法处理之后的hash值的大小，并不能保证和hash运算前完全一样，数据库自然也无法利用索引的数据来避免任何排序运算。

优点：hash效率很高
缺点

hash索引不能进行范围查询
hash索引无法被用来避免数据的排序操作
hash索引不能利用部分索引键查询
hash索引在任何时候都不能避免表扫描
hash索引遇到大量hash值相等的情况后性能不一定就会比b-tree索引高

3.btree+索引

BTree索引是 MySQL 数据库中使用最为频繁的索引类型。

那么问题来了，常见的数据结构如：二叉树，红黑树，avl树，bTree等，为什么偏偏选择btree呢？

3.1 二叉搜索树

二叉搜索树是一种支持数据快速查找的数据结构。

在这里插入图片描述
但是二叉树有个致命的缺点，极端情况下会退化成链表，检索性能急剧下降。在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。

在这里插入图片描述

3.2 红黑树

红黑树是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。红黑树拥有不错的平均查找效率。

在这里插入图片描述

但是，当数据顺序插入时，树的形态一直处于右倾的趋势。从根本上看，依旧没有解决问题。随着大量数据的不断加入。对应性能的损耗也是巨大的。

3.3 avl树

avl树是一种严格的自平衡树。因为avl树是绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

在这里插入图片描述

AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。
但是，新的问题又来了，数据库查找的瓶颈在于磁盘，使用的是 AVL 树，每一个树节点只存储一个数据，一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，这样是非常耗时的。

3.4 bTree

BTree是为了磁盘等外存储设备设计的一种平衡二叉树。
数据存储页的概念：系统从磁盘读取数据到内存时是以磁盘块为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

BTree用作数据库索引优点：

优秀检索速度，
尽可能少的磁盘IO，加快了检索速度
可以支持范围查找

BTree

为了描述B-Tree，首先定义一条记录为一个二元组[key,data ，key为记录的键值，对应表中主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

在这里插入图片描述

3.5 B+Tree

为什么使用B+Tree？
从一个中的一个BTree结构图中可以看到每个节点中不仅包含数据的key值，还有一个data值。而每一页的存储空间时有限的，如果data数据较大时将会导致每个节点能存储的key数量很少。

B+树的优化思想：

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的的叶子节点上，而非叶子节点上值存储key值星系，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

在这里插入图片描述

3.6 索引最终选择B+Tree的原因

hash很快，但每次io只能读一个数
红黑树，在大量数据的情况下，容易右倾。极端情况下形成一个链表，搜索效率很低
AVL树虽然没有右倾的现象，但由于是高度平衡的树，消耗性能很高
B树每个节点内存储的数据，若数据量太大，每一页存放的key较少
B+树使用索引和指针的存储方式，可以存储大量索引

4. 聚簇索引和非聚簇索引

4.1 聚簇索引

聚簇索引就是指主索引文件和数据文件为同一文件，聚簇索引主要用在Innodb存储引擎中。在改索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键。
在这里插入图片描述

4.1.1 主键索引

InnoDB会根据主键ID作为key建立索引B+树，而B+树的叶子节点存储的是主键ID对于的数据。
通过主键查找时，通过主键key找到索引的位置，并直接读取主键key对应的叶子节点的数据，速度非常快。

在这里插入图片描述

4.1.2 辅助索引

辅助索引：在InnoDB中为表里某个字段加索引（除主键索引，比如emp_name）:
InnoDB就会建立emp_name索引B+树
节点里存的就是emp_name这个key
叶子节点路存储的数据包括主键key
在这里插入图片描述

通过辅助索引查找时需要查找两次：
1.根据user_name在B+树查询到叶子节点后，再在索引树找到主键key
2.在根据主键key查询主键索引树，才可以定位查找对应的数据。

4.2非聚簇索引

MyISAM用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM在建表时以主键作为key来建立主索引B+树，树的叶子节点存的是对应数据的物理地址。

在这里插入图片描述

搜索时得到物理地址后，就可以到MyISAM数据文件中直接定位到具体的数据记录。

在这里插入图片描述

5. mybatis索引分类

查看索引

SHOW KEYS FROM book_tab

5.1 普通索引（单列索引）

单列索引是最基本的索引，它没有任何限制。

CREATE INDEX index_name ON table_name(col_name);

5.2 复合索引

复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会使用。因此，在复合索引中索引列的顺序至关重要。

create index index_name on table_name(col_name1,col_name2,...);

5.3 唯一索引

唯一索引和普通索引类似，主要区别在于，唯一索引限制列的值必须唯一，但允许存在空值。

CREATE UNIQUE INDEX index_name on table_name(col_name,...);

5.4主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是建表的时候同时创建主键索引。

5.5 全文检索

在一般情况下，模糊查询都是通过like的方式进行查询。但是，对于海量数据，这不是一个好方法，在like “value%”可以使用索引，但是对于like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但对于海量数据，全表扫描是非常可怕的事情，所以like进行模糊查询的性能很差。
全文索引主要用来查找文本中的关键子，而不是直接与索引中的值比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

CREATE FULLTEXT INDEX index_fulltext ON table_name(col_name)

目前只有char、varchar，text 列上可以创建全文索引

fulltext索引配合match against操作使用，而不是一般的where语句加like。

SELECT * FROM tablename WHERE MATCH(column1) AGAINST('中国')

6.删除索引

DROP INDEX 索引名 ON 表名

通过主键约束自动添加的主键索引不能用DROP INDEX语句删除，但通过唯一约束自动创建的唯一索引可以这么删除，但删除后的唯一约束也被删除了。

7.索引失效

在where后面使用or，倒是索引失效（经量少用or）
使用like，like查询是以%开头的
复合索引遵循“最左前缀”原则，记载查询条件中是以了复合索引的第一个字段，索引才会被使用。
如果列类型是字符串，那么一定要在条件中将数据使用引号引用起来，否则不使用索引。
使用in导致索引失效。
DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效。
对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！

有效：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';     
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

无效：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';       
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

奇怪的玩家aaa

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql索引

mysql索引1. 索引简介1.1 分类1.2 索引的优缺点1.3 索引关键字的选取原则2.hash索引3.btree+索引3.1 二叉搜索树3.2 红黑树3.3 avl树3.4 b+Tree1. 索引简介索引是将关键字数据以某种数据结构的方式存储到内存，用于提升数据的检索性能。索引既有逻辑上的概念，更是一种物理存储方式，且事实存在，需要耗费一定的存储空间。索引的原理大概可以概况为以空间换时间。1.1 分类BTREEHash1.2 索引的优缺点优点：大大加快了数据的减速速度，这是创
复制链接

扫一扫