index merge的一次優化

来源:http://www.cnblogs.com/billyxp/archive/2016/05/25/5527532.html
-Advertisement-
Play Games

手機微博4040埠SQL優化 現象 某埠常態化延遲，通過使用pt-query-digest發現主要由於一條count（*）語句引發，具體如下： 我們來查看一下這個表的表結構和這條語句的explain結果，看是否可以優化，具體如下： 可以看到通過type和extra都可以發現其實是用到了index ...


手機微博4040埠SQL優化

現象

某埠常態化延遲，通過使用pt-query-digest發現主要由於一條count（*）語句引發，具體如下：

# 13.5s user time, 40ms system time, 21.58M rss, 156.84M vsz

# Current date: Fri Apr  1 17:43:05 2016

# Hostname: naga64

# Files: /data1/mysql4040/slow.log

# Overall: 45.87k total, 53 unique, 1.01 QPS, 9.05x concurrency __________

# Time range: 2016-04-01 05:05:02 to 17:43:05

# Attribute          total     min     max     avg     95%  stddev  median

# ============     ======= ======= ======= ======= ======= ======= =======

# Exec time        411622s      1s    238s      9s     29s     13s      6s

# Lock time            70s       0      4s     2ms   138us    57ms    76us

# Rows sent         12.66M       0   1.31M  289.43   19.46  13.90k    0.99

# Rows examine     310.43M       0   5.40M   6.93k  31.59k  65.56k    0.99

# Query size         5.89M      17   4.14k  134.67  563.87  150.53   76.28

 

# Profile

# Rank Query ID           Response time     Calls R/Call  Apdx V/M   Item

# ==== ================== ================= ===== ======= ==== ===== =====

#    1 0xE74340EE1DEFEC99 317229.0380 77.1% 34627  9.1613 0.11 12.60 SELECT user_rec_?

#    2 0xB9959C570826EFA4  72164.9508 17.5%  3746 19.2645 0.15 36.13 SELECT app

#    3 0xECEF2B7CA2BE445C   7136.5824  1.7%  3581  1.9929 0.53  2.75 SELECT user_rec_?

#    4 0x7B9529D6435F23B3   3465.0381  0.8%   137 25.2922 0.16 33.53 SELECT app

#    5 0x270C8D7D3EC37561   2209.2050  0.5%  1087  2.0324 0.51  2.34 SELECT apk

#    6 0x6AF45A776EDFF7A9   1921.4956  0.5%   905  2.1232 0.50  2.63 SELECT apk

#    7 0x67DC38C9C5F7EEBB   1816.0314  0.4%   108 16.8151 0.08  7.32 SELECT ios_apk

#    8 0x5F7E7D2BFA8FB79B   1388.2303  0.3%   518  2.6800 0.49 10.45 SELECT apk cooper

#    9 0x79F2C2072394C9BB   1005.4780  0.2%   656  1.5327 0.59  1.64 SELECT user_rec_?b

#   10 0x3229403E99601A69    632.3939  0.2%    81  7.8073 0.07  1.07 SELECT ios_app

#   11 0x83D4C6B0BB535E12    506.5923  0.1%    15 33.7728 0.10 11.12 SELECT apk

#   13 0x2F002402DBB98EE9    226.3586  0.1%    73  3.1008 0.42  4.04 SELECT app

#   14 0x992F97D6C4D52DF6    219.2329  0.1%    44  4.9826 0.19  2.00 SHOW STATUS

#   16 0x791C5370A1021F19    140.2855  0.0%    30  4.6762 0.25  1.87 SHOW SLAVE STATUS

#   18 0x2F27EBCFABB23992    110.6802  0.0%    36  3.0744 0.40  2.47 SELECT app_recommend app

#   19 0x980736573219087A    108.8593  0.0%    15  7.2573 0.00  0.45 SELECT ios_app_free ios_app

#   20 0x58492BB2C89253D8     71.5322  0.0%    10  7.1532 0.05  0.57 SELECT ios_app_free ios_app

