-
Notifications
You must be signed in to change notification settings - Fork 0
/
51857.html
309 lines (300 loc) · 37.8 KB
/
51857.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>索引补充 | 征蓬</title><meta name="keywords" content="MySQL"><meta name="author" content="huy"><meta name="copyright" content="huy"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="索引其他知识补充，选择、优化等。">
<meta property="og:type" content="article">
<meta property="og:title" content="索引补充">
<meta property="og:url" content="http://example.com/51857.html">
<meta property="og:site_name" content="征蓬">
<meta property="og:description" content="索引其他知识补充，选择、优化等。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/pics/scenery/27.jpg">
<meta property="article:published_time" content="2021-09-01T07:40:05.000Z">
<meta property="article:modified_time" content="2021-09-10T07:04:44.981Z">
<meta property="article:author" content="huy">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/pics/scenery/27.jpg"><link rel="shortcut icon" href="/pics/avatar/8.png"><link rel="canonical" href="http://example.com/51857"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://lf3-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="/font/wcsf.ttf" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
root: '/',
algolia: undefined,
localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
translate: undefined,
noticeOutdate: undefined,
highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
copy: {
success: '复制成功',
error: '复制错误',
noSupport: '浏览器不支持'
},
relativeDate: {
homepage: false,
post: false
},
runtime: '',
date_suffix: {
just: '刚刚',
min: '分钟前',
hour: '小时前',
day: '天前',
month: '个月前'
},
copyright: undefined,
lightbox: 'fancybox',
Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"bottom-left"},
source: {
jQuery: 'https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/jquery/3.6.0/jquery.min.js',
justifiedGallery: {
js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
},
fancybox: {
js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
}
},
isPhotoFigcaption: false,
islazyload: false,
isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
title: '索引补充',
isPost: true,
isHome: false,
isHighlightShrink: false,
isToc: true,
postUpdate: '2021-09-10 15:04:44'
}</script><noscript><style type="text/css">
#nav {
opacity: 1
}
.justified-gallery img {
opacity: 1
}
#recent-posts time,
#post-meta time {
display: inline !important
}
</style></noscript><script>(win=>{
win.saveToLocal = {
set: function setWithExpiry(key, value, ttl) {
if (ttl === 0) return
const now = new Date()
const expiryDay = ttl * 86400000
const item = {
value: value,
expiry: now.getTime() + expiryDay,
}
localStorage.setItem(key, JSON.stringify(item))
},
get: function getWithExpiry(key) {
const itemStr = localStorage.getItem(key)
if (!itemStr) {
return undefined
}
const item = JSON.parse(itemStr)
const now = new Date()
if (now.getTime() > item.expiry) {
localStorage.removeItem(key)
return undefined
}
return item.value
}
}
win.getScript = url => new Promise((resolve, reject) => {
const script = document.createElement('script')
script.src = url
script.async = true
script.onerror = reject
script.onload = script.onreadystatechange = function() {
const loadState = this.readyState
if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
script.onload = script.onreadystatechange = null
resolve()
}
document.head.appendChild(script)
})
win.activateDarkMode = function () {
document.documentElement.setAttribute('data-theme', 'dark')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
}
}
win.activateLightMode = function () {
document.documentElement.setAttribute('data-theme', 'light')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
}
}
const t = saveToLocal.get('theme')
if (t === 'dark') activateDarkMode()
else if (t === 'light') activateLightMode()
const asideStatus = saveToLocal.get('aside-status')
if (asideStatus !== undefined) {
if (asideStatus === 'hide') {
document.documentElement.classList.add('hide-aside')
} else {
document.documentElement.classList.remove('hide-aside')
}
}
const detectApple = () => {
if (GLOBAL_CONFIG_SITE.isHome && /iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
document.documentElement.classList.add('apple')
}
}
detectApple()
})(window)</script><link rel="stylesheet" href="/css/custom.css" media="defer" onload="this.media='all'"><!-- hexo injector head_end start --><link rel="stylesheet" href="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hexo-butterfly-tag-plugins-plus@latest/lib/tag_plugins.min.css" media="defer" onload="this.media='all'"><script async src="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/carousel-touch.js"></script><!-- hexo injector head_end end --><meta name="generator" content="Hexo 5.4.1"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/pics/Asoul/8.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">54</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">17</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">12</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('/pics/scenery/27.jpg')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">征蓬</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">索引补充</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-09-01T07:40:05.000Z" title="发表于 2021-09-01 15:40:05">2021-09-01</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-09-10T07:04:44.981Z" title="更新于 2021-09-10 15:04:44">2021-09-10</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/MySQL/">MySQL</a></span></div><div class="meta-secondline"></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h2 id="索引类型">索引类型</h2>
<p>这部分可以参考之前的内容：<a target="_blank" rel="noopener" href="http://localhost:4000/47099.html">MySQL实战 基础篇（三）</a></p>
<h2 id="普通索引和唯一索引">普通索引和唯一索引</h2>
<h3 id="查询过程的区别">查询过程的区别</h3>
<ul>
<li>
<p>普通索引<br>
查询到第一个符合条件的记录后，继续查询，在碰到第一个不符合条件的记录时停止。</p>
</li>
<li>
<p>唯一索引<br>
查询到第一个符合条件的记录后，停止查询。</p>
</li>
</ul>
<h3 id="更新过程的区别">更新过程的区别</h3>
<p>需要分两种情况：要更新的目标页在内存中，或者不在内存中。</p>
<ol>
<li>目标页在内存中
<ul>
<li>普通索引：找到 3 到 5 之间的位置，插入记录，结束；</li>
<li>唯一索引：找到 3 到 5 之间的位置，判断是否有冲突，插入记录，结束。</li>
</ul>
</li>
<li>要更新的目标页不在内存中
<ul>
<li>普通索引：将记录更新到 change buffer 中，结束；</li>
<li>唯一索引：读取数据页到内存，判断是否有冲突，插入记录，结束。</li>
</ul>
</li>
</ol>
<h3 id="如何选择和使用">如何选择和使用</h3>
<p>如果业务可以接受（业务代码保证不会写入重复数据），优先使用普通索引，change buffer 优化效果明显。</p>
<p>如果业务要求数据库对字段进行约束，就只能选择唯一索引。</p>
<ul>
<li>查询<br>
二者区别不大，仅有一些特殊情况（例如：索引在数据页的末尾，需要读下一页进行判断时，但是MySQL有预读机制）。</li>
<li>插入、更新
<ul>
<li>尽量选择 <strong>普通索引</strong></li>
<li>如果有更新后立即查询的场景，也使用 <strong>普通索引</strong>，并关闭 change buffer</li>
<li>change buffer 和 <strong>普通索引</strong> 搭配使用时，对大数据量的表和使用机械硬盘存储的优化效果明显</li>
</ul>
</li>
<li>备注
<ul>
<li>如果碰上大量插入数据慢、内存命中率低时，可以根据索引排查原因。</li>
</ul>
</li>
</ul>
<h2 id="为什么会选择错误的索引">为什么会选择错误的索引</h2>
<p>如果在 SQL 语句中没有指定索引，就会由优化器选择，有时本可以执行很快的语句，却因为错误索引导致执行速度变慢。</p>
<p>优化器在选择索引时主要考虑以下几个因素:</p>
<ol>
<li>是否需要排序</li>
<li>是否使用临时表</li>
<li>扫描行数</li>
</ol>
<h3 id="如何判断扫描行数">如何判断扫描行数</h3>
<p>扫描行数根据索引的选择性进行判断，索引上不同的值越多，选择性越高，不同值的个数称为基数（cardinality）。</p>
<article class="message is-info"><div class="message-header">
<p>例如：</p>
</div><div class="message-body">
<p>表 t 有 100 条记录，在字段 a 上建立索引：<br>
如果 a 的值是 1 ~ 100，则 a 有 100 个不同值，<b>基数高</b>（很快可以查出主键ID，再回表查询记录），<b>选择性好</b>；<br>
如果 a 的值都是 1，则字段 a 只有一个值，<b>基数很低</b>，<b>选择性很差</b>，a 索引就没有意义。</p>
</div></article>
<h4 id="索引的选择性">索引的选择性</h4>
<p>索引的选择性是指 <strong>不重复的索引值（基数） 和 数据表记录总数的比值</strong>。</p>
<p>选择性越高，查询效率越高，因为可以过滤更多的行。唯一索引的选择性为 1，是最高的索引选择性，查询效率也是最高的。</p>
<p>可以用以下语句计算</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">SELECT</span> <span class="built_in">COUNT</span>(<span class="keyword">DISTINCT</span> b)<span class="operator">/</span><span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">FROM</span> t;</span><br></pre></td></tr></table></figure>
<h4 id="基数（cardinality）">基数（cardinality）</h4>
<ul>
<li>如何获取基数？<br>
使用 <strong>随机采样统计</strong> 的方法估算行数；<br>
如果逐行进行统计，得到的结果精确，但是代价太高。</li>
<li>自动触发索引统计<br>
统计信息会随着记录变更而修改，当变更行数超过 1/m 时，就会自动重新统计。<br>
例如：表 t 有 100 行数据，变更超过 10 行时，就会自动触发重新统计。</li>
</ul>
<article class="message is-info"><div class="message-header">
<p>基数的计算过程：</p>
</div><div class="message-body">
<p><b>选择 N 个数据页，统计数据页上的不同值，得到这些不同值的平均值，再乘索引页面数。</b></p>
<p>选择 3 个数据页（page1、page2、page3），假设有 10 个索引页，数据页的不同值分别为<br>
page1：10、page2：20、page3：15；<br>
则索引基数为：基数 = (10 + 20 + 15)/3 * 10 = 150</p>
</div></article>
<p>在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：<br>
N：数据页的数量；M：变更行数的比例。</p>
<ul>
<li>设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。</li>
<li>设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。</li>
</ul>
<h3 id="选择异常时如何处理">选择异常时如何处理</h3>
<h4 id="强制指定">强制指定</h4>
<p>使用 <em>force index</em> 强制指定索引，例如：</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">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> t force index(a) <span class="keyword">where</span> a <span class="keyword">between</span> <span class="number">100</span> <span class="keyword">and</span> <span class="number">20000</span> <span class="keyword">order</span> <span class="keyword">by</span> b;</span><br></pre></td></tr></table></figure>
<p>使用该方法时，MySQL 将不会评估其他索引的执行效率，将会直接使用指定的索引。</p>
<p>但是这种方法很不方便，如果索引名称变更，则 sql 语句也需要修改；如果迁移数据库，也可能不支持该方法。</p>
<h4 id="引导优化器使用正确的索引">引导优化器使用正确的索引</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></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改前</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> t force index(a) <span class="keyword">where</span> a <span class="keyword">between</span> <span class="number">100</span> <span class="keyword">and</span> <span class="number">2000</span> <span class="keyword">and</span> b <span class="keyword">between</span> <span class="number">3000</span> <span class="keyword">and</span> <span class="number">5000</span> <span class="keyword">order</span> <span class="keyword">by</span> b;</span><br><span class="line"><span class="comment">--修改后</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> t force index(a) <span class="keyword">where</span> a <span class="keyword">between</span> <span class="number">100</span> <span class="keyword">and</span> <span class="number">2000</span> <span class="keyword">and</span> b <span class="keyword">between</span> <span class="number">3000</span> <span class="keyword">and</span> <span class="number">5000</span> <span class="keyword">order</span> <span class="keyword">by</span> b, a;</span><br></pre></td></tr></table></figure>
<p>修改前使用 b 索引，可以避免重新排序，只要遍历，所以优化器采用了 b 索引。</p>
<p>修改后使用 b, a 两字段进行排序，意味着需要将两个索引都需要排序，这时候，扫描行数就成为选择索引的关键条件。</p>
<p>这种方法也不太好用，不仅没有通用性，还难以维护。</p>
<h4 id="新建或者删除索引">新建或者删除索引</h4>
<p>既然当前索引不合适，可以选择新建一个索引，提供给优化器做选择；或者删除掉误用或者没有必要的索引，帮助优化器排除错误选项。</p>
<p>这种方法简单粗暴好用。</p>
<h2 id="如何创建高性能的索引">如何创建高性能的索引</h2>
<h3 id="查询时使用独立列">查询时使用独立列</h3>
<p>独立的列是指：索引列不能作为表达式的一部分，也不能是函数的参数。</p>
<p><img src="/pics/MySQL/%E7%B4%A2%E5%BC%95%E8%A1%A5%E5%85%85-%E7%A4%BA%E4%BE%8B%E5%9B%BE1.png" alt="索引列参与运算时"></p>
<p><img src="/pics/MySQL/%E7%B4%A2%E5%BC%95%E8%A1%A5%E5%85%85-%E7%A4%BA%E4%BE%8B%E5%9B%BE2.png" alt="正常使用索引"></p>
<p>使用 explan 命令很容易看出，作为表达式的一部分时，没有使用索引。</p>
<p>使用中应该简化 where 条件，始终将索引列单独放在比较符号的一边。</p>
<h3 id="前缀索引">前缀索引</h3>
<p>有时需要在很长的字符串上建立索引，例如：邮箱、身份证号等，字符串长度过长会影响索引速度。</p>
<p>可以索引开始的部分字符，降低索引长度，提高效率，但是也会降低索引的选择性（选择性可以参考本文中 3.1.1 ：<a target="_blank" rel="noopener" href="http://localhost:4000/51857.html#%E7%B4%A2%E5%BC%95%E7%9A%84%E9%80%89%E6%8B%A9%E6%80%A7">索引的选择性</a>）。</p>
<p>一般情况下，某个列的前缀选择性可以满足查询性能的。对于 BLOB、TEXT、很长的 VARCHAR 列，必须使用前缀索引。</p>
<article class="message is-info"><div class="message-header">
<p>举例：</p>
</div><div class="message-body">
<p>使用邮箱登录的系统中建立用户表，id 为自增主键，email 列上也需要建立索引。</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></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 该索引包含每个记录 email 字段的全部字符串</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> user_info <span class="keyword">add</span> index index1(email);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 该索引包含每个记录 email 字段的前 6 个字符</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> user_info <span class="keyword">add</span> index index2(email(<span class="number">6</span>));</span><br></pre></td></tr></table></figure>
<p><strong>如何确定前缀索引的的长度呢？</strong><br>
需要关注索引的选择性，选择性越高，则重复的值越少，可以参考 <a target="_blank" rel="noopener" href="https://lazylutra.github.io/51857.html#%E7%B4%A2%E5%BC%95%E7%9A%84%E9%80%89%E6%8B%A9%E6%80%A7">3.1.1 索引的选择性</a>。</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="keyword">SELECT</span> </span><br><span class="line"> <span class="built_in">COUNT</span>(<span class="keyword">DISTINCT</span> <span class="keyword">left</span>(email, <span class="number">4</span>))<span class="operator">/</span><span class="built_in">COUNT</span>(<span class="operator">*</span>),</span><br><span class="line"> <span class="built_in">COUNT</span>(<span class="keyword">DISTINCT</span> <span class="keyword">left</span>(email, <span class="number">5</span>))<span class="operator">/</span><span class="built_in">COUNT</span>(<span class="operator">*</span>),</span><br><span class="line"> <span class="built_in">COUNT</span>(<span class="keyword">DISTINCT</span> <span class="keyword">left</span>(email, <span class="number">6</span>))<span class="operator">/</span><span class="built_in">COUNT</span>(<span class="operator">*</span>)</span><br><span class="line"> <span class="keyword">FROM</span> t;</span><br></pre></td></tr></table></figure>
<p><strong>执行顺序：</strong></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">SELECT</span> username, email <span class="keyword">FROM</span> user_info <span class="keyword">WHERE</span> email <span class="operator">=</span> <span class="string">'[email protected]'</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>
<p>index1（即 email 整个字符串的索引结构）</p>
<ol>
<li>index1 索引树找到满足索引值为 <a href="mailto:[email protected]">[email protected]</a> 的记录，并获取主键 id；</li>
<li>为了获取 username 的值，需要用 id 回主键表查询记录，将这行记录加入结果集；</li>
<li>因为是普通索引，需要取 index1 索引树上的下一条记录，发现条件不满足 <a href="mailto:[email protected]">[email protected]</a>，循环结束。</li>
</ol>
<p><img src="/pics/MySQL/%E7%B4%A2%E5%BC%95%E8%A1%A5%E5%85%85-%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95-index1.png" alt="email索引结构图"></p>
</li>
<li>
<p>index2（即 email(6) 索引结构）</p>
<ol>
<li>在 index2 索引树上查找满足 123456 的结果，id 为1；</li>
<li>回主键表，查询 id = 1 的记录，验证 email 是否为 <a href="mailto:[email protected]">[email protected]</a>，如果不是则弃用，继续循环；</li>
<li>假设 index2 查询到下一条记录的索引仍是 12346，则取出 id，回表，校验 email，结果一致时加入结果集；</li>
<li>重复上述循环，直到 index2 上取得索引不是 123456，返回结果集。</li>
</ol>
<p><img src="/pics/MySQL/%E7%B4%A2%E5%BC%95%E8%A1%A5%E5%85%85-%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95-index2.png" alt="email(6)索引结构图"></p>
</li>
</ul>
<p>这个过程可以看出，前缀索引的扫描次数要多于普通索引。</p>
<p>优点：<strong>如果能控制好前缀索引的长度，可以做到既省空间、又不额外增加查询成本。</strong></p>
<p>缺点：<strong>增加扫描次数，不能使用覆盖索引。</strong></p>
</div></article>
<h2 id="建表语句">建表语句</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></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `t` (</span><br><span class="line"> `id` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line"> `a` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span>,</span><br><span class="line"> `b` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span>,</span><br><span class="line"> <span class="keyword">PRIMARY</span> KEY (`id`),</span><br><span class="line"> KEY `a` (`a`),</span><br><span class="line"> KEY `b` (`b`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB；</span><br></pre></td></tr></table></figure>
<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></pre></td><td class="code"><pre><span class="line">delimiter ;;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> idata()</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="keyword">declare</span> i <span class="type">int</span>;</span><br><span class="line"> <span class="keyword">set</span> i<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line"> while(i<span class="operator"><=</span><span class="number">100000</span>)do</span><br><span class="line"> <span class="keyword">insert</span> <span class="keyword">into</span> t <span class="keyword">values</span>(i, i, i);</span><br><span class="line"> <span class="keyword">set</span> i<span class="operator">=</span>i<span class="operator">+</span><span class="number">1</span>;</span><br><span class="line"> <span class="keyword">end</span> while;</span><br><span class="line"><span class="keyword">end</span>;;</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="keyword">call</span> idata();</span><br></pre></td></tr></table></figure>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">huy</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://example.com/51857.html">http://example.com/51857.html</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://example.com" target="_blank">征蓬</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySQL/">MySQL</a></div><div class="post_share"><div class="social-share" data-image="/pics/scenery/27.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://jsdelivr.pai233.top/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://jsdelivr.pai233.top/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/11256.html" title="MySQL实战 基础篇（一）"><img class="cover" src="/pics/scenery/06.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-17</div><div class="title">MySQL实战 基础篇（一）</div></div></a></div><div><a href="/48002.html" title="MySQL实战 基础篇（二）"><img class="cover" src="/pics/scenery/22.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-26</div><div class="title">MySQL实战 基础篇（二）</div></div></a></div><div><a href="/47099.html" title="MySQL实战 基础篇（三）"><img class="cover" src="/pics/scenery/29.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-05-12</div><div class="title">MySQL实战 基础篇（三）</div></div></a></div><div><a href="/7044.html" title="MySQL实战 基础篇（五）"><img class="cover" src="/pics/scenery/28.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-07-02</div><div class="title">MySQL实战 基础篇（五）</div></div></a></div><div><a href="/55610.html" title="MySQL实战 基础篇（四）"><img class="cover" src="/pics/scenery/16.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-21</div><div class="title">MySQL实战 基础篇（四）</div></div></a></div><div><a href="/26960.html" title="MySQL常用"><img class="cover" src="/pics/scenery/29.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-08-17</div><div class="title">MySQL常用</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E7%B1%BB%E5%9E%8B"><span class="toc-number">1.</span> <span class="toc-text">索引类型</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%99%AE%E9%80%9A%E7%B4%A2%E5%BC%95%E5%92%8C%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95"><span class="toc-number">2.</span> <span class="toc-text">普通索引和唯一索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E8%BF%87%E7%A8%8B%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="toc-number">2.1.</span> <span class="toc-text">查询过程的区别</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9B%B4%E6%96%B0%E8%BF%87%E7%A8%8B%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="toc-number">2.2.</span> <span class="toc-text">更新过程的区别</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E9%80%89%E6%8B%A9%E5%92%8C%E4%BD%BF%E7%94%A8"><span class="toc-number">2.3.</span> <span class="toc-text">如何选择和使用</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BC%9A%E9%80%89%E6%8B%A9%E9%94%99%E8%AF%AF%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="toc-number">3.</span> <span class="toc-text">为什么会选择错误的索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E5%88%A4%E6%96%AD%E6%89%AB%E6%8F%8F%E8%A1%8C%E6%95%B0"><span class="toc-number">3.1.</span> <span class="toc-text">如何判断扫描行数</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E7%9A%84%E9%80%89%E6%8B%A9%E6%80%A7"><span class="toc-number">3.1.1.</span> <span class="toc-text">索引的选择性</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%9F%BA%E6%95%B0%EF%BC%88cardinality%EF%BC%89"><span class="toc-number">3.1.2.</span> <span class="toc-text">基数（cardinality）</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%80%89%E6%8B%A9%E5%BC%82%E5%B8%B8%E6%97%B6%E5%A6%82%E4%BD%95%E5%A4%84%E7%90%86"><span class="toc-number">3.2.</span> <span class="toc-text">选择异常时如何处理</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%BC%BA%E5%88%B6%E6%8C%87%E5%AE%9A"><span class="toc-number">3.2.1.</span> <span class="toc-text">强制指定</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%BC%95%E5%AF%BC%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%AD%A3%E7%A1%AE%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="toc-number">3.2.2.</span> <span class="toc-text">引导优化器使用正确的索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%96%B0%E5%BB%BA%E6%88%96%E8%80%85%E5%88%A0%E9%99%A4%E7%B4%A2%E5%BC%95"><span class="toc-number">3.2.3.</span> <span class="toc-text">新建或者删除索引</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E5%88%9B%E5%BB%BA%E9%AB%98%E6%80%A7%E8%83%BD%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="toc-number">4.</span> <span class="toc-text">如何创建高性能的索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E6%97%B6%E4%BD%BF%E7%94%A8%E7%8B%AC%E7%AB%8B%E5%88%97"><span class="toc-number">4.1.</span> <span class="toc-text">查询时使用独立列</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95"><span class="toc-number">4.2.</span> <span class="toc-text">前缀索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%BB%BA%E8%A1%A8%E8%AF%AD%E5%8F%A5"><span class="toc-number">5.</span> <span class="toc-text">建表语句</span></a></li></ol></div></div></div></div></main><footer id="footer" style="background-image: url('/pics/scenery/27.jpg')"><div id="footer-wrap"><div class="copyright">©2021 - 2022 By huy</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.js"></script><script src="/js/search/local-search.js"></script><div class="js-pjax"></div><script defer="defer" id="ribbon" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="false"></script><script id="click-heart" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script></div><!-- hexo injector body_end start --><script async src="//at.alicdn.com/t/font_2032782_8d5kxvn09md.js"></script><!-- hexo injector body_end end --></body></html>