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

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

2022-05-13 77
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 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 版
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
崇尚学技术的科班人
目录
相关文章
jianz123
|
1月前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
jianz123
16 0
技术蜜糖罐
|
1月前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
技术蜜糖罐
34 0
jianz123
|
1月前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
jianz123
101 1
技术蜜糖罐
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
46 0
威哥爱编程
|
12天前
|
存储 关系型数据库 MySQL
MySQL 8 索引原理详细分析
了解索引的详细原则，不仅有助于优化，能把索引搞清楚的，面试中优势也会很突显。 关于数据库优化的话题，V哥觉得还有很多地方可以聊，如果你有兴趣，欢迎关注一起讨论。
威哥爱编程
19 0
MySQL 8 索引原理详细分析
桃李春风一杯酒
|
14天前
|
缓存 关系型数据库 MySQL
【专栏】提升MySQL性能和高可用性的策略，包括索引优化、查询优化和事务管理
【4月更文挑战第27天】本文探讨了提升MySQL性能和高可用性的策略，包括索引优化、查询优化和事务管理。通过合理使用B-Tree和哈希索引，避免过度索引，以及优化查询语句和利用查询缓存，可以改善性能。事务管理中，应减小事务大小并及时提交，以保持系统效率。主从或双主复制可增强高可用性。综合运用这些方法，并根据实际需求调整，是优化MySQL的关键。
桃李春风一杯酒
24 2
渐暖
|
16天前
|
SQL 关系型数据库 MySQL
【mysql】mysq优化全方面分析
【mysql】mysq优化全方面分析
渐暖
19 0
最后一支迷迭香
|
1月前
|
存储 关系型数据库 MySQL
mysql索引优化，更好的创建和使用索引
mysql索引优化，更好的创建和使用索引
最后一支迷迭香
25 1
Y2000104
|
1月前
|
存储 关系型数据库 MySQL
MySQL索引简介（包含索引优化，索引失效，最左前缀简洁版）
MySQL索引简介（包含索引优化，索引失效，最左前缀简洁版）
Y2000104
33 0
爱吃糖的范同学
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL】慢SQL分析流程
【4月更文挑战第1天】【MySQL】慢SQL分析流程
爱吃糖的范同学
33 1

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    [MySQL]SQL优化之索引的使用规则
    42
  • 2
    【mysql】—— 用户管理
    36
  • 3
    【mysql】—— 视图
    35
  • 4
    【mysql】—— 表的增删改查
    42
  • 5
    【mysql】—— 表的约束
    39
  • 6
    【MYSQL】—— MySQL 在 Centos 7环境安装
    70
  • 7
    MySQL模糊查询二三事
    28
  • 8
    MySQL的JOIN到底是怎么玩的
    109
  • 9
    MySQL怎样删除重复数据，只保留一条？
    40
  • 10
    【怒怼大厂面试官】你先说说知道哪些MySQL的高级特性
    54

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）