本篇内容介绍了“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;
如上两者的执行计划一模一样，为什么？
因为第二条sql在真正执行前经过了sql优化器的调整，所以与上条保持一致。第一种sql是推荐写法。
再看一个如下sql语句：
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=1 AND a4=1 ORDER BY a3;
我们看执行计划的结果：使用了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;
我们看到以上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;
我们可以看到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;
我们看到了恐怖的事情（type 为ALL），因为没有使用任何索引.
<1>第一步优化，加上索引如下：
ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid);
我们看到了type使用index，略微的有点提升，using index说明了使用了覆盖索引，也提升了一点点。
<2>进一步优化，依据最左前缀原则优化，注意：不用的索引要删除，避免干扰。
ALTER TABLE book ADD INDEX idx_bta1 (typeid,authorid,bid);
<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;
我们可以看出，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;
通过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>无索引优化看如下执行计划：
可以看出extra 中的Using join buffer，说明sql写的太烂，连MySql都看不下去了，作用是MySql引擎使用了连接缓存。
<2>由上面的规则，我们给t表中的cid加索引
ALTER TABLE teacher2 ADD INDEX index_teacher2_cid (cid);
可见t表已经提升了好几个档次
<3>一般where后面的字段要加索引
ALTER TABLE course2 ADD INDEX index_course2_cname(cname);
可见c表也提升了好几个档次。
综上所述，就是两表查询的优化过程。
案例4：三表优化
规则如下：
a. 小表驱动大表
b. 索引建立在经常查询的字段上
可按照单表，两表的优化规则进行优化。
“MySql性能优化实例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
