[译] MYSQL索引最佳实践
xbdm3765
8年前
<p>今日整理文档时发现多年前的这个文档还是蛮实用的，然后在网络搜索了一下并没有相关的译文，所以决定把它翻译过来，如有不当的地方请多包涵和指正。以下是内容：</p> <h2><strong>你做了一个明智的选择</strong></h2> <ul> <li> <p>理解索引对开发和dba来说都是极其重要</p> </li> <li> <p>差劲的索引对产品问题负相当大的一部分责任</p> </li> <li> <p>索引不是多么高深的问题</p> </li> </ul> <h2><strong>MySQL 索引一览表</strong></h2> <ul> <li> <p>理解索引</p> </li> <li> <p>为你的应用创建最佳索引</p> </li> <li> <p>拥抱MySQL的限制</p> </li> </ul> <h2><strong>简述索引</strong></h2> <p>索引有什么用</p> <ul> <li> <p>为从数据库读取数据加速</p> </li> <li> <p>强制约束 (唯一索引 UNIQUE, 外键 FOREIGN KEY)</p> </li> <li> <p>没有任何索引的情况下查询页能正常运行</p> </li> <li> <p>但是那可能需要执行很长的时间</p> </li> </ul> <h2><strong>你可能听说过的索引类型</strong></h2> <ul> <li> <p>BTREE索引 – mysql中主要的索引类型</p> </li> <li> <p>RTREE索引 – 只有MyISAM支持, 用于GIS</p> </li> <li> <p>HASH 索引 – MEMORY, NDB 支持</p> </li> <li> <p>BITMAP 索引 – MySQL 不支持</p> </li> <li> <p>FULLTEXT 索引 – MyISAM, Innodb（MySQL 5.6以上支持）</p> </li> </ul> <h2><strong>类BTREE索引家族</strong></h2> <ul> <li> <p>有很多不同的实现</p> <ul> <li> <p>在可加速的操作中共享相同的属性</p> </li> <li> <p>内存相比硬盘使生活变得美好</p> </li> </ul> </li> <li> <p>B+树通常用于硬盘存储</p> <ul> <li> <p>数据存储于叶子节点</p> </li> </ul> </li> </ul> <h2><strong>B+Tree 示例</strong></h2> <p style="text-align:center"><img src="https://simg.open-open.com/show/5fef35b3402d213eed0d53f6d8dbc19a.png"></p> <h2><strong>MyISAM、Innodb索引对比</strong></h2> <ul> <li> <p>MyISAM</p> <ul> <li> <p>数据指针指向数据文件中的物理位置</p> </li> <li> <p>所有索引都是一样的（指向物理位置））</p> </li> </ul> </li> <li> <p>Innodb</p> <ul> <li> <p>主键索引 (显式或隐式) - 直接将数据存储于索引的叶子节点，而不是指针</p> </li> <li> <p>二级索引 – 保存主键索引的值作为数据指针</p> </li> </ul> </li> </ul> <h2><strong>BTREE索引能用于什么操作 ?</strong></h2> <ul> <li> <p>查询所有 KEY=5 的记录 (点查询)</p> </li> <li> <p>查询所有 KEY>5 的记录 (开合间)</p> </li> <li> <p>查询所有 5<KEY<10 的记录 (闭合间)</p> </li> <li> <p>不适用于：查询KEY最后一个数字等于0的所有记录</p> <p>– 因为这不能定义为范围查询操作</p> </li> </ul> <h2><strong>字符索引</strong></h2> <ul> <li> <p>这（和数值）没什么区别… 真的</p> <ul> <li> <p>collation是为字符串定义的排序规则</p> </li> <li> <p>如： “AAAA” < “AAAB”</p> </li> </ul> </li> <li> <p>前缀LIKE 查询是一种特殊的范围查询</p> <ul> <li> <p>LIKE “ABC%” 的意思是：</p> </li> <li> <p>“ABC[最小值]”<KEY<“ABC[最大值]”</p> <p>– LIKE “%ABC” 无法使用索引查询</p> </li> </ul> </li> </ul> <h2><strong>联合索引</strong></h2> <ul> <li> <p>是这样进行排序的， 比较首列，然后第二列，第三列以此类推，如：</p> <ul> <li> <p>KEY(col1,col2,col3)</p> </li> <li> <p>(1,2,3) < (1,3,1)</p> </li> </ul> </li> <li> <p>使用一个BTREE索引，而不是每个层级一个单独的BTREE索引</p> </li> </ul> <h2><strong>索引的开销</strong></h2> <ul> <li> <p>索引是昂贵的，不要添加多余的索引</p> <p>– 多数情况下，扩展索引比添加一个新的索引要好</p> </li> <li> <p>写 - 更新索引常常是数据库写操作的主要开销</p> </li> <li> <p>读 - 需要再硬盘和内存开销空间; 查询优化中需要额外的开销</p> </li> </ul> <h2><strong>索引成本的影响</strong></h2> <ul> <li> <p>长主键索引（Innodb）</p> <p>– 使所有相应的二级索引 变得更长、更慢</p> </li> <li> <p>“随机”主键索引（Innodb）</p> <p>– 插入导致大量的页面分割</p> </li> <li> <p>越长的索引通常越慢</p> </li> <li> <p>Index with insertion in random order</p> <p>– SHA1(‘password’)</p> </li> <li> <p>低区分度的索引是低劣的</p> <p>– 在性别字段建的索引</p> </li> <li> <p>相关索引是不太昂贵的</p> <p>– insert_time与自增id是相关的</p> </li> </ul> <h2><strong>Innodb表的索引</strong></h2> <ul> <li> <p>数据按主键聚集</p> <ul> <li> <p>选择最佳的字段作为主键</p> </li> <li> <p>比如评论表 – (POST_ID,COMMENT_ID) 是作为主键的不错选择，使得单个post的评论聚在一起</p> </li> </ul> </li> <li> <p>或者 “打包” 单个 BIGINT（字段）</p> </li> <li> <p>主键隐式地附加到所有索引中</p> <ul> <li> <p>KEY (A) 实质上是 KEY (A,ID)</p> </li> </ul> </li> <li> <p>覆盖索引，有利于排序</p> </li> </ul> <h2><strong>MySQL是如何使用索引的</strong></h2> <ul> <li> <p>查询</p> </li> <li> <p>排序</p> </li> <li> <p>避免读取数据（只读取索引）</p> </li> <li> <p>其他专门的优化</p> </li> </ul> <h2><strong>使用索引进行查询</strong></h2> <ul> <li> <p>SELECT * FROM EMPLOYEES WHERE</p> <p>LAST_NAME=“Smith”</p> <ul> <li> <p>这是典型的索引 KEY(LAST_NAME)</p> </li> </ul> </li> <li> <p>可以使用复合索引</p> <ul> <li> <p>SELECT * FROM EMPLOYEES WHERE</p> <p>LAST_NAME=“Smith” AND DEPT=“Accounting”</p> </li> <li> <p>将会使用索引 KEY(DEPT,LAST_NAME)</p> </li> </ul> </li> </ul> <h2><strong>复合索引比较复杂</strong></h2> <ul> <li> <p>Index (A,B,C) - 字段顺序问题</p> </li> <li> <p>下列情形将会使用索引进行查询（全条件）</p> <ul> <li> <p>A>5</p> </li> <li> <p>A=5 AND B>6</p> </li> <li> <p>A=5 AND B=6 AND C=7</p> </li> <li> <p>A=5 AND B IN (2,3) AND C>5</p> </li> </ul> </li> <li> <p>下列条件将不会使用索引</p> <ul> <li> <p>B>5 – 条件没有B字段前的A</p> </li> <li> <p>B=6 AND C=7 - 条件没有B、C字段前的A</p> </li> </ul> </li> <li> <p>以下情形使用索引的一部分</p> <ul> <li> <p>A>5 AND B=2 - 第一个字段A的范围查询，导致只用上了索引中A字段的部分</p> </li> <li> <p>A=5 AND B>6 AND C=2 - B字段的范围范围查询，导致只使用了索引中A和B两个字段的部分</p> </li> </ul> </li> </ul> <h2><strong>MySQL优化器的第一法则</strong></h2> <ul> <li> <p>在复合索引中，MySQL在遇到返回查询(<,>,</p> <p>BETWEEN)时，将停止中止剩余部分（索引）的使用；但是使用IN(…)的"范围查询"则可以继续往右使用索引（的更多部分）</p> </li> </ul> <h2><strong>所用索引进行排序</strong></h2> <ul> <li> <p>SELECT * FROM PLAYERS ORDER BY SCORE</p> <p>DESC LIMIT 10</p> <ul> <li> <p>将使用索引 KEY(SCORE)</p> </li> <li> <p>不使用索引将进行非常昂贵的“filesort”操作(external</p> <p>sort)</p> </li> </ul> </li> <li> <p>常常使用组合索引进行查询</p> <ul> <li> <p>SELECT * FROM PLAYERS WHERE COUNTRY=“US”</p> <p>ORDER BY SCORE DESC LIMIT 10</p> </li> <li> <p>最佳选择是 KEY(COUNTRY,SCORE)</p> </li> </ul> </li> </ul> <h2><strong>高效排序的联合索引</strong></h2> <ul> <li> <p>变得更加受限!</p> </li> <li> <p>KEY(A,B)</p> </li> <li> <p>以下情形将会使用索引进行排序</p> <ul> <li> <p>ORDER BY A - 对索引首字段进行排序</p> </li> <li> <p>A=5 ORDER BY B - 对第一个字段进行点查询，对第二个字段进行排序</p> </li> <li> <p>ORDER BY A DESC, B DESC - 对两个字段进行相同的顺序进行排序</p> </li> <li> <p>A>5 ORDER BY A - 对首字段进行范围查询，并对首字段进行排序</p> </li> </ul> </li> <li> <p>以下情形将不使用索引进行排序</p> <ul> <li> <p>ORDER BY B - 对第二个字段进行排序（未使用首字段）</p> </li> <li> <p>A>5 ORDER BY B – 对首字段进行范围查询，对第二个字段进行排序</p> </li> <li> <p>A IN(1,2) ORDER BY B - 对首字段进行IN查询，对第二个字段进行排序</p> </li> <li> <p>ORDER BY A ASC, B DESC - 对两个字段进行不同顺序的排序</p> </li> </ul> </li> </ul> <h2><strong>MySQL使用索引排序的规则</strong></h2> <ul> <li> <p>不能对两个字段进行不同顺序的排序</p> </li> <li> <p>对非ORDER BY部分的字段只能使用点查询（=）</p> <p>– 在这种情形下，IN()也不行</p> </li> </ul> <h2><strong>避免读取数据（只读取索引）</strong></h2> <ul> <li> <p>“覆盖索引”</p> <p>– 这里指 适用于特定查询的索引，而不是一种索引的类型</p> </li> <li> <p>只读取索引，而不去读取数据</p> </li> <li> <p>SELECT STATUS FROM ORDERS WHERE</p> <p>CUSTOMER_ID=123</p> </li> </ul> <p>– KEY(CUSTOMER_ID,STATUS)</p> <ul> <li> <p>索引通常比数据本身要小</p> </li> <li> <p>（索引）读取起来更有次序</p> <p>– 读取数据指针通常是随机的</p> </li> </ul> <h2><strong>Min/Max的优化</strong></h2> <ul> <li> <p>索引可以帮助优化 MIN()/MAX() 这类的统计函数</p> <p>– 但只包含以下这些：</p> </li> <li> <p>SELECT MAX(ID) FROM TBL;</p> </li> <li> <p>SELECT MAX(SALARY) FROM EMPLOYEE</p> <p>GROUP BY DEPT_ID</p> <ul> <li> <p>将受益于 KEY(DEPT_ID,SALARY)</p> </li> <li> <p>“Using index for group-by”</p> </li> </ul> </li> </ul> <h2><strong>联表查询中索引的使用</strong></h2> <ul> <li> <p>MySQL 使用 “嵌套循环（Nested Loops）”进行联表查询</p> <ul> <li> <p>SELECT * FROM POSTS,COMMENTS WHERE</p> <p>AUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID</p> </li> <li> <p>扫描表POSTS查询所有复合条件的 posts</p> </li> <li> <p>循环posts 在表COMMENTS 中查找 每个post的所有comments</p> </li> </ul> </li> <li> <p>使每个关联的表（关联字段）都使用上索引显得非常的重要</p> </li> <li> <p>索引只有在被查询的字段上是必要的</p> <p>– POSTS.ID字段的索引再本次查询中是用不上的</p> </li> <li> <p>重新设计不能很好的所有索引的联合查询吧</p> </li> </ul> <h2><strong>使用多索引</strong></h2> <ul> <li> <p>MySQL可以使用超过1个索引</p> <ul> <li> <p>“索引合并”</p> </li> </ul> </li> <li> <p>SELECT * FROM TBL WHERE A=5 AND B=6</p> <p>– 可以分别使用索引 KEY(A)和 KEY(B)</p> <ul> <li> <p>索引 KEY(A,B) 是更好的选择</p> </li> </ul> </li> <li> <p>SELECT * FROM TBL WHERE A=5 OR B=6</p> <p>– 两个索引同时分别被使用</p> <ul> <li> <p>索引 KEY(A,B) 在这个查询中无法使用</p> </li> </ul> </li> </ul> <h2><strong>前缀索引</strong></h2> <ul> <li> <p>你可以在字段最左前缀建立索引</p> <ul> <li> <p>ALTER TABLE TITLE ADD KEY(TITLE(20));</p> </li> <li> <p>需要对BLOB/TEXT类型的字段建立索引</p> </li> <li> <p>能显著的减少空间使用</p> </li> <li> <p>不能用于覆盖索引</p> </li> <li> <p>选择前缀长度成为一个问题</p> </li> </ul> </li> </ul> <h2><strong>选择前缀长度</strong></h2> <ul> <li> <p>前缀应该有足够的区分度</p> <ul> <li> <p>比较distinct前缀、distinct整个字段的值</p> </li> </ul> </li> </ul> <p>mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title,20))) p20 from title;</p> <table> <thead> <tr> <th>total</th> <th>p10</th> <th>p20</th> </tr> </thead> <tbody> <tr> <td>998335</td> <td>624949</td> <td>960894</td> </tr> </tbody> </table> <p>1 row in set (44.19 sec)</p> <ul> <li> <p>检查异常值</p> <ul> <li> <p>确保不会有很多记录使用相同的前缀</p> </li> </ul> </li> </ul> <p>使用最多的Title</p> <p>mysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;</p> <table> <thead> <tr> <th>cnt</th> <th>tl</th> </tr> </thead> <tbody> <tr> <td>136</td> <td>The Wedding</td> </tr> <tr> <td>129</td> <td>Lost and Found</td> </tr> <tr> <td>112</td> <td>Horror Marathon</td> </tr> </tbody> </table> <p>3 rows in set (27.49 sec)</p> <p>使用最多的Title 前缀</p> <p>mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;</p> <table> <thead> <tr> <th>cnt</th> <th>tl</th> </tr> </thead> <tbody> <tr> <td>184</td> <td>Wetten, dass..? aus</td> </tr> <tr> <td>136</td> <td>The Wedding</td> </tr> <tr> <td>129</td> <td>Lost and Found</td> </tr> </tbody> </table> <p>3 rows in set (33.23 sec)</p> <h2><strong>MySQL如何选择使用哪个索引的?</strong></h2> <ul> <li> <p>每次查询动态选择</p> <p>– 查询文本中常量很重要</p> </li> <li> <p>评估需要查询的行数</p> <p>对给定的索引，在表中进行"dive"</p> </li> <li> <p>如果（dive）不可行时，使用 “Cardinality” 进行统计</p> <p>– 这是进行 ANALYZE TABLE时 更新的</p> </li> </ul> <h2><strong>更多关于索引的选择</strong></h2> <ul> <li> <p>并不只是最小化扫描行数</p> </li> <li> <p>很多其他的heuristics（尝试） and hacks</p> <p>– 对Innodb来说主键是很重要的</p> <ul> <li> <p>覆盖索引效益</p> <p>-Full table scan is faster, all being equal（这句不是太明白）</p> </li> <li> <p>我们也可以使用索引进行排序</p> </li> </ul> </li> <li> <p>须知</p> <ul> <li> <p>验证MYSQL实际使用的执行计划</p> </li> <li> <p>注意是可以根据常量和数据动态改变的</p> </li> </ul> </li> </ul> <h2><strong>使用EXPLAIN</strong></h2> <ul> <li> <p>EXPLAIN 是一个很好的工具，可以看到MYSQL将如何进行查询</p> <ul> <li> <p>记住，真实的查询可能跟执行计划不同</p> </li> </ul> <p>mysql> explain select max(season_nr) from title group by production_year;</p> </li> </ul> <table> <thead> <tr> <th>id</th> <th>select_type</th> <th>table</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>Extra</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>SIMPLE</td> <td>title</td> <td>range</td> <td>NULL</td> <td>production_year</td> <td>5</td> <td>NULL</td> <td>201</td> <td>Using index for group-by</td> </tr> </tbody> </table> <p>1 row in set (0.01 sec)</p> <h2><strong>MySQL Explain 101</strong></h2> <ul> <li> <p>“type” 从好到差排序如下：</p> <p>– system,const,eq_ref,ref,range,index,ALL</p> </li> <li> <p>注意 “rows” – 更大的数值意味着更慢的查询</p> </li> <li> <p>检查 “key_len” – 显示索引的哪些部分真实使用到了</p> </li> <li> <p>留意"Extra"</p> <ul> <li> <p>Using Index - 好</p> </li> <li> <p>Using Filesort, Using Temporary - 差</p> </li> </ul> </li> </ul> <h2><strong>索引策略</strong></h2> <ul> <li> <p>为你的关键性能查询集建立索引</p> <p>– 整体取审视他们，而不是一个个看</p> </li> <li> <p>最好所有的查询条件和联表条件都使用索引</p> <p>– 起码区分度最高的部分是</p> </li> <li> <p>一般来说，可以的话，扩展索引，而不是创建新的索引</p> </li> <li> <p>修改时记得验证对性能的影响</p> </li> </ul> <h2><strong>索引策略示例</strong></h2> <ul> <li> <p>按能支持更多查询的顺序建立索引</p> <ul> <li> <p>SELECT * FROM TBL WHERE A=5 AND B=6</p> </li> <li> <p>SELECT * FROM TBL WHERE A>5 AND B=6</p> <p>– 对两个查询来说 KEY(B,A) 是更好的选择</p> </li> </ul> </li> <li> <p>把所有都是点查询的字段放到索引的首位</p> </li> <li> <p>不要添加非性能关键查询的索引</p> <p>– 太多的索引会使MYSQL慢下来</p> </li> </ul> <h2><strong>Trick #1: 枚举范围</strong></h2> <ul> <li> <p>KEY (A,B)</p> </li> <li> <p>SELECT * FROM TBL WHERE A BETWEEN 2</p> <p>AND 4 AND B=5</p> <ul> <li> <p>将只使用索引的第一个字段部分</p> </li> </ul> </li> <li> <p>SELECT * FROM TBL WHERE A IN (2,3,4) AND</p> <p>B=5</p> <ul> <li> <p>索引的两个字段部分都使用</p> </li> </ul> </li> </ul> <h2><strong>Trick #2: 添加一个假的条件</strong></h2> <ul> <li> <p>KEY (GENDER,CITY)</p> </li> <li> <p>SELECT * FROM PEOPLE WHERE CITY=“NEW</p> <p>YORK”</p> <ul> <li> <p>完全用不上索引</p> </li> </ul> </li> <li> <p>SELECT * FROM PEOPLE WHERE GENDER IN</p> <p>(“M”,”F”) AND CITY=“NEW YORK”</p> <ul> <li> <p>将用上索引</p> </li> <li> <p>这个Trick在低区别度的字段上可以很好的使用</p> </li> <li> <p>Gender, Status, Boolean Types etc</p> </li> </ul> </li> </ul> <h2><strong>Trick #3: 虚实Filesort</strong></h2> <ul> <li> <p>KEY(A,B)</p> </li> <li> <p>SELECT * FROM TBL WHERE A IN (1,2) ORDER BY</p> <p>B LIMIT 5;</p> <ul> <li> <p>无法使用索引进行排序</p> </li> </ul> </li> <li> <p>(SELECT <em>FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT</em> FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;</p> <ul> <li> <p>将会用上索引，而“filesort”只用于对不超过10行记录</p> </li> </ul> </li> </ul> <p> </p> <p> </p> <p>来自：https://segmentfault.com/a/1190000007494097</p> <p> </p>