SQL優化常用技巧--資料類型優化｜索引優化｜查詢優化

mysql架構圖

注意：根據存儲引擎的不同，以下的優化方法不一定全部适用。一般情況是适用的。
           

一、資料類型優化

1. 更小的通常更好。一般情況下，應該盡量使用可以正确存儲資料的最小資料類型。更小的資料類型通常更快，因為它們占用更少的磁盤、記憶體和CPU緩存，處理時需要的CPU周期也更少。（但是要確定沒有低估需要存儲的值的範圍，因為在schema中多個地方增加資料類型的範圍是一個非常耗時和痛苦的操作）
   • 使用varchar(5)和varchar(200)存儲‘hello’的空間開銷是一樣的。但是更長的列會消耗更多的記憶體，因為mysql通常會配置設定固定大小的記憶體來儲存内部值。
2. 簡單就好。簡單資料類型的操作通常需要更少的CPU周期。例如：
   • 整型比字元操作代價更低；
   • 使用mysql内建的類型而不是字元串來存儲日期和時間；
   • 用整形存儲IP位址。
3. 盡量避免NULL。除非真實資料模型中有确切的需要，否則應該盡可能的避免使用NULL值。很多表都包含為NULL的列，因為可為NULL是列的預設屬性，通常情況下最好指定列為NOT NULL.
   • 當可為NULL的列被索引時，每個索引記錄需要一個額外的位元組，在MYISAM裡甚至還可能導緻固定大小的索引變成可變大小的索引。
   • 如果計劃在列上建索引，就應該盡量避免設計成可為NULL的列。

二、索引優化

參考部落格

索引優化應該是對查詢性能優化最有效的手段了。		--《高性能mysql》
           

​

1、索引設計的原則:

