《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列1：數(shù)據(jù)類型與索引調(diào)優(yōu)全解析》要點(diǎn)：
本文介紹了MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列1：數(shù)據(jù)類型與索引調(diào)優(yōu)全解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

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

數(shù)據(jù)類型

• 整數(shù)

數(shù)字類型：整數(shù)和實(shí)數(shù)

tinyint(8)、smallint(16)、mediuint(24)、int(32)、bigint(64) 數(shù)字表示對(duì)應(yīng)最大存儲(chǔ)位數(shù),如 tinyint (-127 --- 128),tinyint unsigned 表示不允許負(fù)數(shù),則范圍為 (0 -- 255).

常規(guī)數(shù)據(jù)庫(kù)中 int(11) 只是表示控制顯示字符的個(gè)數(shù)是11個(gè),int(1) 和 int(20) 存儲(chǔ)和計(jì)算是一樣的,即 int(1) 照樣可以存儲(chǔ)1111(4位數(shù)).

• 實(shí)數(shù)

實(shí)數(shù)有分?jǐn)?shù)部分

• float 和 double 類型支持使用標(biāo)準(zhǔn)的浮點(diǎn)運(yùn)算近似計(jì)算

• float 占用4個(gè)字節(jié) double占用8個(gè)字節(jié)

• decimal 類型用于保存精確的小數(shù)

• decimal(18,9) 18表示小數(shù)點(diǎn)前后總位數(shù) 9表示小數(shù)點(diǎn)后面位數(shù)

• mysql 5.0版本以上 4個(gè)字節(jié)保存9位數(shù)字

• decimal(18,9) 共占用9個(gè)字節(jié) 小數(shù)點(diǎn)前4個(gè)字節(jié) 小數(shù)點(diǎn)后占1個(gè) 小數(shù)點(diǎn)后4個(gè)字節(jié)

• 字符串類型

varchar和char類型

• varchar保存可變長(zhǎng)度的字符串,比固定長(zhǎng)度類型占用更少的存儲(chǔ)空間,只占用需要的空間.

  varchar使用額外的1到2字節(jié)存儲(chǔ)長(zhǎng)度,列小于255使用1字節(jié)保存長(zhǎng)度,大于255使用2字節(jié)保存,varchar保留字符串末尾的空格.

• char是固定長(zhǎng)度,保存char值時(shí)候 **mysql去掉任何末尾的空格** ,進(jìn)行比較時(shí) 空格會(huì)被填充到字符串末尾.

  很多的char列,效率高于varchar,比如 char(1)對(duì)于單字節(jié)字符集占用1字節(jié),varchar(1)占用兩字節(jié),因?yàn)?字節(jié)保存長(zhǎng)度.

慷慨不是明智的,分配真正需要的空間.

• Blob和Text類型

blob和text唯一區(qū)別就是blob保存二進(jìn)制數(shù)據(jù)、沒(méi)有字符集和排序規(guī)則.

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

• 更小通常越好

使用更少的磁盤、內(nèi)存、cpu,確保不會(huì)低估保存的值,但是text有字符集和排序規(guī)則.mysql不能索引這些數(shù)據(jù)類型的完整長(zhǎng)度,也不能為排序使用索引.

• 簡(jiǎn)單就好

比較整數(shù)的代價(jià)小于比較字符,使用mysql內(nèi)建類型保存時(shí)間和日期,使用整數(shù)保存ip.

• 盡量避免

mysql難以優(yōu)化可空列查詢,使固定索引(整數(shù)列上的索引)編程可變大小索引；沒(méi)有值可以使用 0 或者空字符串代替；把 列改為not 帶來(lái)的性能提升很小.

• 確定類型

像數(shù)字、字符串、時(shí)間、直觀類型可以確定,但是像 datetime 和timestamp,能保存同樣的類型.timestamp使用空間只有datetime一半.可以保存時(shí)區(qū).

• 使用enum代替字符串類型

enum列可以保存65535不同的字符串,存儲(chǔ)在一個(gè) "查找表"中 mysql內(nèi)部存儲(chǔ)的是列表中的位置.

內(nèi)部存儲(chǔ)的是這個(gè)字符串對(duì)應(yīng)的位置,實(shí)際表中存儲(chǔ)的還是字符串.

創(chuàng)建一個(gè)表fruit category字段為enum類型,包含4種不同水果：

插入4條數(shù)據(jù),即4中不同水果.其中,最后一個(gè)菠蘿(pineapple) 沒(méi)有enum值 則插入了空數(shù)據(jù).

發(fā)現(xiàn)字段category保存的還是字符串,其實(shí)內(nèi)部已經(jīng)將這些字符串關(guān)聯(lián)到enum字符的位置.

