本文主要介紹 MySQL開發和面試中所必知的
本文較長，分為上下篇（可收藏，勿吃塵）
如有需要，可以參考
如有幫助，不忘點贊 ?

一、MySQL架構

1）MySQL簡介

MySQL是一個關系型數據庫管理系統，由瑞典MYSQL AB公司開發，目前屬于Oracle公司。
MySQL 是一種關聯數據庫管理系統，將數據保存在不同的表中，而不是將所有數據放在一個大倉庫中，這樣就增加了速度并提高了靈活性。
Mysql是開源的，是可以定制的，采用了GPL協議，你可以修改源碼來開發自己的MySQL系統。
MySQL支持大型的數據庫。可以處理擁有上千萬條記錄的大型數據庫。MySQL可以允許于多個系統上，并且支持多種語言。這些編程語言包括C、C++、Python、JAVA、Perl、php、Eiffel、Ruby和Tcl等。
MySQL支持大型數據庫，支持5000條記錄的數據倉庫，32位系統表文件最大可支持4GB，64位系統支持最大的表文件為8TB。

2）MySQL配置文件

binlog(二進制日志)
用于主從復制及備份恢復：binlog中存放了所有操作記錄，可用于恢復。相當于redis中的AOF，my.ini中binlog配置（默認是關閉的）如何開啟：

[mysqld]
log-bin = mysql-bin
binlog-format = row
復制代碼

Error log(錯誤日志)
默認是關閉的，通常用于記錄數據庫服務端啟動、重啟、主從復制時，記錄錯誤，將日志詳情保留在文件中，方便DBA、運維開發人員閱讀。如何開啟：

[mysqld]
log-error=/data/mysql_error.log
復制代碼

慢查詢日志log
默認是關閉的。記錄查詢的sql語句，如果開啟會減低mysql的整體性能，因為記錄日志也是需要消耗系統資源的。如何開啟：

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log     //linux
long_query_time = 1
復制代碼

數據文件
windows：
..mysql-8.0.19-winx64data目錄下存儲數據庫文件linux：
默認路徑/var/lib/mysql（可在配置文件中更改/usr/share/mysql/下的my-huge.cnf）每個目錄代表一個同名的庫。Myisam存放方式：
frm文件（framework）：存放表結構
myd文件（data）：存放表數據
myi文件（index）：存放表索引
innodb存放方式：
ibdata1：Innodb引擎將所有表的數據都存放在這里面/usr/share/mysql/ibdata1而frm文件存放在庫同名的包下
frm文件：存放表結構
配置方式

windows：my.ini 配置文件

linux：my.cnf 配置文件
3）MySQL的用戶與權限管理

MysSQL用戶管理

1.創建用戶
create user cbuc identified by '123456'

2.關于 user 表
select host,user,select_priv,insert_priv,drop_priv from mysql.user;

host： 表示連接類型
user：表示用戶名
select_priv，insert_priv，drop_priv等：該用戶所擁有的權限

3.設置密碼

--- 修改當前用戶的密碼
set password = password('123456')
--- 修改某個用戶的密碼
update mysql.user set password = password('123456') where user = 'cbuc'

4.修改用戶
--- 修改用戶名：
update mysql.user set user = 'cbuc' where user='c1';
--- 所有通過user表修改后必須用該命令才能生效
flush privileges;

5.刪除用戶

--- 不要通過delete from user t1 where t1.user='cbuc'進行刪除，系統會有殘留信息保留
drop user cbuc;

權限管理

1.授予權限：

--- 如果發現沒有該用戶，則會直接創建一個用戶
grant 權限1，權限2,…,權限n on 數據庫名.表名 to 用戶名@用戶地址 identified by '密碼'
--- 給cbuc用戶賦予對表增刪改查的權限
grant select,insert,delete,update on db_crm.* to cbuc@localhost;

2.收回權限：

