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欄位

• 例子數據

  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比較