AI 助理
备案控制台
开发者社区 数据库 文章 正文

索引优化分析下 【MySQL高级篇3】

2022-05-13 91
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 索引优化分析下

1、索引优化

1.1、索引单表优化案例

1. 建表sql语句

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
问题要查询 category_id为1、views大于1且comments最多的article_id。

2. 没建索引之前

  • 查询的sql语句
SELECT * FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1
  • 结果

在这里插入图片描述

  • 性能分析

在这里插入图片描述

很显然 type是ALL（也就是所谓的全文搜索)， Extra里还出现了Using filesort，也是最坏的情况。优化是很有必要的。

3. 建了idx_article_ccv索引之后

  • 建索引的语句
CREATE INDEX idx_article_ccv ON `article` (category_id,comments,views)
  • 建索引后的性能分析

在这里插入图片描述

结果分析：type变为了range，但Extra中的Using filesort 没有被解决。 但是我们建立了索引，为什么没有用呢？ 这是因为我们按照 BTree索引的工作原理， 先排序category_id，如果遇到相同的category_id则在排序comments，如果遇到相同的comments则在排序views。当comments字段在联合索引里处于中间位置时，因为comments > 1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索， 即range类型查询字段后面的索引无效。

4. 建了idx_article_cv索引之后

  • 建索引的sql语句
CREATE INDEX idx_article_cv ON `article` (category_id,views)
  • 建索引后的性能分析

在这里插入图片描述

可以看到 type变为了ref， Extra中Using filesort也消失了，结果非常理想。

1.2、索引两表优化案例

问题： 两表互相关联，那么我们要在关联的字段上建索引，我们是建在左表还是右表上呢？
  • 建表和插入数据的sql语句
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

1. 没建索引之前

  • 查询的sql语句
SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 结果演示

在这里插入图片描述

  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

在这里插入图片描述

type有ALL。

2. 在左表card字段建索引

  • 建索引的sql语句
CREATE INDEX Y ON book(card)
  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

在这里插入图片描述

给左连接的左表加索引后， type 达到 index 级别，但是 扫描的行数没有减少。

3. 在右表card字段建索引

  • 建索引的sql语句
CREATE INDEX X ON class(card)
  • 性能分析的sql语句
EXPLAIN SELECT * FROM book LEFT JOIN class ON book.card = class.card
  • 性能分析结果

在这里插入图片描述

第二行的 type变为了ref， rows也变成了优化比较明显。这是由左连接特性决定的， left join 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。

4. 总结

所以说，如果是左连接的话，就将索引建立在右表上；如果是右连接的话，就将索引建立在左表上。

1.3、索引三表优化案例

在这个案例上使用到的表是在上一个案例上添加了一张表。

  • 建表和插入数据的sql语句
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

1. 没建索引之前

  • 查询数据的sql语句
SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
  • 查询结果演示

在这里插入图片描述

  • 性能分析的sql语句
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;
  • 性能分析结果

在这里插入图片描述

2. 在book和phone表上建了索引之后

  • 性能分析的sql语句
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;
  • 性能分析结果

在这里插入图片描述

后两行的type都是ref且总rows优化很好，效果不错。因为索引最好设置在需要经常查询的字段中。

1.4、 Join语句的优化

  1. 尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。
  2. 优先优化NestedLoop的内层循环。
  3. 保证Join语句中被驱动表上的Join条件字段已经被索引。
  4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

1.5、怎样避免索引失效？

  • 建表和插入数据的sql语句
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());
insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());
insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());

1.5.1、全值匹配我最爱

全值匹配：就是查询字段在索引中可以全部匹配到。

---

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的结果演示

在这里插入图片描述

2.第二条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July' AND age = 23
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age = 23
  • 性能分析的结果演示

在这里插入图片描述

3.第三条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July' AND age = 23 AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age = 23 AND pos = 'dev'
  • 性能分析的结果演示

在这里插入图片描述

1.5.2、最佳左前缀法则

最佳左前缀法则：如果索引了多列，要遵守最佳左前缀法则。指的是擦查询从索引的最左列开始并且不跳过索引中的列。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'
  • 性能分析的结果演示

在这里插入图片描述

很显然 索引失效了，进行了全文搜索。