#   21 0x0EB86D9E4630253A     61.5251  0.0%    27  2.2787 0.52  0.33 SELECT ios_app_recommend ios_app

#   22 0x398799E91C3C2AAD     59.5222  0.0%    12  4.9602 0.33  3.46 SELECT apk cooper

#   24 0x53148D850C2E022E     45.0953  0.0%    11  4.0996 0.23  1.04 SELECT ios_app

#   25 0x07387FA6467B3DB9     34.6657  0.0%    17  2.0392 0.50  0.39 SELECT app_recommend app

#   26 0xBD799CC975081065     31.1719  0.0%    16  1.9482 0.47  0.51 SELECT app

#   27 0xB7F06103A7ADA5C0     30.4686  0.0%    13  2.3437 0.42  0.52 SELECT user_rec_?d

#   30 0x188747BC3CB9728B     19.8929  0.0%    12  1.6577 0.58  0.22 SELECT app_recommend app

# MISC 0xMISC                987.4775  0.2%    92 10.7335   NS   0.0 <29 ITEMS>

 

# Query 1: 0.76 QPS, 6.97x concurrency, ID 0xE74340EE1DEFEC99 at byte 2753434

# This item is included in the report because it matches --limit.

# Scores: Apdex = 0.11 [1.0], V/M = 12.60

# Query_time sparkline: |      ^_|

# Time range: 2016-04-01 05:05:02 to 17:43:04

# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count         75   34627

# Exec time     77 317229s      1s    174s      9s     23s     11s      7s

# Lock time     55     39s    46us      3s     1ms   119us    46ms    73us

# Rows sent      0  31.80k       0       1    0.94    0.99    0.23    0.99

# Rows examine   0  22.97k       0       5    0.68    0.99    0.55    0.99

# Query size    44   2.61M      76      79   79.00   76.28    0.02   76.28

# String:

# Databases    apps

# Hosts

# Users        apps_r

# Query_time distribution

#   1us

#  10us

# 100us

#   1ms

#  10ms

# 100ms

#    1s  ################################################################

#  10s+  #######################

# Tables

#    SHOW TABLE STATUS FROM `apps` LIKE 'user_rec_07'\G

#    SHOW CREATE TABLE `apps`.`user_rec_07`\G

# EXPLAIN /*!50100 PARTITIONS*/

select count(*) as total from user_rec_07 where type=5 and weiboId='1934676487'\G

 

我們來查看一下這個表的表結構和這條語句的explain結果，看是否可以優化，具體如下：

 

localhost.apps>show create table user_rec_45;

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

| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |

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

| user_rec_45 | CREATE TABLE `user_rec_45` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `softId` int(11) NOT NULL DEFAULT '0',

  `weiboId` bigint(20) NOT NULL DEFAULT '0',

  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0???',

  `content` varchar(512) NOT NULL DEFAULT '' COMMENT '???????url??????????????',

  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  KEY `idx_softId_weiboId` (`softId`,`weiboId`),

  KEY `idx_weiboId` (`weiboId`),

  KEY `idx_type` (`type`)

) ENGINE=TokuDB AUTO_INCREMENT=3252283 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_LZMA |

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

1 row in set (0.00 sec)

localhost.apps>explain select count(*) as total from user_rec_07 where type=5 and weiboId=1934676487\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: user_rec_07

         type: index_merge

possible_keys: idx_weiboId,idx_type

          key: idx_weiboId,idx_type

      key_len: 8,1

          ref: NULL

         rows: 1

        Extra: Using intersect(idx_weiboId,idx_type); Using where; Using index

1 row in set (0.01 sec)

 

可以看到通過type和extra都可以發現其實是用到了index的，但是為這麼還會這麼慢呢？

ps：一開始看到是tokuDB的引擎，下意識的以為是tk對count（）支持不好，後來實踐證明，還是index的問題。

推理

這條sql的查詢條件還是相當簡單的，僅為2個等式，根據個人的習慣，我會先看下這2個等值條件的結果集分別是多大？

首先是weiboID的explain：

localhost.apps>explain select count(*) as total from user_rec_07 where weiboId=1934676487\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: user_rec_07

         type: ref

