MySQL 資料庫與 SQL 優化

本文介紹的是針對MySQL 資料庫的SQL優化，接下來讓我們一起先了解下MySQL DBMS(MySQL Database Management System)。

1、結構圖

2、MySQL 資料庫引擎簡介

• 2.1 ISAM(Indexed Sequential Access Method)
  ISAM 是一個定義明確且歷經時間考驗的資料表格管理方法， 它在設計之時就考慮到資料庫被查詢的次數要遠大於更新的次數。
  特點：
  ISAM 執行讀取操作的速度很快， 而且不佔用大量的記憶體和儲存資源
  缺陷：
  不支援事務處理
  不能夠容錯，硬碟崩潰了， 那麼資料檔案就無法恢復了

• 2.2 MyISAM
  MyISAM 是 MySQL 的 ISAM 擴充套件格式（ MySQL5.5 之前版本的預設資料庫引擎） 資料庫引擎。 除了提供 ISAM 裡所沒有的索引和欄位管理的大量功能， MyISAM 還使用一種表格鎖定的機制， 來優化多個併發的讀寫操作， 其代價是你需要經常執行 OPTIMIZE TABLE 命令，來恢復被更新機制所浪費的空間。
  特點：
  MYISAM 強調了快速讀取操作， 這可能就是為什麼 MySQL 受到了 WEB 開發如此青睞的主要原因： 在 WEB 開發中你所進行的大量資料操作都是讀取操作。
  缺陷：
  不支援事務處理
  不能在表損壞後恢復資料
  注意：
  如果使用該資料庫引擎， 會生成三個檔案：
  .frm:表結構資訊
  .MYD:資料檔案
  .MYI:表的索引資訊

• 2.3 InnoDB
  InnoDB 資料庫引擎都是造就 MySQL 靈活性的技術的直接產品，MySQL（ 5.5 以上版本） 常用版本預設引擎。
  特點：
  對事務處理和外來鍵的支援
  資料多版本讀取（ InnoDB+MyISAM+ISAM）
  缺陷：
  讀取操作比MyISAM慢
  注意：
  在 MySQL5.7 版本中， InnoDB 儲存引擎管理的資料檔案為兩個： 分別是 frm表結構資訊和idb索引檔案。

• 2.4 Memory 儲存引擎
  Memory 儲存引擎是一個將資料儲存在記憶體中的儲存引擎。僅僅存放了一個表結構相關資訊的.frm 檔案在磁碟上面。 所以一旦 MySQL Crash 或者主機 Crash 之後， Memory 的表就只剩下一個結構了。 Memory 表支援索引， 並且同時支援 Hash 和 B－Tree 兩種格式的索引。 由於是存放在記憶體中， 所以 Memory 都是按照定長的空間來儲存資料的， 而且不支援 BLOB 和 TEXT型別的欄位。 Memory 儲存引擎實現頁級鎖定。

• 2.5 BLACKHOLE 儲存引擎
  BLACKHOLE 黑洞儲存引擎。就像我們 linux系統下面的"/dev/null"裝置一樣， 不管我們寫入任何資訊， 都是有去無回。

總結：
innodb 與 myisam 區別

1. InnoDB 支援事務， MyISAM 不支援， 對於 InnoDB 每一條 SQL 語言都預設封裝成事務，自動提交， 這樣會影響速度， 所以最好把多條 SQL 語言放在 begin transaction 和 commit 之間， 組成一個事務；
2. InnoDB 支援外來鍵， 而 MyISAM 不支援。 對一個包含外來鍵的 InnoDB 錶轉為 MYISAM 會失敗；
3. InnoDB 是聚集索引， 資料檔案是和索引綁在一起的， 必須要有主鍵， 通過主鍵索引效率很高。 但是輔助索引需要兩次查詢， 先查詢到主鍵， 然後再通過主鍵查詢到資料。 因此，主鍵不應該過大， 因為主鍵太大， 其他索引也都會很大。 而 MyISAM 是非聚集索引， 資料檔案是分離的， 索引儲存的是資料檔案的指標。 主鍵索引和輔助索引是獨立的。
4. InnoDB 不儲存表的具體行數， 執行 select count(*) from table 時需要全表掃描。 而MyISAM 用一個變數儲存了整個表的行數， 執行上述語句時只需要讀出該變數即可， 速度很快；
5. Innodb 不支援全文索引， 而 MyISAM 支援全文索引， 查詢效率上 MyISAM 要高；

如何選擇innodb 與 myisam

1. 是否要支援事務， 如果要請選擇 innodb， 如果不需要可以考慮 MyISAM
2. 如果表中絕大多數都只是讀查詢， 可以考慮 MyISAM， 如果既有讀寫也挺頻繁， 請使用 InnoDB。
3. 系統崩潰後， MyISAM 恢復起來更困難， 能否接受；
4. MySQL5.5 版本開始 Innodb 已經成為 Mysql 的預設引擎(之前是 MyISAM)， 說明其優勢是有目共睹的。

3、儲存引擎管理

• 3.1 檢視資料庫支援的儲存引擎
  show engines
• 3.2 檢視資料庫當前使用的儲存引擎
  show variables like '%storage_engine%'
  也可以在 MySQL 配置檔案中檢視。 windows ------ my.ini。 linux ------ my.cnf
• 3.3 檢視資料庫表所用的儲存引擎
  show create table table_name
• 3.4 建立表指定儲存引擎
  create table table_name (column_name column_type) engine = engine_name
• 3.5 修改表的儲存引擎
  alter table table_name engine=engine_name
