MySQL关于索引的理解

浮尘笔记

已于 2023-05-12 15:46:49 修改

阅读量284

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-04-27 21:52:08 首次发布

本文链接：https://blog.csdn.net/rxbook/article/details/130396914

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

【关于mysql中的索引】

MySQL的MyISAM和InnoDB存储引擎的索引底层实际上是一个B+TREE，具体存储底层逻辑会在后面讲。这里先说说索引的用法。

/*添加普通索引*/
ALTER TABLE tb_test ADD KEY is_del(update_time,is_del);
ALTER TABLE tb_test ADD INDEX idx_name(name); 

/*删除索引*/
ALTER TABLE tb_test DROP INDEX is_del;

/*显示索引*/
show index(es) from 表名
show keys from 表名 
desc 表名

1.索引的本质是数据结构，可以理解为“排好序的快速查找数据结构”，使用索引的目的是为了使查询的速度变快。

2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。

3.虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。

4.主键与唯一索引的区别：主键不能有空值，唯一索引可以有空值。一个表中可以有多个唯一索引，但是主键只能有一个。

【索引的分类】

按照底层数据结构分类

MyISAM和InnoDB存储引擎只支持B+TREE索引，MEMORY和HEAP存储引擎⽀持Hash和B+TREE索引。

关于Hash索引和 B+tree索引：

BTree和B+Tree索引可以用在 =, >, >=，而 Hash索引只能用于对等比较，例如=操作符。

B+Tree索引需要从根节点到枝节点，最后才能访问到页节点，需要多次IO访问；然而Hash索引可以一次定位数据，所以Hash检索效率远高于B+Tree索引。

在效率和功能选择上，InnoDB和MyISAM还是选择了功能，效率层面再用其它方式去优化。

现在来说说使用较多的InnoDB和MyISAM引擎。

聚簇索引（InnoDB）：主键的叶⼦节点存储数据⾏，数据和索引是在⼀起；普通索引只存储主键值，查询其它字段需要回表。
⾮聚簇索引（MyISAM）：不管是主键索引还是普通索引，B+树叶⼦节点只会存储数据⾏指针，数据和索引不在⼀起。

为什么不建议InnoDB使⽤过⻓的字段作为主键? 因为所有的普通索引都引⽤主键，过⻓的主键会让普通索引变得过⼤。

从性能上讲，主键索引和普通索引差别在哪里？

InnoDB使用的是聚簇索引，数据文件和索引文件是同一个文件,将主键组织到一棵B+tree中，而行数据就储存在叶子节点上。查询普通索引其实是先查找叶子节点对应的主键，再使用主键再执行一次B+tree检所操作。

MyISM使用的是非聚簇索引（.frm、.myd、.myi），节点的结构完全一致只是存储的内容不同而已，由于索引树是独立的，索引指向数据的位置。通过普通索引检索无需访问主键的索引树。因此MyISAM的查询效率高。

按照功能分类

普通索引：最基本的索引，没有任何约束限制；
唯一索引：与普通索引类似，但是具有唯一性约束，可以有空值；
主键索引：特殊的唯一索引，不允许有空值，一个表只能有一个主键索引；
组合索引：将多个列组合在一起创建索引，可以覆盖多个列，需要注意最左原则；
外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作；
全文索引：只能用于MyISAM，并且只能对英文进行全文索引（一般搜索会用第三方的ES或者sphinx等）。

组合索引说明：

select * from user where name='renxing'; 需要回表查询，⾮主键索引 —> 主键索引 —> 数据。 
select id,name from user where name='renxing'; 不需要回表，在辅助索引树上就可以查询到了，叫做：覆盖索引，因此要多⽤组合索引。

组合索引的最左原则说明，假设有个组合索引(a,b,c) 在如下查询条件下都会生效：

① a=1 and b=1 and c=1

② a=1 and b=1

③ a=1

④ b=1 and a=1 and c=1

对于 a=1 and c=1 只能部分生效，可以理解为mysql会先对前边的部分筛选结果，然后在结果中进行后面的筛选。

【索引的使用场景】

哪些情况需要创建索引

频繁作为查询条件的字段应该创建索引；
关联查询on两边都要创建索引；
需要排序的字段应该创建索引；
需要统计和分组(group by) 的字段应该创建索引；
DISTINCT 字段需要创建索引；

哪些情况不需要创建索引

表记录太少不用建索引；
经常增删改操作的表不用建索引，（为什么？）

* 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。

* 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

* 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。

频繁更新的字段不适合建立索引，因为每次更新不仅是更新了记录，还会更新索引（视具体业务情况而定）
WHERE条件里用不到的字段可以不创建索引
数据重复度高的字段（比如性别字段：男和女）

比如:要在存储性别的100万行数据中查找其中的50万行(比如性别为男的数据)，如果创建了索引，需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

什么情况下索引会失效

索引进行了表达式计算；
对索引使用函数；
如果在 OR 前的条件列加了索引，而在 OR 后的条件列没有进行索引，那么索引会失效；
使用LIKE %模糊查询；

