MySQL优化-03索引

最新推荐文章于 2024-06-15 16:09:53 发布

让你三行代码QAQ

最新推荐文章于 2024-06-15 16:09:53 发布

阅读量395

点赞数 5

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_43676797/article/details/138762932

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

索引

一.索引的作用

一个索引就是一颗B+树，索引让我们可以快速定位和扫描到我们想要的数据，增加查询速度。
在一个select查询语句中最多用到一个二级索引，即使在where条件中使用了多个二级索引。

二.InnoDB存储引擎支持的索引

B+树索引、哈希索引、全文索引，其中最重要的是B+树索引，它是mysql最常用的索引。

三.B+树索引

1.聚簇索引/聚集索引

它是一种主键索引，MySql 会通过主键生成一颗B+树，B+树的叶子节点中存储的是整行数据。
如果没有主键，MySql会通过rowId来生成B+树。

2.辅助索引/二级索引

它是Mysql由索引列生成的一颗B+树，B+树的叶子节点存储内容不包行记录的全部数据，它存储除了包含键值以外，还包含一个书签来存储主键。
每定义一个二级索引就会生成一颗B+树。
如果查询的内容不是索引列也不是主键列，那么会进行回表操作，所谓回表就是通过二级索引找到叶子节点，再拿叶子节点的主键去主键索引中查找内容，比较耗时，因此应该尽量少的避免回表操作。

3.联合索引/复合索引

它是由多列组成的索引，MySql通过联合索引生成一颗B+树，它的叶子节点存储的是作为联合索引的列数据+主键。
B+树遵循最左侧优先排原则，所谓最左侧优先排原则就是B+会根据从左到右的列进行排序。

4.覆盖索引

如果从辅助索引中可以查询到的记录，而不需要查询聚簇索引中的记录，从而避免回表操作。
它不是索引类型的一种。

举个例子：表student中有id,name,age,desc四个列，创建一个name+age的联合索引；

select name from student where age = ‘18’,使用了覆盖索引，可以直接从联合索引中取到name;
select name,desc from student where age = ‘18’,没有使用覆盖索引，因为desc列需要回表操作,从聚簇索引中取数据;

四.自适应Hash索引

决定B+树索引的效率是树的高度，MySql一般B+树的高度一般为3_{4层，那么就需要3}4此的IO操作来查询数据。因此，Mysql提供了Hash索引。
对于热数据来说，使用B+树索引效率慢。
MySql监控会对热数据创建Hash索引，只需要一次操作就可以查询到数据，底层是除法散列表。
它的缺点是无法排序。
MySql5.7开始，自动开启自适应Hash索引。SHOW ENGINE INNODB STATUS 命令查看Hash索引

五.高性能索引创建策略

索引列的类型尽量小（主键）

这个条件只针对主键来讲。
因为二级索引、联合索引都要存储主键列，索引索引越小，占用的空间越小，同样的内存空间存储的数据行数越多，IO操作越少，同时cup执行越快。

索引列离散型高

索引列的离散型越高，索引效率越高，离散型=不重复索引值的/表记录总数。
使用 select count(distinct 列)/count(*) from table可以计算离散型。越接近1越适合做索引。

前缀索引

对于字段比较长的列，比如blob、text、很长的varchar，mysql不支持索引她们的全部长度，可建立前缀索引。
语句：alert table 表名 add key/index (column(x))，x表示前缀长度。
使用select count(distinct left(列名，长度x))/count(*) from table确定x的合适值。
它不支持group by 和 order by，也无法覆盖索引，因为要回表。

后缀索引

mysql不支持后缀索引，可以增加一个列将后缀倒排放到列中，给新增的列加索引。使用场景：邮箱后缀。

以上三个都是对于单个索引的创建策略

多列索引

多列索引的创建一般要根据不同的业务场景来创建。
一般来讲：
将离散型高的列放到索引前面。
根据运行频率最高的查询来调整索引列。
性能优化时，需要相同的列，不同的顺序来创建索引。比如order a,b,c 和order c,b,a来创建两个列相同但顺序不同的索引。

三星索引

满足条件：

索引的选择性：索引的字段能够显著减少查询结果集的大小，即索引字段的唯一值越多，能够过滤掉的数据越多，从而获得一颗星。
排序和分组性能：如果索引的列排序顺序能够匹配查询语句中的ORDER BY或GROUP BY的顺序，这样的索引可以被评定为两颗星。这允许查询直接利用索引完成排序或分组，避免了额外的排序操作。
覆盖索引：如果索引包含了查询语句中所有的列，那么这个索引就是一个覆盖索引，会被评定为三颗星。这意味着查询可以仅通过访问索引获取所有需要的信息，无需访问实际的数据表，从而大大提高查询效率。

综上所述，三星索引是指在选择性、排序和分组性能以及覆盖索引三个方面都达到最优的索引。这种索引能够最小化必须扫描的索引片宽度，避免排序操作，以及避免回表操作，从而极大地提升查询效率。然而，三星索引也有其缺点，比如在小型表和超大型表上的效果可能不佳，因为小型表的全表扫描代价很小，而超大型表的索引维护成本很高。