1、開啟Mysql慢查詢

1.1、查看慢查詢相關(guān)配置

show variables like 'slow_query_log%'

slow_query_log 表示慢查詢開啟的狀態(tài)

slow_query_log_file 表示慢查詢?nèi)罩敬娣诺奈恢?/p>

1.2、查詢慢查詢sql耗時臨界點

show variables like 'long_query_time'

long_query_time  表示查詢超過多少秒才記錄到慢查詢?nèi)罩尽?/p>

1.3、開啟Mysql慢查詢

方式一：修改配置文件開啟慢查詢

        在my.ini增加如下配置

# 定義sql耗時多少秒就算是慢查詢，記錄慢查詢?nèi)罩?
long_query_time=2
 
# 開啟慢查詢
slow_query_log=on
 
# 定義慢查詢?nèi)罩疚募?
slow_query_log_file=/usr/local/mysql/mysql-slow-query.log

方式二：通過命令開啟慢查詢

set global slow_query_log=ON    # 開啟慢查詢?nèi)罩?
set global long_query_time=2    # SQL查詢時間大于2秒，則記錄慢查詢?nèi)罩?/pre>

2、explain查看SQL執(zhí)行計劃

explain + SQL，查看SQL的執(zhí)行計劃。重點關(guān)注type、rows、extra、key指標。

2.1、Select_type

查詢類型：

• SIMPLE ： 表示查詢語句不包含子查詢或union
• PRIMARY：表示此查詢是最外層的查詢
• UNION：表示此查詢是UNION的第二個或后續(xù)的查詢
• DEPENDENT UNION：UNION中的第二個或后續(xù)的查詢語句，使用了外面查詢結(jié)果
• UNION RESULT：UNION的結(jié)果
• SUBQUERY：SELECT子查詢語句
• DEPENDENT SUBQUERY：SELECT子查詢語句依賴外層查詢的結(jié)果

2.2、Type

存儲引擎查詢數(shù)據(jù)時采用的方式：

性能：NULL > const > eq_ref > ref > range > index > ALL

• ALL：表示全表掃描，性能最差。
• index：表示基于索引的全表掃描，先掃描索引再掃描全表數(shù)據(jù)。
• range：表示使用索引范圍查詢。使用>、>=、<、<=、in等等。
• ref：表示使用非唯一索引進行單值查詢。
• eq_ref：一般情況下出現(xiàn)在多表join查詢，表示前面表的每一個記錄，都只能匹配后面表的一行結(jié)果。
• const：表示使用主鍵或唯一索引做等值查詢，常量查詢。
• NULL：表示不用訪問表，速度最快。

2.3、Possible_keys

        表示查詢時可能使用到的索引。

2.4、Key

        查詢時真正使用到的索引。

2.5、Key_len

        表示查詢使用了索引的字節(jié)數(shù)量?？梢耘袛嗍欠袢渴褂昧私M合索引。

• 字符串類型

        字符串長度跟字符集有關(guān)：latin1=1、gbk=2、utf8=3、utf8mb4=4

        char(n)：n*字符集長度

        varchar(n)：n * 字符集長度 + 2字節(jié)

• 數(shù)值類型

        TINYINT：1個字節(jié)        SMALLINT：2個字節(jié)        MEDIUMINT：3個字節(jié)

        INT、FLOAT：4個字節(jié)        BIGINT、DOUBLE：8個字節(jié)

• 時間類型

        DATE：3個字節(jié)        TIMESTAMP：4個字節(jié)        DATETIME：8個字節(jié)

• 字段屬性

        NULL屬性占用1個字節(jié)，如果一個字段設(shè)置了NOT NULL，則沒有此項

2.6、Rows

        SQL查詢掃描的行數(shù)，行數(shù)越小越好。MySQL查詢優(yōu)化器會根據(jù)統(tǒng)計信息，估算SQL要查詢到結(jié)果需要掃描多少行記錄。

2.7、Extra

額外信息。

• Using where：表示查詢需要通過索引回表查詢數(shù)據(jù)。
• Using index：表示查詢需要通過索引，索引就可以滿足所需數(shù)據(jù)。
• Using filesort：表示查詢出來的結(jié)果需要額外排序，數(shù)據(jù)量小在內(nèi)存，大的話在磁盤，因此有Using filesort建議優(yōu)化。
• Using temprorary：查詢使用到了臨時表，一般出現(xiàn)于去重、分組等操作。
• Using index condition：MySQL5.6之后新增的索引下推。在存儲引擎層進行數(shù)據(jù)過濾，而不是在服務(wù)層過濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。

3、profile 分析執(zhí)行耗時

觀測SQL真正的執(zhí)行線程狀態(tài)及消耗的時間。

3.1、查詢profile開啟狀態(tài)

show variables like '%profil%'

