天天看點
傳回

記錄mysql優化資料庫設計資料庫結構優化mysql索引優化mysql的sql語句優化mysql開發技巧

資料庫設計

需求分析

資料庫優良設計：

A、減少資料備援

B、避免資料維護異常

C、節約存儲空間

D、高效的通路

資料庫設計的步驟：

A、需求分析（資料分析）

B、邏輯設計（ER圖）

C、實體設計（資料庫特點轉換）

D、維護優化（需求建表、索引優化，大表拆分）

資料庫結構優化

選擇合适的資料類型

選擇合适的資料類型

  1. 使用可存下資料的最小的資料類型
  2. 使用簡單地資料類型，Int要比varchar類型在mysql處理上更簡單
  3. 盡可能使用not null定義字段，這是由innodb的特性決定的，

    因為非not null的資料可能需要一些額外的字段進行存儲，這樣就會增加一些IO。 可以對非null的字段設定一個預設值

  4. 盡量少用text，非用不可最好分表，

    将text字段存放到另一張表中，在需要的時候再使用聯合查詢，這樣可提高查詢主表的效率

例子1、用Int存儲日期時間

from_unixtime()可将Int類型的時間戳轉換為時間格式

select from_unixtime(1392178320); 輸出為 2014-02-12 12:12:00
unix_timestamp()可将時間格式轉換為Int類型
select unix_timestamp('2014-02-12 12:12:00'); 輸出為1392178320

例子2

存儲IP位址——bigInt
利用inet_aton(),inet_ntoa()轉換
select inet_aton('192.169.1.1'); 輸出為3232301313
select inet_ntoa(3232301313); 輸出為192.169.1.1

表的垂直拆分和水準拆分

表的垂直拆分的原則

所謂垂直拆分，就是把原來一個有很多列的表拆分成多個表解決表的寬度問題，通常拆分原則如下：

  1. 把不常用的字段單獨存放到一個表中
  2. 把大字段獨立存放到一個表中
  3. 把經常一起使用的字段放到一起

垂直拆分:一個表的列太多,可以分為多個表

水準拆分:一個表中的資料太多,分多表結構不變

為了解決單表資料量過大的問題，每個水準拆分表的結構完全一緻

方法

  1. 哈希取模：hash(key) % N；
  2. 範圍：可以是 ID 範圍也可以是時間範圍；
  3. 映射表：使用單獨的一個資料庫來存儲映射關系

range 來分，好處在于說，擴容的時候很簡單，因為你隻要預備好，給每個月都準備一個庫就可以了，到了一個新的月份的時候，自然而然，就會寫新的庫了；缺點，但是大部分的請求，都是通路最新的資料。實際生産用 range，要看場景。

hash 分發，好處在于說，可以平均配置設定每個庫的資料量和請求壓力；壞處在于說擴容起來比較麻煩，會有一個資料遷移的過程，之前的資料需要重新計算 hash 值重新配置設定到不同的庫或表。

水準拆分之後的挑戰

  1. 跨分區進行資料查詢
  2. 統計及背景報表操作

前背景使用的表進行分開，前台要求查詢效率，是以可以說會用拆分之後的表，背景在統計資料時可以使用彙總表

mysql索引優化

如何選擇合适的列履歷索引

  1. 在進行查詢時，索引列不能是表達式的一部分，也不能是函數的參數，否則無法使用索引
  2. 在需要使用多個列作為條件進行查詢時，使用多列索引比使用多個單列索引性能更好。例如下面的語句中，最好把 actor_id 和 film_id 設定為多列索引
  3. 讓選擇性最強的索引列放在前面。索引的選擇性是指：不重複的索引值和記錄總數的比值。最大值為 1，此時每個記錄都有唯一的索引與其對應。選擇性越高，查詢效率也越高。（選擇性對應的是下面的離散值）
  4. 對于 BLOB、TEXT 和 VARCHAR 類型的列，必須使用字首索引，隻索引開始的部分字元。
  5. 索引包含所有需要查詢的字段的值。

選擇合适的索引列

  1. 在where，group by，order by，on從句中出現的列
  2. 索引字段越小越好(因為資料庫的存儲機關是頁，一頁中能存下的資料越多越好 )
  3. 離散度大得列放在聯合索引前面

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

檢視離散度 通過統計不同的列值來實作 count越大 離散程度越高

離散度，我的了解就是唯一性了，比如主鍵，絕對是離散度最大的，而一些用來辨別狀态辨別的列，基本隻有幾個可選項，離散度就很小

索引維護的方法

