前言
在專案中，我們用的sql陳述句，無非就是CRUD，小老犇統計了數百臺資料庫服務器（胡說八道），得出結論，它們百分之八十的性能用在了查詢上，修改，新增，洗掉三個模塊共同占了百分之二十，
所以，我們對SQL的優化，基本體現在select上，
步驟一：舉例說明SQL時間概念
在這里我舉幾個例子來說明，讓大家對sql執行時間有一個簡單的概念，
場景：
我現在有兩張表，一張表有5000條資料，另一張表有50000條資料，
audit_legal ：5000條資料，
resource：50000條資料，
案例一：查詢1000條資料，兩張表速度一樣嗎，
一個用了21ms，一個用了243ms，得出結果，
就算查詢同樣的資料，如果基數不一樣，那么查詢耗時不同，
原因如下：
在沒有索引的情況下，我們執行一條sql陳述句，表是進行全域遍歷，磁盤尋址，就算邏輯上相鄰的記錄在磁盤上也并不一定是物理相鄰的，基數越大，查詢時間也就越長，
專案中訪問介面的請求回應時間一般在200ms以內，高于200ms的介面，嘿嘿，要求不高就沒問題，
注意一點：
直接在MySQL可視化界面執行sql的時間要遠大于訪問這個介面獲得回應的時間，因為子彈要飛一會，
案例二：增加索引，查詢效率提升有多少倍，
在resource這張表進行測驗，
分兩次：
1、通過主鍵進行查詢，
2、通過普通欄位進行查詢，
用一張表，同一條資料：
一個幾乎不耗時，0.00025s，另一個用了1.378s，所以索引很牛逼，如果有人問你，增加索引，查詢效率提升有多少倍，那他一定是個錘子，不同基數，查詢效率倍數不一樣，
再問一個問題，MySQL資料庫單表資料量為多少時，效率基本就達到了極限，
一千萬條資料量，答案在這里：
https://blog.csdn.net/numbbe/article/details/109300087
步驟二：開啟資料庫分析功能
開啟之前，先介紹一個英文單詞：profiling
它的中文是分析，接下來會一直用到它，
輸入命令列：
SHOW VARIABLES LIKE ‘%pro%’;
我們看到了兩個引數：
profiling：
確定分析陳述句功能是否開啟，mysql默認off/0關閉，on/1為開啟，
開啟后，之后執行的SQL，mysql服務器則會記錄該條sql的系統開銷，比如cpu，io，記憶體消耗等，
profiling_history_size：
保留分析profiling數量，范圍為0-100，為0時，即關閉該功能，
步驟三：MySQL教你怎么玩profiling
我說的肯定沒有mysql教的專業，所以我們看MySQL怎么說，
輸入命令列：
HELP PROFILE;
我將mysql的example，拷貝到這里，
哇，好長啊，別看了，往下翻，我把尿文翻譯一下，
NAME description EXAMPLE
SHOW PROFILE Syntax:
SHOW PROFILE [TYPE [, TYPE] ... ]
[FOR QUERY n]
[LIMIT ROW_COUNT [OFFSET OFFSET]]
TYPE: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}
The SHOW PROFILE AND SHOW PROFILES statements display profiling
information that indicates resource USAGE FOR statements executed
during the course of the current session.
*Note*:
The SHOW PROFILE AND SHOW PROFILES statements are deprecated AND will
be removed IN a future MySQL release. USE the Performance SCHEMA
instead; see
https://dev.mysql.com/doc/refman/5.7/en/performance-SCHEMA-QUERY-profil
ing.html.
TO control profiling, USE the profiling SESSION variable, which has a
DEFAULT VALUE of 0 (OFF). ENABLE profiling BY setting profiling TO 1 OR
ON:
mysql> SET profiling = 1;
SHOW PROFILES displays a LIST of the most recent statements sent TO the
server. The size of the LIST IS controlled BY the
profiling_history_size SESSION variable, which has a DEFAULT VALUE of
15. The maximum VALUE IS 100. Setting the VALUE TO 0 has the practical
effect of disabling profiling.
ALL statements are profiled except SHOW PROFILE AND SHOW PROFILES, so
you will find neither of those statements IN the PROFILE list.
Malformed statements are profiled. FOR EXAMPLE, SHOW PROFILING IS an
illegal statement, AND a syntax error occurs IF you try TO EXECUTE it,
but it will SHOW up IN the profiling list.
SHOW PROFILE displays detailed information about a single statement.
Without the FOR QUERY n clause, the output pertains TO the most
recently executed statement. IF FOR QUERY n IS included, SHOW PROFILE
displays information FOR statement n. The VALUES of n correspond TO the
Query_ID VALUES displayed BY SHOW PROFILES.
The LIMIT ROW_COUNT clause may be given TO LIMIT the output TO
ROW_COUNT rows. IF LIMIT IS given, OFFSET OFFSET may be added TO BEGIN
the output OFFSET ROWS INTO the FULL SET of rows.
BY DEFAULT, SHOW PROFILE displays STATUS AND Duration columns. The
STATUS VALUES are LIKE the State VALUES displayed BY SHOW PROCESSLIST,
although there might be SOME minor differences IN interpretion FOR the
two statements FOR SOME STATUS VALUES (see
https://dev.mysql.com/doc/refman/5.7/en/thread-information.html).
Optional TYPE VALUES may be specified TO display SPECIFIC additional
TYPES of information:
o ALL displays ALL information
o BLOCK IO displays counts FOR block input AND output operations
o CONTEXT SWITCHES displays counts FOR voluntary AND involuntary
context switches
o CPU displays USER AND system CPU USAGE times
o IPC displays counts FOR messages sent AND received
o MEMORY IS NOT currently implemented
o PAGE FAULTS displays counts FOR major AND minor page faults
o SOURCE displays the NAMES of functions FROM the source CODE, together
WITH the NAME AND line number of the FILE IN which the FUNCTION
occurs
o SWAPS displays swap counts
Profiling IS enabled per session. WHEN a SESSION ENDS, its profiling
information IS lost.
URL: https://dev.mysql.com/doc/refman/5.7/en/SHOW-profile.html
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 ROW IN SET (0.00 sec)
mysql> SET profiling = 1;
QUERY OK, 0 ROWS affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
QUERY OK, 0 ROWS affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
QUERY OK, 0 ROWS affected (0.01 sec)
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | QUERY |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 ROWS IN SET (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| STATUS | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating TABLE | 0.000056 |
| AFTER CREATE | 0.011363 |
| QUERY END | 0.000375 |
| freeing items | 0.000089 |
| logging slow QUERY | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 ROWS IN SET (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| STATUS | Duration |
+--------------------+----------+
| QUERY END | 0.000107 |
| freeing items | 0.000008 |
| logging slow QUERY | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 ROWS IN SET (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| STATUS | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating TABLE | 0.000056 | 0.000028 | 0.000028 |
| AFTER CREATE | 0.011363 | 0.000217 | 0.001571 |
| QUERY END | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow QUERY | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 ROWS IN SET (0.00 sec)
這里講了如何使用profile命令，介紹了它的語法，介紹了它的引數，
SHOW PROFILE Syntax:
語法
SHOW PROFILE [TYPE [, TYPE] ... ] type是下面的type
[FOR QUERY n] 這里的n是sql陳述句代號，
[LIMIT ROW_COUNT [OFFSET OFFSET]]
引數
TYPE: {
ALL 展示所有的開銷資訊，
| BLOCK IO IO開銷，
| CONTEXT SWITCHES 背景關系開銷，
| CPU CPU開銷，
| IPC 發送和接受開銷，
| MEMORY 記憶體開銷，
| PAGE FAULTS 頁面錯誤開銷，
| SOURCE source開銷，
| SWAPS 交換次數開銷，
}
舉例說明：
SHOW PROFILE CPU FOR QUERY 2; 查詢2號SQL的CPU開銷情況，
下面是一段英譯中，用CSDN翻譯的（在CSDN里不給某翻譯打廣告），有能力的同學可以看上邊的英文，
博主建議大家，將這段中文拷貝到自己的notepad++之類檔案打開，這里看著實在不順眼，太小了，
這段話最好還是看一看，不要因為我說是翻譯的就不看，
里邊不僅僅是翻譯，有我自己的話在其中，有很多知識，
一共十一句話，
1、SHOW PROFILE和SHOW PROFILES陳述句根據組態檔顯示陳述句在本次session期間執行的資源使用情況的資訊，
2、SHOW PROFILE和SHOW PROFILES陳述句已棄用并將在未來的MySQL版本中洗掉，使用Performance Schema替代，
# 納尼，以后就沒profile這個東西了，大家散了吧，
3、要控制分析，請使用分析會話變數，該變數具有默認值為0（關閉），通過將分析設定為1或啟用分析on：
4、SHOW PROFILES顯示發送到的最新陳述句的串列,服務器串列的大小由profile_history_size會話變數決定，默認值為15最大值為100，將值設定為0具有禁用分析的效果，
5、除SHOW PROFILE和SHOW PROFILES外，所有陳述句都將被分析，因此您在概要檔案串列中找不到這些陳述句，
6、profile格式錯誤的陳述句，例如，SHOW PROFILING是一個非法陳述句，如果試圖執行該陳述句，則會出現語法錯誤，但它會出現在分析串列中，
7、show profile陳述句顯示有關單個陳述句的詳細資訊，如果沒有for query n子句，則輸出屬于最近執行的陳述句，如果包含FOR QUERY n，則顯示PROFILE，顯示陳述句n的資訊，n的值對應于查詢SHOW PROFILES顯示的ID值，
8、可以使用LIMIT ROW_COUNT子句將輸出限制為數行，如果給定了限制，則可以在開始處添加偏移偏移量，將輸出偏移行放入完整的行集合中，
9、默認情況下，show profile顯示狀態和持續時間列，即兩個欄位，狀態值類似于SHOW PROCESSLIST顯示的狀態值，可以指定可選型別值來顯示特定的附加值的資訊型別，
10、show profile陳述句引數型別解釋,和上邊的九個引數一一對應.
ALL 全部顯示所有資訊
BLOCK IO 塊IO顯示塊輸入和輸出操作的計數
CONTEXT SWITCHES 背景關系切換
CPU CPU顯示用戶和系統CPU使用時間
IPC IPC顯示發送和接收的訊息計數
MEMORY 當前未實作記憶體
PAGE FAULTS 頁面錯誤顯示主要和次要頁面錯誤的計數
SOURCE SOURCE顯示源代碼中函式的名稱或者函式所在檔案的名稱和行號
SWAPS 交換顯示交換計數
11、為每個會話啟用分析，會話結束時，其分析資訊將丟失，
步驟四：決議MySQL的教學example，
執行命令列，開啟sql分析功能：
SHOW VARIABLES LIKE ‘%pro%’;
SET profiling = 1;
SELECT @@profiling; 用來查看是否開啟
執行命令：
SELECT COUNT(*) FROM laoben;
SHOW PROFILES;
可以看出上方select命令的query_id為5，消耗時間為0.00078257s，
執行命令：
SHOW PROFILE;
該命令顯示的是上一條sql陳述句的執行情況，默認包括兩個欄位，
status，duration，狀態和持續時間，
執行命令：指定欄位，指定query_id，查看cpu,io開銷，
SHOW PROFILE cpu,block io FOR QUERY 11;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/207055.html
標籤：java
上一篇：《Oracle MySQL編程自學與面試指南》01：入門（Getting Started）
下一篇：狂神老師的MySQL學習筆記