索引优化与查询优化

已于 2022-11-29 22:52:46 修改
阅读量394 收藏 2
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
于 2022-11-29 01:24:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/G_amazing/article/details/128088182
版权

MySQL中提高性能最有效的方式是对数据表设计合理的索引。索引提供了访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。但是，用不用的上索引是优化器说了算，查询开销怎么小怎么来。所以SQL语句是否使用索引？使用那个索引？跟数据库版本、数据量、数据选择度都有关系。

一、索引失效案例

1、全值匹配我最爱

CREATE INDEX idx_userid ON user (userId);
CREATE INDEX idx_userid_age ON user (userId,age);
CREATE INDEX idx_userid_age_name ON user (userId,age,name);

SELECT * FROM user WHERE userId = 2 AND age = 30 AND name = 'abc';

如上所示：

当执行查询语句时，会选择 idx_userid_age_name 这个索引使用，因为查询条件的列在索引中都存在，其他索引则会失效。所以，给插叙条件中的列创建联合索引可以提升查询效率。

2、最佳左前缀原则

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);

EXPLAIN SELECT * FROM student WHERE age = 14 AND name = 'mkmFqP';
EXPLAIN SELECT * FROM student WHERE classId = 854 AND name = 'mkmFqP';
EXPLAIN SELECT * FROM student WHERE classId = 854 AND age = 14  AND name = 'mkmFqP';

如上所示：

创建联合索引的字段顺序很重要，只有当查询条件中包含联合索引的第一个字段时，联合索引才会生效。

查询语句说明：

  • 第一个查询：联合索引是以age字段开头的，where条件中也包含age字段，而我们创建的三个索引都是以age开头的，所以三个索引都能使用。但是where条件中还包含name字段，三个索引中没有一个索引中的age和name是连着的，因此如果使用以上三个索引，只能使用索引的一部分，即age字段的部分。至于具体使用那个索引，会根据实际表中的数据量，查询结果等因素来决定，查询器会根据消耗成本来选择具体使用那个索引。
  • 第二个查询：where条件没有包含age字段，所以没有索引可以使用。
  • 第三个查询：idx_age_classid_name 索引中的所有列都包含在where条件中，所以会使用 idx_age_classid_name 这个索引。

注意：最佳左前缀原则中的索引顺序是按照创建索引时的顺序来的，和where条件中的字段顺序没有关系。例如第三个查询语句，虽然使用上了 idx_age_classid_name 这个索引，但是where条件中字段的顺序和 idx_age_classid_name 索引中的顺序不一至，这是因为在真正执行这个SQL语句的时候，查询优化器会自动调整where条件中字段的顺序，使其按照索引的顺序来。

3、计算、函数、类型转换（自动或手动）导致索引失效

#计算
CREATE INDEX idx_stuno ON student(stuno);
EXPLAIN SELECT * FROM student WHERE stuno + 1 = 900001; #失效
EXPLAIN SELECT * FROM student WHERE stuno = 900000;     #有效

#函数
CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT * FROM student WHERE LEFT(name,3) = 'abc'; #失效
EXPLAIN SELECT * FROM student WHERE name LIKE 'abc%';     #有效

#类型转换
EXPLAIN SELECT * FROM student WHERE name = 123;   #失效
EXPLAIN SELECT * FROM student WHERE name = '123'; #有效

查询语句说明：

  • 计算：当where条件的字段有计算时，索引失效
  • 函数：当where条件的字段有函数时，索引失效
  • 类型转换：当where条件的字段有类型转换时，索引失效。例如最有一个查询语句，name字段是varchar类型，但是却让他和数值型的123对比，会进行类型转换，所以索引失效。

4、范围条件右边的列索引失效

CREATE INDEX idx_age_classid_name ON student(age,classId,name);
EXPLAIN SELECT * FROM student WHERE age=30 AND classId > 20 AND name = 'abc'; #部分失效
EXPLAIN SELECT * FROM student WHERE age=30 AND name = 'abc' AND classId > 20; #部分失效

如图所示：这两个where条件中classId是范围查询，根据规则，只能使用 idx_age_classid_name 索引中的age和classid这一部分的索引。要想使三个字段都使用上查询，可以建立如下索引：

CREATE INDEX idx_age_name_classid ON student(age,name,classId);

注意：当where条件有范围查询时，创建索引的时候把用到范围查询的列放在最后效果最好。

常见的范围包括：< 、<= 、 > 、 >= 、 between 等

5、不等于（!= 或者 <>）索引失效

