MySql性能优化实例分析
本篇内容介绍了“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性能优化实例分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站,小编将为大家输出更多高质量的实用文章!
文章题目:MySql性能优化实例分析
标题链接:http://scyanting.com/article/gidgge.html