MySQL數(shù)據(jù)庫優(yōu)化讓數(shù)據(jù)庫讀取更快

本文的內(nèi)容是總結(jié)一些MySQL的常見使用技巧，以供沒有DbA的團(tuán)隊(duì)參考。以下內(nèi)容以MySQL5.5為準(zhǔn)，如無特殊說明，存儲(chǔ)引擎以InnoDb為準(zhǔn)。

MySQL的特點(diǎn)

了解MySQL的特點(diǎn)有助于更好的使用MySQL，MySQL和其它常見數(shù)據(jù)庫大的不同在于存在存儲(chǔ)引擎這個(gè)概念，存儲(chǔ)引擎負(fù)責(zé)存儲(chǔ)和讀取數(shù)據(jù)。不同的存儲(chǔ)引擎具有不同的特點(diǎn)，用戶可以根據(jù)業(yè)務(wù)的特點(diǎn)選擇適合的存儲(chǔ)引擎，甚至是開發(fā)一個(gè)新的引擎。MySQL的邏輯架構(gòu)大致如下：

MySQL默認(rèn)的存儲(chǔ)引擎是InnoDb，該存儲(chǔ)引擎的主要特點(diǎn)是：

• 支持事務(wù)處理

• 支持行級(jí)鎖

• 數(shù)據(jù)存儲(chǔ)在表空間中，表空間由一些列數(shù)據(jù)文件組成

• 采用MVVC(多版本并發(fā)控制)機(jī)制實(shí)現(xiàn)高并發(fā)

• 表基于主鍵的聚簇索引建立

• 支持熱備份

其它常見存儲(chǔ)引擎特點(diǎn)概述：

• MyISAM：老版本MySQL的默認(rèn)引擎，不支持事務(wù)和行級(jí)鎖，開發(fā)者可以手動(dòng)控制表鎖；支持全文索引；崩潰后無法安全恢復(fù)；支持壓縮表，壓縮表數(shù)據(jù)不可修改，但占用空間較少，可以提高查詢性能

• Archive：只支持Insert和Select，批量插入很快，通過全表掃描查詢數(shù)據(jù)

• SCV：把一個(gè)SCV文件當(dāng)做一個(gè)表處理

• Memory：數(shù)據(jù)存儲(chǔ)在內(nèi)存中

還有很多，不再一一列舉。

數(shù)據(jù)類型優(yōu)化

選擇數(shù)據(jù)類型的原則：

• 選擇占用空間小的數(shù)據(jù)類型

• 選擇簡(jiǎn)單的類型

• 避免不必要的可空列

占用空間小的類型更節(jié)省硬件資源，如磁盤、內(nèi)存和CpU。盡量使用簡(jiǎn)單的類型，如能用int就不用char，因?yàn)楹笳叩呐判蛏婕暗阶址倪x擇，比使用int復(fù)雜?？煽樟惺褂酶嗟拇鎯?chǔ)空間，如果在可空列上創(chuàng)建索引，MySQL需要額外的字節(jié)做記錄。創(chuàng)建表時(shí)，默認(rèn)都是可空，容易被開發(fā)者忽視，最好是手動(dòng)改為不可空，如果要存儲(chǔ)的數(shù)據(jù)確實(shí)不會(huì)有空值的話。

整型類型

整型類型包括：

• tinyint

• smallint

• mediumint

• int

• bigint

它們分別使用8、16、24、32和64位存儲(chǔ)數(shù)字，它們可以表示范圍的數(shù)字，前面可以加unsigned修飾，這樣可以讓正數(shù)的可表示范圍提高1倍，但是無法表示負(fù)數(shù)。另外，為整型指定長(zhǎng)度沒什么卵用，數(shù)據(jù)類型定下來，長(zhǎng)度也就相應(yīng)定下來了。

小數(shù)類型

• float

• double

• decimal

float和double就是通常意義上的float和double，前者使用32位存儲(chǔ)數(shù)據(jù)，后者使用64位存儲(chǔ)數(shù)據(jù)，和整型一樣，為它們指定長(zhǎng)度沒什么卵用。

decimal類型比較復(fù)雜，支持精確計(jì)算，占用的空間也大，decimal使用每4個(gè)字節(jié)表示9個(gè)數(shù)字，如decimal(18,9)表示數(shù)字長(zhǎng)度是18，其中小數(shù)位9個(gè)數(shù)字，整數(shù)部分9個(gè)數(shù)字，加上小數(shù)點(diǎn)本身，共占用9個(gè)字節(jié)?？紤]到decimal占用空間較多，以及精度計(jì)算很復(fù)雜，數(shù)據(jù)量大的時(shí)候可以考慮用bigint代替之，可以在持久化和讀取前對(duì)真實(shí)數(shù)據(jù)進(jìn)行一些縮放操作。

字符串類型

• varchar

• char

• varbinary

• binary

• blob

• text

• 枚舉