CREATE INDEX idx_name ON student(name);
EXPLAIN SELECT * FROM student WHERE name <> 'abc'; #索引失效
EXPLAIN SELECT * FROM student WHERE name != 'abc'; #索引失效

6、is null可以使用索引，is not null无法使用索引

CREATE INDEX idx_age ON student(age);
EXPLAIN SELECT * FROM student WHERE age IS NULL;     #有效
EXPLAIN SELECT * FROM student WHERE age IS NOT NULL; #失效

7、like以通配符%开头索引失效

CREATE INDEX idx_name ON student(name);
EXPLAIN SELECT * FROM student WHERE name LIKE 'abc%';  #有效
EXPLAIN SELECT * FROM student WHERE name LIKE '%abc%'; #无效

8、OR 前后存在非索引的列，索引失效

CREATE INDEX idx_age ON student(age);
EXPLAIN SELECT * FROM student WHERE age = 10 OR classId = 100; #失效

如上所示：where 条件中 OR 前后只有age有索引，所以索引会失效。如果想要生效，必须创建classId的索引，才会生效，如下所示：

CREATE INDEX idx_classId ON student(classId);

9、小结

  • 若只能创建单列索引，尽量选择对当前查询过滤性更好的列作为索引。
  • 创建组合索引时，查询过滤性好的字段的位置越靠前越好。
  • 创建组合索引时，尽可能多的加上where条件中的字段。
  • 创建组合索引时，如果出现范围查询，尽可能把这个字段放在最后。

二、关联查询优化

  1. 保证被驱动表的JOIN字段已经创建了索引
  2. 需要JOIN 的字段，数据类型保持绝对一致
  3. LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表，减少外层循环的次数。
  4. INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略
  5. 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  6. 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

三、子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因如下：

1、执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

2、子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

3、对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

结论：

1、在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

2、尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

四、排序优化

在MySQL中，支持两种排序方式，分别为 FileSort 排序和 Index 排序。

  • Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • FileSort 排序则一般再内存中进行排序，占用CPU较多。如果待排序结果较大，会产生临时I/O 到磁盘进行排序，效率较低。

优化建议：

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

举例说明：

1、查询结果不限制，索引失效：

CREATE INDEX idx_age_classid_name ON student(age,classid,name);
EXPLAIN SELECT * FROM student ORDER BY age,classId; #索引失效

如上所示，虽然排序字段都有索引，但是不会使用索引。因为创建的联合索引为二级索引，索引中子节点只会存放 age，classid，name，主键，这四个列的信息。而我们的查询结果是SELECT *，即查询所有数据的信息，这时，即使使用了联合索引，还需要再进行回表操作。查询优化器经过比较，发现不使用索引效果更高，所以就全表扫描，不使用索引。若想要索引生效，改为如下：

EXPLAIN SELECT id,age,classid,name FROM student ORDER BY age,classId;

2、order by时不limit，索引失效

CREATE INDEX idx_age_classid_name ON student(age,classid,name);

EXPLAIN SELECT * FROM student ORDER BY age,classId LIMIT 10; # 生效
EXPLAIN SELECT * FROM student ORDER BY age,classId;          # 失效

3、order by 时规则不一致，索引失效（顺序错、方向错）

CREATE INDEX idx_age_classid_sid ON student(age,classid,stuno);
CREATE INDEX idx_age_classid_name ON student(age,classid,name);

EXPLAIN SELECT * FROM student ORDER BY age DESC, classId ASC LIMIT 10;  #失效
EXPLAIN SELECT * FROM student ORDER BY classId DESC, name ASC LIMIT 10; #失效
EXPLAIN SELECT * FROM student ORDER BY age ASC, classId DESC LIMIT 10;  #失效
EXPLAIN SELECT * FROM student ORDER BY age DESC, classId DESC LIMIT 10; #生效

分析：

  • 第一个失效。创建索引时，没有显示的指定排序（MySQL8.0生效），默认都是升序（ASC），而age为降序，则索引失效
  • 没有遵从最左前缀原则，失效
  • classId为DESC，失效
  • 都是降序的话，可以倒着查询，所以索引生效

4、无过滤，不索引

CREATE INDEX idx_age_classid_sid ON student(age,classid,stuno);
CREATE INDEX idx_age_classid_name ON student(age,classid,name);

EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid;          #生效
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,name;     #生效
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age;          #失效
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10; #生效

分析：

  • 第一、第二个查询语句中where条件都有过滤，所以生效
  • 第三个不符合最左前缀原则，失效
  • 第四个有limit，经过查询优化器分析，生效

