體系結(jié)構(gòu)及基礎(chǔ)管理
1.MySQL有哪些安裝方式？
二進制安裝
編譯安裝
2.MySQL 5.6 、 5.7 安裝過程有什么區(qū)別？
初始化：
5.6 : /data/app/mysql/scripts/mysql_install_db
5.7 : mysqld --initialize-insecure / --initialize
3.MySQL 5.7、 8.0 在用戶管理有什么區(qū)別，請舉例說明？
8.0 之前：
用戶管理：
1\. create user [<u>oldboy@’10.0.0.%’identified</u>](mailto:oldboy@'10.0.0.%25'identified) by ‘123’;
2\. grant all on *.* to [<u>oldboy@’10.0.0.%</u>](mailto:oldboy@'10.0.0.%25)’;
或者：
Grant all on *.* to [<u>oldguo@’10.0.0.%</u>](mailto:oldguo@'10.0.0.%25)’ identified by ‘123’;
8.0 以后：
1. 只能先建用戶后授權(quán)
2. 加密方式：從native插件方式改變?yōu)榱藄ha2加密插件。遠程客戶端、主從、MHA架構(gòu)等。
4.請介紹MySQL授權(quán)表有哪些，都有什么作用？
User ：用戶、密碼、加密插件、全庫級別的用戶權(quán)限。
Db ：單庫級別
tables_priv : 表級別
columns_priv：列級別
procs_priv ：存儲過程
proxies_priv：代理
user 用戶所有信息+*.*級別權(quán)限
db 用戶部分信息+wordpress級別權(quán)限
tables_priv 用戶的部分信息+wordpress.t1級別信息
5.簡述你在工作中MySQL 遠程連接的方式？
musql &
mysql -uroot -p
mysql -uroot -p -h 10.0.0.51 -P3306
sqyog
navicat
6.請列舉MySQL配置文件的讀取順序？
mysqld --verbose --help | grep -A 1 'Default options'
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
7.請簡述MySQL配置文件結(jié)構(gòu)？列舉至少5項基礎(chǔ)配置項？
標簽項
[mysqld] [mysqld_safe] [server]
配置項
datadir=/data/3306/data
basedir=/data/app/mysql
server_id=3306
log_error=/data/3306/mysql.log
socket=/tmp/mysql.sock
8.請列舉MySQL的啟動和關(guān)閉的方式？
啟動： sys-v、systemd → support-fils/mysql.server→mysqld_safe→mysqld
關(guān)閉：sys-v、systemd → support-fils/mysql.server ，shutdown，mysqladmin shutdown
9請簡述MySQL忘記本地root密碼的處理方法和其中的原理？
1.關(guān)數(shù)據(jù)庫
2.啟動到安全模式： mysqld_safe --skip-grant-tables --skip-networking &
3.flush privileges; 手工加載表信息
4.修改密碼：grant \ alter \set password
4.重啟數(shù)據(jù)庫
10.你們公司使用多實例環(huán)境嗎？在什么地方用的？
1.測試環(huán)境
2.分布式架構(gòu)
3.備用系統(tǒng)
11.如何查看數(shù)據(jù)庫的當前連接情況？
select * from information_schema.processlist;
show full processlist;
12.簡述數(shù)據(jù)庫啟動不了，你的排查過程？
查看錯誤日志
（1）看日志 log_error
（2）[root@db01 ~]# mysqld --defaults-file=/etc/my.cnf &
13.升級至5.7 、8.0，升級方式上有什么區(qū)別？8.0要注意什么？
區(qū)別：升級到5.7 ，需要執(zhí)行mysql_upgrade,升級到8.0,直接用高版本軟件掛低版本庫啟動。
升級到8.0以前，一定要提前備份。
升級時候建議，不要跨多版本。5.6最新版升級到5.7,5.7最新版升級到8.0
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"
14.mysql查詢表中數(shù)據(jù)中文字體亂碼，原因可能是什么？如何修改？
字符集問題。
一般是客戶端軟件的字符集設(shè)定和服務(wù)器端不一致。
character_set_server=utf8mb4
Set names utf8mb4;
替換字符集：邏輯導出數(shù)據(jù)，庫alter，替換備份中的字符集，導入數(shù)據(jù)。
15.什么是實例？
公司 = boss+經(jīng)理+員工+辦公室
實例 = mysqld + master thread + 干活的thread + 內(nèi)存結(jié)構(gòu)
16.簡述MySQL的程序結(jié)構(gòu)？
Mysqld = server層（連接層+SQL層） + 存儲引擎層
17.簡述一條Select語句的執(zhí)行過程？
sql 首先通過連接層
1.驗證鏈接協(xié)議，協(xié)議分別是socked本地連接和TCP/IP 遠程連接，驗證通過
2.會檢查ip 用戶名 端口 密碼的合法性，再交給連接層
3.連接層不具備處理sql語句，會交給sql層來處理
sql層
1.sql層拿到連接層傳來的sql語句，進行語法，語義，權(quán)限，sql_mode驗證，驗證通過交給解析器
2.解析器對sql語句進行解析，形成多種執(zhí)行樹
3.解析完通過優(yōu)化器算法，生成代價最低的執(zhí)行計劃
4.再由執(zhí)行器，最終真正運行sql語句
存儲引擎層:
負責根據(jù)sql層的執(zhí)行結(jié)果，從磁盤上拿數(shù)據(jù)；
將16進制的磁盤數(shù)據(jù)，交給sql結(jié)構(gòu)化 化成表，連接層的專用線程返回給用戶
18.請簡述MySQL的邏輯結(jié)構(gòu)和宏觀物理結(jié)構(gòu)？
邏輯結(jié)構(gòu)：庫，表，數(shù)據(jù)行
物理結(jié)構(gòu)：
Myisam： myi myd frm
InnoDB: ibd 、frm、ibdata、ibtmp、undo、redo
19.請簡述段、區(qū)、頁的構(gòu)成？
一個表就是一個段。
一個段有多個區(qū)構(gòu)成，
一個區(qū)（16k）有連續(xù)的64個頁 1M大小
索引篇
一、列舉MySQL索引類型
Btree
Rtree
Fulltest
HASH
二mysql為什么選擇Btree索引查找算法？
btree可以快速鎖定范圍查詢，掃描次數(shù)是一樣的
三、mysql索引算法演變：btree-b+tree-B*tree的區(qū)別？
1.b-tree是btree的變體，普通的葉子節(jié)點沒有指針，想要不遍布全表查詢，只能等值查詢
2.所以增加了B+tree,B+tree的葉子節(jié)點有指針，對于范圍查找和io優(yōu)化，不會遍布全表查詢。><=
3.是B+樹的變體，在B+樹的非根和非葉子結(jié)點再增加指向兄弟的指針；
四、mysql 聚簇索引如何構(gòu)建B+tree?
葉子節(jié)點：聚簇索引組織表，按照聚簇索引順序，物理有序的存儲的是表的數(shù)據(jù)行
枝節(jié)點：保存下層葉子節(jié)點的ID范圍+指針
根節(jié)點：保存下層枝節(jié)點的id范圍和指針
五、mysql輔助索引如何構(gòu)建B+tree?
葉子節(jié)點：ID+輔助索引列排序后生成。
枝節(jié)點： 保存下層葉子節(jié)點輔助索引鍵值的范圍+指針
根節(jié)點： 保存下層枝節(jié)點輔助索引鍵值范圍+指針
六、什么是回表查詢？如何減少回表？
通過輔助索引查找到ID，回到聚簇索引查詢。
產(chǎn)生的問題：會導致IO增加
減少：
1.增加查詢條件的精確度
2.覆蓋索引。覆蓋大部分查詢條件和數(shù)據(jù)
3.優(yōu)化器算法
七、索引樹高度影響因素有哪些？
索引樹高度指的是，有幾層，最低兩層，建議4層。
影響因素：
1.數(shù)據(jù)行多，
解決:分區(qū)表，歸檔表（轉(zhuǎn)儲表）分庫分表
2.索引列的長度
解決：前綴索引
3.數(shù)據(jù)類型選擇不合適
解決：選擇簡短的
八、如何獲取執(zhí)行計劃？如何理解執(zhí)行計劃的輸出？
desc或explain獲取執(zhí)行計劃
select_type： 查詢類型
table: 哪個表
type: 使用索引方式
key_len: 索引覆蓋長度
rows: 預(yù)估需要獲取的數(shù)據(jù)行
extra: 額外信息
九、聯(lián)合索引應(yīng)用有哪些原則？哪些情況不能覆蓋到聯(lián)合索引？
遵循最左原則。abc ab a
沒有最左列。查詢條件中不等值查詢
十、索引有哪些自優(yōu)化能力？
ICP
MRR
CHANGE BUFFER
AHI
十一、mysql8.0在索引優(yōu)化中添加的新特性有哪些？
MySQL8.0 支持隱藏索引(invisible index)，不可見索引
隱藏索引不會被優(yōu)化器使用，但需要維護。
降序索引
MySQL8.0真正支持降序索引(descending index)。
只有InnoDB存儲引擎支持降序索引，只支持BTREE降序索引
函數(shù)索引
MySQL8.0支持在索引中使用函數(shù)(表達式)的值。
支持降序索引，支持JSON數(shù)據(jù)索引。、
十二.請簡述優(yōu)化器算法：ICP/MRR是如何實現(xiàn)的？
ICP:在執(zhí)行多條件判斷時,判斷了第一個條件后,將過濾后的結(jié)果下推到引擎層繼續(xù)過濾剩余條件,最后再回表取出符合所有條件的行內(nèi)容.大大減少了回表IO
MRR:先將索引到的數(shù)據(jù)行放到緩存中read_rnd_buffer,然后進行排序,如果得到有序結(jié)果,就可以利用節(jié)點中的雙向指針一次IO取出多條數(shù)據(jù)(圖片為mariadb的,所以名稱稍有不同)
十三、1.3.14請說明索引應(yīng)用有哪些規(guī)范？
建索引原則
(1) 必須要有主鍵,如果沒有可以做為主鍵條件的列,創(chuàng)建無關(guān)列
(2) 經(jīng)常做為where條件列 order by group by join on, distinct 的條件(業(yè)務(wù):產(chǎn)品功能+用戶行為)
(3) 最好使用唯一值多的列作為索引,如果索引列重復(fù)值較多,可以考慮使用聯(lián)合索引
(4) 列值長度較長的索引列,我們建議使用前綴索引.
(5) 降低索引條目,一方面不要創(chuàng)建沒用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引維護要避開業(yè)務(wù)繁忙期
不走索引的情況
(1)沒有查詢條件，或者查詢條件沒有建立索引
(2)查詢結(jié)果集是原表中的大部分數(shù)據(jù)，應(yīng)該是15-25%以上
(3)索引本身失效，統(tǒng)計信息不真實
(4)查詢條件使用函數(shù)在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等)
(5)隱式轉(zhuǎn)換導致索引失效.這一點應(yīng)當引起重視.也是開發(fā)中經(jīng)常會犯的錯誤.
(6)<> ，not in 不走索引（輔助索引）
(7)like "%_" 百分號在最前面不走
SQL篇
1.請簡述select語句的各個子句的執(zhí)行順序？
select from where group by having order by limit
2.請列舉SQL語句的種類和代表命令？
1.DDL數(shù)據(jù)定義語言
create 庫/表 drop 庫/表 alter 庫/表
2.DCL數(shù)據(jù)控制語言
GRANT為授權(quán)語句，對應(yīng)的REVOKE是撤銷授權(quán)語句。
3.DML數(shù)據(jù)操作語言
INSERT、UPDATE、DELETE三種指令為核心，分別代表插入、更新與刪除。
4.DQL數(shù)據(jù)查詢語言
其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。這些DQL保留字常與其他類型的SQL語句一起使用。
3.請簡述SQL_MODE的作用？ONLY_FULL_GROUP_BY是干什么用的？
1.sql_mode 用來規(guī)范sql語句
2.ONLY_FULL_GROUP_BY用來控制group + 聚合函數(shù)。select list中的列，要么在group by ,要么聚合函數(shù)操作。
4.請簡述MySQL utf8 和utf8mb4區(qū)別？
utf8 : 最大支持3個字節(jié)的字符
uft8mb4: 最大支持4字節(jié)長度字符。比如，emoji表情字符。
5.請簡述 tinyint int bigint 如何計算的存儲位數(shù)？
inyint : 1字節(jié)長度。2^8 =256 ， 0-255
int : 4字節(jié)
bingint ：8字節(jié)
6.請簡述 char(10)和varchar(10)的區(qū)別，生產(chǎn)如何選擇？并闡述為什么？
char(11) ：
定長 的字符串類型,在存儲字符串時，最大字符長度11個，立即分配11個字符長度的存儲空間，如果存不滿，空格填充。
varchar(11):
變長的字符串類型看，最大字符長度11個。在存儲字符串時，自動判斷字符長度，按需分配存儲空間。
7.請簡述datetime和timestamp區(qū)別？
DATETIME 8字節(jié)
范圍為從 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP 4字節(jié)
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
8.請簡述你們數(shù)據(jù)庫開發(fā)過程，選擇數(shù)據(jù)類型的規(guī)范是什么？
a. 表名：不要出現(xiàn)大寫字母，不要有數(shù)字開頭，要和業(yè)務(wù)有關(guān)，不能使用系統(tǒng)自帶關(guān)鍵字，盡量不要超過16字符。**
b. 表基本屬性：顯示設(shè)置engine,charset,對每個表設(shè)置comment**
c.列的數(shù)據(jù)類型：合適的，簡短的，足夠的、**
-d.約束：每個表要有主鍵，而且最好是自增數(shù)字列。每個表盡量設(shè)置NN,并設(shè)置默認值、**
e.其他：每個列要有注釋**
9.請列舉你了解的數(shù)據(jù)庫的約束有哪些？他們的特點是什么？
pk
not null
unique
unsigned
10.請簡述你們公司在Schema設(shè)計過程中有哪些開發(fā)規(guī)范？
名字： 小寫、16字符以內(nèi)、業(yè)務(wù)相關(guān)、不要用數(shù)字開頭、不要是預(yù)留字符串
設(shè)置字符集、存儲引擎顯式設(shè)置
數(shù)據(jù)類型：合適的、簡短的、足夠的
必須要有主鍵
每個列盡量非空，可以設(shè)置默認值
列名要有意義，不要預(yù)留字符，不要超過16字符
每個列、每個表都有注釋
11.數(shù)據(jù)庫范式有哪些？
第一范式（1NF）：要求數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項。
第二范式（2NF）：在1NF的基礎(chǔ)上，非碼屬性必須完全依賴于候選碼（在1NF基礎(chǔ)上消除非主屬性對主碼的部分函數(shù)依賴）
第二范式需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關(guān)，而不能只與主鍵的某一部分相關(guān)（主要針對聯(lián)合主鍵而言）。
第三范式（3NF）：在2NF基礎(chǔ)上，任何非主屬性不依賴于其它非主屬性（在2NF基礎(chǔ)上消除傳遞依賴）
第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。
12.你們公司Online DDL如何處理的？
一般DDL操作最好都采用pt-osc或gh-ost這樣的工具來實施，并且實施之前務(wù)必要先檢查當前目標表上是否有事務(wù)或大查詢未結(jié)束，避免嚴重的MDL鎖等待
除了8.0以上版本，除了追加式新增列（追加式）、表改名、新增虛擬列這三種支持INSTANT的操作可以直接跑DDL，其余的都統(tǒng)統(tǒng)
采用pt-osc/gh-ost工具，相對更不容易出狀。
執(zhí)行ALTER TABLE DDL時，不要節(jié)外生枝指定ALGORITHM=?, LOCK=?選項，因為MySQL會自行判斷該采用哪種方式。本來可以INPLACE的，可能不小心給指定成COPY就悲劇了
13. 5.6、5.7、8.0在Online DDL的改變
ALTER TABLE時ALGORITHM可以指定的幾種方式：
COPY ，是指DDL時，會生成（臨時）新表，將原表數(shù)據(jù)逐行拷貝到新表中，在此期間會阻塞DML
INPLACE，無需拷貝全表數(shù)據(jù)到新表，但可能還是需要IN-PLACE方式（原地，無需生成新的臨時表）重建整表。這種情況下，在DDL的初始準備和最后結(jié)束兩個階段時通常需要加排他MDL鎖（metadata lock，元數(shù)據(jù)鎖），除此外，DDL期間不會阻塞DML
INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無需拷貝數(shù)據(jù)也無需重建整表，同樣，也無需加排他MDL鎖，原表數(shù)據(jù)也不受影響。整個DDL過程幾乎是瞬間完成的，也不會阻塞DML。這個新特性是8.0.12引入的，再次感謝騰訊互娛DBA團隊的貢獻
14.簡述pt-osc或者gh-ost 第三方工具在處理DDL時的原理？
1.查看是否有從節(jié)點
2.查看是否有外鍵
3.創(chuàng)建新表
4.修改新表結(jié)構(gòu)
5.創(chuàng)建觸發(fā)器，保證拷貝過程中的數(shù)據(jù)同步
6.拷貝表數(shù)據(jù)
7.rename表
8.刪除舊表
9.刪除觸發(fā)器
15.請簡述drop table 、truncate table、delete from table的區(qū)別？
都能刪除表數(shù)據(jù)。
drop : 表結(jié)構(gòu)+表空間。
truncate ： 表數(shù)據(jù)，物理刪除表段，清空所有數(shù)據(jù)頁。立即釋放磁盤空間。
delete ： 表數(shù)據(jù)，邏輯刪除，打上刪除標記。不立即釋放磁盤空間。
16.請簡述如何利用update替換delete語句實現(xiàn)偽刪除
1．添加狀態(tài)列
2．delete命令替換為update
3．select 語句添加狀態(tài)條件
17.如果要你規(guī)劃一個10億的大表，你有什么好的方案？
索引應(yīng)用、存儲問題。
1、分區(qū)表
2、分布式架構(gòu)
3、歸檔表
18.如果這張10億單表已經(jīng)存在了，想要刪除1000W數(shù)據(jù)如何處理？
使用pt-archiver歸檔表
1.歸檔到數(shù)據(jù)庫
2.只清理數(shù)據(jù)
3.只把數(shù)據(jù)導出到外部文件，不刪除原表里的數(shù)據(jù)
19.生產(chǎn)中使用過分區(qū)表嗎？你們使用的是什么分表策略？分區(qū)表有什么優(yōu)勢和劣勢
range: 范圍分區(qū) 針對記錄字段的值在某個范圍
我們一般按時間 范圍分區(qū)。
或者list： 列表分區(qū) 該分區(qū)的特點是某列的值只有有限個值，基于這樣的特點我們可以采用列表分區(qū)。
20.請簡述 group by 語句的執(zhí)行原理
分組列值進行排序、去重復(fù)、聚合函數(shù)其他列聚合。
21.Where和having語句的區(qū)別
having 是在where group by只夠做篩選的
22.生產(chǎn)中進行數(shù)據(jù)庫資產(chǎn)統(tǒng)計，都統(tǒng)計什么？如何統(tǒng)計？
information_schema.tables 系統(tǒng)表
columns 根據(jù)列統(tǒng)計
23.請介紹你常用的聚合函數(shù)及其作用
avg 平均值
sum 求和
count 個數(shù)
min 最小值
max 最大值
group_count 將多行整合為一行
24.簡述多表連接的方式
笛卡爾
內(nèi)連接
外連接
