索引优化与查询优化

已于 2022-11-29 22:52:46 修改
阅读量311 收藏 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优化方法。
G_amazing
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQL索引优化与查询优化
06-21
课程总共分为三个章节： 第一章节主要对mysql常用的索引结构进行讲解 第二章将会从手把手带你演示sql索引优化的一些策略以及注意点 第三章将会演示工作中经常用到的sql查询优化知识点
索引失效案例
一个菜鸡的博客
10-03 1138
1.全值匹配我最爱，最左前缀要遵守。 2.带头大哥不能死，中间兄弟不能段。 3.索引列上少计算，范围之后全失效。 4.like 百分写最右，覆盖索引不写*。 5.不等空值还有or,索引失效要少用。 6.字符串引号不可丢，SQL高级也不难。
mysql 单表使用索引及常见的索引失效的情况总结
LCreator的博客
02-23 1042
单表使用索引及常见的索引失效的情况 目录 单表使用索引及常见的索引失效的情况 1. 全值匹配我最爱 1.1 有以下SQL语句 1.2 建立索引 2. 最佳左前缀法则 3. 不要在索引列上做任何操作 3.1 在查询列上使用了函数 3.2 在查询列上做了转换 4. 索引列上有范围查询时，范围条件右边的列将失效 5. 使用不等于(!= 或者<>)的时候索引失效 6. is not null 不能使用索引，is null可以使用索引 7. like以通配符%或_开头索引失效
Mysql高级部分系列（三）
prefect_start的博客
04-18 1664
1、索引优化与查询优化 都有哪些维度可以进行数据库调优?简言之: 索引失效、没有充分利用到索引――索引建立 关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化 服务器调优及各个参数设置(缓冲、线程数等)—―调整my.cnf 数据过多――分库分表 关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。 物理查询优化是通过索
Mysql高级调优篇——第四章：Sql实战调优场景剖析（下）
qq_31821733的博客
11-01 5558
上一章我们讲述了索引调优实战在Join的过程，那么本章重点阐述索引失效的场景及原因剖析！ 1、索引失效场景 老规矩先导入一些表作为数据使用，表的所有定义在这个链接中： Mysql高级调优篇表补充——建表SQL_风清扬逍遥子的博客-CSDN博客⭐️tbl_emp⭐️CREATE TABLE `tbl_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`deptId` ...
尚硅谷周阳老师 Mysql 高级优化规则总结
@chenhui的博客
01-21 692
尚硅谷周阳老师Mysql索引优化口诀 周阳，yyds 全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； LIKE百分写最右，覆盖索引不写星； 不等空值还有 or，索引失效要少用； ...
第10章_索引优化与查询优化.pdf
09-05
第10章_索引优化与查询优化.pdf
MySQL索引原理以及查询优化
02-24
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。索引的目的在于提高查询效率，与...
MySQL查询优化–索引优化
12-14
废话就不多说了，先建一张表 CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,#文章id `author_id` INT (10) UNSIGNED NOT NULL, #作者id `category_id` INT(10)...
如何更好的利用索引来优化查询？索引优化；mysql索引
qq_52886103的博客
06-18 132
为什么要建立索引？如何分析查询语句，查找慢sql？如何建立索引？
mysql-实用-索引失效和优化原则
07-23
mysql-实用-索引失效和优化原则
重新了解数据库——索引
仙女的博客
04-08 182
撒子是索引？ 索引（Index）是帮助MySQL高效获取数据的数据结构，索引可以大大提高MySQL的检索速度。 索引就像是我们看书一样，不使用索引我们需要看某个知识点要一页一页的翻，而使用了索引就像是我们使用书本的目录来查询需要的内容，可以大大的提升效率。 索引的分类 主键索引（PRIMARY KEY） 唯一的标识，主键不可重复，只能有一个列作为主键 唯一索引（UNIQUE KEY）...
8个尽量优化你的SQL
zhangxiaox1的专栏
09-20 279
查询语句的优化是SQL效率优化的一个方式，做到以下8个方面优化你的SQL。 1、在表中建立索引，优先考虑where、group by使用到的字段。 2、尽量避免使用select *，返回无用的字段会降低查询效率。 Select * from user 优化方式：使用具体的字段代替*，只返回使用到的字段。 3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。 SELECT * FROM t WHERE id IN (2,3) SELECT * FROM t1 WHERE usern
MySQL索引优化（超详细）
weixin_55816781的博客
05-10 5878
MySQL索引优化，涉及到索引介绍，性能分析，查询优化，慢查询日志。
sql中where1=1 和 where1=0的区别
教官的博客
09-07 1837
1，where1=1 1）where 1=1 是为True的语句，语句语法正确，能够被正确执行，它的作用相当于：”select * from table”，即返回表中所有数据。 2）在多条件查询中，减少if判断 只是为了满足多条件查询页面中不确定的各种因素而采用的一种构造一条正确能运行的动态SQL语句的一种方法。 有where1=1 <select id="listTalentScreen"...
第10章_索引优化与查询优化
分享思想，留下痕迹。
04-01 797
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。# 删除之前的索引。
【MySQL 第10章_索引优化与查询优化】
cl24的博客
09-28 294
MySQL
数据库索引优化与查询优化——醍醐灌顶
最新发布
日常记录
07-27 1491
哪些维度可以进行数据库调优关于数据库调优的知识点非常分散。不同的 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成和两大块。
elasticSearch 索引优化 查询优化
04-26
好的，关于elasticSearch索引优化和查询优化，我可以给您一些建议。对于索引优化，您可以考虑以下几点： 1. 确定正确的数据类型：使用正确的数据类型可以减小索引的大小，提高查询速度。 2. 选择正确的字段：精简...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值