--- 如果已賦全庫的表，就回收全庫全表的所有權限
revoke 權限1，權限2,…,權限n on 數據庫名.表名 from 用戶名@用戶地址
revoke all privileges on mysql.* from cbuc@localhost

3.收回權限：

--- 查看當前用戶權限
show grants;
--- 查看某用戶的全局權限
select * from mysql.user;
--- 查看某用戶的某庫的權限
select * from mysql.db;
--- 查看某用戶的某個表的權限
select * from mysql.tables_priv;

4）MySQL其他配置

大小寫問題

windows系統默認是大小寫不敏感，但是linux系統是大小寫敏感的。

0（默認）： 大小寫敏感

1：大小寫不敏感。創建的表，數據庫都是以小寫形式存放在磁盤中，對于sql語句都是轉換為小寫對表的DB進行查找。

2：創建的表和DB依據語句上格式存放，凡是查找都是轉換為小寫進行

SHOW VARIABLES LIKE '%lower_case_table_names%';

設置：

set lower_case_table_names = 1;   #此變量是只讀權限，需要在配置文件中修改
復制代碼

在my.inni / my.cnf中添加

    [mysqld]
    lower_case_table_names = 1 
復制代碼

重啟服務器（重啟前要將原來的數據庫和表轉換為小寫，否則更改后將找不到數據庫名）

sql_mode

sql_mode 是個很容易被忽視的變量，默認值是空值，在這種設置下是可以允許一些非法操作的，比如允許一些非法數據的插入。在生產環境必須將這個值設置為嚴格模式，所以開發、測試環境的數據庫也必須要設置，這樣在開發測試階段就可以發現問題。

常用設置：

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
復制代碼

5）MySQL存儲引擎

查看引擎

show engines;
復制代碼

可以看出默認的存儲引擎是InooDB

各引擎簡介

1.InnoDB存儲引擎：

InnoDB是MySQL默認的事務型引擎，它被設計用來處理大量的短期（short-lived）事務。除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎。具有行級鎖，外鍵，事務等優勢，適合高并發情況。

2.MyISAM存儲引擎：

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數（GIS）等，但MyISAM不支持事務和行級鎖（MyISAM改表時會將整個表全鎖住），缺陷：崩潰后無法安全恢復。

3.Archive引擎：

rchive存儲引擎只支持 insert 和 select操作，在MySQL5.1之前不支持索引。Archive表適合日志和數據采集類引用。適合低訪問量大數據等情況。

4.Blackhole引擎

Blackhole引擎沒有實現任何存儲機制，它會丟棄所有插入的數據，不任何保存。但服務器會記錄Blackhole表的日志，所以可以用于復制數據到備庫，或者簡單地記錄到日志。但這種應用方式會碰到很多問題，因此并不推薦。

5.CSV引擎

CSV引擎可以將普通的CSV文件作為MySQL的表來處理，但不支持索引。可以作為一種數據交換的機制，非常有用。存儲的數據直接可以在操作系統里，用文本編輯器，或者Excel讀取。

6.Memory引擎

如果需要快速地訪問數據，并且這些數據不會被修改，重啟后丟失也沒有關系的話，那么使用Memory表是非常有用的。Memory表至少比MyISAM表要快一個數量級。

7.Federated引擎

Federated引擎是訪問其他MySQL服務器的一個代理，盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經常帶來問題，因此默認是禁用的。

MyISAM和InnoDB比較

InnoDB主鍵為聚簇索引，基于聚簇索引的增刪改查效率非常高
聚簇索引： 實際存儲的循序結構與數據存儲的物理結構是一致的
非聚簇索引： 記錄的物理順序與邏輯順序沒有必然的聯系，與數據的存儲物理結構沒有關系

二、索引優化分析

1）性能下降/SQL執行時間長