支持字符串搜索和位置搜索

emu缺點(diǎn)在于插入數(shù)據(jù)之前,如果沒(méi)有對(duì)應(yīng)enum,則需要alter表結(jié)構(gòu).

enum優(yōu)點(diǎn)在于占用更少的存儲(chǔ)空間.

據(jù)說(shuō) enum 用于聯(lián)接查詢性能也比較好.

• 日期和時(shí)間類型

datetime 保存是1001年到9999年,精度是秒,存儲(chǔ)值為 2016-05-06 22:39:40.

timestamp保存自 1970年1月1日午夜以來(lái)的秒數(shù),和unix時(shí)間戳相同,提供4字節(jié)存儲(chǔ) 只能表示1970年到2038年.默認(rèn)timestamp值 為 NOT .

mysql中提供 from_unixtime函數(shù)把unix時(shí)間戳轉(zhuǎn)換為日期

unix_timestamp把日期轉(zhuǎn)換為unix時(shí)間戳

如果需要秒以下的精度保存日期和時(shí)間,可以使用bigint類型把它以毫秒的精度保存時(shí)間戳格式,或使用double保存秒的分?jǐn)?shù)部分.

• 選擇標(biāo)識(shí)符

整數(shù)類型通常是標(biāo)識(shí)符最佳選擇,速度快,且能使用auto_increment,避免使用字符串做標(biāo)識(shí)符,占用很多空間并且比整數(shù)類型要慢.

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

通常使用varchar(15)保存IP地址,其實(shí)IP地址是無(wú)符號(hào)的32位整數(shù),不是字符串,小數(shù)點(diǎn)僅僅為了可讀性.

mysql提供了 inet_aton inet_ntoa ,用于 ip地址和整數(shù)之前轉(zhuǎn)換.

二、索引優(yōu)化

索引基礎(chǔ)知識(shí)

索引幫助mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu),索引(mysql中叫"鍵(key)") 數(shù)據(jù)越大越重要.索引好比一本書,為了找到書中特定的話題,查看目錄,獲得頁(yè)碼.

select fruit_name from fruit where id = 5 索引列位于id列,索引按值查找并且返回任何包含該值的行.

如果索引了多列數(shù)據(jù),那么列的順序非常重要.

存儲(chǔ)引擎說(shuō)明

• myisam 存儲(chǔ)引擎

• 表鎖：myisam 表級(jí)鎖

• 不支持自動(dòng)恢復(fù)數(shù)據(jù)：斷電之后 使用之前檢查和執(zhí)行可能的修復(fù)

• 不支持事務(wù)：不保證單個(gè)命令會(huì)完成, 多行update 有錯(cuò)誤 只有一些行會(huì)被更新

• 只有索引緩存在內(nèi)存中：mysiam只緩存進(jìn)程內(nèi)部的索引

• 緊密存儲(chǔ)：行被僅僅保存在一起

• Innodb存儲(chǔ)引擎

• 事務(wù)性：Innodb支持事務(wù)和四種事務(wù)隔離級(jí)別

• 外鍵：Innodb唯一支持外鍵的存儲(chǔ)引擎 create table 命令接受外鍵

• 行級(jí)鎖：鎖設(shè)定于行一級(jí) 有很好的并發(fā)性

• 多版本：多版本并發(fā)控制

• 按照主鍵聚集：索引按照主鍵聚集

• 所有的索引包含主鍵列：索引按照主鍵引用行 如果不把主鍵維持很短 索引就增長(zhǎng)很大

• 優(yōu)化的緩存：Innodb把數(shù)據(jù)和內(nèi)存緩存到緩沖池 自動(dòng)構(gòu)建哈希索引

• 未壓縮的索引：索引沒(méi)有使用前綴壓縮,阻塞auto_increment:Innodb使用表級(jí)鎖產(chǎn)生新的auto_increment

• 沒(méi)有緩存的count:myisam 會(huì)把行數(shù)保存在表中 Innodb中的count會(huì)全表或索引掃描

索引類型

索引在存儲(chǔ)引擎實(shí)現(xiàn)的,而不是服務(wù)層.

• B-tree 索引

大多數(shù)談及的索引類型就是B-tree類型, 可以在create table 和其他命令使用它 myisam使用前綴壓縮以減小索引,Innodb不會(huì)壓縮索引,myiam索引按照行存儲(chǔ)物理位置引用被索引的行,Innodb按照主鍵值引用行,B-tree數(shù)據(jù)存儲(chǔ)是有序的,按照順序保存了索引的列,加速了數(shù)據(jù)訪問(wèn),存儲(chǔ)引擎不會(huì)掃描整個(gè)表得到需要的數(shù)據(jù).

