包含mysql數據庫設計,MySQL數據庫設計與應用

本文目錄一覽：

• 1、MYSQL數據庫的物理設計都包括哪些內容，怎麼設計？
• 2、超詳細MySQL數據庫優化
• 3、北大青鳥設計培訓：Mysql數據庫的設計和優化？
• 4、有沒有好用的mysql數據庫設計工具
• 5、Mysql數據庫設計

MYSQL數據庫的物理設計都包括哪些內容，怎麼設計？

Log File物理結構

從 ib_logfile0和 ib_logfile1這兩個文件的物理結構可以看出，在Log Header部分還是有些許差異的， ib_logfile0會多一些額外的信息，主要是checkpoint信息。

並且每個Block的單位是512位元組，對應到磁盤每個扇區也是512位元組，因此redo log寫磁盤是原子寫，保證能夠寫成功，而不像index page一樣需要double write來保證安全寫入。

我們依次從上到下來看每個Block的結構

Log File Header Block

Log Goup ID，可能會配置多個redo組，每個組對應一個id，當前都是0，佔用4位元組

Start LSN，這個redo log文件開始日誌的lsn，佔用8位元組

Log File Number，總是為0，佔用4位元組

Created By，備份程序所佔用的位元組數，佔用32位元組

另外在ib_logfile0中會有兩個checkpoint block，分別是 LOG_CHECKPOINT_1/ LOG_CHECKPOINT_2，兩個記錄InnoDB Checkpoint信息的字段，分別從文件頭的第二個和第四個block開始記錄，並且只在每組log的第一個文件中存在，組內其他文件雖然沒有checkpoint相關信息，但是也會預留相應的空間出來。這裡為什麼有兩個checkpoint的呢？原因是設計為交替寫入，避免因為介質失敗而導致無法找到可用的checkpoint的情況。

Log blocks

請點擊輸入圖片描述

log block結構分為日誌頭段、日誌記錄、日誌尾部

Block Header，佔用12位元組

Data部分

Block tailer，佔用4位元組

Block Header

這個部分是每個Block的頭部，主要記錄的塊的信息

Block Number，表示這是第幾個block，佔用4位元組，是通過LSN計算得來的，佔用4位元組

Block data len，表示該block中有多少位元組已經被使用了，佔用2位元組

First Rec offet，表示該block中作為第一個新的mtr開始的偏移量，佔用2位元組

Checkpoint number，表示該log block最後被寫入時的檢查點的值，佔用4位元組

超詳細MySQL數據庫優化

數據庫優化一方面是找出系統的瓶頸,提高MySQL數據庫的整體性能,而另一方面需要合理的結構設計和參數調整,以提高用戶的相應速度,同時還要儘可能的節約系統資源,以便讓系統提供更大的負荷.

1. 優化一覽圖

2. 優化

筆者將優化分為了兩大類,軟優化和硬優化,軟優化一般是操作數據庫即可,而硬優化則是操作服務器硬件及參數設置.

2.1 軟優化

2.1.1 查詢語句優化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執行信息.

2.例:

顯示:

其中會顯示索引和查詢數據讀取數據條數等信息.

2.1.2 優化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統開銷,而連接查詢不會創建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數據庫查詢速度最重要的方法之一,關於索引可以參高筆者MySQL數據庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

對於字段較多的表,如果某些字段使用頻率較低,此時應當,將其分離出來從而形成新的表,

2.1.5 中間表

對於將大量連接查詢的表可以創建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗餘字段

類似於創建中間表,增加冗餘也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優化表

分析表主要是分析表中關鍵字的分佈,檢查表主要是檢查表中是否存在錯誤,優化表主要是消除刪除或更新造成的表空間浪費.

1. 分析表: 使用 ANALYZE 關鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數值:

3. 優化表:使用OPTIMIZE關鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日誌.,優化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執行過程中會加上只讀鎖.

2.2 硬優化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執行多個線程.

2.配置大內存,提高內存,即可提高緩存區容量,因此能減少磁盤I/O時間,從而提高響應速度.

3.配置高速磁盤或合理分佈磁盤:高速磁盤提高I/O,分佈磁盤能提高並行操作的能力.

2.2.2 優化數據庫參數

優化數據庫參數可以提高資源利用率,從而提高MySQL服務器性能.MySQL服務的配置參數都在my.cnf或my.ini,下面列出性能影響較大的幾個參數.

