mysql查询语句优化

1.背景

2.解读explain

2.1.id详解

1.id相同

2.id不相同

3.id有相同也有不相同

2.2.select_type详解

1.SIMPLE

2.PRIMARY

3.DERIVED

4.SUBQUERY

5.DEPEDENT SUBQUERY

6.UNCACHEABLE SUBQUERY

7.UNION

8.UNION RESULT

2.3.table详解

2.4.type详解

1.system

2.const

3.eq_ref

4.ref

5.full_text了解

6.ref_or_null了解

7.index_merge了解

8.unique_subquery

9.index_subquery

10.range

11.index

12.ALL

2.5.possible_keys

2.6.key

2.7.key_len

2.8.ref

2.9.rows

2.10.Extra

1.Using filesort需要优化

2.Using temporary需要优化

3.Using where

4.Using index

5.Using join buffer

3.常见的索引失效

1.使用like的情况

2.使用Or的情况

3.使用is not null 和 is null的情况

4.使用不等于的情况

5.索引列上使用范围的情况

6.索引列上计算的情况

7.使用覆盖索引的情况

8.复合索引的情况

4.总结

完美!

1.背景

MySQL执行计划（通常被称为EXPLAIN计划）是数据库查询优化器用来决定如何执行SQL查询的详细步骤描述。执行计划包含了关于如何连接表、使用哪些索引（如果有的话）、扫描多少行，以及如何进行排序和临时表操作等信息。这些信息对于理解和优化查询性能非常有用。

2.解读explain

如何使用在执行查询的sql语句前加一个explain关键字,如下图:

解读mysql执行计划,其实就是理解到上图中表头的含义:

id: 查询的标识符。
select_type: 查询的类型（如SIMPLE, PRIMARY, SUBQUERY, DERIVED等）。
table: 输出结果集的表的名称。
type: 访问类型，表示MySQL决定如何连接表。这是性能调优时特别重要的一个字段。
possible_keys: 可能应用的索引。
key: 实际使用的索引。
key_len: 使用的索引的长度。在不使用所有列作为索引的情况下，可以计算得出。
ref: 显示了哪些列或常量被用作索引查找的条件。
rows: 估计为了找到所需的行而必须检查的行数。
Extra: 包含了MySQL解决查询的额外信息，如"Using where"表示使用了WHERE子句来过滤结果，"Using index"表示仅通过索引就可以满足查询需求而无需回表查询等。

请注意，EXPLAIN的输出可能包含更多的列，具体取决于MySQL的版本和配置。

分析EXPLAIN的输出时，你应该特别关注type、possible_keys、key和Extra列，因为这些列提供了关于查询如何执行以及是否进行了有效索引使用的关键信息。如果type列的值不是最优的（如ALL表示全表扫描），或者possible_keys有可用的索引但key列为NULL，那么你可能需要考虑优化你的查询或索引。

记住，EXPLAIN只能告诉你MySQL查询优化器当前的决策，但实际的性能可能还受到其他因素的影响，如硬件、系统负载、数据分布和统计信息等。因此，在调整和优化查询时，除了查看EXPLAIN输出外，还应该考虑进行实际的性能测试。

下面的案例中会用到的表和数据

