代码拉取完成，页面将自动刷新
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964
<!DOCTYPE html>
<html class="theme-next pisces use-motion" lang="zh-CN">
<head><meta name="generator" content="Hexo 3.8.0">
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Monda:300,300italic,400,400italic,700,700italic|Roboto Slab:300,300italic,400,400italic,700,700italic|Lobster Two:300,300italic,400,400italic,700,700italic|PT Mono:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext">
<link rel="stylesheet" href="/lib/font-awesome/css/font-awesome.min.css?v=4.7.0">
<link rel="stylesheet" href="/css/main.css?v=7.1.2">
<link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=7.1.2">
<link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png?v=7.1.2">
<link rel="icon" type="image/png" sizes="16x16" href="/favicon.ico?v=7.1.2">
<link rel="mask-icon" href="/images/logo.svg?v=7.1.2" color="#222">
<script id="hexo.configurations">
var NexT = window.NexT || {};
var CONFIG = {
root: '/',
scheme: 'Pisces',
version: '7.1.2',
sidebar: {"position":"left","display":"hide","offset":12,"onmobile":false,"dimmer":false},
back2top: true,
back2top_sidebar: false,
fancybox: false,
fastclick: false,
lazyload: false,
tabs: true,
motion: {"enable":true,"async":true,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
algolia: {
applicationID: '',
apiKey: '',
indexName: '',
hits: {"per_page":10},
labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
}
};
</script>
<meta name="description" content="背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易">
<meta name="keywords" content="MySQL">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL索引原理及慢查询优化">
<meta property="og:url" content="https://www.dudefu.tk/MySQL索引原理及慢查询优化.html">
<meta property="og:site_name" content="The Future">
<meta property="og:description" content="背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/008i3skNgy1gpsnj7dlaej30f808x74m.jpg">
<meta property="og:image" content="https://tva1.sinaimg.cn/large/008i3skNgy1gpsnjb3oo3j30hc08ct9c.jpg">
<meta property="og:updated_time" content="2021-04-22T09:31:58.948Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL索引原理及慢查询优化">
<meta name="twitter:description" content="背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易">
<meta name="twitter:image" content="https://tva1.sinaimg.cn/large/008i3skNgy1gpsnj7dlaej30f808x74m.jpg">
<link rel="canonical" href="https://www.dudefu.tk/MySQL索引原理及慢查询优化">
<script id="page.configurations">
CONFIG.page = {
sidebar: "",
};
</script>
<title>MySQL索引原理及慢查询优化 | The Future</title>
<noscript>
<style>
.use-motion .motion-element,
.use-motion .brand,
.use-motion .menu-item,
.sidebar-inner,
.use-motion .post-block,
.use-motion .pagination,
.use-motion .comments,
.use-motion .post-header,
.use-motion .post-body,
.use-motion .collection-title { opacity: initial; }
.use-motion .logo,
.use-motion .site-title,
.use-motion .site-subtitle {
opacity: initial;
top: initial;
}
.use-motion .logo-line-before i { left: initial; }
.use-motion .logo-line-after i { right: initial; }
</style>
</noscript>
</head>
<body itemscope="" itemtype="http://schema.org/WebPage" lang="zh-CN">
<div class="container sidebar-position-left page-post-detail">
<div class="headband"></div>
<header id="header" class="header" itemscope="" itemtype="http://schema.org/WPHeader">
<div class="header-inner"><div class="site-brand-wrapper">
<div class="site-meta">
<div class="custom-logo-site-title">
<a href="/" class="brand" rel="start">
<span class="logo-line-before"><i></i></span>
<span class="site-title">The Future</span>
<span class="logo-line-after"><i></i></span>
</a>
</div>
<h1 class="site-subtitle" itemprop="description">Stay hungry,stay foolish.</h1>
</div>
<div class="site-nav-toggle">
<button aria-label="切换导航栏">
<span class="btn-bar"></span>
<span class="btn-bar"></span>
<span class="btn-bar"></span>
</button>
</div>
</div>
<nav class="site-nav">
<ul id="menu" class="menu">
<li class="menu-item menu-item-home">
<a href="/" rel="section"><i class="menu-item-icon fa fa-fw fa-home"></i> <br>首页</a>
</li>
<li class="menu-item menu-item-archives">
<a href="/archives/" rel="section"><i class="menu-item-icon fa fa-fw fa-archive"></i> <br>归档<span class="badge">125</span></a>
</li>
<li class="menu-item menu-item-categories">
<a href="/categories" rel="section"><i class="menu-item-icon fa fa-fw fa-th"></i> <br>分类<span class="badge">15</span></a>
</li>
<li class="menu-item menu-item-tags">
<a href="/tags" rel="section"><i class="menu-item-icon fa fa-fw fa-tags"></i> <br>标签<span class="badge">63</span></a>
</li>
<li class="menu-item menu-item-something">
<a href="/something" rel="section"><i class="menu-item-icon fa fa-fw fa-paper-plane"></i> <br>干货</a>
</li>
<li class="menu-item menu-item-about">
<a href="/about/" rel="section"><i class="menu-item-icon fa fa-fw fa-user"></i> <br>关于</a>
</li>
<li class="menu-item menu-item-search">
<a href="javascript:;" class="popup-trigger">
<i class="menu-item-icon fa fa-search fa-fw"></i> <br>搜索</a>
</li>
</ul>
<div class="site-search">
<div class="popup search-popup local-search-popup">
<div class="local-search-header clearfix">
<span class="search-icon">
<i class="fa fa-search"></i>
</span>
<span class="popup-btn-close">
<i class="fa fa-times-circle"></i>
</span>
<div class="local-search-input-wrapper">
<input autocomplete="off" placeholder="搜索..." spellcheck="false" type="text" id="local-search-input">
</div>
</div>
<div id="local-search-result"></div>
</div>
</div>
</nav>
</div>
</header>
<main id="main" class="main">
<div class="main-inner">
<div class="content-wrap">
<div id="content" class="content">
<div id="posts" class="posts-expand">
<div class="reading-progress-bar"></div>
<article class="post post-type-normal" itemscope="" itemtype="http://schema.org/Article">
<div class="post-block">
<link itemprop="mainEntityOfPage" href="https://www.dudefu.tk/MySQL索引原理及慢查询优化.html">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="name" content="Daniel X">
<meta itemprop="description" content="專注于大数据技術，分享干货">
<meta itemprop="image" content="https://hexoblog-1254111960.cos.ap-guangzhou.myqcloud.com/HexoBlog-tou.jpg">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="The Future">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">MySQL索引原理及慢查询优化
</h2>
<div class="post-meta">
<span class="post-time">
<span class="post-meta-item-icon">
<i class="fa fa-calendar-o"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-04-22 17:29:55 / 修改时间：17:31:58" itemprop="dateCreated datePublished" datetime="2021-04-22T17:29:55+08:00">2021-04-22</time>
</span>
<span class="post-category">
<span class="post-meta-divider">|</span>
<span class="post-meta-item-icon">
<i class="fa fa-folder-o"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope="" itemtype="http://schema.org/Thing"><a href="/categories/数据库/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a></span>
</span>
<span class="post-comments-count">
<span class="post-meta-divider">|</span>
<span class="post-meta-item-icon">
<i class="fa fa-comment-o"></i>
</span>
<span class="post-meta-item-text">评论数：</span>
<a href="/MySQL索引原理及慢查询优化.html#comments" itemprop="discussionUrl">
<span class="post-comments-count valine-comment-count" data-xid="/MySQL索引原理及慢查询优化.html" itemprop="commentCount"></span>
</a>
</span>
<span id="/MySQL索引原理及慢查询优化.html" class="leancloud_visitors" data-flag-title="MySQL索引原理及慢查询优化">
<span class="post-meta-divider">|</span>
<span class="post-meta-item-icon">
<i class="fa fa-eye"></i>
</span>
<span class="post-meta-item-text">阅读次数：</span>
<span class="leancloud-visitors-count"></span>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h2 id="背景"><a href="#背景" class="headerlink" title="背景"></a>背景</h2><p>MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。</p>
<a id="more"></a>
<p>本人从2013年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。</p>
<h2 id="一个慢查询引发的思考"><a href="#一个慢查询引发的思考" class="headerlink" title="一个慢查询引发的思考"></a>一个慢查询引发的思考</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> <span class="keyword">count</span>(*) </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> task </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> <span class="keyword">status</span>=<span class="number">2</span> </span><br><span class="line"> <span class="keyword">and</span> operator_id=<span class="number">20839</span> </span><br><span class="line"> <span class="keyword">and</span> operate_time><span class="number">1371169729</span> </span><br><span class="line"> <span class="keyword">and</span> operate_time<<span class="number">1371174603</span> </span><br><span class="line"> <span class="keyword">and</span> <span class="keyword">type</span>=<span class="number">2</span>;</span><br></pre></td></tr></table></figure>
<p>系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。</p>
<p>并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”。</p>
<p>我很惊讶，问道：“为什么需要每个字段都加上索引？”</p>
<p>“把查询的字段都加上索引会更快”，工程师信心满满。</p>
<p>“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”</p>
<p>“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。</p>
<p>多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。</p>
<h2 id="MySQL索引原理"><a href="#MySQL索引原理" class="headerlink" title="MySQL索引原理"></a>MySQL索引原理</h2><h3 id="索引目的"><a href="#索引目的" class="headerlink" title="索引目的"></a>索引目的</h3><p>索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？</p>
<h3 id="索引原理"><a href="#索引原理" class="headerlink" title="索引原理"></a>索引原理</h3><p>除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。</p>
<p>数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。</p>
<h4 id="磁盘IO与预读"><a href="#磁盘IO与预读" class="headerlink" title="磁盘IO与预读"></a>磁盘IO与预读</h4><p>前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：</p>
<p><img src="https://tva1.sinaimg.cn/large/008i3skNgy1gpsnj7dlaej30f808x74m.jpg" alt="various-system-software-hardware-latencies"></p>
<p>various-system-software-hardware-latencies</p>
<p>考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。</p>
<h4 id="索引的数据结构"><a href="#索引的数据结构" class="headerlink" title="索引的数据结构"></a>索引的数据结构</h4><p>前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。</p>
<h4 id="详解b-树"><a href="#详解b-树" class="headerlink" title="详解b+树"></a>详解b+树</h4><p><img src="https://tva1.sinaimg.cn/large/008i3skNgy1gpsnjb3oo3j30hc08ct9c.jpg" alt="b+树"></p>
<p>b+树</p>
<p>如上图，是一颗b+树，关于b+树的定义可以参见<span class="exturl" data-url="aHR0cDovL3poLndpa2lwZWRpYS5vcmcvd2lraS9CJTJC5qCR" title="http://zh.wikipedia.org/wiki/B%2B树">B+树<i class="fa fa-external-link"></i></span>，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。</p>
<h4 id="b-树的查找过程"><a href="#b-树的查找过程" class="headerlink" title="b+树的查找过程"></a>b+树的查找过程</h4><p>如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。</p>
<h4 id="b-树性质"><a href="#b-树性质" class="headerlink" title="b+树性质"></a>b+树性质</h4><p>1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。</p>
<p>2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。</p>
<h2 id="慢查询优化"><a href="#慢查询优化" class="headerlink" title="慢查询优化"></a>慢查询优化</h2><p>关于MySQL索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：</p>
<h3 id="建索引的几大原则"><a href="#建索引的几大原则" class="headerlink" title="建索引的几大原则"></a>建索引的几大原则</h3><p>1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。</p>
<p>2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。</p>
<p>3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。</p>
<p>4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。</p>
<p>5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。</p>
<h3 id="回到开始的慢查询"><a href="#回到开始的慢查询" class="headerlink" title="回到开始的慢查询"></a>回到开始的慢查询</h3><p>根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> task <span class="keyword">where</span> <span class="keyword">status</span> = <span class="number">0</span> <span class="keyword">and</span> <span class="keyword">type</span> = <span class="number">12</span> <span class="keyword">limit</span> <span class="number">10</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> task <span class="keyword">where</span> <span class="keyword">status</span> = <span class="number">0</span> ;</span><br></pre></td></tr></table></figure>
<p>那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则</p>
<h3 id="查询优化神器-explain命令"><a href="#查询优化神器-explain命令" class="headerlink" title="查询优化神器 - explain命令"></a>查询优化神器 - explain命令</h3><p>关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网<span class="exturl" data-url="aHR0cDovL2Rldi5teXNxbC5jb20vZG9jL3JlZm1hbi81LjUvZW4vZXhwbGFpbi1vdXRwdXQuaHRtbA==" title="http://dev.mysql.com/doc/refman/5.5/en/explain-output.html">explain-output<i class="fa fa-external-link"></i></span>，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。</p>
<h3 id="慢查询优化基本步骤"><a href="#慢查询优化基本步骤" class="headerlink" title="慢查询优化基本步骤"></a>慢查询优化基本步骤</h3><p>0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE</p>
<p>1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高</p>
<p>2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）</p>
<p>3.order by limit 形式的sql语句让排序的表优先查</p>
<p>4.了解业务方使用场景</p>
<p>5.加索引时参照建索引的几大原则</p>
<p>6.观察结果，不符合预期继续从0分析</p>
<h3 id="几个慢查询案例"><a href="#几个慢查询案例" class="headerlink" title="几个慢查询案例"></a>几个慢查询案例</h3><p>下面几个例子详细解释了如何分析和优化慢查询。</p>
<h4 id="复杂语句写法"><a href="#复杂语句写法" class="headerlink" title="复杂语句写法"></a>复杂语句写法</h4><p>很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> <span class="keyword">distinct</span> cert.emp_id </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> cm_log cl </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">select</span></span><br><span class="line"> emp.id <span class="keyword">as</span> emp_id,</span><br><span class="line"> emp_cert.id <span class="keyword">as</span> cert_id </span><br><span class="line"> <span class="keyword">from</span></span><br><span class="line"> employee emp </span><br><span class="line"> <span class="keyword">left</span> <span class="keyword">join</span></span><br><span class="line"> emp_certificate emp_cert </span><br><span class="line"> <span class="keyword">on</span> emp.id = emp_cert.emp_id </span><br><span class="line"> <span class="keyword">where</span></span><br><span class="line"> emp.is_deleted=<span class="number">0</span></span><br><span class="line"> ) cert </span><br><span class="line"> <span class="keyword">on</span> (</span><br><span class="line"> cl.ref_table=<span class="string">'Employee'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.ref_oid= cert.emp_id</span><br><span class="line"> ) </span><br><span class="line"> <span class="keyword">or</span> (</span><br><span class="line"> cl.ref_table=<span class="string">'EmpCertificate'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.ref_oid= cert.cert_id</span><br><span class="line"> ) </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> cl.last_upd_date >=<span class="string">'2013-11-07 15:03:00'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.last_upd_date<=<span class="string">'2013-11-08 16:00:00'</span>;</span><br></pre></td></tr></table></figure>
<p>0.先运行一下，53条记录 1.87秒，又没有用聚合语句，比较慢</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">53 rows in <span class="keyword">set</span> (<span class="number">1.87</span> sec)</span><br></pre></td></tr></table></figure>
<p>1.explain</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">+<span class="comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span></span><br><span class="line">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</span><br><span class="line">+<span class="comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span></span><br><span class="line">| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |</span><br><span class="line">| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |</span><br><span class="line">| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |</span><br><span class="line">| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |</span><br><span class="line">+<span class="comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span></span><br></pre></td></tr></table></figure>
<p>简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。</p>
<p>如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。</p>
<p>优化过的语句如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> emp.id </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> cm_log cl </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> employee emp </span><br><span class="line"> <span class="keyword">on</span> cl.ref_table = <span class="string">'Employee'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.ref_oid = emp.id </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> cl.last_upd_date >=<span class="string">'2013-11-07 15:03:00'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.last_upd_date<=<span class="string">'2013-11-08 16:00:00'</span> </span><br><span class="line"> <span class="keyword">and</span> emp.is_deleted = <span class="number">0</span> </span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line"> emp.id </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> cm_log cl </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> emp_certificate ec </span><br><span class="line"> <span class="keyword">on</span> cl.ref_table = <span class="string">'EmpCertificate'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.ref_oid = ec.id </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> employee emp </span><br><span class="line"> <span class="keyword">on</span> emp.id = ec.emp_id </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> cl.last_upd_date >=<span class="string">'2013-11-07 15:03:00'</span> </span><br><span class="line"> <span class="keyword">and</span> cl.last_upd_date<=<span class="string">'2013-11-08 16:00:00'</span> </span><br><span class="line"> <span class="keyword">and</span> emp.is_deleted = <span class="number">0</span></span><br></pre></td></tr></table></figure>
<p>4.不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致</p>
<p>5.现有索引可以满足，不需要建索引</p>
<p>6.用改造后的语句实验一下，只需要10ms 降低了近200倍！</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">+<span class="comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span></span><br><span class="line">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</span><br><span class="line">+<span class="comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span></span><br><span class="line">| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |</span><br><span class="line">| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |</span><br><span class="line">| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |</span><br><span class="line">| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |</span><br><span class="line">| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |</span><br><span class="line">| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |</span><br><span class="line">+<span class="comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span></span><br><span class="line">53 rows in <span class="keyword">set</span> (<span class="number">0.01</span> sec)</span><br></pre></td></tr></table></figure>
<h4 id="明确应用场景"><a href="#明确应用场景" class="headerlink" title="明确应用场景"></a>明确应用场景</h4><p>举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> * </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> stage_poi sp </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> sp.accurate_result=<span class="number">1</span> </span><br><span class="line"> <span class="keyword">and</span> (</span><br><span class="line"> sp.sync_status=<span class="number">0</span> </span><br><span class="line"> <span class="keyword">or</span> sp.sync_status=<span class="number">2</span> </span><br><span class="line"> <span class="keyword">or</span> sp.sync_status=<span class="number">4</span></span><br><span class="line"> );</span><br></pre></td></tr></table></figure>
<p>0.先看看运行多长时间,951条数据6.22秒，真的很慢。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">951 rows in <span class="keyword">set</span> (<span class="number">6.22</span> sec)</span><br></pre></td></tr></table></figure>
<p>1.先explain，rows达到了361万，type = ALL表明是全表扫描。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">+<span class="comment">----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span></span><br><span class="line">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</span><br><span class="line">+<span class="comment">----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span></span><br><span class="line">| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |</span><br><span class="line">+<span class="comment">----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span></span><br></pre></td></tr></table></figure>
<p>2.所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。</p>
<p>3.让explain的rows 尽量逼近951。</p>
<p>看一下accurate_result = 1的记录数：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*),accurate_result <span class="keyword">from</span> stage_poi <span class="keyword">group</span> <span class="keyword">by</span> accurate_result;</span><br><span class="line">+<span class="comment">----------+-----------------+</span></span><br><span class="line">| count(*) | accurate_result |</span><br><span class="line">+<span class="comment">----------+-----------------+</span></span><br><span class="line">| 1023 | -1 |</span><br><span class="line">| 2114655 | 0 |</span><br><span class="line">| 972815 | 1 |</span><br><span class="line">+<span class="comment">----------+-----------------+</span></span><br></pre></td></tr></table></figure>
<p>我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。</p>
<p>再看一下sync_status字段的情况：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*),sync_status <span class="keyword">from</span> stage_poi <span class="keyword">group</span> <span class="keyword">by</span> sync_status;</span><br><span class="line">+<span class="comment">----------+-------------+</span></span><br><span class="line">| count(*) | sync_status |</span><br><span class="line">+<span class="comment">----------+-------------+</span></span><br><span class="line">| 3080 | 0 |</span><br><span class="line">| 3085413 | 3 |</span><br><span class="line">+<span class="comment">----------+-------------+</span></span><br></pre></td></tr></table></figure>
<p>同样的区分度也很低，根据理论，也不适合建立索引。</p>
<p>问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。</p>
<p>4.找业务方去沟通，看看使用场景。业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。</p>
<p>5.根据建立索引规则，使用如下语句建立索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> stage_poi <span class="keyword">add</span> <span class="keyword">index</span> idx_acc_status(accurate_result,sync_status);</span><br></pre></td></tr></table></figure>
<p>6.观察预期结果,发现只需要200ms，快了30多倍。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">952 rows in <span class="keyword">set</span> (<span class="number">0.20</span> sec)</span><br></pre></td></tr></table></figure>
<p>我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把where条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。</p>
<h4 id="无法优化的语句"><a href="#无法优化的语句" class="headerlink" title="无法优化的语句"></a>无法优化的语句</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> c.id,</span><br><span class="line"> c.name,</span><br><span class="line"> c.position,</span><br><span class="line"> c.sex,</span><br><span class="line"> c.phone,</span><br><span class="line"> c.office_phone,</span><br><span class="line"> c.feature_info,</span><br><span class="line"> c.birthday,</span><br><span class="line"> c.creator_id,</span><br><span class="line"> c.is_keyperson,</span><br><span class="line"> c.giveup_reason,</span><br><span class="line"> c.status,</span><br><span class="line"> c.data_source,</span><br><span class="line"> from_unixtime(c.created_time) <span class="keyword">as</span> created_time,</span><br><span class="line"> from_unixtime(c.last_modified) <span class="keyword">as</span> last_modified,</span><br><span class="line"> c.last_modified_user_id </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> contact c </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> contact_branch cb </span><br><span class="line"> <span class="keyword">on</span> c.id = cb.contact_id </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> branch_user bu </span><br><span class="line"> <span class="keyword">on</span> cb.branch_id = bu.branch_id </span><br><span class="line"> <span class="keyword">and</span> bu.status <span class="keyword">in</span> (</span><br><span class="line"> <span class="number">1</span>,</span><br><span class="line"> <span class="number">2</span>) </span><br><span class="line"> <span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> org_emp_info oei </span><br><span class="line"> <span class="keyword">on</span> oei.data_id = bu.user_id </span><br><span class="line"> <span class="keyword">and</span> oei.node_left >= <span class="number">2875</span> </span><br><span class="line"> <span class="keyword">and</span> oei.node_right <= <span class="number">10802</span> </span><br><span class="line"> <span class="keyword">and</span> oei.org_category = - <span class="number">1</span> </span><br><span class="line"> <span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line"> c.created_time <span class="keyword">desc</span> <span class="keyword">limit</span> <span class="number">0</span> ,</span><br><span class="line"> <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<p>还是几个步骤。</p>
<p>0.先看语句运行多长时间，10条记录用了13秒，已经不可忍受。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">10 rows in <span class="keyword">set</span> (<span class="number">13.06</span> sec)</span><br></pre></td></tr></table></figure>
<p>1.explain</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">+<span class="comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span></span><br><span class="line">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</span><br><span class="line">+<span class="comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span></span><br><span class="line">| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort |</span><br><span class="line">| 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index |</span><br><span class="line">| 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | |</span><br><span class="line">| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |</span><br><span class="line">+<span class="comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span></span><br></pre></td></tr></table></figure>
<p>从执行计划上看，mysql先查org_emp_info表扫描8849记录，再用索引idx_userid_status关联branch_user表，再用索引idx_branch_id关联contact_branch表，最后主键关联contact表。</p>
<p>rows返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有order by + limit组合，会不会是排序量太大搞的？于是我们简化SQL，去掉后面的order by 和 limit，看看到底用了多少记录来排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> <span class="keyword">count</span>(*)</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> contact c </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> contact_branch cb </span><br><span class="line"> <span class="keyword">on</span> c.id = cb.contact_id </span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> branch_user bu </span><br><span class="line"> <span class="keyword">on</span> cb.branch_id = bu.branch_id </span><br><span class="line"> <span class="keyword">and</span> bu.status <span class="keyword">in</span> (</span><br><span class="line"> <span class="number">1</span>,</span><br><span class="line"> <span class="number">2</span>) </span><br><span class="line"> <span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> org_emp_info oei </span><br><span class="line"> <span class="keyword">on</span> oei.data_id = bu.user_id </span><br><span class="line"> <span class="keyword">and</span> oei.node_left >= <span class="number">2875</span> </span><br><span class="line"> <span class="keyword">and</span> oei.node_right <= <span class="number">10802</span> </span><br><span class="line"> <span class="keyword">and</span> oei.org_category = - <span class="number">1</span> </span><br><span class="line">+<span class="comment">----------+</span></span><br><span class="line">| <span class="keyword">count</span>(*) |</span><br><span class="line">+<span class="comment">----------+</span></span><br><span class="line">| <span class="number">778878</span> |</span><br><span class="line">+<span class="comment">----------+</span></span><br><span class="line"><span class="number">1</span> <span class="keyword">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">5.19</span> sec)</span><br></pre></td></tr></table></figure>
<p>发现排序之前居然锁定了778878条记录，如果针对70万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据contact的created_time排序，再来join会不会比较快呢？</p>
<p>于是改造成下面的语句，也可以用straight_join来优化：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">select</span><br><span class="line"> c.id,</span><br><span class="line"> c.name,</span><br><span class="line"> c.position,</span><br><span class="line"> c.sex,</span><br><span class="line"> c.phone,</span><br><span class="line"> c.office_phone,</span><br><span class="line"> c.feature_info,</span><br><span class="line"> c.birthday,</span><br><span class="line"> c.creator_id,</span><br><span class="line"> c.is_keyperson,</span><br><span class="line"> c.giveup_reason,</span><br><span class="line"> c.status,</span><br><span class="line"> c.data_source,</span><br><span class="line"> from_unixtime(c.created_time) as created_time,</span><br><span class="line"> from_unixtime(c.last_modified) as last_modified,</span><br><span class="line"> c.last_modified_user_id </span><br><span class="line">from</span><br><span class="line"> contact c </span><br><span class="line">where</span><br><span class="line"> exists (</span><br><span class="line"> select</span><br><span class="line"> 1 </span><br><span class="line"> from</span><br><span class="line"> contact_branch cb </span><br><span class="line"> inner join</span><br><span class="line"> branch_user bu </span><br><span class="line"> on cb.branch_id = bu.branch_id </span><br><span class="line"> and bu.status in (</span><br><span class="line"> 1,</span><br><span class="line"> 2) </span><br><span class="line"> inner join</span><br><span class="line"> org_emp_info oei </span><br><span class="line"> on oei.data_id = bu.user_id </span><br><span class="line"> and oei.node_left >= 2875 </span><br><span class="line"> and oei.node_right <= 10802 </span><br><span class="line"> and oei.org_category = - 1 </span><br><span class="line"> where</span><br><span class="line"> c.id = cb.contact_id </span><br><span class="line"> ) </span><br><span class="line"> order by</span><br><span class="line"> c.created_time desc limit 0 ,</span><br><span class="line"> 10;</span><br></pre></td></tr></table></figure>
<p>验证一下效果 预计在1ms内，提升了13000多倍！</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">10 rows in <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>
<p>本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再join和先join再排序理论上开销是一样的，为何提升这么多是因为有一个limit！大致执行过程是：mysql先按索引排序得到前10条记录，然后再去join过滤，当发现不够10条的时候，再次去10条，再次join，这显然在内层join过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql还傻乎乎的每次取10条，几乎遍历了这个数据表！</p>
<p>用不同参数的SQL试验下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"> sql_no_cache c.id,</span><br><span class="line"> c.name,</span><br><span class="line"> c.position,</span><br><span class="line"> c.sex,</span><br><span class="line"> c.phone,</span><br><span class="line"> c.office_phone,</span><br><span class="line"> c.feature_info,</span><br><span class="line"> c.birthday,</span><br><span class="line"> c.creator_id,</span><br><span class="line"> c.is_keyperson,</span><br><span class="line"> c.giveup_reason,</span><br><span class="line"> c.status,</span><br><span class="line"> c.data_source,</span><br><span class="line"> from_unixtime(c.created_time) <span class="keyword">as</span> created_time,</span><br><span class="line"> from_unixtime(c.last_modified) <span class="keyword">as</span> last_modified,</span><br><span class="line"> c.last_modified_user_id </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"> contact c </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"> <span class="keyword">exists</span> (</span><br><span class="line"> <span class="keyword">select</span></span><br><span class="line"> <span class="number">1</span> </span><br><span class="line"> <span class="keyword">from</span></span><br><span class="line"> contact_branch cb </span><br><span class="line"> <span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> branch_user bu </span><br><span class="line"> <span class="keyword">on</span> cb.branch_id = bu.branch_id </span><br><span class="line"> <span class="keyword">and</span> bu.status <span class="keyword">in</span> (</span><br><span class="line"> <span class="number">1</span>,</span><br><span class="line"> <span class="number">2</span>) </span><br><span class="line"> <span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"> org_emp_info oei </span><br><span class="line"> <span class="keyword">on</span> oei.data_id = bu.user_id </span><br><span class="line"> <span class="keyword">and</span> oei.node_left >= <span class="number">2875</span> </span><br><span class="line"> <span class="keyword">and</span> oei.node_right <= <span class="number">2875</span> </span><br><span class="line"> <span class="keyword">and</span> oei.org_category = - <span class="number">1</span> </span><br><span class="line"> <span class="keyword">where</span></span><br><span class="line"> c.id = cb.contact_id </span><br><span class="line"> ) </span><br><span class="line"> <span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line"> c.created_time <span class="keyword">desc</span> <span class="keyword">limit</span> <span class="number">0</span> ,</span><br><span class="line"> <span class="number">10</span>;</span><br><span class="line">Empty <span class="keyword">set</span> (<span class="number">2</span> <span class="keyword">min</span> <span class="number">18.99</span> sec)</span><br></pre></td></tr></table></figure>
<p>2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。</p>
<p>通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于SQL用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。</p>
<p>慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。</p>
<h2 id="写在后面的话"><a href="#写在后面的话" class="headerlink" title="写在后面的话"></a>写在后面的话</h2><p>本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”</p>
<h2 id="参考文献："><a href="#参考文献：" class="headerlink" title="参考文献："></a>参考文献：</h2><p>1.《高性能MySQL》 2.《数据结构与算法分析》</p>
<p><span class="exturl" data-url="aHR0cHM6Ly90ZWNoLm1laXR1YW4uY29tLzIwMTQvMDYvMzAvbXlzcWwtaW5kZXguaHRtbA==" title="https://tech.meituan.com/2014/06/30/mysql-index.html">原文地址<i class="fa fa-external-link"></i></span></p>
</div>
<footer class="post-footer">
<div class="post-tags">
<a href="/tags/MySQL/" rel="tag"><i class="fa fa-tag"></i> MySQL</a>
</div>
<div class="post-widgets">
<div class="social_share">
<div>
<script src="//cdn.jsdelivr.net/npm/ilyabirman-likely@2/release/likely.js"></script>
<link rel="stylesheet" href="//cdn.jsdelivr.net/npm/ilyabirman-likely@2/release/likely.css">
<div class="likely likely-light">
<div class="twitter">Tweet</div>
<div class="facebook">Share</div>
<div class="linkedin">Link</div>
<div class="gplus">Plus</div>
<div class="vkontakte">Share</div>
<div class="odnoklassniki">Class</div>
<div class="telegram">Send</div>
<div class="whatsapp">Send</div>
<div class="pinterest">Pin</div>
</div>
</div>
<div>
<div class="bdsharebuttonbox">
<a href="#" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a>
<a href="#" class="bds_douban" data-cmd="douban" title="分享到豆瓣网"></a>
<a href="#" class="bds_sqq" data-cmd="sqq" title="分享到QQ好友"></a>
<a href="#" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a>
<a href="#" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a>
<a href="#" class="bds_tieba" data-cmd="tieba" title="分享到百度贴吧"></a>
<a href="#" class="bds_twi" data-cmd="twi" title="分享到Twitter"></a>
<a href="#" class="bds_fbook" data-cmd="fbook" title="分享到Facebook"></a>
<a href="#" class="bds_more" data-cmd="more"></a>
<a class="bds_count" data-cmd="count"></a>
</div>
<script>
window._bd_share_config = {
"common": {
"bdText": "",
"bdMini": "2",
"bdMiniList": false,
"bdPic": ""
},
"share": {
"bdSize": "16",
"bdStyle": "0"
},
"image": {
"viewList": ["tsina", "douban", "sqq", "qzone", "weixin", "twi", "fbook"],
"viewText": "分享到：",
"viewSize": "16"
}
}
</script>
<script>
with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='//bdimg.share.baidu.com/static/api/js/share.js?cdnversion='+~(-new Date()/36e5)];
</script>
</div>
</div>
</div>
<div class="post-nav">
<div class="post-nav-next post-nav-item">
<a href="/Mysql执行计划分析.html" rel="next" title="Mysql执行计划分析">
<i class="fa fa-chevron-left"></i> Mysql执行计划分析
</a>
</div>
<span class="post-nav-divider"></span>
<div class="post-nav-prev post-nav-item">
</div>
</div>
</footer>
</div>
</article>
</div>
</div>
<div class="comments" id="comments">
<div id="lv-container" data-id="city" data-uid="MTAyMC8yOTk3My82NTM4"></div>
</div>
</div>
<div class="sidebar-toggle">
<div class="sidebar-toggle-line-wrap">
<span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
<span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
<span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
</div>
</div>
<aside id="sidebar" class="sidebar">
<div class="sidebar-inner">
<ul class="sidebar-nav motion-element">
<li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
文章目录
</li>
<li class="sidebar-nav-overview" data-target="site-overview-wrap">
站点概览
</li>
</ul>
<div class="site-overview-wrap sidebar-panel">
<div class="site-overview">
<div class="site-author motion-element" itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<img class="site-author-image" itemprop="image" src="https://hexoblog-1254111960.cos.ap-guangzhou.myqcloud.com/HexoBlog-tou.jpg" alt="Daniel X">
<p class="site-author-name" itemprop="name">Daniel X</p>
<div class="site-description motion-element" itemprop="description">專注于大数据技術，分享干货</div>
</div>
<nav class="site-state motion-element">
<div class="site-state-item site-state-posts">
<a href="/archives/">
<span class="site-state-item-count">125</span>
<span class="site-state-item-name">日志</span>
</a>
</div>
<div class="site-state-item site-state-categories">
<a href="/categories">
<span class="site-state-item-count">15</span>
<span class="site-state-item-name">分类</span>
</a>
</div>
<div class="site-state-item site-state-tags">
<a href="/tags">
<span class="site-state-item-count">63</span>
<span class="site-state-item-name">标签</span>
</a>
</div>
</nav>
<div class="links-of-author motion-element">
<span class="links-of-author-item">
<span class="exturl" data-url="aHR0cHM6Ly9naXRodWIuY29tL2R1ZGVmdQ==" title="GitHub → https://github.com/dudefu"><i class="fa fa-fw fa-github"></i>GitHub</span>
</span>
<span class="links-of-author-item">
<span class="exturl" data-url="bWFpbHRvOmR1ZGVmdUBmb3htYWlsLmNvbT9zdWJqZWN0PUhlbGxvJTIwYWdhaW4=" title="E-mail → mailto:dudefu@foxmail.com?subject=Hello%20again"><i class="fa fa-fw fa-envelope"></i>E-mail</span>
</span>
<span class="links-of-author-item">
<span class="exturl" data-url="aHR0cHM6Ly93ZWliby5jb20vZHVkZWZ1" title="Weibo → https://weibo.com/dudefu"><i class="fa fa-fw fa-weibo"></i>Weibo</span>
</span>
<span class="links-of-author-item">
<span class="exturl" data-url="aHR0cHM6Ly93cGEucXEuY29tL21zZ3JkP3Y9MyZ1aW49MTU3NzU3MTk1OSZzaXRlPWR1ZGVmdS5pbmZvJm1lbnU9eWVz" title="QQ → https://wpa.qq.com/msgrd?v=3&uin=1577571959&site=dudefu.info&menu=yes"><i class="fa fa-fw fa-qq"></i>QQ</span>
</span>
</div>
</div>
</div>
<!--noindex-->
<div class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
<div class="post-toc">
<div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#背景"><span class="nav-number">1.</span> <span class="nav-text">背景</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#一个慢查询引发的思考"><span class="nav-number">2.</span> <span class="nav-text">一个慢查询引发的思考</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#MySQL索引原理"><span class="nav-number">3.</span> <span class="nav-text">MySQL索引原理</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#索引目的"><span class="nav-number">3.1.</span> <span class="nav-text">索引目的</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#索引原理"><span class="nav-number">3.2.</span> <span class="nav-text">索引原理</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#磁盘IO与预读"><span class="nav-number">3.2.1.</span> <span class="nav-text">磁盘IO与预读</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引的数据结构"><span class="nav-number">3.2.2.</span> <span class="nav-text">索引的数据结构</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#详解b-树"><span class="nav-number">3.2.3.</span> <span class="nav-text">详解b+树</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#b-树的查找过程"><span class="nav-number">3.2.4.</span> <span class="nav-text">b+树的查找过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#b-树性质"><span class="nav-number">3.2.5.</span> <span class="nav-text">b+树性质</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#慢查询优化"><span class="nav-number">4.</span> <span class="nav-text">慢查询优化</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#建索引的几大原则"><span class="nav-number">4.1.</span> <span class="nav-text">建索引的几大原则</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#回到开始的慢查询"><span class="nav-number">4.2.</span> <span class="nav-text">回到开始的慢查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询优化神器-explain命令"><span class="nav-number">4.3.</span> <span class="nav-text">查询优化神器 - explain命令</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#慢查询优化基本步骤"><span class="nav-number">4.4.</span> <span class="nav-text">慢查询优化基本步骤</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#几个慢查询案例"><span class="nav-number">4.5.</span> <span class="nav-text">几个慢查询案例</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#复杂语句写法"><span class="nav-number">4.5.1.</span> <span class="nav-text">复杂语句写法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#明确应用场景"><span class="nav-number">4.5.2.</span> <span class="nav-text">明确应用场景</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#无法优化的语句"><span class="nav-number">4.5.3.</span> <span class="nav-text">无法优化的语句</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#写在后面的话"><span class="nav-number">5.</span> <span class="nav-text">写在后面的话</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#参考文献："><span class="nav-number">6.</span> <span class="nav-text">参考文献：</span></a></li></ol></div>
</div>
</div>
<!--/noindex-->
</div>
</aside>
</div>
</main>
<footer id="footer" class="footer">
<div class="footer-inner">
<div class="copyright"> <span class="exturl" data-url="aHR0cDovL3d3dy5iZWlhbi5taWl0Lmdvdi5jbg==">粤ICP备18110871号 </span>© 2017 – <span itemprop="copyrightYear">2021</span>
<span class="with-love" id="animate">
<i class="fa fa-spinner"></i>
</span>
<span class="author" itemprop="copyrightHolder">dudefu</span>
</div>
<!--
<div class="powered-by">由 <span class="exturl theme-link" data-url="aHR0cHM6Ly9oZXhvLmlv">Hexo</span> 强力驱动 v3.8.0</div>
<span class="post-meta-divider">|</span>
<div class="theme-info">主题 – <span class="exturl theme-link" data-url="aHR0cHM6Ly90aGVtZS1uZXh0Lm9yZw==">NexT.Pisces</span> v7.1.2</div>
-->
</div>
</footer>
<div class="back-to-top">
<i class="fa fa-arrow-up"></i>
<span id="scrollpercent"><span>0</span>%</span>
</div>
</div>
<script>
if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
window.Promise = null;
}
</script>
<script color="26,26,26" opacity="0.5" zindex="-1" count="99" src="//cdn.jsdelivr.net/gh/theme-next/theme-next-canvas-nest@1/canvas-nest.min.js"></script>
<script id="ribbon" size="300" alpha="0.6" zindex="-1" src="/lib/canvas-ribbon/canvas-ribbon.js"></script>
<script src="/lib/jquery/index.js?v=3.4.1"></script>
<script src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
<script src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
<script src="/lib/reading_progress/reading_progress.js"></script>
<script src="/js/utils.js?v=7.1.2"></script>
<script src="/js/motion.js?v=7.1.2"></script>
<script src="/js/affix.js?v=7.1.2"></script>
<script src="/js/schemes/pisces.js?v=7.1.2"></script>
<script src="/js/scrollspy.js?v=7.1.2"></script>
<script src="/js/post-details.js?v=7.1.2"></script>
<script src="/js/next-boot.js?v=7.1.2"></script>
<script src="/js/js.cookie.js?v=7.1.2"></script>
<script src="/js/scroll-cookie.js?v=7.1.2"></script>
<script src="/js/exturl.js?v=7.1.2"></script>
<script src="//cdn1.lncld.net/static/js/3.11.1/av-min.js"></script>
<script src="//unpkg.com/valine/dist/Valine.min.js"></script>
<script>
var GUEST = ['nick', 'mail', 'link'];
var guest = 'nick,mail,link';
guest = guest.split(',').filter(function(item) {
return GUEST.indexOf(item) > -1;
});
new Valine({
el: '#comments',
verify: true,
notify: true,
appId: '1N5rpk874DGudJw2wCL9J011-gzGzoHsz',
appKey: '9Y83e6suJgx567wtxhKy45IN',
placeholder: 'Just go go',
avatar: 'mm',
meta: guest,
pageSize: '10' || 10,
visitor: true,
lang: 'zk-cn' || 'zh-cn'
});
</script>
<script>
window.livereOptions = {
refer: 'MySQL索引原理及慢查询优化.html'
};
(function(d, s) {
var j, e = d.getElementsByTagName(s)[0];
if (typeof LivereTower === 'function') { return; }
j = d.createElement(s);
j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
j.async = true;
e.parentNode.insertBefore(j, e);
})(document, 'script');
</script>
<script>
// Popup Window;
var isfetched = false;
var isXml = true;
// Search DB path;
var search_path = "search.xml";
if (search_path.length === 0) {
search_path = "search.xml";
} else if (/json$/i.test(search_path)) {
isXml = false;
}
var path = "/" + search_path;
// monitor main search box;
var onPopupClose = function (e) {
$('.popup').hide();
$('#local-search-input').val('');
$('.search-result-list').remove();
$('#no-result').remove();
$(".local-search-pop-overlay").remove();
$('body').css('overflow', '');
}
function proceedsearch() {
$("body")
.append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
.css('overflow', 'hidden');
$('.search-popup-overlay').click(onPopupClose);
$('.popup').toggle();
var $localSearchInput = $('#local-search-input');
$localSearchInput.attr("autocapitalize", "none");
$localSearchInput.attr("autocorrect", "off");
$localSearchInput.focus();
}
// search function;
var searchFunc = function(path, search_id, content_id) {
'use strict';
// start loading animation
$("body")
.append('<div class="search-popup-overlay local-search-pop-overlay">' +
'<div id="search-loading-icon">' +
'<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
'</div>' +
'</div>')
.css('overflow', 'hidden');
$("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');
$.ajax({
url: path,
dataType: isXml ? "xml" : "json",
async: true,
success: function(res) {
// get the contents from search data
isfetched = true;
$('.popup').detach().appendTo('.header-inner');
var datas = isXml ? $("entry", res).map(function() {
return {
title: $("title", this).text(),
content: $("content",this).text(),
url: $("url" , this).text()
};
}).get() : res;
var input = document.getElementById(search_id);
var resultContent = document.getElementById(content_id);
var inputEventFunction = function() {
var searchText = input.value.trim().toLowerCase();
var keywords = searchText.split(/[\s\-]+/);
if (keywords.length > 1) {
keywords.push(searchText);
}
var resultItems = [];
if (searchText.length > 0) {
// perform local searching
datas.forEach(function(data) {
var isMatch = false;
var hitCount = 0;
var searchTextCount = 0;
var title = data.title.trim();
var titleInLowerCase = title.toLowerCase();
var content = data.content.trim().replace(/<[^>]+>/g,"");
var contentInLowerCase = content.toLowerCase();
var articleUrl = decodeURIComponent(data.url).replace(/\/{2,}/g, '/');
var indexOfTitle = [];
var indexOfContent = [];
// only match articles with not empty titles
if(title != '') {
keywords.forEach(function(keyword) {
function getIndexByWord(word, text, caseSensitive) {
var wordLen = word.length;
if (wordLen === 0) {
return [];
}
var startPosition = 0, position = [], index = [];
if (!caseSensitive) {
text = text.toLowerCase();
word = word.toLowerCase();
}
while ((position = text.indexOf(word, startPosition)) > -1) {
index.push({position: position, word: word});
startPosition = position + wordLen;
}
return index;
}
indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
});
if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
isMatch = true;
hitCount = indexOfTitle.length + indexOfContent.length;
}
}
// show search results
if (isMatch) {
// sort index by position of keyword
[indexOfTitle, indexOfContent].forEach(function (index) {
index.sort(function (itemLeft, itemRight) {
if (itemRight.position !== itemLeft.position) {
return itemRight.position - itemLeft.position;
} else {
return itemLeft.word.length - itemRight.word.length;
}
});
});
// merge hits into slices
function mergeIntoSlice(text, start, end, index) {
var item = index[index.length - 1];
var position = item.position;
var word = item.word;
var hits = [];
var searchTextCountInSlice = 0;
while (position + word.length <= end && index.length != 0) {
if (word === searchText) {
searchTextCountInSlice++;
}
hits.push({position: position, length: word.length});
var wordEnd = position + word.length;
// move to next position of hit
index.pop();
while (index.length != 0) {
item = index[index.length - 1];
position = item.position;
word = item.word;
if (wordEnd > position) {
index.pop();
} else {
break;
}
}
}
searchTextCount += searchTextCountInSlice;
return {
hits: hits,
start: start,
end: end,
searchTextCount: searchTextCountInSlice
};
}
var slicesOfTitle = [];
if (indexOfTitle.length != 0) {
slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
}
var slicesOfContent = [];
while (indexOfContent.length != 0) {
var item = indexOfContent[indexOfContent.length - 1];
var position = item.position;
var word = item.word;
// cut out 100 characters
var start = position - 20;
var end = position + 80;
if(start < 0){
start = 0;
}
if (end < position + word.length) {
end = position + word.length;
}
if(end > content.length){
end = content.length;
}
slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
}
// sort slices in content by search text's count and hits' count
slicesOfContent.sort(function (sliceLeft, sliceRight) {
if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
return sliceRight.searchTextCount - sliceLeft.searchTextCount;
} else if (sliceLeft.hits.length !== sliceRight.hits.length) {
return sliceRight.hits.length - sliceLeft.hits.length;
} else {
return sliceLeft.start - sliceRight.start;
}
});
// select top N slices in content
var upperBound = parseInt('1');
if (upperBound >= 0) {
slicesOfContent = slicesOfContent.slice(0, upperBound);
}
// highlight title and content
function highlightKeyword(text, slice) {
var result = '';
var prevEnd = slice.start;
slice.hits.forEach(function (hit) {
result += text.substring(prevEnd, hit.position);
var end = hit.position + hit.length;
result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
prevEnd = end;
});
result += text.substring(prevEnd, slice.end);
return result;
}
var resultItem = '';
if (slicesOfTitle.length != 0) {
resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
} else {
resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
}
slicesOfContent.forEach(function (slice) {
resultItem += "<a href='" + articleUrl + "'>" +
"<p class=\"search-result\">" + highlightKeyword(content, slice) +
"...</p>" + "</a>";
});
resultItem += "</li>";
resultItems.push({
item: resultItem,
searchTextCount: searchTextCount,
hitCount: hitCount,
id: resultItems.length
});
}
})
};
if (keywords.length === 1 && keywords[0] === "") {
resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x"></i></div>'
} else if (resultItems.length === 0) {
resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x"></i></div>'
} else {
resultItems.sort(function (resultLeft, resultRight) {
if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
return resultRight.searchTextCount - resultLeft.searchTextCount;
} else if (resultLeft.hitCount !== resultRight.hitCount) {
return resultRight.hitCount - resultLeft.hitCount;
} else {
return resultRight.id - resultLeft.id;
}
});
var searchResultList = '<ul class=\"search-result-list\">';
resultItems.forEach(function (result) {
searchResultList += result.item;
})
searchResultList += "</ul>";
resultContent.innerHTML = searchResultList;
}
}
if ('auto' === 'auto') {
input.addEventListener('input', inputEventFunction);
} else {
$('.search-icon').click(inputEventFunction);
input.addEventListener('keypress', function (event) {
if (event.keyCode === 13) {
inputEventFunction();
}
});
}
// remove loading animation
$(".local-search-pop-overlay").remove();
$('body').css('overflow', '');
proceedsearch();
}
});
}
// handle and trigger popup window;
$('.popup-trigger').click(function(e) {
e.stopPropagation();
if (isfetched === false) {
searchFunc(path, 'local-search-input', 'local-search-result');
} else {
proceedsearch();
};
});
$('.popup-btn-close').click(onPopupClose);
$('.popup').click(function(e){
e.stopPropagation();
});
$(document).on('keyup', function (event) {
var shouldDismissSearchPopup = event.which === 27 &&
$('.search-popup').is(':visible');
if (shouldDismissSearchPopup) {
onPopupClose();
}
});
</script>
<script src="https://www.gstatic.com/firebasejs/4.6.0/firebase.js"></script>
<script src="https://www.gstatic.com/firebasejs/4.6.0/firebase-firestore.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bluebird/3.5.1/bluebird.core.min.js"></script>
<script>
(function () {
firebase.initializeApp({
apiKey: '',
projectId: ''
})
function getCount(doc, increaseCount) {
//increaseCount will be false when not in article page
return doc.get().then(function (d) {
var count
if (!d.exists) { //has no data, initialize count
if (increaseCount) {
doc.set({
count: 1
})
count = 1
}
else {
count = 0
}
}
else { //has data
count = d.data().count
if (increaseCount) {
if (!(window.localStorage && window.localStorage.getItem(title))) { //if first view this article
doc.set({ //increase count
count: count + 1
})
count++
}
}
}
if (window.localStorage && increaseCount) { //mark as visited
localStorage.setItem(title, true)
}
return count
})
}
function appendCountTo(el) {
return function (count) {
$(el).append(
$('<span>').addClass('post-visitors-count').append(
$('<span>').addClass('post-meta-divider').text('|')
).append(
$('<span>').addClass('post-meta-item-icon').append(
$('<i>').addClass('fa fa-users')
)
).append($('<span>').text('阅读次数 ' + count))
)
}
}
var db = firebase.firestore()
var articles = db.collection('articles')
//https://hexo.io/docs/variables.html
var isPost = 'MySQL索引原理及慢查询优化'.length > 0
var isArchive = '' === 'true'
var isCategory = ''.length > 0
var isTag = ''.length > 0
if (isPost) { //is article page
var title = 'MySQL索引原理及慢查询优化'
var doc = articles.doc(title)
getCount(doc, true).then(appendCountTo($('.post-meta')))
}
else if (!isArchive && !isCategory && !isTag) { //is index page
var titles = [] //array to titles
var postsstr = '' //if you have a better way to get titles of posts, please change it
eval(postsstr)
var promises = titles.map(function (title) {
return articles.doc(title)
}).map(function (doc) {
return getCount(doc)
})
Promise.all(promises).then(function (counts) {
var metas = $('.post-meta')
counts.forEach(function (val, idx) {
appendCountTo(metas[idx])(val)
})
})
}
})()
</script>
<script>
if ($('body').find('div.pdf').length) {
$.ajax({
type: 'GET',
url: '//cdn.jsdelivr.net/npm/pdfobject@2/pdfobject.min.js',
dataType: 'script',
cache: true,
success: function() {
$('body').find('div.pdf').each(function(i, o) {
PDFObject.embed($(o).attr('target'), $(o), {
pdfOpenParams: {
navpanes: 0,
toolbar: 0,
statusbar: 0,
pagemode: 'thumbs',
view: 'FitH'
},
PDFJS_URL: '/lib/pdf/web/viewer.html',
height: $(o).attr('height') || '500px'
});
});
},
});
}
</script>
<script>
if ($('body').find('pre.mermaid').length) {
$.ajax({
type: 'GET',
url: '//cdn.jsdelivr.net/npm/mermaid@8/dist/mermaid.min.js',
dataType: 'script',
cache: true,
success: function() {
mermaid.initialize({
theme: 'dark',
logLevel: 3,
flowchart: { curve: 'linear' },
gantt: { axisFormat: '%m/%d/%Y' },
sequence: { actorMargin: 50 }
});
}
});
}
</script>
<script>
(function(){
var bp = document.createElement('script');
var curProtocol = window.location.protocol.split(':')[0];
bp.src = (curProtocol === 'https') ? 'https://zz.bdstatic.com/linksubmit/push.js' : 'http://push.zhanzhang.baidu.com/push.js';
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(bp, s);
})();
</script>
<script src="/lib/bookmark/bookmark.min.js?v=1.0"></script>
<script>
bookmark.scrollToMark('auto', "#更多");
</script>
<script>
$('.highlight').not('.gist .highlight').each(function(i, e) {
var $wrap = $('<div>').addClass('highlight-wrap');
$(e).after($wrap);
$wrap.append($('<button>').addClass('copy-btn').append('复制').on('click', function(e) {
var code = $(this).parent().find('.code').find('.line').map(function(i, e) {
return $(e).text();
}).toArray().join('\n');
var ta = document.createElement('textarea');
var yPosition = window.pageYOffset || document.documentElement.scrollTop;
ta.style.top = yPosition + 'px'; // Prevent page scroll
ta.style.position = 'absolute';
ta.style.opacity = '0';
ta.readOnly = true;
ta.value = code;
document.body.appendChild(ta);
const selection = document.getSelection();
const selected = selection.rangeCount > 0 ? selection.getRangeAt(0) : false;
ta.select();
ta.setSelectionRange(0, code.length);
ta.readOnly = false;
var result = document.execCommand('copy');
if (result) $(this).text('复制成功');
else $(this).text('复制失败');
ta.blur(); // For iOS
$(this).blur();
if (selected) {
selection.removeAllRanges();
selection.addRange(selected);
}
})).on('mouseleave', function(e) {
var $b = $(this).find('.copy-btn');
setTimeout(function() {
$b.text('复制');
}, 300);
}).append(e);
})
</script>
</body>
</html>
Loading...
举报
请认真填写举报原因，尽可能描述详细。
请选择举报类型
误判申诉
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化