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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類