第1章MySQL簡介

1.1 什麼是Mysql

1） MySQL是一個關係型數據庫管理系統，由瑞典MySQL AB公司開發，目前屬於Oracle公司。
2） Mysql是開源的，可以定製的，採用了GPL協定，你可以修改原始碼來開發自己的Mysql系統。
3） MySQL使用標準的SQL數據語言形式。
4） Mysql可以允許於多個系統上，並且支援多種語言。這些程式語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
5） MySQL支援大型數據庫，支援5000萬條記錄的數據倉庫，32位元系統表檔案最大可支援4GB，64位元系統支援最大的表檔案爲8TB。

1.2 Mysql高手是怎樣練成的

1）數據庫內部結構和原理
2）數據庫建模優化
3）數據庫索引建立
4） SQL語句優化
5） SQL程式設計(自定義函數、儲存過程、觸發器、定時任務)
6） mysql伺服器的安裝設定
7）數據庫的效能監控分析與系統優化
8）各種參數常數設定
9）主從複製
10）分佈式架構搭建、垂直切割和水平切割
11）數據遷移
12）容災備份和恢復
13） shell或python等指令碼語言開發
14）對開源數據庫進行二次開發

1.3 整體架構圖

和其它數據庫相比，MySQL有點與衆不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在儲存引擎的架構上，外掛式的儲存引擎架構將查詢處理和其它的系統任務以及數據的儲存提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。
（1）連線層
最上層是一些用戶端和連線服務，包含本地sock通訊和大多數基於用戶端/伺服器端工具實現的類似於tcp/ip的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，爲通過認證安全接入的用戶端提供執行緒。同樣在該層上可以實現基於SSL的安全鏈接。伺服器也會爲安全接入的每個用戶端驗證它所具有的操作許可權。

（2）服務層
Management Serveices & Utilities 系統管理和控制工具
SQL Interface: SQL介面。接受使用者的SQL命令，並且返回使用者需要查詢的結果。比如select from就是呼叫SQL Interface
Parser 解析器。 SQL命令傳遞到解析器的時候會被解析器驗證和解析
Optimizer 查詢優化器。 SQL語句在查詢之前會使用查詢優化器對查詢進行優化，比如有where條件時，優化器來決定先投影還是先過濾。
Cache和Buffer 查詢快取。如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取數據。這個快取機制機製是由一系列小快取組成的。比如表快取，記錄快取，key快取，許可權快取等

（3）引擎層
儲存引擎層，儲存引擎真正的負責了MySQL中數據的儲存和提取，伺服器通過API與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
（4）儲存層
數據儲存層，主要是將數據儲存在執行於裸裝置的檔案系統之上，並完成與儲存引擎的互動。

第2章MySQL安裝及其他操作***

2.1在Linux上安裝MySQL

2.1.1 MySQL官網

MySQL官網下載地址

http://dev.mysql.com/downloads/mysql/
MySQL搜狐映象下載地址
http://mirrors.sohu.com/mysql/MySQL-5.7/

2.1.2 MySQL安裝

（1）檢查當前系統是否安裝過Mysql, 需要說明的是CentOS7預設已安裝mariadb(MySQL原始碼的一個分支，MySQL的另外一個衍生版本) ，因此在CentOS6中要檢查mysql，而CentOS7要檢查mariadb
[root@hadoop102 ~]$ rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64 //如果存在通過如下命令解除安裝
[root@hadoop102 ~]$ rpm -e --nodeps mariadb-libs //用此命令解除安裝mariadb
（2）將MySQL安裝包拷貝到/opt/software目錄下
[root@hadoop102 software]# ll
總用量 528384
-rw-r–r--. 1 root root 609556480 3月 21 15:41 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

（3）解壓MySQL安裝包
[root@hadoop102 software]# mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

（4）在安裝目錄下執行rpm安裝
[root@hadoop102 software]$ rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
[root@hadoop102 software]$ rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
[root@hadoop102 software]$ rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
[root@hadoop102 software]$ rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
注意:按照順序依次執行
（5）刪除/etc/my.cnf檔案中datadir指向的目錄下的所有內容:
檢視datadir的值：
[mysqld]
datadir=/var/lib/mysql
刪除/var/lib/mysql目錄下的所有內容:
[root@hadoop102 mysql]# pwd
/var/lib/mysql
[root@hadoop102 mysql]# rm -rf * //注意執行命令的位置
（6）初始化數據庫
[root@hadoop102 opt]$ mysqld --initialize --user=mysql
（7）檢視臨時生成的root使用者的密碼
[root@hadoop102 opt]$ cat /var/log/mysqld.log

（8）啓動MySQL服務
[root@hadoop102 opt]$ service mysqld start
（9）登錄MySQL數據庫
[root@hadoop102 opt]$ mysql -uroot -p
Enter password: 輸入臨時生成的密碼

登錄成功.
（10）必須先修改root使用者的密碼,否則會報錯
mysql> set password = password(「新密碼」)

2.1.3 Mysql的安裝位置

參數路徑解釋備註
–datadir /var/lib/mysql/ mysql數據庫檔案的存放路徑
–basedir /usr/bin 相關命令目錄 mysqladmin mysqldump等命令
–plugin-dir /usr/lib64/mysql/plugin mysql外掛存放路徑
–log-error /var/log/mysqld.log mysql錯誤日誌路徑
–pid-file /var/run/mysqld/mysqld.pid 進程pid檔案
–socket /var/lib/mysql/mysql.sock 本地連線時用的unix通訊端檔案
/usr/share/mysql 組態檔目錄 mysql指令碼及組態檔
/etc/init.d/mysql 服務啓停相關指令碼

2.1.4 MySQL服務的自啓動

（1）Mysql服務是開機自動啓動
[root@hadoop102 ~]$ systemctl list-unit-files | grep mysqld.service
mysqld.service enabled
（2）如果要取消開機自啓動，則輸入命令ntsysv
[root@hadoop102 ~]$ ntsysv
出現以下介面：

使用空格鍵取消選中，然後按TAB確定！
或者
[root@hadoop100 mysql]# systemctl disable mysqld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.

[root@hadoop100 mysql]# systemctl list-unit-files | grep mysqld.service
mysqld.service disabled

2.1.5 修改字元集

（1）常用命令
SQL語句描述備註
show databases 列出所有數據庫
create database 庫名建立一個數據庫
create database 庫名 character set utf8 建立數據庫，順便執行字元集爲utf-8
show create database 庫名檢視數據庫的字元集
show variables like ‘%char%’ 查詢所有跟字元集相關的資訊
set [字元集屬性]=utf8 設定相應的屬性爲utf8 只是臨時修改，當前有效。服務重新啓動後，失效。
alter database 庫名character set ‘utf8’ 修改數據庫的字元集
alter table 表名convert to character set ‘utf8’ 修改表的字元集

（2）案例
mysql>CREATE database mydb;
mysql>use mydb;
mysql>CREATE table mytbl(id int,name varchar(50));
mysql>insert into mytbl(id,name) values (1001,‘jack’);
mysql>insert into mytbl(id,name) values (1002,‘張三’);
ERROR 1366 (HY000): Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column ‘name’ at row 1
Error原因: 當前數據庫的編碼不支援中文。
（3）檢視預設的編碼字元集
mysql>show create database mydb;

mysql>show create table mytbl;

mysql>show variables like ‘%char%’

（4）永久修改預設的編碼字元集
[root@hadoop102 ~]$ vim /etc/my.cnf
#新增如下設定
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
（5）重新啓動MySQL服務
[root@hadoop102 ~]# service mysqld restart
停止 mysqld： [確定]
正在啓動 mysqld： [確定]
再次檢視：

注意：已經建立的數據庫的設定不會發生變化，參數修改只對新建的數據庫有效！
（6）修改已有庫和表的編碼
mysql> alter database mydb character set ‘utf8’;
Query OK, 1 row affected (0.01 sec)

mysql> alter table mytbl convert to character set ‘utf8’;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show create database mydb;

mysql> show create table mytbl;