2.第二条sql语句的性能分析

SELECT * FROM staffs WHERE age = 23 AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev'
  • 性能分析的结果演示

在这里插入图片描述

很显然 索引失效了，进行了全文搜索。

3.第三条sql语句的性能分析

SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev'
  • 性能分析的结果演示

在这里插入图片描述

虽然使用到了索引，但我们可以 通过ref列中可以看出只使用到了一个索引，可见pos的相关索引并未使用到（也就是失效了）。

4. 最佳左前缀法则总结

带头大哥不能死，中间兄弟不能断

1.5.3、不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME='July'
  • 性能分析的结果演示

在这里插入图片描述

2.第二条sql语句的性能分析(在索引列使用了函数)

SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'
  • 性能分析的结果演示

在这里插入图片描述
3.口诀总结

索引列上少计算

1.5.4、存储引擎不能使用范围条件右边的列的索引

  • 存储引擎不能使用范围条件右边的列的索引，但自己这一列的索引有效。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME = 'July' AND age > 22 AND pos='dev'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 22 AND pos='dev'
  • 性能分析的结果演示

在这里插入图片描述

前两个字段得索引都使用到了。第一个字段的索引使用到了是显而易见的，那么第二个字段的索引使用到了是 看type列为range，但是第三个字段的索引并未使用到（也就是失效了）。 因为第二个字段是一个范围条件。

2.多条sql语句的性能分析对比

在这里插入图片描述
3.口诀总结

范围之后全失效

1.5.5、尽量使用覆盖索引

  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
  • 覆盖索引深入理解：如果查询列的字段名和字段个数和索引列的字段名和个数完全一致，或者索引列的字段名和个数真包含查询列的字段名和字段个数。以上两种情况都属于覆盖索引。

1.第一条sql语句的性能分析

SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age = 23 AND pos='dev'
  • 性能分析的sql语句
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age = 23 AND pos='dev'
  • 性能分析的结果演示

在这里插入图片描述

可以看出 Extra列出现了令人满意的Using index。

2.多条sql语句的性能分析对比

在这里插入图片描述

1.5.6、在使用不等于（!= 或 <>）的时候会导致索引失效

  • MySQL在使用不等于（!= 或 <>）的时候无法使用索引会导致全表扫描。

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME != 'July'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'
  • 性能分析的结果演示

在这里插入图片描述

1.5.7、is null、is not null 也会使索引失效

1.第一条sql语句的性能分析

SELECT * FROM staffs WHERE NAME IS NOT NULL
  • 性能分析的sql语句
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL
  • 性能分析的结果演示

在这里插入图片描述

2.多条sql语句的性能分析对比

在这里插入图片描述

1.5.8、like以通配符开头会导致MySQL索引失效

建表和插入数据的sql语句

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');

创建索引的sql语句

CREATE INDEX idx_name_age ON tbl_user(NAME,age)

1.第一条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能分析的结果演示

在这里插入图片描述

2.第二条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE '%aa'
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa'
  • 性能分析的结果演示

在这里插入图片描述

3.第三条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME LIKE 'aa%'
如果是有多个条件查找，那么该条件之后的索引失效。
  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE 'aa%'
  • 性能分析的结果演示

在这里插入图片描述

所以这种写法可以使用到索引。

4.口诀总结

Like百分写最右

5.出现的问题

如果我们不得不使用 %中间是字符%这种类型的通配符的话，我们应该怎样解决其带来的导致索引失效的问题？
  • 对此，我们需要使用覆盖索引。就是我们查询的字段需要被在相应的索引字段中。这样我们可以提高查询速率。

举例说明

  • sql语句
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能说明图示

在这里插入图片描述

type变为了index，所以我们可以通过覆盖索引进行查询优化。
  • sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'
EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%'
  • 性能说明图示

在这里插入图片描述

如果不是覆盖索引，就不能进行优化。

1.5.9、字符串要加单引号

  • 如果你的字段类型是VARCHAR的话，那么相关的字符串不加单引号会导致其字段的索引失效。
  • 因为这样会导致隐式的类型转换，所以会导致全表扫描。

1.5.10、使用or连接会使索引失效

1.第一条sql语句的性能分析