查詢數據過多
能拆則拆，條件過濾盡量少
過多JOIN
JOIN原理：用A表的每一條數據掃描B表的所有數據，所以盡量先過濾再關聯
沒有利用到索引
索引針對列建索引，但并不可能每一列都建索引索引并非越多越好。當數據更新了，索引會進行調整，也會很消耗性能。并且MySQL并不會把所有索引都用上，只會根據其算法挑一個索引用。所以建得準很重要
服務器調優及各個參數設置（緩沖、線程數）

2）JOIN查詢

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 < left_table >ON < join_condition >< join_type > JOIN < right_table >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >SELECT (DISTINCT) < select_list >ORDER BY < order_by_condition >LIMIT < limit_number >

總結：

共有/獨有
有兩個表，員工表Employee和部門表Dept，員工表里面有Dept字段與部門表的主鍵ID相對應。共有：滿足employee.deptId = dept.id 的數據稱為兩表共有獨有：employee.deptId <> dept.id 的數據稱為員工表獨有
七種JOIN
有兩個表，t1 表是員工表 emp，t1 表是部門表 dept

1、 t1 表和 t2 表共有 (inner join)

select * from emp t1 inner join dept t2 on t1.deptId = t2.id

2、t1 表和 t2 表共有 + t1 表獨有 (left join)

select * from emp t1 left join dept t2 on t1.deptId = t2.id

3、t1 表和 t2 表共有 + t2表獨有（right join）

select * from emp t1 right join dept t2 on t1.deptId = t2.id

4、 t1 表的獨有（left join…where t2.id is null）

select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null

5.t2 表的獨有（right join…where t1.id is null）

6. t1 表和 t2 表全有（union）

在這里插入圖片描述

MySQL中不支持FULL JOIN
UNION： 可去除重復數據
UNION ALL： 不去除重復數據

select * from emp t1 left join dept t2 on t1.deptId = t2.id
union
select * from emp t1 right join dept t2 on t1.deptid = t2.id

7、 t1 表的獨有 + t2 表的獨有（union）

在這里插入圖片描述

select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null
UNION
select * from emp t1 right join dept t2 on t1.deptId = t2.id 
where t1.deptId is null 
復制代碼

3）索引簡介

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。可以得到索引的本質：索引是數據結構。 目的在于提高查詢效率，可以類比字典。

簡單理解為 “排好序的快速查找數據結構” ：
在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據。

左邊是數據表，一共有兩列七條數據，最左邊是數據記錄的物理地址，為了加快Col2 的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在一定的復雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。
二叉樹： 二叉樹很可能會發生兩邊不平衡的情況。
B-Tree： 會自動根據兩邊的情況自動調節，使兩端無限趨近于平衡狀態，可以使性能最穩定。但是插入/修改操作過多時，B-TREE會不斷調整平衡，消耗性能。

一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲在磁盤上。
我們平常所說的索引，如果沒有特別指明，都是指B樹（多路搜索樹，并不一定是二叉的）結構組織的索引。其中聚集索引，次要索引，覆蓋索引，復合索引，前綴索引，唯一索引默認都是使用B+樹這種類型的索引之外，還有哈希索引（hash index）等。

索引優勢

類似圖書館簡歷書目索引，提高數據檢索的效率，降低數據庫的IO成本。
通過索引列對數據進行排序，降低數據排序的成本，降低了CPU的消耗。

索引劣勢

實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的。

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE、和DELETE，因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息。

索引只是提高效率的一個因素，如果你的MySQL有大量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句

索引結構

1.BTree索引：
真實數據存在于葉子節點，即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

2.B+Tree索引：

B+Tree 第二級的數據并不能直接取出來，只作索引使用。在內存有限的情況下，查詢效率高于BTree
BTree第二級可以直接取出來，樹形結構比較重，在內存無限大到時候有優勢。

