第1章 Mysql 簡介1.1 概述1.2 進階 MySQL第2章 Mysql Linux 版的安裝2.1 下載下傳位址2.2 檢查目前系統是否安裝過 mysql2.3 修改 Mysql 配置檔案位置2.4 修改字元集和資料存儲路徑2.5 MySQL 的安裝位置說明2.6 Mysql 配置檔案說明2.7 Mysql 的資料存放目錄第3章 Mysql 邏輯架構介紹3.1 總體概覽3.2 查詢說明第4章 Mysql 性能優化4.1 影響 mysql 的性能因素4.2 查詢與索引優化分析4.2.1 性能下降SQL慢的原因4.2.2 常見通用的 Join 查詢4.3 mysql 索引結構與檢索原理4.4 哪些情況需要建立索引4.5 哪些情況不要建立索引第5章 性能分析5.1 索引優化5.1.1 索引分析5.1.2 索引失效(應該避免)5.1.3 一般性建議第6章 查詢截取分析6.1 查詢優化6.1.1 永遠小表驅動大表6.1.2 order by 關鍵字優化6.1.3 gruop by 關鍵字優化6.2 慢查詢日志6.3 批量資料腳本（資料庫程式設計）6.4 show profile6.5 全局查詢日志第7章 大資料量處理理論第8章 Mysql 分區分庫分表簡介8.1 Mysql 分區8.2 List 分區8.3 對 NULL 值的處理第9章 MySql 鎖機制9.1 概述9.2 三鎖9.2.1 表鎖(偏讀)案例示範9.2.2 行鎖(偏寫)案例示範第10章 MySql 主從複制10.1 複制的基本原理10.2 複制的基本原則 和 複制的最大問題10.3 一主一從常見配置
先完成再完美，先功能再性能。
大膽假設，小心求證。
第1章 Mysql 簡介
1.1 概述
MySQL 是一個關系型資料庫管理系統，由瑞典 MySQL AB 公司開發，目前屬于 Oracle 公司。
MySQL 是一種關聯資料庫管理系統，将資料儲存在不同的表中，而不是将所有資料放在一個大倉庫内，這樣就增加了速度并提高了靈活性。
MySQL 是開源的，是以你不需要支付額外的費用。
MySQL 支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
MySQL 使用标準的 SQL 資料語言形式。
MySQL 可以允許于多個系統上，并且支援多種語言。這些程式設計語言包括 C、C++、Python、 、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 對 PHP 有很好的支援，PHP 是目前最流行的 Web 開發語言。
MySQL 支援大型資料庫，支援 5000 萬條記錄的資料倉庫，32 位系統表檔案最大可支援 4GB，64 位系統支援最大的表檔案為 8TB。
MySQL 是可以定制的，采用了 GPL 協定，你可以修改源碼來開發自己的 Mysql 系統。
1.2 進階 MySQL
完整的 mysql 優化需要很深的功底，大公司甚至有專門的 DBA 寫下述内容：
第2章 Mysql Linux 版的安裝
2.1 下載下傳位址
官網下載下傳位址：http://dev.mysql.com/downloads/mysql/
選擇對應的 MySQL 版本、作業系統 和 位數，點選 Download（本博文中以版本 5.5.48 為例）
下載下傳位址：
https://downloads.mysql.com/archives/get/file/MySQL-server-5.5.48-1.linux2.6.x86_64.rpm
https://downloads.mysql.com/archives/get/file/MySQL-client-5.5.48-1.linux2.6.x86_64.rpm
2.2 檢查目前系統是否安裝過 mysql
第一步：以 root 身份登入，檢視 mysql 是否安裝。
rpm -qa | grep mysql
第二步：以 root 身份登入，如果 mysql 的版本不是想要的版本。需要把原來的 mysql 解除安裝。
使用 yum 指令解除安裝
yum remove mysql mysql-server mysql-libs mysql-common
使用 rpm 指令解除安裝
rpm -e RPM軟體包名（該名字是第一步指令查出來的名字）
rm -rf /var/lib/mysql
rm -f /etc/my.cnf
注意
：使用 yum 指令解除安裝，因為 yum 指令可以自動删除與 mysql 相關的依賴；如果使用 rpm 指令解除安裝，則還需要手動去删除和mysql相關的檔案。
第三步1：安裝 mysql。需要使用 yum 指令安裝。在安裝 mysql 之前需要安裝 mysql 的下載下傳源。需要從 oracle 的官方網站下載下傳。
1）下載下傳 mysql 的源包：
我們是 centos6.8 對應的 rpm 包為：mysql-community-release-el6-5.noarch.rpm
指令：wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
2）安裝 mysql 下載下傳源：
yum localinstall mysql-community-release-el6-5.noarch.rpm
3）線上安裝社群版的 mysql：
yum install mysql-community-server
或者線上安裝收費版本的 mysql：
yum install mysql-server
兩種方式均可，建議安裝社群版。
第三步2：我們也可以使用 rpm 指令進行安裝。
安裝 mysql 服務端(注意提示)
安裝 mysql 用戶端
檢視 MySQL 安裝時建立的 mysql 使用者和 mysql 組
或者可以執行
mysqladmin --version
指令。類似
java -version
，如果打出消息，即為安裝成功。
mysql 服務的啟+停
設定開機自啟動 mysql 服務
檢視 設定開機自啟動 mysql 服務
第四步：啟動 mysql。
service mysql start
注意：
第五步：需要給 root 使用者設定密碼。
/usr/bin/mysqladmin -u root password 'new-password' #為 root 賬号設定密碼
第六步：登入 mysql。
[root@localhost ~]# mysql -uroot -p
第七步：需要先登入到 mysql，進行遠端連接配接授權。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
注意
：'myuser'、'mypassword' 需要替換成實際的使用者名和密碼。
2.3 修改 Mysql 配置檔案位置
Mysql 5.5 版本：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
Mysql 5.6 版本：cp /usr/share/mysql/my-default.cnf /etc/my.cnf
2.4 修改字元集和資料存儲路徑
1、檢視字元集
mysql> show variables like 'character%'; 或者
mysql> show variables like '%char%';
預設的 資料庫 和 伺服器都用了 latin1，是以會亂碼。
2、修改
下圖中的紅色字型部分表示要修改的地方
my.cnf 中要修改的代碼如下：
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
# The MySQL server
[mysqld]
port = 3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
lower_case_table_names=1
max_connections=1000
[mysql]
no-auto-rehash
default-character-set=utf8
3、重新開機 mysql
service mysql stop
service mysql start
4、重新連接配接後重新 create databse 并使用建立庫，然後再重建立表試試。
5、還是亂碼的話就設值
init_connect='SET NAMES utf8' ##設定連接配接 mysql 是 UTF8 編碼
。
2.5 MySQL 的安裝位置說明
在 linux 下檢視安裝目錄，指令：ps -ef | grep mysql
2.6 Mysql 配置檔案說明
2.7 Mysql 的資料存放目錄
/var/lib/mysql
第3章 Mysql 邏輯架構介紹
3.1 總體概覽
和其它資料庫相比，MySQL 有點與衆不同，它的架構可以在多種不同場景中應用并發揮良好作用。主要展現在
存儲引擎的架構
上，
插件式的存儲引擎架構
将查詢處理和其它的系統任務以及資料的存儲提取相分離。這種架構可以根據業務的需求和實際需要選擇合适的存儲引擎。
3.2 查詢說明
首先，mysql 的查詢流程大緻是：mysql 用戶端通過協定與 mysql 伺服器建連接配接，發送查詢語句，先檢查查詢緩存，如果命中，直接傳回結果，否則進行語句解析。
有一系列預處理，比如檢查語句是否寫正确了，然後是查詢優化（比如是否使用索引掃描，如果是一個不可能的條件，則提前終止），生成查詢計劃，然後查詢引擎啟動，開始執行查詢，從底層存儲引擎調用 API 擷取資料，最後傳回給用戶端。怎麼存資料、怎麼取資料，都與存儲引擎有關。
然後，mysql 預設使用的 BTREE 索引，并且一個大方向是，無論怎麼折騰 sql，至少在目前來說，mysql 最多隻用到表中的一個索引。
第4章 Mysql 性能優化
4.1 影響 mysql 的性能因素
4.2 查詢與索引優化分析
4.2.1 性能下降SQL慢的原因
4.2.2 常見通用的 Join 查詢
詳解見連結：
4.3 mysql 索引結構與檢索原理
mysql 索引結構
檢索原理
【初始化介紹】
一顆 b+ 樹，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個資料項（深藍色所示）和指針（黃色所示），如磁盤塊 1 包含資料項 17 和 35，包含指針 P1、P2、P3，P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。
真實的資料存在于葉子節點即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非葉子節點不存儲真實的資料，隻存儲指引搜尋方向的資料項，如 17、35 并不真實存在于資料表中。
【查找過程】
如果要查找資料項 29，那麼首先會把磁盤塊 1 由磁盤加載到記憶體，此時發生一次 IO，在記憶體中用二分查找确定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，記憶體時間因為非常短（相比磁盤的 IO）可以忽略不計，通過磁盤塊 1 的 P2 指針的磁盤位址把磁盤塊 3 由磁盤加載到記憶體，發生第二次 IO，29 在 26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過指針加載磁盤塊 8 到記憶體，發生第三次 IO，同時記憶體中做二分查找找到 29，結束查詢，總計三次 IO。
真實的情況是，3 層的 b+ 樹可以表示上百萬的資料，如果上百萬的資料查找隻需要三次 IO，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次 IO，那麼總共需要百萬次的 IO，顯然成本非常非常高。
4.4 哪些情況需要建立索引
4.5 哪些情況不要建立索引
MySql Query Optimizer
(MySQL 查詢優化器)
1、Mysql 中有專門負責優化 SELECT 語句的優化器子產品，主要功能：通過計算分析系統中收集到的統計資訊，為用戶端請求的 Query 提供他認為最優的執行計劃（MySQL 認為最優的資料檢索方式，但不見得是 DBA 認為是最優的，這部分最耗費時間）
2、當用戶端向 MySQL 請求一條 Query，指令解析器子產品完成請求分類，差別出是 SELECT 并轉發給 MySQL Query Optimizer 時，MySQL Query Optimizer 首先會對整條 Query 進行優化，處理掉一些常量表達式的預算，直接換算成常量值。并對 Query 中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然後分析 Query 中的 Hint 資訊（如果有），看顯示 Hint 資訊是否可以完全确定該 Query 的執行計劃。如果沒有 Hint 或 Hint 資訊還不足以完全确定執行計劃，則會讀取所涉及對象的統計資訊，根據 Query 進行寫相應的計算分析，然後再得出最後的執行計劃。
第5章 性能分析
5.1 索引優化
5.1.1 索引分析
1、單表
建表 SQL
CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) 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'),
(1, 1, 3, 3, '3', '3');
SELECT * FROM article;
案例
# 查詢 category_id 為 1 且 comments 大于 1 的情況下，views 最多的 article_id。
# 1.0 第 1 次EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
#結論：很顯然，type 是 ALL，即最壞的情況。Extra 裡還出現了 Using filesort，也是最壞的情況。優化是必須的。
# 開始優化：
# 1.1 建立索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv (`category_id`, `comments`, `views`);
CREATE INDEX idx_article_ccv ON article (category_id, comments, views);
DROP INDEX idx_article_ccv ON article;
# 1.2 第 2 次EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 3 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
# 結論：
# type 變成了 range，這是可以忍受的。但是 extra 裡使用 Using filesort 仍是無法接受的。
# 但是我們已經建立了索引，為啥沒用呢?
# 這是因為按照 BTree 索引的工作原理：（分組之前必排序）
# 先排序 category_id，
# 如果遇到相同的 category_id 則再排序 comments，如果遇到相同的 comments 則再排序 views。
# 當 comments 字段在聯合索引裡處于中間位置時，
# 因 comments > 1 條件是一個範圍值(所謂 range)，
# MySQL 無法利用索引再對後面的 views 部分進行檢索，即 range 類型查詢字段後面的索引無效。
# 1.3 删除第 1 次建立的索引
DROP INDEX idx_article_ccv ON article;
# 1.4 第 2 次建立索引
#ALTER TABLE `article` ADD INDEX idx_article_cv (`category_id`, `views`);
CREATE INDEX idx_article_cv ON article (category_id, views);
# 1.5 第 3 次 EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
# 結論：可以看到，type 變為了 ref，Extra 中的 Using filesort 也消失了，結果非常理想。
# 删除第 2 次建立的索引
DROP INDEX idx_article_cv ON article;
2、兩表
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` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
select * from class;
select * from book;
# 下面開始第 2 次 explain 分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
# 結論：type 有 All
# 添加索引優化
ALTER TABLE `book` ADD INDEX Y (`card`);
# 第 2 次 explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ref | Y | Y | 4 | db2019.class.card | 1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
# 可以看到第二行的 type 變為了 ref，rows 也變成了優化比較明顯。
# 這是由左連接配接特性決定的。LEFT JOIN 條件用于确定如何從右表搜尋行，左表一定都有，
# 是以右表是我們的關鍵點，一定需要建立索引。
# 删除舊索引 + 建立新索引 + 第 3 次 explain
DROP INDEX Y ON book;
ALTER TABLE `class` ADD INDEX X (`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | class | index | NULL | X | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
# 然後來看一個右連接配接查詢（同理）：
# 優化較明顯。這是因為 RIGHT JOIN 條件用于确定如何從左表搜尋行，右表一定都有，
# 是以左表是我們的關鍵點，一定需要建立索引。
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
DROP INDEX X ON class;
ALTER TABLE `book` ADD INDEX Y (`card`);
# 右連接配接，基本無變化
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
3、三表
在上面二表的基礎上再加一張表
CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`phoneid`)
) ENGINE = INNODB;
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
以左連接配接為例：（将右邊對應的字段建立索引）
ALTER TABLE `phone` ADD INDEX z (`card`);
ALTER TABLE `book` ADD INDEX Y (`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ref | Y | Y | 4 | db2019.class.card | 1 | Using index |
| 1 | SIMPLE | phone | ref | z | z | 4 | db2019.book.card | 1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
# 後 2 行的 type 都是 ref 且總 rows 優化很好，效果不錯。是以索引最好設定在需要經常查詢的字段中。
===============================================================================================
【結論】
join 語句的優化：
1、盡可能減少 join 語句中的 Nested Loop(嵌套循環) 的循環總次數；“永遠用小結果集驅動大的結果集”（小表驅動大表）；
2、優先優化 Nested Loop(嵌套循環) 的内層循環；
3、保證 join 語句中的被驅動表上 join 條件字段已經被索引；
4、當無法保證被驅動表的 join 條件字段被索引且記憶體資源充足的前提下，不要太吝惜 join buffer 的設定（即将該值調大些）；
5.1.2 索引失效(應該避免)
詳解見連結
面試題講解：
我們建立了複合索引 idx_test03_c1234，根據以下 SQL 分析下索引使用情況？
【建表語句】
create table test03 (
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03 (c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03 (c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03 (c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03 (c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03 (c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
select * from test03;
【建索引】
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;
問題：我們建立了複合索引 idx_test03_c1234，根據以下 SQL 分析下索引使用情況？
explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
===================================================================================================================================
1） explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; # 索引都使用到了，因為 MySQL 底層進行了優化
2） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; # 索引都使用到了，因為 MySQL 底層進行了優化
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
結論：索引的使用順序與索引的建立順序無關，但是最好按照建立索引的順序使用索引。這樣可以避免一次的 sql 自身的查詢優化。
3） explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'; # 使用到了 c1、c2、c3索引，c3 範圍之後全失效
4） explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'; # 索引都使用到了，因為 MySQL 底層進行了優化，c4 範圍之後全失效，但是 c4 之後沒有範圍了
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
5） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3; # 索引 c3 作用在排序而不是查找（隻是用到了一半，但是不算）
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
6） explain select * from test03 where c1='a1' and c2='a2' order by c3; # 使用到了 c1、c2 索引
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
7） explain select * from test03 where c1='a1' and c2='a2' order by c4; # 使用到了 c1、c2 索引，出現了 filesort，性能下降
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+
8）
8.1 explain select * from test03 where c1='a1' and c5='a5' order by c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
隻用到了 c1 一個字段索引，但是 c2、c3 用于排序，無 filesort。
8.2 explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
隻用到了 c1 一個字段索引，且出現了 filesort，我們建的索引是 1234，它沒有按照順序來，3 2 颠倒了。
9） explain select * from test03 where c1='a1' and c2='a2' order by c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
用到了 c1、c2 兩個字段索引，但是 c2、c3 用于排序，無 filesort。
10）
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3;
結果同 （9），用到了 c1、c2 兩個字段索引，但是 c2、c3 用于排序，無 filesort。
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
本例有常量 c2 的情況，和 8.2 進行對比，無 filesort。
小結：一般來說，order by 的字段順序沒有跟建立索引的字段順序一緻時，一般會出現 filesort，但是排序字段有常量的情況時，那麼 order by 的字段順序沒有跟建立索引的字段順序一緻時 則不會出現 filesort，案例如上。
explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
隻用到了 c1 一個字段索引，且出現了 filesort。
11）explain select * from test03 where c1='a1' and c4='a4' group by c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
隻用到了 c1 一個字段索引，但是 c2、c3 用于排序，無 filesort。
12）explain select * from test03 where c1='a1' and c4='a4' group by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
隻用到了 c1 一個字段索引，且出現了 temporary 和 filesort。因為分組之前必排序（内部排序）。
小結：
1、定值、範圍還是排序，一般 order by 是給個範圍。
2、group by 基本上都需要進行内部排序，如果 group by 字段順序錯了之後，内部會有臨時表産生。
5.1.3 一般性建議
第6章 查詢截取分析
面試問題：你對 Mysql 的優化有哪些？
答：
1、代碼至少跑一天，觀察慢 sql 的情況。
2、開啟慢查詢日志，設定門檻值，比如超過 5 秒鐘的慢 sql，并将它捕獲出來。
3、explain + 慢 sql 分析。
4、show profile，檢視 sql z在伺服器裡面的執行細節和生命周期情況。
5、運維經理或者 DBA 進行 sql 資料庫伺服器的參數調優。
6.1 查詢優化
6.1.1 永遠小表驅動大表
類似嵌套循環(Nested Loop)
6.1.2 order by 關鍵字優化
1、ORDER BY 子句，盡量使用 index 方式排序，避免使用 filesort 方式排序。
CREATE TABLE tblA (
#id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL
);
INSERT INTO tblA (age,birth) VALUES (22, NOW());
INSERT INTO tblA (age,birth) VALUES (23, NOW());
INSERT INTO tblA (age,birth) VALUES (24, NOW());
CREATE INDEX idx_A_ageBirth ON tblA(age, birth);
SELECT * FROM tblA;
案例1
mysql> explain select * from tblA where age > 20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA where age > 20 order by age, birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA where age > 20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA where age > 20 order by birth, age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
截圖如下：
案例2
mysql> explain select * from tblA order by birth;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA where birth > '2019-06-12 00:00:00' order by birth;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA where birth > '2019-06-12 00:00:00' order by age;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from tblA order by age asc, birth desc;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql>
結論：
1、MySQL 支援二種方式的排序：filesort 和 index，index 效率高。
2、它指 MySQL 掃描索引本身完成排序。filesort 方式效率較低。
ORDER BY 滿足兩種情況時，會使用 index方式排序：
1、ORDER BY 語句使用索引最左前列。
2、使用 where 子句與 order by 子句條件列組合滿足索引最左前列。
2、盡可能在索引列上完成排序操作，遵照索引建的最佳左字首原則。
3、如果不在索引列上，filesort 有兩種算法：mysql 就要啟動雙路排序和單路排序。
why
提高 order by 的速度：
1、order by 時 select * 是一個大忌隻 query 需要的字段，這點非常重要。在這裡的影響是：
1.1 當 query 的字段大小總和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 類型時，會用改進後的算法--單路排序，否則用老算法--多路排序。
1.2 兩種算法的資料都有可能超出 sort_buffer 的容量，超出之後，會建立 tmp 檔案進行合并排序，導緻多次 I/O，但是用單路排序算法的風險會更大一些，是以要提高 sort_buffer_size。
2、嘗試提高 sort_buffer_size
不管用哪種算法，提高這個參數都會提高效率，當然，要根據系統的能力去提高，因為這個參數是針對每個程序的。
3、嘗試提高 max_length_for_sort_data
提高這個參數，會增加用改進算法的機率。但是如果設的太高，資料總容量超出 sort_buffer_size 的機率就增大，明顯症狀是高的磁盤 I/O 活動和低的處理器使用率。
小結
6.1.3 gruop by 關鍵字優化
6.2 慢查詢日志
是什麼
怎麼玩
說明
檢視是否開啟和如何開啟
使用 set global slow_query_log=1; 開啟了慢查詢日志隻對目前資料生效，如果 MySQL 重新開機後則會失效。
如果要永久生效，就必須修改配置檔案 my.cnf（其他系統變量亦是如此）
那麼開啟了慢查詢日志後，什麼樣的 sql 才會記錄到慢查詢日志裡面呢？
日志分析工具 mysqldumpslow
在生産環境中，如果手工分析日志、查找、分析SQL，顯然是個體力活，MySQL 提供了日志分析工具 mysqldumpslow。
參數詳解及示例
6.3 批量資料腳本（資料庫程式設計）
6.4 show profile
6.5 全局查詢日志
這招隻允許在測試環境下使用，永遠不允許在生産環境下用。
配置啟動：
編碼啟用：
第7章 大資料量處理理論
第8章 Mysql 分區分庫分表簡介
8.1 Mysql 分區
Mysql 分區是什麼？(PARTITION)
如果一張表的資料量太大的話，那麼 myd,myi 就會變的很大，查找資料就會變的很慢，這個時候我們可以利用 mysql 的分區功能，在實體上将這一張表對應的三個檔案，分割成許多個小塊，這樣呢，我們查找一條資料時，就不用全部查找了，隻要知道這條資料在哪一塊，然後在那一塊找就行了。如果表的資料太大，可能一個磁盤放不下，這個時候，我們可以把資料配置設定到不同的磁盤裡面去。
Range 分區案例
CREATE TABLE tbl_new (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE MYISAM CHARSET utf8
PARTITION BY RANGE(id) (
PARTITION t0 VALUES LESS THAN(10),
PARTITION t1 VALUES LESS THAN(20),
PARTITION t2 VALUES LESS THAN(MAXVALUE)
);
INSERT INTO tbl_new VALUES (1,'z3');
INSERT INTO tbl_new VALUES (2,'z4');
INSERT INTO tbl_new VALUES (3,'z5');
INSERT INTO tbl_new VALUES (4,'z6');
檢視分區
插入資料後再次檢視分區
8.2 List 分區
原理：
MySQL 中的 LIST 分區在很多方面類似于 RANGE 分區。和按照 RANGE 分區一樣，每個分區必須明确定義。它們的主要差別在于，LIST 分區中每個分區的定義和選擇是基于某列的值從屬于一個值清單集中的一個值，而 RANGE 分區是從屬于一個連續區間值的集合。
List 分區案例
create table area (
id INT NOT NULL PRIMARY KEY,
region varchar(20)
)engine myisam charset utf8;
insert into area values (1,'bj');
insert into area values (2,'sh');
insert into area values (3,'gz');
insert into area values (4,'sz');
create table user (
uid int not null,
userName varchar(20),
area_id int
)engine myisam charset utf8
partition by list(area_id) (
partition bj values in (1),
partition sh values in (2),
partition gz values in (3),
partition sz values in (4)
);
insert into user (uid, userName, area_id) values (1,'z3',1);
insert into user (uid, userName, area_id) values (2,'z4',2);
insert into user (uid, userName, area_id) values (3,'z5',3);
8.3 對 NULL 值的處理
MySQL 中的分區在禁止空值 NULL 上沒有進行處理，無論它是一個列值還是一個使用者定義表達式的值，一般而言，在這種情況下 MySQL 把 NULL 當做零。如果你不希望出現類似情況，建議在設計表時聲明該列 “NOT NULL”。
第9章 MySql 鎖機制
9.1 概述
鎖是計算機協調多個程序或線程并發通路某一資源的機制。
在資料庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的争用以外，資料也是一種供許多使用者共享的資源。如何保證資料并發通路的一緻性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發通路性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。
鎖的分類
9.2 三鎖
9.2.1 表鎖(偏讀)案例示範
【表級鎖分析--建表 SQL】
create table mylock (
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
insert into mylock (name) values('a');
insert into mylock (name) values('b');
insert into mylock (name) values('c');
insert into mylock (name) values('d');
insert into mylock (name) values('e');
select * from mylock;
【手動增加表鎖】
lock table tableName1 read/write, tableName2 read/write, 其它;
【檢視表上加過的鎖】
show open tables;
【釋放表鎖】
unlock tables;
我們為 mylock 表加 read 鎖(讀阻塞寫例子)
我們為 mylock 表加 write 鎖(MyISAM 存儲引擎的寫阻塞讀例子)
案例結論
表鎖分析
9.2.2 行鎖(偏寫)案例示範
事務（Transaction）及其 ACID 屬性：
事務是由一組 SQL 語句組成的邏輯處理單元，事務具有以下 4 個屬性，通常簡稱為事務的 ACID 屬性。
原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。
一緻性（Consistent）：在事務開始和完成時，資料都必須保持一緻狀态。這意味着所有相關的資料規則都必須應用于事務的修改，以保持資料的完整性；事務結束時，所有的内部資料結構（如 B 樹索引或雙向連結清單）也都必須是正确的。
隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的 “獨立” 環境執行。這意味着事務處理過程中的中間狀态對外部是不可見的，反之亦然。
持久性（Durable）：事務完成之後，它對于資料的修改是永久性的，即使出現系統故障也能夠保持。
并發事務處理帶來的問題：
更新丢失(Lost Update)：最後的更新覆寫了由其他事務所做的更新。如果在一個程式員在完成并送出事務之前，另一個程式員不能通路同一檔案，則可避免此問題。
髒讀(Dirty Reads)：事務 A 讀取到了事務 B 已修改但尚未送出的的資料，還在這個資料基礎上做了操作。此時，如果 B 事務復原，A 讀取的資料無效，不符合一緻性要求。（讀到了錯誤的資料）
不可重複讀(Non-Repeatable Reads)：事務 A 讀取到了事務 B 已經送出的修改資料，不符合隔離性。（有個資料讀多次，居然讀的不一樣）
幻讀(Phantom Reads)：事務 A 讀取到了事務 B 體送出的新增資料，不符合隔離性。（讀的資料變多了，出現了幻覺）
多說一句：幻讀和髒讀有點類似：
髒讀是事務 B 裡面修改了資料，
幻讀是事務 B 裡面新增了資料。
事務隔離級别：
髒讀”、“不可重複讀” 和 “幻讀”，其實都是資料庫讀一緻性問題，必須由資料庫提供一定的事務隔離機制來解決。
資料庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是沖突的。
同時，不同的應用對讀一緻性和事務隔離程度的要求也是不同的，比如許多應用對“不可重複讀”和“幻讀”并不敏感，可能更關心資料并發通路的能力。
檢視當期資料庫的事務隔離級别指令：show variables like 'tx_isolation';
隔離級别圖解
create table test_innodb_lock (a int(11), b varchar(16)) engine=innodb;
insert into test_innodb_lock values (1,'b2');
insert into test_innodb_lock values (3,'3');
insert into test_innodb_lock values (4,'4000');
insert into test_innodb_lock values (5,'5000');
insert into test_innodb_lock values (6,'6000');
insert into test_innodb_lock values (7,'7000');
insert into test_innodb_lock values (8,'8000');
insert into test_innodb_lock values (9,'9000');
insert into test_innodb_lock values (1,'b1');
create index test_innodb_a_ind on test_innodb_lock (a);
create index test_innodb_lock_b_ind on test_innodb_lock (b);
select * from test_innodb_lock;
行鎖定基本示範
索引失效行鎖更新為表鎖
間隙鎖危害
面試題：如何鎖應一行
案列結論
Innodb 存儲引擎由于實作了行級鎖定
，雖然在鎖定機制的實作方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發處理能力方面要遠遠優于 MyISAM 的表級鎖定的。當系統并發量較高的時候，Innodb 的整體性能和 MyISAM 相比就會有比較明顯的優勢了。
但是，Innodb 的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓 Innodb 的整體性能表現不僅不能比 MyISAM高，甚至可能會更差。
行鎖分析
優化建議
第10章 MySql 主從複制
10.1 複制的基本原理
原理圖
10.2 複制的基本原則 和 複制的最大問題
10.3 一主一從常見配置
Copyright ©2018-2019