MySql优化分析-阿里云开发者社区

开发者社区> 优惠码领取> 正文

MySql优化分析

简介: MySql优化分析原理MYSQL逻辑分层 :连接层 服务层 引擎层 存储层InnoDB(默认) :事务优先 (适合高并发操作;行锁)MyISAM :性能优先 (表锁) SQL优化编写过程:sql select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit .. 解析过程:sql from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ... 索引分类主键索引 : 不能重复。
+关注继续查看

MySql优化分析
原理
MYSQL逻辑分层 :连接层 服务层 引擎层 存储层
InnoDB(默认) :事务优先 (适合高并发操作;行锁)
MyISAM :性能优先 (表锁)

SQL优化
编写过程:
sql select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..

解析过程:
sql from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

索引分类
主键索引 : 不能重复。id 不能是null
唯一索引 : 不能重复。id 可以是null
单值索引 : 单列, age ;一个表可以多个单值索引,name。
复合索引 : 多个列构成的索引 (相当于 二级目录 : z: zhao) (name,age) (a,b,c,d,...,n)

分析SQL
分析SQL的执行计划 : explain ,可以模拟SQL优化器执行SQL语句,从而让开发人员 知道自己编写的SQL状况
MySQL查询优化其会干扰我们的优化
explain字段

参数 说明
id 编号
select_type 查询类型
table 表
type 类型
possible_keys 预测用到的索引
key 实际使用的索引
key_len 实际使用索引的长度
ref 表之间的引用
rows 通过索引查询到的数据量
Extra 额外的信息
例子数据

create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);

insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;
id(编号):

id值相同,从上往下 顺序执行

--查询教授SQL课程的老师的描述(desc)
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
and t.tcid = tc.tcid and c.cname = 'sql' ;
id值不同:id值越大越优先查询 (本质:在嵌套子查询时,先查内层 再查外层)

--将以上 多表查询 转为子查询形式:
explain select tc.tcdesc from teacherCard tc where tc.tcid =
(select t.tcid from teacher t where t.tid =
(select c.tid from course c where c.cname = 'sql')
);
id值有相同,又有不同: id值越大越优先;id值相同,从上往下 顺序执行

--子查询+多表:
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;
select_type(查询类型):

PRIMARY:包含子查询SQL中的 主查询 (最外层)
SUBQUERY:包含子查询SQL中的 子查询 (非最外层)
simple:简单查询(不包含子查询、union)
derived:衍生查询(使用到了临时表)
union:
a.在from子查询中只有一张表

explain select  cr.cname    from ( select * from course where tid in (1,2) ) cr ;

b.在from子查询中, 如果有table1 union table2 ,则table1 就是derived,table2就是union

explain select  cr.cname    from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

union result :那些表之间存在union查询
type(索引类型、类型)

例子数据

create table test01
(
    tid int(3),
    tname varchar(20)
);

insert into test01 values(1,'a') ;
commit;
--增加索引
alter table test01 add constraint tid_pk primary key(tid) ;
explain select * from (select * from test01 )t where tid =1 ;

执行效率:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常用的类型:system>const>eq_ref>ref>range>index>all
要对type进行优化的前提:有索引,其中:system,const只是理想情况;实际能达到 ref>range;
system(忽略): 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询
const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 (类型 与索引类型有关)
eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0)
ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)
range:检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,从而转为 无索引all)
index:查询全部索引中数据
all:查询全部表中的数据
possible_keys

可能用到的索引,是一种预测,不准
如果 possible_key/key是NULL,则说明没用索引
key

实际使用到的索引
key_len

作用:用于判断复合索引是否被完全使用 (a,b,c)
ref

注意与type中的ref值区分,作用: 指明当前表所 参照的 字段
rows

被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
Extra

using filesort : 性能消耗大;需要“额外”的一次排序(查询) 。常见于 order by 语句中
MySql最大连接数
mysql的最大连接数默认是100, 最大可以达到16384,可以通过max_connections设置

对MySQL语句性能优化的16条经验
为查询缓存优化查询
EXPLAIN 我们的SELECT查询(可以查看执行的行数)
当只要一行数据时使用LIMIT 1
为搜索字段建立索引
在Join表的时候使用相当类型的列,并将其索引
千万不要 ORDER BY RAND ()
避免SELECT *
永远为每张表设置一个ID
可以使用ENUM 而不要VARCHAR
尽可能的使用NOT NULL
固定长度的表会更快
垂直分割
拆分打的DELETE或INSERT语句
越小的列会越快
选择正确的存储引擎
小心 "永久链接"
MyISAM和InnoDB比较
MyISAM InnoDB
事务 不支持 支持
数据行锁定 不支持,只有表锁定 支持
外键约束 不支持 支持
表空间大小 相对小 相对大
全文索引 支持 不支持
关注点 性能(select) 事务
原文地址https://www.cnblogs.com/geniusrun/p/10592020.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
冬季实战营第一期:从零到一上手玩转云服务器学习总结
冬季实战营第一期:从零到一上手玩转云服务器学习总结
5 0
02_spring_ioc| 学习笔记
快速学习02_spring_ioc
3 0
01_spring_ 简介| 学习笔记
快速学习01_spring_ 简介
8 0
Django框架介绍与安装
软件框架就是为实现或完成某种软件开发时,提供了一些基础的软件产品, 框架的功能类似于基础设施,提供并实现最为基础的软件架构和体系 通常情况下我们依据框架来实现更为复杂的业务程序开发 二个字,框架就是程序的骨架
4 0
我的无影云电脑初体验
最近看到阿里云开发者社区有无影云电脑的体验活动,遂报名体验。 由于在阿里云开发者社区是专家博主,有幸获得一年的 4核 8G 无影云电脑的体验机会。 试用了几天,下面从无影云电脑的用户群体、无影云电脑的使用场景,我的使用感受,目前还存在的问题等方面,简单谈谈自己的感受。
12 0
03_spring_ioc 实现| 学习笔记
快速学习03_spring_ioc 实现
3 0
python垃圾回收
吃太多,总会变胖,Python也是这样。当Python中的对象越来越多,它们将占据越来越大的内存。不过你不用太担心Python的体形,它会乖巧的在适当的时候“减肥”,启动垃圾回收(garbage collection),将没用的对象清除。在许多语言中都有垃圾回收机制,比如Java。尽管最终目的都是塑造苗条的提醒,但不同语言的减肥方案有很大的差异 。
4 0
04_spring_ioc 创建对象的方式| 学习笔记
快速学习 04_spring_ioc 创建对象的方式
3 0
初始ECS
物联网学习的硬性需求,我们需要搭建一台自己的服务器,在不断查找资料和方法后,获知阿里云ECS云服务器有学生体验资格,继而我将利用ECS云服务器搭建一个自己的EMQX服务器端。
16 0
+关注
优惠码领取
阿里云优惠码阿里云推荐券bieryun.com
193
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载