Mysql索引

� west

已于 2024-02-21 19:26:24 修改

阅读量1k

点赞数 18

文章标签： mysql 数据库

于 2023-12-23 14:46:45 首次发布

本文链接：https://blog.csdn.net/CCi_9/article/details/135168769

版权

一文搞懂MySQL索引所有知识点（建议收藏）_敖丙的博客-CSDN博客

1、优势和劣势：

优势：

加快数据查询的效率，降低数据库的io成本（数据库查询的时候，都是从硬盘中读取一部分数据）

通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗（被索引的列会自动进行排序，如果使用order by语句，效率会提高很多）

劣势：

会占据磁盘空间（索引相当于创建一个新的数据集保存在磁盘中，只是这个数据集只保存了表中数据的存储位置，类似字典中的目录）

会降低表的更新效率，每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

2、类型：

主键索引：索引中的值唯一且不能为空，是一种特殊的唯一索引
唯一索引：索引中的值唯一，可以为空，但空只能有一个
普通索引：很普通的索引，没什么限制，值可以重复，可以为空
复合索引：多个列组合成的索引，可以设置为unique（唯一索引的意思），遵循最左匹配原则
全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。全文索引主要是用来解决WHERE name LIKE "%zhang%"等针对文本的模糊查询效率低的问题
前缀索引：对文本类型，如char, varchar, text类列上创建索引时，可以指定索引列的长度

比如 varcahr(8), 表示对字符串的前8位创建索引。

什么是前缀索引、为什么要用前缀使用、用在什么场景下？ - 知乎 (zhihu.com)

3、数据结构：

hash：hash表，在进行等值查询的时候效率高，时间复杂度为O(1)，但是不支持范围查询，因为hash计算有碰撞

B+树： B树（叶子和非叶子节点都会存储数据）的升级版，特点是只有叶子节点会保存数据，非叶子节点存储键值。叶子节点之间使用双向指针连接，形成一个双向有序列表；B+树可以保证等值和范围查询的快速查找

4、MySQL索引的实现

mysql有两种存储引擎的索引实现：myisam索引和innoDB索引

1、MyIsam

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址，因此在MyIsam中，主键索引和辅助索引的结构是一样的，叶子节点保存的都是行记录的磁盘位置，只是主键索引的键值是唯一的，辅助索引的键值可以重复。对于辅助索引，即使是等值查询，也需要按照范围的方式进行，因为等值查询可能存在多个相同的记录。

2、InnoDB

主键索引（聚簇索引）：

叶子节点存放的就是行数据，因此在InnoDB中，主键索引存放逻辑顺序的就是数据在磁盘中的真实顺序。

辅助索引：

聚簇索引之外的所有索引都称为辅助索引，辅助索引中，叶子节点存放的数据是行数据的主键值。使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程叫做回表。

组合索引:

对于一个组合索引 index(a,b,c), 支持 a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找。查询顺序是先对比a, 然后对比b，再对比c。

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配（先匹配a，再匹配b，再匹配c）直至遇到范围查询（比如a是等值查询，b是范围查询，则b不会进入索引）(>、

在建立组合索引的时候，应该考虑是否可以将多个单列索引组成一个联合索引。不仅节省空间，而且可更容易使用到覆盖索引。创建时，应该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大。如果某个字段频繁返回，也可以将该字段加到联合索引中，这样可以使用到覆盖索引..。

将范围查询的列放在复合索引的最后面

覆盖索引：

覆盖索引不是一种索引结构，只是一种优化手段。当进行查询的时候，比如语句 select * from user where a = 10 and b = 11 and c=11，即使走了联合索引（a,b,c），仍然需要回表，即通过主键索引获取行数据。但如果我们只需要 a b c id 这几个字段，就可以改成 select id, a, b, c from user where a = 10，这样当我们查询完组合索引的叶子节点就可以返回，而不需要回表。这种情况就是覆盖索引。