訂閱
糾錯
加入自媒體

MySQL常見的存儲引擎InnoDB、MyISAM有何區(qū)別?

34、創(chuàng)建索引時需要注意什么?

非空字段:應(yīng)該指定列為NOT NULL,除非你想存儲NULL。在 MySQL 中,含有空值的列很難進行查詢優(yōu)化,因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值;

取值離散大的字段:(變量各個取值之間的差異程度)的列放到聯(lián)合索引的前面,可以通過count()函數(shù)查看字段的差異值,返回值越大說明字段的唯一值越多字段的離散程度高;

索引字段越小越好:數(shù)據(jù)庫的數(shù)據(jù)存儲以頁為單位一頁存儲的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高。唯一、不為空、經(jīng)常被查詢的字段 的字段適合建索引

35、MySQL中CHAR和VARCHAR的區(qū)別有哪些?

char的長度是不可變的,用空格填充到指定長度大小,而varchar的長度是可變的。char的存取數(shù)度還是要比varchar要快得多char的存儲方式是:對英文字符(ASCII)占用1個字節(jié),對一個漢字占用兩個字節(jié)。varchar的存儲方式是:對每個英文字符占用2個字節(jié),漢字也占用2個字節(jié)。

36、MySQL 索引使用的注意事項

MySQL 索引通常是被用于提高 WHERE 條件的數(shù)據(jù)行匹配時的搜索速度,在索引的使用過程中,存在一些使用細節(jié)和注意事項。

函數(shù),運算,否定操作符,連接條件,多個單列索引,最左前綴原則,范圍查詢,不會包含有NULL值的列,like 語句不要在列上使用函數(shù)和進行運算

1)不要在列上使用函數(shù),這將導(dǎo)致索引失效而進行全表掃描。

select * from news where year(publish_time) < 2017

為了使用索引,防止執(zhí)行全表掃描,可以進行改造。

select * from news where publish_time < '2017-01-01'

還有一個建議,不要在列上進行運算,這也將導(dǎo)致索引失效而進行全表掃描。

select * from news where id / 100 = 1

為了使用索引,防止執(zhí)行全表掃描,可以進行改造。

select * from news where id = 1 * 100

2)盡量避免使用 。 或 not in或 <> 等否定操作符

應(yīng)該盡量避免在 where 子句中使用 。 或 not in 或 <> 操作符,因為這幾個操作符都會導(dǎo)致索引失效而進行全表掃描。盡量避免使用 or 來連接條件應(yīng)該盡量避免在 where 子句中使用 or 來連接條件,因為這會導(dǎo)致索引失效而進行全表掃描。

select * from news where id = 1 or id = 2

3)多個單列索引并不是最佳選擇

MySQL 只能使用一個索引,會從多個索引中選擇一個限制最為嚴格的索引,因此,為多個列創(chuàng)建單列索引,并不能提高 MySQL 的查詢性能。假設(shè),有兩個單列索引,分別為 news_year_idx(news_year) 和 news_month_idx(news_month),F(xiàn)在,有一個場景需要針對資訊的年份和月份進行查詢,那么,SQL 語句可以寫成:

select * from news where news_year = 2017 and news_month = 1

事實上,MySQL 只能使用一個單列索引。為了提高性能,可以使用復(fù)合索引 news_year_month_idx(news_year, news_month) 保證 news_year 和 news_month 兩個列都被索引覆蓋。

4)復(fù)合索引的最左前綴原則

復(fù)合索引遵守“最左前綴”原則,即在查詢條件中使用了復(fù)合索引的第一個字段,索引才會被使用。因此,在復(fù)合索引中索引列的順序至關(guān)重要。如果不是按照索引的最左列開始查找,則無法使用索引。假設(shè),有一個場景只需要針對資訊的月份進行查詢,那么,SQL 語句可以寫成:

select * from news where news_month = 1

此時,無法使用 news_year_month_idx(news_year, news_month) 索引,因為遵守“最左前綴”原則,在查詢條件中沒有使用復(fù)合索引的第一個字段,索引是不會被使用的。

5)覆蓋索引的好處

如果一個索引包含所有需要的查詢的字段的值,直接根據(jù)索引的查詢結(jié)果返回數(shù)據(jù),而無需讀表,能夠極大的提高性能。因此,可以定義一個讓索引包含的額外的列,即使這個列對于索引而言是無用的。

6)范圍查詢對多列查詢的影響

查詢中的某個列有范圍查詢,則其右邊所有列都無法使用索引優(yōu)化查找。舉個例子,假設(shè)有一個場景需要查詢本周發(fā)布的資訊文章,其中的條件是必須是啟用狀態(tài),且發(fā)布時間在這周內(nèi)。那么,SQL 語句可以寫成:

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

這種情況下,因為范圍查詢對多列查詢的影響,將導(dǎo)致 news_publish_idx(publish_time, enable) 索引中 publish_time 右邊所有列都無法使用索引優(yōu)化查找。換句話說,news_publish_idx(publish_time, enable) 索引等價于 news_publish_idx(publish_time) 。對于這種情況,我的建議:對于范圍查詢,務(wù)必要注意它帶來的副作用,并且盡量少用范圍查詢,可以通過曲線救國的方式滿足業(yè)務(wù)場景。例如,上面案例的需求是查詢本周發(fā)布的資訊文章,因此可以創(chuàng)建一個news_weekth 字段用來存儲資訊文章的周信息,使得范圍查詢變成普通的查詢,SQL 可以改寫成:

select * from news where news_weekth = 1 and enable = 1

