索引细节优化

最新推荐文章于 2024-06-05 16:05:06 发布
最新推荐文章于 2024-06-05 16:05:06 发布
阅读量211 收藏 2
点赞数
分类专栏： 索引 文章标签： mysql 索引 面试 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42445924/article/details/119182345
版权

索引优化二

一. 表结构以及数据说明：

表结构：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100004 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

向表中插入10w条数据，可以利用存储过程/也可以利用代码，建议利用存储过程。
原因如下：

  1. 简单，不需要复杂的逻辑
  2. 不用频繁调用JDBC，浪费时间

存储过程如下：

create procedure insert_emp()
begin
	 declare i int;
	 set i=1;
	 while(i<=100000)DO
	 insert into employees(name,age,position,hire_time) values(CONCAT('ceshi',i),i,'dev',now());
	 set i=i+1;
	 end while;
end

二. 举例实际分析：

1. 联合索引第一个字段用范围不会走索引

1.1 举例说明：

explain select * from employees where name > 'ceshi' and age = 22 and position ='beijing';

在这里插入图片描述
possible_keys 有可能会使用到的索引，key一定会使用到的索引。

结论：从上图中可以看出，possible_keys列显示出了idx_name_age_position，意思是可能会用到这个联合索引，但是在看key这一列，是null，说明实际没有用到idx_name_age_position这个联合索引。

联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，不如就全表扫描

2. 强制走索引

针对1.1的例子，可以强制走索引，下面以强制走索引和全表扫描做个对比：

2.1 举例说明：

explain select * from employees force index(idx_name_age_position) where name > 'ceshi' and age = 22 and position ='beijing';

在这里插入图片描述根据这张截图可以看出使用到了索引，在看下不使用索引和使用索引的查询时间

1）不使用索引

在这里插入图片描述

2）使用索引

在这里插入图片描述
结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高。

3. 覆盖索引优化

3.1 什么是覆盖索引：

先说明下什么是覆盖索引，覆盖索引不是像唯一索引与联合索引那样实际的索引，而是一种概念，在所需要查询的字段中，我们的索引正好符合所有需求，这一行为就叫做覆盖索引。

比如我们要查询 年龄 姓名，我们的联合索引也有 年龄 姓名，这就是覆盖索引，使用覆盖索引的效率很高的，所以在很大程度上推荐使用覆盖索引：

3.2 举例说明：

select name,age from employees where name > 'ceshi'

结果如下：

在这里插入图片描述
在看下explain分析：使用到了覆盖索引
在这里插入图片描述结论：使用覆盖索引在查询过程中会提高查询效率。

4. in和or

in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描。当然也不是绝对，大多数情况下

4.2 举例说明 in：

数据量10w：

 explain select * from employees where name in ('LiLei','HanMeimei','Lucy') and age = 22 and position ='manager';

结果如下：
在这里插入图片描述可以看出使用到了联合索引 idx_name_age_position

数据量10:

explain select * from employees_copy where name in ('LiLei','HanMeimei','Lucy') 
and age = 22 and position ='beijjing';

结果如下：
在这里插入图片描述

可以看出全表扫描，没有走索引

4.3 举例说明 or：

数据量10w：

explain select * from employees where (name = 'LiLei' or name = 'HanMeimei') and age = 22 and position ='beijjing';

结果如下：
在这里插入图片描述可以看出使用到了联合索引 idx_name_age_position

数据量10:

explain select * from employees_copy where (name = 'LiLei' or name = 'HanMeimei' or name = 'Lucy') 
and age = 22 and position ='beijjing';

结果如下：
在这里插入图片描述可以看出全表扫描，没有走索引

5. like

like KK% 一般情况都会走索引

5.2 举例说明 like：

explain select * from employees where name like 'LiLei%' and age = 22 and position ='manager';

结果如下：
在这里插入图片描述可以看出使用到了索引

