當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL优化之三：SQL语句优化

發布時間：2024/4/17 数据库 39 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL优化之三：SQL语句优化小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一 SQL語句優化的一般步驟：

1 通過show status命令了解各種SQL語句的執行頻率

mysql> show status;????????????????#show status：顯示服務器狀態信息

+-----------------------------------------------+-------------+

| Variable_name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | Value ? ? ? |

+-----------------------------------------------+-------------+

| Aborted_clients ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | 0 ? ? ? ? ? |

| Aborted_connects ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? ? ? |

| Binlog_cache_disk_use ? ? ? ? ? ? ? ? ? ? ? ? | 0 ? ? ? ? ? |

| Binlog_cache_use ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 8 ? ? ? ? ? |

| Binlog_stmt_cache_disk_use ? ? ? ? ? ? ? ? ? ?| 0 ? ? ? ? ? |

| Binlog_stmt_cache_use ? ? ? ? ? ? ? ? ? ? ? ? | 25 ? ? ? ? ?|

| Bytes_received ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 2919 ? ? ? ?|

| Bytes_sent ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 51750 ? ? ? |

......

mysql> show status like "com%";????#顯示當前session中，統計參數的值

+---------------------------+-------+

| Variable_name ? ? ? ? ? ? | Value |

+---------------------------+-------+

| Com_admin_commands ? ? ? ?| 0 ? ? |

| Com_assign_to_keycache ? ?| 0 ? ? |

| Com_alter_db ? ? ? ? ? ? ?| 0 ? ? |

| Com_alter_db_upgrade ? ? ?| 0 ? ? |

| Com_alter_event ? ? ? ? ? | 0 ? ? |

| Com_alter_function ? ? ? ?| 0 ? ? |

| Com_alter_procedure ? ? ? | 0 ? ? |

| Com_alter_server ? ? ? ? ?| 0 ? ? |

| Com_alter_table ? ? ? ? ? | 2 ? ? |

| Com_alter_tablespace ? ? ?| 0 ? ? |

| Com_alter_user ? ? ? ? ? ?| 0 ? ? |

| Com_analyze ? ? ? ? ? ? ? | 0 ? ? |

| Com_begin ? ? ? ? ? ? ? ? | 0 ? ? |

......

Com_xxx：表示每個xxx語句執行的次數，以下幾個統計參數非常重要：

Com_select：執行select的次數，一次查詢累計加1
Com_insert：執行insert操作的次數，批量插入只累加1
Com_delete：執行delete操作的次數，
Com_update：執行update操作的次數，

以上參數針對所有存儲引擎的表操作。

下面的參數是針對InnoDB存儲引擎的，算法也稍有不同：

Innodb_rows_read：select查詢返回的行數

Innodb_rows_inserted：執行insert操作插入的行數

Innodb_rows_updated：執行update操作更新的行數

Innodb_rows_deleted：執行delete操作刪除的行數

通過以上參數的了解，可以判斷出當前數據庫是以插入更新為主還是以查詢操作為主，以及各種類型SQL大致的執行比例是多少。

此外，以下幾個參數可以幫助用戶了解數據庫的基本情況：

Uptime：數據庫服務器的工作時間

Connections：試圖連接服務器的次數

Slow_queries：慢查詢的次數

2 定位執行效率低的SQL語句

方式1：通過慢查詢日志定位

方式2：查看當前正在進行的線程

mysql> show processlist;? ??

+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+

+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+

或

[root@localhost ~]# mysqladmin -uroot -h 127.0.0.1 processlist -proot

Warning: Using a password on the command line interface can be insecure.

+----+------+-----------------+----+---------+------+-------+------------------+

+----+------+-----------------+----+---------+------+-------+------------------+

| 1 ?| root | localhost ? ? ? | ? ?| Sleep ? | 265 ?| ? ? ? | ? ? ? ? ? ? ? ? ?|

+----+------+-----------------+----+---------+------+-------+------------------+

備注：show processlist;只列出前100條，如果想全列出請使用show full processlist;

3 通過explain分析低效的SQL語句的執行

通過之前的步驟查詢到效率低的SQL語句之后，可以通過explain命令獲取MySQL是如何執行select語句的信息。如：

mysql> explain select * from emp1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

select_type——select類型
table——輸出結果的表
type——表示MySQL在表中找到所需行的方式，或者叫訪問類型，常見有以下幾種：性能由最差到最好。

