MySQL之索引

最新推荐文章于 2025-04-26 20:08:02 发布

herrybody

最新推荐文章于 2025-04-26 20:08:02 发布

阅读量130

点赞数

文章标签： mysql 数据结构数据库

本文链接：https://blog.youkuaiyun.com/Scan688/article/details/119956527

版权

本文探讨了MySQL中BTree索引与哈希索引的区别，以及创建索引的原则，包括单列索引、联合索引的最左前缀策略。重点讲解了索引的优缺点、覆盖索引的利用、避免冗余索引，并指导如何在Mysql中高效添加表字段索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

B 树和 B+树区别

覆盖索引

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

什么是索引?

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

为什么要用索引?索引的优缺点分析

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

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

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

将随机IO变为顺序IO

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

索引的优点

可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点

创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

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

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

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

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

B 树和 B+树区别

B 树的所有节点既存放键(key) 也存放数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

覆盖索引

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

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

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

单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。

按顺序访问范围数据是很快的，这有两个原因。第一，顺序 I/O 不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。

索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

索引创建原则

        单列索引：

                单列索引即由一列属性组成的索引。

        联合索引(多列索引)：

                联合索引即由多列属性组成索引。

最左前缀原则：

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子句也遵循此规则。

使用索引的注意事项？

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

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

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

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

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

避免 where 子句中对字段施加函数，这会造成无法命中索引。--哪些操作会引起索引失效

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

将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null 是不影响性能的。 但是，还是不建议列上允许为空。最好限制not null，因为null需要更多的存储空间并且null值无法参与某些运算。

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

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

避免冗余索引

冗余索引指的是索引的功能相同，能够命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 