--- title: "MySQL高性能索引策略" description: "索引类型从物理存储角度：聚集索引InnoDB 叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为Inno" date: 2020-04-10T16:00:00Z canonical: https://xiaobox.github.io/p/2020-04-10-mysql-gao-xing-neng-suo-yin-ce-l-e/ author: 小盒子 categories: ["数据库"] tags: ["MySQL", "数据结构"] source: https://mp.weixin.qq.com/s/g-C2FOTzPrV3fB8GSgo2Ug license: CC BY-NC-SA 4.0 license_url: https://creativecommons.org/licenses/by-nc-sa/4.0/ --- # MySQL高性能索引策略 ![Image](https://pub-f29bf2b53160470c9a85250116509a24.r2.dev/post/2020-04-10-mysql-gao-xing-neng-suo-yin-ce-l-e/001-ed1f8d6b.png) ## 索引类型 ### 从物理存储角度： ### 聚集索引 InnoDB 叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有） ### 非聚集索引 索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。 ### 从数据结构角度： ### B-Tree索引 B-Tree索引，实际上很多存储引擎使用的是B+Tree,即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。InnoDB使用的是B+Tree. B-Tree索引限制： 1 如果不是按照索引的最左列开始查找，则无法使用索引。 2 不能跳过索引中的列。 3 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。 ### 全文索引 全文索引更类似于搜索引擎做的事情 ，而不是简单的where条件匹配，在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE操作。 ### 哈希索引 基于哈希表实现，只有精确匹配所有列的查询才有效。 ### 空间数据索引（R-Tree） ### 从逻辑角度： ### 覆盖索引 如果一个索引包含所有需要查询的字段的值，那么我们就称之为“覆盖索引”，由于InnoDB的聚集索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。MySQL只能使用B-Tree索引做覆盖索引。如果在查询计划EXPLAIN中出现了“Using index”，就出现了覆盖索引。 ### 二级索引 - 表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。 - InnoDB的主键采用聚簇索引存储，使用的是B+Tree作为索引结构，但是叶子节点存储的是索引值和数据本身（**注意和MyISAM的不同**）。 - InnoDB的二级索引不使用聚蔟索引，叶子节点存储的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。 ![Image](002-315de618.png "image.png") ## 索引优点 - 索引大大减少了服务器需要扫描的数据量 - 索引可以帮助服务器避免排序和临时表 - 索引可以将随机IO变为顺序IO. - 如果表数量特别多，可以建立一个元数据信息表，用于记录哪个用户的信息存储在哪个表中。 ## 具体策略 ### 条件过滤 - 如果MySQL使用某个索引进行范围查询，也就无法再使用另一个进行排序了 - 尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。 - Extra列出现 了“Using where”,表示MySQL服务器将存储引擎返回行以后再应用where 过滤条件。 - 如果不能使用索引查找和锁定行的话，MySQL会做全表扫描。 ### 冗余和重复索引 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引 ### 索引排序 只有当索引的列顺序和ORDER BY 子名的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。 ### 多列索引 - 在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。 - 当服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。 ### 其他 - 独立的列：是指索引不能是表达式的一部分，或者函数的参数。 - 选择合适的索引列顺序。 - 前缀的“基数”应该接近于完整列的“基数” - MySQL不能在索引中执行LIKE操作，这是底层存储引擎API的限制，但能在索引中做最左前缀匹配的LIKE比较，但如果是通配符开关的LIKE查询，就无法比较。 ![Image](https://pub-f29bf2b53160470c9a85250116509a24.r2.dev/post/2020-04-10-mysql-gao-xing-neng-suo-yin-ce-l-e/003-42df053f.jpg) 关注公众号 获取更多精彩内容