• 适合索引的列是出現在where子句中的列，或者連接配接子句中指定的列；
• 基數`較小的類，索引效果較差，沒有必要在此列建立索引；
• 使用短索引。如果對長字元串列進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間；
• 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表内容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。是以隻保持需要的索引有利于查詢即可。

以下查詢語句以下表（tb_emp)為例
           

desc tb_emp;
           

基數：單個列唯一鍵（distict_keys）的數量叫做基數。如下圖所示，姓名的基數就是員勞工數，職位基數小于員勞工數。
           

select count(distinct ename),count(distinct job)         from `tb_emp`;
           

2、索引優化規則：

show status like 'handler_read%';
show index from tb_emp;
           

說明：
Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。
Handler_read_rnd_next：資料檔案中讀取下一行的請求數，如果正在進行大量的表掃描，值将較高，則說明索引利用不理想。
           

1. 如果MySQL估計使用索引比全表掃描還慢，則不會使用索引。
2. 前導模糊查詢不能命中索引。

   like '%字段%'

explain select * from tb_emp where ename like '%莫%';
           

explain select * from tb_emp where ename like '李莫%';
           

說明：
1. type列，連接配接類型。一個好的sql語句至少要達到range級别。杜絕出現all級别
const > ref_eq > ref > range > index > all
(常數引⽤ > 唯⼀索引查詢 > 範圍掃描 > 索引掃描 > 全表掃描)
2. key列，使用到的索引名。如果沒有選擇索引，值是NULL。可以采取強制索引方式
3. key_len列，索引長度
4. rows列，掃描行數。該值是個預估值
5. extra列，詳細說明。注意常見的不太友好的值有：Using filesort, Using temporary
           

1. 複合索引的情況下，查詢條件不包含索引列最左邊部分（不滿足最左原則），不會命中符合索引。

alter table tb_emp add index index_mul(ename,job,sal) ;
explain select * from tb_emp where ename ='李莫愁' and sal = 3500;
           

explain select * from tb_emp where job ='設計師' and sal = 3500;
           

1. union、in、or都能夠命中索引，建議使用in(查詢的CPU消耗：or>in>union。
2. 用or分割開的條件，如果or前的條件中列有索引，而後面的列中沒有索引，那麼涉及到的索引都不會被用到。

explain select * from tb_emp where eno = 3088 or sal =3500;
           

​ 因為or後面的條件列中沒有索引，那麼後面的查詢肯定要走全表掃描，在 存在全表掃描的情況下，就沒有必要多一次索引掃描增加IO通路。

1. 負向條件查詢不能使用索引，可以優化為in查詢。

   負向條件有：!=、<>、not in、not exists、not like等。

explain select * from tb_emp where ename != '李莫愁';
           

1. 範圍條件查詢可以命中索引。範圍條件有：<、<=、>、>=、between等。
2. 如果是範圍查詢和等值查詢同時存在，優先比對等值查詢列的索引：

explain select * from tb_emp where sal > 4000 and mgr > 4000;
           

explain select * from tb_emp where sal > 4000 and mgr = 5566;
           

1. 資料庫執行計算不會命中索引。

explain select * from tb_emp where sal > 4000;
           

explain select * from tb_emp where sal+1 > 4000;
           

計算邏輯應該盡量放到業務層處理，節省資料庫的CPU的同時最大限度的命中索引。
           

3、建立索引的注意事項：

1. 更新十分頻繁的字段上不宜建立索引：因為更新操作會變更B+樹，重建索引。這個過程是十分消耗資料庫性能的。
2. 區分度不大的字段上不宜建立索引：類似于性别這種區分度不大的字段，建立索引的意義不大。因為不能有效過濾資料，性能和全表掃描相當。另外傳回資料的比例在30%以外的情況下，優化器不會選擇使用索引。
3. 業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。雖然唯一索引會影響insert速度，但是對于查詢的速度提升是非常明顯的。另外，即使在應用層做了非常完善的校驗控制，隻要沒有唯一索引，在并發的情況下，依然有髒資料産生。
4. 多表關聯時，要保證關聯字段上一定有索引。
5. 建立索引時避免以下錯誤觀念：索引越多越好，認為一個查詢就需要建一個索引；甯缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度；抵制唯一索引，認為業務的唯一性一律需要在應用層通過“先查後插”方式解決；過早優化，在不了解系統的情況下就開始優化。

三、查詢性能優化

1. 查詢性能低下最基本的原因是通路的資料太多。
   1. 是否向資料庫請求了不需要的資料
      • 在查詢結果後面加上limit。
      • 多表關聯時傳回全部的列

      select * from tb_emp inner join tb_dept on tb_emp.dno=tb_dept.dno where tb_dept.dno=20;
      select tb_emp.* from tb_emp inner join tb_dept on tb_emp.dno=tb_dept.dno where tb_dept.dno=20;
                 

   

   注意：總是取出全部的列。取出全部的列會讓優化器無法完成索引覆寫掃描這類優化，還會為伺服器帶來額外的I/O、記憶體和CPU的消耗。是以，一些DBA是嚴格禁止select *的寫法的。
              

   1. mysql是否在掃描額外的記錄.如果查詢需要掃描大量的資料但隻傳回少數的行，那麼可以試試如下方法：
      • 重寫這個複雜查詢
      • 使用索引覆寫掃描
      • 改變庫表結構
2. 重構查詢的方式
   • 一個複雜查詢還是多個簡單查詢
   • 切分查詢,一次删除一萬行資料一般來說是一個比較高效而且對伺服器影響也最小的做法。

   delete from messages where created < date_sub(now(),interval 3 month);
   
   rows_affected = 0
   do {
   	rows_affected = do_query(
     	"delete from messages where created < date_sub(now(),interval 3 month) limit 10000)"
   }while rows_affected > 0
              

   • 分解關聯查詢(優點)
     • 讓緩存效率更高
     • 将查詢分解後，執行單個查詢可以減少鎖的競争
     • 在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高性能和可擴充；
     • 減少備援記錄的查詢
     • 相當于在應用中實作了哈希關聯

   select * from tag
   join tag_post on tag_post.tag_id=tag.id
   join post on tag_post.post_id=post.id
   where tag.tag='mysql';
   
   select * from tag where tag='mysql';
   select * from tag_post where tag_id=1234;
   select * from post where post.id in(123,456,9098,8904);
              

四、伺服器設定優化

五、作業系統和硬體優化

六、應用層優化