MySQL資料庫儲存引擎與資料庫最佳化

標籤：

儲存引擎

（1）MySQL可以將資料以不同的技術儲存在檔案（記憶體）中，這種技術就成為儲存引擎。

每種存數引擎使用不同的儲存機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。

（2）使用不同的儲存引擎也可以說不同類型的表

（3）MySQL支援的儲存引擎

• 1. MyISAM
• 1. InnoDB
• 1. Memory
• 1. CSV
• 1. Archive

查看資料表的建立語句：

SHOW CREATE TABLE 表名

相關概念：
（1）.並發控制：一個人讀資料，另外一個人在刪除這個資料。

當多個串連對記錄進行修改時保證資料的一致性和完整性。系統使用鎖系統來解決這個並發控制，這種鎖分為：

1）.共用鎖定（讀鎖）—在同一時間內，多個使用者可以讀取同一個資源，讀取過程中資料不會發生任何變化。

2）.獨佔鎖定（寫鎖）—在任何時候只能有一個使用者寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。

3.鎖的力度（也叫鎖的顆粒）

鎖顆粒(鎖定時的單位）

—表鎖，是一種開銷最小的鎖策略。得到資料表的寫鎖

—行鎖，是一種開銷最大的鎖策略。並行性最大

表鎖的開銷最小，因為使用鎖的個數最小，行鎖的開銷最大，因為可能使用鎖的個數比較多。

並發性

就是多個連結對同一份資料進行操作時，要保證資料的完整性和一致性。

事務的特性 —–》轉賬業務：從一個人減去 100，另外一個人加上100。

事務（包含一連串的操作，事務（Transaction）是一個對資料庫執行工作單元）是為了保護資料的完整性。幾個過程作為整體即事務 每個過程出現錯誤都恢複到原來的資料

1.原子性（Atomicity）：確保工作單位內的所有操作都成功完成，否則，事務會在出現故障時終止，之前的操作也會復原到以前的狀態。

2.一致性（Consistency)：確保資料庫在成功提交的事務上正確地改變狀態。

3.隔離性（Isolation）：使事務操作相互獨立和透明。

4.持久性（Durability）：確保已提交事務的結果或效果在系統發生故障的情況下仍然存在。

ACID；

外鍵和索引

1、外鍵：保證資料一致性的策略
2、索引：類似目錄，是對資料表中一列或多列的值進行排序的一種結構，方便快速尋找到資料

索引：普通索引、唯一索引、全文索引、Btree索引、hash索引……

各種儲存引擎的特點

使用最多的：MyISAM，InnoDB。

MyISAM：適用於事務的處理不多的情況，支援資料壓縮，容量大；
InnoDB：適用於交易處理比較多，需要有外鍵支援的情況。

CSV儲存引擎：以逗號為分隔字元，不支援索引；
BlackHole：黑洞引擎，寫入的資料都會消失，一般用於做資料複製的中繼；

儲存引擎：
MyISAM： 儲存限制可達256TB,支援索引，表級鎖定，資料壓縮
InnoDB: 儲存限制為64TB,支援事務和索引，鎖顆粒為行鎖。

設定儲存引擎
（1）通過修改MySQL設定檔實現

default-storage-engine = engine

（2）通過建立資料表命令實現

CREATE TABLE table_name(...) ENGINE = engine;

例如：

CREATE TABLE tp1(s1 VARCHAR(10)) ENGINE = MyISAM;SHOW CREATE TABLE tp1; // 查看資料表的結構

（3）通過修改資料表命令實現

ALTER TABLE table_name ENGINE [=] engine_name; 

例如：

ALTER TABLE tp1 ENGINE = InnoDB;

MYSQL資料庫最佳化

1、資料字典的維護

維護資料字典：

1.第三方工具：針對不同的DBMS
2.利用資料庫本身的備忘欄位：對錶和列增加備忘欄位，舉例

3.匯出資料字典（很通用）但是注意：更改表備忘時，只需要更改表備忘，其
他的一些列的屬性（列的長度、寬度、是否非空）必須保持原樣

2、維護索引

建立索引的列：

• 1、出現在where、group by、order by 從句中的列
• 2、可選擇性高的列放到索引前面（條件列順序不要求與索引列順序一致）
• 3、索引列資料不要太長，（如text進行md5處理）
  注意：1、索引不是越多越好（過多的索引也會降低讀的效率:多個索引選擇的過程）

2、定期維護索引片段
3、（MySQL)SQL中不要使用強制索引關鍵字

3、維護（修改）表結構

注意事項
1、MySQL5.5之前會鎖表，可使用第三方工具；5.6之後本身支援線上表結構變更
2、同時維護資料字典
3、控製表的寬度和大小

適合的操作