• 3.6 修改預設的儲存引擎
  在 MySQL 配置檔案中修改下述內容：
  default-storage-engine=INNODB
  MySQL 配置檔案：
  windows 系 統 - MySQL 安 裝目 錄/my.ini （5.7 版 本 my.ini 文 件在 數 據目 錄 中。
  C:/programdata/MySQL Server 5.7/mysql/）
  linux 系統 ------ /etc/my.cnf

4、MySQL索引

索引在MySQL中也叫作 " 鍵 "，是儲存引擎用於快速找到記錄的一種資料結構。
優點：
索引的目的在於提高查詢效率
缺點：
索引需要佔物理空間，意味著索引不是建立越多越好
需要耗費時間維護索引

• 4.1 哪些欄位適合建立索引：
  where + 判斷條件欄位
  order by + 排序欄位
  select + 查詢頻率較高的欄位
  外來鍵欄位：可以加快連線的速度；

• 4.2 哪些欄位不適合建立索引：
  查詢中很少使用的欄位
  修改頻率較高的欄位

5、MySQL管理索引

5.1 普通索引
這是最基本的索引， 也是比較常用的索引。

• 5.1.1 建立索引

CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))

• 5.1.2 檢視索引

SHOW INDEX FROM table_name
SHOW KEYS FROM table_name  #keys關鍵字只適用於MySQL資料庫

• 5.1.3 刪除索引

DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

5.2 唯一索引
唯一索引列的值必須唯一， 但允許有空值 。

• 5.2.1 建立索引

CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))

5.3 全文索引（ FULLTEXT）
MySQL 從 3.23.23 版開始支援全文索引和全文檢索， FULLTEXT 索引僅可用於 MyISAM表。

• 5.3.1 建立索引

CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))

6、MySQL索引優化

• 6.1 索引不包含有 NULL 值的列
  只要列中包含有 NULL 值都將不會被包含在索引中， 組合索引中只要有一列含有 NULL值， 那麼這一列對於此組合索引就是無效的。 所以我們在資料庫設計時不要讓欄位的預設值為 NULL。
• 6.2 使用短索引
  例如， 如果有一個CHAR(255)的列，如果在前10個或20個字元內， 多數值是唯一的， 那麼就不要對整個列進行索引。 短索引不僅可以提高查詢速度而且可以節省磁碟空間和 I/O 操作。
• 6.3 索引列排序
  MySQL 查詢只使用一個索引， 因此如果where子句中已經使用了索引的話， 那麼 order by中的列是不會使用索引的。
• 6.4 like 語句操作
  like "%aaa%"不會使用索引， 而 like "aaa%"可以使用索引。
• 6.5 不要在列上進行運算
  例如： select * from users where YEAR(birthdate)<2019， 將在每個行上進行運算， 這將導致索引失效而進行全表掃 描 ， 因此可以改成 ： select * from users where birthdate<’2019-01-01′

7、MySQL 中的 SQL 的常見優化策略

• 7.1 避免全表掃描
  對查詢進行優化，應儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
• 7.2 避免判斷 null 值
  應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引
  而進行全表掃描，如：
  select id from t where num is null
  可以在 num 上設定預設值 0，確保表中 num 列沒有 null 值，然後這樣查詢：
  select id from t where num=0
• 7.3 避免不等值判斷
  應儘量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。
• 7.4 避免使用 or 邏輯
  應儘量避免在 where 子句中使用 or 來連線條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
  select id from t where num=10 or num=20
  可以這樣查詢：
  select id from t where num=10
  union all
  select id from t where num=20
• 7.5 慎用 in 和 not in
  in 和 not in 也要慎用，否則會導致全表掃描，如：
  select id from t1 where num in(select id from t2 where id > 10)
  此時外層查詢會全表掃描，不使用索引。可以修改為：
  select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
  此時索引被使用，可以明顯提升查詢效率。
• 7.6 注意模糊查詢
  下面的查詢也將導致全表掃描：
  select id from t where name like '%abc%'
  模糊查詢如果是必要條件時，可以使用 select id from t where name like 'abc%'來實現模糊查詢，此時索引將被使用。如果頭匹配是必要邏輯，建議使用全文搜尋引擎 （ Elastic search等）。
• 7.7 避免查詢條件中欄位計算
  應儘量避免在 where 子句中對欄位進行表示式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
  select id from t where num/2=100
  應改為:
  select id from t where num=100*2
• 7.8 避免查詢條件中對欄位進行函式操作
  應儘量避免在 where 子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
  select id from t where substring(name,1,3)='abc' #name 以abc開頭的 id
  應改為:
  select id from t where name like 'abc%'
• 7.9 WHERE 子句" = "左邊注意點
  不要在 where 子句中的 " = " 左邊進行函式、算術運算或其他表示式運算，否則系統
  將可能無法正確使用索引。
• 7.10 組合索引使用
  在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。
• 7.11 exists
  很多時候用 exists 代替 in 是一個好的選擇：
  select num from a where num in(select num from b)
  用下面的語句替換：
  select num from a where exists(select 1 from b where num=a.num)
• 7.12 索引也可能失效
  並不是所有索引對查詢都有效，SQL 是根據表中資料來進行查詢優化的，當索引列有大量資料重複時，SQL 查詢可能不會去利用索引，如一表中有欄位 sex、male、female幾乎各一半，那麼即使在 sex 上建了索引也對查詢效率起不了作用。
• 7.13 表格欄位型別選擇
  儘量使用數字型欄位，若只含數值資訊的欄位儘量不要設計為字元型， 這會降低查詢和連線的效能，並會增加儲存開銷。 這是因為引擎在處理查詢和連線時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。儘可能的使用 varchar 代替 char，因為首先可變長度欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。
• 7.14 查詢語法中的欄位
  任何地方都不要使用 select * from t ，用具體的欄位列表代替 " * "，不要返回用不到的任何欄位。