點擊上方藍字關注我們

表結構設計

1. 為什么要盡量設定一個主鍵?

主鍵是數據庫確保數據行在整張表唯一性的保障,即使業務上本張表沒有主鍵,也建議添加一個自增長的ID列作為主鍵.設定了主鍵之后,在后續的刪改查的時候可能更加快速以及確保操作數據范圍安全.

2. 主鍵使用自增ID還是UUID?

推薦使用自增ID,不要使用UUID.

因為在InnoDB存儲引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序),如果主鍵索引是自增ID,那么只需要不斷向后排列即可,如果是UUID,由于到來的ID與原來的大小不確定,會造成非常多的數據插入,數據移動,然后導致產生很多的內存碎片,進而造成插入性能的下降.

總之,在數據量大一些的情況下,用自增主鍵性能會好一些.

圖片來源于《高性能MySQL》: 其中默認后綴為使用自增ID,_uuid為使用UUID為主鍵的測試,測試了插入100w行和300w行的性能.

關于主鍵是聚簇索引,如果沒有主鍵,InnoDB會選擇一個唯一鍵來作為聚簇索引,如果沒有唯一鍵,會生成一個隱式的主鍵.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

3. 字段為什么要求定義為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值會占用更多的字節,且會在程序中造成很多與預期不符的情況.

4. 如果要存儲用戶的密碼散列,應該使用什么字段進行存儲?

密碼散列,鹽,用戶身份證號等固定長度的字符串應該使用char而不是varchar來存儲,這樣可以節省空間且提高檢索效率.

存儲引擎相關

1. MySQL支持哪些存儲引擎?

MySQL支持多種存儲引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多數的情況下,直接選擇使用InnoDB引擎都是最合適的,InnoDB也是MySQL的默認存儲引擎.

InnoDB和MyISAM有什么區別?

• InnoDB支持事物，而MyISAM不支持事物

• InnoDB支持行級鎖，而MyISAM支持表級鎖

• InnoDB支持MVCC, 而MyISAM不支持

• InnoDB支持外鍵，而MyISAM不支持

• InnoDB不支持全文索引，而MyISAM支持。

零散問題

1. MySQL中的varchar和char有什么區別.

char是一個定長字段,假如申請了char(10)的空間,那么無論實際存儲多少內容.該字段都占用10個字符,而varchar是變長的,也就是說申請的只是最大長度,占用的空間為實際字符長度+1,最后一個字符存儲使用了多長的空間.

在檢索效率上來講,char > varchar,因此在使用中,如果確定某個字段的值的長度,可以使用char,否則應該盡量使用varchar.例如存儲用戶MD5加密后的密碼,則應該使用char.

2. varchar(10)和int(10)代表什么含義?

varchar的10代表了申請的空間長度,也是可以存儲的數據的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能存儲的數字大小以及占用的空間都是相同的,只是在展示時按照長度展示.

3. MySQL的binlog有有幾種錄入格式?分別有什么區別?

有三種格式,statement,row和mixed.

• statement模式下,記錄單元為語句.即每一個sql造成的影響會記錄.由于sql的執行是有上下文的,因此在保存的時候需要保存相關的信息,同時還有一些使用了函數之類的語句無法被記錄復制.

• row級別下,記錄單元為每一行的改動,基本是可以全部記下來但是由于很多操作,會導致大量行的改動(比如alter table),因此這種模式的文件保存的信息太多,日志量太大.

• mixed. 一種折中的方案,普通操作使用statement記錄,當無法使用statement的時候使用row.

此外,新版的MySQL中對row級別也做了一些優化,當表結構發生變化的時候,會記錄語句而不是逐行記錄.

4. 超大分頁怎么處理?

超大的分頁一般從兩個方向上來解決.

• 數據庫層面,這也是我們主要集中關注的(雖然收效沒那么大),類似于select * from table where age < 20 limit 1000000,10這種查詢其實也是有可以優化的余地的. 這條語句需要load1000000數據然后基本上全部丟棄,只取10條當然比較慢. 當時我們可以修改為select * from table where id in (select id from table where age < 20 limit 1000000,10).這樣雖然也load了一百萬的數據,但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快. 同時如果ID連續的好,我們還可以select * from table where id < 1000000 limit 10,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的數據.

