|
1.什么是mysql？ mysql是關(guān)系型數(shù)據(jù)庫，比較常見的關(guān)系型數(shù)據(jù)庫有mysql/oracle/sql server/sqlite ；存數(shù)據(jù)使用的是表，這樣的話結(jié)構(gòu)比較固定，易于維護(hù)，我們通常使用crud去操作表中的數(shù)據(jù)；這里的crud就是insert,delete,update,select 與之相對(duì)的是非關(guān)系型數(shù)據(jù)庫，如redis/mongoDB,是一種key-value形式的存儲(chǔ)，不理解的可以簡(jiǎn)單的看作json那樣存儲(chǔ)的； 至于兩者之間的優(yōu)缺點(diǎn)可以簡(jiǎn)單參考這個(gè)大哥的博客 2.mysql中的sql語言有幾種？ 在mysql中，有4種語言： （1）DDL -- Data Definition Language，中文是數(shù)據(jù)庫定義語言，也就是create(創(chuàng)建數(shù)據(jù)庫或者數(shù)據(jù)表),alter(修改數(shù)據(jù)表相關(guān)信息),drop(刪除數(shù)據(jù)庫或者數(shù)據(jù)表)這幾個(gè)常用關(guān)鍵字， 只有創(chuàng)建表的時(shí)候，或者我們后期需要給表加索引之類的可能會(huì)用到，平常用的比較少 （2）DML-- Data Manipulation Language，中文是數(shù)據(jù)庫操作語言，這個(gè)也就是我們常用的crud操作，注意，我們還需要記住一個(gè)explain關(guān)鍵字，這個(gè)explain是用于分析你寫的sql語句執(zhí)行效率的一個(gè)好用的工具，可以查看到有沒有使用到索引等信息 （3）一筆帶過 DCL：數(shù)據(jù)庫控制語言，例如Grant —為用戶授予權(quán)限 revoke–撤回授權(quán)權(quán)限 TCL：事務(wù)控制語言，例如Commit --保存已完成的內(nèi)容，rollback —回滾 3.什么sql最影響mysql效率？ 不考慮讀寫分離的情況下，最影響mysql的效率的就是查詢語句，也就是你寫的那一大串的select xx from xx這種，所以我們一般就是優(yōu)化查詢語句； 4.如何分析sql的執(zhí)行效率呢？ 我們要找到一些執(zhí)行的很慢的sql，首先我們需要開啟mysql的慢查詢?nèi)罩荆O(shè)置時(shí)間限制，當(dāng)超過這個(gè)時(shí)間限制的就記錄到日志文件中，然后我們就用下面這種方式分析就ok了！那么怎么開啟慢查詢?nèi)罩灸兀?a target="_blank">看這里 使用explain查看sql的執(zhí)行計(jì)劃,如下所示，有興趣的可以看看執(zhí)行計(jì)劃中每個(gè)字段的意思，例如possible_keys 表示可能使用的索引，key 表示實(shí)際使用的索引等等，有興趣的可以看看這個(gè)大哥的博客
注：還有一種更加細(xì)致的分析sql性能消耗的，使用show profile，有興趣的可以看看這個(gè) 5.通常如何優(yōu)化查詢sql呢？ 從上面我們知道了如何分析一條sql中的效率，最常用的優(yōu)化方案便是加索引； 6.索引是什么？ 索引是個(gè)什么東西呢？這里涉及到數(shù)據(jù)結(jié)構(gòu)中的一個(gè)B+樹，索引在磁盤中是以文件的形式存在，其實(shí)可以看做一張表，也是會(huì)占用物理空間的?。。?/p> 通俗易懂一點(diǎn)的解釋就是書籍目錄：在word文檔中目錄肯定也是占物理空間的對(duì)吧，而且我們點(diǎn)擊目錄就可以直接跳轉(zhuǎn)到對(duì)應(yīng)的正文處，所以我們可以大概的知道索引可能存的是實(shí)際數(shù)據(jù)的物理地址空間(后面也可以存實(shí)際的數(shù)據(jù)哦！取決于引擎) 那么問題來了，所有索引都是這么存的嗎？ 7.索引的分類 以innodb為例，索引分為兩種： （1）聚集（clustered）索引，也叫聚簇索引，一個(gè)表只能有一個(gè)。 （2）非聚集（unclustered）索引，也叫稀疏索引，或者叫做普通索引，多個(gè)。 看名字就覺得想放棄了，其實(shí)聚集索引就是主鍵索引，其他的索引都叫做非聚集索引(比如聯(lián)合索引，唯一索引啥的，這些都是非聚集索引的邏輯分類) 8.兩種索引的不同之處 首先說一下mysql中innodb存數(shù)據(jù)的方式，首先我們要有這么一個(gè)想象的畫面，數(shù)據(jù)庫表的數(shù)據(jù)，都是存放在聚集索引下面的，下面畫個(gè)圖就了解了： 有這么一張表，id是主鍵(這里有個(gè)地方需要注意，對(duì)于innodb引擎，如果我們?cè)诮ū碚Z句那里有指定主鍵，那么就ok，沒有指定主鍵，mysql就會(huì)偷偷的創(chuàng)建一個(gè)主鍵索引，我們是看不到的，表中也沒有)
那么主鍵索引應(yīng)該就是這樣的，下圖所示，這個(gè)時(shí)候?qū)嶋H的數(shù)據(jù)就是存到主鍵索引的葉子節(jié)點(diǎn)中的！
現(xiàn)在問題來了，那么非聚集索引是怎么存的呢？例如我把上表中name字段添加索引... 然后我們思考，非聚集索引的結(jié)構(gòu)也是b+樹，和聚集索引一樣，不同的是葉子節(jié)點(diǎn)中村的就不是實(shí)際的數(shù)據(jù)了，而是主鍵的值； 我們首先查詢到葉子節(jié)點(diǎn)的主鍵的值，然后通過這個(gè)主鍵的值再到聚集索引中查詢一次，才能拿到真正的數(shù)據(jù)！也就是說，通過非聚集索引查數(shù)據(jù)，一般要查詢兩次才行！第一次查詢出來的是主鍵的值，第二次通過主鍵的值去聚集索引中查詢實(shí)際的數(shù)據(jù)(用專業(yè)一點(diǎn)的詞語叫做回表)； 為了更清晰的理解聚集索引和非聚集索引，我在網(wǎng)上偷了一張比較好理解的圖(可惜不是b+樹，但是原理一樣)，如下所示，左邊是聚集索引，葉子節(jié)點(diǎn)存的是實(shí)際的數(shù)據(jù)；右邊是非聚集索引，存的是聚集索引的值；
9.是否所有的非聚集索引查詢的時(shí)候都需要查詢兩次呢（知識(shí)點(diǎn)：覆蓋索引）？ 在第8點(diǎn)中說了一般是查詢兩次，通常有 "一般" 這種詞語就說明還有特殊情況； 例如還是以上面的那個(gè)表為例，首先給name添加索引，那么看看這個(gè)sql：select id from user where name = "小王"； 首先這個(gè)sql肯定會(huì)走非聚集索引name，找到葉子節(jié)點(diǎn)的存的id為1，這時(shí)你覺得還需要把1拿去再去查一次聚集索引么？肯定不需要啊，因?yàn)槲覀僺ql中只需要這個(gè)id就行了啊，已經(jīng)查詢出來了，為什么還要去把那一條數(shù)據(jù)查詢出來呢？ 這個(gè)時(shí)候只需要查詢一次就ok了，專業(yè)名詞叫做覆蓋索引，專業(yè)解釋為：如果一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值，稱為'覆蓋索引’，即只需掃描索引而無須回表。 10.主鍵自增比較好還是uuid比較好？ 經(jīng)?？吹降闹麈I是自增好點(diǎn)還是隨機(jī)字符串(例如雪花算法，uuid)好？其實(shí)數(shù)據(jù)量小的話是自增好，首先占用的空間小啊，uuid那么多位，另外還因?yàn)橐稽c(diǎn)，這里用到一點(diǎn)平衡多叉樹的知識(shí)，就是分裂(建議新手去看看多叉樹的分裂，b+樹不是很好理解，點(diǎn)擊這里看看2-3-4樹的分裂)； 而且看到題目中有主鍵兩個(gè)字，我們就知道肯定跟聚集索引有關(guān)呀！廢話，因?yàn)槲覀冃枰玫街麈I去構(gòu)建聚集索引呀，那么如果不是自增的，比如我們?cè)賗nsert數(shù)據(jù)的時(shí)候，主鍵分別為1，10，，5，20，4，3，1這種，為了保存平衡，那么b+樹的節(jié)點(diǎn)就會(huì)分裂，重新組成新的節(jié)點(diǎn)，當(dāng)數(shù)據(jù)量很大的時(shí)候，性能影響還是很大的，而自增的話，直接就在b+樹后面添加節(jié)點(diǎn)就行了，不需要分裂！但是使用自增還有一個(gè)壞處，就是id可預(yù)測(cè)性，簡(jiǎn)直為爬蟲等一些東西打開了方便之門... 如果是在分庫分表的情況下還是用隨機(jī)字符串吧，確保全局id的唯一性，有興趣的還可以再深入了解一下； 11.B+樹為什么比B樹更適合作為索引，或者說為什么innodb使用B+樹作為索引？ 這個(gè)問題答案我就借用一下這個(gè)老哥的； 其實(shí)就是要明白B樹和B+樹的區(qū)別，B樹所有節(jié)點(diǎn)都存了數(shù)據(jù)，而B+樹只有葉子節(jié)點(diǎn)才存了數(shù)據(jù)嘛，這就使得B+樹的效率比較穩(wěn)定，而且由于每個(gè)葉子節(jié)點(diǎn)之間也有指針相互連接，這樣使得范圍查詢會(huì)很方便，不需要從根節(jié)點(diǎn)再遍歷一次； 12.除了B+樹，你還能想到使用什么數(shù)據(jù)結(jié)構(gòu)當(dāng)作索引呢？ 第一想法肯定是hash表啊，用過hashmap的都知道，這東西是真的好用，時(shí)間復(fù)雜度O(1); 那么問題來了，這么好用的東西為什么mysql的innodb引擎就是不用呢？留個(gè)印象，mysql中的MEMORY存儲(chǔ)引擎索引默認(rèn)用的是hash 例如，你想想你把下面這些數(shù)據(jù)放到hashmap中，(1,10),(2,20),(3,30),(4,40)，然后我需要找到key大于2小于4的數(shù)據(jù)，怎么找？如果我還要對(duì)key進(jìn)行排序再輸出呢，怎么辦？ 所以用hash作為索引，有幾點(diǎn)需要注意： （1）Hash索引僅僅能滿足“=”，“IN”，不能支持范圍查詢 （2）對(duì)于排序操作Hash索引也滿足不了 （3）Hash索引不能避免表掃描 （4）當(dāng)有大量數(shù)據(jù)的Hash值相等的時(shí)候Hash索引的性能大打折扣（這個(gè)也就是hash中經(jīng)典的桶碰撞問題） 13.mysql為什么不用MyISAM引擎作為默認(rèn)的引擎呢？ 這個(gè)應(yīng)該知道一點(diǎn)常識(shí)，MyISAM引擎不支持事務(wù)，不支持外鍵，默認(rèn)是表鎖，查詢的效率比innodb要高等等， 還會(huì)經(jīng)常和innodb做比較； 我再多說一點(diǎn)：首先，通過前面這么大的篇幅我們知道了如果使用innodb引擎，那么主索引文件(或者聚集索引文件)和數(shù)據(jù)文件實(shí)際上是放在一起的； 然后，MyISAM引擎引擎索引文件和數(shù)據(jù)文件是分開的?。?！注意，是分開的，也就是說B+樹葉子節(jié)點(diǎn)存放的是實(shí)際行數(shù)據(jù)的指針，例如下面這樣（圖是偷的(￣▽￣)ノ），該引擎下所有的索引都是這樣的，注意，存的是物理地址??！
14.什么是最左匹配原則？ 在說這個(gè)問題之前，我們回憶一下，索引的邏輯分類有單列索引和組合索引吧，其中除了主鍵索引數(shù)據(jù)聚集索引，其他的索引都屬于非聚集索引。最左原則就是對(duì)于組合索引來說的！ 還有一點(diǎn)，B+樹索引的葉子節(jié)點(diǎn)是已經(jīng)排序好了的，我們才能根據(jù)索引值去走B+樹查詢。 例如我們用最開始我們的表，我們對(duì)name和age創(chuàng)建一個(gè)組合索引：
那么現(xiàn)在我們需要將這個(gè)name和age看成一個(gè)整體，例如就這個(gè)組合索引的B+索引如下，我就簡(jiǎn)單畫一下，順便多添加了幾行數(shù)據(jù)：
上圖我們能發(fā)現(xiàn)什么？首先是根據(jù)組合索引的第一個(gè)字段進(jìn)行排序的，當(dāng)?shù)谝粋€(gè)字段是一樣的，才會(huì)繼續(xù)對(duì)第二個(gè)字段進(jìn)行排序；那么你直接使用select id from age = "5"，你覺得會(huì)走這個(gè)索引么？肯定不會(huì)啊，因?yàn)槿~子節(jié)點(diǎn)的age明顯都不是排序的啊,怎么找??？ 所以只要有點(diǎn)數(shù)學(xué)基礎(chǔ)，即使組合索引包含三列， |
|
|
