1. SQL優(yōu)化很有必要

數(shù)據(jù)庫優(yōu)化在提升系統(tǒng)性能是很重要的一個方面，不管是MySQL還是MongoDB還是其它的數(shù)據(jù)庫。
SQL優(yōu)化在提升系統(tǒng)性能中是成本最低 && 優(yōu)化效果最明顯的途徑，可以讓吞吐量更大，響應速度更快。如果你的團隊在SQL優(yōu)化這方面搞得很優(yōu)秀，對你們整個大型系統(tǒng)可用性方面無疑是一個質的跨越，，真的能讓你們老板省下不止幾沓子錢。
在我遇到的項目中就遇到過這樣的問題，數(shù)據(jù)庫數(shù)據(jù)量太大，導致查詢數(shù)據(jù)超時，多個模塊都無法正常提供服務，臨時的解決方法是刪掉老數(shù)據(jù)，但終究治標不治本。

2. SQL優(yōu)化的方向

優(yōu)化成本：硬件>系統(tǒng)配置>數(shù)據(jù)庫表結構>SQL及索引。
優(yōu)化效果：硬件<系統(tǒng)配置<數(shù)據(jù)庫表結構<SQL及索引。

因此：數(shù)據(jù)庫優(yōu)化從以下幾個方面優(yōu)化：

• SQL 調優(yōu)
• 數(shù)據(jù)庫索引
• 定時清除不需要的數(shù)據(jù)，定時進行碎片整理
• 數(shù)據(jù)庫設計—三大范式、字段、表結構
• 分表分庫 (水平分割，垂直分割)
• 對 MySQL 配置優(yōu)化 (配置最大并發(fā)數(shù) my.ini, 調整緩存大小)
• 存儲過程 (模塊化編程，可以提高速度)
• 主從復制、讀寫分離
• 等

2.1. SQL語句調優(yōu)

SQL性能下降原因：
1、查詢語句寫的爛
2、索引失效（數(shù)據(jù)變更）
3、關聯(lián)查詢太多join（設計缺陷或不得已的需求）
4、服務器調優(yōu)及各個參數(shù)設置（緩沖、線程數(shù)等）

通常SQL調優(yōu)過程：

• 觀察，至少跑1天，看看生產(chǎn)的慢SQL情況。
• 開啟慢查詢日志，設置闕值，比如超過5秒鐘的就是慢SQL，并將它抓取出來，并存到日志中 (在 my.ini 可以指定慢查詢日志目錄)。
• explain +慢SQL分析。
• show profile查詢SQL在Mysql服務器里面的執(zhí)行細節(jié)和生命周期情況。
• 運維經(jīng)理 or DBA，進行SQL數(shù)據(jù)庫服務器的參數(shù)調優(yōu)。
• 查看優(yōu)化后的執(zhí)行時間和執(zhí)行計劃，如果優(yōu)化效果不明顯，重復

2.2. SQL索引

索引也算數(shù)據(jù)庫設計的一部分

1.一般來說，應該在這些列上創(chuàng)建索引：

在經(jīng)常需要搜索的列上，可以加快搜索的速度；
在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結構；
在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；
在經(jīng)常需要**排序的列（group by 或者 order by）**上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
在經(jīng)常使用在 WHERE 子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
總結就是：唯一、不為空、經(jīng)常被查詢的字段

2.對于有些列不應該創(chuàng)建索引：

對于那些在查詢中很少使用或者參考的列不應該創(chuàng)建索引。
對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。
對于那些定義為 text, image 和 bit 這種數(shù)據(jù)量很大的數(shù)據(jù)類型的列不應該增加索引。
當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。
3.索引失效

在以下這些情況種，執(zhí)行引擎將放棄使用索引而進行全表掃描

在 where 子句中使用**！= 或 <> 操作符**

在 where 子句中使用 or 來連接條件，當連接的字段有字段沒有索引時，將導致所有字段的索引失效

在 where 子句字段進行 null 值判斷，

在 where 子句中 like 的模糊匹配以 % 開頭

在 where 子句中對索引進行表達式運算或函數(shù)操作

如果執(zhí)行引擎估計使用全表掃描要比使用索引快，則不使用索引

2.3. SQL設計三大范式

（一）數(shù)據(jù)庫設計—三大范式、字段、表結構

1.根據(jù)數(shù)據(jù)庫三范式來進行表結構的設計。設計表結構時，就需要考慮如何設計才能更有效的查詢。

第一范式：數(shù)據(jù)表中每個字段都必須是不可拆分的最小單元，也就是確保每一列的原子性；
第二范式：滿足一范式后，表中每一列必須有唯一性，都必須依賴于主鍵；
第三范式：滿足二范式后，表中的每一列只與主鍵直接相關而不是間接相關 (外鍵也是直接相關)，字段沒有冗余。
2.其他：

