一、MySQL架構介紹

1. MySQL簡介

一個關系型資料庫管理系統，由瑞典MySQL AB公司開發，目前屬于Oracle公司，
一種關聯資料庫管理系統，將資料保存在不同的表中，而不是將所有資料放在一個大倉庫里，這樣可以增加速度并提升靈活性，
開源的，無需支付額外費用，
支持大型的資料庫，可以擁有長千萬條記錄的大型資料庫，32位作業系統最大可支持4GB，64位作業系統最大的表檔案為8TB，
使用標準的SQL資料語言形式，
可以允許于多個系統上，支持多種語言，例如：C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等，
對PHP有很好的支持，
可定制，采用GPL協議，可以通過修改原始碼來開發自己的MySQL系統，

2. Linux安裝MySQL

略

3. MySQL組態檔

1) 二進制日志 log-bin

主要應用于主從復制，記錄資料庫增量日志，用于主從復制，

2）錯誤日志 log-error

默認是關閉的，記錄嚴重的警告和錯誤資訊，每次啟動和關閉的詳細資訊等，

3）查詢日志 log

默認是關閉的，記錄查詢的sql陳述句，如果開啟會降低mysql整體性能，因為記錄日志是需要消耗系統資源的，可以通過設定閾值，例如記錄所有超過五秒以上的查詢sql陳述句，

4）資料檔案

5) 如何配置

4.MySQL的邏輯架構介紹

1）總體概覽

和其他資料庫相比，MySQL有點與眾不同，它得架構可以在多種不同場景中應用并發揮良好作用，主要體現在存盤引擎得架構上，插件式得存盤引擎架構將查詢處理和其他得系統任務以及資料的存盤提取相分離，這種架構可以根據業務的需求和實際需要選擇合適的存盤引擎，

連接層 ? 服務層 ? 引擎層 ? 存盤層

5. MySQL存盤引擎

1) 查看命令

mysql>show variables like '%storage_engine%';

2) MyISAM和InnoDB區別

二、索引優化分析

1.SQL性能下降的原因

1) 查詢陳述句寫的沒眼看

連接，子查詢，沒有用到索引，或沒有建立索引，

2) 索引失效

建立了索引，但是沒有用到，

單值索引： 只給某一個欄位建立索引，應用于頻繁進行單條件的查詢陳述句，例如：

select * from user where name = "?";

create index inx_user_name on user(name); //建立單值索引

在建立索引后，索引欄位會進行排序，大幅提升查詢速度，

復合索引： 給某些欄位建立復合索引，應用于頻繁進行多條件查詢陳述句，例如：

select * from user where name = "?" and email = "?";

create index idx_user_nameEmail on user(name,email); //建立復合索引

3) 關聯查詢太多join (設計缺陷或不得已的需求)

join越少越好，

4）服務器調優以及各個引數設定

例如緩沖、執行緒數等，

2.常見通用的join查詢

1) SQL執行順序

2）七種join圖

先上兩張測驗表

1. 第一種
   

SELECT * FROM emp a INNER JOIN dept b on a.dept_id = b.id

執行結果：

2. 第二種
   

SELECT * from emp a left join dept b on a.dept_id = b.id

執行結果：

3. 第三種
   

SELECT * from emp a RIGHT join dept b on a.dept_id = b.id

執行結果：

4. 第四種
   

SELECT * from emp a left join dept b on a.dept_id = b.id where b.id is null

執行結果：

5. 第五種
   
   sql同第四種，修改left ==> reight

6. 第六種
   

select * from emp a left join dept b on a.dept_id = b.id union select * from emp a right join dept b on a.dept_id = b.id

執行結果：

7. 第七種
   

select * from emp a left join dept b on a.dept_id = b.id where b.id is null union select * from emp a right join dept b on a.dept_id = b.id where a.dept_id is null

執行結果：

3. 索引介紹

1）索引是什么

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構，
在資料本身之外，資料庫系統還維護著滿足特定查找演算法的資料結構，這些資料結構以某種方式指向資料，這樣就可以在這些資料結構上實作高級查找演算法，下圖是一種可能的索引方式示例（二叉查找樹）：