【B+Tree 和 BTree 的區別】
1）內存有限的情況下，B+Tree永遠比BTree好，無限內存則反之
2） B樹的關鍵字和記錄是放在一起的，葉子節點可以看做外部節點，不包含任何信息；B+樹葉子節點中國你只有關鍵字和指向下一個節點的索引，記錄只放在葉子節點中。（一次查詢可能進行兩次I/O操作）
3）在B樹中，越靠近根節點的記錄查找時間越快，只要找到關鍵字即可確定記錄存在；而B+樹每個記錄的查找時間基本是一樣的，都需要從根節點走到葉子節點，而且在葉子節點中還要在比較關鍵字。從這個角度看B樹的性能好像會比B+樹好，而在實際應用中卻是B+樹的性能要好些。因為B+樹的非葉子節點不存放實際的數據，這樣每個節點可容納的元素個數比B數多，樹高比B樹小，這樣帶來的好處是減少磁盤訪問次數。盡管B+樹找到一個記錄所需的比較次數比B樹多但是一次磁盤訪問時間相當于成百上千次內存比較時間，因此實際中B+樹的性能可能還會好寫，而且B+樹的葉子節點使用指針連接在一起，方便順序遍歷（例如查看一個目錄下的所有文件，一個表中的所有記錄等）
4） B+樹的磁盤讀寫代價更低，相對來說IO讀寫次數也就降低了。
5） B+樹的查詢效率更加穩定。由于非終結點并不是指向文件內容的節點，而只是葉子節點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根節點到葉子節點的路。所以關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。

聚簇索引

好處：
按照聚簇索引排序順序，查詢顯示一定范圍數據的時候，由于數據都是緊密相連，數據庫不用從多個數據塊中提取數據，所以節省了大量的IO操作。
限制：

對于MySQL數據庫目前只有InnoDB數據引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
由于數據物理存儲排序方式只能有一種，所以每個MySQL的表只能有一個聚簇索引。一般情況下就是該表的主鍵。

3.full-text全文索引

全文索引（也稱全文檢索）是目前搜索引擎使用的一種關鍵技術。它能夠利用分詞技術等多種算法智能分析出文本文字中關鍵詞的頻率和重要性，然后按照一定的算法規則智能地篩選出我們想要的搜索結果。

查詢：

# 傳統 LIKE 查詢
select * from blink t1 where t1.content like '%菜%'

# 全文檢索
select * from blink t1 where MATCH(title,content) AGAINST('菜') 
復制代碼

限制： MySQL5.6.4之前只用MyISAM 支持，5.6.4以后 InnoDB才支持，但是官方版不支持中文分詞，需要第三方分詞插件。

4.Hash索引

Hash 索引只有Memory，NDB兩種引擎支持，Memory引擎默認支持。
Hash索引，如果多個Hash值相同，出現哈希碰撞，那么索引以鏈表的方式存儲。
NoSql采用此索引結構。

5.RTree索引

R-Tree在MySQL很少使用，僅支持geometry 數據結構，支持該類型的存儲引擎只有MyISAM、bdb、InnoDB、ndb、archive幾種。相對于B-Tree，R-Tree的優勢在于查找。

索引分類

1.主鍵索引

設定為主鍵后數據庫會自動簡歷索引，InnoDB采用聚簇索引
語法：

# 隨表一起創建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT關鍵字的列必須要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , NAME varchar(8)
    , PRIMARY KEY(ID)
 )

# 單獨建主鍵索引
ALTER TABLE emp add PRIMARY KEY emp(id);

# 刪除主鍵索引
ALTER TABLE emp drop PRIMARY KEY;   # 修改主鍵索引前必須刪除(drop)原索引，再新建(add)索引

2.單值索引

即一個索引只包含單個列，一個表可以有多個單列索引。
語法：

# 隨表一起創建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT關鍵字的列必須要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , EMP_NO varchar(8)
    , NAME varchar(8)
    , KEY(EMP_NO)
 )

# 單獨建單列索引
create index idx_emp_no on emp(EMP_NO)

