共計(jì) 5005 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。
這篇文章主要介紹了 Mysql 優(yōu)化問(wèn)題有哪些的相關(guān)知識(shí)，內(nèi)容詳細(xì)易懂，操作簡(jiǎn)單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇 Mysql 優(yōu)化問(wèn)題有哪些文章都會(huì)有所收獲，下面我們一起來(lái)看看吧。
開(kāi)發(fā)過(guò)程如何排查 SQL？排查思路
對(duì)于大部分程序員來(lái)說(shuō)，在開(kāi)發(fā)過(guò)程中排查 SQL 基本是空白。但隨著行業(yè)的內(nèi)卷，對(duì)一開(kāi)發(fā)過(guò)程越來(lái)越重視和專(zhuān)業(yè)，其中一項(xiàng)就是開(kāi)發(fā)過(guò)程中盡可能解決掉 SQL 問(wèn)題，避免生產(chǎn)才暴露 SQL 問(wèn)題。那么在開(kāi)發(fā)過(guò)程中如何方便的進(jìn)行程序的 SQL 排查呢？
其思路還是使用 Mysql 的慢日志來(lái)實(shí)現(xiàn)：
首先在開(kāi)發(fā)過(guò)程中也需要開(kāi)啟數(shù)據(jù)庫(kù) Mysql 的慢查詢(xún)
SET GLOBAL slow_query_log= on其次設(shè)置慢 SQL 的最小時(shí)間
注意：這里時(shí)間單位是 s 秒但是有 6 位小數(shù)因此可以表示到微妙的時(shí)間力度，一般單表 SQL 執(zhí)行時(shí)間在 20ms 之內(nèi)為宜，反之理解就是在開(kāi)發(fā)過(guò)程中，如果你執(zhí)行的 sql 語(yǔ)句超過(guò)了 20ms 則你需要去關(guān)注它。
SET GLOBAL long_query_time=0.02;為方便操作可以把慢 SQL 記錄到表中而不是文件中
SET GLOBAL log_output= TABLE最后通過(guò) mysql.slow_log 表就可以查詢(xún)到記錄的慢 SQL
使用工具
在勇哥給大家開(kāi)發(fā)的軟件中，也提供了圖形化的界面來(lái)一鍵幫助大家快速實(shí)現(xiàn)上述功能。
生產(chǎn)環(huán)境 SQL 問(wèn)題如何排查？排查思路
生成 SQL 問(wèn)題的排查就相對(duì)復(fù)雜一點(diǎn)點(diǎn)，但是整體的思路還是通過(guò)慢 SQL 來(lái)排查，具體思路如下：
首先開(kāi)啟數(shù)據(jù)庫(kù) Mysql 的慢查詢(xún)
SET GLOBAL slow_query_log= on其次設(shè)置慢 SQL 的最小時(shí)間
SET GLOBAL long_query_time=0.02;一般生成時(shí)把慢 SQL 放到文件
SET GLOBAL log_output= FILE下載慢 SQL 日志文件到本地
最后關(guān)閉數(shù)據(jù)庫(kù) Mysql 的慢查詢(xún)
著重注意：生產(chǎn)的慢 SQL 最好在使用時(shí)，才去開(kāi)啟，用完后關(guān)閉，避免日志記錄影響到業(yè)務(wù)性能
SET GLOBAL slow_query_log= offSQL 怎么調(diào)優(yōu)？
SQL 調(diào)優(yōu)融合多方面的知識(shí)，總體來(lái)說(shuō)常見(jiàn)從表結(jié)構(gòu)、表索引、兩方面來(lái)優(yōu)化。
表結(jié)構(gòu)優(yōu)化 1、合理的使用字段類(lèi)及長(zhǎng)度
舉個(gè)例子來(lái)理解：就一個(gè)性別字段，用 tinyint(1)存儲(chǔ)占用 1 字節(jié)，用 int(1)存儲(chǔ)占用 4 個(gè)字節(jié)，如果有 100W 條記錄，那么用 int 存儲(chǔ)的表就比 tinyint 存儲(chǔ)的表文件大小多 2.8M 左右，因此在讀取 int 類(lèi)型存儲(chǔ)的表時(shí)文件大，讀速度相比讀 tinyint 的慢。這其實(shí)就是為什么說(shuō)要合理使用字段類(lèi)型長(zhǎng)度的本質(zhì)：就是減少存儲(chǔ)的文件大小，以提供讀性能。
當(dāng)然有的朋友就可能說(shuō) 2.8M 并不影響大局，因此可以忽略。對(duì)于此想法勇哥要補(bǔ)充一嘴：一個(gè)表假設(shè)有 10 個(gè)字段，你的系統(tǒng)一共有 30 個(gè)表，那么再看一下多出的文件大小是多少？（2.8Mx10x30=840M，840M 你用迅雷超級(jí)下載也要花好幾秒，這個(gè)時(shí)間在計(jì)算機(jī)里面算是很慢了 …）
2、合理的使用冗余設(shè)計(jì)
2.1、冗余設(shè)計(jì)背景——臨時(shí)表
Mysql 內(nèi)部存在一種特殊且輕量級(jí)的臨時(shí)表，它是被 Mysql 自動(dòng)創(chuàng)建和刪除的。主要在 SQL 的執(zhí)行過(guò)程中使用臨時(shí)表來(lái)存儲(chǔ)某些操作的中間結(jié)果，該過(guò)程由 MySQL 自動(dòng)完成，用戶(hù)無(wú)法手工干預(yù)，且這種內(nèi)部表對(duì)用戶(hù)來(lái)說(shuō)是不可見(jiàn)的。
內(nèi)部臨時(shí)表在 SQL 語(yǔ)句的優(yōu)化過(guò)程中非常重要，MySQL 中的很多操作都要依賴(lài)于內(nèi)部臨時(shí)表來(lái)進(jìn)行優(yōu)化操作。但是使用內(nèi)部臨時(shí)表需要?jiǎng)?chuàng)建表以及中間數(shù)據(jù)的存取代價(jià)，所以在寫(xiě) SQL 語(yǔ)句的時(shí)候應(yīng)該盡量去避免使用臨時(shí)表。
那么場(chǎng)景的那些場(chǎng)景 Mysql 內(nèi)部會(huì)使用臨時(shí)表呢？
多表關(guān)聯(lián)查詢(xún) (JOIN) 中，order by 或 group by 使用的列不是第一個(gè)表的列
group by 的列不是索引列時(shí)
distinct 和 group by 聯(lián)合使用
order by 語(yǔ)句中使用了 distinct 關(guān)鍵字
group by 的列時(shí)索引列，但數(shù)據(jù)量過(guò)大時(shí)
2.2、如何查看是否使用內(nèi)部臨時(shí)表？
通過(guò) Explain 關(guān)鍵字或者工具的功能按鈕，查看 SQL 的執(zhí)行過(guò)程，在結(jié)果中的 Extra 列中如果出現(xiàn) Using temporary 關(guān)鍵字，則說(shuō)明你的 SQL 語(yǔ)句在執(zhí)行時(shí)使用了臨時(shí)表。
如下圖，角色 Role 表和角色組 Role_Group 是多對(duì) 1 的關(guān)系，在關(guān)聯(lián)查詢(xún)的時(shí)候，排序使用 role_group 的 id 排序則會(huì)使用臨時(shí)表（見(jiàn)下圖 1），如果排序使用 role 的 id 則不會(huì)使用臨時(shí)表（見(jiàn)圖 2）。
2.3、如何解決不使用內(nèi)部臨時(shí)表？
這個(gè)問(wèn)題解決有兩個(gè)方案，一是調(diào)整 SQL 語(yǔ)句避免使用臨時(shí)表，另外一個(gè)方案就是在表中冗余存儲(chǔ)。比如 2.2 中的圖一例子如果一定要按照 role_group 的 id 排序，則可以按照 role 表中的 group_id 排序，而這列正是冗余存儲(chǔ)的 role_group 表中 id 列值。
3、合理的使用分庫(kù)分表
分庫(kù)分表不僅用于大數(shù)量情況下的優(yōu)化，其中垂直分表還可以使用到 SQL 調(diào)優(yōu)下。（這里我就不去解釋垂直和水平分表了，感興趣的私信我）
例如：一個(gè)文章表一般設(shè)計(jì)不會(huì)包括文章內(nèi)容這個(gè)大字段。
文章內(nèi)容這個(gè)大字段是單獨(dú)放置到一張表中
為什么文章表要采用以上設(shè)計(jì)而不把字段合并到一表中呢？
我們先來(lái)計(jì)算一道數(shù)學(xué)題，假設(shè)一篇文章總共 1M 大小，其中文章內(nèi)容，824KB，其余字段 200KB，這樣的文章一共有 100W 條，則：
方案一，如果用一個(gè)表存儲(chǔ)，則這個(gè)表大小是 100W*1M=100WM
方案二，如果用垂直分表存儲(chǔ)，則基本表時(shí) 200KBx100W，內(nèi)容表 824KBx100W
我們?cè)谇岸擞形恼铝斜砗臀恼略斍閮蓚€(gè)頁(yè)面，分別要直接從數(shù)據(jù)庫(kù)中查詢(xún)相關(guān)內(nèi)容，則：
方案一，文章列表和文章詳情的查詢(xún)都會(huì)從 100WM 數(shù)據(jù)中查詢(xún)
方案二，文章列表會(huì)從 200KBx100W 中查詢(xún)，文章詳情會(huì)從 824KBx100W 中查詢(xún)（當(dāng)前也可能還需要從 200KBx100W 中查詢(xún)）
說(shuō)到這里，相信大家心中應(yīng)該有一個(gè)清晰的答案了吧！垂直拆表可以讓不同業(yè)務(wù)場(chǎng)景的查詢(xún)的數(shù)據(jù)量不同，常常這個(gè)數(shù)據(jù)量往往小于總表數(shù)據(jù)量，這就比從固定很大小的量中查詢(xún)更靈活和高效率。
表索引優(yōu)化 1、合理的添加索引列
大多數(shù)人對(duì)應(yīng)索引的理解層次都在“索引可以加快查詢(xún)的速度”，然而這句話(huà)勇哥要補(bǔ)充下半句“索引可以加快查詢(xún)的速度，也可以減慢數(shù)據(jù)插入或修改的速度”。
如果一個(gè)表有 5 個(gè)索引，那么可以簡(jiǎn)單的把一個(gè)索引當(dāng)成一個(gè)表，則這就會(huì)有 1 張表 + 6 張索引表 = 相當(dāng)于有 6 張表，那么這 6 張表在什么時(shí)候會(huì)操作呢？我們來(lái)計(jì)算一下：
insert 操作，數(shù)據(jù)插入后，需要去對(duì) 5 張索引表插入索引數(shù)據(jù)
delete 操作，數(shù)據(jù)刪除后，需要去把 5 張索引表中的索引刪除
update 操作
如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引
如果沒(méi)有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表
select 操作
如果命中查詢(xún)索引，則先查詢(xún)索引，再查數(shù)據(jù)表
如果沒(méi)命中查詢(xún)索引，則直接查數(shù)據(jù)表
通過(guò)以上的計(jì)算，你會(huì)神奇的發(fā)現(xiàn)，索引個(gè)數(shù)越多，對(duì)于 insert、delete、update 操作是有影響的，而且是負(fù)影響。所以對(duì)于索引竟可能評(píng)估其帶來(lái)的影響小于查詢(xún)的收益，才去添加，而不是盲目的添加。
2、合理的調(diào)配復(fù)合索引列個(gè)數(shù)和順序
復(fù)合索引指的是包括有多個(gè)列的索引，它能有效的減少表的索引個(gè)數(shù)，平衡了多個(gè)字段需要多個(gè)索引直接的性能平衡，但是再使用復(fù)合索引的時(shí)候，需要注意索引列個(gè)數(shù)和順序的問(wèn)題。
先說(shuō)列個(gè)數(shù)的問(wèn)題，指的是一個(gè)復(fù)合索引中包括的列字段太多影響性能的問(wèn)題，主要是對(duì) update 操作的性能影響，如下紅字：
如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引，如果索引列個(gè)數(shù)越多則修改該索引的概率越大
如果沒(méi)有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表
再說(shuō)復(fù)合索引中列順序的問(wèn)題，是指索引的最左匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配，這個(gè)比較容易理解，就不多做闡述。
那些情況索引會(huì)失效？
索引無(wú)法存儲(chǔ) null 值，當(dāng)使用 is null 或 is not nulli 時(shí)會(huì)全表掃描
like 查詢(xún)以 % 開(kāi)頭
對(duì)于復(fù)合索引，查詢(xún)條件中沒(méi)有給出索引中第一列的值時(shí)
mysql 內(nèi)部評(píng)估全表掃描比索引快時(shí)
or、!=、、in、not in 等查詢(xún)也可能引起索引失效
表設(shè)計(jì)有那些規(guī)范？建表規(guī)約
表達(dá)是與否概念的字段，必須使用 is_xxx 的方式命名，數(shù)據(jù)類(lèi)型為 unsigned tinyint。說(shuō)明：任何字段如果為非負(fù)數(shù)，則必須是 unsigned。
字段允許適當(dāng)冗余，以提高查詢(xún)性能，但必須考慮數(shù)據(jù)一致。e.g. 商品類(lèi)目名稱(chēng)使用頻率高，字段長(zhǎng)度短，名稱(chēng)基本一成不變，可在相關(guān)聯(lián)的表中冗余存儲(chǔ)類(lèi)目名稱(chēng)，
避免關(guān)聯(lián)查詢(xún)
。冗余字段遵循：
不是頻繁修改的字段；
不是 varchar 超長(zhǎng)字段，更不能是 text 字段。
索引規(guī)約
在 varchar 字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可。
頁(yè)面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)通過(guò)搜索引擎來(lái)解決。說(shuō)明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用此索引。
如果有 order by 的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢(xún)性能。
正例：where a=? and b=? order by c; 索引: a_b_c。
反例：索引中有范圍查找，那么索引有序性無(wú)法利用，如 WHERE a 10 ORDER BY b; 索引 a_b 無(wú)法排序。
利用延遲關(guān)聯(lián)或者子查詢(xún)優(yōu)化超多分頁(yè)場(chǎng)景。說(shuō)明：MySQL 并不是跳過(guò) offset 行，而是取 offset+N 行，然后返回放棄前 offset 的行，返回 N 行。當(dāng) offset 特別大的時(shí)候，效率會(huì)非常的低下，要么控制返回的總頁(yè)數(shù)，要么對(duì)超過(guò)閾值的頁(yè)數(shù)進(jìn)行 SQL 改寫(xiě)。
建組合索引的時(shí)候，區(qū)分度最高的在最左邊。
SQL 性能優(yōu)化的目標(biāo)，至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，最好是 consts。
SQL 語(yǔ)句
不要使用 count(列名) 或 count(常量) 來(lái)替代 count()，count() 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)句，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)。說(shuō)明：count(*) 會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名) 不會(huì)統(tǒng)計(jì)此列為 NULL 值的行。
count(distinct column) 計(jì)算該列除 NULL 外的不重復(fù)行數(shù)。注意，count(distinct column1,column2) 如果其中一列全為 NULL，那么即使另一列用不同的值，也返回為 0。
當(dāng)某一列的值全為 NULL 時(shí)，count(column) 的返回結(jié)果為 0，但 sum(column) 的返回結(jié)果為 NULL，因此使用 sum() 時(shí)需注意 NPE 問(wèn)題?？梢允褂萌缦路绞絹?lái)避免 sum 的 NPE 問(wèn)題。
SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;使用 ISNULL() 來(lái)判斷是否為 NULL 值。說(shuō)明：NULL 與任何值的直接比較都為 NULL。
不得使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。說(shuō)明：以學(xué)生和成績(jī)的關(guān)系為例，學(xué)生表的 student_id 是主鍵，成績(jī)表的 student_id 則為外鍵。如果更新學(xué)生表中的 student_id，同時(shí)觸發(fā)成績(jī)表中的 student_id 更新，即為級(jí)聯(lián)更新。外鍵與級(jí)聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群；級(jí)聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫(kù)更新風(fēng)暴的風(fēng)險(xiǎn)；外鍵影響數(shù)據(jù)庫(kù)的插入速度。
禁止使用存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程難以調(diào)試和擴(kuò)展，更沒(méi)有移植性。
in 操作能避免則避免。若實(shí)在避免不了，需要仔細(xì)評(píng)估 in 后面的集合元素?cái)?shù)量，控制在 1000 個(gè)之內(nèi)。
ORM 映射
POJO 類(lèi)的布爾屬性不能加 is，而數(shù)據(jù)庫(kù)字段必須加 is_，要求在 resultMap 中進(jìn)行字段與屬性的映射。
sql.xml 配置參數(shù)使用：#{}, #param#，不要使用 ${}，此種方式容易出現(xiàn) SQL 注入。
@Transactional 事務(wù)不要濫用。事務(wù)會(huì)影響數(shù)據(jù)庫(kù)的 QPS。另外，使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補(bǔ)償、統(tǒng)計(jì)修正等。
關(guān)于“Mysql 優(yōu)化問(wèn)題有哪些”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對(duì)“Mysql 優(yōu)化問(wèn)題有哪些”知識(shí)都有一定的了解，大家如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。
