MyiSAM和innodb
MyiSAM：非聚集索引、B+樹、葉子結點保存data地址；
innodb：聚集索引、B+樹、聚集索引中葉子結點保存完整data，innodb非聚集索引需要兩遍索引，innoDB要求表必須有主鍵；
innodb為什么要用自增id作為主鍵：
自增主鍵：順序添加，頁寫滿開辟新的頁；
非自增主鍵（學號等）：主鍵值隨機，有碎片、不夠緊湊的索引結構；
分庫與分表設計、分片：
水平分表；
垂直分表：不常用的加入另一張表、大文本欄位單獨拆分到另一張表、不經常修改的欄位放入另一張表；
聚集索引與非聚集索引：
聚集索引：聚集索引查找完整資料；
非聚集索引：查找對應的主鍵值，然后根據主鍵值查找聚集索引，查找到完整資料
事務四大特性（ACID）：
原子性：一個事務中的所有操作，要么全部完成，要么全部不完成，在執行程序中發生錯誤，會發生回滾（rollback）；
一致性：事務開始前和結束后，資料庫的完整性沒有被破壞；
隔離性：多個事務執行，防止多個事務之間由于交叉執行而導致資料不一致，讀未提交、讀提交、可重復度、串行化，
持久性：事務提交后，對資料庫的修改是永久的，
事務的并發？事務隔離級別，每個級別會引發什么問題，MySQL默認是哪個級別？
臟讀：一個事務處理程序中讀到了另一個事務未提交的資料；
不可重復讀：一個事務多次讀取一個資料，獲得不同的資料結果；
幻讀：一個事務讀的程序中，另一個事務洗掉或者增加一條資料，影響這條事務的讀的結果，
Mysql級別：可重復讀，
事務隔離級別：
讀未提交：讀取未提交的資料，臟讀；
不可重復讀：事務A多次讀取同一資料，事務B在該程序中對資料進行修改并提交，導致A多次讀取資料不一致；
可重復讀：同一事務里，多次讀操作結果一致，但是存在幻讀；
串行化：事務并發，一個個按順序執行，
MySQL常見的存盤引擎InnoDB、MyISAM的區別？
MyISAM：事務×，鎖級別：表級鎖，存盤表總行數，非聚集索引；
（適用于插入不頻繁，查詢頻繁）
InnoDB：事務?，鎖級別：行級鎖和外鍵約束，不存盤表總行數，聚集索引；
（可靠性要求比較高，或要求事務，表更新和查詢都頻繁）
資料庫三范式，根據某個場景設計資料表？優缺點
- 所有欄位值都是不可分解的原子值，
- 在一個資料庫表中，一個表中只能保存一種資料，不可以把多種資料保存在同一張資料庫表中，
- 資料表中的每一列資料都和主鍵直接相關，而不能間接相關，
第一范式(確保每列保持原子性)：
表中欄位值不可再分，提高資料庫性能；
第二范式(確保表中的每列都和主鍵相關)：
第二范式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言），比如要設計一個訂單資訊表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為資料庫表的聯合主鍵，
第三范式(確保每列都和主鍵列直接相關,而不是間接相關)：
比如在設計一個訂單資料表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應的關系，而不可以在訂單表中添加關于客戶其它資訊（比如姓名、所屬公司等）的欄位，
優點：可以盡量得減少資料冗余 缺點:對于查詢需要多個表進行關聯，更難進行索引優化 反范式化: 優點:可以減少表得關聯 缺點:資料冗余以及資料例外
Explain關鍵字：
table：當前陳述句訪問的表；
id：id相同，順序執行，id越大，執行優先度越高；
select_type：小查詢陳述句扮演的角色，比如SIMPLE、PRIMARY、UNION等，
☆type：表示MySQL在表中找到所需行的方式，又稱”訪問型別“，
常見型別：NULL，system，const，eq_ref，ref，range，index，ALL，
（性能由好到差）
ALL：遍歷全表；
EXPLAIN SELECT * FROM s1;
index：只遍歷索引樹；
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
range：檢索給定范圍的行；
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值；
eq_ref：類似ref，區別：唯一索引；
const、system：查詢優化時，轉換為常量；system：只查詢一行；
# const
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
# system
INSERT INTO t VALUES(1);
NULL：不需要訪問表，
☆rows:預估的需要讀取的記錄條數，值越小越好，
☆key_len :
實際使用到的索引長度 (即：位元組數)
幫你檢查是否充分的利用了索引，值越大越好，主要針對于聯合索引，有一定的參考意義，
MVCC多版本并發控制(Multiversion Concurrency Control)：
InnoDB中實作MVCC機制；
快照讀與當前讀：
快照讀：不加鎖的簡單的 SELECT 都屬于快照讀；
當前讀：讀取的記錄進行加鎖；
MVCC：包括：隱藏欄位、Undo Log和Read View
隱藏欄位：當前事務id，undo指標（指向歷史版本的當前記錄）；
Undo Log：undo日志，通過undo指標串聯歷史版本；
ReadView：在查詢時創建，
包含：
創建這個Read View的事務ID；
當前活躍的事務id串列；
活躍事務最小事務ID；
系統事務最大ID（并不一定是活躍的）；
Read View規則：判斷當前查詢事務與Read View中最小事務的關系，
如果小于最小活躍ID，那么說明當前讀取的行已提交；
如果大于最大ID，說明當前的行是由一個活躍的事務還未提交，正在處理，需要按照Undo Log往下找，找到不在活躍事務id串列中的最新的提交的資料，
簡而言之：每一行存盤歷史資訊，查找時獲取當前活躍id，找到歷史資訊中最新的，并且不在活躍id的事務，
處理讀已提交：
每次select時創建一個新的Read View；
處理可重復讀：
每次同樣的select只在最初的select創建一個Read View；
索引優化：
優化的角度：
- 索引失效、沒有充分利用到索引——
建立索引 - 關聯查詢太多JOIN（設計缺陷或不得已的需求）——
SQL優化 - 服務器調優及各個引數設定（緩沖、執行緒數等）——
調整my.cnf - 資料過多——
分庫分表
索引失效情況：
最左前綴：MySQL可以為多個欄位創建索引，一個索引可以包含16個欄位，對于多列索引，過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個欄位，索引后面的欄位都無法被使用，如果查詢條件中沒有用這些欄位中第一個欄位時，多列（或聯合）索引不會被使用，
遞增主鍵：減少頁分裂，不用隨機欄位作為主鍵，
計算、函式、型別轉換(自動或手動)導致索引失效：
函式失效：
# 使用like
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 使用函式
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
# 創建索引之后
CREATE INDEX idx_name ON student(NAME);
函式將導致索引失效；
計算失效：
# 創建索引
CREATE INDEX idx_sno ON student(stuno);
# 查詢時使用計算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
導致索引失效；
型別轉換失效：
# 未使用到索引
# name的型別為字串，下面發生了型別轉換，導致失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
范圍條件右邊的列索引失效：
SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
idx_age_classId_name索引將會失效，因為ID>20作為范圍條件，導致右側的name失效，
除非如下修改，將ID>20放到最后：
create index idx_age_name_classId on student(age,name,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;
不等于(!= 或者<>)索引失效;
is null可以使用索引，is not null無法使用索引;
like以通配符%開頭索引失效；
OR 前后存在非索引的列，索引失效：
OR前后的兩個條件中的列都是索引時，查詢中才使用索引，
查詢優化：
① 盡可能的使用聯合索引而不是索引的組合；
②創建索引盡量讓輔助索引進行索引覆寫 而不是回表；
③在可以使用主鍵id的表中，盡量使用自增主鍵id，這樣可以避免頁分裂；
④查詢的時候盡量不要使用select * ，這樣可以避免大量的回表；
⑤盡量少使用子查詢，能使用外連接就使用外連接，這樣可以避免產生笛卡爾集；
⑥能使用短索引就使用短索引，這樣可以在非葉子節點存盤更多的索引列降低樹的層高，并且減少空間的開銷；
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/549086.html
標籤：MySQL
上一篇：讀SQL進階教程筆記06_外連接