MySQL泛泛而談（3W字）
下面對於MySQL進行相關介紹，文檔的內容較為基礎，僅僅設計操作，少量原理，大佬請繞道哦。
廢話少說，開沖！
一、MySQL架構介紹
1-MySQL簡介
概述
MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，目前屬於Oracle公司。
MySQL是一種關聯資料庫管理系統，將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。
MySQL是開源的，所以你不需要支付額外的費用。
MySQL支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。MySQL使用標準的SQL數據語言形式。
MySQL可以允許於多個系統上，並且支援多種語言。這些程式語言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql對PHP有很好的支援，PHP是目前最流行的Web開發語言。
MySQL支援大型資料庫，支援5000萬條記錄的數據倉庫，32位系統表文件最大可支援4GB，64位系統支援最大的表文件為8TB。Mysql是可以訂製的，採用了GPL協議，你可以修改源碼來開發自己的MySQL系統。
2-RPM安裝
①下載地址
MySQL Linux版（CentOS）官網下載地址
②檢查當前系統是否安裝過MySQL
- 查詢命令：rpm -qa|grep -i mysql
- 刪除命令：rpm -e RPM軟體包名(該名字是上一個命令查出來的名字)`
沒裝過：
已裝過
③安裝MySQL服務端（注意提示）
- 安裝命令：rpm -ivh RPM軟體包名
如果報錯，可以說缺包：
rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm –nodeps –force 解決了依賴性關係，強制安裝
yum -y install glibc.i686
yum -y install libncurses.so.5
yum -y install libaio.so.1
yum -y install libz.so.1
④安裝MySQL客戶端
- 安裝命令：rpm -ivh RPM軟體包名
rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm –nodeps –force 解決了依賴性關係，強制安裝
查看MySQL安裝時創建的MySQL用戶和MySQL組
- cat /etc/passwd |grep mysql
- cat /etc/group |grep mysql
或者可以執行 mysqladmin –version 命令。
或者ps -ef | grep mysql
⑤MySQL服務啟動與暫停
- service mysql stop/restart/start
- ps -ef | grep mysql查看
啟動 service mysql start 可以報錯
因為這個文件的問題：
mv /etc/my.cnf /etc/my.cnf.bak
⑥ROOT密碼設置和開機自啟動
mysql服務啟動後，開始連接。
首次連接成功
- 輸入命令mysql 因為初始的root用戶沒有密碼的，所以直接輸入mysql就可以登錄了
- 退出命令exit
按照安裝Server中的提示修改登錄密碼
- /usr/bin/mysqladmin -u root password coderxz
密碼其實對應著mysql中的mysql資料庫中的user表：
也可以具體到IP 192.168.1.%
自啟動mysql服務
⑦修改配置文件位置
- cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
為什麼mysql會自己讀取/etc/my.cnf？
打開mysql.server你就會發現conf=/etc/my.cnf居然是寫死的。
在Linux下查看安裝目錄ps -ef | grep mysql。
⑧修改字符集
查看字符集
- show variables like ‘character%”;
- show variables like 『%char%”;
默認的是客戶端和伺服器都用了latin1，所以會亂碼。
修改字符集和數據存儲路徑
- vim /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysqld]
port = 3306
character_set_server=utf8
character_set_client=utf8
socket = /var/lib/mysql/mysql.sock
collation-server=utf8_general_ci
#(注意linux下mysql安裝完畢是默認：表名區分大小寫；0：區分大小寫；1：不分區大小寫)
lower_case_table_names=1
#(設置最大連接數，默認為151，MySQL伺服器允許的最大的連接數為16384)
max_connections=1000
[mysql]
default-character-set = utf8
重啟資料庫
- service mysql start
- service mysql stop
重新連接後重新create databse並使用新建庫，然後再重新建表試試
3-MySQL配置文件
主要配置文件
二進位日誌log-bin
- 用於主從複製
錯誤日誌log-error
- 默認是關閉的，記錄嚴重的警告和錯誤資訊，每次啟動和關閉的詳細資訊等。
查詢日誌log
- 默認關閉，記錄查詢的sql語句，如果開啟會減低mysql的整體性能，因為記錄日誌也是需要消耗系統資源
數據文件
-
兩系統
- windows
- 輸入
mysql後select @@database;
- 輸入
- linux
- 默認路徑：/var/lib/mysql
- windows
-
frm文件（form）
- 存放表結構
-
myd文件（my data）
- 存放表數據
-
myi文件（my index）
- 存放表索引
如何配置
- Windows – my.ini文件
- Linux – /etc/my.cnf文件
4-MySQL邏輯架構簡介
總體概覽
和其它資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用並發揮良好作用。主要體現在存儲引擎的架構上，插件式的存儲引擎架構將查詢處理和其它的系統任務以及數據的存儲提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎。
1.連接層
最上層是一些客戶端和連接服務，包含本地sock通訊和大多數基於客戶端/服務端工具實現的類似於tcplip的通訊。主要完成一些類似於連接處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基於SSL的安全鏈接。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
2.服務層
第二層架構主要完成大多少的核心服務功能，如SQL介面，並完成快取的查詢，SQL的分析和優化及部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如過程、函數等。在該層，伺服器會解析查詢並創建相應的內部解析樹，並對其完成相應的優化如確定查詢表的順序是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的快取。如果快取空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
3.引擎層
存儲引擎層，存儲引擎真正的負責了MySQL中數據的存儲和提取，伺服器通過API與存儲引擎進行通訊。不同的存儲引擎具有的功能不同這樣我們可以根據自己的實際需要進行選取，後面介紹MyISAM和InnoDB。
4.存儲層
數據存儲層，主要是將數據存儲在運行於裸設備的文件系統之上，並完成與存儲引擎的交互。
- 1.Connectors
指的是不同語言中與SQL的交互
- 2 Management Serveices & Utilities：
系統管理和控制工具
- 3 Connection Pool: 連接池
管理緩衝用戶連接，執行緒處理等需要快取的需求。
負責監聽對 MySQL Server 的各種請求，接收連接請求，轉發所有連接請求到執行緒管理模組。每一個連接上 MySQL Server 的客戶端請求都會被分配（或創建）一個連接執行緒為其單獨服務。而連接執行緒的主要工作就是負責 MySQL Server 與客戶端的通訊，
接受客戶端的命令請求，傳遞 Server 端的結果資訊等。執行緒管理模組則負責管理維護這些連接執行緒。包括執行緒的創建，執行緒的 cache 等。
- 4 SQL Interface: SQL介面。
接受用戶的SQL命令，並且返回用戶需要查詢的結果。比如select from就是調用SQL Interface
- 5 Parser: 解析器。
SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的，是一個很長的腳本。
在 MySQL中我們習慣將所有 Client 端發送給 Server 端的命令都稱為 query ，在 MySQL Server 裡面，連接執行緒接收到客戶端的一個 Query 後，會直接將該 query 傳遞給專門負責將各種 Query 進行分類然後轉發給各個對應的處理模組。
主要功能：
a . 將SQL語句進行語義和語法的分析，分解成數據結構，然後按照不同的操作類型進行分類，然後做出針對性的轉發到後續步驟，以後SQL語句的傳遞和處理就是基於這個結構的。
b. 如果在分解構成中遇到錯誤，那麼就說明這個sql語句是不合理的
- 6 Optimizer: 查詢優化器。
SQL語句在查詢之前會使用查詢優化器對查詢進行優化。就是優化客戶端請求的 query（sql語句） ，根據客戶端請求的 query 語句，和資料庫中的一些統計資訊，在一系列演算法的基礎上進行分析，得出一個最優的策略，告訴後面的程式如何取得這個 query 語句的結果(SQL查詢順序優先順序從高到低：from-where-groupby-having-select-orderby-limit)
他使用的是「選取-投影-聯接」策略進行查詢。
用一個例子就可以理解： select uid,name from user where gender = 1;
這個select 查詢先根據where 語句進行選取，而不是先將表全部查詢出來以後再進行gender過濾
這個select查詢先根據uid和name進行屬性投影，而不是將屬性全部取出以後再進行過濾
將這兩個查詢條件聯接起來生成最終查詢結果
- 7 Cache和Buffer： 查詢快取。
他的主要功能是將客戶端提交 給MySQL 的 Select 類 query 請求的返回結果集 cache 到記憶體中，與該 query 的一個 hash 值 做一個對應。該 Query 所取數據的基表發生任何數據的變化之後， MySQL 會自動使該 query 的Cache 失效。在讀寫比例非常高的應用系統中， Query Cache 對性能的提高是非常顯著的。當然它對記憶體的消耗也是非常大的。
如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取數據。這個快取機制是由一系列小快取組成的。比如錶快取，記錄快取，key快取，許可權快取等
- 8 、存儲引擎介面
存儲引擎介面模組可以說是 MySQL 資料庫中最有特色的一點了。目前各種資料庫產品中，基本上只有 MySQL 可以實現其底層數據存儲引擎的插件式管理。這個模組實際上只是 一個抽象類，但正是因為它成功地將各種數據處理高度抽象化，才成就了今天 MySQL 可插拔存儲引擎的特色。
從圖中還可以看出，MySQL區別於其他資料庫的最重要的特點就是其插件式的表存儲引擎。MySQL插件式的存儲引擎架構提供了一系列標準的管理和服務支援，這些標準與存儲引擎本身無關，可能是每個資料庫系統本身都必需的，如SQL分析器和優化器等，而存儲引擎是底層物理結構的實現，每個存儲引擎開發者都可以按照自己的意願來進行開發。
注意：存儲引擎是基於表的，而不是資料庫。
5-存儲引擎簡介
查看命令：
- 查看mysql以提供什麼存儲引擎
- show engines;
- 查看mysql當前默認的存儲引擎
- show variables like ‘%storage_engine%’;
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.01 sec)
MyISAM和InnoDB
阿里巴巴、淘寶用哪個？
-
Percona為MySQL資料庫伺服器進行了改進，在功能和性能上較MySQL有著很顯著的提升。該版本提升了在高負載情況下的InnoDB的性能、為DBA提供一些非常有用的性能診斷工具；另外有更多的參數和命令來控制伺服器行為。
-
該公司新建了一款存儲引擎叫xtradb完全可以替代innodb,並且在性能和並發上做得更好，
-
阿里巴巴大部分mysql資料庫其實使用的percona的原型加以修改。
-
AliSql+AliRedis
二、索引優化分析
1-SQL性能下降原因
1.查詢語句寫的爛 沒建索引
2.索引失效
- 單值
select * from user where name='';
create index idx_user_name on user(name);
- 複合
select * from user where name='' and email='';
create index idx_user_name on user(name, email);
3.關聯查詢太多join（設計缺陷或不得已的需求）
4.伺服器調優及各個參數設置（緩衝、執行緒數等）
2-SQL執行載入順序
SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type>
JOIN
<right_table>
ON
<join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_number>
機讀 先從from開始讀
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
總結
3-七種JOIN理論
- 針對情況1
select * from A inner join B on A.key=B.key inner表示需要內查詢 AB的公共部分
- 針對情況2
select * from A left join B on A.key=B.key left左表示需要左表的全部 AB的公共部分+A的獨有
- 針對情況3
select * from A right join B on A.key=B.key right右表示需要右表的全部 AB的公共部分+B的獨有
- 針對情況4 A要獨佔
select * from A left join B on A.key=B.key where B.key is NULL left 左表示需要左表的全部 A所有的部分+非AB的公共部分
- 針對情況5 B要獨佔
select * from A right join B on A.key=B.key where B.key is NULL right 右表示需要右表的全部 B所有的部分+非AB的公共部分
①七種JOIN的SQL編寫
準備工作，創建以下表，插入新數據：
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
執行後的結果：
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
#這就是為什麼需要join from優先順序最高，而笛卡爾積查詢的太多了
mysql> select * from tbl_dept,tbl_emp;
+----+----------+--------+----+------+--------+
| id | deptName | locAdd | id | NAME | deptId |
+----+----------+--------+----+------+--------+
| 1 | RD | 11 | 1 | z3 | 1 |
| 2 | HR | 12 | 1 | z3 | 1 |
| 3 | MK | 13 | 1 | z3 | 1 |
| 4 | MIS | 14 | 1 | z3 | 1 |
| 5 | FD | 15 | 1 | z3 | 1 |
| 1 | RD | 11 | 2 | z4 | 1 |
| 2 | HR | 12 | 2 | z4 | 1 |
| 3 | MK | 13 | 2 | z4 | 1 |
| 4 | MIS | 14 | 2 | z4 | 1 |
| 5 | FD | 15 | 2 | z4 | 1 |
| 1 | RD | 11 | 3 | z5 | 1 |
| 2 | HR | 12 | 3 | z5 | 1 |
| 3 | MK | 13 | 3 | z5 | 1 |
| 4 | MIS | 14 | 3 | z5 | 1 |
| 5 | FD | 15 | 3 | z5 | 1 |
| 1 | RD | 11 | 4 | w5 | 2 |
| 2 | HR | 12 | 4 | w5 | 2 |
| 3 | MK | 13 | 4 | w5 | 2 |
| 4 | MIS | 14 | 4 | w5 | 2 |
| 5 | FD | 15 | 4 | w5 | 2 |
| 1 | RD | 11 | 5 | w6 | 2 |
| 2 | HR | 12 | 5 | w6 | 2 |
| 3 | MK | 13 | 5 | w6 | 2 |
| 4 | MIS | 14 | 5 | w6 | 2 |
| 5 | FD | 15 | 5 | w6 | 2 |
| 1 | RD | 11 | 6 | s7 | 3 |
| 2 | HR | 12 | 6 | s7 | 3 |
| 3 | MK | 13 | 6 | s7 | 3 |
| 4 | MIS | 14 | 6 | s7 | 3 |
| 5 | FD | 15 | 6 | s7 | 3 |
| 1 | RD | 11 | 7 | s8 | 4 |
| 2 | HR | 12 | 7 | s8 | 4 |
| 3 | MK | 13 | 7 | s8 | 4 |
| 4 | MIS | 14 | 7 | s8 | 4 |
| 5 | FD | 15 | 7 | s8 | 4 |
| 1 | RD | 11 | 8 | s9 | 51 |
| 2 | HR | 12 | 8 | s9 | 51 |
| 3 | MK | 13 | 8 | s9 | 51 |
| 4 | MIS | 14 | 8 | s9 | 51 |
| 5 | FD | 15 | 8 | s9 | 51 |
+----+----------+--------+----+------+--------+
40 rows in set (0.04 sec)
1.inner join
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)
2.left join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)
3.right join
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)
4.left join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)
5.right join 2
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptid is null;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
6.full join
MySQL不支援full join，不過可以換種方法表示
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)
7.full join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)
4-索引是什麼
MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。可以得到索引的本質：索引是數據結構。
索引的目的在於提高查詢效率，可以類比字典。
如果要查「mysql」這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要逐個逐個尋找，如果我想找到Java開頭的單詞呢？或者Oracle開頭的單詞呢？
你可以簡單理解為「排好序的快速查找數據結構」。
- 排好序
- 快速查找
在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用(指向）數據，這樣就可以在這些數據結構上實現高級查找演算法。這種數據結構，就是索引。下圖就是一種可能的索引方式示例：
左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址。
為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在一定的複雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。
總結來說，為什麼索引查找會快呢？想想使用二叉查找樹去查詢肯定比順序遍歷要快啊！一般來說索引本身也很大，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲的磁碟上。
我們平常所說的索引，如果沒有特別指明，都是指B樹（多路搜索樹，並不一定是二叉的）結構組織的索引。其中聚集索引，次要索引，覆蓋索引，複合索引，前綴索引，唯一索引默認都是使用B+樹索引，統稱索引。當然，除了B+樹這種類型的索引之外，還有哈稀索引(hash index)等。
一個注意點：
一般在企業中，delete了一條數據，僅僅只是把這一條記錄的active修改了false，其中調用service層的delete實際上很多都是update，只能說是邏輯上進行了刪除，但是其底層資料庫的記錄依舊保留著。其原因之一：一個是為了數據分析；另外一個就是索引（數據被刪除了索引可能不準確了，其位置不再固定了）。當數據頻繁的增加，修改後，這個二叉查找樹就不準確了。（乾脆把索引重建一遍）。
①索引優劣勢
- 優勢
類似大學圖書館建書目索引，提高數據檢索的效率，降低資料庫的IO成本。
通過索引列對數據進行排序，降低數據排序的成本，降低了CPU的消耗。
- 劣勢
實際上索引也是一張表，該表保存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的（占空間）
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊。
索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢。
- 總結
索引，空間換取時間。
②索引分類和建索引命令語句
MySQL索引分類：
- 單值索引：即一個索引只包含單個列，一個表可以有多個單列索引。（建議一張表的索引不要超過5個 優先考慮複合索引）
- 唯一索引：索引列的值必須唯一，但允許有空值。
- 複合索引：即一個索引包含多個列。
- 基本語法：
- 創建
- CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
- ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
- 刪除
- DROP INDEX [indexName] ON mytable;
- 查看
- SHOW INDEX FROM tableName
- 使用alter命令 – 有四種方式來添加數據表的索引
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);：該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
- ALTER TABLE tbl name ADD UNIQUE index_name (column_list);：這條語句創建索引的值必須是唯一的(除了NULL外，NULL可能會出現多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list);：添加普通索引，索引值可出現多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)：該語句指定了索引為FULLTEXT，用於全文索引。
- 創建
③索引結構與檢索原理
MySQL索引結構
- BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
BTree索引檢索原理
初始化介紹
一顆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，顯然成本非常非常高。
④哪些情況適合建索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的欄位應該創建索引
- 查詢中與其它表關聯的欄位，外鍵關係建立索引
- 頻繁更新的欄位不適合創建索引，因為每次更新不單單是更新了記錄還會更新索引
- Where條件里用不到的欄位不創建索引
- 單鍵/組合索引的選擇問題，who?(在高並發下傾向創建組合索引)
- 查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序速度(索引就是檢索+排序)
- 查詢中統計或者分組欄位
⑤哪些情況不適合建索引
- 表記錄太少
- 經常增刪改的表（不建，雖然提高了查詢速度，但是降低了更新的效率，不但要更新欄位，還得更新對應的索引資訊）
- 數據重複且分布平均的表欄位，因此應該只為最經常查詢和最經常排序的數據列建立索引。注意，如果某列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
假如一個表有10萬行記錄，有一個欄位A只有T和F兩種值，且每個值的分布概率天約為50%，那麼對這種表A欄位建索引一般不會提高資料庫的查詢速度。
索引的選擇性是指索引列中不同值的數目與表中記錄數的比。如果一個表中有2000條記錄，表索引列有1980個不同的值，那麼這個索引的選擇性就是1980/2000=0.99。一個索引的選擇性越接近於1，這個索引的效率就越高。
5-性能分析前提知識
MySQL Query Optimizer
Mysql中有專門負責優化SELECT語句的優化器模組，主要功能:通過計算分析系統中收集到的統計資訊，為客戶端請求的Query提供他認為最優的執行計劃（他認為最優的數據檢索方式，但不見得是DBA認為是最優的,這部分最耗費時間）
當客戶端向MySQL請求一條Query，命令解析器模組完成請求分類，區別出是SELECT並轉發給MySQL Query Optimizer時，MySQL Query Optimizer首先會對整條Query進行優化，處理掉一些常量表達式的預算直接換算成常量值。並對Query中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然後分析Query 中的 Hint資訊(如果有），看顯示Hint資訊是否可以完全確定該Query的執行計劃。如果沒有Hint 或Hint資訊還不足以完全確定執行計劃，則會讀取所涉及對象的統計資訊，根據Query進行寫相應的計算分析，然後再得出最後的執行計劃。
MySQL常見瓶頸
- CPU：CPU在飽和的時候一般發生在數據裝入記憶體或從磁碟上讀取數據時候
- IO：磁碟I/O瓶頸發生在裝入數據遠大於記憶體容量的時候
- 伺服器硬體的性能瓶頸：top，free，iostat和vmstat來查看系統的性能狀態
①explain使用簡介
Explain是什麼：使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。
能幹嘛
- 表的讀取順序
- 數據讀取操作的操作類型
- 哪些索引可以使用
- 哪些索引被實際使用
- 表之間的引用
- 每張表有多少行被優化器查詢
怎麼玩
- explain + SQL語句
- 執行計劃包含的資訊
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.01 sec)
mysql> explain select * from tbl_emp;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | tbl_emp | ALL | NULL | NULL | NULL | NULL | 8 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)
②explain之id介紹
select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序
三種情況：
-
id相同，執行順序由上至下
-
id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
-
id相同不同，同時存在
id相同，執行順序由上至下
id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
id相同不同，同時存在
小結
- id如果相同，可以認為是一組，從上往下順序執行；
- 在所有組中，id值越大，優先順序越高，越先執行，衍生=DERIVED（就是那張虛表）drived2中的那個2表示是id為2的那個表延申出來的虛表
- 永遠是id越大越先查詢
③explain之select_type和table介紹
select_type：查詢的類型，主要是用於區別普通查詢、聯合查詢、子查詢等的複雜查詢。
select_type有哪些？
- SIMPLE – 簡單的select查詢,查詢中不包含子查詢或者UNION。
- PRIMARY – 查詢中若包含任何複雜的子部分，最外層查詢則被標記為。（相當於雞蛋殼，最後被載入的）
- SUBQUERY – 在SELECT或WHERE列表中包含了子查詢。(雞蛋黃，先被載入的)
- DERIUED – 在FROM列表中包含的子查詢被標記為DERIVED（衍生）MySQL會遞歸執行這些子查詢，把結果放在臨時表裡。
- UNION – 若第二個SELECT出現在UNION之後，則被標記為UNION；若UNION包含在FROM子句的子查詢中外層SELECT將被標記為：DERIVED。
- UNION RESULT – 從UNION表獲取結果的SELECT。
table：顯示這一行的數據是關於哪張表的。
④explain之type介紹
訪問類型排列
type顯示的是訪問類型，是較為重要的一個指標，結果值從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL
從最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref。
詳細說明
- system
表只有一行記錄（等於系統表），這是const類型的特列，平時不會出現，這個也可以忽略不計。（單表一條記錄）
- const
表示通過索引一次就找到了，const用於比較primary key或者unique索引。因為只匹配一行數據，所以很快如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量。下面的system是因為符合查詢中只會返回一條記錄作為臨時表。
- eq_ref
唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描。
- ref
非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬於查找和掃描的混合體。
- range
只檢索給定範圍的行,使用一個索引來選擇行。key列顯示使用了哪個索引一般就是在你的where語句中出現了between、<、>、in等的查詢。這種範圍掃描索引掃描比全表掃描要好，因為它只需要開始於索引的某一點，而結束語另一點，不用掃描全部索引。
- index
Full Index Scan，index與ALL區別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數據文件小（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）。
- ALL
Full Table Scan，將遍歷全表以找到匹配的行。
備註：一般來說，得保證查詢至少達到range級別，最好能達到ref。
⑤explain之possible_keys和key介紹
possible_keys
顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的欄位火若存在索引，則該索引將被列出，但不一定被查詢實際使用。
key
是否使用到了索引；在多個索引存在的情況下，MySQL究竟使用到了哪一個索引。
實際使用的索引。如果為NULL，則沒有使用索引
查詢中若使用了覆蓋索引，則索引和查詢的select欄位重疊
⑥explain之key_len介紹
表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
key_len顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的
⑦explain之ref介紹
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用於查找索引列上的值。
由key_len可知t1表的idx_col1_col2索引被充分使用，col1匹配t2表的col1，col2匹配了一個常量，即 『ac』。
shared庫的t2表的col1；
查詢中與其它表關聯的欄位，外鍵關係建立索引。
⑧explain之rows介紹
根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數。（每張表有多少行被優化器查詢過）
⑨explain之Extra介紹
包含不適合在其他列中顯示但十分重要的額外資訊。
①Using filesort 九死一生
說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為”文件排序” \G表示使用key-value的顯示方式進行輸出
你建立的索引我只是部分使用到了，查詢的時候我只是部分使用到了索引，排序我並沒有用到。
對比下面兩者，其中上面只用到了col1和col3，中間col2沒了；下者使用到了col1、col2、col3；
把建索引比喻成修好的樓梯，col1、col2、col3修了三條路；
上面的部分相當於自己又進行了一次排序的步驟，內部的折騰多了一次；而下面的就是一步步來的，顯然下面的效率要高於上面的部分。
②Using temporary 必死
使了用臨時表保存中間結果，MysQL在對查詢結果排序時使用臨時表。常見於排序order by和分組查詢group by。
order by和分組查詢group by經常是拖慢SQL的元兇。
上面的部分：建立的複合索引(col1,col2)，但是只用到了col2，沒有一樓而直接上二樓，所以需要臨時表和文件排序進行處理；
下面的部分：建立的是col1、col2，使用的也是col1、col2;
order by和分組查詢group by最好和建立的索引順序一致；
③Using index
表示相應的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數據行，效率不錯！
如果同時出現using where，表明索引被用來執行索引鍵值的查找；
如果沒有同時出現using where，表明索引用來讀取數據而非執行查找動作。
如果同時出現using where，表明索引被用來執行索引鍵值的查找；
覆蓋索引（Covering Index）,一說為索引覆蓋。
1、建是索引是複合索引（1，2，3）；
2、沒有寫select * ；剛好查的就是 select 1 2 3或者select 1 2;這就是覆蓋索引；
理解方式一：就是select的數據列只用從索引中就能夠取得，不必讀取數據行，MySQL可以利用索引返回select列表中的欄位，而不必根據索引再次讀取數據文件,換句話說查詢列要被所建的索引覆蓋。
理解方式二：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的數據，因此它不必讀取整個行。畢竟索引葉子節點存儲了它們索引的數據；當能通過讀取索引就可以得到想要的數據，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結果的數據就叫做覆蓋索引。
注意：
如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不可select*，因為
如果將所有欄位一起做索引會導致索引文件過大，查詢性能下降。
What is a Covering Index？
A covering index is an index that contains all of, and possibly more, the columns you need for your query.
For instance, this:
SELECT *
FROM tablename
WHERE criteriawill typically use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.
However, if the index contained the columns column1, column2 and column3, then this sql:
SELECT column1, column2
FROM tablename
WHERE criteriaand, provided that particular index could be used to speed up the resolution of which rows to retrieve, the index already contains the values of the columns you』re interested in, so it won』t have to go to the table to retrieve the rows, but can produce the results directly from the index.
This can also be used if you see that a typical query uses 1-2 columns to resolve which rows, and then typically adds another 1-2 columns, it could be beneficial to append those extra columns (if they』re the same all over) to the index, so that the query processor can get everything from the index itself.
④Using where
表明使用了where過濾。
⑤Using join buffer
使用了連接快取。
⑥impossible where
where子句的值總是false，不能用來獲取任何元組。
⑦select tables optimized away(用的不多)
在沒有GROUPBY子句的情況下，基於索引優化MIN/MAX操作，或者對於MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。
⑧distinct（用的不多）
優化distinct操作，在找到第一匹配的元組後即停止找同樣值的動作。
⑩explain之熱身Case
第一行（執行順序4）：id列為1，表示是union里的第一個select，select_type列的primary表示該查詢為外層查詢，table列被標記為，表示查詢結果來自一個衍生表，其中derived3中3代表該查詢衍生自第三個select查詢，即id為3的select。【select d1.name… 】
第二行（執行順序2）：id為3，是整個查詢中第三個select的一部分。因查詢包含在from中，所以為derived。【select id,namefrom t1 where other_column=』』】
第三行（執行順序3）：select列表中的子查詢select_type為subquery，為整個查詢中的第二個select。【select id from t3】
第四行（執行順序1）：select_type為union，說明第四個select是union里的第二個select，最先執行【select name,id from t2】
第五行（執行順序5）：代表從union的臨時表中讀取行的階段，table列的<union1,4>表示用第一個和第四個select的結果進行union操作。【兩個結果union操作】
6-索引優化案例
①索引單表優化案例
建表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 VARCHAR(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');
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
- 查詢category_id為1且comments 大於1的情況下，views最多的article_id (只有一條)。
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
結論：很顯然，type是ALL，即最壞的情況。Extra里還出現了Using filesort，也是最壞的情況。優化是必須的。
開始優化
新建索引+刪除索引
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
或者用下面sql創建索引：
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
創建後的效果
Extra里還是出現了Using filesort，創建這索引作用不大。
如果comments > 1換成comments = 1，可以讓Using filesort消失，但不符題目要求。
既然創建這索引作用不大，刪了它吧。
mysql> DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
緣由
type變成了range，這是可以忍受的。但是extra里使用Using filesort仍是無法接受的。
但是我們已經建立了索引，為啥沒用呢？
這是因為按照BTree索引的工作原理：
- 先排序category_id
- 如果遇到相同的category_id則再排序comments
- 如果遇到相同的comments 則再排序views。
當comments欄位在聯合索引里處於中間位置時，因comments > 1條件是一個範圍值(所謂range)，範圍以後的索引會失效，MySQL無法利用索引再對後面的views部分進行檢索，即range類型查詢欄位後面的索引無效。
改進
跟上次創建索引相比，這次不為comments欄位創建索引。
mysql> create index idx_article_cv on article(category_id, views);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次explain
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
可以看到，type變為了ref，Extra中的Using filesort也消失了，結果非常理想。
總結：
盡量不要對range類型（一般就是在你的where語句中出現了between、<、>、in等的查詢）的查詢欄位建索引！
②索引兩表優化案例
新建SQL
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 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)));
創建後的結果：
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
開始explain分析 兩個表有主外鍵，索引應該加在哪張表上？
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
type都是all，需要優化,且查詢的記錄數量為 21+20！
為book.card創建索引
mysql> ALTER TABLE `book` ADD INDEX Y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次explain分析
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
可以看到第二行的type變為了ref，rows也變少了，優化比較明顯。這是由左連接特性決定的。LEFT JOIN的特性就是左表全都有；條件用於確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關鍵點，一定需要在右表建立索引。
刪除為book.card創建索引
mysql> drop index y on book;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
為class.card創建索引
mysql> ALTER TABLE `class` ADD INDEX Y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次explain分析
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
可見右邊是我們的關鍵點，要想優化需要在右表建立索引。
然後我們換用右連接RIGHT JOIN查詢
mysql> EXPLAIN SELECT * FROM class right JOIN book ON class.card = book.card;
換成左邊是我們的關鍵點，要想優化需要在左表建立索引。
小結
索引兩表優化，左連接右表建索引，右連接左表建索引。
③索引三表優化案例
新建SQL
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)));
建後效果
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
復用到上一節book，class兩表，移除它們原有的索引。
mysql> show index from class;
mysql> drop index y on class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from book;
為phone.card和book.card創建新的索引。
mysql> alter table `phone` add index z(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table `book` add index y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
explain三表連接 三表至少兩次關聯，索引應該建在哪張表上？
mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
後2行的 type 都是ref且總 rows優化很好，效果不錯。因此索引最好設置在需要經常查詢的欄位中。
結論
Join語句的優化
儘可能減少Join語句中的NestedLoop的循環總次數(不要join過多或者嵌套)：「永遠用小結果集驅動大的結果集」。
優先優化NestedLoop的內層循環，保證Join語句中被驅動表上Join條件欄位已經被索引。
當無法保證被驅動表的Join條件欄位被索引且記憶體資源充足的前提下，不要太吝惜JoinBuffer（在my.cnf文件中）的設置。
7-索引失效
索引失效的各種原因：（應該避免）
- 全值匹配我最愛
- 最佳左前綴法則 – 如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始並且不跳過複合索引中間列。
- 不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描。
- 存儲引擎不能使用索引中範圍條件右邊的列。
- 盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select *。
- mysql在使用不等於（!=或者<>）的時候無法使用索引會導致全表掃描。
- is null, is not null 也無法使用索引。
- like以通配符開頭（』%abc…』），mysql索引失效會變成全表掃描的操作。
- 字元串不加單引號索引失效。
- 少用or，用它來連接時會索引失效。
①全值匹配我最愛
新建SQL
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年齡',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'職位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職時間'
)CHARSET utf8 COMMENT'員工記錄表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
三種情況：
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
注意下面的explain 我們建立索引的順序是：(name、age、pos)
mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
mysql> EXPLAIN SELECT * FROM staffs WHERE pos='dev';
上面兩個索引失效了。如果查詢欄位的順序與索引的建立的順序不一致，需要滿足最佳左前綴法則。
②最佳左前綴法則
- 如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列。
帶頭大哥不能死！ 中間兄弟不能斷！
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev';
ref只有一個const，說明上述其實只是使用到了部分索引，而且只是用到了第一個索引，name。
②索引列上做額外操作
不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4)='July';
③限定複合索引某列的範圍
存儲引擎不能使用索引中範圍條件右邊的列（我理解為限定複合索引某欄位的範圍會時索引失效，也就是>，<，between…and…謹慎用在複合索引某欄位）。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
由age=25變成age>25後，type從ref變成range。
④select *
盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select *
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='manager';
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
我們最好按需索取，少用select *
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND pos='dev';
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
mysql> EXPLAIN SELECT name FROM staffs WHERE NAME='July' AND age=25;
⑤!=或者<>
mysql在使用不等於（!=或者<>）的時候無法使用索引會導致全表掃描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
⑥is null或者is not null
is null, is not null 也無法使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
Extra列印為Impossible WHERE，是因為我們在創建staffs表，設置name欄位的屬性為not null。
下面額外演示Extra為Impossible WHERE情況。
mysql> EXPLAIN SELECT * FROM staffs WHERE 1=1;
mysql> EXPLAIN SELECT * FROM staffs WHERE 1!=1;
⑦like以通配符%開頭字元串
like以通配符%開頭（』%abc…』），mysql索引失效會變成全表掃描的操作。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
問題：解決like ‘%字元串%’時索引不被使用的方法？
- 可以使用主鍵索引
- 推薦使用覆蓋索引，查詢欄位必須是建立覆蓋索引欄位
- 當覆蓋索引指向的欄位是varchar(380)及380以上的欄位時，覆蓋索引會失效！
新建SQL
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'[email protected]');
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | [email protected] |
| 2 | 2bb2 | 23 | [email protected] |
| 3 | 3cc3 | 24 | [email protected] |
| 4 | 4dd4 | 26 | [email protected] |
+----+------+------+-----------+
4 rows in set (0.00 sec)
創建索引前，先看看以下explain：
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
沒有創建索引，都全表查找。
現在創建索引
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
再執行上述一系列explain
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
看得出，用上索引（覆蓋索引）
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
看得出，都用上索引（覆蓋索引）
mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
看得出，都用上索引（覆蓋索引）
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
看得出，都沒用上索引了，有email欄位再，只能全表搜索。
2）覆蓋索引（Covering Index）
一說為索引覆蓋。 類比鍋和鍋蓋，查詢的欄位要小於等於創建的索引 (注意：主鍵id天生自帶索引，故而主鍵欄位對與覆蓋索引無影響)。
理解方式一：就是select的數據列只用從索引中就能夠取得，不必讀取數據行， MySQL可以利用索引返回select列表中的欄位，而不必根據索引再次讀取數據文件，換句話說查詢列要被所建的索引覆蓋。
理解方式二：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的數據，因此它不必讀取整個行。畢竟索引葉子節點存儲了它們索引的數據；當能通過讀取索引就可以得到想要的數據，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結果的數據就叫做覆蓋索引。
注意：
如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不可select*，因為如果將所有欄位一起做索引會導致索引文件過大，查詢性能下降。
小結
解決like ‘%字元串%’時索引不被使用的方法？複合索引，然後覆蓋索引。
⑧數目字元串不加單引號
數目字元串不加單引號索引失效。 varchar類型一定要加單引號！！！
MySQL底層發送了隱式的類型轉換，影響效率。
mysql> SELECT * FROM staffs WHERE NAME=2000;
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM staffs WHERE NAME='2000';
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM staffs WHERE NAME=2000;
mysql> explain SELECT * FROM staffs WHERE NAME='2000';
⑨用關鍵字OR
少用or，用它來連接時會索引失效。
mysql> explain SELECT * FROM staffs WHERE NAME='July' or name='z3';
mysql> SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2021-04-03 14:03:18 |
| 2 | July | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+---------+---------------------+
2 rows in set (0.00 sec)
⑩小總結where之後order by之前
小總結
假設 創建的索引：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 （沒有大哥a） |
| 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 |
優化總結口訣
全值匹配我最愛， 最左前綴要遵守；
帶頭大哥不能死， 中間兄弟不能斷；
索引列上少計算， 範圍之後全失效；
LIKE 百分寫最右， 覆蓋索引不寫 *；
不等空值還有 OR， 索引影響要注意；
VAR 引號不可丟， SQL 優化有訣竅。
8-索引面試題分析
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');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
mysql> show index from test03;
問題：我們創建了複合索引idx_test03_c1234 (c1、c2、c3、c4) ，根據以下SQL分析下索引使用情況？
①案例1
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';
mysql> explain select * from test03 where c1='a1';
mysql> explain select * from test03 where c1='a1' and c2='a2';
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
#換一下條件順序
mysql> explain select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1';
為什麼我建的是1、2、3、4，但是我查詢的是4、3、2、1，依舊可以使用到索引呢？MySQL中的第二層中的Optimizer會把MySQL中的命令自動的進行調整和優化。。
②案例2
限定範圍
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
③案例3
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
c3作用在排序而不是查找
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3;
上面兩個explain的相同。上述兩條說明：和c4=’a4’這個沒啥關係 直接在三樓（c3）排序就行，不找了。
order by c3換成order by c4
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c4;
用到Using filesort。中間的兄弟斷了，但是MySQL要交出結果，只好整一次內排序 Using filesort
④ order by的欄位順序與索引欄位的順序
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
只用c1一個欄位索引，但是c2、c3用於排序，無filesort。
將order by c2,c3換成order by c3,c2。
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
出現了filesort，我們建的索引是1234，它沒有按照順序來，3，2顛倒了。
⑤order by中的特例
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
用c1、c2兩個欄位索引，但是c2、c3用於排序，無filesort 和c5=’a5’沒啥關係
上述第二條SQL的order by違反了創建索引的順序，那麼為什麼沒有產生using filesort呢？
原因是：
一般情況下：只要order by欄位順序與創建索引的順序不一致就會產生using filesort；
特例是：排序的欄位已經是一個常量了，上述情況就是 c3和c2=’a2’兩個欄位排序。
對比說明：
mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
跳過c2，就用c3，就出現Using filesort。
⑥group by
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
group by表面上是分組，但是分組之前必排序，所以group by優化原則幾乎和order by相同
定值、範圍還是排序，一般order by是給個範圍
group by基本上都需要進行排序，會有臨時表產生(建的索引搞不定，MySQL自己內部再次排序)
一般性建議
- 對於單鍵索引，盡量選擇針對當前query過濾性更好的索引。
- 在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠左越好。
- 在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where字句中更多欄位的索引。
- 儘可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的。
9-索引優化答疑補充和總結口訣
假設index(a, b, c)
- Y，使用到a，b，c
#百分號在右邊,相當於說左邊有常量了，實際上用到了c2索引mysql> explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';
- Y，使用到a
#根據百分號在左邊 c2直接用不了，導致中間兄弟斷了，所以c3也用不了mysql> explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';
- Y，使用到a
#理由同上mysql> explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';
- Y，使用到a，b，c
mysql> explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';
三、查詢截取分析
1-查詢優化
①小表驅動大表
通常SQL調優過程：（面試問：平時是咋樣進行SQL優化的？）
- 觀察，至少跑1天，看看生產的慢SQL情況。
- 開啟慢查詢日誌，設置闕值，比如超過5秒鐘的就是慢SQL，並將它抓取出來。（找出慢SQL）
- explain + 慢SQL分析。
- show profile。
- 運維經理 or DBA，進行SQL資料庫伺服器的參數調優。
總結：
- 慢查詢的開啟並捕獲
- explain + 慢SQL分析
- show profile查詢SQL在Mysql伺服器裡面的執行細節和生命周期情況
- SQL資料庫伺服器的參數調優。
優化原則：小表驅動大表，即小的數據集驅動大的數據集。
for(int i=0;i<5;i++){
for(int j=0;j<1000;j++){
...
}
}
====================
for(int i=0;i<1000;i++){
for(int j=0;j<5;j++){
...
}
}
類似上述兩個for循環，盡量選擇上面的那種。資料庫最耗時的是多表之間關係的建立。第一種：相當於兩張表建立了5次連接；第二種：相當於兩張表建立了1000次連接。也就是說被嵌套在裡面的應該是小表！（因為是裡面嵌套的先執行，相當於被嵌套在裡面的SQL是外層for循環）
RBO原理
當B表的數據集小於A表的數據集時，用in優於exists。
select * from A where id in (select id from B)
等價於:
for select id from B
for select * from A where A.id = B.id
當B表的數據集大於A表的數據集時，用exists優於in。
select * from A where exists (select 1 from B where B.id = A.id)
等價於：
先：for select * from A
後：for select * from B where B.id = A.id
注意：A表與B表的ID欄位應建立索引。
EXISTS關鍵字
SELECT ...FROM table WHERE EXISTS (subquery)
該語法可以理解為：先將主查詢的數據，放到子查詢中做條件驗證，根據驗證結果（TRUE或FALSE）來決定主查詢的數據結果是否得以保留。
提示
- EXSTS(subquey)只返回TRUE或FALSE，因此子查詢中的SELECT * 也可以是 SELECT 1 或select 『X』，官方說法是實際執行時會忽略SELECT清單，因此沒有區別。
- EXISTS子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。
- EXISTS子查詢往往也可以用條件表達式，其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析
②in和exists
示例表：
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.02 sec)
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.01 sec)
in和exists用法
mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept d);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id = e.deptId);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
#'X'就是表示一個常量，是一個常量就行了，反正返回的就是true/false
mysql> select * from tbl_emp e where exists (select 'X' from tbl_dept d where d.id = e.deptId);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
③為排序使用索引OrderBy優化
ORDER BY子句，盡量使用Index方式排序，避免使用FileSort方式排序。
新建SQL
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);
查看驗證：
mysql> select * from tblA;
+------+---------------------+
| age | birth |
+------+---------------------+
| 22 | 2021-04-04 19:31:45 |
| 23 | 2021-04-04 19:31:45 |
| 24 | 2021-04-04 19:31:45 |
+------+---------------------+
3 rows in set (0.00 sec)
mysql> show index from tblA;
分析SQL 重點在於會不會產生UsingFileSort：
mysql> EXPLAIN SELECT * FROM tblA where age > 20 order by age;
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
mysql> EXPLAIN SELECT * FROM tblA order by birth;
mysql> EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' order by birth;
mysql> EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' order by age;
mysql> EXPLAIN SELECT * FROM tblA order by age, birth desc;
最後一種情況：儘管order by的順序和創建索引的順序保持一致，但是由於order by默認的是升序，而DESC是降序，MySQL建立索引的順序默認的就是升序，現在需要降序，用不上了，那麼MySQL只有產生一次內部排序了。
MySQL支援二種方式的排序，FileSort和lIndex，Index效率高，它指MySQL掃描索引本身完成排序。FileSort方式效率較低。
結論：ORDER BY滿足兩情況，會使用Index方式排序：
- ORDER BY語句使用索引最左前列。
- 使用where子句與Order BY子句條件列組合滿足索引最左前列。
④MySQL的Filesort
如果Order by查詢的列不在索引列上，MySQL的Filesort有兩種演算法：
1）雙路排序
MySQL4.1之前是使用雙路排序，字面意思就是兩次掃描磁碟，最終得到數據，讀取行指針和OrderBy列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀對應的數據輸出。
從磁碟取排序欄位，在buffer進行排序，再從磁碟取其他欄位。
取一批數據，要對磁碟進行了兩次掃描，眾所周知，I\O是很耗時的，所以在mysql4.1之後，出現了第二種改進的演算法，就是單路排序。
2）單路排序
從磁碟讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然後掃描排序壓的列表進行輸出，它的效率更快一些，避免了第二次讀取數據。並且把隨機IO變成了順序IO,但是它會使用更多的空間，因為它把每一行都保存在記憶體中了。
結論及引申出的問題
-
由於單路是後出的，總體而言好過雙路
-
但是用單路有問題
在sort_buffer(在my.cnf中有配置緩衝區大小)中，方法B比方法A要多佔用很多空間，因為方法B是把所有欄位都取出,所以有可能取出的數據的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的數據，進行排序（創建tmp文件，多路合併)，排完再取取
sort_buffer容量大小，再排……從而多次I/O。（類比於緩衝區大小不夠，那只有取多次了）
本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失。
⑤優化策略
- 增大sort_buffer_size參數的設置
- 增大max_length_for_sort_data參數的設置
為什麼設置sort_buffer_size、max_length_for_sort_data參數能優化排序？
提高Order By的速度：
-
Order by時select * 是一個大忌只Query需要的欄位，這點非常重要（很容易把緩衝區佔滿）。在這裡的影響是;
-
當Query的欄位大小總和小於max_length_for_sort_data而且排序欄位不是TEXT|BLOB類型時，會用改進後的演算法——單路排序，否則用老演算法——多路排序。
-
兩種演算法的數據都有可能超出sort_buffer的容量，超出之後，會創建臨時tmp文件進行合併排序，導致多次IO，但是用單路排序演算法的風險會更大一些，所以要提高sort_buffer__size。
-
-
嘗試提高sort_buffer_size，不管用哪種演算法，提高這個參數都會提高效率，當然，要根據系統的能力去提高，因為這個參數是針對每個進程的。
-
嘗試提高max_length_for_sort_data，提高這個參數，會增加用改進演算法的概率。但是如果設的太高，數據總容量超出sort_buffer_size的概率就增大，明顯癥狀是高的磁碟I/O活動和低的處理器使用率。
⑥索引和Order by小結
為排序使用索引
- MySql兩種排序方式∶文件排序 或 掃描有序索引排序
- MySql能為 排序 與 查詢 使用相同的索引
創建複合索引 a_b_c (a, b, c)
order by能使用索引最左前綴
- ORDER BY a
- ORDER BY a, b
- ORDER BY a, b, c
- ORDER BY a DESC, b DESC, c DESC (都是升序或都是降序)
如果WHERE使用素引的最左前綴定義為常量，則order by能使用索引（不產生FileSort）
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b > const ORDER BY b, c 這裡雖然是>但是依舊是一個常量
不能使用索引進行排序
- ORDER BY a ASC, b DESC, c DESC //排序不—致
- WHERE g = const ORDER BY b, c //產丟失a索引
- WHERE a = const ORDER BY c //中間兄弟斷了 產丟失b索引
- WHERE a = const ORDER BY a, d //d不是素引的一部分
- WHERE a in (…) ORDER BY b, c //對於排序來說,多個相等條件也是範圍查詢 不是常量
2-GroupBy優化
GroupBy優化
- group by實質是先排序後進行分組，遵照索引建的最佳左前綴。
- 當無法使用索引列，增大max_length_for_sort_data參數的設置 + 增大sort_buffer_size參數的設置。
- where高於having，能寫在where限定的條件就不要去having限定了。
3-慢查詢日誌
- MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。
- 具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的默認值為10，意思是運行10秒以上的語句。
- 由他來查看哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析。
①如何操作
默認情況下，MySQL資料庫沒有開啟慢查詢日速，需要我們手動來設置這個參數。
當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。慢查詢日誌支援將日誌記錄寫入文件。
1）查看是否開啟及如何開啟
- 默認 – SHOW VARIABLES LIKE ‘%slow_query_log%’;
- 一次性開啟 – set global slow_query_log=1;，只對當前資料庫生效，如果MySQL重啟後則會失效。只針對本次生效。
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------+
| Variable_name | Value |
+---------------------+--------------------------+
| slow_query_log | OFF |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------+
| Variable_name | Value |
+---------------------+--------------------------+
| slow_query_log | ON |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
如果要永久生效，就必須修改配置文件my.cnf(其它系統變數也是如此)
修改my.cnf文件，[mysqld]下增加或修改參數slow_query_log和slow_query_log_file後，然後重啟MySQL伺服器。也即將如下兩行配置進my.cnf文件
slow_query_log =1slow_query_log_file=/var/lib/mycoderxz-slow.log
關於慢查詢的參數slow_query_log_file，它指定慢查詢日誌文件的存放路徑，系統默認會給一個預設的文件host_name-slow.log（如果沒有指定參數slow_query_log_file的話）
2）開啟了慢查詢日誌
什麼樣的SQL才會記錄到慢查詢日誌裡面呢？
這個是由參數long_query_time控制，默認情況下long_query_time的值為10秒，命令：SHOW VARIABLES LIKE ‘long_query_time%’;
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
可以使用命令修改，也可以在my.cnf參數裡面修改。
假如運行時間正好等於long_query_time的情況，並不會被記錄下來。也就是說，在mysql源碼里是判斷大於long_query_time，而非大於等於。
3）設置慢SQL閾值時間：
set global long_query_time=3;
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
為什麼設置後看不出變化？
需要重新連接或新開一個會話才能看到修改值。
4）記錄慢SQL並後續分析
假設我們成功設置慢SQL閾值時間為3秒（set global long_query_time=3;）。
模擬超時SQL：
mysql> SELECT sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)
日誌記錄：
查詢當前系統中有多少條慢查詢記錄
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
在配置文件中設置慢SQL閾值時間 永久生效！
#[mysqld]下配置:
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE;
②日誌分析工具mysqldumpslow
在生產環境中，如果要手工分析日誌，查找、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具mysqldumpslow。
查看mysqldumpslow的幫助資訊，mysqldumpslow –help。
- s：是表示按照何種方式排序
- c：訪問次數
- l：鎖定時間
- r：返回記錄
- t：查詢時間
- al：平均鎖定時間
- ar：平均返回記錄數
- at：平均查詢時間
- t：即為返回前面多少條的數據
- g：後邊搭配一個正則匹配模式，大小寫不敏感的
工作常用參考
- 得到返回記錄集最多的10個SQL
- mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
- 得到訪問次數最多的10個SQL
- mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
- 得到按照時間排序的前10條裡面含有左連接的查詢語句
- mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log
- 另外建議在使用這些命令時結合│和more 使用，否則有可能出現爆屏情況 （也可以結合管道把結果寫到一個文件裡面）
- mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more
3-批量插入數據腳本(存儲過程)
①創建SQL
create database bigData;
use bigData;
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20)NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;
②設置參數log_bin_trust_function_creators
創建函數，假如報錯：This function has none of DETERMINISTIC…
由於開啟過慢查詢日誌，因為我們開啟了bin-log，我們就必須為我們的function指定一個參數。
show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.02 sec)
set global log_bin_trust_function_creators=1;
這樣添加了參數以後，如果mysqld重啟，上述參數又會消失，永久方法：
-
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
-
linux下/etc/my.cnf 下my.cnf[mysqld]加上log_bin_trust_function_creators=1
③創建函數，保證每條數據都不同
1）隨機產生字元串
delimiter $$ # 兩個 $$ 表示結束
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
declare return_str varchar(255) default ''; #定義返回值變數
declare i int default 0;
while i < n do
#concat函數連接字元串
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$
2）隨機產生部門編號
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$
運行結果
mysql> delimiter $$ # 聲明兩個 $$ 表示結束
mysql> create function rand_string(n int) returns varchar(255)
-> begin
-> declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
-> 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 $$
Query OK, 0 rows affected (0.00 sec)
mysql> select rand_string(2);
-> ;
-> $$
+----------------+
| rand_string(2) |
+----------------+
| af |
+----------------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create function rand_num() returns int(5)
-> begin
-> declare i int default 0;
-> set i=floor(100+rand()*10);
-> return i;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> select rand_num()$$
+------------+
| rand_num() |
+------------+
| 105 |
+------------+
1 row in set (0.00 sec)
④創建存儲過程
創建往emp表中插入數據的存儲過程
注意：設置autocommit設置為0；
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))#沒有返回值
begin
declare i int default 0;
set autocommit = 0;#把自動提交設置為0 ，默認是寫一次提交一次，需要批處理只提交一次
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;#注意要commit
end $$
創建往dept表中插入數據的存儲過程
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$
⑤調用存儲過程
往dept表中插入數據
mysql> DELIMITER ;#恢復為；作為結束符號，上面改為了$$
mysql> CALL insert_dept(100, 10);
Query OK, 0 rows affected (0.01 sec)
往emp表中插入50萬數據
mysql> DELIMITER ;
mysql> CALL insert_emp(100001, 500000);
Query OK, 0 rows affected (27.00 sec)
運行結果
mysql> select * from dept;
+----+--------+---------+--------+
| id | deptno | dname | loc |
+----+--------+---------+--------+
| 1 | 101 | mqgfy | ck |
| 2 | 102 | wgighsr | kbq |
| 3 | 103 | gjgdyj | brb |
| 4 | 104 | gzfug | p |
| 5 | 105 | keitu | cib |
| 6 | 106 | nndvuv | csue |
| 7 | 107 | cdudl | tw |
| 8 | 108 | aafyea | aqq |
| 9 | 109 | zuqezjx | dpqoyo |
| 10 | 110 | pam | cses |
+----+--------+---------+--------+
10 rows in set (0.00 sec)
mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
| 11 | 100012 | re | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 12 | 100013 | qip | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 13 | 100014 | bvaf | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 14 | 100015 | g | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
| 15 | 100016 | qt | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 16 | 100017 | bzy | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 17 | 100018 | gf | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 18 | 100019 | r | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 19 | 100020 | ydokg | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 20 | 100021 | ee | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)
4-用Show Profile進行SQL分析
Show Profile是MySQL提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優的測量。（比Explain更加詳細）
默認情況下，參數處於關閉狀態，並保存最近15次的運行結果。
- 慢查詢日誌抓取有查詢較慢的SQL；
- Explain
- Show Profile
- 伺服器數據和腳本重要屬性的調優（比如my.cnf的配置文件）
分析步驟
1.是否支援，看看當前的mysql版本是否支援。
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
默認是關閉，使用前需要開啟。
2.開啟功能，默認是關閉，使用前需要開啟。
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
3.運行SQL
mysql> select * from emp group by id%10 limit 150000;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
| 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
10 rows in set (0.55 sec)
mysql> select * from emp group by id%20 order by 5;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 11 | 100012 | re | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 13 | 100014 | bvaf | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 15 | 100016 | qt | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 17 | 100018 | gf | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 19 | 100020 | ydokg | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
| 12 | 100013 | qip | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 14 | 100015 | g | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
| 16 | 100017 | bzy | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 18 | 100019 | r | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 20 | 100021 | ee | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.57 sec)
4.查看結果，show profiles;
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 1 | 0.00204000 | show variables like 'profiling' |
| 2 | 0.55134250 | select * from emp group by id%10 limit 150000 |
| 3 | 0.56902000 | select * from emp group by id%20 order by 5 |
+----------+------------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
- 化驗單： 診斷SQL，show profile cpu,block io for query 上一步show profiles中的問題SQL數字型大小碼;
mysql> show profile cpu,block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000008 | 0.000007 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000002 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000021 | 0.000021 | 0.000000 | 0 | 0 |
| checking permissions | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| Opening tables | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
| init | 0.000019 | 0.000019 | 0.000000 | 0 | 0 |
| optimizing | 0.000003 | 0.000002 | 0.000000 | 0 | 0 |
| statistics | 0.000007 | 0.000008 | 0.000000 | 0 | 0 |
| preparing | 0.000004 | 0.000004 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000020 | 0.000020 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000002 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.329865 | 0.330004 | 0.000000 | 0 | 0 |
| Sorting result | 0.000015 | 0.000011 | 0.000000 | 0 | 0 |
| Sending data | 0.000023 | 0.000023 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000003 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000638 | 0.000638 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000004 | 0.000000 | 0 | 0 |
| query end | 0.000016 | 0.000016 | 0.000000 | 0 | 0 |
| closing tables | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000005 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000002 | 0.000000 | 0 | 0 |
| freeing items | 0.000056 | 0.000056 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000002 | 0.000000 | 0 | 0 |
| freeing items | 0.000001 | 0.000001 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000003 | 0.000003 | 0.000000 | 0 | 0 |
| logging slow query | 0.000001 | 0.000001 | 0.000000 | 0 | 0 |
| cleaning up | 0.000002 | 0.000002 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
29 rows in set (0.02 sec)
參數備註
- ALL：顯示所有的開銷資訊。
- BLOCK IO：顯示塊lO相關開銷。
- CONTEXT SWITCHES ：上下文切換相關開銷。
- CPU：顯示CPU相關開銷資訊。
- IPC：顯示發送和接收相關開銷資訊。
- MEMORY：顯示記憶體相關開銷資訊。
- PAGE FAULTS：顯示頁面錯誤相關開銷資訊。
- SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊。
- SWAPS：顯示交換次數相關開銷的資訊。
6.日常開發需要注意的結論 （有下面四種情況就很糟糕了）
- converting HEAP to MyISAM 查詢結果太大，記憶體都不夠用了往磁碟上搬了。
- Creating tmp table 創建臨時表，拷貝數據到臨時表，用完再刪除
- Copying to tmp table on disk 把記憶體中臨時表複製到磁碟，危險!
- locked
5-全局查詢日誌
永遠不要在生產環境開啟這個功能。
配置文件啟用
在mysql的my.cnf中，設置如下：
#開啟
general_log=1
#記錄日誌文件的路徑
general_log_file=/path/logfile
#輸出格式
log_output=FILE
編碼啟用：
- set global general_log=1;
- set global log_output=’TABLE’;
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
此後，你所編寫的sql語句，將會記錄到mysql庫里的geneial_log表，可以用下面的命令查看：
mysql> select * from mysql.general_log;
四、MySQL鎖機制
1-資料庫鎖理論概述
鎖是電腦協調多個進程或執行緒並發訪問某一資源的機制。
在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。
類比：網上購物
打個比方，我們到淘寶上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，那麼如何解決是你買到還是另一個人買到的問題？
這裡肯定要用到事務，我們先從庫存表中取出物品數量，然後插入訂單，付款後插入付款表資訊，然後更新商品數量。在這個過程中，使用鎖可以對有限的資源進行保護，解決隔離和並發的矛盾。
鎖的分類
從對數據操作的類型（讀\寫）分
- 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
- 寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。
從對數據操作的粒度分
- 表鎖
- 行鎖
2-讀鎖案例講解
表鎖（偏讀） 下面使用的是MyISAM引擎。
特點：偏向MyISAM存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。
建表SQL
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam; #注意這裡使用了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;
運行結果
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
①手動增加表鎖
lock table 表名字 read(write), 表名字2 read(write), 其他;
- read(write)表示：對這張表是加讀鎖還是寫鎖；
mysql> lock table mylock read;Query OK, 0 rows affected (0.00 sec)
②查看錶上加過的鎖
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| my | test03 | 0 | 0 |
| bigdata | mylock | 1 | 0 |
...
In_use為1表示這個庫的mylock表被加了一把鎖。
③釋放鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
④加讀鎖——為mylock表加read鎖（讀阻塞寫例子）
多個session可模擬分散式的情況：當session1對A表進行加鎖，其他的session只能對該表進行讀，更新和插入都會被阻塞。
3-讀鎖案例講解2
為mylock表加write鎖（MylSAM存儲引擎的寫阻塞讀例子）
session1加了寫鎖之後（自己愛怎麼玩就怎麼玩），其他的session不能讀了！
案例結論
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。
MySQL的表級鎖有兩種模式：
-
表共享讀鎖(Table Read Lock)
-
表獨佔寫鎖(Table Write Lock)
| 鎖類型 | 可否兼容 | 讀鎖 | 寫鎖 |
|---|---|---|---|
| 讀鎖 | 是 | 是 | 否 |
| 寫鎖 | 是 | 否 | 否 |
結合上表，所以對MyISAM表進行操作，會有以下情況：
-
對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。
-
對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。
簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞。
4-表鎖分析
如果有一種命令，可以讓我們知道，鎖了哪些表，鎖了多長時間
- 看看哪些表被加鎖了
mysql> show open tables;
- 如何分析表鎖定
可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定。
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 170 |
| Table_locks_waited | 0 | #越高鎖門鎖競爭的越激烈
+-----------------------+-------+
2 rows in set (0.00 sec)
這裡有兩個狀態變數記錄MySQL內部表級鎖定的情況，兩個變數說明如下：
- Table_locks_immediate：產生表級鎖定的次數，表示可以立即獲取鎖的查詢次數，每立即獲取鎖值加1 ;
- Table_locks_waited：出現表級鎖定爭用而發生等待的次數(不能立即獲取鎖的次數，每等待一次鎖值加1)，此值高則說明存在著較嚴重的表級鎖爭用情況；
此外，MyISAM的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主表的引擎。因為寫鎖後，其他執行緒不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞。
5-行鎖理論
偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。
InnoDB與MyISAM的最大不同有兩點：一是支援事務(TRANSACTION)；二是採用了行級鎖。
由於行鎖支援事務，複習老知識
- 事務(Transaction）及其ACID屬性
- 並發事務處理帶來的問題
- 事務隔離級別
事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性：
- 原子性(Atomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
- 一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以保持數據的完整性;事務結束時，所有的內部數據結構〈如B樹索引或雙向鏈表）也都必須是正確的。
- 隔離性（lsolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
- 持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。
並發事務處理帶來的問題
- 更新丟失(Lost Update)
- 臟讀(Dirty Reads)
- 不可重複讀(Non-Repeatable Reads)
- 幻讀(Phantom Reads)
更新丟失(Lost Update)
當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題――最後的更新覆蓋了由其他事務所做的更新。
例如，兩個程式設計師修改同一java文件。每程式設計師獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。最後保存其更改副本的編輯人員覆蓋前一個程式設計師所做的更改。
如果在一個程式設計師完成並提交事務之前，另一個程式設計師不能訪問同一文件，則可避免此問題。
臟讀(Dirty Reads)
一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的數據就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現象被形象地叫做」臟讀」。
一句話：事務A讀取到了事務B已修改但尚未提交的的數據，還在這個數據基礎上做了操作。此時，如果B事務回滾，A讀取的數據無效，不符合一致性要求。(也許我改錯了，而你拿到了錯誤的數據去幹活)
不可重複讀(Non-Repeatable Reads)
一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了!這種現象就叫做「不可重複讀」。
一句話：事務A讀取到了事務B已經提交的修改數據，不符合隔離性。
幻讀(Phantom Reads)
一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀「。
一句話:事務A讀取到了事務B體提交的新增數據，不符合隔離性。
多說一句：幻讀和臟讀有點類似，
臟讀是事務B裡面修改了數據，
幻讀是事務B裡面新增了數據。
事務隔離級別
」臟讀」、「不可重複讀」和「幻讀」，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。
| 讀數據—致性及允許的並發副作用（隔離級別） | 讀數據一致性 | 臟讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|---|
| 未提交讀（Read Uncommitted） | 最低級別，只能保證不讀取物理上損壞的數據 | 是 | 是 | 是 |
| 已提交讀（Read committed） | 語句級 | 否 | 是 | 是 |
| 可重複讀（Repeatable read） | 事務級 | 否 | 否 | 是 |
| 可序列化（serializable） | 最高級別，事務級 | 否 | 否 | 否 |
資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對「不可重複讀」和「幻讀」並不敏感，可能更關心數據並發訪問的能力。
常看當前資料庫的事務隔離級別：show variables like ‘tx_isolation’;
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)
MySQL默認的是事務級，有可能出現幻讀。
6-行鎖案例講解
新建SQL
#使用INNODB引擎
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);
運行結果
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql> show index from test_innodb_lock;
行鎖定基本演示（兩個客戶端更新同一行記錄）
讀自己之所寫，因為session1中的數據還沒有提交，所以在session2中是不能夠讀到最新的數據的；可類比：發朋友圈，第一時間自己肯定是可以看到的，但是其他的用戶就不能保證在第一時間更新到了，應該是延遲幾秒（分散式系統），無法滿足強一致性；
當兩個session修改同一行數據，後來的會被阻塞；當修改的不是同一行數據時，互不干擾（大路朝天各走一邊）。
7-行鎖演示答疑補充
session2還需要重新commit才可刷新最新的數據：原因是因為session2的commit也設置為0了，如果MySQL默認的commit=1的時候，讀的時候是能夠拿到最新的數據的。
8-索引失效導致行鎖變表鎖
無索引行鎖升級為表鎖
9-間隙鎖危害
什麼是間隙鎖
當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖，對於鍵值在條件範圍內但並不存在的記錄，叫做「間隙（GAP）」。
InnoDB也會對這個「間隙」加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）寧可錯殺，不可放過。上述案例中沒有a=2的記錄，但是MySQL依舊給這一條記錄加鎖了。。
【危害】
因為Query執行過程中通過過範圍查找的話，他會鎖定整個範圍內所有的索引鍵值，即使這個鍵值並不存在。
間隙鎖有一個比較致命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值範圍內的任何數據。在某些場景下這可能會對性能造成很大的危害。
10-面試題：如何鎖定一行
面試：如何鎖定一行？begin…commit
我希望在操作這條數據的時候，此數據不能被其他執行緒修改。
11-行鎖總結與頁鎖
Innodb存儲引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體並發處理能力方面要遠遠優於MyISAM的表級鎖定的。當系統並發量較高的時候，Innodb的整體性能和MylISAM相比就會有比較明顯的優勢了。
但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現不僅不能比MyISAM高，甚至可能會更差。（使用不當，行鎖變表鎖）
①行鎖分析
如何分析行鎖定
通過檢查lnnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 128380 |
| Innodb_row_lock_time_avg | 32095 |
| Innodb_row_lock_time_max | 50618 |
| Innodb_row_lock_waits | 4 |
+-------------------------------+--------+
5 rows in set (0.01 sec)
對各個狀態量的說明如下:
- Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
- Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
- Innodb_row_lock_time_avg：每次等待所花平均時間；
- Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
- Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；
對於這5個狀態變數，比較重要的主要是
- Innodb_row_lock_time_avg（等待平均時長）
- lnnodb_row_lock_waits（等待總次數）
- lnnodb_row_lock_time（等待總時長）這三項。
尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果著手指定優化計劃（可以使用showProfile）。
②優化建議
- 儘可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
- 合理設計索引，盡量縮小鎖的範圍
- 儘可能較少檢索條件，避免間隙鎖
- 盡量控制事務大小，減少鎖定資源量和時間長度
- 儘可能低級別事務隔離
- varchar類型一定要加單引號！
③頁鎖
開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般。（了解一下即可）
五、主從複製
複製的基本原理
slave會從master讀取binlog來進行數據同步
三步驟+原理圖
MySQL複製過程分成三步
- master將改變記錄到二進位日誌(binary log)。這些記錄過程叫做二進位日誌事件，binary log events;
- slave將master的binary log events拷貝到它的中繼日誌(relay log) ;
- slave重做中繼日誌中的事件，將改變應用到自己的資料庫中。MySQL複製是非同步的且串列化的
複製的基本原則
- 每個slave只有一個master
- 每個slave只能有一個唯一的伺服器ID
- 每個master可以有多個salve
複製的最大問題是延遲。
一主一從常見配置
-
mysql版本一致且後台以服務運行
-
主從都配置在[mysqld]結點下，都是小寫
主機修改my.ini配置文件
-
[必須]主伺服器唯一ID
- server-id=1
-
[必須]啟用二進位日誌
- log-bin=自己本地的路徑/mysqlbin
- log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
-
[可選]啟用錯誤日誌
- log-err=自己本地的路徑/mysqlerr
- log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
-
[可選]根目錄
- basedir=「自己本地路徑」
- basedir=「D:/devSoft/MySQLServer5.5/」
-
[可選]臨時目錄
- tmpdir=「自己本地路徑」
- tmpdir=「D:/devSoft/MySQLServer5.5/」
-
[可選]數據目錄
- datadir=「自己本地路徑/Data/」
- datadir=「D:/devSoft/MySQLServer5.5/Data/」
-
主機，讀寫都可以
- read-only=O
-
[可選]設置不要複製的資料庫
- binlog-ignore-db=mysql
-
[可選]設置需要複製的資料庫
- binlog-do-db=需要複製的主資料庫名字
從機修改my.cnf配置文件
- [必須]從伺服器唯一ID
- [可選]啟用二進位日誌
配置文件，請主機+從機都重啟後台mysql服務
- service mysql stop
- service mysql start
主機從機都關閉防火牆
-
windows手動關閉
-
關閉虛擬機linux防火牆
查看防火牆狀態
firewall-cmd –state
停止firewall
systemctl stop firewalld.service
禁止firewall開機啟動
systemctl disable firewalld.service
在Windows主機上建立帳戶並授權slave
- GRANT REPLICATION SLAVE ON *.* TO 『zhangsan』@『從機器資料庫IP』 IDENTIFIED BY 『123456』;
- flush privileges;//刷新
- 查詢master的狀態
- show master status;
- 記錄下File和Position的值
- 執行完此步驟後不要再操作主伺服器MYSQL，防止主伺服器狀態值變化
在Linux從機上配置需要複製的主機
-
CHANGE MASTER TO MASTER_HOST=』主機 IP』, MASTER_USER=『zhangsan』, MASTER_PASSWORD=』123456』, MASTER_LOG_FILE=’File名字』, MASTER_LOG_POS=Position數字;
-
啟動從伺服器複製功能
- start slave;
-
show slave status\G
- 下面兩個參數都是Yes，則說明主從配置成功!
- Slave_IO_Running：Yes
- Slave_SQL_Running：Yes
主機新建庫、新建表、insert記錄，從機複製
如何停止從服務複製功能
- stop slave;