/*
Navicat MySQL Data TransferSource Server         : centos7_01
Source Server Version : 50562
Source Host           : 192.168.138.154:3308
Source Database       : my_data2Target Server Type    : MYSQL
Target Server Version : 50562
File Encoding         : 65001Date: 2024-07-25 11:50:41
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `ta`
-- ----------------------------
DROP TABLE IF EXISTS `ta`;
CREATE TABLE `ta` (`id` int(10) NOT NULL AUTO_INCREMENT,`name` varchar(128) DEFAULT NULL,`remark` varchar(100) DEFAULT NULL,`price` int(6) DEFAULT NULL COMMENT '价格',PRIMARY KEY (`id`),KEY `idx_remark` (`remark`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of ta
-- ----------------------------
INSERT INTO `ta` VALUES ('1', '张无忌', 'ta_1', '30');
INSERT INTO `ta` VALUES ('2', '2', 'ta_2', '10');
INSERT INTO `ta` VALUES ('3', '赵敏', 'ta_3', '40');
INSERT INTO `ta` VALUES ('4', 'name_2', 'ta_4', '20');-- ----------------------------
-- Table structure for `tb`
-- ----------------------------
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (`id` int(10) NOT NULL AUTO_INCREMENT,`name` varchar(128) DEFAULT NULL,`remark` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name_b` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', 'name_1', 'tb_1');
INSERT INTO `tb` VALUES ('2', 'name_2', 'tb_2');
INSERT INTO `tb` VALUES ('3', 'name_3', 'tb_3');
INSERT INTO `tb` VALUES ('4', '赵敏', 'tb_4');
INSERT INTO `tb` VALUES ('5', 'name_5', 'tb_5');-- ----------------------------
-- Table structure for `tc`
-- ----------------------------
DROP TABLE IF EXISTS `tc`;
CREATE TABLE `tc` (`id` int(10) NOT NULL AUTO_INCREMENT,`remark` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of tc
-- ----------------------------
INSERT INTO `tc` VALUES ('1', 'tc_1');

常用额索引创建,查看和删除操作

#创建索引
CREATE INDEX idx_remark on ta(remark);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_remark ON ta;

2.1.id详解

id表示查询序列,每一个id值表示一次查询,一个sql的查询id越少越好
①id相同，执行顺序由上至下执行
②id 不同，从大到小执行
③id有相同也有不同,id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大,越先执行

下面详细讲解,每一种情况,希望大家都把sql执行一下,加强理解

1.id相同

①id相同，执行顺序由上至下执行

explain select * from ta,tb,tc where ta.id=tb.id and tb.id=tc.id;

2.id不相同

②id 不同，从大到小执行

explain select ta.id from ta where ta.id in (select tb.id from tb where tb.id in ( select tc.id from tc where tc.remark='tc_1'));

3.id有相同也有不相同

③id有相同也有不同,id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大,越先执行;

explain select ta.* from ta,(SELECT * from tb where tb.remark='tb_1') sb where sb.id=ta.id;

2.2.select_type详解

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_type 属性	含义
SIMPLE	简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED	出现在from后的子查询在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询(单个值)
DEPEDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询(多个值)
UNCACHEABLE SUBQUERY	无法使用缓存的子查询(一般不会用到)
UNION	UNION之后的select查询的表标记为union
UNION RESULT	从UNION表获取结果的SELECT

1.SIMPLE

simple解读:

简单的 select 查询,查询中不包含子查询或者 UNION

EXPLAIN select ta.* from ta;

2.PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary

3.DERIVED

出现在from后的子查询在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)MySQL 会递归执行这些子查询, 把结果放在临时表里。

PRIMARY,derived解读

EXPLAIN select a.* from (SELECT ta.remark from ta) a;

4.SUBQUERY

在SELECT或WHERE列表中包含了子查询(单个值)

subquery解读:

explain select ta.id from ta where ta.id = ( SELECT tb.id from tb where tb.id=1 );

5.DEPEDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询(多个值)

dependentsubquery解读:

#dependentsubquery 都是where后面的条件，subquery是单个值(条件是等号)，dependentsubquery是一组值(条件是in)。
explain select ta.id from ta where ta.id in ( SELECT tb.id from tb where tb.id =1 );

6.UNCACHEABLE SUBQUERY

无法使用缓存的子查询(一般不会用到)

UNCACHEABLE SUBQUERY解读:

EXPLAIN SELECT * from ta where id=(SELECT id from tb where tb.id=@@sort_buffer_size);

7.UNION

UNION之后的select查询的表标记为union

8.UNION RESULT

从UNION表获取结果的SELECT

UNION,UNION RESULT解读:

EXPLAIN select ta.id,ta.remark from ta UNION ALL select tb.id,tb.remark from tb;

2.3.table详解

这个比较好理解,就是指查询的那张表

2.4.type详解

访问类型,这是性能调优特别重要的一个字段.

序号	类别	说明
1	system	表中只有一行数据（等于const）。这是一个非常快的连接类型，因为只需要读取一行数据。
2	const	表中的唯一索引或主键被用于检索单条记录。只会返回一行数据，因为它基于唯一索引或主键。
3	eq_ref	所有的部分索引键都被唯一匹配。这通常发生在外连接或自然连接中，并且索引列被用作连接条件。对于每个索引键值的组合，只会返回一行数据。
4	ref	返回匹配某个非唯一索引值的所有行。可能会返回多行数据，因为索引不是唯一的。通常比全表扫描要快，但比eq_ref慢
5	fulltext	使用全文索引进行搜索。只有MyISAM和InnoDB存储引擎支持全文索引。确保你的 MySQL 版本支持全文索引： MyISAM 存储引擎从 MySQL 5.0.5 开始支持全文索引。 InnoDB 存储引擎从 MySQL 5.6.4 开始支持全文索引。
6	ref_or_null	与ref类似，但是还搜索了包含NULL值的列。这主要发生在外连接中，并且用于搜索可能为NULL的列。
7	index_merge	表示使用了索引合并优化方法。这种情况下，MySQL会扫描多个索引，然后将它们合并以找出与WHERE子句匹配的行。这通常比全表扫描要快，但可能会比单个索引扫描慢。
8	unique_subquery	用于IN子查询，该子查询返回唯一的结果集。 MySQL会对IN子查询的结果集进行唯一索引扫描。
9	index_subquery	与unique_subquery类似，但是子查询返回的结果集可能包含重复值。 MySQL会对IN子查询的结果集进行索引扫描。
10	range	只检索给定范围内的行，通常使用BETWEEN、<、>等操作符。可以使用索引来快速找到匹配的行。
11	index	全索引扫描，但MySQL只会扫描索引树。这通常比全表扫描要快，因为索引通常比表小得多。但是，如果查询需要访问表中的实际数据，则可能需要额外的磁盘I/O操作。
12	ALL	全表扫描，MySQL会扫描整个表来找到匹配的行。这是最慢的连接类型，因为它需要读取和检查表中的每一行。如果可能的话，应该尽量避免全表扫描。

1.system

表中只有一行数据（等于const）。这是一个非常快的连接类型，因为只需要读取一行数据。

EXPLAIN SELECT * from (SELECT * from ta WHERE ta.id=1) s;

2.const

表中的唯一索引或主键被用于检索单条记录。只会返回一行数据，因为它基于唯一索引或主键。

EXPLAIN SELECT * from (SELECT * from ta WHERE ta.id=1) s;

3.eq_ref

所有的部分索引键都被唯一匹配。这通常发生在外连接或自然连接中，并且索引列被用作连接条件。对于每个索引键值的组合，只会返回一行数据。

EXPLAIN SELECT * FROM ta left JOIN tb on (ta.id=tb.id);

4.ref

返回匹配某个非唯一索引值的所有行。可能会返回多行数据，因为索引不是唯一的。

通常比全表扫描要快，但比eq_ref慢

#添加列name,并设置为索引
CREATE INDEX idx_name_a on ta(name);
CREATE INDEX idx_name_b on tb(name);
EXPLAIN SELECT * FROM ta inner JOIN tb on (ta.name=tb.name);

5.full_text了解

使用全文索引进行搜索。

只有MyISAM和InnoDB存储引擎支持全文索引。

确保你的 MySQL 版本支持全文索引：

MyISAM 存储引擎从 MySQL 5.0.5 开始支持全文索引。

InnoDB 存储引擎从 MySQL 5.6.4 开始支持全文索引。

# 查看mysql版本号
select VERSION();
# 查看存储引擎
SHOW ENGINES;

一般用得很少,当前版本不支持...需要了解的童鞋自行研究!

6.ref_or_null了解

出现的情况很少,我在实际生产中也很少遇到,下面是文心一言的解读:

在MySQL的查询执行计划中，ref_or_null 是一个不太常见的访问类型（type），但它确实存在，并且表示了一种特定的索引查找方式。

ref_or_null 通常用于表示查询中既使用了某个索引的引用（ref）查找，也包含了处理NULL值的情况。这通常发生在查询条件中使用了 IS NULL 或者 IS NOT NULL 与某个索引列结合时。

举个例子，假设我们有一个名为 users 的表，并且这个表有一个名为 email 的列，该列上有一个索引。如果我们执行如下的查询：
sql复制代码

SELECT * FROM users WHERE email = 'someuser@example.com' OR email IS NULL;

对于 email = 'someuser@example.com' 部分，MySQL可能会使用 ref 类型的索引查找。但是，对于 email IS NULL 部分，因为索引通常不包含NULL值（除非是使用特殊的索引类型，如MySQL 8.0+中的NULLS FIRST或NULLS LAST），所以MySQL可能需要进行表扫描或使用其他策略来处理这部分查询。

但是，在某些情况下，优化器可能会选择一种策略，使得 ref 和 IS NULL 的情况都可以高效处理，这时你可能会在 EXPLAIN 的输出中看到 ref_or_null。

然而，要注意的是，ref_or_null 并不是所有MySQL版本或所有存储引擎都支持的访问类型。在某些情况下，你可能会看到 ALL（表扫描）或其他访问类型，即使查询中包含了可以使用索引的 IS NULL 条件。

为了获得最佳的查询性能，你应该始终确保你的查询和索引设计都是针对你的具体需求进行优化的。这通常包括避免在索引列上使用函数或计算，确保查询条件可以充分利用索引，以及定期分析和优化你的数据库和查询。

7.index_merge了解

在实际中出现的情况很少,下面是文心一言的解读

在MySQL的查询执行计划中，index_merge是一种访问类型，它表示MySQL优化器决定使用索引合并优化方法来执行查询。当查询条件能够使用多个索引，并且优化器认为合并这些索引的扫描比单独使用它们更有效率时，就会采用index_merge方法。

index_merge的几种类型

intersection（交集）：使用多个索引进行检索，并返回这些索引检索结果的交集。
union（并集）：使用多个索引进行检索，并返回这些索引检索结果的并集。
sort_union（排序并集）：与union类似，但结果会按照某个列进行排序。

示例

假设我们有一个名为employees的表，它有以下几个字段和索引：

id（主键）
first_name（有索引）
last_name（有索引）
age（有索引）

如果我们想查询first_name以'John'开头且age大于30的所有员工，查询可能如下：
sql复制代码

SELECT * FROM employees WHERE first_name LIKE 'John%' AND age > 30;

如果MySQL优化器认为使用first_name和age的索引合并比全表扫描或其他单个索引更高效，那么它可能会选择index_merge访问类型。

解读执行计划

使用EXPLAIN命令查看执行计划：
sql复制代码

EXPLAIN SELECT * FROM employees WHERE first_name LIKE 'John%' AND age > 30;

在返回的结果中，你可能会看到类似以下的输出（具体输出可能会因MySQL版本和数据表结构的不同而有所差异）：
markdown复制代码

+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | index_merge | first_name,age | first_name,age | ... | NULL | ... | Using intersect(first_name,age)|
+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+

这里，type列的值是index_merge，possible_keys列出了可能使用的索引，而key列则显示了实际被选中的索引。Extra列中的Using intersect(first_name,age)表明优化器决定使用交集策略合并first_name和age的索引扫描结果。

注意事项

索引合并并不总是最佳选择。在某些情况下，添加复合索引（即包含多个列的索引）可能更为有效。
索引合并可能会增加查询的复杂性，并可能消耗更多的CPU和内存资源。
在评估查询性能时，应始终考虑查询的实际执行时间和资源消耗。

总结

index_merge是MySQL优化器提供的一种优化手段，它允许MySQL在查询时合并多个索引的检索结果。然而，它并不总是最佳选择，需要根据具体情况和性能测试结果来判断是否应该使用它。

8.unique_subquery

用于IN子查询，该子查询返回唯一的结果集。MySQL会对IN子查询的结果集进行唯一索引扫描。

EXPLAIN SELECT * from ta where ta.id IN (SELECT tb.id from tb);

9.index_subquery

与unique_subquery类似，但是子查询返回的结果集可能包含重复值。MySQL会对IN子查询的结果集进行索引扫描。

EXPLAIN SELECT * from ta where ta.name in (SELECT tb.name from tb);

10.range

只检索给定范围内的行，通常使用BETWEEN、<、>等操作符。可以使用索引来快速找到匹配的行。

EXPLAIN SELECT * from ta where ta.id<10;

11.index

全索引扫描，但MySQL只会扫描索引树。这通常比全表扫描要快，因为索引通常比表小得多。

但是，如果查询需要访问表中的实际数据，则可能需要额外的磁盘I/O操作。

EXPLAIN SELECT id from ta;

12.ALL

全表扫描，MySQL会扫描整个表来找到匹配的行。这是最慢的连接类型，因为它需要读取和检查表中的每一行。如果可能的话，应该尽量避免全表扫描。

EXPLAIN SELECT * from ta;

2.5.possible_keys

可用索引,不一定实际被使用

2.6.key

实际被使用的索引

2.7.key_len

表示索引中使用的字节数,越长表示使用索引越充分。

2.8.ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

2.9.rows

执行时必须检查的行数,越少越好

2.10.Extra

其他额外信息,很重要的一个参数,

这里讲几个重要,常见的,其他的大家可以百度一下

1.Using filesort需要优化

没有使用索引进行排序,出现文件排序,可以优化

EXPLAIN SELECT ta.name from ta order by ta.name

优化方式:对name字段添加索引.

#创建索引
CREATE INDEX idx_name_a on ta(name);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_name_a ON ta;

2.Using temporary需要优化

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

EXPLAIN SELECT ta.name,count(1) from ta group by ta.name

优化方式:对name字段添加索引.

3.Using where

#Using where 使用where进行过滤
EXPLAIN SELECT * from ta where ta.name='张三'

4.Using index

Using index 使用了覆盖索引,避免访问了表的数据行
EXPLAIN SELECT ta.id from ta where ta.id>1;

5.Using join buffer

百度问心一言解读如下:

在MySQL的执行计划（Explain）中，Extra列中的Using join buffer信息表示MySQL在执行连接（JOIN）操作时使用了连接缓冲区（join buffer）。这通常发生在非索引连接（如嵌套循环连接）中，当MySQL无法通过索引直接找到连接键的匹配行时，就会使用连接缓冲区来临时存储中间结果。

具体来说，当MySQL执行一个连接查询时，它可能会尝试不同的连接策略，如嵌套循环连接（Nested Loop Join）、块嵌套循环连接（Block Nested Loop Join）、索引合并连接（Index Merge Join）等。如果MySQL选择了嵌套循环连接，并且连接条件中的列没有被索引，或者索引的使用效率不高，MySQL就会使用连接缓冲区来辅助完成连接操作。

连接缓冲区的工作原理大致如下：

读取一行：MySQL首先从第一个表（通常称为外部表）中读取一行数据。

查找匹配行：然后，MySQL尝试在第二个表（内部表）中找到与第一行数据相匹配的行。如果连接条件中的列没有索引，或者索引的使用效率不高，MySQL就会将内部表的相关行（或行的键）存储到连接缓冲区中。

比较和输出：对于外部表的每一行，MySQL都会重复步骤2，将内部表中匹配的行从连接缓冲区中取出，进行比较，并输出满足连接条件的行。

重复处理：这个过程会一直重复，直到外部表的所有行都被处理完毕。

需要注意的是，连接缓冲区的大小是有限的，由系统变量join_buffer_size控制。如果连接操作需要处理的数据量超过了连接缓冲区的大小，MySQL可能需要多次从磁盘读取内部表的数据，这会影响查询的性能。

此外，Using join buffer并不总是表示查询性能低下。在某些情况下，即使使用了连接缓冲区，查询仍然可以高效地执行，特别是当连接操作涉及的数据量不大时。然而，如果Extra列中同时出现了Using join buffer和Using where; Using filesort等其他提示，那么可能就需要对查询进行优化了。

优化此类查询的方法可能包括：

确保连接条件中的列被索引，并且索引是有效的。
考虑调整查询逻辑，使用更高效的连接策略。
如果可能，尝试重写查询以减少连接操作的需要。
调整join_buffer_size的大小，以适应更大的连接操作。但是，需要注意的是，增加join_buffer_size可能会增加内存的使用量，从而影响其他查询的性能。因此，这个调整应该基于实际的查询需求和系统资源来进行。

重点:在数据量不大的情况下,Using join buffer可以提升效率,但如果数据多,缓存不够时,需要读取磁盘,这时效率就很低
如果Extra列中同时出现了Using join buffer和Using where; Using filesort等其他提示，那么可能就需要对查询进行优化了。

EXPLAIN SELECT * from ta INNER JOIN tb on (ta.id=tb.id);

3.常见的索引失效

总结:

复合索引全部匹配效率最好,条件中只有部分字段在复合索引中,从左到右进行匹配有效;

复合索引中查询条件范围后的索引会失效;

条件中有计算时索引失效;

使用like,or,is not null容易导致索引失效;

1.使用like的情况

# 假设 remark是索引字段
#ta.remark like '无%' 索引有效
#ta.remark like '%无' 索引无效
#ta.remark like '%无%' 索引无效
EXPLAIN SELECT * from ta where ta.remark like '无%';

2.使用Or的情况

使用or或者in会导致索引失效,但是并不表示不能使用or或者in

#创建索引
CREATE INDEX idx_remark on ta(remark);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_remark ON ta;

--------------------------
#索引有效
EXPLAIN SELECT * from ta where ta.id=2;
#索引有效
EXPLAIN SELECT * from ta where ta.remark='ta_1';
#索引失效
EXPLAIN SELECT * from ta where ta.remark='ta_1' or ta.remark='ta_2';
#索引失效
EXPLAIN SELECT * from ta where ta.remark in('ta_1','ta_2');
#索引失效
EXPLAIN SELECT * from ta where ta.remark='ta_1' or ta.id=2;

#使用or导致索引失效,可以使用union all 或者 union 代替,但是...但是...这样虽然索引有效了,查询效率会更低.
EXPLAIN SELECT * from ta where ta.remark='ta_1'
UNION SELECT * from ta where ta.remark='ta_2';

3.使用is not null 和 is null的情况

#is not null 用不到索引，is null 可以用到索引
#索引有效
EXPLAIN SELECT * from ta where ta.remark is null;

#索引失效
EXPLAIN SELECT * from ta where ta.remark is not null;

4.使用不等于的情况

#mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。

EXPLAIN SELECT * from ta where ta.remark!='ta_1';

5.索引列上使用范围的情况

#复合索引中,范围查询会导致,后面的索引失效

# 增加字段价格,并创建复合索引

#创建索引
CREATE INDEX idx_name_price_remark on ta(name,price,remark);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_remark ON ta;

#复合索引全部有效
EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.price=30 and ta.remark='ta_1';
#复合索引部分有效
EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.price>=30 and ta.remark='ta_1';

6.索引列上计算的情况

在价格上创建索引

#创建索引
CREATE INDEX idx_price on ta(price);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_price ON ta;

# 索引效果好
EXPLAIN SELECT * from ta where ta.price=30;
# 索引效果差
EXPLAIN SELECT * from ta where CONCAT(ta.price,'元')='30元';

总结:在等号左边不要使用计算,函数等

7.使用覆盖索引的情况

查询的列都是索引列,

因此如果价格,和id都是索引,而查询不需要其他字段,那么不要使用 * 查询

这里关于select * from 表,多说几句,有几个缺陷

1.无法使用覆盖索引;

2.读取过多不需要的字段,磁盘IO高;

3.在联表查询时,如果其中使用了*,原本测试正常的代码,如果后期数据库表增加字段,且字段与联表的字段相同会导致查询字段模糊报错;

# 用不到索引
EXPLAIN SELECT * from ta ;

# 使用覆盖索引
EXPLAIN SELECT ta.id,ta.price from ta ;

8.复合索引的情况

#创建索引
CREATE INDEX idx_name_price_remark on ta(name,price,remark);
#查看索引
SHOW INDEX FROM ta;
#删除索引
DROP INDEX idx_name_price_remark ON ta;

# 复合索引命中 name
EXPLAIN SELECT * from ta where ta.name='张无忌';

# 复合索引命中 name,price
EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.price=30;

# 复合索引命中 name,price,remark 全命中
EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.price=30 and ta.remark='ta_1';

重要说明:

1.复合索引全覆盖索引效率最高

2.使用复合索引时与顺序无关

EXPLAIN SELECT * from ta where ta.remark='ta_1' and ta.name='张无忌' and ta.price=30;
索引全部有效

3.复合索引如果中间少一个,后面的索引无效

如:EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.remark='ta_1';

只有name使用到索引,remark使用不到索引

4.复合索引如果其中使用了范围查询,后面的索引会失效

如:EXPLAIN SELECT * from ta where ta.name='张无忌' and ta.price>=30 and ta.remark='ta_1';
name索引有效,remark索引无效

4.总结

sql语句执行计划虽然是最常见的也是最基础的sql调优问题排查,但是不同的版本,甚至是相同的版本相同的sql语句有时候sql的执行计划也是不一样的.

	`+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+`
	`\| id \| select_type \| table \| partitions \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|`
	`+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+`
	`\| 1 \| SIMPLE \| employees \| NULL \| index_merge \| first_name,age \| first_name,age \| ... \| NULL \| ... \| Using intersect(first_name,age)\|`
	`+----+-------------+---------+------------+------+---------------+--------+---------+------+------+----------+-----------------------------+`