possible_keys: idx_weiboId

          key: idx_weiboId

      key_len: 8

          ref: const

         rows: 18

        Extra: Using index

1 row in set (0.00 sec)

接下來是type的explain：

localhost.apps>explain select count(*) as total from user_rec_07 where type=5\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: user_rec_07

         type: ref

possible_keys: idx_type

          key: idx_type

      key_len: 1

          ref: const

         rows: 114834

        Extra: Using index

1 row in set (0.00 sec)

可以很明顯的看到weiboID的區分度還是很好的，而type的就差很多了（需要掃描將近12w rows），但是理論上使用weiboID作為index只需要掃描18 rows左右，按說查詢時間應該在5ms之內才對。

 

我們分別看下3條sql的查詢時間：

2個條件：

 

localhost.apps>select count(*) as total from user_rec_45 where type=5 and weiboId='2717608261';

+-------+

| total |

+-------+

|     1 |

+-------+

1 row in set (0.57 sec)

 

 

weiboID作為條件：

localhost.apps>select count(*) as total from user_rec_45 where weiboId='2717608261'\G;

*************************** 1. row ***************************

total: 9

1 row in set (0.00 sec)

 

 

type作為條件：

localhost.apps>select count(*) as total from user_rec_45 where type=5\G;

*************************** 1. row ***************************

total: 103838

1 row in set (0.19 sec)

 

可以從上面明顯的看出來雙條件耗時最多570ms，weiboID作為條件0ms，type作為條件190ms

根據以上的結果，我們就可以進行index的優化了。

優化

添加index的思路非常的簡單，直接加一個兩條件的index即可，具體SQL如下：

localhost.apps>alter table user_rec_45 drop index idx_weiboID,add index idx_weiboID_type(weiboID,type);

我們看下添加前和添加之後的區別：

添加前：

localhost.apps>select count(*) as total from user_rec_45 where type=5 and weiboId='2717608261';

+-------+

| total |

+-------+

|     1 |

+-------+

1 row in set (0.57 sec)

 

添加後：

localhost.apps>select count(*) as total from user_rec_45 where type=5 and weiboId='2717608261';

+-------+

| total |

+-------+

|     1 |

+-------+

1 row in set (0.00 sec)

 

可以看到效果非常的明顯。

從伺服器的負載看下：

修改之前：

07:42:42 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle

07:42:43 PM  all   96.00    0.00    3.38    0.00    0.00    0.62    0.00    0.00    0.00

07:42:43 PM    0   91.00    0.00    5.00    0.00    0.00    4.00    0.00    0.00    0.00

07:42:43 PM    1   97.98    0.00    2.02    0.00    0.00    0.00    0.00    0.00    0.00

07:42:43 PM    2   98.00    0.00    2.00    0.00    0.00    0.00    0.00    0.00    0.00

07:42:43 PM    3   96.00    0.00    4.00    0.00    0.00    0.00    0.00    0.00    0.00

07:42:43 PM    4   95.96    0.00    3.03    0.00    0.00    1.01    0.00    0.00    0.00

07:42:43 PM    5   96.00    0.00    4.00    0.00    0.00    0.00    0.00    0.00    0.00

07:42:43 PM    6   97.00    0.00    3.00    0.00    0.00    0.00    0.00    0.00    0.00

07:42:43 PM    7   97.00    0.00    3.00    0.00    0.00    0.00    0.00    0.00    0.00

 

修改之後：

07:42:23 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle

07:42:24 PM  all   24.25    0.00    1.12    3.50    0.00    0.12    0.00    0.00   71.00

07:42:24 PM    0   16.16    0.00    2.02   18.18    0.00    1.01    0.00    0.00   62.63

07:42:24 PM    1    3.03    0.00    0.00    6.06    0.00    0.00    0.00    0.00   90.91

07:42:24 PM    2   90.00    0.00    0.00    1.00    0.00    0.00    0.00    0.00    9.00

07:42:24 PM    3   84.00    0.00    6.00    2.00    0.00    0.00    0.00    0.00    8.00

