概述

MySQL的主要適用場景

1、Web網站系統

2、日志記錄系統

3、數據倉庫系統

4、嵌入式系統

MySQL架構圖：

索引

索引是什么

官方介紹索引是贊助MySQL高效獲取數據的數據結構.

筆者理解索引相當于一本書的目錄,通過目錄就知道要的資料在哪里,不用一頁一頁查閱找出必要的資料.

索引目的

索引的目的在于提高查詢效率,可以類比字典,如果要查“mysql”這個單詞,我們肯定必要定位到m字母,然后從下往下找到y字母,再找到剩下的sql.

如果沒有索引,那么你可能必要把所有單詞看一遍才能找到你想要的,如果我想找到m開頭的單詞呢?或者ze開頭的單詞呢?是不是覺得如果沒有索引,這個事情根本無法完成?

索引原理

除了辭書,生活中隨處可見索引的例子,如火車站的車次表、圖書的目錄等.

它們的原理都是一樣的,通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果,同時把隨機的事件變成順序的事件,也便是我們總是通過同一種查找方式來鎖定數據.

數據庫也是一樣,但顯然要復雜許多,因為不僅面臨著等值查詢,還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等.

數據庫應該選擇怎么樣的方式來應對所有的問題呢?

我們回想字典的例子,能不克不及把數據分成段,然后分段查詢呢?

最簡單的如果1000條數據,1到100分成第一段,101到200分成第二段,201到300分成第三段……這樣查第250條數據,只要找第三段就可以了,一下子去除了90%的無效數據.

但如果是1千萬的記錄呢,分成幾段比擬好?稍有算法基礎的同學會想到搜索樹,其平均復雜度是lgN,具有不錯的查詢性能.

但這里我們忽略了一個關鍵的問題,復雜度模型是基于每次相同的操作成原來考慮的,

數據庫實現比較復雜,數據保留在磁盤上,而為了提高性能,每次又可以把部分數據讀入內存來計算,

因為我們知道拜訪磁盤的成本大概是拜訪內存的十萬倍左右,所以簡單的搜索樹難以滿足復雜的應用場景.

磁盤IO與預讀

前面提到了拜訪磁盤,那么這里先簡單介紹一下磁盤IO和預讀,

磁盤讀取數據靠的是機械運動,每次讀取數據花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分,尋道時間指的是磁臂移動到指定磁道所必要的時間,主流磁盤一般在5ms以下；

旋轉延遲就是我們經常聽說的磁盤轉速,好比一個磁盤7200轉,表示每分鐘能轉7200次,也就是說1秒鐘能轉120次,旋轉延遲就是1/120/2 = 4.17ms；

傳輸時間指的是從磁盤讀出或將數據寫入磁盤的時間,一般在零點幾毫秒,相對于前兩個時間可以忽略不計.

那么拜訪一次磁盤的時間,即一次磁盤IO的時間約等于5+4.17 = 9ms左右,聽起來還挺不錯的,

但要知道一臺500 -MIPS的機器每秒可以執行5億條指令,因為指令依靠的是電的性質,

換句話說執行一次IO的時間可以執行40萬條指令,數據庫動輒十萬百萬乃至千萬級數據,每次9毫秒的時間,顯然是個災難.

下圖是計算機硬件延遲的對比圖,供大家參考：

考慮到磁盤IO是非常昂揚的操作,計算機操作系統做了一些優化,

當一次IO時,不光把當前磁盤地址的數據,而是把相鄰的數據也都讀取到內存緩沖區內,

因為局部預讀性原理告訴我們,當計算機拜訪一個地址的數據的時候,與其相鄰的數據也會很快被拜訪到.

每一次IO讀取的數據我們稱之為一頁(page).

具體一頁有多大數據跟操作系統有關,一般為4k或8k,也就是我們讀取一頁內的數據時候,實際上才發生了一次IO,這個理論對于索引的數據結構設計非常有贊助.

索引的數據布局

前面講了生活中索引的例子,索引的基本原理,數據庫的復雜性,又講了操作系統的相關知識,目的就是讓大家了解,任何一種數據結構都不是憑空產生的,一定會有它的配景和使用場景.