5、GROUP BY优化

  • group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照索引建的最佳左前缀法则。
  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。
  • where效率高于having，能写在where限定的条件就不要写在having中了。
  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行 以内，否则SQL会很慢。

五、覆盖索引

当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

简单说就是， 索引列+主键包含 SELECT 到 FROM之间查询的列 。

CREATE INDEX idx_age_classid_name ON student(age,classid,name);
EXPLAIN SELECT id,age,classid,name FROM student ORDER BY age,classId;

好处：

  • 避免Innodb表进行索引的二次查询（回表）
  • 可以把随机IO变成顺序IO加快查询效率

坏处：

  • 索引字段的维护总是有代价的

六、索引条件下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

EXPLAN 中的 Extra显示为 Using index condition 就表示索引下推。

举例：

CREATE INDEX idx_name_fname_addr ON user(name, address, last_name);
SELECT * FROM user WHERE name = 'zhangsan' AND address LIKE '%北京%' AND last_name LIKE '%张%';

如上所示：创建一个联合索引，按照之前的索引失效原则，查询只能用上name的索引，后面不会查询索引，但是，如果where条件都在联合索引中，优化器会全部使用上这些索引，然后再去进行回表操作，减少了I/O次数，提升了效率。这就叫做索引条件下推。

其实，只有二级索引才有可能会索引下推。因为索引下推的目的就是为了尽可能多的过滤范围，减少回表次数，增加效率。聚簇索引中都包含了所有列的值，就不存在回表操作，自然而然的也不需要索引下推。同理覆盖索引也不需要

索引下推的使用条件：

  1. 只能用于二级索引。
  2. explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
  3. 以用于MyISAM和InnnoDB存储引擎。
  4. MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
  5. 当SQL使用覆盖索引时，不支持ICP优化方法。