SELECT * FROM tbl_user WHERE NAME = '1aa1' OR NAME = '2aa2'
  • 查询结果

在这里插入图片描述

  • 性能分析的sql语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME = '1aa1' OR NAME = '2aa2'
  • 性能分析的结果演示

在这里插入图片描述

1.5.11、小结

优化口诀总结

  • 全值匹配我最爱，最左前缀要遵守；
  • 带头大哥不能死，中间兄弟不能断；
  • 索引列上少计算，范围之后全失效；
  • Like百分写最右，覆盖索引不写星；
  • 不等空值还有or，索引失效要少用；
  • VAR引号不可丢，SQL高级也不难！

1.6、索引优化面试题

1.6.1、创建数据库

create table test(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));

insert into test(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

mysql> select * from test;
+----+------+------+------+------+------+
| id | c1   | c2   | c3   | c4   | c5   |
+----+------+------+------+------+------+
|  1 | a1   | a2   | a3   | a4   | a5   |
|  2 | b1   | b2   | b3   | b4   | b5   |
|  3 | c1   | c2   | c3   | c4   | c5   |
|  4 | d1   | d2   | d3   | d4   | d5   |
|  5 | e1   | e2   | e3   | e4   | e5   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)

1.6.2、创建索引

# 创建索引
mysql> create index idx_test_c1234 on test(c1,c2,c3,c4);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 查询索引
mysql> show index from test;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY        |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 se

1.6.3、分析以下SQL执行情况

1、基本查询

explain select * from test where c1 = 'a1';
explain select * from test where c1 = 'a1' and c2 = 'a2';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
以上 SQL 均可以使用索引，且使用的索引列逐渐增加。

2、基本查询2

explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
explain select * from test where c1 = 'a1' and c3 = 'a3' and c2 = 'a2' and c4 = 'a4';
explain select * from test where c4 = 'a4' and c3 = 'a3' and c2 = 'a2' and c1 = 'a1';
以上的 SQL 查询均使用四个索引列，原因在于 MySQL 内部优化器进行 SQL 优化，但是建议索引顺序和查询顺序保持一致。

3、范围查询

explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';
以上的 SQL 只能应用索引列 c1、c2、c3因为 c3 处进行范围查询导致索引失效。 
explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';
以上的 SQL 应用索引列 c1、c2、c3，c4 因为 c4 处进行范围查询导致索引失效，但是 c4 是最后一个索引列，虽然是范围查找，但是仍使用了全部的索引列。

4、单值排序查询

explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;
以上 SQL 实际使用了三个索引列 c1、c2、c3，但是仅显示使用了 c1、c2 实际情况是 c1、c2 参与查询操作， a3 参与排序操作。由此引出索引的两大作用，查询和排序。 
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c3;
与上面的 SQL 执行情况一致。 
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c4;
上面的 SQL 会出现 filesort，原因在于复合索引在 a3 处断裂，导致后续的 a4 只能进行文件排序

5、多值排序查询

explain select * from test where c1 = 'a1' and c5 = 'a5' order by c2,c3;
以上 SQL 仅使用到 c1 索引列，实际上 c2、c3 被用作于排序。 
explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
以上的 SQL 会出现 filesort，原因在于索引在 c2 处断裂导致索引失效转而进行文件排序。 
explain select * from test where c1 = 'a1' and c2 = 'a2' order by c2,c3;
应用两个索引列 c1、c2，同时 c2、c3 参与排序 
explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2,c3;
与上一条 SQL 执行情况一致。 
explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2;
结合 MySQL 的特性可知，c2 没有参与排序只有 c3 参与了排序，实际使用索引列 c1、c2 进行查找，c3 进行排序索引没有失效。

原因：MySQL 中当排序的值是个常量时，这个常量不会参与排序。

explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
以上 SQL 会出现 filesort，仅使用索引列 c1 进行查询，在排序使用 c3 时因为索引在 c2 处断裂，导致索引失效转而进行文件排序。

6、分组查询