（7）再次插入中文數據
mysql> insert into mytbl (id, name) values (「1002」,「張三」);
mysql> select * from mytbl;
±-----±---------+
| id | name |`
±-----±---------+
| 1001 | zhangsan |
| 1002 | 張三 |
±-----±---------+

2.2 MySQL的一些雜項設定

2.2.1 設定大小寫不敏感

（1）檢視大小寫是否敏感
mysql> show variables like ‘%lower_case_table_names%’
提示：windows系統預設大小寫不敏感，但是linux系統是大小寫敏感的

（2）設定大小寫不敏感
[root@hadoop102 ~]$ vim /etc/my.cnf
#追加如下內容，然後重新啓動服務
[mysqld]
lower_case_table_names = 1

0 大小寫敏感

1 大小寫不敏感

2 建立的表和DB依據語句上格式存放，凡是查詢都是轉換爲小寫進行

注意：如果要設定屬性爲大小寫不敏感，要在重新啓動數據庫範例之前就需要將原來的數據庫和錶轉換爲小寫，否則將找不到數據庫名。在進行數據庫參數設定之前，需要掌握這個參數帶來的影響，切不可盲目設定。

2.2.2 sql_mode

sql_mode定義了對Mysql中sql語句語法的校驗規則！

sql_mode是個很容易被忽視的變數，如果設定爲空值（非嚴格模式），在這種情況下是可以允許一些非法操作的，比如允許一些非法數據的插入。在生產環境必須將這個值設定爲嚴格模式，所以開發、測試環境的數據庫也必須要設定，這樣在開發測試階段就可以發現問題。
（1） sql_mode常用的值
ONLY_FULL_GROUP_BY 對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因爲列不在GROUP BY從句中
NO_AUTO_VALUE_ON_ZERO 該值影響自增長列的插入。預設設定下，插入0或NULL代表生成下一個自增長值。如果使用者希望插入的值爲0，而該列又是自增長的，那麼這個選項就有用了
STRICT_TRANS_TABLES 在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制
NO_ZERO_IN_DATE 在嚴格模式下，不允許日期和月份爲零
NO_ZERO_DATE 設定該值，mysql數據庫不允許插入零日期，插入零日期會拋出錯誤而不是警告
ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE過程中，如果數據被零除，則產生錯誤而非警告。如果未給出該模式，那麼數據被零除時MySQL返回NULL
NO_AUTO_CREATE_USER 禁止GRANT建立密碼爲空的使用者
NO_ENGINE_SUBSTITUTION 如果需要的儲存引擎被禁用或未編譯，那麼拋出錯誤。不設定此值時，用預設的儲存引擎替代，並拋出一個異常
PIPES_AS_CONCAT 將"||"視爲字串的連線操作符而非或運算子，這和Oracle數據庫是一樣的，也和字串的拼接函數Concat相類似
ANSI_QUOTES 啓用ANSI_QUOTES後，不能用雙引號來參照字串，因爲它被解釋爲識別符
ORACLE 設定等同於PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
（2）檢視當前的sql_mode
mysql> select @@sql_mode;
（3）臨時修改 sql_mode
mysql> set @@sql_mode=’’;
（4）永久修改，需要在組態檔my.cnf中修改
[root@hadoop102 ~]$ vim /etc/my.cnf
#新增下列設定，然後重新啓動mysql即可
[mysqld]
sql_mode=’’
（5） sql_mode的影響案例：group by 查詢語法錯誤！
CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,‘zhang3’,33,101);
INSERT INTO mytbl2 VALUES(2,‘li4’,34,101);
INSERT INTO mytbl2 VALUES(3,‘wang5’,34,102);
INSERT INTO mytbl2 VALUES(4,‘zhao6’,34,102);
INSERT INTO mytbl2 VALUES(5,‘tian7’,36,102);

查詢每個dept中年齡最大的人：
錯誤結果：
SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;
正確結果：
SELECT id,name,ab.dept,ab.maxage FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept)ab ON ab.dept=m.dept AND m.age=ab.maxage;

2.3 MySQL的使用者管理

（1）MySQL的使用者管理在 mysql庫中的user表中
需要瞭解的列: Host,User, authentication_string等，可通過 desc user 檢視user表結構
（1）相關命令
命令描述備註
create user zhang3 identified by ‘123123’; 建立名稱爲zhang3的使用者，密碼設爲123123；
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user; 檢視使用者和許可權的相關資訊
set password =password(‘123456’) 修改當前使用者的密碼
update mysql.user set authentication_string=password(‘123456’) where user=‘li4’; 修改其他使用者的密碼
注意:mysql 5.7 通過authentication_string表示密碼列所有通過user表的修改，必須用flush privileges;命令才能纔能生效
update mysql.user set user=‘li4’ where user=‘wang5’; 修改使用者名稱所有通過user表的修改，必須用flush privileges;命令才能纔能生效
drop user li4 刪除使用者不要通過delete from user u where user=‘li4’ 進行刪除，系統會有殘留資訊保留。

（2）範例說明

host : 表示連線型別
%：表示所有遠端通過 TCP方式的連線
IP地址：如 (192.168.1.2,127.0.0.1) 通過制定ip地址進行的TCP方式的連線
機器名：通過制定i網路中的機器名進行的TCP方式的連線
::1：IPv6的本地ip地址等同於IPv4的 127.0.0.1
localhost：本地方式通過命令列方式的連線,比如mysql -u xxx -p 123xxx 方式的連線
user: 表示使用者名稱
同一使用者通過不同方式連線的許可權是不一樣的。
password: 密碼
所有密碼串通過password(明文字串) 生成的密文字串。
加密演算法爲MYSQLSHA1 ，不可逆。
mysql 5.7 的密碼儲存到 authentication_string 欄位中不再使用password 欄位。
select_priv , insert_priv等 :
該使用者所擁有的許可權。
（3）通過遠端工具存取MySQL
當前root使用者對應的host值爲localhost，意味着只允許本機連線

 需要將host的值修改爲%，表示允許所有遠端通過 TCP方式的連線
  
通過遠端工具測試:

2.4 MySQL的許可權管理

2.4.1 授予許可權

命令描述
grant 許可權1,許可權2,…許可權n on 數據庫名稱.表名稱 to 使用者名稱@使用者地址 identified by ‘連線口令’ 該許可權如果發現沒有該使用者，則會直接新建一個使用者。
範例：
grant select,insert,delete,drop on atguigudb.* to li4@localhost ;
給li4使用者用本地命令列方式下，授予atguigudb這個庫下的所有表的插刪改查的許可權。

grant all privileges on . to joe@’%’ identified by ‘123’; 授予通過網路方式登錄的的joe使用者，對所有庫所有表的全部許可權，密碼設爲123.

2.4.2 收回許可權

命令描述備註
show grants 檢視當前使用者許可權
revoke [許可權1,許可權2,…許可權n] on
庫名.表名 from 使用者名稱@使用者地址 ; 收回許可權命令
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost; 收回全庫全表的所有許可權
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost; 收回mysql庫下的所有表的插刪改查許可權
提示：許可權收回後，必須使用者重新登錄後，才能纔能生效。

2.5 檢視sql的執行週期

（1）檢視profile是否開啓
mysql> show variables like ‘%profiling%’;

（2）開啓profiling
mysql> set profiling=1;

（3）使用profile,可以檢視最近的幾次查詢。
mysql> show profiles;

（4）根據Query_ID,檢視sql的具體執行步驟
mysql> show profile cpu,block io for query 2;

（5）大致的查詢流程
mysql用戶端通過協定與mysql伺服器建連線，發送查詢語句，先檢查查詢快取，如果命中，直接返回結果，否則進行語句解析,也就是說，在解析查詢之前，伺服器會先存取查詢快取(query cache)——它儲存SELECT語句以及相應的查詢結果集。如果某個查詢結果已經位於快取中，伺服器就不會再對查詢進行解析、優化、以及執行。它僅僅將快取中的結果返回給使用者即可，這將大大提高系統的效能。
語法解析器和預處理：首先mysql通過關鍵字將SQL語句進行解析，並生成一顆對應的「解析樹」。mysql解析器將使用mysql語法規則驗證和解析查詢；前處理器則根據一些mysql規則進一步檢查解析數是否合法。
查詢優化器當解析樹被認爲是合法的了，並且由優化器將其轉化成Explian。一條查詢可以有很多種執行方式，最後都返回相同的結果。優化器的作用就是找到這其中最好的Explian。。
然後，mysql預設使用的BTREE索引，並且一個大致方向是:無論怎麼折騰sql，至少在目前來說，mysql最多隻用到表中的一個索引。
（6）SQL的書寫順序
SELECT- DISTINCT- FROM- JOIN ON- WHERE- GROUP BY- HIVING- ORDER BY- LIMIT

（7）真正執行的順序：
隨着Mysql版本的更新換代，其優化器也在不斷的升級，優化器會分析不同執行順序產生的效能消耗不同而動態調整執行順序。下面下麪是經常出現的查詢順序：
FROM- ON- JOIN- WHERE- GROUP BY- HAVING- SELECT- DISTINCT- ORDER BY- LIMIT

2.6 查詢快取

2.6.1 檢視查詢快取相關的設定

檢視快取相關的設定
mysql> show variables like 「%query_cache%」;
相關參數解釋
 query_cache_limit：超過此大小的查詢將不再快取。
 query_cache_min_res_unit：快取塊的最小值。
 query_cache_size：快取大小值
 query_cache_type：快取型別，決定快取什麼樣的查詢。
 0 表示關閉查詢快取OFF
 1 表示開啓查詢快取ON
 2 表示SQL語句中有SQL_CACHE關鍵詞時才快取…
例如: select SQL_CACHE name from t_user where id = 1001;
 query_cache_wlock_invalidate：表示當有其它用戶端正在對MyISAM表進行寫操作時，讀請求是要等write lock釋放資源後再查詢還是允許直接從query cache中讀取結果

2.6.2 開啓MySQL的查詢快取

在MySQL的組態檔中 /etc/my.cnf中[mysqld] 節點下新增如下設定:
[mysqld]
query_cache_type = 1
重新啓動MySQL服務
[root@hadoop102 ~]# service mysqld restart
停止 mysqld： [確定]
正在啓動 mysqld： [確定]

2.6.3 使用查詢快取

開啓profiling
mysql> set profiling =1 ;
在MySQL中執行兩條相同的SQL
mysql> select * from mytbl2;
mysql> select * from mytbl2;
檢視最近執行的SQL
mysql> show profiles;
檢視兩條相同SQL的執行週期
 檢視第一次執行的SQL
mysql> show profile cpu,block io for query 1 ;

 檢視第二次執行的SQL
mysql> show profile cpu,block io for query ;

可以看出第二次執行的SQL結果是從快取中查詢.

2.6.4 查詢不使用快取

如果在開啓了查詢快取的情況, 某條SQL執行時不想使用快取，可在SQL中顯示執行
SQL_NO_CACHE
mysql> select SQL_NO_CACHE * from mytbl2;

2.7 MySQL儲存引擎

2.7.1 檢視儲存引擎

檢視支援的儲存引擎
mysql> show engines;
檢視當前MySQL預設的儲存引擎

2.7.2 各個儲存引擎介紹

InnoDB儲存引擎
InnoDB是MySQL的預設事務型引擎，它被設計用來處理大量的短期(short-lived)事務。除非有非常特別的原因需要使用其他的儲存引擎，否則應該優先考慮InnoDB引擎。
MyISAM儲存引擎
MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(GIS)等，但MyISAM不支援事務和行級鎖，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。
Archive引擎
Archive檔案儲存引擎只支援INSERT和SELECT操作，在MySQL5.1之前不支援索引。
Archive表適合日誌和數據採集類應用。
根據英文的測試結論來看，Archive表比MyISAM表要小大約75%，比支援事務處理的InnoDB表小大約83%。
Blackhole引擎
Blackhole引擎沒有實現任何儲存機制機製，它會丟棄所有插入的數據，不做任何儲存。但伺服器會記錄Blackhole表的日誌，所以可以用於複製數據到備庫，或者簡單地記錄到日誌。但這種應用方式會碰到很多問題，因此並不推薦。
CSV引擎
CSV引擎可以將普通的CSV檔案作爲MySQL的表來處理，但不支援索引。
CSV引擎可以作爲一種數據交換的機制機製，非常有用。
CSV儲存的數據直接可以在操作系統裡，用文字編輯器，或者excel讀取。
Memory引擎
如果需要快速地存取數據，並且這些數據不會被修改，重新啓動以後丟失也沒有關係，那麼使用Memory表是非常有用。Memory表至少比MyISAM表要快一個數量級。
Federated引擎
Federated引擎是存取其他MySQL伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。
2.7.3 MyISAM和InnoDB引擎
對比項 MyISAM InnoDB
外來鍵不支援支援
事務不支援支援
行表鎖表鎖，即使操作一條記錄也會鎖住整個表，不適合高併發的操作行鎖,操作時只鎖某一行，不對其它行有影響，
適合高併發的操作
快取只快取索引，不快取真實數據不僅快取索引還要快取真實數據，對記憶體要求較高，而且記憶體大小對效能有決定性的影響
關注點讀效能併發寫、事務、資源
預設安裝 Y Y
預設使用 N Y
自帶系統表使用 Y N

第3章SQL預熱***
3.1 常見的Join查詢圖
內連線: A inner join B on
內連線的結果集: 交集
外連線: A left outer join B on A right outer join B on
主表(驅動表) 從表(匹配表)
外連線確定主從表: 左外連左主右從，右外連右主左從！
外連線的結果集: 主表取所有，從表取匹配. 主表與從表未匹配的數據通過null來補全.

3.2 Join範例

建表語句
CREATE TABLE t_dept (
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE t_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (id),
KEY idx_dept_id (deptId)
#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES t_dept (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES(‘華山’,‘華山’);
INSERT INTO t_dept(deptName,address) VALUES(‘丐幫’,‘洛陽’);
INSERT INTO t_dept(deptName,address) VALUES(‘峨眉’,‘峨眉山’);
INSERT INTO t_dept(deptName,address) VALUES(‘武當’,‘武當山’);
INSERT INTO t_dept(deptName,address) VALUES(‘明教’,‘光明頂’);
INSERT INTO t_dept(deptName,address) VALUES(‘少林’,‘少林寺’);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘風清揚’,90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘岳不羣’,50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘令狐沖’,24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘洪七公’,70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘喬峯’,35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘滅絕師太’,70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘周芷若’,20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘張三豐’,100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘張無忌’,25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(‘韋小寶’,18,null,100010);

所有有門派人員的資訊（要求顯示門派名稱）
SELECT e.name,d.deptName FROM t_emp e INNER JOIN t_dept d ON e.deptId=d.id;
列出所有人員及其門派資訊
SELECT e.name,d.deptName FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id;
列出所有門派
SELECT * FROM t_dept;
所有無門派人士
SELECT * FROM t_emp WHERE deptId IS NULL;
所有無人門派
SELECT d.* FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
所有人員和門派的對應關係
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId=d.id;
所有沒有入門派的人員和沒人入的門派
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id WHERE e.deptId IS NULL
UNION
SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
新增CEO欄位
ALTER TABLE t_dept
add CEO INT(11) ;
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;
求各個門派對應的掌門人名稱
SELECT d.deptName,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id
求所有當上掌門人的平均年齡
SELECT AVG(e.age) FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id
求所有人物對應的掌門名稱
SELECT ed.name ‘人物’,c.name ‘掌門’
FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed
LEFT JOIN t_emp c on ed.ceo= c.id;

SELECT e.name ‘人物’,tmp.name ‘掌門’
FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp
ON e.deptId=tmp.did;

SELECT e1.name ‘人物’,e2.name ‘掌門’
FROM t_emp e1
LEFT JOIN t_dept d on e1.deptid = d.id
LEFT JOIN t_emp e2 on d.ceo = e2.id ;

SELECT e2.name ‘人物’,
(SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) ‘掌門’
from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id;
第4章索引優化分析
4.1 索引簡介
4.1.1 索引是什麼
MySQL官方對索引的定義爲：索引（Index）是幫助MySQL高效獲取數據的數據結構。可以得到索引的本質：索引是數據結構。可以簡單理解爲排好序的快速查詢數據結構。
在數據之外，數據庫系統還維護着滿足特定查詢演算法的數據結構，這些數據結構以某種方式參照（指向）數據，這樣就可以在這些數據結構上實現高階查詢演算法。這種數據結構，就是索引。下圖就是一種可能的索引方式範例：

左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的實體地址。爲了加快Col2的查詢，可以維護一個右邊所示的二叉查詢樹，每個節點分別包含索引鍵值和一個指向對應數據記錄實體地址的指針，這樣就可以運用二叉查詢在一定的複雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。
一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。
4.1.2 索引的優劣勢

優點：
提高數據檢索的效率，降低數據庫的IO成本。
通過索引列對數據進行排序，降低數據排序的成本，降低了CPU的消耗。
劣勢：
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、
UPDATE和DELETE。因爲更新表時，MySQL不僅要儲存數據，還要儲存一下索引文
件每次更新新增了索引列的欄位，都會調整因爲更新所帶來的鍵值變化後的索引資訊。

實際上索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄，所以索引
列也是要佔用空間的。

4.2 MySQL的索引結構
4.2.1 B-tree索引

一顆b樹，淺藍色的塊我們稱之爲一個磁碟塊，可以看到每個磁碟塊包含幾個數據項（深藍色所示）和指針（黃色所示），如磁碟塊1包含數據項17和35，包含指針P1、P2、P3，P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。
如果要查詢數據項29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分查詢確定29在17和35之間，鎖定磁碟塊1的P2指針，記憶體時間因爲非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指針的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指針，通過指針載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分查詢找到29，結束查詢，總計三次IO。
4.2.2 B+tree索引

B+樹的非葉子結點僅僅儲存着關鍵字資訊和兒子的指針，B+樹中的數據都儲存在葉子結點上，也就是其所有葉子結點的數據組合起來就是完整的數據.因此每個磁碟塊包含的關鍵字資訊會更多。這樣也就決定了載入一個磁碟塊可以獲取到更多的關鍵字，可以減少IO操作，
一次IO操作相當於成百上千次的記憶體比較.
在B+樹上增加了順序存取指針，也就是每個葉子節點增加一個指向相鄰葉子節點的指針，這樣一棵樹成了數據庫系統實現索引的首選數據結構
4.2.3 B-Tree與B+Tree 的區別

B-樹的關鍵字和記錄是放在一起的， B+樹的非葉子節點中只有關鍵字和指向下一個節點的索引，記錄只放在葉子節點中。
在B-樹中，越靠近根節點的記錄查詢時間越快，只要找到關鍵字即可確定記錄的存在；而B+樹中每個記錄的查詢時間基本是一樣的，都需要從根節點走到葉子節點，而且在葉子節點中還要再比較關鍵字。從這個角度看B-樹的效能好像要比B+樹好，而在實際應用中卻是B+樹的效能要好些。因爲B+樹的非葉子節點不存放實際的數據，這樣每個節點可容納的元素個數比B-樹多，樹高比B-樹小，這樣帶來的好處是減少磁碟存取次數。儘管B+樹找到一個記錄所需的比較次數要比B-樹多，但是一次磁碟存取的時間相當於成百上千次記憶體比較的時間，因此實際中B+樹的效能可能還會好些，而且B+樹的葉子節點使用指針連線在一起，方便順序遍歷（例如檢視一個目錄下的所有檔案，一個表中的所有記錄等），這也是很多數據庫和檔案系統使用B+樹的緣故。
　
4.2.4 思考：爲什麼說B+樹比B-樹更適合實際應用中操作系統的檔案索引和數據庫索引`？
B+樹的磁碟讀寫代價更低
B+樹的內部結點並沒有指向關鍵字具體資訊的指針。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
B+樹的查詢效率更加穩定
由於非終結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。

