MySql性能优化实例分析

发布时间：2021-12-04 14:12:15 作者：iii
来源：亿速云阅读：135

本篇内容介绍了“MySql性能优化实例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

实例1：复合索引的最佳左前缀原则（where ... order by ...）

创建如下表和索引

CREATE TABLE test03(  a1 INT(4) NOT NULL,  a2 INT(4) NOT NULL,  a3 INT(4) NOT NULL,  a4 INT(4) NOT NULL);ALTER TABLE test03 ADD INDEX idx_a1_a2_a3_a4(a1,a2,a3,a4);

看第一种sql语句的执行计划如下：（where 后面的条件与索引顺序一致）

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a3=1 AND a4=1;

第二种sql语句的执行计划如下：（where 后面的条件与索引顺序不一致）

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a4=1 AND a3=1 AND a2=1 AND a1=1;

MySql性能优化实例分析

如上两者的执行计划一模一样，为什么？

因为第二条sql在真正执行前经过了sql优化器的调整，所以与上条保持一致。第一种sql是推荐写法。

再看一个如下sql语句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a4=1 ORDER BY a3;

MySql性能优化实例分析

我们看执行计划的结果：使用了using where，则进行了回表查询，索引失效。以上sql用到了a1,a2两个索引，该两个字段不需要回表查询，因此是using index，而a4因为跨列使用，造成了索引失效，需要回表查询，因此是using where，以上可以通过key_length校验。

再看一个如下sql语句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=1 ORDER BY a3;

MySql性能优化实例分析

我们看到以上sql出现了using filesort(文件内排序，"多了一次额外的查找/排序")；不要跨列使用(where 和order by 拼起来，不要跨列使用)

再看一个如下sql语句：

EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=1 ORDER BY a2,a3;

MySql性能优化实例分析

我们可以看到where后面的a1 和order by 后面a2,a3构成了连续性，所以就没有出现using filesort。

总结：

1. 如果(a,b,c,d)复合索引和使用顺序全部一致（且不跨列使用），则复合索引全部使用。如果部分一致（且不跨列使用），则使用部分索引。

2. where 和order by 拼起来，不要跨列使用

案例2：单表优化

创建如下表：

CREATE TABLE book(  bid INT(4) PRIMARY KEY,  NAME VARCHAR(20) NOT NULL,  authorid INT(4) NOT NULL,  publicId INT(4) NOT NULL,  typeid INT(4) NOT NULL);INSERT INTO book VALUES(1,'tjava',1,1,2);INSERT INTO book VALUES(2,'tc',2,1,2);INSERT INTO book VALUES(3,'wx',3,2,1);INSERT INTO book VALUES(4,'math',4,2,3);

查询authorid=1 且 typeid 为2或者3的bid

EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;

MySql性能优化实例分析

我们看到了恐怖的事情（type 为ALL），因为没有使用任何索引.

<1>第一步优化，加上索引如下：

ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid);

MySql性能优化实例分析

我们看到了type使用index，略微的有点提升，using index说明了使用了覆盖索引，也提升了一点点。

<2>进一步优化，依据最左前缀原则优化，注意：不用的索引要删除，避免干扰。

ALTER TABLE book ADD INDEX idx_bta1 (typeid,authorid,bid);

MySql性能优化实例分析

<3>进一步优化，因为范围查询typeid in (2,3)有时会失效，所以我们把typeid 放在后面：

ALTER TABLE book ADD INDEX idx_bta1 (authorid,typeid,bid);EXPLAIN SELECT bid FROM book WHERE  authorid = 1 AND typeid IN (2,3) ORDER BY typeid DESC;

MySql性能优化实例分析

我们可以看出，type提高了两个级别，直接有index变成了ref.

大家可以看下同时出现了using where 和 using index，为什么呢？

using where代表回原表查询，using index代表不回原表查询，因为type in (2,3) 让索引失效了，所以回原表查询。

例如以下，不使用in语句

EXPLAIN SELECT bid FROM book WHERE  authorid = 1 AND typeid = 2 ORDER BY typeid DESC;

MySql性能优化实例分析

通过key_len再次证明in可以使索引失效。

小结：

a. 最佳左前缀，保持索引的定义和使用的顺序一致性

b. 索引需要逐步优化

c. 将含有in的范围查询放在where条件的最后，防止失效

案例3：两表优化

创建两个表如下：

CREATE TABLE teacher2(  tid INT(4) PRIMARY KEY,  cid INT(4) NOT NULL);
INSERT INTO teacher2 VALUES(1,2);INSERT INTO teacher2 VALUES(2,1);INSERT INTO teacher2 VALUES(3,3);
CREATE TABLE course2(  cid INT(4),  cname VARCHAR(20));
INSERT INTO course2 VALUES(1,'java');INSERT INTO course2 VALUES(2,'python');INSERT INTO course2 VALUES(3,'koltin');

看如下sql查询：

SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

假设t表10条数据，c表300条数据

现在有一个问题，索引应该往哪个表里加？

规范：小表驱动大表、索引建立在经常使用的字段上

由t表和c表的数量可以得出，t表的cid使用次数频繁，t表cid一次要循坏300次，故使用频繁，因此给t表的cid加索引。（一般情况下对于左外连接，给左表加索引；右外连接，给右表加索引）

当编写..on t.cid = c.cid时，将数据量小的表放在左边(假设此时t表数据量小)

<1>无索引优化看如下执行计划：

MySql性能优化实例分析

可以看出extra 中的Using join buffer，说明sql写的太烂，连MySql都看不下去了，作用是MySql引擎使用了连接缓存。

<2>由上面的规则，我们给t表中的cid加索引

ALTER TABLE teacher2 ADD INDEX index_teacher2_cid (cid);

MySql性能优化实例分析

可见t表已经提升了好几个档次

<3>一般where后面的字段要加索引

ALTER TABLE course2 ADD INDEX index_course2_cname(cname);

MySql性能优化实例分析

可见c表也提升了好几个档次。

综上所述，就是两表查询的优化过程。

案例4：三表优化

规则如下：

a. 小表驱动大表

b. 索引建立在经常查询的字段上

可按照单表，两表的优化规则进行优化。

“MySql性能优化实例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！

MySql性能优化实例分析

相关阅读