07:42:24 PM    4    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

07:42:24 PM    5    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

07:42:24 PM    6    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

07:42:24 PM    7    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

但是為什麼會這樣呢？ 細心的同學應該發現了，之前其實MySQL也使用了2個索引，只不過是使用的index merge，將兩個單獨的index合併在一起使用了，為什麼差距會這麼大呢？

分析

我們首先來看下index merge也就是 index intersect（indx1，index2）的定義

index_merge: This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

從上面的解釋我們可以看出來，index merge其實就是分別通過對兩個獨立的index進行過濾之後，將過濾之後的結果聚合在一起，然後在返回結果集。

在我們的這個例子中，由於type欄位的過濾性不好，故返回的rows依然很多，所以造成的很多的磁碟read，導致了cpu的負載非常的高，直接就出現了延遲。

ps：其實在這個case中，並不需要加2個條件的index，只需要將type這個index幹掉，直接使用weiboID這個index即可，畢竟這個index的過濾的結果集已經很小了。

或者通過關閉index intersect功能也可以。

SET [GLOBAL|SESSION] optimizer_switch="index_merge_intersection=off";

展示一下優化前後的io吞吐：

優化前

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--

usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw

 10   1  85   4   0   0|3842k 3440k|   0     0 |   0     0 | 629  3275

 71   4  14  11   0   0|  26M 2593k|  69k   47k|   0     0 |  31k 6920

 72   4  11  13   0   0|  26M 3258k|  79k   47k|   0     0 |  27k
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • jq時間戳動畫
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.or ...
  • Oracle基礎學習筆記（一）
    接觸oracle不久，近段時間邊看資料學習邊記錄筆記，正所謂好記性不如爛筆頭嘛。稍微整理了下這兩天學習的內容，以後回過頭來翻一翻這些基礎知識，感覺還是比較有意義的。 我本地下載的是 "oracle11g" 的版本，記錄的筆記以及測試都是在本版本上的，下麵進入正題。 用戶 oracle的用戶分為系統級 ...
  • 參數化（三）：參數嗅探
    在之前的隨筆中我提到過參數嗅探，這是非常重要的概念。下麵我們深入的研究一下參數嗅探… 首先我們知道批處理可以是參數化的或者非參數化。參數化的批處理計劃有兩種類型：“Prepared” 或者“Proc”。前者對應帶有至少一個參數的sys.sp_executesql的執行，並且從T-SQL批處理，或者應 ...
  • R語言學習-基礎篇
    從五月10日開始自學R in action，將我的學習所得逐漸發佈在博客上。 chapter1.新手上路 工作空間：存儲著所有用戶定義的對象（向量，矩陣，函數，數據框，列表）； 當前的工目錄保存是R用來讀取文件和保存結果的預設目錄。 getwd（）顯示當前工作目錄； setwd（“”）修改當前的工作 ...
  • mariadb DML語句及用戶授權
    DML(Data Manipulation Language)：INSERT， DELETE， UPDATE， SELECT INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),... 假如有上面這張表 插入一行數 ...
  • Mysql資料庫的基本概念和架構
    資料庫 1、鍵：主鍵是表中的標誌列。一個鍵可能由幾列組成。可以使用鍵作為表格之間的引用。 CustomerID是Customers表的主鍵，當它出現在其他表，例如Orders表中的時候就稱它為外鍵。 2、模式 資料庫整套表格的完整設計稱為資料庫的模式。 一個模式應該顯示表格及表格的列、每個表的主鍵和 ...
  • 轉載+自己心得
    原文鏈接 ...
  • PL/SQL編程_流控制
    PL/SQL提供了豐富的流控制語句，用來對程式的執行流程進行控制。 通過流控制語句，我們可以編寫更複雜的PL/SQL塊。 流控制語句分為兩類，即條件判斷語句和迴圈語句。 IF語句 IF語句是一種條件判斷語句，它根據條件判斷的結果執行不同的代碼。 最簡單的IF語句格式為： IF 條件 THEN 代碼 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類