閱讀本文你的收獲
- 了解系統(tǒng)運行效率提升的整體解決思路和方向
- 學會MySQl中進行數(shù)據(jù)庫查詢優(yōu)化的步驟
- 學會看慢查詢、執(zhí)行計劃、進行性能分析、調優(yōu)
一、問題：如果你的系統(tǒng)運行很慢，你有什么解決方案？
?關于這個問題，我們通常首先考慮的是硬件升級，畢竟服務器的內存、CPU、磁盤IO速度 、網(wǎng)絡速度等都是制約我們系統(tǒng)快慢的首要因素。硬件的升級相對來說比較容易，花點錢買臺好點的服務器就行了。如果你用的是云服務器，那就更Easy了，花錢升級增配就行了，幾分鐘就完成了。
那程序（軟件）層面我們怎么進行優(yōu)化呢？我想，主要是以下幾個方面：
- 前端方面的優(yōu)化
頁面緩存、前端框架層面的優(yōu)化等 - 應用程序方面的優(yōu)化：
- 代碼層面進行一些優(yōu)化，如采用更加適合的數(shù)據(jù)容器、處理邏輯/算法方便的改進；
- 采用性能更好的數(shù)據(jù)庫訪問技術，如Dapper比EF性能好，執(zhí)行原生SQL語句比用ORM快等
- 引入緩存機制，緩存熱點數(shù)據(jù)以減輕數(shù)據(jù)庫I/O壓力
- 采用異步編程，提高接口吞吐量等
- 數(shù)據(jù)庫層面的優(yōu)化
- 識別慢查詢
- 加索引
- 通過執(zhí)行計劃，優(yōu)化SQL語句
- 采用存儲過程
- 讀寫分離
- 分庫分表等
除此以外，還有Web服務器，如IIS服務器的調優(yōu)、系統(tǒng)架構層面的集群優(yōu)化等，本文主要分享的是數(shù)據(jù)庫層面的調優(yōu)。
案例的演示環(huán)境：
操作系統(tǒng)：Windows10 專業(yè)版
MySQL版本：MySql 5.7.17
MySql客戶端：Navicat for MySql
開發(fā)框架：ASP.NET Core6.0
二、如何進行數(shù)據(jù)庫調優(yōu)？
2.1 查出那些查詢很慢的SQL語句
慢查詢（slow_query)：是指MySQL中響應時間超過閥值(long_query_time，單位：秒)的SQL語句。
MySQL的慢查詢日志是MySQL提供的一種日志記錄，專門用來記錄慢查詢?？梢园凑找韵碌牟襟E來開啟和查看慢查詢日志：
-
修改一下mysql的配置文件，然后重啟mysql服務器
我們首先要知道
windows下的配置文件在哪里？如何查看？配置哪些選項？在mysql命令行界面，輸入以下命令：
mysql> show variables like ‘%data%’;
打開my.ini配置文件，并添加/修改如下的配置參數(shù)：
[mysqld]
…
slow-query-log=1 #開啟慢查詢日志，默認為1，即開啟
#slow_query_log_file=“WANGXF-PC-slow.log”
slow_query_log_file=D:/mysql/slow.log #慢查詢日志的記錄文件路徑
#long_query_time=10
long_query_time=1 #查詢超過1秒語句全部記錄，默認值是10秒
log_queries_not_using_indexes=1 #沒有使用索引的查詢全部記錄
改完之后重啟mysql的服務
2. 測試是否記錄慢查詢！此處用sleep函數(shù)來模擬一個很慢的查詢，花5秒。
打開“D:/mysql/slow.log”查看是否記錄了慢查詢日志：
3. 慢查詢分析工具mysqldumpslow
mysqldumpslow是MySQL自帶的分析慢查詢的工具。 該命令格式如下：
mysqldumpslow [ OPTS…][ LOGS…]
mysqldumpslow是一個perl腳本，只需下載并賦權即可執(zhí)行。所以我們首先需要在windows下安裝Perl，安裝過程很簡單，從官網(wǎng) http://strawberryperl.com/ 下載windows安裝包，安裝好之后，測試perl -v，如果能顯示版本號，表示安裝成功。
幾個常用的命令
- 得到返回記錄集最多的10條SQL：
perl mysqldumpslow.pl -s r -t 10 D:/mysql/slow.log
- 得到訪問次數(shù)最多的10條SQL：
perl mysqldumpslow.pl -s r -t 10 D:/mysql/slow.log
- 得到按照時間排序的前10條里面含有左連接的SQL：
perl mysqldumpslow.pl -s t -t 10-g “l(fā)eft join” D:/mysql/slow.log
2.2 通過explain查看執(zhí)行計劃
通過上面的步驟鎖定了哪些查詢是慢查詢，接下來我們可以用explain命令查看執(zhí)行計劃，看看是否命中了索引。如果沒有索引則創(chuàng)建索引，如果創(chuàng)建了索引但是沒有起作用，則可以優(yōu)化SQL語句來使索引生效。
創(chuàng)建索引后，再去分析，可以看到他已經(jīng)命中索引了：
explain命令的執(zhí)行結果每一列的說明:
| 信息 | 描述 |
|---|---|
| id | 查詢的序號，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序 兩種情況 id相同，執(zhí)行順序從上往下 id不同，id值越大，優(yōu)先級越高，越先執(zhí)行 |
| select_type | 查詢類型，主要用于區(qū)別普通查詢，聯(lián)合查詢，子查詢等的復雜查詢 1、simple ——簡單的select查詢，查詢中不包含子查詢或者UNION 2、primary ——查詢中若包含任何復雜的子部分，最外層查詢被標記 3、subquery——在select或where列表中包含了子查詢 4、derived——在from列表中包含的子查詢被標記為derived（衍生），MySQL會遞歸執(zhí)行這些子查詢，把結果放到臨時表中 5、union——如果第二個select出現(xiàn)在UNION之后，則被標記為UNION，如果union包含在from子句的子查詢中，外層select被標記為derived 6、union result:UNION 的結果 |
| table | 輸出的行所引用的表 |
| type | 顯示聯(lián)結類型，顯示查詢使用了何種類型，按照從最佳到最壞類型排序 1、system：表中僅有一行（=系統(tǒng)表）這是const聯(lián)結類型的一個特例。 2、const：表示通過索引一次就找到，const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以如果將主鍵置于where列表中，mysql能將該查詢轉換為一個常量。 3、eq_ref:唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于唯一索引或者主鍵掃描。 4、ref:非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，可能會找多個符合條件的行，屬于查找和掃描的混合體。 5、range:只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是where語句中出現(xiàn)了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好，因為它開始于索引的某一個點，而結束另一個點，不用全表掃描。 6、index:index 與all區(qū)別為index類型只遍歷索引樹。通常比all快，因為索引文件比數(shù)據(jù)文件小很多。 7、all：遍歷全表以找到匹配的行。 注意:一般保證查詢至少達到range級別，最好能達到ref。 |
| possible_keys | 指出MySQL能使用哪個索引在該表中找到行 |
| key | 顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。查詢中如果使用覆蓋索引，則該索引和查詢的select字段重疊。 |
| key_len | 表示索引中使用的字節(jié)數(shù)，該列計算查詢中使用的索引的長度 在不損失精度的情況下，長度越短越好。如果鍵是NULL,則長度為NULL。該字段顯示為索引字段的最大可能長度，并非實際使用長度。 |
| ref | 顯示索引的哪一列被使用了，如果有可能是一個常數(shù)，哪些列或常量被用于查詢索引列上的值 |
| rows | 根據(jù)表統(tǒng)計信息以及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù) |
| Extra | 包含不適合在其他列中顯示，但是十分重要的額外信息 1、Using filesort：說明mysql會對數(shù)據(jù)適用一個外部的索引排序。而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成排序操作稱為“文件排序” 2、Using temporary:使用了臨時表保存中間結果，mysql在查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by。 3、Using index:表示相應的select操作用使用覆蓋索引，避免訪問了表的數(shù)據(jù)行。如果同時出現(xiàn)using where，表名索引被用來執(zhí)行索引鍵值的查找；如果沒有同時出現(xiàn)using where，表名索引用來讀取數(shù)據(jù)而非執(zhí)行查詢動作。 4、Using where :表明使用where過濾 5、using join buffer:使用了連接緩存 6、impossible where:where子句的值總是false，不能用來獲取任何元組 7、select tables optimized away：在沒有group by子句的情況下，基于索引優(yōu)化Min、max操作或者對于MyISAM存儲引擎優(yōu)化count（*），不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。 8、distinct：優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作。 |
2.3 如何進一步進行查詢性能分析？
profiling性能分析，可以更進一步去分析每一句sql語句的cpu性能及內存占用。
- 該功能默認是關閉的，要開啟profiling功能，輸入以下命令：
執(zhí)行查詢語句，接著顯示所有查詢的性能分析結果：
mysql> select * from syslog;
顯示query_id為1的查詢的性能報告：
2.4 數(shù)據(jù)庫調優(yōu)的手段
-
索引優(yōu)化
可以對where條件后面的條件字段，和order by/group by后面的排序、分組字段創(chuàng)建索引，以提高查詢速度。 -
SQL語句優(yōu)化
- select 時不用* 返回所有字段，查詢具體字段可以提高一定速度
- 不用is null或is not null、
- not in/in 不能使，用exists查詢替代
- 不能用like ‘%xxx’，%開頭的模糊查詢用不到索引
- 少用不等于 !=、大于>、小于<的查詢
- or查詢用union查詢替代
- …
-
MySQL實現(xiàn)讀寫分離
一般數(shù)據(jù)庫都是讀多而寫少，如果數(shù)據(jù)量一大，單體數(shù)據(jù)庫服務器造成性能瓶頸，可以采用MySQL數(shù)據(jù)庫集群實現(xiàn)讀寫分離，一主多從。主庫負責寫操作，多個從庫只負責讀操作。 -
分庫、分表
按業(yè)務系統(tǒng)不同，使用不同的數(shù)據(jù)庫，即“分庫”。
分表則分為垂直分表和水平分表。
（注：不做展開了，后續(xù)專門發(fā)文探討）文章來源：http://www.zghlxwxcb.cn/news/detail-770434.html
本次主要分享的是MySQL數(shù)據(jù)庫的性能優(yōu)化思路，如果本文對你有幫助的話，請點贊+評論+關注，或者轉發(fā)給需要的朋友。文章來源地址http://www.zghlxwxcb.cn/news/detail-770434.html
到了這里，關于【性能優(yōu)化】MySql數(shù)據(jù)庫查詢優(yōu)化方案的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