為了加快Col2的查找，可以維護一個類似于右邊的二叉查找樹，每個節點分別包含索引鍵值和一個只想對應資料記錄物理地址的指標，這樣就可以運用二叉查找在一定的復雜度內獲取到相應的資料，從而快速的檢索出符合條件的記錄，

適合建立索引的欄位是不常進行刪改操作且經常查詢的欄位，

一般來說索引本身也很大，不可能全部存盤在記憶體中，因此索引往往以索引檔案的形式存盤在磁盤上，

我們平常所說的索引，如果沒有特別指明，都是指B樹（多路搜索樹，并不一定是二叉樹）結構組織的索引，其中聚集索引，次要索引，復合索引，前綴索引，唯一索引默認都是B+樹索引，統稱索引，當然，除了B+樹這種型別的索引 之外，還有哈希索引 ? hash index等，

2) 索引優勢和劣勢

優勢：

• 類似于大學圖書館建立書目索引，提高資料檢索效率，降低資料庫的IO成本，
• 通過索引對資料進行排序，降低資料排序的成本，降低了CPU的消耗，

劣勢：

• 索引也是一張表，該表保存了主鍵與索引欄位，并指向物體表的記錄，所以索引也是要占用空間的，
• 雖然索引大大提高了查詢速度，同時也會降低更新表的速度，如INSERT,UPDATE和DELETE，因為更新表時，MySQL不僅要保存資料，還要保存一下索引檔案每次更新添加了索引列的欄位，都會調整因為更新所帶來的鍵值變化后的索引資訊，
• 索引只是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間研究建立最優秀的索引，

3）索引分類

4）索引基本語法

• 添加索引

//添加主鍵索引，唯一且不能為NULL
alter table table_name add primary key (column_list);
//添加唯一索引，唯一可為NULL
alter table table_name add unique index_name (column_list);
//添加普通索引，索引值可出現多次
alter table table_name add index index_name (column_list);
//添加全文索引
alter table table_name add fulltext index_name (column_list);

• 洗掉索引

drop index index_name on table_name

• 查看索引

show index from table_name

5) 創建索引條件

1. 主鍵自動建立唯一索引，
2. 查詢中與其他表關聯的欄位，外鍵關系應該建立索引，
3. 頻繁作為查詢條件的欄位應該建立索引，
4. 單值/復合索引的選擇問題，高并發下傾向于創建復合索引，
5. 查詢中排序欄位，排序欄位若通過索引去訪問將大大提高排序速度，
6. 查詢中同級或者分組的欄位要建立索引，
7. 記錄太少不需要建立索引，
8. 頻繁更新的欄位不適合建立索引，
9. 資料重復且分布平均的表欄位不適合建立索引，
10. where條件里用不到的欄位不適合創建索引，

4.sql性能分析

1)MySQL常見瓶頸

1. CPU：CPU飽和時一般發生在資料裝入記憶體或從磁盤上讀取資料，
2. IO：磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量的時候
3. 服務器硬體的性能瓶頸：top，free，iostat和vmstat來查看系統性能狀態，

2)Explain關鍵字

使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢陳述句，查看MySQL是如何處理你的SQL陳述句的，從而得知你的查詢陳述句或是表結構的性能瓶頸，

• 作用

1. 表的讀取順序
2. 資料讀取操作的操作型別
3. 哪些所以可以使用
4. 哪些索引被實際使用
5. 表之間的參考
6. 每張表有多少行被優化器查詢

• 怎么玩

explain + sql

執行結果：

表頭解釋：

1. id

select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序，id相同，執行順序由上至下；如果是子查詢，id序號會遞增，id值越大表明優先級越高，越早被執行；若有相同有不同，先執行優先級高的，在按照順序執行優先級相同的，

2. select_type

3. table

表明當前行是哪張表的查詢，

4. type

顯示的是訪問型別，是較為重要的一個性能直表，

結果值從最好到最壞依次是：

system ? const ? eq_ref ? ref ? range ? index ? ALL

一般來說，得保證查詢至少達到range級別，最好能達到ref，

5. possible_keys和key

用于判斷是否使用了索引（即索引是否失效）且在多個索引競爭的情況下MySQL到底使用了哪個索引，