第10章_索引优化与查询优化
分享思想，留下痕迹。
04-01 873
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。# 删除之前的索引。
第8篇：Milvus性能优化技巧：索引优化与查询优化
最新发布
Gemini的博客
06-11 1543
通过这篇博客，我们详细介绍了Milvus的性能优化技巧，包括索引优化和查询优化。我们讨论了如何根据不同的应用场景（小规模、中等规模、大规模数据集）和向量维度（低维、中等维度、高维）调整索引和查询参数，并提供了具体的调优建议和代码示例。优化Milvus性能的核心理念在于根据具体的需求和应用场景，找到查询速度、精度和资源消耗之间的最佳平衡点。不同的应用场景对性能有不同的要求，因此需要灵活调整参数，以实现最佳性能。
索引与查询性能优化
Victorgcx的博客
02-16 617
MySQL 索引
MySQL之索引查询优化
小白不菜的博客
05-28 346
1. 单表查询优化 注意 使用复合索引的效果会大于使用单个字段索引（但是要注意顺序）。 查询条件时要按照索引中的定义顺序进行匹配。如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描 存储引擎不能使用索引中范围条件右边的列，范围查询的列在定义索引的时候，应该放在最后面。 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。 is not nu
数据库索引 优化查询
qq_35577329的博客
06-21 203
添加索引的几种方法 1.添加PRIMARY KEY（主键索引）： ALTER TABLE table_name ADD PRIMARY KEY ( column ) ‘table_name’ 表名 column 字段 将table_name表的column字段设置为主键 2.添加UNIQUE(唯一索引) ： ALTER TABLE table_name ADD UNIQUE ( column ) 3.添加INDEX(普通索引) ： 写法一：ALTER TABLE table_name ADD INDEX i
mysql索引查询优化_mysql索引优化查询
weixin_36059638的博客
01-28 170
一：mysql的引擎简介。1、ISAM:查找速度快，不支持事物，如果硬盘崩溃则无法恢复。2、MyISAM:是ISAM的扩展，在web中用的很多，读取速度快，，不支持事物。3、InnoDB:InnoDB：支持事物，但是速度比ISAM和MyISAM慢，稳定可靠性高查看当前数据库默认引擎，可以看到本人用的是InnoDB；二：mysql索引简介1、普通索引创建和删除create index index_m...
第10章_索引优化与查询优化.pdf
09-05
在本章中，我们主要探讨的是MySQL数据库中的索引优化与查询优化，这是数据库性能提升的关键环节。首先，为了模拟真实的数据环境，讲师通过一系列步骤创建了两个表——`class`（班级表）和`student`（学生表），并...
MYSQL索引优化与查询优化
06-21
在实际应用中，MySQL的索引优化和查询优化不仅涉及到技术层面，也与数据库设计和业务逻辑密切相关。通过对这些知识的深入理解和熟练运用，开发者可以有效地提升数据库的性能，从而提高整个应用程序的用户体验。通过...
.arch06-索引优化与查询优化.sql
04-05
.arch06-索引优化与查询优化.sql
通过联合索引优化MYSQL慢查询
12-14
通过联合索引优化MYSQL慢查询 一、慢SQL分析 慢SQL如下 SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ...
索引优化和查询优化--MySQL
Littewood的博客
02-28 564
索引优化与查询语句的优化介绍
索引和查询优化系列之三--查询优化
不要10000小时，先从1000小时开始！
03-18 354
查询优化主要包括两部分： 一mysql查询机制 二mysql不同语句的优化方式 mysql的查询机制    1为什么会出现查询性能低下的情况      主要有，查询不需要的记录，多表连接返回全部列，总是用*取出全部列，重复查询相同的数据等造成。    2衡量查询的开销     主要从，响应时间，扫描的行数，返回的行数可以大概估计查询的开销。这三个指标会记录到慢查询日志中，所以检查慢查
MySQL索引及其优化查询
LPoint01的博客
02-24 541
MySQL-索引 索引优势：提高检索效率、降低数据库的IO成本、通过索引对数据进行排序、降低数据排序成本、降低CPU性能消耗 劣势：索引也是一张表、表中保存了主键与索引字段、并指向实体表的记录、索引也占内存空间 索引分类 单值索引 一个索引只包含单个列、一个表有多个单列索引 唯一索引 索引列的值必须唯一、但可以为null 复合索引 一个索引包含多个列 MyISAM和In noDB索引区别 MyISAM 不支持事务、每次查询都是原子性的 支持表级锁、每次操作都是针对整张表
【索引优化与查询优化】
qq_51240148的博客
11-27 923
索引优化与查询优化
MySQL系列-优化之查询索引优化
UFO___的博客
07-22 514
这篇博客我们来谈谈如何正确使用索引以及正确建立索引，当然在开发当中情况比这个复杂多了，还要根据业务实际情况来，这篇博客就只能算是入门了，要想继续进阶的话可以和公司的DBA混个两年，前提是他愿意要带你玩。话不多说，进入正题。 如果不了解explain执行计划可以看我之前的文章MySQL系列-优化之explain执行计划详解 说明：我使用的是mysq5.5，windows10操作系统。可能实验测试...
04-Mysql索引优化和查询优化
liaoqing1999的博客
04-13 448
Mysql索引优化和查询优化 索引建立的规则 表的主键、外键必须有索引； 数据量超过300的表应该有索引； 经常与其他表进行连接的表，在连接字段上应该建立索引； 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 索引应该建在选择性高的字段上； 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： 正确选择复合索引中的主列字段，一般是选择性较好的字段； 复合索引的几个字段是否经常同时以AND
数据库第四章索引与查询优化
zhousiyuan0515的博客
06-13 504
索引：多级索引：数据记录数量庞大的情况下，单级索引效率太低，采用多级索引可以大大提高速度数据库系统中使用最广泛的多级索引；特点：B+树的插入删除不会引起过多的I/O操作；插入之后B+树依然是平衡的，并保持了很好的性能；插入过程：插入实例： B+树用一个链表存储重复的键值对应记录行的RID的值： 效率：使用场景：B+树索引需要三次左右磁盘I/O才能查到数据记录；散列索引只需一次磁盘I/O就可以查到数据记录；散列索引：根据给定索引值，用一种算法将记录分散存储在多个桶中（一般一个桶就是一个数据块，块中内容用一次磁
第21章：索引优化与查询优化
jbkjhji的博客
07-07 220
①覆盖索引是一种数据查询方式，不是索引的类型。索引列+主键包括在select到from中②通过索引值可以直接找到查询字段的值，不需要回表操作就是覆盖索引如:name,age查询张三的年龄:selectname, age查询的字段name和age都在联合索引idx_name_age的索引树中，这样查询就是覆盖索引查询。
标题：第10章 索引优化与查询优化的数据准备及表结构设计
综上所述，《第10章_索引优化与查询优化》是一本关于数据库性能优化方面的专业书籍，内容涵盖了索引优化和查询优化的基本原理和实践经验。通过本书的学习，读者可以掌握数据库性能优化的基本方法和技巧，提高数据库...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值