• 從需求的角度減少這種請求….主要是不做類似的需求(直接跳轉到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預測,可緩存)以及防止ID泄漏且連續被人惡意攻擊.

解決超大分頁,其實主要是靠緩存,可預測性的提前查到內容,緩存至redis等k-V數據庫中,直接返回即可.

在阿里巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.

5. 關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?

在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.

慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大?

所以優化也是針對這三個方向來的,

• 首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫.

• 分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引.

• 如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表.

6. 上面提到橫向分表和縱向分表,可以分別舉一個適合他們的例子嗎?

橫向分表是按行分表.假設我們有一張用戶表,主鍵是自增ID且同時是用戶的ID.數據量較大,有1億多條,那么此時放在一張表里的查詢效果就不太理想.我們可以根據主鍵ID進行分表,無論是按尾號分,或者按ID的區間分都是可以的. 假設按照尾號0-99分為100個表,那么每張表中的數據就僅有100w.這時的查詢效率無疑是可以滿足要求的.

縱向分表是按列分表.假設我們現在有一張文章表.包含字段id-摘要-內容.而系統中的展示形式是刷新出一個列表,列表中僅包含標題和摘要,當用戶點擊某篇文章進入詳情時才需要正文內容.此時,如果數據量大,將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度.我們可以將上面的表分為兩張.id-摘要,id-內容.當用戶點擊詳情,那主鍵再來取一次內容即可.而增加的存儲量只是很小的主鍵字段.代價很小.

當然,分表其實和業務的關聯度很高,在分表之前一定要做好調研以及benchmark.不要按照自己的猜想盲目操作.

7. 什么是存儲過程？有哪些優缺點？

存儲過程是一些預編譯的SQL語句。1、更加直白的理解：存儲過程可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現一些功能(對單表或多表的增刪改查)，然后再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。2、存儲過程是一個預編譯的代碼塊，執行效率比較高,一個存儲過程替代大量T_SQL語句 ，可以降低網絡通信量，提高通信速率,可以一定程度上確保數據安全

但是,在互聯網項目中,其實是不太推薦存儲過程的,比較出名的就是阿里的《Java開發手冊》中禁止使用存儲過程,我個人的理解是,在互聯網項目中,迭代太快,項目的生命周期也比較短,人員流動相比于傳統的項目也更加頻繁,在這樣的情況下,存儲過程的管理確實是沒有那么方便,同時,復用性也沒有寫在服務層那么好.

8. 說一說三個范式

第一范式: 每個列都不可以再拆分. 第二范式: 非主鍵列完全依賴于主鍵,而不能是依賴于主鍵的一部分. 第三范式: 非主鍵列只依賴于主鍵,不依賴于其他非主鍵.

在設計數據庫結構的時候,要盡量遵守三范式,如果不遵守,必須有足夠的理由.比如性能. 事實上我們經常會為了性能而妥協數據庫的設計.

9. MyBatis中的#

Mybatis 的Mapper.xml語句中parameterType向SQL語句傳參有兩種方式：#{}和${}

我們經常使用的是#{},一般解說是因為這種方式可以防止SQL注入，簡單的說#{}這種方式SQL語句是經過預編譯的，它是把#{}中間的參數轉義成字符串，舉個例子：

select * from student where student_name = #{name}?

預編譯后,會動態解析成一個參數標記符?：

select * from student where student_name = ?

而使用${}在動態解析時候，會傳入參數字符串

select * from student where student_name = 'hr'

#{} 這種取值是編譯好SQL語句再取值

${} 這種是取值以后再去編譯SQL語句

#{}方式能夠很大程度防止sql注入。

$方式無法防止Sql注入。

$方式一般用于傳入數據庫對象，例如傳入表名.

一般能用#的就別用$.

總結

以上是生活随笔為你收集整理的mysql 同一天多条记录只取第一条_MySQL面试高频100问（二）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。