MySQL數(shù)據(jù)庫(kù)快問快答

1. UNION ALL 與 UNION 的區(qū)別

邢臺(tái)網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián),邢臺(tái)網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為邢臺(tái)1000多家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的邢臺(tái)做網(wǎng)站的公司定做！

  • UNION和UNION ALL關(guān)鍵字都是將兩個(gè)結(jié)果集合并為一個(gè)。

  • UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。

  • 而UNION ALL只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。

  • 由于UNION需要排序去重，所以 UNION ALL 的效率比 UNION 好很多。

2. TRUNCATE 與 DELETE 區(qū)別

  • TRUNCATE 是DDL語(yǔ)句，而 DELETE 是DML語(yǔ)句。

  • TRUNCATE 是先把整張表drop調(diào)，然后重建該表。而 DELETE 是一行一行的刪除，所以 TRUNCATE 的速度肯定比 DELETE 速度快。

  • TRUNCATE 不可以回滾，DELETE 可以。

  • TRUNCATE 執(zhí)行結(jié)果只是返回0 rows affected，可以解釋為沒有返回結(jié)果。

  • TRUNCATE 會(huì)重置水平線（自增長(zhǎng)列起始位），DELETE 不會(huì)。

  • TRUNCATE 只能清理整張表，DELETE 可以按照條件刪除。

一般情景下，TRUNCATE性能比DELETE好一點(diǎn)。

3. TIMESTAMP 與 DATETIME 的區(qū)別

相同點(diǎn)

TIMESTAMP 列的顯示格式與 DATETIME 列相同。顯示列寬固定在19字符，并且格式為YYYY-MM-DD HH:MM:SS。

不同點(diǎn)

TIMESTAMP

  • 4個(gè)字節(jié)存儲(chǔ)，時(shí)間范圍：1970-01-01 08:00:01~2038-01-19 11:14:07。

  • 值以UTC格式保存，涉及時(shí)區(qū)轉(zhuǎn)化，存儲(chǔ)時(shí)對(duì)當(dāng)前的時(shí)區(qū)進(jìn)行轉(zhuǎn)換，檢索時(shí)再轉(zhuǎn)換回當(dāng)前的時(shí)區(qū)。

DATETIME

  • 8個(gè)字節(jié)存儲(chǔ)，時(shí)間范圍：1000-10-01 00:00:00~9999-12-31 23:59:59。

  • 實(shí)際格式存儲(chǔ)，與時(shí)區(qū)無關(guān)。

4. 什么是聯(lián)合索引

兩個(gè)或更多個(gè)列上的索引被稱作聯(lián)合索引，聯(lián)合索引又叫復(fù)合索引。

5. 為什么要使用聯(lián)合索引

  • 減少開銷：建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。減少磁盤空間的開銷。

  • 覆蓋索引：對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機(jī)io操作。覆蓋索引是主要的提升性能的優(yōu)化手段之一。

  • 效率高：索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sqlselect from table where col1=1 and col2=2 and col3=3，假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W * 10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過索引篩選出1000w * 10% * 10% * 10%=1w，效率得到明顯提升。

6. MySQL 聯(lián)合索引最左匹配原則

  • 在 MySQL 建立聯(lián)合索引時(shí)會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配。

  • MySQL 會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

  • = 和 in 可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

7. 什么是聚集和非聚集索引

  • 聚集索引就是以主鍵創(chuàng)建的索引。

  • 非聚集索引就是以非主鍵創(chuàng)建的索引。

8. 什么是覆蓋索引

覆蓋索引（covering index）指一個(gè)查詢語(yǔ)句的執(zhí)行只用從索引頁(yè)中就能夠取得（如果不是聚集索引，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵+列值，最終還是要回表，也就是要通過主鍵再查找一次），避免了查到索引后，再做回表操作，減少I/O提高效率。

可以結(jié)合第10個(gè)問題更容易理解。

9. 什么是前綴索引

前綴索引就是對(duì)文本的前幾個(gè)字符（具體是幾個(gè)字符在創(chuàng)建索引時(shí)指定）創(chuàng)建索引，這樣創(chuàng)建起來的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前綴索引，也不能把它們用作覆蓋索引。

創(chuàng)建前綴索引的語(yǔ)法：

ALTERTABLEtable_nameADDKEY(column_name(prefix_length))

10. InnoDB 與 MyISAM 索引存儲(chǔ)結(jié)構(gòu)的區(qū)別

  • MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。

  • 而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引，所以必須有主鍵，如果沒有顯示定義，自動(dòng)為生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類型為長(zhǎng)整型。

  • InnoDB的輔助索引（Secondary Index，也就是非主鍵索引）存儲(chǔ)的只是主鍵列和索引列，如果主鍵定義的比較大，其他索引也將很大。

  • MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，索引文件葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址，指向數(shù)據(jù)文件中對(duì)應(yīng)的值，每個(gè)節(jié)點(diǎn)只有該索引列的值。

  • MyISAM主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，輔助索引可以重復(fù)，（由于MyISAM輔助索引在葉子節(jié)點(diǎn)上存儲(chǔ)的是數(shù)據(jù)記錄的地址，和主鍵索引一樣，所以不需要再遍歷一次主鍵索引）。

