資料庫面試題總結

# 1. 業務是什麼？ 產品的功能 使用者的行為（熱功能，熱資料）

# 2.你們公司用什麼版本資料庫？ 5.6.38 5.7.20

#3. 你們公司怎麼還在用這麼低的版本？ 我們這個業務已經執行了4年了，一直用的5.6版本，非常穩定，所以說暫時沒有打算升級。

# 4. GA時間問題漏洞怎麼說？ 答：剛去上家公司不到兩年，交接的時候已經執行了2年多了，我接手的時候版本已經是5.6.38 ，應當是做過小版本的升級。

# 5. 我們現在公司用的是5.7，你能勝任嗎？ 能。雖然我們生產中一直使用的5.6，但是我對5.7，8.0的新特性已經足夠了解了。所以我完全能夠勝任股公司的工作

# 6. 報錯？

 [root@db01 bin]# /data/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/mysql 
--datadir=/data/mysql/data /data/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[root@db01 bin]# [root@db01 ~]# yum install -y libaio-devel

# 7.關於初始化的新特性（5.6 和 5.7 ） 5.6 版本的初始化程式： /data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

初始化完成後：無密碼和無安全策略的 5.7 版本的初始化程式：

（1） /data/mysql/bin/mysqld --initialize --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
（2） /data/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data 

提示：5.7以後初始化命令變為bin/mysqld命令,提供了兩種初始化方式 第一種初始化方式：開啟臨時密碼和安全策略 第二種初始化方式和之前版本一樣，是無密碼無安全策略。 演示： 2019-04-03T03:40:15.595883Z 1 [Note] A temporary password is generated for root@localhost: **MWht)!4%sa,3** 安全策略？ 密碼的複雜度進行了要求，對於密碼的過期時間設定了限制

# 8. 啟動報錯

 

 

 思路是看日誌，日誌行中[ERROR]的

9.報錯

 

 

 以上問題是：已經啟動了，在此啟動會說檔案佔用

10.為什麼資料庫連線不上？

 沒啟動，賬號密碼錯誤，IP 、port 、 socket，分配不了連線執行緒

11.報錯

12. SQL92是什麼？

 關係型資料庫中（MySQL，Oracle，MSSQL，DB2，PG）：SQL語句的標準