在看一下使用>查找的sql：

explain select * from employees where name > 'LiLei' and age = 22 and position ='manager';

在这里插入图片描述根据上图可以看到联合索引只有name生效了，>号后面的索引都没有生效，看到这里，大家一定有疑问，为什么使用like后面可以的索引可以生效而>不可以呢？下面分析一下这两条sql。

首先我们要先想到B+树的结构是依次向右递增的，脑海里应该构思出一棵结构树，下面给大家画出来。
在这里插入图片描述可以看出，只有当第一个索引name完全相等时，第二个字段age才是有序的，当name不想等时候，age不一定是有序的，所以不符合b+树 依次向右递增，position也是一样的道理。

那大家会问了，like也是范围查找，age和position也不一定是有序的，为什么索引会生效呢？这就需要给大家引出另外一个知识点了，索引下推。

5.3 什么是索引下推呢（Index Condition Pushdown，ICP）？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则。

SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'

这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索 引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可 以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过 滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

like KK%其实就是用到了索引下推优化。

三. 常见sql深入优化

3.1 Order by与Group by优化

3.1.1 事例1

explain select * from employees where name = 'LiLei' and position ='manager' order by age ;

结果：
在这里插入图片描述分析： 利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

3.1.2 事例2

explain select * from employees where name = 'LiLei' order by position ;

结果：
在这里插入图片描述
分析： 从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了 age，出现了Using filesort。

3.1.3 事例3

explain select * from employees where name = 'LiLei' order by age,position ;

结果：
在这里插入图片描述分析： 查找只用到索引name，age和position用于排序，无Using filesort。

3.1.4 事例4

explain select * from employees where name = 'LiLei' order by position,age ;

结果：
在这里插入图片描述分析： 和事例 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了。

3.1.5 事例5

explain select * from employees where name = 'LiLei' and age = 22 order by position,age ;

结果：
在这里插入图片描述分析： 与事例4对比，在Extra中并未出现Using filesort，因为age为常量，在查询过程中查出来的数据已经是按照age排好序的，在排序中被优化，所以索引未颠倒， 不会出现Using filesort。

3.1.6 事例6

explain select * from employees where name = 'LiLei' order by age,position desc ;

结果：
在这里插入图片描述分析： 虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的 排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

Mysql索引的规则是，有序依次向右递增

四. 优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index：

  1. order by语句使用索引最左前列
  2. 使用where子句与order by子句条件列组合满足索引最左前列

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