# 刪除單列索引
drop index idx_emp_no

3.唯一索引

索引列的值必須唯一，但允許有空值。
建立唯一索引是必須保證所有的值是唯一的(除了null)，若有重復數據，會報錯

# 隨表一起創建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT關鍵字的列必須要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , EMP_NO varchar(8)
    , NAME varchar(8)
    , UNIQUE(EMP_NO)
 )

# 單獨建唯一索引
create unique index idx_emp_no on emp(EMP_NO)

# 刪除主鍵索引
drop index idx_emp_no on emp

4.復合索引

在數據庫操作期間，復合索引比單值索引所需要的開銷更小（對于相同的多個列建索引）;
當表的行數遠大于索引列的數目時可以使用復合索引。

# 隨表一起創建CREATE TABLE emp (    # 使用AUTO_INCREMENT關鍵字的列必須要有索引    ID int(10) UNSIGNED AUTO_INCREMENT    , EMP_NO varchar(8)    , NAME varchar(8)    , key(EMP_NO，NAME) )#建立唯一索引是必須保證所有的值是唯一的(除了null)，若有重復數據，會報錯# 單獨建唯一索引create index idx_no_name on emp(EMP_NO,NAME)# 刪除主鍵索引drop index idx_no_name on emp

【基本語法】

# 創建
alter < table_name > add [unique] index <index_name> on <column_name>

# 刪除
drop index <index_name> on <table_name>

#查看
show index from <table_name>

#使用ALTER命令
#方式1：該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為null
alter table <table_name> add primary key <column_name>

#方式2：該語句添加一個唯一索引，值必須是唯一的(null外，null可能會出現很多次)
alter table <table_name> add unique key <column_name>

#方式3：該語句添加普通索引，索引值可以出現很多次
alter table <table_name> add index <index_name>(column_name)

#方式4：該語句指定了索引為FULLTEXT，用戶全文索引
alter table <table_name> add FULLTEXT <index_name>(column_name)

哪些情況需要建立索引

主鍵自動建立唯一索引
頻繁作為查詢條件的字段應該創建索引（where后面的語句）
查詢中與其他表關聯的字段，外鍵關系建立索引
單鍵/組合索引的選擇問題（在高并發下傾向創建組合索引）
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

查詢中統計或者分組字段

哪些情況不需要建立索引

表記錄太少
經常增刪改的表（因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件）
where 條件里用不到的字段不創建索引
數據重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數據列建立索引。注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。

4）性能分析

MySQL常見瓶頸

CPU
SQL中對大量數據進行比較、關聯、排序、分組（最大的壓力在于比較）

IO
實例內存滿足不了緩存數據或排序等需要，導致產生大量物理IP。查詢執行效率低，掃描過多數據行。

鎖
不適宜的鎖的設置，導致線程阻塞，性能下降。死鎖，線程之間交叉調用資源，導致死鎖，程序卡主。

服務器硬件的性能瓶頸：
top，free，IOStat和vmstat來查看系統的性能狀態

Explain的使用

使用Explain關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸

可以查看的內容

表的讀取順序
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優化器查詢

怎么用

explain + SQL語句
包含的信息：

各字段解釋

1.【 id】
select查詢的序列號，包含一組數字，表示查詢中執行select字句或操作表的順序。
三種情況：

id相同，執行順序由上至下

id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越被先執行

id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行。
2.【select_type】

SIMPLE
簡單的select查詢，查詢中不包含子查詢或者UNION

PRIMARY
查詢中若包含任何復雜的子部分，最外層查詢則被標記為Primary

DERIVED
在FROM列表中包含的子查詢被標記為DERIVED（衍生）MySQL會遞歸執行這些子查詢，把結果放在臨時表里。

SUBQUERY
在SELECT或WHERE列表中包含了子查詢

DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查詢，子查詢基于外層