我們現在總結一下,我們必要這種數據結構能夠做些什么,其實很簡單,那就是：每次查找數據時把磁盤IO次數控制在一個很小的數量級,最好是常數數量級.那么我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢?就這樣,b+樹應運而生.

詳解b+樹

如上圖,是一顆b+樹,關于b+樹的定義可以參見B+樹.

這里只說一些重點,淺藍色的塊我們稱之為一個磁盤塊,可以看到每個磁盤塊包括幾個數據項(深藍色所示)和指針(黃色所示),

如磁盤塊1包括數據項17和35,包括指針P1、P2、P3,P1表示小于17的磁盤塊,P2表示在17和35之間的磁盤塊,P3表示大于35的磁盤塊.

真實的數據存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99.

非葉子節點只不存儲真實的數據,只存儲指引搜索方向的數據項,

如17、35并不真實存在于數據表中.

b+樹的查找過程

如圖所示,如果要查找數據項29,

那么首先會把磁盤塊1由磁盤加載到內存,此時產生一次IO,

在內存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內存時間因為非常短(相比磁盤的IO)可以忽略不計,

通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存,產生第二次IO,

29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內存,產生第三次IO,

同時內存中做二分查找找到29,結束查詢,總計三次IO.

真實的情況是,3層的b+樹可以表示上百萬的數據,如果上百萬的數據查找只必要三次IO,性能提高將是巨大的,

如果沒有索引,每個數據項都要發生一次IO,那么總共需要百萬次的IO,顯然本錢非常非常高.

b+樹性質

通過上面的分析,我們知道IO次數取決于b+數的高度h,假設當前數據表的數據為N,每個磁盤塊的數據項的數量是m,則有h=㏒(m+1)N,當數據量N必定的情況下,m越大,h越小；

而m = 磁盤塊的大小 / 數據項的大小,磁盤塊的大小也便是一個數據頁的大小,是固定的,如果數據項占的空間越小,數據項的數量越多,樹的高度越低.

這就是為什么每個數據項,即索引字段要盡量的小,好比int占4字節,要比bigint8字節少一半.

這也是為什么b+樹要求把真實的數據放到葉子節點而不是內層節點,一旦放到內層節點,磁盤塊的數據項會大幅度下降,導致樹增高.

當數據項等于1時將會退化成線性表.

當b+樹的數據項是復合的數據結構,好比(name,age,sex)的時候,

b+數是依照從左到右的順序來建立搜索樹的,

好比當(張三,20,F)這樣的數據來檢索的時候,

b+樹會優先比擬name來確定下一步的所搜方向,如果name相同再依次比擬age和sex,最后得到檢索的數據；

但當(20,F)這樣的沒有name的數據來的時候,b+樹就不知道下一步該查哪個節點,

因為建立搜索樹的時候name就是第一個比擬因子,必須要先根據name來搜索才能知道下一步去哪里查詢.

好比當(張三,F)這樣的數據來檢索時,b+樹可以用name來指定搜索方向,

但下一個字段age的缺失,所以只能把名字等于張三的數據都找到,然后再匹配性別是F的數據了,

這個是非常重要的性質,即索引的最左匹配特性.

索引是不是越多越好?

索引能夠極大的提高數據檢索效率,也能夠改善排序分組操作的性能,但是我們不能忽略的一個問題便是索引是完全獨立于基礎數據之外的一部分數據.

假設我們在更新表中有字段的同時,也更新索引數據,調整因為更新所帶來鍵值變化后的索引信息.

而如果我們沒有對字段進行索引的話,MySQL 所必要做的僅僅只是更新表中字段 的信息.

這樣,所帶來的最明顯的資源消耗便是增加了更新所帶來的IO量和調整索引所致的計算量.

此外,索引是必要占用存儲空間的,而且隨著數據量的增長,所占用的空間也會不斷增長.

所以索引還會帶來存儲空間資源消耗的增長.

什么場景應該加索引?加索引的四個原則

1. 較頻繁的作為查詢條件的字段應該創立索引

提高數據查詢檢索的效率最有效的辦法就是減少需要拜訪的數據量,從上面所了解到的索引的益處中我們知道了,索引正是我們減少通過索引鍵字段作為查詢條件的Query 的IO 量的最有效手段.所以一般來說我們應該為較為頻繁的查詢條件字段創建索引.