• have_profiling：確定是否支持 profile
• profiling：是否開啟profiling
• profiling_history_size：定義MySQL服務(wù)器最近接收到的SQL條數(shù)。

3.2、開啟profiling

執(zhí)行如下SQL：

set profiling=ON
set profiling_history_size=30

3.3、查看最近運行的SQL

3.3.1、show profiles

查詢最近SQL的執(zhí)行耗時。

-- 查詢最近profiling_history_size條SQL
show profiles;

3.3.2、show profile for query id

查詢一條SQL從開始到結(jié)束整個生命周期各個階段的執(zhí)行耗時。

-- 根據(jù)query_id查詢指定SQL執(zhí)行耗時
show profile for query id

4、Optimizer Trace分析詳情

        profile只能查詢SQL執(zhí)行耗時，無法看到SQL具體的執(zhí)行信息。

        Optimizer Trace：可以跟蹤執(zhí)行語句的解析優(yōu)化執(zhí)行的全過程。

4.1、分析流程

打開開關(guān)

set optimizer_trace="enabled=on"

執(zhí)行需要分析的SQL執(zhí)行跟蹤

select * from information_schema.optimizer_trace

4.2、結(jié)果分析

• join_preparation：準備階段
• join_optimization：分析階段
• join_execution：執(zhí)行階段

5、慢查詢經(jīng)典案例總結(jié)

以user表為例舉例說明：

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id varchar(32) NOT NULL,
  age  varchar(16) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.1、隱式類型轉(zhuǎn)換

把userId設(shè)為索引，如果在查詢條件中把一個數(shù)字傳給user_id，則索引失效。

# user_id索引失效，傳的是數(shù)字123，索引user_id類型為字符串，兩者類型不匹配
# MySql會將user_id轉(zhuǎn)換為字符串再進行比較。
select *from user where user_id = 123
 
# 走userId索引
select *from user where user_id = '123'

5.2、最左匹配

        不滿足最左匹配原則，索引不生效。

5.3、limit深分頁問題

MySql會查詢滿足條件的100010行，然后丟棄前100000行，返回最后10行。

select *from user where age > 20 limit 100000,10

解決方案：減少回表

標簽記錄法：

        標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。

# 標簽記錄法：局限是要求id連續(xù)
select *from user where id > 100000 limit 10

延遲關(guān)聯(lián)法：

        把條件轉(zhuǎn)移到主鍵索引樹，減少回表。

# 為age字段創(chuàng)建索引，通過age索引查詢到滿足條件的id，再與原表通過id內(nèi)連接
select user1.id, user1.age, user1.name 
FROM user user1 
INNER JOIN (
    SELECT u.id FROM user u WHERE u.age > 20 limit 100000, 10
) AS user2 on user1.id= user2.id;

5.4、in元素過多

        如果in中的元素過多，建議分組查詢，一次200個。

select * from user where user_id in (1,2,3...200)
union all
select * from user where user_id in (201,202,203...400)

5.5、order走文件排序?qū)е碌穆樵?/h3>

        執(zhí)行計劃如下，Extra中包含了Using filesort(文件排序)。

因為數(shù)據(jù)是無序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不會再用到文件排序啦。而索引數(shù)據(jù)本身是有序的，我們通過建立索引來優(yōu)化order by語句。

我們還可以通過調(diào)整max_length_for_sort_data、sort_buffer_size等參數(shù)優(yōu)化；

5.6、索引字段使用is null 或 is not null可能導(dǎo)致索引失效

        有時可能因為數(shù)據(jù)量問題，導(dǎo)致MySQL優(yōu)化器放棄走索引。另外，用explain分析SQL的時候，需要注意type=range時，可能會因為數(shù)據(jù)量問題，導(dǎo)致索引無效。

5.7、索引字段上使用（！= 或者 < >, not in）可能導(dǎo)致索引失效

        如果優(yōu)化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。

5.8、左右連接，關(guān)聯(lián)的字段編碼格式不一致

select u.name, j.name, j.job
from user u
left join user_job j on u.name = j.name

將user表的name字段以及user_job表的name字段均設(shè)置索引

• 假設(shè)user表的name字段編碼為utf8，user_job表的name字段編碼為utf8mb4，則上述sql查詢不走索引。
• 假設(shè)user表的name字段編碼和user_job表的name字段編碼均為utf8，則上述sql查詢走索引。

5.9、delete + in子查詢不走索引

delete from user where name in (select name from old_user)

• delete + in子查詢不走索引
• select + in子查詢走索引

        這是因為，實際執(zhí)行的時候，MySQL對select in子查詢做了優(yōu)化，把子查詢改成join的方式，所以可以走索引。但是對于delete in子查詢，MySQL卻沒有對它做這個優(yōu)化。

總結(jié)

到此這篇關(guān)于MySQL慢sql優(yōu)化思路詳細講解的文章就介紹到這了,更多相關(guān)MySQL慢sql優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論