MySQL優化(1)--------常用的優化步驟

来源:https://www.cnblogs.com/jian0110/archive/2018/07/23/9356347.html
-Advertisement-
Play Games

在開始博客之前,還是同樣的給一個大概的目錄結構,實則即為一般MySQL的優化步驟 1、查看SQL的執行頻率 使用show status命令 2、定位哪些需要優化的SQL 通過慢查詢記錄+show processlist命令查看當前線程 3、分析為什麼SQL執行效率低 使用explain/desc命令 ...


在開始博客之前,還是同樣的給一個大概的目錄結構,實則即為一般MySQL的優化步驟

1、查看SQL的執行頻率---------------使用show status命令

2、定位哪些需要優化的SQL------------通過慢查詢記錄+show processlist命令查看當前線程

3、分析為什麼SQL執行效率低------------使用explain/desc命令分析

  • 相關列簡單解釋:type、table、select_type...

4、對症下藥採取優化措施-----------舉例採取index進行優化

  • 如何使用索引?
  • 使用索引應該註意的事項
  • 查看索引使用情況

主要參考資料:《深入淺出MySQL》,https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html

 


 

一、查看SQL執行頻率

  使用show [session|gobal] status命令瞭解SQL執行頻率、線程緩存內的線程的數量、當前打開的連接的數量、獲得的表的鎖的次數等。

比如執行show status like 'Com_%'查看每個語句執行的次數即頻率,其中Com_xxx中xxx表示就是語句,比如Com_select:執行select操作的次數。

 1 mysql> use test;
 2 Database changed
 3 mysql> show status like 'Com_%';
 4 +-----------------------------+-------+
 5 | Variable_name               | Value |
 6 +-----------------------------+-------+
 7 | Com_admin_commands          | 0     |
 8 | Com_assign_to_keycache      | 0     |
 9 | Com_alter_db                | 0     |
10 | Com_alter_db_upgrade        | 0     |
11 | Com_alter_event             | 0     |
12 | Com_alter_function          | 0     |
13 | Com_alter_instance          | 0     |
14 | Com_alter_procedure         | 0     |
15 | Com_alter_server            | 0     |
16 | Com_alter_table             | 0     |
17 | Com_alter_tablespace        | 0     |
18 | Com_alter_user              | 0     |
19 | Com_analyze                 | 0     |
20 | Com_begin                   | 0     |
21 | Com_binlog                  | 0     |
22 | Com_call_procedure          | 0     |
23 | Com_change_db               | 2     |
24 | Com_change_master           | 0     |
25 | Com_change_repl_filter      | 0     |
26 | Com_check                   | 0     |
27 | Com_checksum                | 0     |
28 | Com_commit                  | 0     |
29 | Com_create_db               | 0     |
30 | Com_create_event            | 0     |
31 | Com_create_function         | 0     |
32 | Com_create_index            | 0     |
  ..............................

比如執行show status like 'slow_queries'查看慢查詢次數(黑人問號??什麼是慢查詢呢?就是通過設置查詢時間閾值long_query_time(0-10s)並打開開關show_query_log(1=OFF/0=ON),當超過這個閾值的查詢都稱之為慢查詢,通常用來劃分執行SQL效率)

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set

比如執行show status like 'uptime'查看服務工作時間(即運行時間)

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 21645 |
+---------------+-------+
1 row in set

比如執行show status like 'connections'查看MySQL連接數:

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set

  通過show [session|gobal] status命令很清楚地看到哪些SQL執行效率不如人意,但是具體是怎麼個不如意法,還得繼續往下看,使用EXPLAIN命令分析具體的SQL語句

 二、定位效率低的SQL

  上面也提到過慢查詢這個概念主要是用來劃分效率低的SQL,但是慢查詢是在整個查詢結束後才記錄的,所以光是靠慢查詢日誌是跟蹤不了效率低的SQL。一般有兩種方式定位效率低的SQL:

  1、通過慢查詢日誌查看效率低的SQL語句,慢查詢日誌是通過show_query_log_file指定存儲路徑的,裡面記錄所有超過long_query_time的SQL語句(關於日誌的查看,日後再一步研究學習),但是需要慢查詢日誌的產生是在查詢結束後才有的。

  2、通過show processlist命令查看當前MySQL進行的線程,可以看到線程的狀態信息

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  2 | root | localhost:58377 | NULL | Sleep   | 2091 |          | NULL             |
|  3 | root | localhost:58382 | test | Sleep   | 2083 |          | NULL             |
|  4 | root | localhost:58386 | test | Sleep   | 2082 |          | NULL             |
|  5 | root | localhost:59092 | test | Query   |    0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
4 rows in set

  其中主要的是state欄位,表示當前SQL語句線程的狀態,如Sleeping 表示正在等待客戶端發送新請求,Sending data把查詢到的data結果發送給客戶端等等,具體請看https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

