#頭條創作挑戰賽#
慢sql分析
查詢生成慢sql的位置，通過指令複制到docker容器外
docker cp centos7_mysql57:/var/lib/mysql/e2d0077fb342-slow.log $PWD
打開檔案，複制sql到Navicat執行
查詢耗時20s
使用 explain 分析sql執行過程
- 表的讀取順序
- 資料讀取操作的操作類型
- 哪些索引可以使用
- 哪些索引被實際使用
- 表之間的引用
- 每張表有多少行被優化器查詢
rows表示掃描的行數
explain表字段的含義：
- id: 編号
- select_type: 查詢類型
- table: 表
- type: 類型
- possible_keys: 預測用到的索引
- key: 實際用到的索引
- key_len: 使用的索引長度
- ref: 表之間的比對條件
- rows：查找到記錄所掃描的行數
- extra： 額外的 在這個裡面會告訴你，我們腳本是否有使用條件
EXPLAIN SELECT EXPLAIN SELECT
ca.id,ca.username,ca.gqid
FROM
cb_account ca
WHERE id
IN
(SELECT DISTINCT(user_id) from cb_order co); 在id相同時，sql執行的執行過程：從上往下執行，子查詢先執行
select_type: 查詢類型 - 枚舉含義
- PRIMARY：查詢中包含複雜的子查詢，最外層的select被标記為PRIMARY
- SUBQUERY：子查詢的第一個select
- SIMPLE：簡單的SELECT，不使用union或子查詢
- UNION：union中第二個或後面的select語句
- DEPENDENT UNION： union中的第二個或後面的select語句，取決于外面的查詢
- UNION RESULT: union的結果
- DEPENDENT SUBQUERY：子查詢中的第一個select，取決于外面的查詢
- DERIVED：衍生查詢，使用了臨時表(select、from子句的子查詢)
- UNCACHEABLE SUBQUERY： 一個子查詢的結果不能被緩存，必須重新評估外連結的第一行
type: 類型 - 枚舉含義
system ，const：MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪 問。如将主鍵置于where清單中，MySQL就能将該查詢轉換為一個常量,system是const類型的特例，當查詢的表隻有一行的情況下，使用system
eq_ref：類似ref，隻是使用的索引為唯一索引
ref：使用了索引列上值進行查詢
range：使用一個索引來檢索給定範圍的行
index：索引 周遊索引資料Full index scan
ALL : 全表掃描，你要找資料，要全表查找 影響性能
NULL: MySQL在優化過程中分解語句，執行時甚至不用通路表或索引
性能效率排行 system > const > eq_ref > ref > range > index > all
extra 類型 - 枚舉含義
- using where：顯示的字段，不在索引
- Using index： 使用了索引，不 用回表查詢，能夠起到性能提升
- Using temporary： 使用了臨時表，消耗比較大，常見于group by語句
- Using filesort：使用檔案排序，無法利用索引完成排序操作，性能消耗非常大
- Using join buffer：mysql引擎使用了連接配接緩存
- Impossible where: where 子句永遠為false
- Select tables optimized away：僅通過使用索引，優化器可能僅從聚合函數結果中傳回一行
優化慢 sql
type：ALL 表示為全表掃描
rows：查找了14w的資料
key 為空說明沒有使用索引
優化點：建立mobile的索引
建立索引
使用索引後,查詢時間由20s優化到0.3s
SQL優化方法
- 寫on語句時，将資料量小的表放左邊，小表寫左邊，大表寫右邊
- where後面的條件盡可能用索引字段，複合索引時，最好按複合索引順序寫where條件
- where後面有in語句，in字段的索引，最好放複合索引的後面，因為in的字段索引可能會失效
- 模糊查詢時，盡量用常量開頭，不要用%開頭，用%開頭查詢索引将失效
- 減少不要使用or，否則索引失效
- 減少使用類型轉換(顯式、隐式),因其會導緻索引失效
- 如果主查詢資料量大，則使用in，如果子查詢資料量大，則使用exists查詢哪些列
- 查詢那些列，就根據哪些列group by，不然會産生一個臨時表