欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

一篇文章掌握MySQL的索引查詢優(yōu)化技巧

 更新時間:2020年07月07日 08:58:03   作者:PHP進階架構(gòu)師  
這篇文章主要給大家介紹了關(guān)于如何通過一篇文章掌握MySQL的索引查詢優(yōu)化技巧,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

前言

本文的內(nèi)容是總結(jié)一些MySQL的常見使用技巧,以供沒有DBA的團隊參考。如無特殊說明,存儲引擎以InnoDB為準(zhǔn)。

MySQL的特點

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

MySQL默認(rèn)的存儲引擎是InnoDB,該存儲引擎的主要特點是:

  • 支持事務(wù)處理
  • 支持行級鎖
  • 數(shù)據(jù)存儲在表空間中,表空間由一些列數(shù)據(jù)文件組成
  • 采用MVVC(多版本并發(fā)控制)機制實現(xiàn)高并發(fā)
  • 表基于主鍵的聚簇索引建立
  • 支持熱備份

其它常見存儲引擎特點概述:

  • MyISAM:老版本MySQL的默認(rèn)引擎,不支持事務(wù)和行級鎖,開發(fā)者可以手動控制表鎖;支持全文索引;崩潰后無法安全恢復(fù);支持壓縮表,壓縮表數(shù)據(jù)不可修改,但占用空間較少,可以提高查詢性能
  • Archive:只支持Insert和Select,批量插入很快,通過全表掃描查詢數(shù)據(jù)
  • SCV:把一個SCV文件當(dāng)做一個表處理
  • Memory:數(shù)據(jù)存儲在內(nèi)存中

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

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

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

  • 選擇占用空間小的數(shù)據(jù)類型
  • 選擇簡單的類型
  • 避免不必要的可空列

占用空間小的類型更節(jié)省硬件資源,如磁盤、內(nèi)存和CPU。盡量使用簡單的類型,如能用 int 就不用 char ,因為后者的排序涉及到字符集的選擇,比使用 int 復(fù)雜??煽樟惺褂酶嗟拇鎯臻g,如果在可空列上創(chuàng)建索引,MySQL需要額外的字節(jié)做記錄。創(chuàng)建表時,默認(rèn)都是可空,容易被開發(fā)者忽視,最好是手動改為不可空,如果要存儲的數(shù)據(jù)確實不會有空值的話。

整型類型

整型類型包括 :

  • tinyint
  • smallint
  • mediumint
  • int
  • bigint

它們分別使用8、16、24、32和64位存儲數(shù)字,它們可以表示

范圍的數(shù)字,前面可以加unsigned修飾,這樣可以讓正數(shù)的可表示范圍提高1倍,但是無法表示負(fù)數(shù)。另外,為整型指定長度沒什么卵用,數(shù)據(jù)類型定下來,長度也就相應(yīng)定下來了。

小數(shù)類型

  • float
  • double
  • decimal

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

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

字符串類型

  • varchar
  • char
  • varbinary
  • binary
  • blob
  • text
  • 枚舉

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

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

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

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

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

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

時間類型

  • year
  • date
  • time
  • datetime
  • timestamp

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

主鍵類型的選擇

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

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

存儲IP最好使用32位無符號整型,MySQL提供了函數(shù)inet_aton()和inet_ntoa()進行IP地址的數(shù)字表示和字符串表示之間的轉(zhuǎn)換。

索引優(yōu)化

InnoDB使用B+樹實現(xiàn)索引,舉個例子,假設(shè)有個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 '編號',
 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列比較大小。先用第一列排序,然后是第二列,最后是第三列。

查詢的使用應(yīng)該盡量從左往右匹配,另外,如果左邊列范圍查找,右邊列無法使用索引;還有就是不能隔列查詢,否則后面的索引也無法使用到。如以下幾個SQL是正面范例:

  • SELECT * from people where Name ='Abel' and Age = 2 AND Number = 12312
  • SELECT * from people where Name ='Abel'
  • SELECT * from people where Name like ‘Abel%'
  • SELECT * from people where Name = ‘Andy' and Age BETWEEN 11 and 20
  • SELECT * from people ORDER BY NAME
  • SELECT * from people ORDER BY NAME, Age
  • SELECT * from people GROUP BY Name

以下幾個SQL是反面范例:

  • SELECT * from people where Age = 2
  • SELECT * from people where NAME like ‘%B'
  • SELECT * from people where age = 2
  • SELECT * from people where NAME = ‘ABC' AND number = 3
  • SELECT * from people where NAME like ‘B%' and age = 22

一個使用Hash值創(chuàng)建索引的技巧

