• [译] MYSQL索引最佳实践

    近日整理文档时发现多年前的这个文档还是蛮实用的，然后在网络搜索了一下并没有相关的译文，所以决定把它翻译过来，如有不当的地方请多包涵和指正。原文地址：https://www.percona.com/files...

    以下是译文：

    你做了一个明智的选择

    • 理解索引对开发和dba来说都是极其重要

    • 差劲的索引对产品问题负相当大的一部分责任

    • 索引不是多么高深的问题

    MySQL 索引一览表

    • 理解索引

    • 为你的应用创建最佳索引

    • 拥抱MySQL的限制

    简述索引

    索引有什么用

    • 为从数据库读取数据加速

    • 强制约束 (唯一索引 UNIQUE, 外键 FOREIGN KEY)

    • 没有任何索引的情况下查询页能正常运行

    • 但是那可能需要执行很长的时间

    你可能听说过的索引类型

    • BTREE索引 – mysql中主要的索引类型

    • RTREE索引 – 只有MyISAM支持, 用于GIS

    • HASH 索引 – MEMORY, NDB 支持

    • BITMAP 索引 – MySQL 不支持

    • FULLTEXT 索引 – MyISAM, Innodb（MySQL 5.6以上支持）

    类BTREE索引家族

    • 有很多不同的实现

      • 在可加速的操作中共享相同的属性

      • 内存相比硬盘使生活变得美好

    • B+树通常用于硬盘存储

      • 数据存储于叶子节点

    B+Tree 示例

    MyISAM、Innodb索引对比

    • MyISAM

      • 数据指针指向数据文件中的物理位置

      • 所有索引都是一样的（指向物理位置））

    • Innodb

      • 主键索引 (显式或隐式) - 直接将数据存储于索引的叶子节点，而不是指针

      • 二级索引 – 保存主键索引的值作为数据指针

    BTREE索引能用于什么操作 ?

    • 查询所有 KEY=5 的记录 (点查询)

    • 查询所有 KEY>5 的记录 (开合间)

    • 查询所有 5<KEY<10 的记录 (闭合间)

    • 不适用于：查询KEY最后一个数字等于0的所有记录

      • 因为这不能定义为范围查询操作

    字符索引

    • 这（和数值）没什么区别… 真的

      • collation是为字符串定义的排序规则

      • 如： “AAAA” < “AAAB”

    • 前缀LIKE 查询是一种特殊的范围查询

      • LIKE “ABC%” 的意思是：

      • “ABC[最小值]”<KEY<“ABC[最大值]”

      • LIKE “%ABC” 无法使用索引查询

    联合索引

    • 是这样进行排序的， 比较首列，然后第二列，第三列以此类推，如：

      • KEY(col1,col2,col3)

      • (1,2,3) < (1,3,1)

    • 使用一个BTREE索引，而不是每个层级一个单独的BTREE索引

    索引的开销

    • 索引是昂贵的，不要添加多余的索引

      • 多数情况下，扩展索引比添加一个新的索引要好

    • 写 - 更新索引常常是数据库写操作的主要开销

    • 读 - 需要再硬盘和内存开销空间; 查询优化中需要额外的开销

    索引成本的影响

    • 长主键索引（Innodb） – 使所有相应的二级索引 变得更长、更慢

    • “随机”主键索引（Innodb） – 插入导致大量的页面分割

    • 越长的索引通常越慢

    • Index with insertion in random order – SHA1(‘password’)

    • 低区分度的索引是低劣的 – 在性别字段建的索引

    • 相关索引是不太昂贵的– insert_time与自增id是相关的

    Innodb表的索引

    • 数据按主键聚集

      • 选择最佳的字段作为主键

      • 比如评论表 – (POST_ID,COMMENT_ID) 是作为主键的不错选择，使得单个post的评论聚在一起

    • 或者 “打包” 单个 BIGINT（字段）

    • 主键隐式地附加到所有索引中

      • KEY (A) 实质上是 KEY (A,ID)

    • 覆盖索引，有利于排序

    MySQL是如何使用索引的

    • 查询

    • 排序

    • 避免读取数据（只读取索引）

    • 其他专门的优化

    使用索引进行查询

    • SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith”

      • 这是典型的索引 KEY(LAST_NAME)

    • 可以使用复合索引

      • SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith” AND DEPT=“Accounting”

      • 将会使用索引 KEY(DEPT,LAST_NAME)

    复合索引比较复杂

    • Index (A,B,C) - 字段顺序问题

    • 下列情形将会使用索引进行查询（全条件）

      • A>5

      • A=5 AND B>6

      • A=5 AND B=6 AND C=7

      • A=5 AND B IN (2,3) AND C>5

    • 下列条件将不会使用索引

      • B>5 – 条件没有B字段前的A

      • B=6 AND C=7 - 条件没有B、C字段前的A

    • 以下情形使用索引的一部分

      • A>5 AND B=2 - 第一个字段A的范围查询，导致只用上了索引中A字段的部分

      • A=5 AND B>6 AND C=2 - B字段的范围范围查询，导致只使用了索引中A和B两个字段的部分

    MySQL优化器的第一法则

    • 在复合索引中，MySQL在遇到返回查询(<,>,BETWEEN)时，将停止中止剩余部分（索引）的使用；但是使用IN(…)的"范围查询"则可以继续往右使用索引（的更多部分）

    所用索引进行排序

    • SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10

      • 将使用索引 KEY(SCORE)

      • 不使用索引将进行非常昂贵的“filesort”操作(externalsort)

    • 常常使用组合索引进行查询

      • SELECT * FROM PLAYERS WHERE COUNTRY=“US”ORDER BY SCORE DESC LIMIT 10

      • 最佳选择是 KEY(COUNTRY,SCORE)

    高效排序的联合索引

    • 变得更加受限!

    • KEY(A,B)

    • 以下情形将会使用索引进行排序

      • ORDER BY A - 对索引首字段进行排序

      • A=5 ORDER BY B - 对第一个字段进行点查询，对第二个字段进行排序

      • ORDER BY A DESC, B DESC - 对两个字段进行相同的顺序进行排序

      • A>5 ORDER BY A - 对首字段进行范围查询，并对首字段进行排序

    • 以下情形将不使用索引进行排序

      • ORDER BY B - 对第二个字段进行排序（未使用首字段）

      • A>5 ORDER BY B – 对首字段进行范围查询，对第二个字段进行排序

      • A IN(1,2) ORDER BY B - 对首字段进行IN查询，对第二个字段进行排序

      • ORDER BY A ASC, B DESC - 对两个字段进行不同顺序的排序

    MySQL使用索引排序的规则

    • 不能对两个字段进行不同顺序的排序

    • 对非ORDER BY部分的字段只能使用点查询（=）– 在这种情形下，IN()也不行

    避免读取数据（只读取索引）

    • “覆盖索引”– 这里指 适用于特定查询的索引，而不是一种索引的类型

    • 只读取索引，而不去读取数据

      • SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123

      • KEY(CUSTOMER_ID,STATUS)

    • 索引通常比数据本身要小

    • （索引）读取起来更有次序– 读取数据指针通常是随机的

    Min/Max的优化

    • 索引可以帮助优化 MIN()/MAX() 这类的统计函数– 但只包含以下这些：

    • SELECT MAX(ID) FROM TBL;

    • SELECT MAX(SALARY) FROM EMPLOYEEGROUP BY DEPT_ID

      • 将受益于 KEY(DEPT_ID,SALARY)

      • “Using index for group-by”

    联表查询中索引的使用

    • MySQL 使用 “嵌套循环（Nested Loops）”进行联表查询

      • SELECT * FROM POSTS,COMMENTS WHEREAUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID

      • 扫描表POSTS查询所有复合条件的 posts

      • 循环posts 在表COMMENTS 中查找 每个post的所有comments

    • 使每个关联的表（关联字段）都使用上索引显得非常的重要

    • 索引只有在被查询的字段上是必要的– POSTS.ID字段的索引再本次查询中是用不上的

    • 重新设计不能很好的所有索引的联合查询吧

    使用多索引

    • MySQL可以使用超过1个索引

      • “索引合并”

    • SELECT * FROM TBL WHERE A=5 AND B=6– 可以分别使用索引 KEY(A)和 KEY(B)

      • 索引 KEY(A,B) 是更好的选择

    • SELECT * FROM TBL WHERE A=5 OR B=6– 两个索引同时分别被使用

      • 索引 KEY(A,B) 在这个查询中无法使用

    前缀索引

    • 你可以在字段最左前缀建立索引

      • ALTER TABLE TITLE ADD KEY(TITLE(20));

      • 需要对BLOB/TEXT类型的字段建立索引

      • 能显著的减少空间使用

      • 不能用于覆盖索引

      • 选择前缀长度成为一个问题

    选择前缀长度

    • 前缀应该有足够的区分度

      • 比较distinct前缀、distinct整个字段的值

    mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title,20))) p20 from title;

    totalp10p20
    998335 624949 960894

    1 row in set (44.19 sec)

    • 检查异常值

      • 确保不会有很多记录使用相同的前缀

    使用最多的Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;

    cnttl
    136 The Wedding
    129 Lost and Found
    112 Horror Marathon

    3 rows in set (27.49 sec)

    使用最多的Title 前缀 mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;

    cnttl
    184 Wetten, dass..? aus
    136 The Wedding
    129 Lost and Found

    3 rows in set (33.23 sec)

    MySQL如何选择使用哪个索引的?

    • 每次查询动态选择– 查询文本中常量很重要

    • 评估需要查询的行数 对给定的索引，在表中进行"dive"

    • 如果（dive）不可行时，使用 “Cardinality” 进行统计– 这是进行 ANALYZE TABLE时 更新的

    更多关于索引的选择

    • 并不只是最小化扫描行数

    • 很多其他的heuristics（尝试） and hacks– 对Innodb来说主键是很重要的

      • 覆盖索引效益

      • Full table scan is faster, all being equal（这句不是太明白）

      • 我们也可以使用索引进行排序

    • 须知

      • 验证MYSQL实际使用的执行计划

      • 注意是可以根据常量和数据动态改变的

    使用EXPLAIN

    • EXPLAIN 是一个很好的工具，可以看到MYSQL将如何进行查询

      mysql> explain select max(season_nr) from title group by production_year;

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1 SIMPLE title range NULL production_year 5 NULL 201 Using index for group-by

    1 row in set (0.01 sec)

    MySQL Explain 101

    • “type” 从好到差排序如下：– system,const,eq_ref,ref,range,index,ALL

    • 注意 “rows” – 更大的数值意味着更慢的查询

    • 检查 “key_len” – 显示索引的哪些部分真实使用到了

    • 留意"Extra"

      • Using Index - 好

      • Using Filesort, Using Temporary - 差

    索引策略

    • 为你的关键性能查询集建立索引– 整体取审视他们，而不是一个个看

    • 最好所有的查询条件和联表条件都使用索引– 起码区分度最高的部分是

    • 一般来说，可以的话，扩展索引，而不是创建新的索引

    • 修改时记得验证对性能的影响

    索引策略示例

    • 按能支持更多查询的顺序建立索引

      • SELECT * FROM TBL WHERE A=5 AND B=6

      • SELECT * FROM TBL WHERE A>5 AND B=6– 对两个查询来说 KEY(B,A) 是更好的选择

    • 把所有都是点查询的字段放到索引的首位

    • 不要添加非性能关键查询的索引– 太多的索引会使MYSQL慢下来

    Trick #1: 枚举范围

    • KEY (A,B)

    • SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5

      • 将只使用索引的第一个字段部分

    • SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5

      • 索引的两个字段部分都使用

    Trick #2: 添加一个假的条件

    • KEY (GENDER,CITY)

    • SELECT * FROM PEOPLE WHERE CITY=“NEWYORK”

      • 完全用不上索引

    • SELECT * FROM PEOPLE WHERE GENDER IN(“M”,”F”) AND CITY=“NEW YORK”

      • 将用上索引

      • 这个Trick在低区别度的字段上可以很好的使用

      • Gender, Status, Boolean Types etc

    Trick #3: 虚实Filesort

    • KEY(A,B)

    • SELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;

      • 无法使用索引进行排序

    • (SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;

      • 将会用上索引，而“filesort”只用于对不超过10行记录

    作者的ppt发出来后，很多人向他咨询相关问题，另外专门做了回复，oschina已经有对回复进行了翻译：
    http://www.oschina.net/transl...

     
     
  • 相关阅读:
    Yupoo! 的网站技术架构(转)
    用DELPHI编写NT服务时，如何指定依存关系？
    soapUI快速入门
    Delphi2007开发WebService调用COM+无响应现象分析
    折半查找法的C++原型
    soapUI快速入门
    Delphi2007开发WebService调用COM+无响应现象分析
    SQL Server 性能调优
    SQL Server 性能调优
    用DELPHI编写NT服务时，如何指定依存关系？
  • 原文地址：https://www.cnblogs.com/webenh/p/6097028.html
Copyright © 2020-2023  润新知