## 為什么要分庫分表？ 分庫主要針對的在單庫情況下并發限制問題或單庫的磁盤限制問題； 分表主要針對的是單表的數據量很大，影響讀性能； ##分庫分表之后事務是如何解決的？ 在每個庫中記錄一張流水表 ## 三大范式是什么？ * 第一范式：數據表中的每一列（每個字段）必須是不可拆分的最小單元，也就是確保每一列的原子性； * 第二范式（2NF）：滿足1NF后，要求表中的所有列，都必須依賴于主鍵，而不能有任何一列與主鍵沒有關系，也就是說一個表只描述一件事情； * 第三范式：必須先滿足第二范式（2NF），要求：表中的每一列只與主鍵直接相關而不是間接相關，（表中的每一列只能依賴于主鍵）； ## 怎么區分三大范式？ 第?一范式和第二范式在于有沒有分出兩張表，第二范式是說一張表中包含了所種不同的實體屬性，那么要必須分成多張表， 第三范式是要求已經分成了多張表，那么一張表中只能有另一張表中的id（主鍵），而不能有其他的任何信息（其他的信息一律用主鍵在另一表查詢）。 ## 數據庫五大約束是什么？ 1.primary KEY:設置主鍵約束； 2.UNIQUE：設置唯一性約束，不能有重復值； 3.DEFAULT 默認值約束，height DOUBLE(3,2)DEFAULT 1.2 height不輸入是默認為1,2 4.NOT NULL：設置非空約束，該字段不能為空； 5.FOREIGN key :設置外鍵約束。 ## 主鍵是什么，怎么設置主鍵？ 主鍵默認非空，默認唯一性約束，只有主鍵才能設置自動增長，自動增長一定是主鍵，主鍵不一定自動增長； 在定義列時設置：ID INT PRIMARY KEY 在列定義完之后設置：primary KEY（id） ## 數據庫的外鍵是什么？ 只有INNODB的數據庫引擎支持外鍵。 不見已使用基于mysql的物理外鍵，這樣可能會有超出預期的后果。推薦使用邏輯外鍵，就是自己做表設計，根據代碼邏輯設定的外鍵，自行實現相關的數據操作。 ## innodb和myisam有什么區別？ * InnoDB支持事務，而MyISAM不支持事物，崩潰后無法安全恢復，表鎖非常影響性能 * InnoDB支持行級鎖，而MyISAM支持表級鎖 * InnoDB支持MVCC，實現了四個標準的隔離級別 而MyISAM不支持 * InnoDB 表是基于聚族索引建立的，聚族索引對主鍵查詢有很高的性能 * InnoDB支持外鍵，而MyISAM不支持 * InnoDB文件結構是.frm和.ibd/.ibdata，而MyISAM是.frm、.myd、.myi * MyISAM 存儲引擎已經有了20年的歷史，在1995年時，MyISAM 是 MySQL 唯一的存儲引擎，服務了20多年，即將退居二線。隨著mysql5.7，8版本的提升，myisam優點已經逐漸被 InnoDB 實現了。比如全文索引，表空間優化，臨時表優化，高效的count(\*) >獨享表空間存儲方式使用.ibd文件，并且每個表一個ibd文件 共享表空間存儲方式使用.ibdata文件，所有表共同使用一個ibdata文件 ## 什么是索引？ 索引是一種數據結構,可以幫助我們快速的進行數據的查找. ## 索引是個什么樣的數據結構呢? 索引的數據結構和具體存儲引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引. ## innodb索引的實現原理是什么？ InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數據就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之后獲得行數據。若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行數據。 ## btree和hash類型的索引有什么不同？ 首先要知道Hash索引和B+樹索引的底層實現原理: hash索引底層就是hash表,進行查找時,調用一次hash函數就可以獲取到相應的鍵值,之后進行回表查詢獲得實際數據.B+樹底層實現是多路平衡查找樹.對于每一次的查詢都是從根節點出發,查找到葉子節點方可以獲得所查鍵值,然后根據查詢判斷是否需要回表查詢數據. 那么可以看出他們有以下的不同: * hash索引進行等值查詢更快(一般情況下),但是卻無法進行范圍查詢. 因為在hash索引中經過hash函數建立索引之后,索引的順序與原順序無法保持一致,不能支持范圍查詢.而B+樹的的所有節點皆遵循(左節點小于父節點,右節點大于父節點,多叉樹也類似),天然支持范圍. * hash索引不支持使用索引進行排序,原理同上. * hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測.**AAAA**和**AAAAB**的索引沒有相關性. * hash索引任何時候都避免不了回表查詢數據,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢. * hash索引雖然在等值查詢上較快,但是不穩定.性能不可預測,當某個鍵值存在大量重復的時候,發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定,對于所有的查詢都是從根節點到葉子節點,且樹的高度較低. 因此,在大多數情況下,直接選擇B+樹索引可以獲得穩定且較好的查詢速度.而不需要使用hash索引. ## 什么是覆蓋索引？ 簡單的說，select的數據列只用從索引中就能夠取得，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。 覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引不存儲索引列的值，所以mysql**只能用B-tree索引**做覆蓋索引。 ## B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據，什么是聚簇索引? 在B+樹的索引中,葉子節點可能存儲了當前的key值,也可能存儲了當前的key值以及整行的數據,這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個鍵來建立聚簇索引. 當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行數據,因此不用再次進行回表查詢. ## 在建立索引的時候,都有哪些需要考慮的因素呢? 建立索引的時候一般要考慮到字段的使用頻率,經常作為條件進行查詢的字段比較適合.如果需要建立聯合索引的話,還需要考慮聯合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對表造成太大的壓力.這些都和實際的表結構以及查詢方式有關. ## 聯合索引/多列索引的注意事項是什么？ MySQL可以使用多個字段同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要命中索引,需要按照建立索引時的字段順序挨個使用,否則無法命中索引. 具體原因為: MySQL使用索引時需要索引有序,假設現在建立了"name,age,school"的聯合索引,那么索引的排序為: 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進行排序. 當進行查詢時,此時索引僅僅按照name嚴格有序,因此必須首先使用name字段進行等值查詢,之后對于匹配到的列而言,其按照age字段嚴格有序,此時可以使用age字段用做索引查找,,,以此類推.因此在建立聯合索引的時候應該注意索引列的順序,一般情況下,將查詢需求頻繁或者字段選擇性高的列放在前面.此外可以根據特例的查詢或者表結構進行單獨的調整. ## 導致索引失效的原因有哪些？ * 列參與了數學運算或者函數； * 如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)； * 對于多列索引，不符合最左匹配的命中規則； * like查詢是以%開頭； * 如果直接查比用索引快，那么數據庫會自動選擇最優方式，不用索引； * in 和 not in 也要慎用，否則會導致全表掃描。 ## 主鍵使用自增ID還是UUID? 推薦使用自增ID,不要使用UUID. 因為在InnoDB存儲引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序),如果主鍵索引是自增ID,那么只需要不斷向后排列即可,如果是UUID,由于到來的ID與原來的大小不確定,會造成非常多的數據插入,數據移動,然后導致產生很多的內存碎片,進而造成插入性能的下降. 總之,在數據量大一些的情況下,用自增主鍵性能會好一些. ## 字段為什么要求定義為not null? MySQL官網這樣介紹: > NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte. null值會占用更多的字節,且會在程序中造成很多與預期不符的情況. ## drop、delete與truncate分別在什么場景之下使用？ 我們來對比一下他們的區別： drop table * 1)屬于DDL * 2)不可回滾 * 3)不可帶where * 4)表內容和結構刪除 * 5)刪除速度快 truncate table * 1)屬于DDL * 2)不可回滾 * 3)不可帶where * 4)表內容刪除 * 5)刪除速度快 delete from * 1)屬于DML * 2)可回滾 * 3)可帶where * 4)表結構在，表內容要看where執行的情況 * 5)刪除速度慢,需要逐行刪除 總結： **不再需要一張表的時候，用drop** **想刪除部分數據行時候，用delete，并且帶上where子句** **保留表而刪除所有數據的時候用truncate** ## 關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過? 在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們. 慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大? 所以優化也是針對這三個方向來的, * 首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫. * 分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引. * 如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表. ## UNION、UNION ALL區別？ union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序； union All：對兩個結果集進行并集操作，包括重復行，不進行排序； ## 你的sql優化常用技巧有哪些？ 1. 去掉不必要的查詢和搜索字段 2. 爭取命中索引，或者根據已有的查詢情景簡歷合理的索引 3. 不要使用select *：以提高查詢效率，減少輸出的數據量，提高傳輸速度 4. 盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理和是否分頁 5. 減少訪問數據庫的次數，業務代碼邏輯優化，避免for循環 6. 使用表的別名(Alias)：當在SQL語句中連接多個表時, 減少解析的時間，減少那些由Column歧義引起的語法錯誤 7. 字段設計not null 8. 索引可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因此索引并非越多越好，合理有效的使用 9. 盡量避免大事務操作，提高系統并發能力 ## 數據庫事務的使用的規范有哪些？ 1. 控制事務大小，減少鎖定的資源量和鎖定時間長度。 2. 所有的數據檢索都通過索引來完成，從而避免因為無法通過索引加鎖而升級為表鎖。 3. 減少基于范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的數據。 4. 在業務條件允許下，盡量使用較低隔離級別的事務隔離。減少隔離級別帶來的附加成本。 5. 合理使用索引，讓innodb在索引上面加鎖的時候更加準確。 6. 在應用中盡可能做到訪問的順序執行（串行）。 7. 如果容易死鎖，就可以考慮使用表鎖來減少死鎖的概率。 ## InnoDB怎么實現的事務ACID特性？ * redo log重做日志用來保證事務的持久性 * undo log回滾日志保證事務的原子性 * undo log+redo log保證事務的一致性 * 鎖（共享、排他）用來保證事務的隔離性 undo log 實現如下兩個功能：1.實現事務回滾 2.實現MVCC undo log和redo log記錄物理日志不一樣，它是邏輯日志。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。 推薦閱讀，加深理解 :[https://www.cnblogs.com/jianzh5/p/11643151.html]