6. key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用索引的長度，在不損失精度的情況下，長度越少越好，key_len顯示的值為索引欄位的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出來的，

7. ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數，即哪些列或常量被用于查找索引列上的值 ，

8. rows

根據表統計資訊以及索引選用情況，大致估算出找到所需記錄多需要讀取的行數，

9. Extra

包含不適合在其他列中顯示但十分重要的額外資訊，

5.索引優化 （重要）

1）索引N表優化案例

• 單表優化案例

CREATE TABLE `article`  (
    `id` int(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`author_id` int(11) UNSIGNED NOT NULL,
	`category_id` int(11) UNSIGNED NOT NULL,
	`views` int(11) UNSIGNED NOT NULL,
	`comments` int(11) UNSIGNED NOT NULL,
    `title` VARBINARY(255) NOT NULL,
	`content` TEXT NOT NULL );

INSERT INTO `article` ( `author_id`, `category_id`, `views`, `comments`, `title`, `content` )
VALUES
	( 1, 1, 1, 1, '1', '1' ),
	( 2, 2, 2, 2, '2', '2' ),
	( 3, 3, 3, 3, '3', '3' );

執行sql：

結論：type是ALL即全表無索引掃描（最壞情況），Extra還出現了Using filesort，也是最壞的情況，

嘗試優化：

//建立索引
mysql> alter table article add index idx_article_ccv (category_id,comments,views);

嘗試執行sql：

結論：此處type已經變為了range即表明查詢陳述句進行了范圍性的索引掃描，這優于剛才的全表非索引掃描（ALL），但Extra依舊存在檔案內排序（Using filesort），

分析：使用范圍會導致索引失效，

改進思路：既然comments欄位是按照范圍搜索，會導致索引失效，那么我們能不能越過comments欄位，只建立category_id和views欄位的復合索引呢?

索引的建立不是一朝一夕就玩的溜的，需要反復的測驗和練習，

再次優化：

//洗掉之前創建的索引
mysql> drop index idx_article_ccv on article;
mysql> alter table article add index idx_article_cv (category_id,views);

執行sql：

結論：我們看到type已變為ref，即非唯一性全表掃描，利用到了索引，并且解決了檔案內排序（Using filesort），

• 雙表優化案例

CREATE table  if not exists `class`(
`id` int(10) UNSIGNED not null auto_increment,
`card` int(10) UNSIGNED not null,
PRIMARY KEY(`id`)
);

CREATE table if not exists `book`(
`book_id` int(10) UNSIGNED not null auto_increment,
`card` int(10) UNSIGNED not null,
PRIMARY KEY (`book_id`)
);

INSERT into class(card) VALUES(floor(1+(rand()*20))); //執行20次插入20條資料

INSERT into book(card) VALUES(floor(1+(rand()*20))); //執行20次插入20條資料

執行sql：

結論：有ALL，且出現Using join buffer，查看rows欄位，得知兩個表都進行全表掃描了全部欄位（20個），

優化思路：嘗試增加索引，但索引到底是加在哪一張表呢？不妨都試試，

嘗試優化（方案一）：

//給class表的card欄位增加索引
alter table class add index idx_class_card (card);

執行結果：

可知在class表的card欄位增加索引后，雖然其class表使用到了索引，并且也成為了非唯一性的索引掃描，但book表中依舊是非索引性全表掃描，使用到的索引也為NULL，

嘗試優化（方案二）：

drop index idx_class_card on class
alter table book add index idx_book_card (card);

執行sql：

結論：運行結果我們看到，未增加索引的class表變成了非索引性全表掃描，用到的索引為NULL，然而book表雖然增加了索引，并且也使用到了其索引，但查詢型別卻是index即索引性全表掃描，這與方案一的ref非唯一性索引掃描相比效率要差得多，

三、查詢截取分析

四、MySQL鎖機制

五、主從復制

六、常用SQL陳述句（補充）

select * from dept a,emp b where a.id = b.id;
select * from dept where id between 30 and 60;
select * from dept where id in (1,2,6);
desc table_name; //查詢表結構

七、配套視頻資料

尚硅谷【周陽/陽哥】MySQL資料庫高級