• B-tree 索引實(shí)例

使用B-tree索引的查詢類型,很好用于全鍵值、鍵值范圍或鍵前綴查找,只有在超找使用了索引的最左前綴的時(shí)候才有用.

匹配全名：全鍵值匹配和索引中的所有列匹配

查找叫Tang Kang 出生于 1991-09-23 的人

匹配最左前綴：B-tree找到姓為tang的人

匹配列前綴： 匹配某列的值的開(kāi)頭部分 查找姓氏以T開(kāi)頭的人

匹配范圍值：索引查找姓大于Tang小于zhu的人

精確匹配一部分并且匹配某個(gè)范圍的另外一部分：

查找姓為Tang并且名字以字母K開(kāi)頭的人 精確匹配last_name列并且對(duì)

first_name進(jìn)行范圍查詢

只訪問(wèn)索引的查詢：B-tree支持只訪問(wèn)索引的查詢,不會(huì)訪問(wèn)行

• B-tree局限性

B-tree局限性：(案例中索引順序:last_name first_name dob )

如果查找沒(méi)有送索引列的最左邊開(kāi)始,沒(méi)有什么用處,即不能查找所有叫Kang 的人,也不能找到所有出生在某天的人,因?yàn)檫@些列不再索引最左邊,也不能使用該索引超找某個(gè)姓氏以特定字符結(jié)尾的人.

不能跳過(guò)索引的列,即不能找到所有姓氏為Tang并且出生在某個(gè)特定日期的人,如果不定義first_name列的值,Mysql只能使用索引的第一列.

存儲(chǔ)引擎不能優(yōu)化任何在第一個(gè)范圍條件右邊的列,比如查詢是where last_name = 'Tang' AND first_name like 'K%' AND dob='1993-09-23' 訪問(wèn)只能使用索引頭兩列.

由此可知 索引列順序的重要性!

• 哈希索引

目前只有Memory存儲(chǔ)引擎支持顯示的哈希索引,而且Memory引擎對(duì)我來(lái)說(shuō)不常用,所以我們就輕描淡寫的過(guò)了吧.

• R-tree(空間索引)

Myisam支持空間索引,可以使用geometry空間數(shù)據(jù)類型.

空間索引不會(huì)要求where子句使用索引最左前綴可以全方位索引數(shù)據(jù),可以高效使用任何數(shù)據(jù)組合查找 配合使用mercontains函數(shù)使用.

• 全文索引

fulltext是Myisam表特殊索引,從文本中找關(guān)鍵字不是直接和索引中的值進(jìn)行比較.

全文索引可以和B-Tree索引混用,索引價(jià)值互不影響.

全文索引用于match against操作 而不是普通的where子句.

• 前綴索引和索引選擇性

通常索引幾個(gè)字符,而不是全部值,以節(jié)約空間并得到好的性能,同時(shí)也降低選擇性.

索引選擇性是不重復(fù)的索引值和全部行數(shù)的比值.高選擇性的索引有好處,查找匹配過(guò)濾更多的行,唯一索引選擇率為1最佳狀態(tài).

blob列、text列及很長(zhǎng)的varchar列,必須定義前綴索引,mysql不允許索引他們的全文.

• 前綴索引和索引選擇性實(shí)例

造數(shù)據(jù)

#復(fù)制一份與cs_area表結(jié)構(gòu)

#插入1600數(shù)據(jù)

#模擬真實(shí)數(shù)據(jù)

#表area有name列 需要對(duì)name列前綴索引

#計(jì)算得比值接近0.9350就好了

#分別取 3 4 5位name值計(jì)算

#可知name列添加5位前綴索引就可以了

#Mysql不能在order by 或 group by查詢使用前綴索引 也不能將其用作覆蓋索引

• 聚集索引

聚集索引不是一種單獨(dú)的索引類型,而是一種存儲(chǔ)數(shù)據(jù)的方式.

Innodb 的聚集索引實(shí)際上同樣的結(jié)構(gòu)保存了B-tree索引和數(shù)據(jù)行,"聚集" 是指實(shí)際的數(shù)據(jù)行和相關(guān)的鍵值保存在一起,每個(gè)表只能有一個(gè)聚集索引,因此不能一次把行保存在兩個(gè)地方. (由于聚集索引對(duì)我來(lái)說(shuō)不常用,我們就略過(guò)啦~)

• 覆蓋索引

索引支持高效查找行,mysql也能使用索引來(lái)接收列的數(shù)據(jù).這樣不用讀取行數(shù)據(jù),當(dāng)發(fā)起一個(gè)被索引覆蓋的查詢,explain解釋器的extra列看到 using index.

#滿足條件：#

# select 查詢的字段必須 有索引全覆蓋