如果表中有一列存儲較長字符串,假設(shè)名字為URL,在此列上創(chuàng)建的索引比較大,有個辦法可以緩解:創(chuàng)建URL字符串的數(shù)字哈希值的索引。再新建一個字段,比如叫做URL_CRC,專門放置URL的哈希值,然后給這個字段創(chuàng)建索引,查詢時這樣寫:

select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'

如果數(shù)據(jù)量比較多,為防止哈希沖突,可自定義哈希函數(shù),或用MD5函數(shù)返回值的一部分作為哈希值:

SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)

前綴索引

如果字符串列存儲的數(shù)據(jù)較長,創(chuàng)建的索引也很大,這時可以使用前綴索引,即:只針對字符串前幾個字符做索引,這樣可以縮短索引的大小,不過,顯然,此類索引在執(zhí)行 order by 和 group by 時不起作用。

創(chuàng)建前綴索引時選擇前綴長度很重要,在不破壞原來數(shù)據(jù)分布的情況下盡可能選擇較短的前綴。舉個例子,如果如果大部分字符串是以”abc”開頭,那么如果限定前綴索引長度為4,索引值會包含太多的重復(fù)的”abcX”。

多列索引

上面提到的“People”上創(chuàng)建的索引即為多列索引,多列索引往往比多個單列索引更好。

  • 對多個索引進行and查詢時,應(yīng)該創(chuàng)建多列索引,而不是多個單列索引
  • 可以試試這樣寫的效果:
select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'

多列索引的順序很重要,通常,不考慮排序和分組查詢時,應(yīng)該把選擇性(選擇性是指某表索引列不同數(shù)據(jù)的個數(shù)/總行數(shù)。選擇性高意味著重復(fù)數(shù)據(jù)少)大的列放到前面。但也有例外,如果能確認(rèn)某些查詢是頻繁執(zhí)行的,則應(yīng)該優(yōu)先照顧這些查詢的選擇性,比如,如果上面的People表中Name的選擇性大于Age,查詢語句應(yīng)該這樣寫:

select * from people where name = 'xxx' and age = xx

Name列放了索引中的左側(cè)比較合適,但是如果某個SQL執(zhí)行的評率最高,比如

select * from people where name = 'xxx' and age = 20

當(dāng)age=20的記錄在數(shù)據(jù)庫中非常少時,反而把age放到索引列的左端效率更高。把age放了索引左端可能對其它age不等于20的查詢來說不公平,如果不能確定age=20是最非常頻繁的查詢條件,還是要綜合考慮,把name放了左側(cè)合適。

聚簇索引

聚簇索引是一種數(shù)據(jù)存儲結(jié)構(gòu),InnoDB在主鍵的索引的葉子節(jié)點中直接保存了數(shù)據(jù)行,而不是像二級索引那樣只是保存了索引列的值和所指向行的主鍵值。由于這個特性,一個表只能有一個聚簇索引。如果一個表沒有定義主鍵也沒有定義具有唯一索引的列,那么InnoDB會生成一個隱藏列,并且在此列設(shè)為聚簇索引列。

覆蓋索引

簡單地說,某些查詢只需要查詢索引列,那么就不用再根據(jù)索引B樹節(jié)點記錄的主鍵ID進行二次查詢了。

重復(fù)索引和冗余索引

如果重復(fù)在某列創(chuàng)建索引,并不會帶來任何好處,只有壞處,應(yīng)該盡量避免。比如給主鍵創(chuàng)建唯一索引和普通索引就是多于的,因為InnoDB的主鍵默認(rèn)就是聚簇索引了。

冗余索引和重復(fù)索引不同,比如某個索引是(A,B),另一個索引是(A),這叫冗余索引,前者可以代替后者,后者不可以代替前者的作用。但是(A,B)和(B)以及(A,B)和(B,A)不算冗余索引,起作用誰也代替不了誰。

如果一個表中已經(jīng)存在索引(A),現(xiàn)在又想創(chuàng)建索引(A,B),那么只需擴展就的索引就可以,沒有必要創(chuàng)建新的索引。需要注意的是如果已經(jīng)存在索引(A),那么也沒有必要在創(chuàng)建索引(A,ID),其中ID指主鍵,因為索引A默認(rèn)已經(jīng)包含了主鍵了,也算是冗余主鍵。

但是,有時候,冗余索引也是可取的,假設(shè)已經(jīng)存在索引(A),將其擴展為(A,B)后,因為B列是一個很長的類型,導(dǎo)致用A單獨查詢時沒有以前快了,這時可以考慮新創(chuàng)建索引(A,B)。

不使用的索引

不使用的索引徒然增加insert、update和delete的效率,應(yīng)該及時刪除

索引使用總結(jié)