4.2.5 聚簇索引和非聚簇索引
聚簇索引並不是一種單獨的索引型別，而是一種數據儲存方式。術語‘聚簇’表示數據行和相鄰的鍵值聚簇的儲存在一起。如下圖，左側的索引就是聚簇索引，因爲數據行在磁碟的排列和索引排序保持一致。

聚簇索引的好處：
按照聚簇索引排列順序，查詢顯示一定範圍數據的時候，由於數據都是緊密相連，數據庫不用從多個數據塊中提取數據，所以節省了大量的io操作。
聚簇索引的限制：
對於mysql數據庫目前只有innodb數據引擎支援聚簇索引，而Myisam並不支援聚簇索引。
由於數據物理儲存排序方式只能有一種，所以每個Mysql的表只能有一個聚簇索引。一般情況下就是該表的主鍵。
爲了充分利用聚簇索引的聚簇的特性，所以innodb表的主鍵列儘量選用有序的順序id，而不建議用無序的id，比如uuid這種。
4.3. MySQL索引分類
4.3.1 單值索引 INDEX
概念：即一個索引只包含單個列，一個表可以有多個單列索引

隨表一起建立：
CREATE TABLE customer (
id INT(10) AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
單獨建單值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
檢視某個表的索引
show index from 表名
show keys from 表名
4.3.2 唯一索引
概念：索引列的值必須唯一，但允許有空值
隨表一起建立：
CREATE TABLE customer (
id INT(10) AUTO_INCREMENT ,
customer_no
VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
單獨建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

4.3.3 主鍵索引
概念：設定爲主鍵後數據庫會自動建立索引，innodb爲聚簇索引

隨表一起建索引
CREATE TABLE customer (
id INT(10) AUTO_INCREMENT ,
customer_no
VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
單獨建主鍵索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
刪除建主鍵索引：
ALTER TABLE customer drop PRIMARY KEY ;
需要注意的問題：
設定爲自增的主鍵上的索引不能刪除.
4.3.4 複合索引
概念：即一個索引包含多個列
隨表一起建索引：
CREATE TABLE customer (
id INT(10) AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
單獨建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

4.3.5 基本語法
建立 CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
刪除 DROP INDEX [indexName] ON mytable;
檢視 SHOW INDEX FROM table_name\G
使用Alter命令 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : 該語句新增一個主鍵，這意味着索引值必須是唯一的，且不能爲NULL。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 新增普通索引，索引值可出現多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引爲 FULLTEXT ，用於全文索引。
4.4 索引的建立時機
4.4.1 適合建立索引的情況

主鍵自動建立唯一索引
頻繁作爲查詢條件的欄位應該建立索引
查詢中與其它表關聯的欄位，外來鍵關係建立索引
單鍵/組合索引的選擇問題，組合索引性價比更高
查詢中排序的欄位，排序欄位若通過索引去存取將大大提高排序速度
查詢中統計或者分組欄位
總結: 查詢中過濾、統計、分組、排序、關聯所能用到的欄位應該建立索引,
建索引優先考慮複合索引，其次考慮單值索引.
4.4.2 不適合建立索引的情況
表記錄太少
經常增刪改的表或者欄位
Where條件裡用不到的欄位不建立索引
過濾性不好的不適合建索引
第5章 Explain效能分析
5.1 概念
使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的效能瓶頸。
官網地址：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
5.2 怎麼用
Explain+SQL語句。
Explain執行後返回的資訊：

5.2 Explain分析準備工作
5.2.1 Explain欄位解釋
id: select 查詢的序列號`,表示查詢中執行select子句或操作表的順序。
select_type: 主要用於區別普通查詢、聯合查詢、子查詢等的複雜查詢。
table: 這個數據是基於哪張表的。
partitions: 查詢數據匹配的分割區
type: 是查詢的存取型別，是較爲重要的一個指標
possible_keys: 顯示可能應用在這張表中的索引，一個或多個。
key: 實際使用的索引。如果爲NULL，則沒有使用索引。
key_len: 表示索引中使用的位元組數
ref: 顯示索引的哪一列被使用了
rows: 顯示MySQL認爲它執行查詢時必須檢查的行數，不精確。
filtered: 返回結果的行佔需要讀到的行(rows列的值)的百分比
Extra: 其他的額外重要的資訊。
5.2.2 建立測試數據
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));

INSERT INTO t1(content) VALUES(CONCAT(‘t1_’,FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT(‘t2_’,FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT(‘t3_’,FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT(‘t4_’,FLOOR(1+RAND()*1000)));
5.3 Explain欄位分析
5.3.1 Explain 之id
id: select查詢的序列號,包含一組數位，表示查詢中執行select子句或操作表的順序

id相同：執行順序由上至下
mysql>EXPLAIN select * from t1,t2,t3 where t1.id = t2.id and t2.id = t3.id ;
id不同：如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
mysql> EXPLAIN select t1.id from t1 where t1.id in
(select t2.id from t2 where t2.id in
(select t3.id from t3 where t3.content = ‘’)
);

MySQL5.5結果:

有相同也有不同
mysql> EXPLAIN select t2.* from t2 ,(select * from t3 where t3.content = ‘’) s3 where
s3.id = t2.id ;

MySQL5.5的結果:

總結：
id如果相同，可以認爲是一組，從上往下順序執行；
在所有組中，id值越大，優先順序越高，越先執行。
id號每個號碼，表示一趟獨立的查詢。一個sql 的查詢趟數越少越好。
5.3.2 Explain 之select_type
select_type : 主要用於區別普通查詢、聯合查詢、子查詢等的複雜查詢。
SIMPLE：代表單表查詢
mysql>EXPLAIN select * from t1 ;
PRIMARY：查詢中若包含任何複雜的子部分，最外層查詢則被標記爲Primary。

MySQL5.5結果:

DERIVED：在FROM查詢中的子查詢，結果存放在臨時表中
SUBQUERY：在WHERE列表中包含了子查詢。
mysql> EXPLAIN select t2.id from t2 where t2.id =
( select t3.id from t3 where t3.id = 1);
DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查詢,子查詢基於外層。
mysql> EXPLAIN select t2.id from t2 where t2.id in
(select t3.id from t3 where t3.content = ‘t3_522’);

MySQL5.5的結果:
注意：SUBQUERY和DEPENDENT SUBQUERY都是where後面的條件，subquery是單個值，dependent subquery是一組值。
6) UNCACHEABLE SUBQUREY：當使用了@@來參照系統變數的時候，不會使用快取。
mysql> EXPLAIN select * from t3 where id =
(select id from t2 where t2.id = @@sort_buffer_size);

UNION：若第二個SELECT出現在UNION之後，則被標記爲UNION；
若UNION包含在FROM子句的子查詢中,外層SELECT將被標記爲：DERIVED。
mysql> EXPLAIN select t2.id ,t2.content from t2
union all
select t3.id ,t3.content from t3 ;

mysql> EXPLAIN select t2.id ,t2.content from
(select t3.id, t3.content from t3 union all select t1.id ,t1.content from t1 ) t2;

UNION RESULT：從UNION表獲取結果的SELECT。
mysql> EXPLAIN select t2.id ,t2.content from t2
union all
select t3.id ,t3.content from t3 ;
MySQL5.5結果:

5.3.3 Explain 之type
type是查詢的存取型別，是較爲重要的一個指標結果值從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，
一般來說，得保證查詢至少達到range級別，最好能達到ref。

system
表只有一行記錄（等於系統表），這是const型別的特列，平時不會出現，這個也可以忽略不計。
const
表示通過索引一次就找到了,const用於比較primary key或者unique索引。因爲只匹配一行數據，所以很快。如將主鍵置於where列表中，MySQL就能將該查詢轉換爲一個常數。
mysql> EXPLAIN select * from t1 where t1.id = 1;
eq_ref
唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描。
mysql> EXPLAIN select * from t1 ,t2 where t1.id = t2.id ;
ref
非唯一性索引掃描，返回匹配某個單獨值的所有行.本質上也是一種索引存取，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬於查詢和掃描的混合體。
 沒用索引前：
mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content;

 建立索引後：
mysql> create index idx_t2_content on t2(content);
mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content;

range
只檢索給定範圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引一般就是在你的where語句中出現了between、<、>、in等的查詢這種範圍掃描索引掃描比全表掃描要好，因爲它只需要開始於索引的某一點，而結束語另一點，不用掃描全部索引。
mysql> EXPLAIN select * from t1 where t1.id >10 ;

mysql> EXPLAIN select * from t1 where t1.id between 1 and 3 ;

mysql> EXPLAIN select * from t1 where t1.id in (1,2);

index
出現index是sql使用了索引但是沒用通過索引進行過濾，一般是使用了覆蓋索引或者是利用索引進行了排序分組。
mysql> EXPLAIN select * from t1;
mysql> EXPLAIN select id from t1;
all
Full Table Scan，將遍歷全表以找匹配的行。
mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content ;
index_merge
在查詢過程中需要多個索引組合使用，通常出現在有 or 的關鍵字的sql中。
MySQL5.5的結果:
ref_or_null
對於某個欄位既需要關聯條件，也需要null值得情況下。查詢優化器會選擇用ref_or_null連線查詢。
mysql> EXPLAIN select * from t2 where t2.content is null or t2.content = ‘abc’;
index_subquery
利用索引來關聯子查詢，不再全表掃描。
mysql> create index idx_t3_content on t3(content);
mysql> EXPLAIN select * from t2 where t2.content in (select t3.content from t3) ;

MySQL5.5的結果:

unique_subquery
該聯接型別類似於index_subquery。子查詢中的唯一索引。
mysql> EXPLAIN select * from t2 where t2.id in (select t3.id from t3 );

MySQL5.5的結果

說明：一般來說，得保證查詢至少達到range級別，最好能達到ref。
5.3.4 Explain 之possible_keys
顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用。
5.3.5 Explain 之 key
實際使用的索引。如果爲NULL，則沒有使用索引。
5.3.6 Explain 之 key_len
表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。 key_len欄位能夠幫你檢查是否充分的利用上了索引。ken_len越長，說明索引使用的越充分。
範例如下

如何計算：
1）先看索引上欄位的型別+長度比如 int=4 ; varchar(20) =20 ; char(20) =20
2）如果是varchar或者char這種字串欄位，視字元集要乘不同的值，比如utf-8 要乘 3,GBK要乘2
3） varchar這種動態字串要加2個位元組
4）允許爲空的欄位要加1個位元組
第一組：key_len=age的位元組長度：name的位元組長度=4+1 + ( 20*3+2)=5+62=67
第二組：key_len=age的位元組長度：4+1=5

5.3.7 Explain 之 ref
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常數被用於查詢索引列上的值。
mysql> create index idx_name_t_emp on t_emp(name);
mysql> EXPLAIN select * from t_emp emp ,t_dept dept where emp.name = ‘aaa’
and emp.deptId = dept.id ;

5.3.8 Explain 之 rows
rows列顯示MySQL認爲它執行查詢時必須檢查的行數。越少越好！
mysql> create index idx_name_t_emp on t_emp(name);
mysql> EXPLAIN select * from t_emp emp ,t_dept dept where emp.name = ‘aaa’
and emp.deptId = dept.id ;

5.3.9 Explain 之 Extra

Using filesort
說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱爲「檔案排序」。
注意: 測試表數據不能太小。
 優化前：
mysql> explain select id ,empno ,name from t_emp
where deptid = 100 order by name limit 10 ;

 優化後：
mysql> create index idx_name_emp_1 on emp(name);
mysql> explain select id ,empno ,name from emp where deptId = 100 order by name limit 10 ;

Using temporary
使用臨時表儲存中間結果,MySQL在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。
 優化前：
mysql> explain select name, count(*) from emp where deptid = 100 group by name limit 10 ;

 優化後：
mysql> create index idx_name_emp_1 on emp(name);
mysql> explain select name, count(*) from emp where deptid = 100 group by name limit 10 ;

Using index
Using index表示相應的select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數據行，效率不錯！
如果同時出現using where，表明索引被用來執行索引鍵值的查詢;如果沒有同時出現using where，表明索引只是用來讀取數據而非利用索引執行查詢。
mysql> explain select id from emp where id >1000 order by id ;
Using where：表明使用了where過濾。
Using join buffer：使用了連線快取。
mysql> explain select * from emp ,dept where emp.empno = dept.ceo ;
impossible where：
where子句的值總是false，不能用來獲取任何元組。
mysql> explain select empno, name from emp where empno >200000 and empno < 100000;

MySQL5.5的結果:

select tables optimized away
在沒有GROUPBY子句的情況下，基於索引優化MIN/MAX操作或者對於MyISAM儲存引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢Explian生成的階段即完成優化。
 在innodb中：
mysql> explain select max(id) from emp ;

 在Myisam中：

第6章批次數據指令碼
6.1 準備工作

建表語句
CREATE TABLE dept (
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE emp (
id INT(11) NOT NULL AUTO_INCREMENT,
empno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES t_dept (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2) 開啓log_bin_trust_function_creators
它控制是否可以信任儲存函數建立者
mysql> show variables like ‘log_bin_trust_function_creators’; //查詢
mysql> set global log_bin_trust_function_creators=1; //開啓
設定永久生效方式：修改/etc/my.cnf 檔案
[mysqld]
log_bin_trust_function_creators=1
6.2 編寫隨機函數

生成隨機字串
DELIMITER $CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END$
如果要刪除函數，則執行：drop function rand_string
生成亂數字
#用於隨機產生多少到多少的編號
DELIMITER $CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ; RETURN i; END$
如果要刪除函數：drop function rand_num
6.3 建立儲存過程
建立往emp表中插入數據的儲存過程
DELIMITER $KaTeX parse error: Expected 'EOF', got '#' at position 96: \dots DEFAULT 0; #̲set autocommit \dots$

#刪除

DELIMITER ;

drop PROCEDURE insert_emp;

建立往dept表中插入數據的儲存過程
#執行儲存過程，往dept表新增隨機數據
DELIMITER $CREATE PROCEDURE `insert_dept`( max_num INT ) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000)); UNTIL i = max_num END REPEAT; COMMIT; END$

#刪除

DELIMITER ;

drop PROCEDURE insert_dept;

6.4 呼叫儲存過程

新增數據到部門表
#執行儲存過程，往dept表新增1萬條數據
DELIMITER ;
CALL insert_dept(10000);
新增數據到員工表
#執行儲存過程，往emp表新增50萬條數據
DELIMITER ;
CALL insert_emp(100000,500000);

6.5 批次刪除某個表上的所有索引

刪除索引的儲存過程
DELIMITER $CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200)) BEGIN DECLARE done INT DEFAULT 0; DECLARE ct INT DEFAULT 0; DECLARE _index VARCHAR(200) DEFAULT ''; DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ; DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ; OPEN _cur; FETCH _cur INTO _index; WHILE _index<>'' DO SET @str = CONCAT("drop index ",_index," on ",tablename ); PREPARE sql_str FROM @str ; EXECUTE sql_str; DEALLOCATE PREPARE sql_str; SET _index=''; FETCH _cur INTO _index; END WHILE; CLOSE _cur; END$
執行儲存過程
CALL proc_drop_index(「dbname」,「tablename」);
6.6 批次數據指令碼
同學們只需要在Mysql中執行如下指令碼即可完成上述的準備工作.本章內容無須掌握，只是爲後續的內容做準備。
…\2.資料\批次數據指令碼\batch.sql
首先將batch.sql 上傳到Linux的某個目錄下: 如 /opt/software/mysql/batch.sql ,然後在mysql中執行如下操作:
mysql> source /opt/software/mysql/batch.sql

第7章單表使用索引常見的索引失效***
7.1 全值匹配我最愛

建立索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
檢視下列sql的Explian
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = ‘abcd’

結論：全值匹配我最愛指的是，查詢的欄位按照順序在索引中都可以匹配到！
3) 更換條件順序檢視索引能否被應用
結論：SQL中查詢欄位的順序，跟使用索引中欄位的順序沒有關係。
7.2 最佳左字首法則
使用複合索引，需要遵循最佳左字首法則，即如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列。

結論：過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個欄位，索引後面的欄位都無法被使用。
7.3 索引列上做計算
不在索引列上做任何操作（計算、函數、(自動or手動)型別轉換），會導致索引失效而轉向全表掃描。

在查詢列上使用了函數
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

結論：等號左邊無計算！
2) 在查詢列上做了轉換
create index idx_name on emp(name);
explain select sql_no_cache * from emp where name=‘30000’;
explain select sql_no_cache * from emp where name=30000;

結論：等號右邊無轉換！
7.4 索引列上進行範圍查詢

在索引列上使用範圍查詢
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = ‘abcd’;
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<5 AND emp.name = ‘abcd’;

建議：將可能做範圍查詢的欄位的索引順序放在最後
7.5 使用覆蓋索引

查詢列和索引列時，不要寫select *
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name=‘XamgXt’;
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name=‘XamgXt’;

7.6 使用不等於(!= 或者<>)

使用不等於(!= 或者<>)時，有時會無法使用索引會導致全表掃描。
mysql> explain select SQL_NO_CACHE * from emp where emp.age = 30 ;
mysql> explain select SQL_NO_CACHE * from emp where emp.age != 30 ;

7.7 is not null 和 is null

檢視索引欄位是否允許爲空
mysql> desc emp;
當欄位允許爲Null的條件下
explain select * from emp where age is null
explain select * from emp where age is not null

結論：is not null用不到索引，is null可以用到索引。
7.8 like的前後模糊匹配

字首模糊查詢會導致索引失效
mysql> create index idx_name_emp on emp (name);
mysql> explain select * from emp where name like ‘%a’ ;
mysql> explain select * from emp where name like ‘%a%’ ;
mysql> explain select * from emp where name like ‘a%’ ;

7.9 使用or

OR查詢會導致索引失效
mysql> explain select * from emp where age = 30 or age = 40 ;
使用union all或者union來替代
mysql> explain select * from emp where age = 30 union all select * from emp where age =
40 ;

7.10 總結練習

建立索引index(a,b,c)，判斷下列索引參照情況。
Where語句索引是否被使用
where a = 3 Y,使用到a
where a = 3 and b = 5 Y,使用到a，b
where a = 3 and b = 5 and c = 4 Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到a，但是c不可以，b中間斷了
where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在範圍之後，b斷了
where a is null and b is not null is null 支援索引但是is not null 不支援,所以 a 可以使用索引,但是 b不可以使用
where a <> 3 不能使用索引
where abs(a) =3 不能使用索引
where a = 3 and b like ‘kk%’ and c = 4 Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4 Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4 Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4 Y,使用到a,b,c

7.11 口訣
全值匹配我最愛，最左字首要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，範圍之後全失效；
LIKE百分寫最右，覆蓋索引不寫*；
不等空值還有OR，索引影響要注意；
第8章查詢優化
8.1 關聯查詢優化

建表語句
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)));

8.1.1 left join優化

檢視Explian
mysql> explain select * from class left join book on class.card = book.card ;
在book表上建立索引
mysql> ALTER TABLE book ADD INDEX idx_card(card);

在class表上建立索引
mysql> drop index idx_card on book;
mysql> alter table class add index idx_card(cad);
結論：在優化關聯查詢時，只有在匹配表上建立索引纔有效。
left join時，左側的爲驅動表，右側爲匹配表！
8.1.2 inner join優化

檢視Explian
mysql> explain select * from book inner join class on class.card = book.card;
在book表上建索引
mysql>ALTER TABLE book ADD INDEX idx_card(card);
mysql> explain select * from book inner join class on class.card = book.card;

調換兩個表的順序
mysql> explain select * from class inner join book on class.card = book.card;

在book表中，刪除9條記錄
mysql> delete from book where bookid <10 ;
mysql> select count() from book ;
mysql> select count() from class ;

mysql> CALL proc_drop_index(「mydb」,「book」);
mysql> explain select * from class inner join book on class.card = book.card;

結論：inner join 時，mysql會自己幫你把小結果集的表選爲驅動表。
4) straight_join: 效果和inner join一樣，但是會強制將左側作爲驅動表！
mysql> explain select * from class straight_join book on class.card= book.card;

8.1.3 四個關聯查詢案例分析

案例一
EXPLAIN SELECT ed.name ‘人物’,c.name ‘掌門’ FROM
(SELECT e.name,d.ceo from emp e LEFT JOIN dept d on e.deptid=d.id) ed
LEFT JOIN emp c on ed.ceo= c.id;

MySQL5.5結果:

案例二
EXPLAIN SELECT e.name ‘人物’,tmp.name ‘掌門’
FROM emp e LEFT JOIN (SELECT d.id did,e.name FROM dept d LEFT JOIN emp e ON d.ceo=e.id)tmp
ON e.deptId=tmp.did;

MySQL5.5結果:

案例三
EXPLAIN SELECT e1.name ‘人物’,e2.name ‘掌門’
FROM emp e1
LEFT JOIN dept d on e1.deptid = d.id
LEFT JOIN emp e2 on d.ceo = e2.id ;

MySQL5.5結果:

案例四
Explain SELECT e2.name ‘人物’,
(SELECT e1.name FROM emp e1 where e1.id= d.ceo) ‘掌門’
from emp e2 LEFT JOIN dept d on e2.deptid=d.id;

MySQL5.5結果：

8.1.4 建議

保證被驅動表的join欄位已經被索引
Left Join 或者 right join時，選擇小表作爲驅動表，大表作爲被驅動表
inner join 時，mysql會自己把小表選爲驅動表
子查詢儘量不要放在被驅動表，有可能使用不到索引
能直接多表關聯就儘量直接關聯，不用子查詢

8.2 子查詢優化

取所有不爲掌門人的員工，按年齡分組！
mysql> CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
mysql> explain select age as ‘年齡’, count(*) as ‘人數’ from emp where id not in
(select ceo from dept where ceo is not null) group by age;
解決dept表的全表掃描，建立ceo欄位的索引
進一步優化，替換not in。
mysql>explain select age as ‘年齡’,count(*) as ‘人數’ from emp e left join dept d on e.id=d.ceo
where d.id is null group by age;
結論：在範圍判斷時，儘量不要使用not in和not exists，使用 left join on xxx is null代替。
8.3 排序分組優化
where 條件和 on的判斷這些過濾條件，作爲優先優化的部分，是要被先考慮的！其次，如果有分組和排序，那麼也要考慮group by 和order by。
8.3.1 無過濾，不索引
建立索引，檢視Explian
create index idx_age_deptid_name on emp (age,deptid,name);
explain select * from emp where age=40 order by deptid;
explain select * from emp order by age,deptid;
explain select * from emp order by age,deptid limit 10;
結論：using filesort說明進行了手工排序。原因在於沒有where作爲過濾條件。where，limt都相當於一種過濾條件，所以才能纔能使用上索引。
8.3.2 順序錯，必排序
檢視Explian
explain select * from emp where age=45 order by deptid,name;
explain select * from emp where age=45 order by deptid,empno;
explain select * from emp where age=45 order by name,deptid;
explain select * from emp where deptid=45 order by age;

結論:正常使用索引
結論：empno欄位並沒有建立索引，因此也無法用到索引，此欄位需要排序！
結論：where 兩側列的順序可以變換，但是order by列的順序不能隨便變換！
結論：deptid作爲過濾條件的欄位，無法使用索引，因此排序沒法用上索引
8.3.3. 方向反，必排序

檢視Explian
explain select * from emp where age=45 order by deptid desc, name desc ;
結論：如果可以用上索引的欄位都使用正序或者逆序，實際上是沒有任何影響的，無非將結果集調換順序。
explain select * from emp where age=45 order by deptid asc, name desc ;
結論：如果排序的欄位，順序有差異，就需要將差異的部分，進行一次倒置順序，因此還是需要手動排序的！
8.3.4. 索引的選擇
清除emp上面的所有索引，只保留主鍵索引！
CALL proc_drop_index(「mydb」,「emp」);
查詢年齡爲30歲的，且員工編號小於101000的使用者，按使用者名稱稱排序
explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
優化：建立一個此三個欄位的複合索引
create index idx_age_empno_name on emp(age,empno,name);
再次查詢，發現using filesort依然存在。empno是範圍查詢，因此導致了索引失效，所以name欄位無法使用索引排序。所以，三個欄位的符合索引，沒有意義，因爲empno和name欄位只能選擇其一！
解決：要麼選擇empno,要麼選擇name
CALL proc_drop_index(「mydb」,「emp」);
create index idx_age_name on emp(age,name);
create index idx_age_empno on emp(age,empno);
explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

原因：所有的排序都是在條件過濾之後才執行的，所以如果條件過濾了大部分數據的話，幾百幾千條數據進行排序其實並不是很消耗效能，即使索引優化了排序但實際提升效能很有限。相對的 empno<101000 這個條件如果沒有用到索引的話，要對幾萬條的數據進行掃描，這是非常消耗效能的，使用empno欄位的範圍查詢，過濾性更好（empno從100000開始）！結論：當範圍條件和group by 或者 order by 的欄位出現二選一時，優先觀察條件欄位的過濾數量，如果過濾的數據足夠多，而需要排序的數據並不多時，優先把索引放在範圍欄位上。反之，亦然。
8.3.5 group by
group by 使用索引的原則幾乎跟order by一致，唯一區別是groupby 即使沒有過濾條件用到索引，也可以直接使用索引。

8.3.6 使用覆蓋索引
簡單說就是，select 到 from 之間查詢的列 <=使用的索引列+主鍵
SQL只需要通過索引就可以返回查詢所需要的數據，而不必通過二級索引查到主鍵之後再去查詢數據。

第9章慢查詢日誌
9.1 是什麼
MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。
具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值爲10，意思是執行10秒以上的語句。

由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析
9.2 開啓慢查詢日誌
預設情況下，MySQL數據庫沒有開啓慢查詢日誌，需要我們手動來設定這個參數。
當然，如果不是調優需要的話，一般不建議啓動該參數，因爲開啓慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案

檢視慢查詢日誌是否開啓
mysql> show variables like 「%slow_query_log%」;
開啓慢查詢日誌
mysql> set global slow_query_log =1 ;

因爲slow_query_log是一個全域性變數，因此需要加上 global關鍵字.
如果要永久生效,需要修改/etc/my.cnf組態檔
[mysqld]
slow_query_log =1 #開啓慢查詢
slow_query_log_file=/var/lib/mysql/atguigu-slow.log #慢查詢日誌位置
3) 多久算慢查詢
這個是由參數long_query_time控制，預設情況下long_query_time的值爲10秒，MySQL會將執行時間大於 long_query_time的SQL記錄到日誌中。
mysql> show variables like 「%long_query_time%」;

9.3 檢視慢查詢日誌

跟隨查詢
[root@hadoop205 ~]# tail -f /var/lib/mysql/xxxx.log
通過Mysql方式檢視
mysql> show status like ‘%slow_queries%’;
日誌分析工具mysqldumpslow
 參數介紹
-s 是表示按照何種方式排序
c 存取次數
l 鎖定時間
r 返回記錄
t 查詢時間
al 平均鎖定時間
ar 平均返回記錄數
at 平均查詢時間
-t 返回前面多少條的數據
-g 後邊搭配一個正則表達式，大小寫不敏感的
 工作常用參考
得到返回記錄集最多的10個SQL mysqldumpslow -s r -t 10 日誌檔案
得到存取次數最多的10個SQL mysqldumpslow -s c -t 10 日誌檔案
得到按照時間排序的前10條裏面含有左連線的查詢語句 mysqldumpslow -s t -t 10 -g 「left join」日誌檔案
建議在使用這些命令時結合 | 和more 使用，否則有可能出現爆屏情況 mysqldumpslow -s r -t 10 日誌檔案 | more

第10 章檢視***
10.1 是什麼
將一段查詢sql封裝爲一個虛擬的表, 這個虛擬表只儲存了sql邏輯，不會儲存任何查詢結果。
10.2 幹什麼

封裝複雜sql語句，提高複用性
邏輯放在數據庫上面，更新不需要發佈程式，面對頻繁的需求變更更靈活
10.3 使用
建立檢視
CREATE OR REPLACE VIEW 檢視名 AS SQL語句
使用檢視
select * from 檢視名
第11章主從複製
11.1 主從複製基本原理
master將改變記錄到二進制日誌（binary log）。
這些記錄過程叫做二進制日誌事件，binary log events；
不管使用何種儲存引擎，在server層都可以開啓binlog日誌功能。binlog會記錄所有的邏輯操作，並且是採取追加寫的形式，將寫操作命令，記錄在一個二進制檔案中。因此binlog日誌通常用於恢復數據，或者是主從複製。
slave將master的binary log events拷貝到它的中繼日誌（relay log）；
slave重做中繼日誌中的事件，將改變應用到自己的數據庫中。MySQL複製是非同步的且序列化的
11.2 複製的基本原則
每個slave只有一個master
每個slave只能有一個唯一的伺服器ID
每個master可以有多個salve
11.3 一主(hadoop100)一從(hadoop101)
修改主機hadoop100的 my.cnf組態檔

[mysqld]
server-id=1 #server範例的id
log-bin=/var/lib/mysql/mysql-bin #log-bin檔案儲存位置
binlog_format=ROW # 設定log-bin格式 STATEMENT ROW MIXED

#可選的設定
binlog-ignore-db=mysql # 設定不要複製的數據庫
binlog-do-db=xxx # 設定需要複製的主數據庫名字
注意:修改完後重新啓動MySQL服務
2) 修改從機hadoop101的my.cnf 組態檔
[mysqld]
server-id=2 #server範例的id
relay-log=mysql-relay #中繼日誌
注意:修改完後重新啓動MySQL服務
3) 在主機hadoop100上建立賬戶並授權從機slave
GRANT REPLICATION SLAVE ON . TO ‘slave’@‘從機器數據庫IP’ IDENTIFIED BY ‘密碼’;
mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@‘192.168.202.101’ IDENTIFIED BY ‘123456’;

查詢主機hadoop100 的狀態
mysql> show master status ;

注意:記錄下File 和Position的值，切記不要再操作主機MySQL，防止主機的File和Position
值發生變化
5) 在從機hadoop101上設定需要複製的主機
CHANGE MASTER TO MASTER_HOST=‘主機IP’,
MASTER_USER=‘主機建立好的使用者’,
MASTER_PASSWORD=‘密碼’,
MASTER_LOG_FILE=‘File名1字’,
MASTER_LOG_POS=Position數位;
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.202.100’,
MASTER_USER=‘slave’,
MASTER_PASSWORD=‘123456’,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=154;
6) 在從機hadoop101上啓動主從複製功能
mysql> start slave;
7) 在從機hadoop101檢視主從複製狀態
mysql> show slave status\G;
主要檢視兩個參數:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8) 測試主從
在主機hadoop100上新建庫、新建表、插入數據，檢視從機是否複製
9) 在從機hadoop101停止主從
mysql> stop slave ;
第12章 MySQL HA
12.1 HA
HA是High Available縮寫，是雙機集羣系統簡稱，指高可用性叢集，是保證業務連續性的有效解決方案，一般有兩個或兩個以上的節點，且分爲活動節點及備用節點.
簡單來說就是7*24小時不間斷對外提供服務.
12.2 MySQL 主從複製
MySQL的HA離不開其主從複製的技術。主從複製是指一臺伺服器充當主數據庫伺服器（master），另一臺或多臺伺服器充當從數據庫伺服器（slave），從伺服器（slave）自動向主伺服器（master）同步數據。實現MySQL的HA，需使兩臺伺服器互爲主從關係。
12.3 Keepalived
Keepalived是基於VRRP（Virtual Router Redundancy Protocol，虛擬路由器冗餘協定）協定的一款高可用軟體。Keepailived有一臺主伺服器（master）和多臺備份伺服器（backup），在主伺服器和備份伺服器上面部署相同的服務設定，使用一個虛擬IP地址對外提供服務，當主伺服器出現故障時，虛擬IP地址會自動漂移到備份伺服器。
12.4 互爲主從
在第11章主從複製中的一主一從的基礎之上，搭建互爲主從.
12.4.1 設定主機hadoop101(原從)

修改 /etc/my.cnf
[mysqld]
#開啓binlog
log_bin = /var/lib/mysql/mysql-bin
binlog_format=ROW # 設定log-bin格式 STATEMENT ROW MIXED
server-id=2 #server範例的id
relay-log=mysql-relay #中繼日誌
重新啓動hadoop101的MySQL服務
在主機hadoop101上建立賬戶並授權從機slave
GRANT REPLICATION SLAVE ON . TO ‘slave’@‘從機器數據庫IP’ IDENTIFIED BY ‘密碼’;
mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@‘192.168.202.100’ IDENTIFIED BY ‘123456’;
檢視hadoop101 MySQL的master狀態
mysql> show master status;

注意:記錄下File 和Position的值，切記不要再操作主機MySQL，防止主機的File和Position
值發生變化
12.4.2 設定從機hadoop100(原主)

修改hadoop100上 /etc/my.cnf
[mysqld]
#MySQL伺服器唯一id
server_id = 1

#開啓binlog
log_bin = mysql-bin

#開啓slave中繼日誌
relay-log=mysql-relay
2) 重新啓動 hadoop100上的MySQL服務
3) 在從機hadoop100上設定需要複製的主機
CHANGE MASTER TO MASTER_HOST=‘主機IP’,
MASTER_USER=‘主機建立好的使用者’,
MASTER_PASSWORD=‘密碼’,
MASTER_LOG_FILE=‘File名1字’,
MASTER_LOG_POS=Position數位;
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.202.101’,
MASTER_USER=‘slave’,
MASTER_PASSWORD=‘123456’,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=452
4) 在從機hadoop100上啓動主從
mysql> start slave ;
5) 在從機hadoop100上檢視slave狀態
mysql> show slave status\G;
主要檢視兩個參數:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6) 測試主從
在主機hadoop101上建立庫，建立表，插入數據,檢視從機是否複製
7) 在從機hadoop100上停止主從
mysql> stop slave;
12.5 搭建MySQL HA
12.5.1 安裝 Keepalived

分別在hadoop100 和 hadoop101上安裝 keepalived
[root@hadoop100 ~] yum install -y keepalived
[root@hadoop101 ~] yum install -y keepalived
分別在hadoop100 和 hadoop101上設定 /etc/keepalived/keepalived.conf
 hadoop100
! Configuration File for keepalived
global_defs {
router_id MySQL-HA
}
vrrp_instance VI_1 {
state master #初始狀態
interface eth0 #網絡卡
virtual_router_id 51 #虛擬路由id
priority 100 #優先順序
advert_int 1 #Keepalived心跳間隔
nopreempt #只在高優先順序設定，原master恢復之後不重新上位
authentication {
auth_type PASS #認證相關
auth_pass 1111
}
virtual_ipaddress {
192.168.202.222 #虛擬ip
}
}

#宣告虛擬伺服器
virtual_server 192.168.202.222 3306 {
delay_loop 6
persistence_timeout 30
protocol TCP
#宣告真實伺服器
real_server 192.168.202.100 3306 {
notify_down /var/lib/mysql/killkeepalived.sh #真實服務故障後呼叫指令碼
TCP_CHECK {
connect_timeout 3 #超時時間
nb_get_retry 1 #重試次數
delay_before_retry 1 #重試時間間隔
}
}
}
 hadoop101
! Configuration File for keepalived
global_defs {
router_id MySQL-HA
}
vrrp_instance VI_1 {
state master #初始狀態
interface eth0 #網絡卡
virtual_router_id 51 #虛擬路由id
priority 100 #優先順序
advert_int 1 #Keepalived心跳間隔
nopreempt #只在高優先順序設定，原master恢復之後不重新上位
authentication {
auth_type PASS #認證相關
auth_pass 1111
}
virtual_ipaddress {
192.168.202.222 #虛擬ip
}
}

#宣告虛擬伺服器
virtual_server 192.168.202.222 3306 {
delay_loop 6
persistence_timeout 30
protocol TCP
#宣告真實伺服器
real_server 192.168.202.101 3306 {
notify_down /var/lib/mysql/killkeepalived.sh #真實服務故障後呼叫指令碼
TCP_CHECK {
connect_timeout 3 #超時時間
nb_get_retry 1 #重試次數
delay_before_retry 1 #重試時間間隔
}
}
}
3) 分別在hadoop100和 hadoop101上編輯指令碼檔案 /var/lib/mysql/killkeepalived.sh
#! /bin/bash
service keepalived stop
賦予指令碼執行許可權
chmod 777 /var/lib/mysql/killkeepalived.sh
4) 分別在hadoop100 和 hadoop101上啓動 Keepalived服務
[root@hadoop100 ~] service keepalived start
[root@hadoop101 ~] service keepalived start
5) 測試MySQL HA

03.MySQL高階

第1章MySQL簡介

1.1 什麼是Mysql

1.2 Mysql高手是怎樣練成的

1.3 整體架構圖

第2章MySQL安裝及其他操作***

2.1在Linux上安裝MySQL

2.1.1 MySQL官網

2.1.2 MySQL安裝

2.1.3 Mysql的安裝位置

2.1.4 MySQL服務的自啓動

2.1.5 修改字元集

2.2 MySQL的一些雜項設定

2.2.1 設定大小寫不敏感

0 大小寫敏感

1 大小寫不敏感

2 建立的表和DB依據語句上格式存放，凡是查詢都是轉換爲小寫進行

2.2.2 sql_mode

2.3 MySQL的使用者管理

2.4 MySQL的許可權管理

2.4.1 授予許可權

2.4.2 收回許可權

2.5 檢視sql的執行週期

2.6 查詢快取

2.6.1 檢視查詢快取相關的設定

2.6.2 開啓MySQL的查詢快取

2.6.3 使用查詢快取

2.6.4 查詢不使用快取

2.7 MySQL儲存引擎

2.7.1 檢視儲存引擎

2.7.2 各個儲存引擎介紹

DELIMITER ;

drop PROCEDURE insert_emp;

DELIMITER ;

drop PROCEDURE insert_dept;