【案例】MySQL count操作優化案例一則
一 背景
某業務的資料庫定期報 thread_runing 飆高,通定位發現一個慢查詢sql導致會話堆積。執行sql 耗時如下
二 分析
慢查詢表結構如下
很明顯出現問題的sql由於使用了count(item_id) ,而item_id欄位並沒有和 selid 和end_time 構成有效索引 故該sql 沒有合理的使用索引 。檢視其直系計劃
從key_len=8 和Extra: Using where 可以看出MySQL沒有完全利用到idx_deller_id_end_time組合索引而是利用到了 selid欄位作為過濾條件回表查詢。
count(item_id)的意思是符合where條件的結果集中item_id非空集合的總和。
三 如何優化
根據該sql的業務需求是需要獲取到某商家參加活動且活動截止時間大於當前時間的商品總數,可以使用如下sql滿足要求:
執行時間僅為原來的1/4,新的sql釋出之後thread_running報警消失,業務校驗時間明顯縮短。
優化後的sql的explain 方式如下:
四 小結
a 這個問題是在沒有修改索引的基礎中做出的優化,老的sql沒有有效的利用當前的索引導致耗時操作
b 對於不同count型別的sql 總結如下
count(*)/count(1) 返回結果集的總和包括null和重複的值。
count(column) 返回結果集中非空 column 的總和,執行查詢的過程中會校驗欄位是否非空。
c 在業務設計的時候 滿足業務邏輯的前提下推薦使用count(*).
d 從官方文件中摘錄 Using where 和 Using index 的區別
如果您覺得從這篇文章受益,可以贊助 北在南方 一瓶飲料 ^_^
某業務的資料庫定期報 thread_runing 飆高,通定位發現一個慢查詢sql導致會話堆積。執行sql 耗時如下
-
root@db 05:32:05>select count(item_id) from xxxtable where selid = 345705650 and end_time > now();
-
+----------------+
-
| count(item_id) |
-
+----------------+
-
| 2247052 |
-
+----------------+
- 1 row in set (4.65 sec)
慢查詢表結構如下
-
root@db >show create table xxxtable \G
-
*************************** 1. row ***************************
-
Table: uac_shop_item_promotion_0091
-
Create Table: CREATE TABLE `uac_shop_item_promotion_0091` (
-
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
-
`gmt_modified` datetime NOT NULL COMMENT '修改時間',
-
`selid` bigint(20) NOT NULL COMMENT '分表欄位',
-
`end_time` datetime NOT NULL COMMENT '活動結束時間',
-
`item_id` bigint(20) NOT NULL COMMENT '商品id',
-
PRIMARY KEY (`id`),
-
UNIQUE KEY `idx_uq_item` (`item_id`),
-
KEY `idx_deller_id_end_time` (`selid`,`end_time`),
-
KEY `idx_deller_id_start_time` (`selid`,`start_time`),
-
KEY `idx_seller_item_start` (`selid`,`start_time`,`item_id`)
-
) ENGINE=InnoDB AUTO_INCREMENT=42132149 DEFAULT CHARSET=gbk COMMENT='索引表'
- 1 row in set (0.00 sec)
-
root@db >explain select count(item_id) from xxxtable
-
>where selid = 345705650 and end_time > now() \G
-
*************************** 1. row ***************************
-
id: 1
-
select_type: SIMPLE
-
table: xxxtable
-
type: ref
-
possible_keys: idx_deller_id_end_time,idx_deller_id_start_time,idx_seller_item_start
-
key: idx_deller_id_end_time
-
key_len: 8
-
ref: const
-
rows: 1726757
-
Extra: Using where
- 1 row in set (0.00 sec)
count(item_id)的意思是符合where條件的結果集中item_id非空集合的總和。
三 如何優化
根據該sql的業務需求是需要獲取到某商家參加活動且活動截止時間大於當前時間的商品總數,可以使用如下sql滿足要求:
- select count(*) from xxxtable where selid = 345705650 and end_time > now()
-
root@db >select count(*) from xxxtable where selid = 345705650 and end_time > now();
-
+----------+
-
| count(*) |
-
+----------+
-
| 2247052 |
-
+----------+
-
1 row in set (0.82 sec)
-
root@db >select count(1) from xxxtable where selid = 345705650 and end_time > now();
-
+----------+
-
| count(1) |
-
+----------+
-
| 2247052 |
-
+----------+
- 1 row in set (0.79 sec)
-
root@db >explain select count(*) from xxxtable where selid = 345705650 and end_time > now() \G
-
*************************** 1. row ***************************
-
id: 1
-
select_type: SIMPLE
-
table: xxxtable
-
type: range
-
possible_keys: idx_deller_id_end_time,idx_deller_id_start_time,idx_seller_item_start
-
key: idx_deller_id_end_time
-
key_len: 16
-
ref: NULL
-
rows: 1726768
-
Extra: Using where; Using index
- 1 row in set (0.00 sec)
a 這個問題是在沒有修改索引的基礎中做出的優化,老的sql沒有有效的利用當前的索引導致耗時操作
b 對於不同count型別的sql 總結如下
count(*)/count(1) 返回結果集的總和包括null和重複的值。
count(column) 返回結果集中非空 column 的總和,執行查詢的過程中會校驗欄位是否非空。
c 在業務設計的時候 滿足業務邏輯的前提下推薦使用count(*).
d 從官方文件中摘錄 Using where 和 Using index 的區別
-
Using index
-
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
-
If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups. For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
-
Using where
- A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-1791124/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- IO優化案例一則優化
- MySQL SQL優化案例(一)MySql優化
- 【MySQL】NOT EXISTS優化的一個案例MySql優化
- 一個MySQL優化案例的初步思路MySql優化
- 【MySQL】Too many connections 案例一則MySql
- 【MySQL】mysqldgotsignal11案例一則MySqlGo
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- MySQL案例05:CPU負載優化MySql負載優化
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- 技術分享 | MySQL 覆蓋索引最佳化案例一則MySql索引
- 【MySQL】 DB 回滾崩潰案例一則MySql
- 【MySQL】mysqld got signal 11 案例一則MySqlGo
- 記一個SQL優化案例SQL優化
- 一個效能優化的案例優化
- 【MySQL】效能優化之 count(*) VS count(col)MySql優化
- MySQL優化COUNT()查詢MySql優化
- 效能優化案例-SQL優化優化SQL
- 一次成功的優化案例優化
- 百萬資料 mysql count(*)優化MySql優化
- oracle啟動案例一則Oracle
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- 一個複合索引的優化案例索引優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Oracle SQL 'or' 的最佳化,最近的案例一則。OracleSQL
- SQL最佳化 | MySQL問題處理案例分享三則MySql
- Oracle優化案例-(三十四)Oracle優化
- redis快取優化案例Redis快取優化
- SQL效能優化案例分析SQL優化
- 記一次前端效能優化的案例前端優化
- count(*) 優化優化
- count(*)優化優化
- AMDU資料抽取案例一則
- Mysql之案例分析(一)MySql
- Python+pandas+matplotlib視覺化案例一則Python視覺化
- SQL優化案例-union代替or(九)SQL優化
- Oracle優化案例-union代替or(九)Oracle優化