簡(jiǎn)單的說：

  • 主索引的區(qū)別：InnoDB的數(shù)據(jù)文件本身就是索引文件。而MyISAM的索引和數(shù)據(jù)是分開的。

  • 輔助索引的區(qū)別：InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區(qū)別。

11. 為什么盡量選擇單調(diào)遞增數(shù)值類型的主鍵

InnoDB中數(shù)據(jù)記錄本身被存于主索引（B+樹）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)慕Y(jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16），則開辟一個(gè)新的頁(yè)。

如果使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引結(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)，這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)，因此效率很高，也不會(huì)增加很多開銷在維護(hù)索引上。

如果使用非自增主鍵，由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插入到現(xiàn)有索引頁(yè)的中間某個(gè)位置，此時(shí)MySQL不得不為了將新記錄查到合適位置而移動(dòng)元素，甚至目標(biāo)頁(yè)可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來，這增加了很多開銷，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過 OPTIMIZE TABLE 來重建表并優(yōu)化填充頁(yè)面。

簡(jiǎn)單的說：

索引樹只能定位到某一頁(yè)，每一頁(yè)內(nèi)的插入還是需要通過比較、移動(dòng)插入的。所以有序主鍵可以提升插入效率。

12. 建表時(shí)，int 后面的長(zhǎng)度的意義

int占多少個(gè)字節(jié)，已經(jīng)是固定的了，長(zhǎng)度代表了顯示的大寬度。如果不夠會(huì)用0在左邊填充，但必須搭配zerofill使用。也就是說，int的長(zhǎng)度并不影響數(shù)據(jù)的存儲(chǔ)精度，長(zhǎng)度只和顯示有關(guān)。

13. SHOW INDEX 結(jié)果字段代表什么意思

Table：

  • 表名。

Non_unique：

  • 0：該索引不含重復(fù)值。

  • 1：該索引可含有重復(fù)值。

Key_name：

  • 索引名稱，如果是注解索引，名稱總是為PRIMARY。

Seq_in_index：

  • 該列在索引中的序號(hào)，從 1 開始。例如：存在聯(lián)合索引 idx_a_b_c (a,b,c)，則a的Seq_in_index=1，b=2，c=3。

Column_name：

  • 列名。

Collation：

  • 索引的排列順序：A（ascending），D (descending)，NULL (not sorted)。

Cardinality：

  • 一個(gè)衡量該索引的唯一程度的值，可以使用ANALYZE TABLE（INNODB） 或者 myisamchk -a（MyISAM）更新該值。

  • 如果表記錄太少，該字段的意義不大。一般情況下，該值越大，索引效率越高。

Sub_part：

  • 對(duì)于前綴索引，用于索引的字符個(gè)數(shù)。如果整個(gè)字段都加上了索引，則顯示為NULL。

Null：

  • YES：該列允許NULL值。

  • ”：該列不允許NULL值。

Index_type：

  • 索引類型，包括(BTREE, FULLTEXT, HASH, RTREE)。

  1. 如何解決like’%字符串%’時(shí)索引失效？

LIKE問題：like 以通配符開頭 (‘%abc…’)，mysql索引失效會(huì)變成全表掃描的操作。

  • 罪魁禍?zhǔn)资?，不是LIKE，LIKE 條件是 type = range 級(jí)別

  • %xxx%：全表掃描

  • %xxx：全表掃描

  • xxx%：range

解決辦法：

使用覆蓋索引，可以由 ALL 變?yōu)镮NDEX，為啥呢？覆蓋索引之后就能使用使用索引進(jìn)行全表掃描。這里要注意一下，使用符合索引的時(shí)候，命中一個(gè)字段就可以，不用全部命中。

15. MySQL高效分頁(yè)

存在SQL：SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的問題大：取出N+M行，丟棄前N行，返回 N ~ N+M 行的記錄，如果N值非常大，效率極差（表記錄1500w，N=10000000,M=30 需要9秒）。

解決辦法：SQL：SELECT id FROM ttl_product_info WHERE id > N LIMIT M，id 列是索引列，id > N屬于 range 級(jí)別，效率自然高，然后從位置開始取30條記錄，效率極高（表記錄1500w，N=10000000,M=30，需要0.9毫秒）。

當(dāng)然想要實(shí)現(xiàn)上述效果的前提是：

  • id是唯一索引，而且單調(diào)遞增。

  • N 的值是上一次查詢的記錄的最后一條id，（需要前端保存一下，不能直接用傳統(tǒng)的方法獲得）

  • 不支持跨頁(yè)查詢，只能按照第1，2，3，4頁(yè)這樣查詢逐頁(yè)查詢。

網(wǎng)站欄目：MySQL數(shù)據(jù)庫(kù)快問快答
URL標(biāo)題：http://www.muchs.cn/article44/ipee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、移動(dòng)網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、App設(shè)計(jì)、網(wǎng)站導(dǎo)航、虛擬主機(jī)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站建設(shè)