關於MySQL資料庫最佳化

標籤：

MySQL資料庫最佳化相關知識：

一、兩種常用引擎的選擇

1、MyISAM

當MySQL版本小於5.5時的預設引擎

優點：擅長資料處理；高速讀寫；資料的儲存順序為插入順序，插入速度快；空間佔用量小

特性：全文索引支援(版本大於等於5.6的Innodb也支援)，可以利用myisamPack完成資料的壓縮功能

缺點：僅支援表級鎖定，支援並發插入，寫操作中插入操作不會影響其他動作。不支援事務

2、InnoDB

版本大於等於5.5的預設引擎

優點：提供事務，行級鎖定，外鍵約束，注重資料的完整性和安全性

特點：事務，外鍵約束，維護資料完整性；擅長並發處理，支援行級鎖定

缺點：資料按照主鍵的順序進行儲存，插入時做排序工作效率低

 

二、資料類型的選擇原則

在滿足需求的前提下：

1、資料儘可能的小，使佔用的儲存空間小

2、儘可能定長，使佔用的儲存空間固定，減少變長時計算帶來的損耗

3、儘可能使用整數

 

三、常用手段：索引

1、概念

索引是利用關鍵字使記錄的部分資料與記錄位置有直接的對應關係

2、索引的類型

①主索引(primary key)：要求關鍵字不能重複，也不能為NULL，同時添加主鍵約束

②唯一索引(unique index)：要求關鍵字不能重複，同時添加唯一約束

③普通索引(index)：對關鍵字沒有要求

④全文索引(fulltext index)：關鍵字的來源是從欄位中提取的特別關鍵詞

⑤複合索引，多個欄位上提取關鍵字需要用到複合索引

3、添加索引

alter table table_name add {(primary key),(unique index),(index),(fulltext index)} index_name (‘欄位名‘);

4、刪除索引

alter table table_name drop {(primary key),(index)...} index_name;

tips:可以利用explain來擷取查詢語句的執行計畫

5、使用情境

①索引檢索：條件過濾的欄位添加索引，如where,select

②索引排序：如order by

③索引覆蓋：如where後面的欄位已經在select後面多個欄位中已經出現，只需要添加select後面欄位的複合索引

6、使用規則

①列獨立：索引欄位需要保證 獨立 在一側

②左原則：匹配模式必須在左邊確定不能以萬用字元開頭，即 like 後面的條件不能以% _ 開頭

③複合索引左規則：複合索引關聯多個欄位，where時僅對複合索引最左邊的欄位有效果

④OR的使用：需要保證OR兩邊的條件都存在可用的索引，該查詢才會使用索引

⑤MySQL智能選擇：查詢使用索引會導致大量的隨機IO，當隨機IO大於順序遍曆IO時，會自動棄用該索引

 ⑥同時滿足檢索、排序和覆蓋是最好的

7、首碼索引(建立索引關鍵字的一種方案，不能用於索引覆蓋)

index `index_name` (`index_field`(N))使用index_field的前N個字元建立的索引

確定N的tips：

①計算最大辨識度：

select all_count/count(distinct 欄位名) from table_name，越接近該值越接近極限

②計算前N個字元達到的辨識度：

select all_count/count(distinct substr(欄位名, 1, N)) from table_name;依次增大N的值，達到最大辨識度時的N即可建立前N個字元建立的索引

8、全文索引

舉個栗子，解決 like ‘%keyword%’這類查詢的匹配問題

Select * from articles where title like ‘%database%’ or body like ‘%database%’;此時不能建立普通索引，查詢不符合 左原則，建立了也使用不了。此時 全文索引就可以其作用了：

alter table articles add fulltext index `title_body` (`title`,`body`);

再使用Match()against()進行全文索引匹配文法才可以生效。本栗子中使用

select * from article where match(title, body) against(‘database‘);

注意：①不能對中文起作用；②類似 in a 等無具體意義的詞不能用該方法，因為全文索引的是資料中提取的關鍵詞

9、索引的資料結構(hash、B-Tree、叢集索引/聚簇索引)

 

四、查詢快取 query_cache

將select的查詢結果儲存起來供二次使用的快取區域

開啟方法：

①show variables like ‘query_cache%‘;

②set global query_cache_type = 1; //開啟查詢快取

③set global query_cache_size = 1024*1024*32;//設定查詢快取為32M

注意：①查詢嚴格以來select語句本身， 語句順序、大小寫不能變；②不能包含動態資料；③可以使用SQL_NO_CACHE語句使該語句不被緩衝，例如select SQL_NO_CACHE * from emp where empno = 1234567;

 

五、分區(partition)

將某張表的資料分別儲存到不同地區

1、建立分區，在建立表時，指定分區的選項

create table table_name(定義) partition by 分區演算法 (參數) 分區選項

tips:利用show variables like ‘have_partitionong‘來查看是否支援分區

2、四種分區演算法

取餘：key，hash

條件：list，range

例如：

create table xxx(xx) partition by key (id) partitions 5;//將表按照id取餘分為5個區

paritition by hash (month(date)) parititions 12;//將表從date中取出的month分為12個

paritition by list (month(date))(paritition spring values in (3,4,5), paritition summer values in (6,7,8,9), partition autumn values in (10,11), partition winter values in (12,1,2));//使用list按照具體的值劃分

partition by range (year(date))(partition p_80 values less than (1990), partition p_90 values less than (2000), partition p_00 values less than maxvalue);//使用range劃分範圍進行分區

3、管理分區

①取餘key has中

增加分區數量： add partition partitions N

減少分區數量 ：coalesce partition N

②條件list range中

增加分區： add partition(pritition p_new00 values less than (2010));

刪除分區： drop partition partition_name;//注意，會導致刪除的分區資料丟失

4、選擇分區演算法

①平均分配：按照主鍵進行key取餘分區即可

②按照某種商務邏輯分區：選擇最容易篩選的欄位，比如整數型

 

六、表合并

可以使用mrg_myisam使多個結構相同的myisam表合并到一起

 

七、邏輯

①並發性的sql：少用不用多表操作，如子查詢，jion等；將複雜的SQL差分多次執行

②大量資料的插入：

對於myisam：建議先使用 alter table table_name disable keys 禁用索引約束，再大量資料插入完畢後，再利用 alter table table_name enable keys 開啟

對於innodb：

Drop index, drop constraint 要保留主鍵Begin transaction|set autocommit=0;[資料本身已經按照主索引值排序]大量的插入Commit;Add index, add constraint

另外，insert into table_name values (),(),()...以10量級為單位即可，不宜過多

 

八、慢查詢日誌

定位執行比較慢的查詢語句

show variables like ‘slow_query%‘　　//查看慢查詢日誌狀態以及位置

show variables like ‘%long_query%‘　//對快慢查詢的時間臨界點進行設定

關於MySQL資料庫最佳化