慢查詢判定
1.開啟慢查詢日志記錄執行時間超過long_query_time 秒的sql語句
2.通過show processlist命令查看線程執行狀態
3.通過explain解析sql了解執行狀態
慢查詢優化
是否向服務器請求列不必要的數據
查詢不需要的記錄(limit)，多表關聯返回全部列，總是取出全部列和重復io等
是否走索引
建立索引的原則：
最左前綴匹配原則，mysql會一直向右匹配直到遇到范圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整
等值查詢(=和in)可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql查詢優化器會優化成索引為可識別的形式
模糊匹配like，“%”不能在第一個位置
選擇區分度高的列作為索引，區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大掃描的記錄數越少，唯一鍵的區分度是1
索引列不能是表達式的一部分或者是函數的參數
盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可
避免在where子句中對字段進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描
or改寫成in：or的效率是n級別，in的效率是log(n)級別，in的個數建議控制在200以內
盡量避免在where子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描
對于連續數值，使用between不用in
查詢sql是否合理
切分查詢
分解關聯查詢
將一個大的查詢分解為多個小查詢是很有必要的。很多高性能的應用都會對關聯查詢進行分解，就是可以對每一個表進行一次單表查詢，然后將查詢結果在應用程序中進行關聯，很多場景下這樣會更高效
表結構優化
對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率
水平拆分
分區表
分庫分表
垂直拆分，將單個表字段分解為多個表字段(根據模塊的耦合度，拆分為分布式系統)
查詢執行路徑
客戶端/服務器通信
半雙工模式---- 任意時刻數據只能單向傳輸
查詢優化器
mysql查詢優化器會通過某種策略自動生成最優的執行計劃
重新定義關聯表的順序
數據表的關聯順序并不總是按照在查詢中指定的順序執行
將外連接轉化為內連接
并不是所有的OUTER JOIN操作都必須以外連接的方式執行
使用等價變換規則
mysql會使用一些等價變換規則來優化表達式
優化COUNT()
MyISAM維護一個變量存放表的行數, MIN() -- 查詢列對應B+樹索引最左端記錄 ,MAX() -- 查詢列對應B+樹索引最右端記錄
預估并轉化為常數表達式
覆蓋索引掃描
當索引中的列包含列所有查詢需要返回的列時，mysql會自動使用覆蓋索引掃描而無需查詢對應的數據行
子查詢優化
提前終止查詢，如Limit
等值傳播
列表IN()的比較
在很多數據庫系統中，IN()完全等同于多個OR條件的子句，但是mysql會先將IN()列表中的數據進行排序，然后通過二分查找來確定列表中的值是否滿足條件，O(log(n))復雜度操作，而如果轉換為OR則復雜度為O(n)
SQL執行順序
FROM—>ON—>JOIN—>WHERE—>GROUP BY—>SUM(聚合函數)—>HAVING—>SELECT—>DISTINCT—>UNION—>ORDER BY—>LIMIT
優化特定類型查詢
優化子查詢
MySql的子查詢實現非常糟糕，特別是where條件中包含IN()的子查詢性能通常會比較差，如
select * from film where film_id in (
select film_id from film_actor where film.film_id = 1
);
MySql查詢優化器會將上面的查詢通過Exists改寫
select * from film where exists(
select * from film_actor where film.film_id = 1
and film.film_id = film_actor.film_id);
IN比較通過在內存中遍歷，而exists走數據庫索引，所以當子查詢中表的數據量比較大時exists效率優于in
優化子查詢最常見的建議就是盡可能使用關聯查詢代替
select film.* from film inner join film_actor using (film_id) where actor_id = 1;
優化COUNT()
COUNT函數用于統計某個列值的數量或者行數，統計列值時要求列值非空(不統計NULL)
優化關聯查詢
確保ON或者USING子句的列上有索引。只需在關聯順序的第二個表上建立索引即可，如當表A和表B用列c關聯的時候，如果優化器的優化順序為B，A，那么就不需要在表B上建立索引
確保任何的GROUP BY或者ORDER BY中的表達式只涉及到一個表中的列，這樣mysql才有可能使用索引來優化這個過程
優化GROUP BY和DISTINCT
當無法使用索引時，GROUP BY會通過臨時表或者文件排序做排序
優化limit分頁
limit操作在偏移量非常大的情況，mysql會掃描大量不需要的行然后拋棄掉導致效率降低
1.延遲關聯：在關聯查詢中使用覆蓋索引掃描，獲取關聯字段后再根據關聯列回表查詢需要的所有列
SELECT film_id, description FROM film
INNER JOIN (
SELECT film_id
FROM film ORDER BY title LIMIT 10000, 10
) AS tmp USING (film_id);
2.取上次分頁查詢操作返回的主鍵ID作為下一次分頁查詢起始位置
SELECT film_id, description FROM film WHERE film_id > 10000 ORDER BY title LIMIT 10;
NULL值
空值是不占空間的，NULL是占空間的
聚合函數,如COUNT(),MIN(),SUM()在進行查詢時會忽略掉null值
查詢列不為NULL應使用IS NOT NULL進行查詢
條件查詢<>會過濾掉NULL值和空值
Explain
id:執行編號，標識select所屬的行。如果在語句中沒子查詢或關聯查詢，只有唯一的select，每行都將顯示1。否則，內層的select語句一般會順序編號，對應于其在原始語句中的位置
select_type: 顯示本行是簡單或復雜select。如果查詢有任何復雜的子查詢，則最外層標記為PRIMARY(DERIVED、UNION、UNION RESUlT)
table: 訪問引用哪個表(引用某個查詢，如“derived3”)
type: 數據訪問讀取操作類型(ALL、index、range、ref、eq_ref、const/system、NULL)
possible_keys: 揭示哪一些索引可能有利于高效的查找
key: 顯示mysql決定采用哪個索引來優化查詢
key_len: 顯示mysql在索引里使用的字節數
ref: 顯示了之前的表在key列記錄的索引中查找值所用的列或常量
rows: 為了找到所需的行而需要讀取的行數，估算值，不精確。通過把所有rows列值相乘，可粗略估算整個查詢會檢查的行數
Extra: 額外信息，如using index、filesort等
select_type(查詢類型)
simple:簡單查詢，不包含子查詢和union
primary:查詢包含子查詢和union，最外層部分標記為primary
derived:派生表，該臨時表是從子查詢中派生出來，位于from中的子查詢
union:union中第二個及以后的select，第一個union標記為primary
union result:從匿名臨時表中檢索結果的select操作
dependent union:union中第二個或后面的select語句，取決于外層查詢
subquery:子查詢中第一個select
dependent subquery:子查詢中的第一個select，取決于外層查詢
type(訪問類型)
all:全表掃描
index:和全表掃描一樣，只是掃描表的順序是按照索引的順序，優點是避免排序，但是開銷仍然非常大
range:范圍掃描，key列顯示使用哪個索引。當使用如=,<>,>,>=,
ref:索引訪問，返回所有匹配的記錄，當使用非唯一索引或者唯一索引非唯一前綴
eq_ref:最多只返回1條符合條件的記錄，使用唯一索引或者主鍵索引時
const/system:mysql能對查詢的某部分進行優化并將其轉化為一個常量
null:mysql能在優化階段分解查詢語句，在執行時不需訪問表或者索引
Extra
Using filesort: mysql會對結果使用外部索引排序，而不是按索引次序從表讀取行
Using temporary: mysql在對查詢結果排序時使用臨時表，常見于排序和分組查詢
Using index: 使用覆蓋索引掃描，直接從索引中過濾不需要的記錄并返回命中結果。這是在mysql服務器層完成的，但無需再回表查詢記錄
Using index condition:
Using where: mysql服務器將在存儲引擎檢索行后再進行過濾
distinct: 優化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作
復制
主從復制
1.在主庫把數據更改記錄到二進制文件中(Binary Log)
2.從庫將主庫上的日志復制到自己的中繼日志(Relay Log)上
3.從庫讀取中繼日志的事件，將其重放到從庫數據中
...)
...)