数据库面试（二）--- 索引及查询优化

最新推荐文章于 2022-03-31 11:18:37 发布

Jie_xiaoyu

最新推荐文章于 2022-03-31 11:18:37 发布

阅读量260

点赞数

文章标签： big data 数据库 mysql

本文链接：https://blog.csdn.net/jie_xiaoyu/article/details/120687502

版权

本文详细介绍了MySQL索引的原理、设计原则及其对性能的影响。内容包括索引的创建、不同类型的索引（如普通索引、唯一索引、主键索引、联合索引和全文索引）以及数据结构（如B+树）。同时，讨论了索引的优缺点，如查询速度的提升与写操作性能的下降，以及聚簇索引与非聚簇索引的区别。此外，还提到了执行计划分析和慢查询优化，强调了索引覆盖、分库分表等策略在性能优化中的作用。

摘要由CSDN通过智能技术生成

索引概述

索引的本质就是帮助MySQL高效获取数据的排好序的数据结构。
索引的原理：就是把无序的数据变成有序的查询。
1、把创建了索引的列的内容进行排序；
2、对排序结果生成倒排表；
3、在倒排表内容上拼上数据地址链；
4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

索引设计的原则

使查询更快，占用空间更小

适合建索引的列：出现在where子句中的列，连接子句中指定的列，有外键的列
不建议建索引的列：基数小，更新频繁，区分度低，查询很少，数据重复
尽量使用短索引；尽量扩展索引，不要新建索引
不要过度建索引，索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，只保持需要的索引有利于查询即可；

索引对性能的影响

索引可以极大的提高数据的查询速度。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件，索引需要占用物理空间，处理数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚簇索引很多，一旦聚簇索引改变，那么所有非聚簇索引都会跟着变。

索引类型

普通索引：允许被索引的数据列包含重复的值
唯一索引：可以保证数据记录的唯一性
主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录
联合索引：索引可以覆盖多个数据列，如像INDEX（columnA， columnB）
全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术

索引数据结构

在MySQL中使用较多的索引有Hash索引，B+树索引等。
对于Hash索引来说，底层结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择Hash索引，只需一次哈希算法即可立即定位到响应的位置，查询性能最快，在有大量重复键值的情况下，Hash索引的效率也是极低的，因为存在哈希碰撞，还要遍历链表；
在B+树上的常规检索，从根结点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

联合索引

使用多个字段创建的索引是联合索引，联合索引中，如果想要命中索引要按照建立索引时的字段顺序挨个使用，否则无法命中索引。因为索引会按照创建索引时的字段顺序来对数据进行排序。一般情况下，将查询需求比较强的字段放在前面。

聚簇索引和非聚簇索引

聚簇索引：数据和索引是在一起的。聚簇索引的数据物理存放顺序是一致的，找到索引也就找到了数据，只要索引是相邻的，那么对应的数据一定也是相邻的放在磁盘上的；
非聚簇索引：数据和索引不在一起，叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据。
优势：
（1）查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询效率要高；
（2）聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的；
（3）聚簇索引适合用在排序的场合，非聚簇索引不适合。
劣势：
（1）维护索引很昂贵，特别是插入新行或主键被更新导致要分页的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片，使用独享空间可以弱化碎片；
（2）表因为使用UUID（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能比全表扫描更慢，所以建议使用int的auto_increment作为主键；
（3）如果主键比较大的话，那辅助索引将会变得更大，因为辅助索引的叶子存储的主键值，过长的主键值，会导致非叶子节点占用更多的物理空间。

MyISAM使用的是非聚簇索引，树的子节点上的data不是数据本身，而是数据存放的地址。而InnoDB采用的是聚簇索引，树的叶子节点上的data就是数据本身。数据存储在独立的地方，叶子节点都使用一个地址指向真正的表数据。
InnoDB中，如果表定义了PK，那PK就是聚簇索引，如果没有PK，就会找第一个非空的unique列作为聚簇索引。否则，InnoDB会创建一个隐藏的row-id作为聚簇索引。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

覆盖索引和回表

如果只需要在一棵索引树上就可以获取SQL所需要的所有列，就不需要再回表查询，这样查询速度就可以更快。实现索引覆盖最简单的方式就是将要查询的字段，全部建立到联合索引当中。

性能优化

执行计划
执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数。
1、id：是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。id的顺序是按select出现的顺序增长的。id列的值越大执行的优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行；
2、selectType表示查询中每个select子句的类型：

SIMPLE：不包含UNION查询或子查询
PRIMARY：此查询是最外层的查询（包含子查询）
SUBQUERY：子查询中的第一个SELCET
UNION：表示此查询是UNION的第二或随后的查询
DEPENDENT UNION：UNION中的第二个或后面的查询语句，取决于外面的查询
UNION RESULT：UNION的结果
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询，即子查询依赖于外层查询的结果
DERIVED：衍生，表示到处表的SELECT（FROM子句的子查询）
3、table：表示该语句查询的表
4、type：优化sql的重要字段，也是我们判断sql性能和优化程度的重要指标。
取值类型范围：
const:通过索引一次命中，匹配一行数据
system：表中只有一行记录，相当于系统表
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref：非唯一性索引扫描，返回匹配某个值的所有
range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>
index：只遍历索引树
ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。基本就是随着表的数量增多，执行效率越慢。
执行效率：ALL < index < range < eq_ref < const < system，最好能避免ALL和index。
5、possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。
6、key：此字段是mysql在当前查询时所有真正使用到的索引。是possible_keys的子集；
7、key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否被完全使用，这也是我们优化sql时，评估索引的重要指标；
8、rows：mysql查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大；
9、filtered：返回结果的行占需要读到的行（rows列的值）的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少；
10、extra
Using filesort：表示mysql对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询cpu资源消耗大，延时大
Using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错
Using temporary：查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化
Using where：sql使用了where过滤，效率较高