訂閱
糾錯(cuò)
加入自媒體

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

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

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

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

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

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

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

36、MySQL 索引使用的注意事項(xiàng)

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

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

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

select * from news where year(publish_time) < 2017

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

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

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

select * from news where id / 100 = 1

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

select * from news where id = 1 * 100

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

應(yīng)該盡量避免在 where 子句中使用 != 或 not in 或 <> 操作符,因?yàn)檫@幾個(gè)操作符都會(huì)導(dǎo)致索引失效而進(jìn)行全表掃描。盡量避免使用 or 來(lái)連接條件應(yīng)該盡量避免在 where 子句中使用 or 來(lái)連接條件,因?yàn)檫@會(huì)導(dǎo)致索引失效而進(jìn)行全表掃描。

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

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

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

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

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

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

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

select * from news where news_month = 1

此時(shí),無(wú)法使用 news_year_month_idx(news_year, news_month) 索引,因?yàn)樽袷亍白钭笄熬Y”原則,在查詢條件中沒(méi)有使用復(fù)合索引的第一個(gè)字段,索引是不會(huì)被使用的。

5)覆蓋索引的好處

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

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

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

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

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

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

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

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

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

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

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

select * from news where date_str = 201701

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

9)like 語(yǔ)句的索引失效問(wèn)題

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

37、MySQL中有哪些索引?有什么特點(diǎn)?

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

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

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

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

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

40、使用索引的注意事項(xiàng)

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

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

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

在經(jīng)常需要排序的列上創(chuàng)建索引,因?yàn)樗饕呀?jīng)排序,這樣查詢可以利用索引的排序,加快排序查詢時(shí)間

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

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

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

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

刪除長(zhǎng)期未使用的索引,不用的索引的存在會(huì)造成不必要的性能損耗

在使用limit offset查詢緩存時(shí),可以借助索引來(lái)提高性能。

聲明: 本文由入駐維科號(hào)的作者撰寫,觀點(diǎn)僅代表作者本人,不代表OFweek立場(chǎng)。如有侵權(quán)或其他問(wèn)題,請(qǐng)聯(lián)系舉報(bào)。

發(fā)表評(píng)論

0條評(píng)論,0人參與

請(qǐng)輸入評(píng)論內(nèi)容...

請(qǐng)輸入評(píng)論/評(píng)論長(zhǎng)度6~500個(gè)字

您提交的評(píng)論過(guò)于頻繁,請(qǐng)輸入驗(yàn)證碼繼續(xù)

  • 看不清,點(diǎn)擊換一張  刷新

暫無(wú)評(píng)論

暫無(wú)評(píng)論

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

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