type=all，即通過全表掃描找到匹配的行。

type=index，索引全掃描，mysql遍歷索引才找到匹配的行。

type=range，索引范圍掃描，

type=ref，使用非唯一索引掃描，或唯一索引的前綴掃描，返回匹配某個單獨值的記錄行

type=eq_ref，類似ref，區別在于使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配。

type=const/system，表單中最多有一個匹配行，查詢起來非常迅速。如根據主鍵和唯一索引進行的查詢。

type=null，不需要訪問表或索引，直接就可以得到結果。

possible_keys——表示查詢時可能使用的索引
key——表示實際使用的索引
key_len——使用到索引字段的長度
rows——掃描行的數量
Extra——執行情況的說明和描述

4 通過show profile了解分析SQL執行的過程

mysql> select @@have_profiling;????????#查看是否支持

+------------------+

| @@have_profiling |

+------------------+

| YES ? ? ? ? ? ? ?|

+------------------+

mysql> set profiling=1;????????????????#開啟profiling，默認是關閉

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from emp1;???????????? #執行一個語句

+------+--------+-------+------------+

+------+--------+-------+------------+

| ?111 | ? ? ?4 | ccc ? | 2011-11-30 |

| ?666 | ? ? 11 | ddd ? | 2014-12-22 |

| ?888 | ? ? 22 | eee ? | 2015-11-30 |

| ?333 | ? ? ?8 | fff ? | 2011-04-30 |

+------+--------+-------+------------+

4 rows in set (0.02 sec)

mysql> show profiles;????????????????? #查看當前SQL語句的查詢ID

+----------+------------+---------------------------+

| Query_ID | Duration ? | Query ? ? ? ? ? ? ? ? ? ? |

+----------+------------+---------------------------+

| ? ? ? ?1 | 0.01696625 | select count(*) from emp1 |

| ? ? ? ?2 | 0.02623125 | select * from emp1 ? ? ? ?|

+----------+------------+---------------------------+

mysql> show profile for query 2;? ? ? ?#查看執行過程中線程的每個狀態和消耗時間

+----------------------+----------+

| Status ? ? ? ? ? ? ? | Duration |

+----------------------+----------+

| starting ? ? ? ? ? ? | 0.000111 |

| checking permissions | 0.000019 |

| Opening tables ? ? ? | 0.000046 |

| init ? ? ? ? ? ? ? ? | 0.000043 |

| System lock ? ? ? ? ?| 0.000031 |

| optimizing ? ? ? ? ? | 0.000016 |

| statistics ? ? ? ? ? | 0.000039 |

| preparing ? ? ? ? ? ?| 0.000023 |

| executing ? ? ? ? ? ?| 0.000008 |

| Sending data ? ? ? ? | 0.025442 |

| end ? ? ? ? ? ? ? ? ?| 0.000020 |

| query end ? ? ? ? ? ?| 0.000014 |

| closing tables ? ? ? | 0.000016 |

| freeing items ? ? ? ?| 0.000326 |

| cleaning up ? ? ? ? ?| 0.000079 |

+----------------------+----------+

Sending data表示MySQL線程開始訪問數據行并把結果返回給客戶端。通常是整個查詢中耗時最長的狀態

mysql> show profile cpu for query 2;????#查看耗費CPU的時間，Sending data主要耗費在CPU上

+----------------------+----------+----------+------------+

+----------------------+----------+----------+------------+

| starting ? ? ? ? ? ? | 0.000111 | 0.000000 | ? 0.000000 |

| checking permissions | 0.000019 | 0.000000 | ? 0.000000 |

| Opening tables ? ? ? | 0.000046 | 0.000000 | ? 0.000000 |

| init ? ? ? ? ? ? ? ? | 0.000043 | 0.000000 | ? 0.000000 |

| System lock ? ? ? ? ?| 0.000031 | 0.000000 | ? 0.000000 |

| optimizing ? ? ? ? ? | 0.000016 | 0.000000 | ? 0.000000 |

| statistics ? ? ? ? ? | 0.000039 | 0.000000 | ? 0.000000 |

| preparing ? ? ? ? ? ?| 0.000023 | 0.000000 | ? 0.000000 |

| executing ? ? ? ? ? ?| 0.000008 | 0.000000 | ? 0.000000 |

| Sending data ? ? ? ? | 0.025442 | 0.000000 | ? 0.001999 |

