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耗時(shí)臨界點(diǎn)

show variables like 'long_query_time'

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

1.3、開啟Mysql慢查詢

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

        在my.ini增加如下配置

# 定義sql耗時(shí)多少秒就算是慢查詢，記錄慢查詢?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查詢時(shí)間大于2秒，則記錄慢查詢?nèi)罩?/pre>

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

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

2.1、Select_type

查詢類型：

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

2.2、Type

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

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

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

2.3、Possible_keys

        表示查詢時(shí)可能使用到的索引。

2.4、Key

        查詢時(shí)真正使用到的索引。

2.5、Key_len

        表示查詢使用了索引的字節(jié)數(shù)量。可以判斷是否全部使用了組合索引。

• 字符串類型

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

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

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

• 數(shù)值類型

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

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

• 時(shí)間類型

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

• 字段屬性

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

2.6、Rows

        SQL查詢掃描的行數(shù)，行數(shù)越小越好。MySQL查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息，估算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：查詢使用到了臨時(shí)表，一般出現(xiàn)于去重、分組等操作。
• Using index condition：MySQL5.6之后新增的索引下推。在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過濾，而不是在服務(wù)層過濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。

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

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

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、查看最近運(yùn)行的SQL

3.3.1、show profiles

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

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

3.3.2、show profile for query id

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

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

4、Optimizer Trace分析詳情

        profile只能查詢SQL執(zhí)行耗時(shí)，無法看到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：準(zhǔn)備階段
• join_optimization：分析階段
• join_execution：執(zhí)行階段

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

以u(píng)ser表為例舉例說明：

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è)為索引，如果在查詢條件中把一個(gè)數(shù)字傳給user_id，則索引失效。

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

5.2、最左匹配

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

5.3、limit深分頁問題

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

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

解決方案：減少回表

標(biāo)簽記錄法：

        標(biāo)記一下上次查詢到哪一條了，下次再來查的時(shí)候，從該條開始往下掃描。

# 標(biāo)簽記錄法：局限是要求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個(gè)。

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í)行計(jì)劃如下，Extra中包含了Using filesort(文件排序)。

因?yàn)閿?shù)據(jù)是無序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不會(huì)再用到文件排序啦。而索引數(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í)可能因?yàn)閿?shù)據(jù)量問題，導(dǎo)致MySQL優(yōu)化器放棄走索引。另外，用explain分析SQL的時(shí)候，需要注意type=range時(shí)，可能會(huì)因?yàn)閿?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子查詢走索引

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

總結(jié)

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

最新評(píng)論