MySQL之SQL語句最佳化

標籤：mysql 索引

一 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 |

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 |

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

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：值高意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在資料檔案中讀下一行的請求數。如果進行了大量的掃描，它的值會很高，說明索引不正確或查詢沒有利用到索引。

本文出自 “一萬年太久，只爭朝夕” 部落格，請務必保留此出處http://zengwj1949.blog.51cto.com/10747365/1920641

MySQL之SQL語句最佳化