2. 唯一性太差的字段不適合單獨創立索引,即使頻繁作為查詢條件

唯一性太差的字段主要是指哪些呢?

如狀態字段,類型字段等等,這些字段中存方的數據可能總共便是那么幾個幾十個值重復使用,每個值都會存在于成千上萬或是更多的記錄中.

對于這類字段,我們完全沒有需要創建單獨的索引的.

因為即使我們創立了索引,MySQL Query Optimizer 大多數時候也不會去選擇使用,

如果什么時候MySQL Query Optimizer 抽了一下風選擇了這種索引,那么非常遺憾的告訴你,這可能會帶來極大的性能問題.

由于索引字段中每個值都含有大量的記錄,那么存儲引擎在根據索引拜訪數據的時候會帶來大量的隨機IO,甚至有些時候可能還會出現大量的重復IO.

3. 更新非常頻繁的字段不適合創立索引

上面在索引的弊端中我們已經分析過了,索引中的字段被更新的時候,不僅僅必要更新表中的數據,同時還要更新索引數據,以確保索引信息是準確的.

這個問題所帶來的是IO 拜訪量的較大增加,不僅僅影響更新Query 的響應時間,還會影響整個存儲系統的資源消耗,加大整個存儲系統的負載.

4. 不會呈現在WHERE子句中的字段不創建索引

查詢時,不會命中索引.那么索引就沒有存在的意義了.

創建索引的舉例闡明