三、 查看分析效率低的SQL

  MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以後就可以EXPLAIN SELECT,UPDATE,DELETE,現在我們先創建一個user_table的表,之後分析select* from user where name=''語句

mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB;
Query OK, 0 rows affected

之後插入三條數據:

mysql> insert into user values(1,'Zhangsan',replace(UUID(),'-','')),(2,'Lisi',replace(UUID(),'-','')),(3,'Wangwu',replace(UUID(),'-',''));
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> select* from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

下麵以分析select*from user where name='Lisi'語句為例:

mysql> explain select*from user where name='Lisi';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

 

下麵講解select_type等常見列的含義的:

(1)select_type:表示SELECT的類型,主要有:

  • SIMPLE:簡單表,沒有表連接或者子查詢
  • PRIMARY:主查詢,即最外城的查詢
  • UNION:UNION中的第二個或者後面的語句
  • SUBQUERY:子查詢中的第一個SELECT

(2)table:結果輸出的表

(3)type:表示表的連接類型,性能由好到差為:

  • system:常量表
  • const:單表中最多有一行匹配,比如primary key,unique index
  • eq_ref:多表連接中使用primary key,unique index
  • ref:使用普通索引
  • ref_or_null:與ref類似,但是包含了NULL查詢
  • index_merge:索引合併優化
  • unique_subquery:in後面是一個查詢主鍵欄位的子查詢
  • index_subquery:in後面是非唯一索引欄位的子查詢
  • range:單表中範圍查看,使用like模糊查詢
  • index:對於後面每一行都通過查詢索引得到數據
  • all:表示全表查詢

(3)possible_key:查詢時可能使用的索引

(4)key:表示實際使用的索引

(5)key_len:索引欄位的長度

(6)rows:查詢時實際掃描的行數

(7)Extra:執行情況的說明和描述

(8)partitions:分區數目

(9)filtered:查詢過濾的表占的百分比,比如這裡查詢的記錄是name=Lisi的記錄,占三條記錄的33.3%

四、 關於索引的優化

1、使用索引優化的舉例

  上個例子我們看到到執行explain select*from user where name='Lisi',掃描了3行(全部行數)使用了全表搜索all。如果實際業務中name是經常用到查詢的欄位(是指經常跟在where後的欄位,不是select後的欄位)並且數據量很大的情況呢?這時候就需要索引了(索引經常用到where後面的欄位比select後面的欄位效果更好,或者說就是要使用在where後面的欄位上)

增加name首碼索引(這裡只是舉例,並沒有選擇最合適的首碼):

