mysql索引小结_MySQL索引小结

最新推荐文章于 2022-02-16 23:21:34 发布

克嘉

最新推荐文章于 2022-02-16 23:21:34 发布

阅读量71

点赞数

文章标签： mysql索引小结

本文链接：https://blog.csdn.net/weixin_28781243/article/details/113454843

版权

1 MySQL的基本存储结构

MySQL的基本存储结构是页(记录都存在页里边)：

各个数据页可以组成一个双向链表

每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页：需要遍历双向链表，找到所在的页

从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

2 为什么要使用索引？

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度(大大减少检索的数据量)，这也是创建索引的最主要原因。

帮助服务器避免排序和临时表。

将随机IO变为顺序IO。

可以加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

3 索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

当表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样会降低的数据的维护速度。

索引需要占用物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

创建索引和维护索引要好费时间，这种时间随着数据量的增加而增加。

4 索引是如何提高查询速度的？

将无序的数据变成有序的数据(就像查目录一样)。

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！(二分查找，时间复杂度近似为O(logn))

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

5 使用索引注意事项

在经常需要搜索的列上，可以加快搜索的速度；

在经常使用where子句的列上创建索引，加快条件的判断速度；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询速度；

对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引；

在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度；

避免where子句对字段施加函数，只会造成无法命中索引。

在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键；

将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描；

删除长期未使用的索引，无用索引的存在会造成不必要的性能损耗，MySQL 5.7可以通过查询sys库的chema_unused_indexes视图来查询哪些索引从未被使用

在使用limit offset查询缓慢时，可以借助索引来提高性能。

6 MySQL索引主要使用的两种数据结构

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

BTree索引

MySQL的BTree索引使用的是B树中的B+Tree。但对于主要的两种存储引擎(MyIsam和InnoDB)的实现方式是不一样的。

7 MySQL索引种类

单列

普通索引：加速查找；

唯一索引：加速查找+约束：不能重复(只能有一个空，不然就重复了)；

主键：加速查找+约束：不能重复+不能为空；

多列

联合索引(多个列创建索引)---->相当于单列的普通索引

联合唯一索引 ---->相当于单列的唯一索引

ps：联合索引的特点：遵循最左前缀的规则

8 什么是最左前缀原则？

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引

select * from user where name=xx ; // 可以命中索引

select * from user where city=xx ; // 无法命中索引

复制代码

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

9 MyIsam和InnoDB实现BTree索引方式的区别

MyIsam：

B+Tree叶子节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后data域的值为地址读取响应的数据记录。这被称为“非聚簇索引”。

InnoDB：

其数据文件本身就是索引文件。MyIsam中，索引文件和数据文件是分离的，而InnoDB中，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyIsam不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引搜索时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这会造成主索引频繁分裂。

【注】更详细的请出门左转: MySQL索引和SQL调优

10 覆盖索引介绍

什么是覆盖索引？

如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。覆盖索引就是要查询的列和索引是对应的，不做回表操作。

覆盖索引使用实例

加入创建了索引(username，age)，在查询数据的时候：

select username，age from user where username = ‘Java’ and age = 22;

要查询成的列在叶子节点都存在，所以不用回表。

11 选择索引和编写利用这些索引查询的3个原则

单行访问和很慢的。特别是在机械硬盘存储中。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能尽可能多的包含所需要的行。使用索引创建位置引，用以提高效率。

按顺序访问范围数据是很快的，这有两个原因：

第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多(特别是对机械硬盘)。