1、大量操作(資料庫中）逐條操作（應用程式中）
2、盡量少用”select * “查詢
3、控制使用使用者自訂函數（使用函數，索引不起作用）
4、不要使用全文索引（中文支援不好，需要另建索引檔案）

4、資料表的水平分割和垂直分割

垂直分割：為了控製表的寬度

水平分割：為了控製表的資料量

表示二維的是個平面，上面的情況是非常容易想想的，問題的關鍵是要依靠一定原則了！
目標是不變的：為了效率、為了可維護性、為了更快更省事！

SQL查詢語句最佳化

explain分析sql的執行計畫，並找出sql需要最佳化的地方

explain select customer_id,first_name,last_name from customer;
+—-+————-+———-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | customer | ALL | NULL | NULL | NULL | NULL | 599 | NULL |
+—-+————-+———-+——+—————+——+———+——+——+——-+

• table：表名；
• type：串連的類型，const、eq_reg、ref、range、index和ALL；const：主鍵、索引；eq_reg：主鍵、索引的範圍尋找；ref：串連的尋找（join），
• range：索引的範圍尋找；index：索引的掃描；
• possible_keys：可能用到的索引；
• key：實際使用的索引；
• key_len：索引的長度，越短越好；
• ref：索引的哪一列被使用了，常數較好；
• rows：mysql認為必須檢查的用來返回請求資料的行數；
• extra：using filesort、using temporary（常出現在使用order by時）時需要最佳化。

Max()和Count()的最佳化

1.對max()查詢，可以為表建立索引，create index index_name on table_name(column_name 規定需要索引的列)，這裡就是以付款的日期為索引；,然後在進行查詢。

如果沒有索引，查詢的可能一直到最後一行。

2.count()對多個關鍵字進行查詢，比如在一條SQL中同時查出2006年和2007年電影的數量，語句：

select count(release_year=‘2006‘ or null) as ‘2006年電影數量‘,count(release_year=‘2007‘ or null) as ‘2007年電影數量‘from film;

count(*)時會包含null空這一列，而count(id)這種寫法將不包含null這一列.

3.子查詢的最佳化

把子查詢改為左串連查詢，但是如果兩張表裡存在一對多的情況，左串連查詢結果會出現，所以要使用distinct去掉重複記錄

select * from table1 where table1.column1 in (select table2.column2 from table2);select distinct table1.column1 from table1 join table2 on table1.column1=table2.column2;

4.order by語句最佳化
group by可能會出現暫存資料表（Using temporary），檔案排序（Using filesort）等，影響效率。
可以通過關聯的子查詢，來避免產生暫存資料表和檔案排序，可以節省io
改寫前

select actor.first_name,actor.last_name,count(*)from sakila.film_actorinner join sakila.actor using(actor_id)group by film_actor.actor_id;

改寫後

select actor.first_name,actor.last_name,c.cntfrom sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group byactor_id)as c using(actor_id);

5.limit 語句最佳化

limit常用於分頁處理，時常會伴隨order by從句使用，因此大多時候會使用Filesorts這樣會造成大量的io問題

1.使用有索引的列或主鍵進行order by操作

2.記錄上次返回的主鍵，在下次查詢時使用主鍵過濾
使用這種方式有一個限制，就是主鍵一定要順序排序和連續的，如果主鍵出現空缺可能會導致最終頁面上顯示的列表不足5條，解決辦法是附加一列，保證這一列是自增的並增加索引就可以了

6.選擇合適的索引列

1.在where，group by，order by，on從句中出現的列

2.索引欄位越小越好(因為資料庫的儲存單位是頁，一頁中能存下的資料越多越好 )

3.離散度大得列放在聯合索引前面

select count(distinct customer_id), count(distinct staff_id) from payment;

查看離散度 通過統計不同的列值來實現 count越大 離散程度越高

過多的索引不但影響寫入，而且影響查詢，索引越多，分析越慢
如何找到重複和多餘的索引，主鍵已經是索引了，所以primay key 的主鍵不用再設定unique唯一索引了

冗餘索引，是指多個索引的首碼列相同，innodb會在每個索引後面自動加上主鍵資訊


冗餘索引查詢工具
pt-duplicate-key-checker

由於業務變更有些原來使用的索引現在不使用了也是需要清除的，這也是索引最佳化的一個方面了！有些索引的使用的頻率很低，甚至沒用過。
注意：作者再次的強調SQL和索引的最佳化對於資料庫的最佳化是相當重要的，這一層的最佳化如果做好了，其他的最佳化也能起到一些作用否則其他的最佳化所能起到的作用是微乎其微的，這一層的最佳化也是成本最低效果最好的一層了，所以對於資料庫的最佳化最好重點放在這一層。

1. 設定檔的最佳化；

#重要，緩衝池的大小 推薦總記憶體量的75%，越大越好。innodb_buffer_pool_size#預設只有一個緩衝池，如果一個緩衝池中並發量過大，容易阻塞，此時可以分為多個緩衝池；innodb_buffer_pool_instances#log緩衝的大小，一般最常1s就會重新整理一次，故不用太大；innodb_log_buffer_size#重要，對io效率影響較大。0:1s重新整理一次到磁碟；1：每次提交都會重新整理到磁碟；2：每次提交重新整理到緩衝區，1s重新整理到磁碟；預設為1。innodb_flush_log_at_trx_commit#讀寫的io進程數量，預設為4innodb_read_io_threadsinnodb_write_io_threads#重要，控制每個表使用獨立的資料表空間，預設為OFF，即所有表建立在一個共用的資料表空間中。innodb_file_per_table#mysql在什麼情況下會重新整理表的統計資訊，一般為OFF。innodb_stats_on_metadata

MySQL資料庫儲存引擎與資料庫最佳化