《Mysql應(yīng)用sql語句優(yōu)化的一般步驟詳解》要點(diǎn)：
本文介紹了Mysql應(yīng)用sql語句優(yōu)化的一般步驟詳解，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
MYSQL實(shí)例前言
MYSQL實(shí)例本文主要給大家分享了關(guān)于sql語句優(yōu)化的一般步驟,分享出來供大家參考學(xué)習(xí),下面話不多說了,來一起看看詳細(xì)的介紹吧.
MYSQL實(shí)例一、通過 show status 命令了解各種 sql 的執(zhí)行頻率
MYSQL實(shí)例mysql 客戶端連接成功后,通過 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息,也可以在操作系統(tǒng)上使用 mysqladmin extend-status 命令獲取這些消息.
MYSQL實(shí)例show status 命令中間可以加入選項(xiàng) session(默認(rèn)) 或 global：
MYSQL實(shí)例
# Com_xxx 表示每個(gè) xxx 語句執(zhí)行的次數(shù).
mysql> show status like 'Com_%';
MYSQL實(shí)例我們通常比較關(guān)心的是以下幾個(gè)統(tǒng)計(jì)參數(shù)：
MYSQL實(shí)例上面這些參數(shù)對(duì)于所有存儲(chǔ)引擎的表操作都會(huì)進(jìn)行累計(jì).下面這幾個(gè)參數(shù)只是針對(duì) innodb 的,累加的算法也略有不同：
MYSQL實(shí)例通過以上幾個(gè)參數(shù),可以很容易地了解當(dāng)前數(shù)據(jù)庫的應(yīng)用是以插入更新為主還是以查詢操作為主,以及各種類型的 sql 大致的執(zhí)行比例是多少.對(duì)于更新操作的計(jì)數(shù),是對(duì)執(zhí)行次數(shù)的計(jì)數(shù),不論提交還是回滾都會(huì)進(jìn)行累加.
MYSQL實(shí)例對(duì)于事務(wù)型的應(yīng)用,通過 Com_commit 和 Com_rollback 可以了解事務(wù)提交和回滾的情況,對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫,可能意味著應(yīng)用編寫存在問題.
MYSQL實(shí)例此外,以下幾個(gè)參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況：
MYSQL實(shí)例二、定義執(zhí)行效率較低的 sql 語句
MYSQL實(shí)例1. 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語句,用 --log-slow-queries[=file_name] 選項(xiàng)啟動(dòng)時(shí),mysqld 寫一個(gè)包含所有執(zhí)行時(shí)間超過 long_query_time 秒的 sql 語句的日志文件.
MYSQL實(shí)例2. 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才記錄,所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時(shí)候慢查詢?nèi)罩静⒉荒芏ㄎ粏栴},可以使用 show processlist 命令查看當(dāng)前 mysql 在進(jìn)行的線程,包括線程的狀態(tài)、是否鎖表等,可以實(shí)時(shí)的查看 sql 的執(zhí)行情況,同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化.
MYSQL實(shí)例三、通過 explain 分析低效 sql 的執(zhí)行計(jì)劃
MYSQL實(shí)例測(cè)試數(shù)據(jù)庫地址：https://downloads.mysql.com/docs/sakila-db.zip(本地下載)
MYSQL實(shí)例統(tǒng)計(jì)某個(gè) email 為租賃電影拷貝所支付的總金額,需要關(guān)聯(lián)客戶表 customer 和 付款表 payment , 并且對(duì)付款金額 amount 字段做求和(sum) 操作,相應(yīng)的執(zhí)行計(jì)劃如下：
MYSQL實(shí)例
mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\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實(shí)例1.type=ALL,全表掃描,mysql 遍歷全表來找到匹配的行：
MYSQL實(shí)例
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)
MYSQL實(shí)例2.type=index, 索引全掃描,mysql 遍歷整個(gè)索引來查詢匹配的行
MYSQL實(shí)例
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)
MYSQL實(shí)例3.type=range,索引范圍掃描,常見于<、<=、>、>=、between等操作：
MYSQL實(shí)例
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)
MYSQL實(shí)例4.type=ref, 使用非唯一索引掃描或唯一索引的前綴掃描,返回匹配某個(gè)單獨(dú)值的記錄行,例如：
MYSQL實(shí)例
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)
MYSQL實(shí)例索引 idx_fk_customer_id 是非唯一索引,查詢條件為等值查詢條件 customer_id = 350, 所以掃描索引的類型為 ref.ref 還經(jīng)常出現(xiàn)在 join 操作中：
MYSQL實(shí)例
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)
MYSQL實(shí)例5.type=eq_ref,類似 ref,區(qū)別就在使用的索引時(shí)唯一索引,對(duì)于每個(gè)索引的鍵值,表中只要一條記錄匹配；簡(jiǎn)單的說,就是多表連接中使用 primary key 或者 unique index 作為關(guān)聯(lián)條件.
MYSQL實(shí)例
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)
MYSQL實(shí)例6.type=const/system,單表中最多有一個(gè)匹配行,查起來非常迅速,所以這個(gè)匹配行中的其他列的值可以被優(yōu)化器在當(dāng)前查詢中當(dāng)作常量來處理,例如,根據(jù)主鍵 primary key 或者唯一索引 unique index 進(jìn)行查詢.
MYSQL實(shí)例
mysql> create table test_const (
-> test_id int,
-> test_context varchar(10),
-> primary key (`test_id`),
-> );
insert into test_const values(1,'hello');
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)
MYSQL實(shí)例7.type=null, mysql 不用訪問表或者索引,直接就能夠得到結(jié)果：
MYSQL實(shí)例
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)
MYSQL實(shí)例 類型 type 還有其他值,如 ref_or_null (與 ref 類似,區(qū)別在于條件中包含對(duì) null 的查詢)、index_merge(索引合并優(yōu)化)、unique_subquery (in 的后面是一個(gè)查詢主鍵字段的子查詢)、index_subquery(與 unique_subquery 類似,區(qū)別在于 in 的后面是查詢非唯一索引字段的子查詢)等.
MYSQL實(shí)例show warnings 命令
MYSQL實(shí)例執(zhí)行explain 后再執(zhí)行 show warnings,可以看到sql 真正被執(zhí)行之前優(yōu)化器做了哪些 sql 改寫：
MYSQL實(shí)例
MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\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` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MYSQL實(shí)例從 warning 的 message 字段中能夠看到優(yōu)化器自動(dòng)去除了 1=1 恒成立的條件,也就是說優(yōu)化器在改寫 sql 時(shí)會(huì)自動(dòng)去掉恒成立的條件.
MYSQL實(shí)例explain 命令也有對(duì)分區(qū)的支持.
MYSQL實(shí)例
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 '1',
-> `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)
MYSQL實(shí)例可以看到 sql 訪問的分區(qū)是 p2.
MYSQL實(shí)例四、通過 performance_schema 分析 sql 性能
MYSQL實(shí)例舊版本的 mysql 可以使用 profiles 分析 sql 性能,我用的是5.7.18的版本,已經(jīng)不允許使用 profiles 了,推薦用
performance_schema 分析sql.
MYSQL實(shí)例五、通過 trace 分析優(yōu)化器如何選擇執(zhí)行計(jì)劃.
MYSQL實(shí)例mysql5.6 提供了對(duì) sql 的跟蹤 trace,可以進(jìn)一步了解為什么優(yōu)化器選擇 A 執(zhí)行計(jì)劃而不是 B 執(zhí)行計(jì)劃,幫助我們更好的理解優(yōu)化器的行為.
MYSQL實(shí)例使用方式：首先打開 trace ,設(shè)置格式為 json,設(shè)置 trace 最大能夠使用的內(nèi)存大小,避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整顯示.
MYSQL實(shí)例
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)
MYSQL實(shí)例接下來執(zhí)行想做 trace 的 sql 語句,例如像了解租賃表 rental 中庫存編號(hào) inventory_id 為 4466 的電影拷貝在出租日期 rental_date 為 2005-05-25 4:00:00 ~ 5:00:00 之間出租的記錄：
MYSQL實(shí)例
mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' 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)
MYSQL實(shí)例六、 確定問題并采取相應(yīng)的優(yōu)化措施
MYSQL實(shí)例經(jīng)過以上步驟,基本就可以確認(rèn)問題出現(xiàn)的原因.此時(shí)可以根據(jù)情況采取相應(yīng)的措施,進(jìn)行優(yōu)化以提高執(zhí)行的效率.
MYSQL實(shí)例總結(jié)
MYSQL實(shí)例以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對(duì)維易PHP的支持.
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3474.html