13. 問題

 [root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock -h 10.0.0.51 -P3306
 Enter password:
 mysql> show processlist;
 +----+------+------------+------+---------+------+----------+------------------+
 | Id | User | Host      | db  | Command | Time | State    | Info            |
 +----+------+------------+------+---------+------+----------+------------------+
 | 15 | root | localhost  | NULL | Sleep  |  25 |          | NULL            |
 | 16 | root | db01:39646 | NULL | Query  |    0 | starting | show processlist |
 +----+------+------------+------+---------+------+----------+------------------+
 2 rows in set (0.00 sec)

14. mysql root密碼忘記了？

 1. 掛維護頁
 2. 關資料庫

 [root@db01 ~]# systemctl stop mysqld

 3. 跳過授權啟動

 [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
 --skip-grant-tables : 連線層關閉驗證模組，所有驗證表不載入。
 --skip-networking ：連線層關閉TCP/IP協議，禁止遠端訪問。

 4. 改密碼

 mysql> alter user root@'localhost' identified by '456';
 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
 mysql>
 mysql>
 mysql>
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)
 mysql> alter user root@'localhost' identified by '456';
 Query OK, 0 rows affected (0.00 sec)
 mysql>
 [root@db01 ~]# pkill mysqld
 [root@db01 ~]# systemctl start mysqld

 5.正常開啟業務

15. 初始化配置優先順序

 命令列>配置檔案>預編譯

16. mysql命令列顯示定義

 prompt=Master [\\d]>

17.報錯

 

 

 

 

18.資料庫啟動不了

 1.先看日誌
 2. 配置檔案
 3. 資料
 4. 終極大招 mysqld  --defaults-file=xxxx --user=mysql &

19. char和varchar的優缺點，生產如何選擇？

 char(11) ：***定長***的字串型別,在儲存字串時，最大字元長度11個，立即分配11個字元長度的儲存空間，如果存不滿，***空格***填充。
 varchar(11):***變******長***的字串型別看，最大字元長度11個。在儲存字串時，自動判斷字元長度，***按需***分配儲存空間。
 優缺點：
 1. char型別儲存時，相比varchar型別儲存效率更高，不需要判斷字元長度，直接分配磁碟空間
 2.varchar型別，相比char型別，按需分配空間。
 選擇？
 1. 從原則上來講，將來字串長度不固定的話，選擇varchar型別，字串長度固定不變則選擇char型別
 2. 實際上我們生產中在考慮效能問題的方面，需要有大量插入（insert）操作的應用中，我們可以考慮使用char去代替varchar。
 3. 如果我們業務中，大量是查詢類操作的應用中，資料量級又比較大情況下，變長長度資料型別，可以考慮採用varchar，一方面節省空間，可以有效的減少***索引樹***的高度，從而提高索引的優化查詢的效果。

20. 為什麼資料庫名，不能有大寫字母。

 開發環境通常是windows的，不區分大小寫，線上環境通常是Linux，區分大小寫的。

21 . 領導讓你熟悉資料庫業務

 1. 和開發混到好，直接找他要ER圖
 2. 誰也不認識：

 use xuexiao 
 show tables；
 desc xuesheng;
 show create table xuesheng；
 select  *  from xuesheng  where  id <5
 create table ceshi like xuesheng; (複製結構一樣的空表)

1. union 和 union all區別

 union 對結果集進行去重複，union all 不去重複

1. 在5.7 中，sql_mode使用的是嚴格模式

 sql_mode=only_full_group_by
 引數的意思是：
 在select 後的查詢列，必須是在group by出現過的列，或者使用函式括起來的。
 否則group by語句會報錯

image

24. b-tree與b+tree的區別?

 葉子節點中，會儲存相鄰葉子結點的指標，可以很大程度優化範圍查詢（> < >= <=）

25. 為什麼char和varchar會對索引高度產生影響？

 變長長度字串，使用char型別，在資料量級很大的時候，使用此列建索引高度變高
  為什麼表設計時設定為非空？
 針對輔助索引，如果出現大量空值，會導致索引失效

26. 聯合索引使用原則：

 1. 如果是where條件後有多列等值查詢，那麼只需要將這多列進行聯合索引即可（= and or  in）
 但是，我們建索引是儘量將唯一值多的列放在前面，儘量滿足優化器的演算法規則
 2.如果是 where A   group by B  order by C，必須按照語句的執行順序建立聯合索引

27. SQL面試題

 

 

 

 

 

 select e.emp_id,e.emp_name,count(c.course_id) from e
join c
on e.emp_id=c.emp_id
where e.dept_name='技術部';

select e.dept,t.course,count(emp_id)
from e
join c
on e.emp_id=c.emp_id
join t
on c.course_id=t.course_id
where c.score>=60

 

 

 

 select e.emp_id,e.emp_name ,
case c.score
when c.score< 60
then '不及格'
when c.score >=60 and c.scoe <70
then '及格'
when score >=70 and c.scoe <85
then '良好'
when score >=85
then '優秀'
END as '評級'
from e
join c
on e.emp_id=c.emp_id
join t
on c.course_id=t.corse_id
where t.tea_name='張三'

 

 

 select e.detp_name ,e.emp_name,t.course_name,count(c.score)
from e
join c
on e.emp_id=c.emp_id
join t
on c.course_id=t.course_id
where count(c.score)>0
order by t.course_name, c.score desc
limit 3

 

 

 select e.emp_name,t.course_name ,avg(c.score)
from e
join c
on e.emp_id=c.emp_id
join t
on c.course_id=t.course_id
where t.course_name in (‘資料庫’,'java')
group by e.emp_name
having count(t.course_name)>1 

28 . 報錯原因是什麼?

 

 

 1.資料庫沒有啟動
2.socket檔案位置不對

29. 資料庫損壞？

 物理損壞
 邏輯損壞

30 . 從mysqldump 全備中獲取 庫和表的備份

 1、獲得表結構

 # sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
 

​
 2、獲得INSERT INTO 語句，用於資料的恢復
 ​

 # grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

​
 3.獲取單庫的備份
 ​

 # sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

31.面試題

 

 

 

 1. 聯合索引： idx(status , signal)
2. 查詢條件調整  where status   and signal

 

 

 資料和型別 ，not null   ，註釋

 

 

 SQL語句是資料DDL操作，是屬於列的新增操作
直接執行時會產生表鎖，對業務的影響較大，資料量大，業務量大時，對於業務影響較大。
所以我們推薦使用 pt-osc 工具來進行online DDL,減少對業務的影響

 

 

 

 

 

 A->B架構轉換
1. 主庫寫壓力大，可以通過多級主從減輕主庫的壓力
B-》A架構轉換
2.業務讀多寫少，通過多從庫實現讀寫分離和讀的負載均衡，另外可以配合MHA等架構進行高可用

32.報錯

 

 

 

33 MySQL5.7中如何獨立undo

 innodb_max_undo_log_size = 128M
 innodb_undo_log_truncate = ON
 innodb_undo_logs = 128
 innodb_undo_tablespaces = 3
 innodb_purge_rseg_truncate_frequency = 10

34 mysqldump 匯出時，去掉auto_increment

 mysqldump -uroot -p -d test -S /tmp/mysql.sock | sed 's/AUTO_INCREMENT=[0-9]*\s*//g' > test.sql

35 MySQL 5.7 如何開啟ssl功能

1.生成證照檔案

 [root@mycat1 ~]# mysql_ssl_rsa_setup  
 [root@mycat1 ~]# ll /data/mysql/*.pem
 -rw------- 1 root root 1675 Jun 10 16:00 /data/mysql/ca-key.pem
 -rw-r--r-- 1 root root 1107 Jun 10 16:00 /data/mysql/ca.pem
 -rw-r--r-- 1 root root 1107 Jun 10 16:00 /data/mysql/client-cert.pem
 -rw------- 1 root root 1675 Jun 10 16:00 /data/mysql/client-key.pem
 -rw------- 1 root root 1679 Jun 10 16:00 /data/mysql/private_key.pem
 -rw-r--r-- 1 root root  451 Jun 10 16:00 /data/mysql/public_key.pem
 -rw-r--r-- 1 root root 1107 Jun 10 16:00 /data/mysql/server-cert.pem
 -rw------- 1 root root 1675 Jun 10 16:00 /data/mysql/server-key.pem

2.證照檔案介紹

  ca-key.pem         #CA私鑰
  ca.pem             #自籤的CA證照，客戶端連線也需要提供
  client-cert.pem    #客戶端連線伺服器端需要提供的證照檔案
  client-key.pem     #客戶端連線伺服器端需要提供的私鑰檔案
  private_key.pem    #私鑰/公鑰對的私有成員
  public_key.pem     #私鑰/公鑰對的共有成員
  server-cert.pem    #伺服器端證照檔案
  server-key.pem     #伺服器端私鑰檔案

3. 未完待續

36. 為什麼很多人喜歡使用varchar(255)?

 1. InnoDB儲存引擎的表索引單一欄位或字首長度，最長是767位元組(bytes)。
 存中文時，utf8字符集，那麼767/3=255,
 如果是utf8mb4應當是，767/4=191.
 2. 字串長度值視實際資料長度，需佔用 1 或 2 個位元組儲存。
   當實際資料長度 <= 255 時，varchar欄位長度 = 1 + N
   當實際資料長度 > 255 時，varchar欄位長度 = 2 + N
 ​
 總結：其實是很多開發人員的一些習慣吧，但是往往不知道原因。所以呢帶大家普及一下。