MySql优化分析
程序员文章站
2022-05-18 23:18:34
原理 MYSQL逻辑分层 :连接层 服务层 引擎层 存储层 InnoDB(默认) :事务优先 (适合高并发操作;行锁) MyISAM :性能优先 (表锁) SQL优化 编写过程: 解析过程: 索引分类 主键索引 : 不能重复。id 不能是null 唯一索引 : 不能重复。id 可以是null 单值索 ......
原理
- 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 ;
- a.在from子查询中只有一张表
- 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) | 事务 |