【dependent subquery 和 subquery 的區別】
依賴子查詢：子查詢結果為多值子查詢：查詢結果為單值
UNCACHEABLE SUBQUERY
無法被緩存的子查詢

@@表示查的是環境參數，沒辦法緩存
UNION
若第二個SELECT出現在UNION之后，則被標記為UNION；若UNION 包含在FROM字句的子查詢中，外層SELECT將被標記為：DERIVED

UNION RESULT
從UNION表獲取結果的SELECT

3.【table】
顯示這一行的數據是關于哪張表的
4.【type】
type顯示的是訪問類型，是較為重要的一個指標，結果值從最好到最壞的依次排序：
system > const > eq_ef > ref > range(盡量保證) > index > ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref

system
表只有一行記錄（等于系統表），這是const類型的特列，平時不會出現，這個也可以忽略不計
const
表示通過索引一次就找到了，const 用于比較primary key或者 unique索引。因為只匹配一行數據，所以很快將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量
eq_ref
唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
ref
非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，他可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體
range
只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你的where語句中出現了between、<、>、in等查詢。這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某一個點，而結束于另一點，不用掃描全部索引。
index
Full Index Scan，index與ALL區別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數據文件小。（也就是說雖然all和index都是讀全表的），但index是從索引中讀取的，而all是從硬盤中讀的。
all
Full Table Scan，將遍歷全表以找到匹配的行

5.【possible_keys】
顯示可能應用到這張表中的索引，一個或多個。查詢涉及到的字段上若存在的索引，則該索引將被列出，但不一定被查詢實際使用
6.【key】
實際使用的索引，如果為NULL，則沒有使用索引。查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。
7.【key_len】
表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。key_len字段能夠幫你檢查是否充分利用上了索引。

計算方式：

動態類型包括：varchar，detail text（）截取字符串
本章的表結構如下：

