【MySQL】——索引

小羊学伽瓦

已于 2024-09-29 13:37:06 修改

阅读量787

点赞数 22

分类专栏： mysql学习文章标签： mysql 数据库

于 2024-09-28 17:05:35 首次发布

本文链接：https://blog.csdn.net/qq_45730261/article/details/142589566

版权

mysql学习专栏收录该内容

3 篇文章 0 订阅

订阅专栏

4.问题：为什么Innodb存储结构要使用B+树的索引结构？

一.什么是索引？

索引(index)是帮助MySQL高效获取（检索）数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.一个例子直观看一下索引是什么？

这里我们有一条sql语句，用来查询age = 45的数据
在无索引的情况下，会从头开始扫描表中数据，即使找到该数据后，依然会继续扫描。效率较低
而索引结构中（以二叉树为例），节点会指向该数据的地址。45会与节点（36,48）不断比对，从而找到45，找到后又由于节点指向了数据地址，从而找到那一行的记录。

2.索引的优缺点

二.索引结构

1.二叉树

缺点：

顺序插入时，会形成一个链表，查询性能便会大大降低
又由于一个节点下最多只能有两个子节点，在大数据量的情况下，层级就会较深，检索速度就会比较慢

那如何解决这些个缺点呢？大家可能会想到红黑树，由于其自平衡的特点，解决了顺序插入时会形成链表，从而导致性能降低的问题。但问题来了，红黑树本质还是二叉树，一个节点下最多只能有两个子节点，并没有解决大数据量情况下，层级较深，检索速度慢的问题。

说白了，如果一个节点下可以有多个子节点就好解决这个问题了。这时我们想到了B 树。

2.B-Tree

3.B+ Tree

每一个节点存放在一个数据块（页）中的，默认为16KB。

B+ Tree与B tree的区别在于：B+ Tree只有叶子节点存放了数据，而非叶子节点只起到了索引的作用

4.问题：为什么Innodb存储结构要使用B+树的索引结构？

B+树相对于二叉树来说：在数据量一定的情况下，B+树的层级更少，搜索效率更高。

相对于B树来说：B树中叶子节点和非叶子节点都会存放数据，一个节点是通过一个磁盘块，或说一个页来存放（默认大小为16kb），每一页中的存储空间是有限的。而b+树只有叶子节点会存放数据，在页目录中只存放了key和指针。对于b树来说，在页节点中多存放了数据，而一个页中的存放数据又是有限的，也就导致页目录中能存放的页的个数会变少，所以在同等数据量的情况下，就会导致b树比b+树的深度要更深，增大查询时的磁盘IO次数，效率变低了。

三.索引的分类

这里只针对聚集索引和非聚集索引（二级索引）（在sql优化中非常重要）（黑m的无敌百万级ppt！）

那么具体是如何实现的呢？

一条sql语句的执行过程又是什么样的呢？

先走二级索引找到主键值，再通过主键值到聚集索引中拿到行数据

四.SQL性能分析

为什么要学习SQL性能分析呢？因为要做SQL优化，那么要做SQL优化，首先得先得定位出对哪一类sql优化，那我们就得知道每一个的执行性能是怎么样的。对于做SQL优化来说，主要优化哪一类语句呢？主要是查询语句，而索引又在里面占据了主导地位。

1.SQL的执行频率

为什么要知道SQL的执行频率呢？

假如有一个业务系统，一张表数据量很大，但我只往里面insert，不查询，那我就没必要优化。所以我们要知道当前数据库，到底是以CRUD什么为主。以查询为主的，我们才考虑进行优化。

2.慢查询日志

如果我们已经确定了是查询为主了，那哪些SQL语句需要进行优化呢？那我们就需要定位到这些效率比较低SQL语句，从而对它进行优化。

慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

3.profile

show profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数，能看到当前 MySQL 是否支持 profile 操作：SELECT @@have_profiling;

profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：

SET profiling = 1;

查看所有语句的耗时：show profiles;
查看指定query_id的SQL语句各个阶段的耗时：show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况：show profile cpu for query query_id;

4.explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：直接在select语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

EXPLAIN 各字段含义：

id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）
select_type：表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
possible_key：可能应用在这张表上的索引，一个或多个
Key：实际使用的索引，如果为 NULL，则没有使用索引
Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

五.索引使用规则

1.最左前缀法则

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

我们假设创建了一个idx_user_pro_age_sta的索引

所以这里来说，当查询的时候（where. ？？）pro必须存在。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

查pro和sta就中间跳过了age。pro将会走索引，而sta不会走索引。

联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

2.索引失效情况

在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';

字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号

模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

3.覆盖索引和回表查询

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列（select ？），在该索引中已经全部能够找到)，减少select*。

使用explain后，查看sql语句执行流程查看到Extra那一行的信息，发现：

using index condition:查找使用了索引，但是需要回表查询数据
using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

那么具体这个是什么意思呢？我们来看一个例子

我们为这个表建立了聚集索引（id），也为name建立了索引

这一条语句，是根据id值来查询信息，这时候我们直接走聚集索引（主键索引id）即可拿到整行信息。查一次索引即可。

这一条语句，根据name查询，返回的是id和name，这时候我们会走二级索引（辅助索引），根据name查询，即可拿到id和name的值。在这种情况中，我们依然不需要进行回表查询，因为我查询返回的字段，在辅助索引中已经找到了（走一个索引就已经找到了我想要的所有字段了），这就叫覆盖索引。

这一句语句，需要返回的是id，name还有gender，我们还是根据name进行查询，走辅助索引，也找到了Arm这个名字和对应的id值，可是我们还需要gender这个值，在辅助索引中并不能找到，所以这个时候我们就需要回表查询了，再用id值在聚集索引中进行比对，最后拿到整行的内容，也就拿到了gender这个值。

通过上述的例子，我们可以发现，为什么在前面我们在解释聚集索引的时候，我们说尽量不去使用select *，因为select * 返回的是整行数据，这样会导致十分容易进行回表查询，这样你就进行了两次索引的扫描，自然性能就会降低了（除非你建立了一个联合索引，且包含了这张表中的所有字段）。

4.前缀索引

5.单列索引和联合索引

单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。

6.设计原则

前面我们了解到索引使用的原则，以及什么样的情况索引会失效。所以我们在SQL语句编写，优化的时候，就要尽量的必要索引会失效的这些情况。那么在这里需要讲到索引的设计原则，假如在具体的业务中，我们需要对某张表的某些字段创建索引来提升sql的查询效率那我们要针对什么样的表，什么样的字段，建立什么样的索引呢？

索引的设计原则：

1.针对于数据量较大（>100万），且查询比较频繁的表建立索引。
2.针对于常作为查询条件(where)、排序(orderby)、分组(group by)操作的字段建立索引。
3尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。