首頁 > 文章 > 資料庫 > 有關sql語句優化的教程

有關sql語句優化的教程

巴扎黑原創: 2017-09-04 15:55:341447瀏覽

網路上關於SQL優化的教學很多，但比較雜亂，近日有空整理了一下，寫出來跟大家分享，下面這篇文章主要給大家分享介紹了關於sql語句優化的一般步驟，需要的朋友可以參考借鑒，下面隨著小編來一起學習學習吧。

前言

本文主要跟大家分享了關於sql語句優化的一般步驟，分享出來供大家參考學習，下面話不多說了，來一起看看詳細的介紹吧。

一、透過show status 指令了解各種sql 的執行頻率

mysql 用戶端連線成功後，透過show [ session|global] status 指令可以提供伺服器狀態信息，也可以在作業系統上使用mysqladmin extend-status 指令取得這些訊息。

show status 指令中間可以加入選項 session（預設）或global：

session （目前連線）
global（自資料上次啟動至今）

# Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like &#39;Com_%&#39;;

我們通常比較關心的是以下幾個統計參數：

Com_select : 執行select 操作的次數，一次查詢只累加1。
Com_insert : 執行 insert 運算的次數，對於批次插入的 insert 操作，只會累加一次。
Com_update : 執行 update 運算的次數。
Com_delete : 執行 delete 操作的次數。

上面這些參數對於所有儲存引擎的表格操作都會進行累計。下面這幾個參數只是針對 innodb 的，累加的演算法也略有不同：

#Innodb_rows_read : select 查詢傳回的行數。
Innodb_rows_inserted : 執行 insert 操作插入的行數。
Innodb_rows_updated : 執行 update 作業更新的行數。
Innodb_rows_deleted : 執行 delete 操作刪除的行數。

透過以上幾個參數，可以輕鬆地了解目前資料庫的應用程式是以插入更新為主還是以查詢操作為主，以及各種類型的sql 大致的執行比例是多少。對於更新操作的計數，是對執行次數的計數，不論提交或回滾都會進行累加。

對於交易型的應用，透過Com_commit 和Com_rollback 可以了解交易提交和回溯的情況，對於回溯操作非常頻繁的資料庫，可能意味著應用編寫存在問題。

此外，以下幾個參數方便使用者了解資料庫的基本情況：

Connections ：試圖連接 mysql 伺服器的次數。
Uptime ：伺服器工作時間。
Slow_queries : 慢查詢次數。

二、定義執行效率較低的sql 語句

1. 透過慢速查詢日誌定位那些執行效率較低的sql 語句，用--log-slow-queries[=file_name] 選項啟動時，mysqld 寫一個包含所有執行時間超過long_query_time 秒的sql 語句的日誌檔。

2. 慢查詢日誌在查詢結束以後才記錄，所以在應用反映執行效率出現問題的時候慢查詢日誌並不能定位問題，可以使用show processlist 命令查看當前mysql 在進行的線程，包括執行緒的狀態、是否鎖定表等，可以即時的查看sql 的執行情況，同時對一些鎖定表操作進行最佳化。

三、透過explain 分析低效率sql 的執行計畫

測試資料庫位址：https://downloads.mysql.com/docs /sakila-db.zip（本地下載）

統計某個email 為租賃電影拷貝所支付的總金額，需要關聯客戶表customer 和付款表payment ，並且對付款金額amount 欄位做求和（sum）操作，對應的執行計劃如下：

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email=&#39;JANE.BENNETT@sakilacustomer.org&#39;\G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

select_type: 表示select 類型，常見的取值有：
      simple：簡單表，及不使用表格連接或子查詢
      primary：主查詢，即外層的查詢
      union：union 中的第二個或後面的查詢語句
subquery：子查詢中的第一個select
table ：輸出結果集的表
type ：表示mysql 在表中找到所需行的方式，或稱為存取類型，常見類型效能由差到最好依序是：all、index、range、ref、eq_ref、const，system、null：

1.type=ALL，全表掃描，mysql 遍歷全表來找到符合的行：

mysql> explain select * from film where rating > 9 \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 33.33
 Extra: Using where
1 row in set, 1 warning (0.01 sec)

2.type=index, 索引全扫描，mysql 遍历整个索引来查询匹配的行

mysql> explain select title form film\G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: index
possible_keys: NULL
  key: idx_title
 key_len: 767
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

3.type=range,索引范围扫描，常见于d2714fbb0e49a95306c2048bc19e4f2b、>=、between等操作：

mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: range
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: NULL
 rows: 1350
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.07 sec)

4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：

mysql> explain select * from payment where customer_id = 350 \G 
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: const
 rows: 23
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.01 sec)

索引 idx_fk_customer_id 是非唯一索引，查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中：

mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 599
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.b.customer_id
 rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5.type=eq_ref,类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。

mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: sakila.b.film_id
 rows: 1
 filtered: 100.00
 Extra: Using where
2 rows in set, 1 warning (0.03 sec)

6.type=const/system,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 unique index 进行查询。

mysql> create table test_const (
 ->  test_id int,
 ->  test_context varchar(10),
 ->  primary key (`test_id`),
 -> );
 
insert into test_const values(1,&#39;hello&#39;);

explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: test_const
 partitions: NULL
 type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 4
  ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 1 warning (0.00 sec)

7.type=null, mysql 不用访问表或者索引，直接就能够得到结果：

mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: NULL
 partitions: NULL
 type: NULL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
 Extra: No tables used
1 row in set, 1 warning (0.00 sec)

　　类型 type 还有其他值，如 ref_or_null (与 ref 类似，区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。

possible_keys : 表示查询时可能使用的索引。
key ：表示实际使用索引
key-len : 使用到索引字段的长度。
rows ：扫描行的数量
extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写：

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = &#39;JANE.BENNETT@sakilacustomer.org&#39;\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = &#39;JANE.BENNETT@sakilacustomer.org&#39;)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件，也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT &#39;1&#39;,
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: customer_part
 partitions: p2
  type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: const
  rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warnings (0.00 sec)

可以看到 sql 访问的分区是 p2。

四、通过 performance_schema 分析 sql 性能

旧版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已经不允许使用 profiles 了，推荐用
performance_schema 分析sql。

五、通过 trace 分析优化器如何选择执行计划。

mysql5.6 提供了对 sql 的跟踪 trace，可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划，帮助我们更好的理解优化器的行为。

使用方式：首先打开 trace ，设置格式为 json，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

接下来执行想做 trace 的 sql 语句，例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录：

mysql> select rental_id from rental where 1=1 and rental_date >= &#39;2005-05-25 04:00:00&#39; and rental_date <= &#39;2005-05-25 05:00:00&#39; and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

六、确定问题并采取相应的优化措施

经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。

以上是有關sql語句優化的教程的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

上一篇：解決mysql資料庫無法被其他ip存取的情況下一篇：解決mysql資料庫無法被其他ip存取的情況

看更多