盡量使用 TINYINT、SMALLINT、MEDIUM_INT 作為整數(shù)類型而非 INT，如果非負則加上 UNSIGNED
VARCHAR 的長度只分配真正需要的空間
盡量使用整數(shù)代替字符串類型
單表不要有太多字段，建議在 20 以內
避免使用 NULL 字段，很難查詢優(yōu)化且占用額外索引空間
不建議使用 select * from t ，用具體的字段列表代替 “”，不要返回用不到的任何字段。盡量避免向客戶 端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應該考慮相應需求是否合理
表與表之間通過一個冗余字段來關聯(lián)，要比直接使用 JOIN 有更好的性能
select count () from table；這樣不帶任何條件的 count 會引起全表掃描

2.4. 主從復制和讀寫分離

在實際的生產(chǎn)環(huán)境中，對數(shù)據(jù)庫的讀和寫都在同一個數(shù)據(jù)庫服務器中，是不能滿足實際需求的。無論是在安全性、高可用性還是高并發(fā)等各個方面都是完全不能滿足實際需求的。因此，通過主從復制的方式來同步數(shù)據(jù)，再通過讀寫分離來提升數(shù)據(jù)庫的并發(fā)負載能力。

作用：數(shù)據(jù)庫備份，讀寫分離，高可用，集群.

2.過程：

在每個事務更新數(shù)據(jù)完成之前，master 在二進制日志記錄這些改變。寫入二進制日志完成后，master 通知存儲引擎提交事務。

Slave 將 master 的 binary log 復制到其中繼日志。首先 slave 開始一個工作線程（I/O），I/O 線程在 master 上打開一個普通的連接，然后開始 binlog dump process。binlog dump process 從 master 的二進制日志中讀取事件，如果已經(jīng)跟上 master，它會睡眠并等待 master 產(chǎn)生新的事件，I/O 線程將這些事件寫入中繼日志。

Sql slave thread（sql 從線程）處理該過程的最后一步，sql 線程從中繼日志讀取事件，并重放其中的事件而更新 slave 數(shù)據(jù)，使其與 master 中的數(shù)據(jù)一致，只要該線程與 I/O 線程保持一致，中繼日志通常會位于 os 緩存中，所以中繼日志的開銷很小。

2.5. 分庫分表

分庫分表

主從復制中，從數(shù)據(jù)庫可以通過增加數(shù)量不斷擴張，但是主數(shù)據(jù)庫不能輕易增加，這個時候可以考慮分表分庫。

1.分表方式

水平分割（按行）、垂直分割 (按列)

垂直拆分：垂直拆分就是要把表按模塊劃分到不同的數(shù)據(jù)庫中，數(shù)據(jù)庫按模塊和功能把表劃分出來，趨向于服務化

水平切分主要是用于解決“數(shù)據(jù)庫數(shù)據(jù)量大的問題”

水平拆分：水平拆分就是要把一個表按照一定的規(guī)則把數(shù)據(jù)劃分到不同的表或數(shù)據(jù)庫中。比如按時間，賬號規(guī)則，年份，取模算法等.
2.分表場景

根據(jù)經(jīng)驗，mysql 表數(shù)據(jù)一般達到百萬級別，查詢效率就會很低。
一張表的某些字段值比較大并且很少使用。可以將這些字段隔離成單獨一張表，通過外鍵關聯(lián)，例如考試成績，我們通常關注分數(shù)，不關注考試詳情。
3.水平分表策略

按時間分表：當數(shù)據(jù)有很強的實效性，例如微博的數(shù)據(jù)，可以按月分割。
按區(qū)間分表：例如用戶表 1 到一百萬用一張表，一百萬到兩百萬用一張表。
hash 分表：通過一個原始目標 id 或者是名稱按照一定的 hash 算法計算出數(shù)據(jù)存儲的表名。
4.分表缺點：
分頁查詢困難
查詢非常受限

2.6. 架構優(yōu)化

應用與數(shù)據(jù)庫之間增加一個緩存服務，如Redis或Memcache。

當接收到查詢請求后，我們先查詢緩存，判斷緩存中是否有數(shù)據(jù)，有數(shù)據(jù)就直接返回給應用，如若沒有再查詢數(shù)據(jù)庫，并加載到緩存中，這樣就大大減少了對數(shù)據(jù)庫的訪問次數(shù)，自然而然也提高了數(shù)據(jù)庫性能。不過需要注意的是，引入分布式緩存后系統(tǒng)需要考慮如何應對緩存穿透、緩存擊穿和緩存雪崩的問題。文章來源地址http://www.zghlxwxcb.cn/news/detail-642332.html

到了這里，關于面試八股文Mysql：（2）數(shù)據(jù)庫調優(yōu)的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！