通過統計資訊庫information_schma查找一些重複備援的索引,

  1. 通過查詢統計資訊表

  2. 通過拿用第三方的統計工具pt-duplicate-key-checker(使用者名,密碼,資料庫伺服器ip),會給出一些優化建議.

    如果因為業務變更一些索引已經未使用Mysql目前隻能使用慢查詢日志配合pt-index-usage來進行index使用分析

  3. 過多的索引不僅影響增加、修改、删除資料的效率，而且也影響查詢的效率， 這是因為查詢的時候資料庫需要選擇使用索引進行查詢呢,那麼需要更合理的使用索引(增加合适的索引、删除重複的索引)
  4. 過多的索引不但影響寫入，而且影響查詢，索引越多，分析越慢如何找到重複和多餘的索引，主鍵已經是索引了，是以primay key 的主鍵不用再設定unique唯一索引了 備援索引，是指多個索引的字首列相同，innodb會在每個索引後面自動加上主鍵資訊
  5. 備援索引查詢工具 pt-duplicate-key-checker

mysql的sql語句優化

mysql慢查日志

一 通過慢查日志記錄帶索引的sql語句 進行sql優化

1)檢視mysql是否開啟慢查詢日志

show variables like 'slow_query_log';

2)設定沒有索引的記錄到慢查詢日志

set global log_queries_not_using_indexes=on;

3)檢視超過多長時間的sql進行記錄到慢查詢日志

show variables like 'long_query_time'

4)開啟慢查詢日志

set global slow_query_log=on

mysql慢查日志分析工具

用mysql官方提供的日志分析工具檢視慢日志

mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

linux系統下如果使用mysqldumpslow出現報錯:

-bash: mysqldumpslow: command not found的話,建立連結即可: ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

mysqldumpslow 在windows中是一個perl檔案，是以需要你配置perl環境變量并使用perl運作

mysqldumpslow.pl

通過慢查日志發現問題

  1. 查詢次數多且每次查詢占用時間長的SQL,通常為pt-query-digest分析的前幾個查詢
  2. IO大的SQL,注意pt-query-digest分析中的Rows examine項
  3. 未命中索引的SQL,注意pt-query-digest分析中的Rows examine和Row send 的對比

使用explain查詢和分析語句

傳回各列的含義

table：顯示這一行的資料是關于哪張表的

type:這是重要的列,顯示連接配接使用了何種類型。從最好到最差的連接配接類型為const、eq_reg、ref、range、index和ALL

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。

key:實際使用的索引。如果為NULL，則沒有使用索引。

key_len:使用的索引的長度。在不損失精确性的情況下，長度越短越好。

ref:顯示索引的哪一列被使用了,如果可能的話，是一個常數

rows：MYSQL認為必須檢查的用來傳回請求資料的行數

extra列需要注意的傳回值

Using filesort:看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對傳回的行排序。它根據連接配接類型以及存儲排序鍵值和比對條件的全部行的行指針來排序全部行

Using temporary:看到這個的時候，查詢需要優化了。這裡，MYSQL徐喲建立一個臨時表來存儲接口，這通常發生在對不同的清單進行ORDER BY上，而不是GROUP 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;
  1. count(*) 查詢的結果中，包含了該列值為null的結果

子查詢一般優化成join的查詢方式，同時需考慮關聯鍵是否存在一對多的關系

如果存在一對多的關系，則可以使用distinct關鍵字去重.

limit常用于分頁處理，時常會伴随order by從句使用，是以大多時候會使用Filesorts這樣會造成大量的io問題

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

  2. 記錄上次傳回的主鍵，在下次查詢時使用主鍵過濾

    使用這種方式有一個限制，就是主鍵一定要順序排序和連續的，如果主鍵出現空缺可能會導緻最終頁面上顯示的清單不足5條，解決辦法是附加一列，保證這一列是自增的并增加索引就可以了

mysql開發技巧

DDL：資料定義語言 — CREATE、ALTER、DROP、TRUNCATE

TPL：事務處理語言 — COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION

DCL：資料控制語言 — GRANT、REVOKE

DML：資料操作語言 — SELECT、UPDATE、INSERT、DELETE

算了，各種join和groupby什麼的就不舉例子了，寫了一下，發現太啰嗦了。

基礎的用法在這

https://github.com/CyC2018/CS-Notes/blob/master/docs/notes/SQL.md

想要掌握的話，還是去LeetCode裡面練習吧

https://leetcode-cn.com/problemset/database/

參考資料：

https://github.com/CyC2018/CS-Notes/blob/master/docs/notes/MySQL.md

資料庫 mysql優化
上一篇: MySQL性能優化經驗分享
下一篇: 優化mysql經驗

繼續閱讀