第一組計算結果：
key_len=deptno(int)+null+ename(varchar(20)3+動態=4+1+203+2=67
第二組計算為：
key_len=deptno(int)+null=4+1=5
8.【ref】
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或者常量被用于查找索引列上的值

9.【row】
rows列顯示MySQL認為它執行查詢時必須檢查的行數（越少越好）

10.【Extra】
包含不適合在其他列中顯示但十分重要的額外信息。

Using filesort
說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”。
Using temporary
使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于排序order by 和分組查詢 group by。
Using index
表示相應的select操作中使用了覆蓋索引（Covering Index），避免了表的數據行，效率不錯。如果同時出現using where，表明索引被用來執行索引鍵值得查找；如果沒有同時出現using where，表明索引只是用來讀取數據而非利用索引執行查找。
覆蓋索引：一個索引包含了（或覆蓋了）【select子句】與查詢條件【where 子句】中所有需要的字段就叫做覆蓋索引。注意： 只取出需要的列，不可select *，不可將所有字段一起做索引
Using where
表明使用了 where 過濾
Using join buffer
使用了連接緩存

5）查詢優化

索引的使用

全值匹配我最愛
staffs 表建立索引 idx_staffs_nameAgePos,以name，age，pos的順序建立，全值匹配標識按順序匹配。

2.最佳左前綴原則
如果索引了多列，要遵守最左前綴原則，值得是查詢從索引的最左前列開始，并且不跳過索引中的列
and 忽略左右關系，即使沒有按順序，由于優化器的存在，也會自動優化

3.不在索引列上做任何操作（計算、函數、（自動或手動）類型轉換），會導致索引失效而轉向全表掃描。

4.存儲引擎不能使用索引中范圍條件右邊的列
范圍若有索引則能使用到索引，范圍條件右邊的索引會失效（范圍條件右邊與范圍條件使用的同一個組合索引，右邊的才會失效，若是不同索引則不會失效）

5.**盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select ***

6.MySQL在使用不等于（!= 或 <>）的時候無法使用索引，會導致全表掃描。
where age != 10 and name = 'xxx' 這種情況下，mysql會自動優化將 name = 'xxx' 放在 age != 10 之前，name依然能使用索引，只是age的索引失效

7.is not null 也無法使用索引，但是 is null 是可以使用索引

8.like 以通配符開頭（'%xxx'）索引失效變成全表掃描
like '%xxx'：type 類型會變成all
like 'xxx%'：type 類型為range，算是范圍，可以使用索引

9.字符串不加單引號索引失效
底層進行類型轉換時索引失效，使用了函數造成了索引失效

10.少用or，用它連接時索引會失效

【例子小結】
此時復合索引index（a,b,c）

【使用建議】

對于單鍵索引，盡量選擇針對當前query過濾性更好的索引
在選擇組合索引的時候，當前query中過濾性最好的字段在索引字段順序中，位置越靠前越好。（避免索引過濾性不好的索引失效）
在選擇組合索引的時候，盡量選擇可以能夠包含當前query中where字句中更多字段的索引
盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的

關聯查詢優化
1、保證被驅動表的join字段已經被索引（join后的表為驅動表）2、left join時，選擇小表作為驅動表，大表作為被驅動表（left join一定是左邊是驅動表，右邊是被驅動表）3、inner join時，MySQL會自己幫你把小結果集選為驅動表。因為驅動表無論如何都會被全表掃描，所以掃描次數越少越好。
4、子查詢盡量不要放在被驅動表，有可能使用不到索引。

# 未加索引，type為ALLexplain select * from class left join book on class.card = book.card# 添加索引優化，第二行的type變成了refalter table book add index idx_card_B(card);# 這是由左連接特效決定的，left join條件用于確定如何從右表搜索行，左邊一定都有# 繼續優化，刪除舊索引，新建新索引drop index idx_card_B on book;alter table class add index idx_card_A(card)

的查詢優化
1、有索引的情況下用 inner join 是最好的，其次就是 in，exists最糟糕2、無索引的情況下用小表驅動大表，因為 join 方式需要 distinct 沒有索引distinct 消耗性能比較大，所以 exists 性能最佳，其次 in 其次，join性能最差3、無索引的情況下大表驅動小表，in和exists的性能應該是接近的，都比較糟糕，exists稍微好一點，但是超不過5%
order by關鍵字優化
盡量使用Index方式排序，避免使用FileSort 方式排序。MySQL中支持兩種方式的排序，FileSort和Index，其中index效率高，它指Mysql掃描索引本身完成排序，FileSort方式效率比較低。滿足三種情況會使用Index排序。
Order By語句使用索引最左前列
使用where子句與order by子句條件列組合滿足索引最左前列
where子句中如果出現索引的范圍查詢（即explain中出現range）會導致order by索引失效。

例子：talA 表中有索引（age,birth,name）

分頁查詢的優化--limit

explain select sql_no_chache  * from emp order by deptno limit 10000,40

#加上索引create index emp on emp(deptno)

# 通過以上結果可以看出加上索引并不能改變# 進一步優化：先利用覆蓋索引把要取的數據行的主鍵取到，然后再用這個主鍵列與數據表做關聯（查詢數據量小了后）explain select sql_no_cache * from emp e inner join (select id from emp e order by deptno limit 10000,40)a on a.id = e.id

GROUP BY 關鍵字優化
1、group by實質上是先排序后進行分組，遵照索引建的最佳前綴
2、當無法使用索引列，增大max_length_for_sort_data參數的設置+增大sort_buffer_size參數的設置
3、where高于having，能寫在where限定的條件就不要去having限定了。

去重優化
盡量不要使用distinct關鍵字去重例子：

# 使用distinct關鍵字去重消耗性能select distinct BOOK_NAME from book where id in(1,2,5,4,8)# 使用 group by能夠利用到索引select BOOK_NAME from book where id in(1,2,5,4,8) group by BOOK_NAME