溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一 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;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 34400 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 7738 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 4 | root | localhost | NULL | Query | 0 | init | 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.
+----+------+-----------------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----+---------+------+-------+------------------+
| 1 | root | localhost | | Sleep | 265 | | |
| 12 | root | localhost:42210 | | Query | 0 | init | show processlist |
+----+------+-----------------+----+---------+------+-------+------------------+
備注：show processlist;只列出前100條，如果想全列出請使用show full processlist;
3 通過explain分析低效的SQL語句的執行
通過之前的步驟查詢到效率低的SQL語句之后，可以通過explain命令獲取MySQL是如何執行select語句的信息。如：
mysql> explain select * from emp1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | emp1 | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
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; #執行一個語句
+------+--------+-------+------------+
| age1 | deptno | ename | birth |
+------+--------+-------+------------+
| 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上
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| 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;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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：值高意味著查詢運行低效，并且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果進行了大量的掃描，它的值會很高，說明索引不正確或查詢沒有利用到索引。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