varchar類型數(shù)據(jù)實(shí)際占用空間等于字符串的長(zhǎng)度加上1個(gè)或2個(gè)用來記錄字符串長(zhǎng)度的字節(jié)（當(dāng)row-format沒有被設(shè)置為fixed時(shí)），varchar很節(jié)省空間。當(dāng)表中某列字符串類型的數(shù)據(jù)長(zhǎng)度差別較大時(shí)適合使用varchar。

char的實(shí)際占用空間是固定的，當(dāng)表中字符串?dāng)?shù)據(jù)的長(zhǎng)度相差無幾或很短時(shí)適合使用chart類型。

與varchar和char對(duì)應(yīng)的有varbinary和binary，后者存儲(chǔ)的是二進(jìn)制字符串，和前者相比，后者大小寫敏感，不用考慮編碼方式，執(zhí)行比較操作時(shí)更快。

需要注意的是：雖然varchar(5)和varchar(200)在存儲(chǔ)“hello”這個(gè)字符串時(shí)使用相同的存儲(chǔ)空間，但并不意味著將varchar的長(zhǎng)度設(shè)置太大不會(huì)影響性能，實(shí)際上，MySQL的某些內(nèi)部計(jì)算，比如創(chuàng)建內(nèi)存臨時(shí)表時(shí)（某些查詢會(huì)導(dǎo)致MySQL自動(dòng)創(chuàng)建臨時(shí)表），會(huì)分配固定大小的空間存放數(shù)據(jù)。

blob使用二進(jìn)制字符串保存大文本，text使用字符保存大文本，InnoDb會(huì)使用專門的外部存儲(chǔ)區(qū)來存放此類數(shù)據(jù)，數(shù)據(jù)行內(nèi)僅存放指向他們的指針，此類數(shù)據(jù)不宜創(chuàng)建索引（要?jiǎng)?chuàng)建也只能正對(duì)字符串前綴創(chuàng)建），不過也不會(huì)有人這么干。

如果某列字符串大量重復(fù)且內(nèi)容有限，可使用枚舉代替，MySQL處理枚舉時(shí)維護(hù)了一個(gè)“數(shù)字-字符串”表，使用枚舉可以減少很多存儲(chǔ)空間。

時(shí)間類型

• year

• date

• time

• datetime

• timestamp

datetime存儲(chǔ)范圍是1001到9999，精確到秒。timestamp存儲(chǔ)1970年1月1日午夜以來的秒數(shù)，可以表示到2038年。占用4個(gè)字節(jié)，是datetime占用空間的一半。timestamp表示的時(shí)間和時(shí)區(qū)有關(guān)，另外timestamp列還有個(gè)特性，執(zhí)行insert或update語句時(shí)，MySQL會(huì)自動(dòng)更新第一個(gè)類型為timestamp的列的數(shù)據(jù)為當(dāng)前時(shí)間。很多表中都有設(shè)計(jì)有一列叫做UpdateTime，這個(gè)列使用timestamp倒是挺合適的，會(huì)自動(dòng)更新，前提是系統(tǒng)不會(huì)使用到2038年。

主鍵類型的選擇

盡可能使用整型，整型占用空間少，還可以設(shè)置為自動(dòng)增長(zhǎng)。尤其別使用GUID，MD5等哈希值字符串作為主鍵，這類字符串隨機(jī)性很大，由于InnoDb主鍵默認(rèn)是聚簇索引列，所以導(dǎo)致數(shù)據(jù)存儲(chǔ)太分散。另外，InnoDb的二級(jí)索引列中默認(rèn)包含主鍵列，如果主鍵太長(zhǎng)，也會(huì)使得二級(jí)索引很占空間。

特殊類型的數(shù)據(jù)

存儲(chǔ)Ip最好使用32位無符號(hào)整型，MySQL提供了函數(shù)inet_aton()和inet_ntoa()進(jìn)行Ip地址的數(shù)字表示和字符串表示之間的轉(zhuǎn)換。

索引優(yōu)化

InnoDb使用b+樹實(shí)現(xiàn)索引，舉個(gè)例子，假設(shè)有個(gè)people，建表語句如下

CREATE table `people` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(5) NOT NULL,
  `Age` tinyint(4) NOT NULL,
  `Number` char(5) NOT NULL COMMENT '編號(hào)',
  pRIMARY KEY (`Id`),
  KEY `i_name_age_number` (`Name`,`Age`,`Number`)
) ENGINE=InnoDb AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

插入數(shù)據(jù)：

它的索引結(jié)構(gòu)大致是這樣的：

也就是說，索引列的順序很重要，如果兩行數(shù)據(jù)的Name列相同，則用Age列比較大小，如果Age列相同，則用Number列比較大小。先用第一列排序，然后是第二列，最后是第三列。

查詢的

本文題目：MySQL數(shù)據(jù)庫優(yōu)化讓數(shù)據(jù)庫讀取更快
鏈接地址：http://www.bm7419.com/news43/292093.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供移動(dòng)網(wǎng)站建設(shè)、用戶體驗(yàn)、ChatGPT、虛擬主機(jī)、域名注冊(cè)、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)