explain select * from test where c1 = 'a1' and c4 = 'a4' group by c2,c3;
查询时使用索引列 c1 分组时使用索引列 c2、c3 索引没有失效。 
explain select * from test where c1 = 'a1' and c4 = 'a4' group by c3,c2;
以上 SQL 会出现 filesort 和 temporary，仅使用索引列 c1 进行查询，在分组时使用 c3 时因为索引在 c2 处断裂，导致索引失效转而进行文件排序。同时由于对c3 进行分组操作，MySQL 在分组前会先进行排序导致出现临时表。

1.6.4、面试题总结

  • 定值、范围实际上还是排序，通常 order by 给定的是个范围；
  • group by 一般都需要进行排序，会产生临时表；

1.7、优化建议

  • 对于单键索引，尽量选择针对当前 query 过滤性更好的字段；
  • 在选择组合索引时，当前 query 中过滤性最好的字段在索引字段顺序中越靠前越好；
  • 在选择组合索引时，尽量选择能包含当前 query 中 WHERE 子句中更多字段的索引；
  • 尽可能通过分析统计信息和调整 query 写法来达到选择合适索引的目的；
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
存储
关键词：
云数据库 RDS MySQL 版索引优化
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版高级
索引优化云数据库 RDS MySQL 版
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
崇尚学技术的科班人
目录
相关文章
mikechen的互联网架构
|
11天前
|
SQL 关系型数据库 MySQL
大厂面试官：聊下 MySQL 慢查询优化、索引优化？
MySQL慢查询优化、索引优化，是必知必备，大厂面试高频，本文深入详解，建议收藏。关注【mikechen的互联网架构】，10年+BAT架构经验分享。
mikechen的互联网架构
59 0
大厂面试官：聊下 MySQL 慢查询优化、索引优化？
mikechen的互联网架构
|
15天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案，包括索引优化、SQL慢查询优化和数据库表优化，帮助提升数据库性能。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
92 9
MySQL慢查询优化、索引优化、以及表等优化详解
绿水长流*z
|
1月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1646 14
小王老师呀
|
1月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
44 4
糜终
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
121 4
baker_zhuang
|
1月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
222 0
糜终
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
糜终
457 2
蓝易云
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施，可以有效地维护MySQL主从复制环境中数据的一致性和完整性，确保数据库系统的稳定性和可靠性。
蓝易云
115 6
猿java
|
1月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析？
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析，帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划，其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议，帮助你理解和应用`EXPLAIN`，提升数据库查询效率。
猿java
87 0
蓝易云
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施，可以有效地维护MySQL主从复制环境中数据的一致性和完整性，确保数据库系统的稳定性和可靠性。
蓝易云
65 1

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    自动执行主从mysql同步的脚本（能执行，但有缺陷）
  • 4
    MySQL的innodb_flush_log_at_trx_commit配置值的设定
  • 5
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 6
    mysql 数据类型
  • 7
    RDS MySQL 数据库运维简述
  • 8
    mysql合并查询(多张表) union 和 union all
  • 9
    Mysql数据库主从不同步解决案例
  • 10
    MySQL各种日期类型与整型（转）
  • 1
    实时数仓 Hologres产品使用合集之可以直接接入接口吗
    112
  • 2
    实时数仓 Hologres产品使用合集之报错：ORCA failed to produce a plan : PlStmt Translation: Group by key is type of imprecise not supported如何解决
    410
  • 3
    实时数仓 Hologres产品使用合集之在执行SQL语句时，在插入语句后面直接跟上了insert，insert操作就会报错如何解决
    168
  • 4
    实时数仓 Hologres产品使用合集之ologres holostudio为什么不支持max_pt('table')取最大分区这个方法
    93
  • 5
    实时数仓 Hologres产品使用合集之哪里可以后置操作执行一下 analysis 这样的操作
    75
  • 6
    实时数仓 Hologres产品使用合集之建表字符串默认都是bitmap索引，如果字符串的是高基数的，会不会有影响
    123
  • 7
    实时数仓 Hologres产品使用合集之查询数据的入库时间该怎么操作
    120
  • 8
    实时数仓 Hologres产品使用合集之有没有MySQL那样的AUTOINCREMENT字段来实现自增ID功能
    174
  • 9
    实时数仓 Hologres产品使用合集之该创建外部表maxCompute的这个服务器列表如何解决
    111
  • 10
    实时数仓 Hologres产品使用合集之业级问答知识库该如何部署有教程吗
    137

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面