2.2.3 分庫分表

因為數據庫壓力過大，首先一個問題就是高峰期系統性能可能會降低，因為數據庫負載過高對性能會有影響。另外一個，壓力過大把你的數據庫給搞掛了怎麼辦？所以此時你必須得對系統做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數據庫服務上，這時作為主庫承載寫入請求。然後每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機器，比如說系統層面不停加機器，就可以承載更高的並發請求。然後數據庫層面如果寫入並發越來越高，就擴容加數據庫服務器，通過分庫分表是可以支持擴容機器的，如果數據庫層面的讀並發越來越高，就擴容加更多的從庫。但是這裡有一個很大的問題：數據庫其實本身不是用來承載高並發請求的，所以通常來說，數據庫單機每秒承載的並發就在幾千的數量級，而且數據庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高並發架構里通常都有緩存這個環節，緩存系統的設計就是為了承載高並發而生。所以單機承載的並發量都在每秒幾萬，甚至每秒數十萬，對高並發的承載能力比數據庫系統要高出一到兩個數量級。所以你完全可以根據系統的業務特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數據庫的時候同時寫一份數據到緩存集群里，然後用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的並發。

一個完整而複雜的高並發系統架構中，一定會包含：各種複雜的自研基礎架構系統。各種精妙的架構設計.因此一篇小文頂多具有拋磚引玉的效果,但是數據庫優化的思想差不多就這些了.

北大青鳥設計培訓：Mysql數據庫的設計和優化？

在JAVA開發中數據庫的學習也是我們需要了解的，截下來幾篇文章都是關於數據庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！  數據庫的設計  數據庫設計是基礎，數據庫優化是建立在設計基礎之上的。

好的數據庫一定擁有好的設計。

  數據庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。

  數據庫的三大範式  第一範式1NF:所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

  第二範式2Nf:第二範式在第一範式的基礎之上更進一層。

第二範式需要確保數據庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。

也就是說在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。

  第三範式3Nf:所有字段必須與主鍵直接相關，而不是間接相關。

也可以理解為字段不要和其他非主鍵字段相關.  注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使字段盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反.  數據庫設計的步驟  需求分析階段  準確了解與分析用戶需求(包括數據與處理)。

是整個設計過程的基礎，是最困難、最耗費時間的一步。

  概念結構設計階段  是整個數據庫設計的關鍵–設計數據庫的E-R模型圖，確認需求信息的正確和完整  Entity_Relationship—實體之間的關係  一對一  一對多  多對一

有沒有好用的mysql數據庫設計工具

下面推薦兩款：

一、Navcat：

Navicat 是一套快速、可靠並價格相宜的數據庫管理工具，專為簡化數據庫的管理及降低系統管理成本而設。它的設計符合數據庫管理員、開發人員及中小企業的需要。Navicat 是以直覺化的圖形用戶界面而建的，讓你可以以安全並且簡單的方式創建、組織、訪問並共用信息。

Navicat是聞名世界、廣受全球各大企業、政府機構、教育機構所信賴，更是各界從業員每天必備的工作夥伴。自2001 年以來，Navicat 已在全球被下載超過 2,000,000 次，並且已有超過 70,000 個用戶的客戶群。《財富》世界 500 強中有超過 100 家公司也都正在使用 Navicat。

二、MySQL Workbench：

MySQL Workbench是一款圖形化的數據庫設計工具，它在一個開發環境中集成了SQL的開發，管理，數據庫設計，創建以及維護。它是 fabFORCE.net旗下DBDesigner 4的繼承者，並替代了MySQL GUI Tools Bundle。當前版本是5.2，最早版本是5.0，這是為了強調MySQL Workbench是作為DBDesigner4的繼承者被開發出來的事實。

Mysql數據庫設計

簡單用戶表 tb_user:

userid , username

用戶詳細信息表 tb_userinfo

userid , email , homepage , phone , address

把用戶信息分開的目的就是保證經常查詢的數據在一張表，其它信息放到另一張表

論壇主題表 tb_bbs

bbsid , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime

論壇內容標 tb_bbs_content (此表可按照bbsid進行分表存儲)

bbsid , content;

論壇回復表 tb_bbs_reply (此表可按照bbsid進行分表存儲)

replyid , bbsid , userid , content , replytime , ip