CREATE TABLE `v9_member_menu` ( `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, # 主鍵標識 `name` char(40) NOT NULL DEFAULT '', # 菜單名稱 `parentid` smallint(6) NOT NULL DEFAULT '0', # 父級ID `m` char(20) NOT NULL DEFAULT '', # 模型名稱 `c` char(20) NOT NULL DEFAULT '', # 控制器名 `a` char(20) NOT NULL DEFAULT '', # 辦法名 `data` char(100) NOT NULL DEFAULT '', # 附加數據 `listorder` smallint(6) unsigned NOT NULL DEFAULT '0',# 排序值 `display` enum('1','0') NOT NULL DEFAULT '1', # 是否顯示 `isurl` enum('1','0') NOT NULL DEFAULT '0', # 是否是一個鏈接 `url` char(255) NOT NULL DEFAULT '', # 鏈接地址 PRIMARY KEY (`id`), KEY `listorder` (`listorder`),  KEY `parentid` (`parentid`), KEY `module` (`m`,`c`,`a`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

關于 菜單的使用場景, 我做出如下整理

1. 會根據 url分割出 m,c,a 然后進行查詢菜單ID,再關聯權限表,查詢是否有權限.

2. 根據 菜單ID 獲取菜單信息,例如 刪,改,查的應用場景

3. 會根據菜單的父級ID 查詢父級信息, 或者同本身的ID 查詢子級信息.

4. 顯示菜單時,通常會進行排序.

5. 第一個情況 就符合 ,創建復合索引的條件,在where中常常會一起出現,

   例如 m=home and c=index and a=login

6. 第二個情況 可以使用主鍵索引,主鍵自己就自帶索引屬性.

7. 第三個情況,在查詢子級時 通常會使用到.

8. 第四個情況： 排序也常常使用到.

9. data 和 url 為何不加索引?

   data 和 url 屬于詳細內容, 一般只用于展示,不會加入到where條件查詢中,所以不必要加索引.

10. display 和 isurl 為何不加索引

    display 和 isurl 一樣 他的數值很單一,不是1就是0,沒需要加索引,而且符合條件的數據有很多,給mysql帶來大量的隨機IO.

索引的類型

聚簇索引和非聚簇索引

索引分為聚簇索引和非聚簇索引兩種,聚簇索引是依照數據存放的物理位置為順序的,而非聚簇索引就不一樣了；

聚簇索引能提高多行檢索的速度,而非聚簇索引對于單行的檢索很快.

聚簇索引是一種數據存儲方式,它實際上是在同一個結構中保存了B+樹索引和數據行,InnoDB表是依照聚簇索引組織的(類似于Oracle的索引組織表).

InnoDB通過主鍵聚簇數據,如果沒有定義主鍵,會選擇一個唯一的非空索引代替,如果沒有這樣的索引,會隱式定義個主鍵作為聚簇索引.

下圖形象闡明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的區別：

對于非聚簇索引表來說(右圖),表數據和索引是分成存儲的,主鍵索引和二級索引存儲上沒有任何區別.

而對于聚簇索引表來說(左圖),表數據是和主鍵一起存儲的,主鍵索引的葉結點存儲行數據,二級索引的葉結點存儲行的主鍵值.

聚簇索引表最大限度地提高了I/O密集型應用的性能,但它也有以下幾個限制：

1)插入速度嚴重依賴于插入順序,依照主鍵的順序插入是最快的方式,否則將會出現頁分裂,嚴重影響性能.

因此,對于InnoDB表,我們一般都會定義一個自增的ID列為主鍵.

2)更新主鍵的代價很高,因為將會導致被更新的行移動.因此,對于InnoDB表,我們一般定義主鍵為弗成更新.

3)二級索引拜訪需要兩次索引查找,第一次找到主鍵值,第二次根據主鍵值找到行數據.

二級索引的葉節點存儲的是主鍵值,而不是行指針(非聚簇索引存儲的是指針或者說是地址),這是為了減少當呈現行移動或數據頁分裂時二級索引的維護工作,但會讓二級索引占用更多的空間.

聚簇索引的葉節點就是數據節點,而非聚簇索引的頁節點仍然是索引檢點,并保存一個鏈接指向對應數據塊.

聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多.

相比之下,聚簇索引適合排序,非聚簇索引不適合用在排序的場所.

因為聚簇索引本身已經是依照物理順序放置的,排序很快.

非聚簇索引則沒有順次存放,需要額外消耗資源來排序.

當你需要取出必定范圍內的數據時,用聚簇索引也比用非聚簇索引好.

主鍵索引(PRIMARY KEY )

主鍵自帶索引屬性. 不管是 修改查詢刪除 基本都會用到它.

普通索引(Normal)

這是最基本的索引,它沒有任何限制,好比上文中為listorder字段創建的索引就是一個普通索引,MyIASM中默認的BTREE類型的索引,也是我們大多數情況下用到的索引.

實例

–直接創建索引CREATE INDEX index_name ON table(column(length))–修改表布局的方式添加索引ALTER TABLE table_name ADD INDEX index_name ON (column(length))–創建表的時候同時創建索引CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), INDEX index_name (title(length)))–刪除索引DROP INDEX index_name ON table

唯一索引(Unique)

與普通索引類似,不同的就是：索引列的值必需唯一,但允許有空值(注意和主鍵不同).

如果是組合索引,則列值的組合必須唯一,創建辦法和普通索引類似.

例如：用戶表的 用戶名 和 郵箱 都可以進行唯一索引

實例

–創建唯一索引CREATE UNIQUE INDEX indexName ON table(column(length))–修改表布局ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))–創建表的時候直接指定CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE indexName (title(length)));

全文索引(Full Text)

MySQL從3.23.23版開始支持全文索引和全文檢索,FULLTEXT索引僅可用于 MyISAM 表；

他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部門被創建,

或是隨后使用ALTER TABLE 或CREATE INDEX被添加.

對于較大的數據集,將你的材料輸入一個沒有FULLTEXT索引的表中,然后創建索引,其速度比把材料輸入現有FULLTEXT索引的速度更為快.

不外切記對于大容量的數據表,生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法.

在數據量不是很大的情況下 可以利用 全文索引做 站內搜索.

但是得先分詞,能力進行全文檢索.檢索時 是通過 空格來分割詞匯.

最好是 新建一個關聯表(此中 存儲分詞的字段 用全文索引),把分詞后的內容 用 空格分割 存儲到 關聯表,然后對應原始表.

查詢流程如下

1. 查詢關聯表

2. 獲取所有能查到的 文章ID

3. 根據文章ID 獲取文章數據

也可以配合第三方的檢索插件 來進行全文檢索

packagist.org 搜索中文分詞

小項目可以使用 結巴分詞

單列索引 和 復合索引

多個單列索引與單個多列索引的查詢效果分歧,因為執行查詢時,MySQL只能使用一個索引,會從多個索引中選擇一個限制最為嚴格的索引.

即 mysql 底層本身會判斷 使用那個索引 速度會更快

組合索引(最左前綴)

平時用的SQL查詢語句一般都有比擬多的限制條件,所以為了進一步榨取MySQL的效率,就要考慮建立組合索引.

例如上表中針對title和time建立一個組合索引：

 ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

建立這樣的組合索引,其實是相當于分別建立了下面兩組組合索引：

–title,time

–title

為什么沒有time這樣的組合索引呢?這是因為MySQL組合索引“最左前綴”的成果.

簡單的理解便是只從最左面的開始組合.

并不是只要包括這兩列的查詢都會用到該組合索引,如下面的幾個SQL所示：

–使用到上面的索引SELECT * FROM article WHREE title='測試' AND time=1234567890;SELECT * FROM article WHREE utitle='測試';–不使用上面的索引SELECT * FROM article WHREE time=1234567890;

自創索引表

如果又必要可以自創 索引表(關聯表).

例如 現在有一個文章表, 必要做一個文章的站內搜索

那么 我們必要新建一個文章表

CREATE TABLE `article` ( `id` int(11) unsigned NOT NULL COMMENT '主鍵', `title` varchar(255) NOT NULL COMMENT '題目', `author` varchar(255) NOT NULL DEFAULT '' COMMENT '作者', `content` text NOT NULL COMMENT '內容', `create_time` int(11) unsigned NOT NULL COMMENT '創建時間', `update_time` int(11) unsigned DEFAULT NULL COMMENT '修改時間', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

創建一個 分詞索引表

CREATE TABLE `article_participle` ( `id` int(11) NOT NULL, `article_id` int(11) unsigned NOT NULL COMMENT '文章表ID ', `participle` varchar(1000) NOT NULL COMMENT '關鍵詞 以空格分隔', PRIMARY KEY (`id`), UNIQUE KEY `article_id` (`article_id`) USING BTREE COMMENT '文章ID', FULLTEXT KEY `participle` (`participle`) COMMENT '中文分詞存儲') ENGINE=MyISAM DEFAULT CHARSET=utf8;

先根據 搜索的關鍵詞 搜索 分詞索引表

然后在根據搜索出的成果 (article_id 文章ID) 搜索文章表

索引辦法

BTree 索引特征

BTree索引可以被用在像=,>,>=,<,<=和BETWEEN這些比擬操作符上.而且還可以用于LIKE操作符,只要它的查詢條件是一個不以通配符開頭的常量.像下面的語句就可以使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

下面這兩種情況不會使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一條是因為它以通配符開頭,第二條是因為沒有使用常量.

假如你使用... LIKE '%string%'并且string超過三個字符,MYSQL使用Turbo Boyer-Moore algorithm算法來初始化查詢表達式,然后用這個表達式來讓查詢更迅速.

一個這樣的查詢col_name IS NULL是可以使用col_name的索引的.

任何一個沒有覆蓋所有WHERE中AND級別條件的索引是不會被使用的.也就是說,要使用一個索引,這個索引中的第一列必要在每個AND組中出現.

下面的WHERE條件會使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3 /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2 /* 優化成 "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5 /* 可以使用 index1 的索引但是不會使用 index2 和 index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的WHERE條件不會使用索引：

 /* index_part1 沒有被使用到 */... WHERE index_part2=1 AND index_part3=2 /* 索引 index 沒有呈現在每個 where 子句中 */... WHERE index=1 OR A=10 /* 沒有索引覆蓋所有列 */... WHERE index_part1=1 OR index_part2=10

有時候mysql不會使用索引,即使這個在可用的情況下.

例如當mysql預估使用索引會讀取大部分的行數據時.(在這種情況下,一次全表掃描可能比使用索引更快,因為它必要更少的檢索).

然而,假如語句中使用LIMIT來限定返回的行數,mysql則會使用索引.

因為當成果行數較少的情況下使用索引的效率會更高.

位圖索引 (HASH)

Hash類型的索引有一些區別于以上所述的特征：

1.相對于BTree索引,占用的空間非常小,創立和使用非常快.

位圖索引由于只存儲鍵值的起止Rowid和位圖,占用的空間非常少.

2.不適合鍵值較多的列.

3.不適合update、insert、delete頻繁的列.

4.可以存儲null值.

BTree索引由于不記錄空值,當基于is null的查詢時,會使用全表掃描.

而對位圖索引列進行is null查詢時,則可以使用索引.

5.當select count(XX) 時,可以直接拜訪索引中一個位圖就快速得出統計數據.

6.當根據鍵值做and,or或 in(x,y,..)查詢時,直接用索引的位圖進行或運算,快速得出成果行數據統計.

7.它們只能用于對等比擬,例如=和<=>操作符(但是快很多).它們不能被用于像<這樣的范圍查詢條件.假如系統只需要使用像“鍵值對”的這樣的存儲結構,盡量使用hash類型索引.

8.優化器不克不及用hash索引來為ORDER BY操作符加速.(這類索引不克不及被用于搜索下一個次序的值)

9.mysql不能判斷出兩個值之間有多少條數據(這必要使用范圍查詢操作符來決定使用哪個索引).假如你將一個MyISAM表轉為一個依靠hash索引的MEMORY表,可能會影響一些語句(的性能).

10.只有完整的鍵能力被用于搜索一行數據.(假如用B-tree索引,任何一個鍵的片段都可以用于查找).

去索引化

為了更好的提高并發量,又發生了另一個思想!去索引化.

去索引化,并不是真正的去掉索引.只是通過異步操作把索引 像關系表那樣存起來.

這樣可以提升,高并發寫入的性能,又可以提升數據查詢的性能.

SQL語句優化

經常用到的必要條件字段 必要建立索引

避免在 where 子句中對字段進行 null 值判斷(全表掃描)

避免 !=或<>操作

避免 in 和 not in 可用(between)

避免 '%c%' 考慮使用全文檢索.

避免使用 參數,子句,函數操作

避免表達式操作 如 num/2=100; 優化后 num=100*2;

查詢時 把條件中 有索引的 放在最左邊 (最左前綴)

exists 代替 in

分布式架構 和集群架構的區別

簡單說,分布式是以縮短單個任務的執行時間來提升效率的,而集群則是通過提高單位時間內執行的任務數來提升效率.

例如：

如果一個任務由10個子任務組成,每個子任務單獨執行需1小時,則在一臺服務器上執行改任務需10小時.

采用分布式方案,提供10臺服務器,每臺服務器只負責處理一個子任務,不考慮子任務間的依賴關系,執行完這個任務只需一個小時.(這種工作模式的一個典型代表便是Hadoop的Map/Reduce分布式計算模型)

而采納集群方案,同樣提供10臺服務器,每臺服務器都能獨立處理這個任務.假設有10個任務同時到達,10個服務器將同時工作,10小后,10個任務同時完成,這樣,整身來看,還是1小時內完成一個任務!

分表

為什么要分表?

數據庫中的數據量不必定是可控的,在未進行分庫分表的情況下,隨著時間和業務的發展,庫中的表會越來越多,表中的數據量也會越來越大,相應地,數據操作,增刪改查的開銷也會越來越大；

另外,由于無法進行分布式式部署,而一臺服務器的資源(CPU、磁盤、內存、IO等)是有限的,最終數據庫所能承載的數據量、數據處理才能都將遭遇瓶頸.

分表的方式?

程度切分(橫向切分)

垂直切分(縱向切分)

聯合切分(橫向切分 和縱向切分)

垂直分表

何為垂直分表?

即將表依照功能模塊、關系密切程度劃分出來,部署到不同的數據表上.

比如user(用戶表 主要存用戶名 和暗碼)表和user_details(用戶詳情 頭像,地址等)表.

好比博客表中的title和content表.(大字段 拆到另外一個表里)

大字段垂直切分

什么樣的字段適合于從表中拆分：

首先要肯定是大字段.為什么?原因很簡單,便是因為他的大.

大字段一般都是存放著一些較長的Detail 信息,如文章的內容,帖子的內容,產物的介紹等等.

其次是和表中其他字段相比拜訪頻率明顯要少很多.

如果我們要查詢某些記錄的某幾個字段,數據庫并不是只需要拜訪我們需要查詢的哪幾個字段,而是需要讀取其他所有字段這樣,我們就不得不讀取包括大字段在內的很多并不相干的數據.

而由于大字段所占的空間比例非常大,自然所浪費的IO 資源也就非常之大了.

實際上,在有些時候,我們甚至都不必定非要大字段才能進行垂直分拆.

在有些場景下,有的表中大部分字段平時都很少拜訪,而其中的某幾個字段卻是拜訪頻率非常高.

對于這種表,也非常適合通過垂直分拆來達到優化性能的目的.

垂直切分的長處

• 數據庫的拆分簡單明了,拆分規則明

• 應用法式模塊清晰明確,整合容易

• 數據維護便利易行,容易定位

垂直切分的缺點

• 部分表關聯無法在數據庫級別完成,必要在程序中完成

• 對于拜訪極其頻繁且數據量超大的表仍然存在性能瓶頸,不一定能滿足要求

• 事務處置相對更為復雜

• 切分達到必定程度之后,擴展性會遇到限制

• 過度切分可能會帶來系統過渡復雜而難以維護

程度分表

何為程度切分?

當一個表中的數據量過大時,我們可以把該表的數據依照某種規則,進行劃分,然后存儲到多個結構相同的表,和不同的庫上.

依據的條件可以是時間、地域、功能等比擬清晰的條件

好比財務報表、薪資發放就可以用時間進行水平分割；

好比商品庫存就可以用地域進行分割

好比用戶表的普通用戶、商戶就可以用功能來進行劃分

程度通用分表策略

以uuid作為全局唯一標識,為每一個新生成的用戶生成uuid

將uuid進行md5加密,生成16進制隨機字符串,取隨機字符串前兩位進行10進制轉換,對分表數量的取余,獲取插入的表后綴名.

好比建立8張表,對8取余,則會生成user_0...user_7,每個用戶會隨機插入這8張表中

分表后,如何統計數據?

所有統計數據都是根據業務需求而來的,原始數據存在的情況,我們可以進行自建索引,實現具體的業務需求.

好比根據添加時間自建索引,其結構如下：

|id|uuid|addtime|

|---|---|---|

那么根據addtime 我們就可以得出總數,最新個數.

分表后查詢效率的問題?

根據自建索引表,獲取uuid,再根據uuid獲取數據每一行的數據. 只不過多了一個10次的for循環罷了,而php的10for循環可以說是微秒級的.結果集存儲的是指針 在通過 mysql_fetch_row()讀取磁盤文件

水平切分的優點

• 表關聯基本能夠在數據庫端全部完成

• 不會存在某些超大型數據量和高負載的表遇到瓶頸的問題

• 應用法式端整體架構改動相對較少

• 事務處置相對簡單

• 只要切分規則能夠定義好,基本上較難遇到擴展性限制

程度切分的缺點

• 切分規則相對更為復雜,很難抽象出一個能夠滿足整個數據庫的切分規則

• 后期數據的維護難度有所增加,人為手工定位數據更困難

• 應用系統各模塊耦合度較高,可能會對后面數據的遷移拆分造成必定的困難

垂直與程度聯合切分的使用 (聯合切分)

如果大部門業務邏輯稍微復雜一點,系統負載大一些的系統,

都無法通過上面任何一種數據的切分辦法來實現較好的擴展性,

而需要將上述兩種切分辦法結合使用,不同的場景使用不同的切分辦法.

聯合切分的長處

• 可以充分利用垂直切分和程度切分各自的優勢而避免各自的缺陷

• 讓系統擴展性得到最大化提升

聯合切分的缺點

• 數據庫系統架構比擬復雜,維護難度更大；

• 應用法式架構也相對更復雜.

總結

以上是對MySQL性能調優,分享給大家,希望大家可以了解什么是MySQL性能調優.覺得收獲的話可以點個存眷收藏轉發一波喔,謝謝大佬們支持.(吹一波,233~~)

Java小馬哥,頭條出品,每天一篇干貨,喜歡就收藏＋存眷