#索引上表达式计算会失效
WHERE comment_id+1 = 91，优化为 WHERE comment_id = 90 

#索引使用函数会失效
WHERE SUBSTRING(comment_text, 1,3)='abc' 优化为 WHERE comment_text LIKE 'abc%' 

#字段上进行运算，索引会失效
WHERE left(department_code, 5) = '00028’ 优化为 WHERE department_code LIKE '00028%';

索引的创建原则

选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
为经常需要排序、分组和联合操作的字段建立索引：经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
为常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
限制索引的数目（避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率）：索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
删除不再使用或者很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
根据情况创建复合索引，复合索引可以提高查询效率
避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率
主键尽可能选择短的数据类型，可以有效减少索引的磁盘占用提高查询效率

索引的细节

复合索引遵循最左原则
like 查询，%不能在前。复杂的文本关键词查询建议使用ES或者sphinx或者solr
column is null 可以使用索引
如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引
如果or前面的条件中的列有索引，后面的没有，索引都不会被用到
如果列的类型是字符串，查询时一定要给值加引号，否则索引会失效
索引列尽量设置为 NOT NULL 约束；
注意最左原则。

如何设计索引比较合适

        首先一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外的负担。针对这个情况，当你需要新建索引的时候，首先考虑在原有的索引片上增加索引，也就是采用复合索引的方式，而不是新建一个新的索引。另外我们可以定期检查索引的使用情况，对于很少使用到的索引可以及时删除，从而减少索引数量。
        同时，在索引片中，我们也需要控制索引列的数量，通常情况下我们将 WHERE 里的条件列添加到索引中，而 SELECT 中的非条件列则不需要添加。除非 SELECT 中的非条件列数少，并且该字段会经常使用到。
        另外单列索引和复合索引的长度也需要控制，在 MySQL InnoDB 中，系统默认单个索引长度最大为 767 bytes，如果单列索引长度超过了这个限制，就会取前缀索引，也就是取前 255 字符。这实际上也是告诉我们，字符列会占用较大的空间，在数据表设计的时候，尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引。

在多条件联合查询的时候最好创建联合索引。对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。回表指的就是数据库根据索引找到了数据行之后，还需要通过主键再次到数据表中读取数据的情况。

【索引对性能的影响】

使用索引的优点：

大大减少服务器需要扫描的数据量；
帮助服务器避免排序和临时表；
将随机 I/O 变成顺序I/O；
大大提高查询速度，

使用索引的缺点：

写入数据的速度会降低；
占用更多磁盘空间（空间换时间）

【Q&A】

【问题】主键与唯一索引的区别

【回答】主键不能有空值，且只能有一个；唯一索引可以有空值，且可以有多个。

【问题】MySQL字段设为not null的好处

【回答】① 查询效率高；② 唯一索引会认为 null 和 "" 为两个值。

【问题】在 MySQL 中统计数据表的行数，可以使用三种方式：SELECT COUNT(*)、SELECT COUNT(1)和SELECT COUNT(具体字段)，使用这三者之间的查询效率是怎样的？
【回答】一般情况下，三者执行的效率为 COUNT(*)= COUNT(1)> COUNT(字段)。我们尽量使用COUNT(*)，当然如果你要统计的是某个字段的非空数据行数，则要用COUNT(字段)。 InnoDB 存储引擎中，COUNT(*)和COUNT(1)本质上并没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环 + 计数的方式进行统计。MyISAM 存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了row_count值，而一致性则由表级锁来保证。

【问题】在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
【回答】SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列，如果不同就使用联合索引。

【问题】ORDER BY 是对分组排序还是对分组中的记录排序呢？
【回答】ORDER BY 是对记录进行排序。如果你在 ORDER BY 前面用到了 GROUP BY，实际上这是一种分组的聚合方式，已经把一组的数据聚合成为了一条记录，再进行排序的时候，相当于对分组进行了排序。

【问题】mysq|中的聚集索引 ,稀疏索引如何理解?
【回答】innodb 数据和索引存储在一起就是聚簇索引。myisam的数据和索引没有存储在一起所以是非聚簇索引。

【问题】like 'aaa%' 一定会用到索引么?
【回答】会，但是 '%aaa' 用不到索引。

【问题】为什么不建议写select * from进行查询
【回答】避免回表查询

【问题】为什么innodb引擎要求一定要建立主键索引?
【回答】主键索引下面直接保存的是数据，非主键索引保存的是主键，需要回表查询。

浮尘笔记

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL关于索引的理解

MySQL的MyISAM和InnoDB存储引擎的索引底层实际上是一个B+TREE，具体存储底层逻辑会在后面讲。这里先说说索引的用法。1.索引的本质是数据结构，可以理解为“排好序的快速查找数据结构”，使用索引的目的是为了使查询的速度变快。2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。3.虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。4.主键与唯一索引的区别：主键不能有空值，唯一索引可以有空值。
复制链接

扫一扫