摸索前行。
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL索引优化Explain详解
01-21
所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。 -- 实际SQL，查找用户名为Jefabc的员工 select * from emp where...
mysql索引下推
qq_32979219的博客
02-05 7458
索引下推
MySQL索引下推
xp_xpxp的博客
12-13 2518
索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率;
MySQL的索引下推机制
林隐的博客
02-01 1478
其中，UNIQUE关键字表示创建唯一索引，index_name表示索引名称，table_name表示表名，column_name表示要创建索引的列名，ASC和DESC表示升序和降序排序，默认为ASC。维护索引的统计信息：MySQL会根据索引的统计信息来选择最优的查询计划，需要定期更新和维护索引的统计信息，以保证查询性能的稳定和可靠。此外，还有Memory存储引擎，它将数据存储在内存中，具有很高的读写速度，但数据仅在服务重启前有效，适用于临时性的数据存储需求。
MySQL的索引下推
xiangzaixiansheng的博客
03-15 1422
mysql 索引下推
Explain详解与索引优化
03-17
总结，"Explain"是MySQL性能分析的重要工具，通过其输出我们可以了解查询执行的细节，找出性能瓶颈并进行优化。配合合理的索引设计，可以显著提升数据库的查询速度，降低资源消耗。在日常数据库管理中，应定期进行...
网站优化细节之网页信噪比
10-01
### 网站优化细节之网页信噪比详解 #### 一、网页信噪比的概念 网页信噪比（Signal-to-Noise Ratio, SNR）是网站优化领域的一个重要概念，它指的是网页中的文本内容与生成这些文本所必需的HTML标签内容之间的比例...
MYSQL索引建立需要注意以下几点细节
09-10
以下是对创建MySQL索引时需要注意的一些重要细节的深入解析： 1. **建立索引的时机**：索引应当在那些频繁出现在查询条件、排序或分组中的字段上创建。例如，如果一个字段经常用于`SELECT`查询的`WHERE`子句，那么...
MySQL索引下推（Index Condition Pushdown, ICP）优化深入解析
搬砖农民工的博客
12-27 2394
数据库性能优化是现代软件开发中不可或缺的一环。在MySQL中，索引的使用往往是提高查询性能的关键。自5.6版本起，MySQL引入了一个强大的优化器功能，名为索引下推（Index Condition Pushdown, 简称ICP）。通过ICP，我们可以显著提升部分查询的效率，尤其是在使用索引过滤数据时。本文将详细介绍ICP的原理、作用以及应用场景。
MySQL索引下推：原理与实践
热门推荐
码到三十五
01-21 2万+
（Index Condition Pushdown, ICP）是MySQL优化查询的一种方式，其核心思想是将原本在服务层（上层）进行的部分过滤操作下推到存储引擎层（下层）执行，从而减少不必要的数据行检索，提高查询效率。我们先简单了解一下MySQL大概的架构:索引下推优化的核心思想是将WHERE子句中的部分条件直接下推到索引扫描的过程中。这样，在扫描索引时，就可以提前过滤掉不满足条件的索引项，从而减少后续需要访问的数据行数。
MySQL之索引下推
weixin_43442127的博客
01-15 670
MySQL之索引下推
Mysql数据库中什么是索引下推
牛肉胡辣汤
07-26 367
在这个查询中，如果我们只创建了索引idx_name(name)，那么Mysql会通过该索引快速定位到满足条件的行，但是还需要回到主表中获取对应行的name字段的值，这个过程就是回表。但是需要注意的是，索引下推对于某些特定的查询条件可能没有效果，或者在某些情况下可能会降低查询性能。Mysql数据库中的"索引下推"是指在使用索引进行查询时，将部分过滤条件下推至存储引擎层进行过滤，减少回表的次数和数据传输量，从而提高查询性能。这样，在查询价格大于等于5.00的商品的名称时，Mysql会将条件​。
什么是mysql中的索引下推？
weixin_62957013的博客
07-09 255
我们再来看一下mysql采用索引下推又会产生什么情况，如下图，mysql在查找辅助索引时，会根据条件结合索引进行筛选，筛选出符合条件的数据才会回表，这样就减少了回表次数，大大提升了查找效率。我们来看下mysql不使用索引下推的情况，其会先匹配到name中第一个字为"张"的user，然后就开始回表，查出数据后再判断age是否等于10，进行过滤数据，回表四次。name like "张%"
MySQL——索引下推
最新发布
m0_60500421的博客
06-05 1096
MySQL索引下推
MySql 索引下推知识分享
m0_69804655的博客
04-04 759
首先，我们大致回顾下 mysql 的基本架构：MySQL 基本的架构示例图MySQL 服务层主要负责 SQL 语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和查询。索引下推的下推其含义就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。在 MySql 5.6 版本之前没有索引下推这个功能，从 5.6 版本后才加上了这个优化项。我们先简单对比一下使用和未使用 ICP 两种情况下，MySql 的查询过程吧。- 存储引擎读取索引记录；
MySQL计数bug与索引优化解析
在实践中，还应结合EXPLAIN计划分析查询执行的细节，以进一步优化索引策略。 在处理像`count(distinct)`这样的复杂操作时，也可以考虑其他替代方法，如使用GROUP BY结合聚合函数，或者在特定场景下，预计算结果并...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值