然而,并不是所有的范圍查詢都可以進行改造,對于必須使用范圍查詢但無法改造的情況,我的建議:不必試圖用 SQL 來解決所有問題,可以使用其他數(shù)據(jù)存儲技術(shù)控制時間軸,例如 Redis 的 SortedSet 有序集合保存時間,或者通過緩存方式緩存查詢結(jié)果從而提高性能。

7)索引不會包含有NULL值的列

只要列中包含有 NULL 值都將不會被包含在索引中,復(fù)合索引中只要有一列含有 NULL值,那么這一列對于此復(fù)合索引就是無效的。因此,在數(shù)據(jù)庫設(shè)計時,除非有一個很特別的原因使用 NULL 值,不然盡量不要讓字段的默認值為 NULL。

8)隱式轉(zhuǎn)換的影響

當查詢條件左右兩側(cè)類型不匹配的時候會發(fā)生隱式轉(zhuǎn)換,隱式轉(zhuǎn)換帶來的影響就是可能導(dǎo)致索引失效而進行全表掃描。下面的案例中,date_str 是字符串,然而匹配的是整數(shù)類型,從而發(fā)生隱式轉(zhuǎn)換。

select * from news where date_str = 201701

因此,要謹記隱式轉(zhuǎn)換的危害,時刻注意通過同類型進行比較。

9)like 語句的索引失效問題

like 的方式進行查詢,在 like “value%” 可以使用索引,但是對于 like “%value%” 這樣的方式,執(zhí)行全表查詢,這在數(shù)據(jù)量小的表,不存在性能問題,但是對于海量數(shù)據(jù),全表掃描是非?膳碌氖虑。所以,根據(jù)業(yè)務(wù)需求,考慮使用 ElasticSearch 或 Solr 是個不錯的方案。

37、MySQL中有哪些索引?有什么特點?

普通索引:僅加速查詢唯一索引:加速查詢 + 列值唯一(可以有null)主鍵索引:加速查詢 + 列值唯一(不可以有null)+ 表中只有一個組合索引:多列值組成一個索引,專門用于組合搜索,其效率大于索引合并全文索引:對文本的內(nèi)容進行分詞,進行搜索索引合并:使用多個單列索引組合搜索覆蓋索引:select的數(shù)據(jù)列只用從索引中就能夠取得,不必讀取數(shù)據(jù)行,換句話說查詢列要被所建的索引覆蓋聚簇索引:表數(shù)據(jù)是和主鍵一起存儲的,主鍵索引的葉結(jié)點存儲行數(shù)據(jù)(包含了主鍵值),二級索引的葉結(jié)點存儲行的主鍵值。使用的是B+樹作為索引的存儲結(jié)構(gòu),非葉子節(jié)點都是索引關(guān)鍵字,但非葉子節(jié)點中的關(guān)鍵字中不存儲對應(yīng)記錄的具體內(nèi)容或內(nèi)容地址。葉子節(jié)點上的數(shù)據(jù)是主鍵與具體記錄(數(shù)據(jù)內(nèi)容)

38、既然索引有那么多優(yōu)點,為什么不對表總的每一列創(chuàng)建一個索引呢?

當對表中的數(shù)據(jù)進行增加、刪除和修改的時候,索引也要動態(tài)的維護,這樣就降低了數(shù)據(jù)的維護速度。索引需要占物理空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個索引還要占一定的物理空間,如果要建立簇索引,那么需要的空間就會更大。創(chuàng)建索引和維護索引要耗費時間,這種時間隨著數(shù)據(jù)量的增加而增加

39、索引如何提高查詢速度的

將無序的數(shù)據(jù)變成相對有序的數(shù)據(jù)(就像查有目的一樣)

40、使用索引的注意事項

在經(jīng)常需要搜索的列上,可以加快搜索的速度;

在經(jīng)常使用在where子句中的列上面創(chuàng)建索引,加快條件的判斷速度。

將打算加索引的列設(shè)置為NOT NULL,否則將導(dǎo)致引擎放棄使用索引而進行全表掃描

在經(jīng)常需要排序的列上創(chuàng)建索引,因為索引已經(jīng)排序,這樣查詢可以利用索引的排序,加快排序查詢時間

避免where子句中對字段施加函數(shù),這會造成無法命中索引

在中到大型表索引都是非常有效的,但是特大型表的維護開銷會很大,不適合建索引,建立用邏輯索引

在經(jīng)常用到連續(xù)的列上,這些列主要是由一些外鍵,可以加快連接的速度

與業(yè)務(wù)無關(guān)時多使用邏輯主鍵,也就是自增主鍵在使用InnoDB時使用與業(yè)務(wù)無關(guān)的自增主鍵作為主鍵,即使用邏輯主鍵,而不要使用業(yè)務(wù)主鍵。

刪除長期未使用的索引,不用的索引的存在會造成不必要的性能損耗

在使用limit offset查詢緩存時,可以借助索引來提高性能。

<上一頁  1  2  3  下一頁>  
聲明: 本文由入駐維科號的作者撰寫,觀點僅代表作者本人,不代表OFweek立場。如有侵權(quán)或其他問題,請聯(lián)系舉報。

發(fā)表評論

0條評論,0人參與

請輸入評論內(nèi)容...

請輸入評論/評論長度6~500個字

您提交的評論過于頻繁,請輸入驗證碼繼續(xù)

暫無評論

暫無評論

人工智能 獵頭職位 更多
掃碼關(guān)注公眾號
OFweek人工智能網(wǎng)
獲取更多精彩內(nèi)容
文章糾錯
x
*文字標題:
*糾錯內(nèi)容:
聯(lián)系郵箱:
*驗 證 碼:

粵公網(wǎng)安備 44030502002758號