mysql> create index index_name on user(name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

執行explain分析

mysql> explain select*from user where name = 'Lisi';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

  可以看到type變為ref、rows降為1(實際上只要使用了索引都是1),filtered過濾百分比為100%,實際用到的索引為index_name。如果數據量很大的話使用索引就是很好的優化措施,對於如何選擇索引,什麼時候用索引,我做出瞭如下總結:

2、如何高效使用索引?

  (1) 創建多列索引時,只要查詢條件中用到最左邊的列,索引一般都會被用到

  我們創建一張沒有索引的表user_1:

mysql> show create table 
user_1;
+--------+--------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                             |
+--------+--------------------------------------------------------------------------------------------------------------------------+
| user_1 | CREATE TABLE `user_1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------+
 1 row in set

 之後同樣插入數據:

mysql> select *from user_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | Zhangsan |
|  2 | Lisi     |
+----+----------+
2 rows in set

 創建多列索引index_id_name

mysql> create index index_id_name on user_1(id,name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 實驗查詢explain分析name與id

mysql> explain select * from user_1 where id=1;
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_1 | NULL       | ref  | index_id_name | index_id_name | 5       | const |    1 |      100 | Using index |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set

mysql> explain select * from user_1 where name='Lisi';
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user_1 | NULL       | index | NULL          | index_id_name | 38      | NULL |    2 |       50 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set

  可以看到使用最左列id的時候,rows為1,並且Extra明確使用了index,key的值為id_name_index,type的值為ref,而where不用到id,而是name的話,rows的值為2。filtered為50%,雖然key是index_id_name,但是表明是索引(個人理解,應該不太準確)

  (2) 使用like的查詢,只有%不是第一個字元並且%後面是常量的情況下,索引才可能會被使用。

   執行explain select *from user where name like ‘%Li’後type為ALLkey的值為NULL,執行explain select *from user where name like ‘Li%’後key值不為空為index_name。

mysql> explain select*from user where name like '%Li';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
mysql> explain select*from user where name like 'Li%';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | index_name    | index_name | 9       | NULL |    1 |      100 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set

  (3) 如果對打的文本進行搜索,使用全文索引而不是用like ‘%...%’(只有MyISAM支持全文索引)

  (4) 如果列名是索引,使用column_name is null將使用索引

mysql> explain select*from user where name is null;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

mysql> explain select*from user where password
 is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

3、哪些情況下即使有索引也用不到?

  (1) MySQL使用MEMORY/HEAP引擎(使用的HASH索引),並且WHERE條件中不會使用”=”,in等進行索引列,那麼不會用到索引(這是關於引擎部分特點,之後會介紹)。

  (2) 用OR分隔開的條件,如果OR前面的條件中的列有索引,而後面的列沒有索引,那麼涉及到的列索引不會被使用。

  執行命令show index from user可以看出password欄位並沒有使用任何索引,而id使用了兩個索引,但是where id=1 or password='2d7284808e5111e8af74201a060059ce' 導致沒有使用id列的primary索引與id_name_index索引

mysql> show index from user;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY       |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name    |            1 | name        | A         |           3 |        2 | NULL   | YES  | BTREE      |         |               |
| user  |          1 | id_name_index |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | id_name_index |            2 | name        | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set

mysql> explain select*	   

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 環境準備最少兩台主機一臺當DNS伺服器,一臺當客戶機如:192.168.43.7這台主機當DNS伺服器,192.168.43.6這台主機當客戶機安裝DNS服務yum install -y bind##備註:實現先關閉防火牆開啟服務systemctl start named##更改主配置文件vim /... ...
  • 一、運行容器 1、基於鏡像新建一個容器並啟動 指明瞭 -d 運行鏡像,會返回容器的 id;如果不指明 -d 運行鏡像,會列印出 catalina.out 的 日誌,在 [crtl +c] 後,容器即停止運行。 當利用 docker run 來創建容器時,Docker 在後臺運行的標準操作包括: -- ...
  • top命令命令用於實時監測系統資源使用狀況,包含,進程、cpu,記憶體等 1、用法 top [選項] 2、命令選項 -b 以批處理模式操作-c 顯示完整的治命令-d 屏幕刷新間隔時間-I 忽略失效過程-s 保密模式-S 累積模式-i<時間> 設置刷新間隔時間-u<用戶名> 指定用戶名-p<進程號> 指 ...
  • 第1章 Linux啟動過程 開機自檢(BIOS)##硬體檢查 MBR引導 GRUB菜單(選擇不同的內核) 載入內核 運行init進程(Linux系統裡面第一個進程) 讀取/etc/inittab配置文件(讀取運行級別) 執行/etc/rc.d/sysinit腳本(系統的初始化腳本 設置IP地址) 執 ...
  • 1、下載zxing源碼 git clone https://github.com/15903016222/zxing-cpp.git 2、安裝依賴工具cmake sudo apt-get install cmake cmake-qt-gui 在此安裝中需要使用3.0以上版本的cmake工具,而ubu ...
  • 簡介DNS是用來名字解析的,名字解析成IP地址,IP地址解析成名字,正反操作,有伺服器端和客戶端即 S/CDNS是應用層協議,基於UDP/53、TCP/53埠,缺一不可分為正向解析和反向解析/遞歸查詢、迭代查詢註意:正反向解析是兩個不同的名稱空間,是兩棵不同的解析樹名稱解析:主機名解析把一種名稱轉... ...
  • 最近使用 iDrac 和 iLO 總是會使用到 .jnlp 文件, 為了方便,今天把設置過程記錄下來。 JNLP 文件,全名為 Java Network Launching Protocol 文件, 我們需要 javaws 命令去執行它。 在 fedora 中 ,我們需要安裝 相應的 rpm 包,y ...
  • 基於centos7靜默安裝oracle 11gr2單實例資料庫 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...