select last_name,first_name 其中 last_name 和first_name 必須都有索引

#不能在索引執(zhí)行l(wèi)ike操作

• 為排序使用索引掃描

mysql排序結(jié)果的方式：使用文件排序 、 掃描有序的索引

explain中的type列若為 "索引(Index)" 說(shuō)明mysql掃描索引.單純掃描索引很快,如果mysql沒(méi)有使用索引覆蓋查詢 就不得不查找索引中發(fā)現(xiàn)的每一行.

mysql能有為排序和查找行使用同樣的索引,如表 user 索引 (uid,birthday ) .

使用排序索引：

• 避免多余和重復(fù)索引

重復(fù)索引：類型相同,以同樣的順序在同樣的列創(chuàng)建索引,比如在表user id列 添加 unique(id)約束 、id not .

primary key 約束 index(id),其實(shí)這些是相同的索引 !

多余索引：如存在(A)索引 應(yīng)該擴(kuò)展它 滿足 (A,B)索引

(A,B)索引 <==> (B)

(A,B)索引 <==> (A)

(A,B) A最左前綴 (B,A) B最左前綴

• 索引實(shí)例研究

設(shè)計(jì)user表 字段：country、 state/region 、city 、sex 、age 、eye 、color 功能：支持組合條件搜索用戶 支持用戶排序 用戶上次在線時(shí)間

• 支持多種過(guò)濾條件

不在選擇性很差的列添加索引

• 優(yōu)化排序

索引和表維護(hù)

表維護(hù)三個(gè)目標(biāo)：查找和修復(fù)損壞、維護(hù)精確的索引統(tǒng)計(jì),并減少碎片

• 查找并修復(fù)表?yè)p壞

check table 命令：確定表是否損壞,能抓到大部分表和索引錯(cuò)誤

repair table 命令：修復(fù)損壞的表

myisamchk ：離線修復(fù)工具

• 更新索引統(tǒng)計(jì)

analyze table cs_area 更新索引統(tǒng)計(jì)信息,便于優(yōu)化器優(yōu)化sql

show index 命令檢查索引的基數(shù)性

• 減少索引和數(shù)據(jù)碎片

myisam引擎 使用 optimize table 清除碎片 Innodb 引擎 使用 alter table .. engine = .. 重新創(chuàng)建索引

正則化和非正則化

• 正則化和非正則化

• 正則化數(shù)據(jù)庫(kù)：每個(gè)因素只會(huì)表達(dá)一次,教師表teacher (id,school_id), 學(xué)校表school

  (school_id,school_name) 優(yōu)點(diǎn)：更新信息只變動(dòng)一張表 缺點(diǎn)：簡(jiǎn)單的學(xué)校名稱查詢 需要關(guān)聯(lián)表

  非正則化數(shù)據(jù)庫(kù)：信息是重復(fù)的 或者 保存在多個(gè)地方

  
  

• 教師表teacher (id,school_id,school_name) 學(xué)校表school (school_id,school_name)

  
  

• 優(yōu)點(diǎn)：便于直接統(tǒng)計(jì)對(duì)應(yīng)學(xué)校名稱的老師

  缺點(diǎn)：更新需要變動(dòng)的表多一張

  
  

• 正則化和非正則化并用：比如需要統(tǒng)計(jì)用戶的發(fā)帖數(shù) 可以在user表添加字段num_message 保存發(fā)帖總數(shù) 避免高密度查詢統(tǒng)計(jì)

• 緩存和匯總表

實(shí)例：統(tǒng)計(jì)過(guò)去24小時(shí)發(fā)布的信息精確的數(shù)量

• 表周期性創(chuàng)建

周期創(chuàng)建可以得到?jīng)]有碎片和全排序索引的高效表

注意：此法會(huì)將數(shù)據(jù)清除,只是得到一個(gè)沒(méi)有碎片和高效的索引表.

計(jì)數(shù)表：比如緩存用戶朋友數(shù)量、文件下載次數(shù) 通常建立一個(gè)單獨(dú)的表,以保持快速維護(hù)計(jì)數(shù)器.

計(jì)劃任務(wù)定期聚合函數(shù)查詢,更新對(duì)應(yīng)的字段.

近期熱文(點(diǎn)擊標(biāo)題可閱讀全文)

近期活動(dòng)：

Gdevops全球敏捷運(yùn)維峰會(huì)北京站

峰會(huì)官網(wǎng)：www.gdevops.com

《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列1：數(shù)據(jù)類型與索引調(diào)優(yōu)全解析》是否對(duì)您有啟發(fā)，歡迎查看更多與《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列1：數(shù)據(jù)類型與索引調(diào)優(yōu)全解析》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/7829.html