欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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 ;
    • 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) 事务