索引的三星原則:

  • 索引將查詢相關(guān)的記錄按順序放在一起則得一星
  • 索引中的數(shù)據(jù)順序和查詢結(jié)果的排序一致則得一星
  • 索引中包含了查詢所需要的全部列則得一星

第一個條原則的意思是where條件中查詢的順序和索引是一致的,就是前面說的從左到右使用索引。

索引不是萬能的,當(dāng)數(shù)據(jù)量巨大時,維護索引本身也是耗費性能的,應(yīng)該考慮分區(qū)分表存儲。

查詢優(yōu)化

查詢慢的原因

是否向數(shù)據(jù)庫請求了多余的行

比如應(yīng)用程序只需要10條數(shù)據(jù),但是卻向數(shù)據(jù)庫請求了所有的數(shù)據(jù),在顯示在UI上之前拋棄了大部分?jǐn)?shù)據(jù)。

是否向數(shù)據(jù)庫請求了多余的列

比如應(yīng)用程序只需要展現(xiàn)5列,但卻通過select * from 把全部的列都查了出來

是否重復(fù)多次執(zhí)行了相同的查詢

應(yīng)用程序是否可以考慮一次查詢?nèi)缓缶彺?,后面的用到時可以使用第一次查詢出來的記錄。

MySQL是否在掃描額外的記錄

通過查看執(zhí)行計劃可以大概了解需要掃描的記錄數(shù),如果這個數(shù)字超出了預(yù)期,盡可能通過添加索引、優(yōu)化SQL(就是本節(jié)的重點),或者改變表結(jié)構(gòu)(如新增一個單獨的匯總表,專門供某個語句查詢用)來解決。

重構(gòu)查詢的方式

  • 將一個復(fù)雜的查詢分解成多個簡單的查詢
  • 將大的查詢切分成小的查詢,每次查詢功能一樣,只完成一小部分
  • 分解關(guān)聯(lián)查詢??梢詫⒁粋€大的關(guān)聯(lián)查詢改成分別查詢?nèi)舾蓚€表,然后在應(yīng)用程序代碼中處理

雜七雜八

優(yōu)化count()

Count有兩個作用,一是統(tǒng)計指定的列或表達式,二是統(tǒng)計行數(shù)。如果參數(shù)傳入一列名或者是一個表達式,那么count會統(tǒng)計所有結(jié)果不為NULL的行數(shù),如果參數(shù)是*,那么count會統(tǒng)計所有行數(shù)。這里有一個傳表達式的例子:

SELECT count(name like 'B%') from people
  • 可以使用近似值優(yōu)化來代替count(),如執(zhí)行計劃中的行數(shù)。
  • 索引覆蓋掃描
  • 增加匯總表
  • 增加內(nèi)存緩存系統(tǒng)記錄數(shù)據(jù)條數(shù)

關(guān)聯(lián)查詢的優(yōu)化

  • MySQL優(yōu)化器關(guān)聯(lián)表查詢是這樣進行的,比如有兩個表A和B通過c列關(guān)聯(lián),MySQL會遍歷A表,然后根據(jù)遍歷到的c列的值去B表中查找數(shù)據(jù)。綜上所述,通常,如無只需要給B表的c列加上索引即可
  • 確保order by和group by涉及到的列只屬于一個表,這樣才有可能發(fā)揮索引的作用

優(yōu)化子查詢

對于MySQL5.5及以下版本,盡量用連接代替子查詢。

優(yōu)化group by、distinct

如果可能,盡量對主鍵施加這兩種操作。

優(yōu)化limit,比如有SQL

SELECT * from sa_stockinfo ORDER BY StockAcc LIMIT 400, 5

MySQL優(yōu)化器會查找405行所有列數(shù)據(jù)然后丟棄400。如果能利用覆蓋索引查詢則不必查詢出這么多列,先修改為:

SELECT * FROM sa_stockinfo i JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5)t ON i.StockInfoID = t.StockInfoID

StockAcc上建有索引,該查詢會利用索引覆蓋,較快找出符合條件的主鍵,然后在做聯(lián)合查詢,在數(shù)據(jù)量大的時候效果明顯。

優(yōu)化union

如無必要,一定要用關(guān)鍵字 union all,這樣MySQL把數(shù)據(jù)放到臨時表時不會再做唯一性驗證

判斷某條記錄是否存在,通常的做法是

select count(*) from t where condition

最好這樣寫:

SELECT IFNULL((SELECT 1 from tableName where condition LIMIT 1),0)

總結(jié)

到此這篇關(guān)于MySQL索引查詢優(yōu)化技巧的文章就介紹到這了,更多相關(guān)MySQL索引查詢優(yōu)化技巧內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

最新評論