| end ? ? ? ? ? ? ? ? ?| 0.000020 | 0.000000 | ? 0.000000 |

| query end ? ? ? ? ? ?| 0.000014 | 0.000000 | ? 0.000000 |

| closing tables ? ? ? | 0.000016 | 0.000000 | ? 0.000000 |

| freeing items ? ? ? ?| 0.000326 | 0.000000 | ? 0.000000 |

| cleaning up ? ? ? ? ?| 0.000079 | 0.000000 | ? 0.000000 |

+----------------------+----------+----------+------------+

mysql> show profile all for query 1\G ?#查看所有明細，了解MySQL在什么資源上耗費了過高的時間

5 通過trace分析優化器如何選擇執行計劃

6 確定問題之后，采取相應的措施優化

由前面的步驟確認對表進行全表掃描，導致查詢效果不理想，那么對表的某個字段建立索引。具體如下：

mysql> create index index_ename on emp1(ename);

Query OK, 0 rows affected (0.25 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

建立索引后，再看下這條語句的執行狀態：

mysql> explain select ename from emp1;

建立索引后，可以發現對表掃描的行數大大減少，提高了對表的訪問速度。

二索引問題

索引是數據庫優化中最重要也是最常用的手段之一，通過索引可以幫助用戶解決大多數SQL性能問題。

1 索引的存儲分類：索引是在存儲引擎層中實現的

B-Tree索引：最常見的索引，大部分引擎支持B樹索引。
HASH索引：只有Memory引擎支持，使用場景簡單
Full-text（全文索引）：一種特殊索引類型

創建索引方式 1：

mysql>?create?index index_age1 on emp1(age1);

Query OK, 0 rows affected (0.15 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

創建索引方式 2：

mysql>?alter table?zwj.emp1 add index index_ename (ename);

Query OK, 0 rows affected (0.05 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

查看索引：

mysql> show index from zwj.emp1;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| emp1 ?| ? ? ? ? ?1 | index_ename | ? ? ? ? ? ?1 | ename ? ? ? | A ? ? ? ? | ? ? ? ? ? 4 | ? ? NULL | NULL ? | YES ?| BTREE ? ? ?| ? ? ? ? | ? ? ? ? ? ? ? |

| emp1 ?| ? ? ? ? ?1 | index_age1 ?| ? ? ? ? ? ?1 | age1 ? ? ? ?| A ? ? ? ? | ? ? ? ? ? 4 | ? ? NULL | NULL ? | YES ?| BTREE ? ? ?| ? ? ? ? | ? ? ? ? ? ? ? |

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

刪除索引：

mysql> drop index index_age1 on zwj.emp1;

Query OK, 0 rows affected (0.06 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

或

mysql> alter table zwj.emp1 drop index index_ename;

Query OK, 0 rows affected (0.04 sec)

Records: 0 ?Duplicates: 0 ?Warnings: 0

另有復合索引：需要咨詢開發人員

創建復合索引（將最常用作限制條件的列放在最左邊，依次遞減）：

mysql> create index name_passwd on abc.student(name,passwd);（需要咨詢研發部門）

2 查看索引的使用情況：

mysql> show status like 'handler_read%';

+-----------------------+-------+

| Variable_name ? ? ? ? | Value |

+-----------------------+-------+

| Handler_read_first ? ?| 4 ? ? |

| Handler_read_key ? ? ?| 5 ? ? |

| Handler_read_last ? ? | 0 ? ? |

| Handler_read_next ? ? | 0 ? ? |

| Handler_read_prev ? ? | 0 ? ? |

| Handler_read_rnd ? ? ?| 0 ? ? |

| Handler_read_rnd_next | 56 ? ?|

+-----------------------+-------+

7 rows in set (0.00 sec)

Handler_read_key：如果索引正在工作，此值應該很高，這個值代表了一個行被索引值讀的次數。如果值過低，表明增加索引得到的性能改善不高，因為索引并不常被使用。

Handler_read_rnd_next：值高意味著查詢運行低效，并且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果進行了大量的掃描，它的值會很高，說明索引不正確或查詢沒有利用到索引。

本文轉自 zengwj1949 51CTO博客，原文鏈接:http://blog.51cto.com/zengwj1949/1920641

總結

以上是生活随笔為你收集整理的MySQL优化之三：SQL语句优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。