索引原理介绍及索引优化

VV酱
573 阅读8分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第2天，点击查看活动详情

索引原理介绍及索引优化

索引优化--Explain执行计划返回值🐴

image-20220601231747772.png

使用Explain分析select语句执行部分字段简单说明；

  • id

​ id就是select时的编号，执行计划中有几条select就有几个id,按照select的查询顺序排列，id越大查询的优先级则越高

  • select_type

​ 查询的类型

1. SIMPLE ： 表示查询语句不包含子查询或union
2. primary：查询中最外层的 select
3. SUBQUERY：SELECT子查询语句
4. UNION：表示此查询是UNION的第二个或后续的查询

  • type :是存储引擎重要的一个字段，表示sql执行是否走索引，或者效率等级；

    • ALL:表示全表扫描，性能最差；
    • index: 基于索引的全表扫描，一般是直接扫描所有的叶子节点，
    • range:表示使用索引范围查询。使用>、>=、<、<=、in等
    • ref：表示使用非唯一索引或唯一索引的部分前缀进行单值查询
    • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果
    • const：表示使用主键或唯一索引做等值查询，常量查询。
    • NULL：表示不用访问表，速度最快。

    索引效率自上而下越来高，一般来说需要把sql优化到range级别，最好到ref;

  • possible_keys

    • 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
    • 可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询；

  • key

    • 显示mysql实际采用哪个索引来优化对该表的访问，显示索引名称
    • 如果没有使用索引，则该列是 NULL。
    • 使用 force index可以强制mysql使用索引、同样适用possible_keys也可以忽略列中的索引

  • key_len

    • mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

  • rows

    这一列是mysql估计要扫描的行数，不是结果集里的行数。

  • extra

    • Extra表示很多额外的信息，各种操作会在Extra提示相关信息；

    • Using where 表示查询的列未被索引覆盖 ，需要通过索引回表查询数据

    • Using index 表示查询需要通过索引实现了自动排序，索引就可以满足所需数据，使用了覆盖索引。

    • Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化；

      ​ filesort会先把结果查出来然后再在磁盘或者缓存中排序，效率相对低；

    • Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作

索引优化--优化事项:man_in_tuxedo:

1、覆盖索引：什么是覆盖索引？

​ 一条查询语句使用到联合索引，查询语句的结果集，恰好都能在联合索引中全部覆盖，这种叫做覆盖索引；使用到覆盖索引不会产生回表操作，一定程度上提升查询效率；

​ 所谓回表操作是指在非聚簇索引或者辅助索引的叶子节点存储的不是所有的行数据，而是当前行的主键和索引字段，因此当查询结果存在非索引字段的字段时，会拿着主键去聚簇索引做一次回表查询，会扫描两遍索引树，性能相对低一些；

​ 因此，一般情况下建议使用联合索引的覆盖索引机制，不建议添加多个单值索引，

2、最左前缀原则

​ 在使用联合索引时应遵循以左为先的左前缀原则，意思就是在where语句中标记了索引的字段查询要跟创建索引的顺序一致，否则会导致索引失效；查询时会一直➡向右匹配，直至遇到范围查找（> < between等）才停止；

3、like查询

​ like查询是可以走索引的

​ 在like字句中，%代表任意匹配，模糊查询百分号写在查询关键字之前则会相当于范围查找，破坏了索引的顺序性，因此这种情况不会走索引，所以在模糊查询时，查询关键字之前不能写%，只能写在后面；

4、关于or和in

​ 关于or和in的使用并没有可以拿捏的规则，一般情况下mysql数据库内部优化器会根据表的大小，数据量级等因素来评判会不会走索引，当数据量很小有可能就不走索引，反之就可能会走索引查询，所以建议非必要情况下，尽量少用；

5、在装有索引的字段上做函数操作，会导致索引失效

6、对于长字段需要加索引的情况，满足需求的前提下使用字段的部分前缀作为索引index(field(10))，，毕竟所以能也是需要占据空间的，也要考虑维护成本

👆【关于索引下推优化】

​ 索引下推可以有效提高查询的检索效率，减少查询的回表次数；

例如新建索引（a,b,c），select* from table where a like 'aa%' and b=bb and c=cc；

​ 按照最前缀原则，先按照a进行查询，正常情况下，上述sql 只有a会走索引，a索引完之后后面是全字段模糊匹配，b和c都不会再走索引查询；因为a之后，相关与范围查找，违背的了索引的顺序性；这种情况下，一般的查询逻辑是匹配到索引中所有aa为前缀的索引，然后回表逐个查询，将查询的结果在跟where后面你的条件也就是b=bb and c=cc做过滤，最终得到查询的结果集；

​ mysql5.6之前是这么操作的，之后的版本，引入了索引下推，让服务层查询完之后再次过滤的操作下推到引擎层索引查询中就把相关的字段先进行过滤，显然上述示例的 b和c都在索引中，完全可以在索引中先对相关的数据进行过滤，然后在把得到的结果回表查询结果，这样能很大程度上降低了回表次数，提升的查询效率；

👆【关于orderby】

​ 当查询的结果需要使用orderby排序时，如果排序字段没有使用索引，则会查询出所有数据进行外部排序，所谓外部排序是指把需要排序的字段分批从磁盘读到内存，在内存进行排序，非常消耗性能；

​ 因此orderby字段添加适当索引，索引本身是有序的，读取的时候就已经是排好序的了；不需要额外的性能消耗；

​ 当where条件和order by出现索引使用的冲突时， 一般这种时候是保证where条件使用索引来快速筛选出来数据，接着再进行排序。 因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本；

👆【分页优化】

​ 分页查询使用简单的 limit 子句就可以实现

​ 比如：SELECT * FROM 表名 LIMIT [offset,] limit

​ 这种sql查询是查询出limit条结果，从offset开始；貌似是只差了limit条数据，实际上是先查了offset条数据，然后丢弃得到offet的值，往后再读取limit条，再数据量小的情况下，影响不大，但是当数据量大查询后面几页数据的时候，就出现查询耗时很慢的情况，效率非常低；

​ 1、如果数据库不存在删除操作，可以根据自增且连续的主键排序的分页查询；这种情况实用性不大实际应用场景很少；

​ 2、使用覆盖索引优化

​ 3、利用子查询即正常查询出条件的id值，在回表根据id主键查找具体字段；

索引原理介绍🐵
  • 索引是按照一定规则排好序的用于存储引擎快查找数据的一种数据结构，帮助快速查询和更新数据；
  • 一般Mysql的索引结构有B+tree、Hash，大多数情况下我们都是使用InnoDB 存储引擎的默认索引---Btree
索引分类

  1. 应用层

    • 普通索引

    ALTER TABLE 表名 ADD INDEX [索引名称] (field_name 字段名)

    • 唯一索引

    • 主键索引:不允许空值，唯一

    • 联合索引

    • 全文索引

    必须建立在字符串和文本的数据字段之上，当数据量比较大的时候 使用like模糊查询的效率不高，此时可以使用全文索引的方式查找数据；MySQL 5.6之后MyISAM和InnoDB存储引擎均支持此索引。 具体使用示例：select * from blogwhere match(article) against('vv'); '))

  2. 数据存储

    • 聚簇索引

      1. 简单来说聚簇索引和非聚簇索引的区分就是在Btree的叶子节点存储的当前索引值的主键索引值以及行数据，查询的时候不需要回表操作，这种就是聚簇索引；
      2. InnoDB 引擎的主键索引就是聚簇索引，Innodb存储引擎必须得有一个主键索引，如果自己没有手动创建，Mysql会自动维护一个row-id 字段来维护，一般主键索引我们自己维护，一定程度上也减少了Mysql的压力；

    • 非聚簇索引

      1. MyISAM 存储就属于非聚簇索引，联合索引其实也是；
      2. 一般来说在走这种索引查询数据时，会有回表查询操作，但是也不一定，select指定字段，利用覆盖索引可以解决回表情况；
Btree和Hash索引

​ 一般情况下，Mysql数据库常用的索引有hash索引和btree索引；hash索引底层就是hash表，单条记录查询的时候hash索引的效率更好，如果遇到范围查找则会全表扫描；所以大多数情况下会使用btree索引；Innodb存储引擎默认使用btree索引，存储引擎内部的bufferpool则会使用自适应hash索引；

Btree和B+tree的区别

​ Btree 【图片来自网络】

  • 索引值以及相应的数据分布在整个树结构中
  • 索引的顺序按照大小左右排列
  • btree从根节点使用二分法，查找数据，从根节点到子节点一层层查找直到找到活节点为空，因此可以利用这种特性把一些热点数据存放到靠近根节点的位置，所以高重复性查询的场景中，btree性能很高；

飞书20220601-202456.png

​ B+tree

  • 相对应的数据只存储在叶子节点，非叶子（内部）节点只存储key 用于查找；由于这种结构，可以把大量的内部节点加载进内存进行查找，减少了io操作，从而提升查询效率
  • 叶子节点之间用指针连接，提高区间访问性能；
  • 数据检索时，每次查询的路径长度都是固定的，因为数据都在叶子节点，索引内部是排好序的数据，所以在检索的时候每一条路径到达叶子节点效率大致相同；

飞书20220601-202615.png

​ hash索引

  • hash索引底层就是hash表，查询数据进行hash运算得到一个随机值，用得到的值取查询数据，由于随机性，期间可能会出现hash碰撞，如果碰撞次数过多，导致链表过长占用内存变大，同样影响查询效率；
  • 一般用于key-value等值查询，无法范围查找，因为每次hash计算后，索引的顺序无法与之前顺序保持一致；
  • innodb内部的bufferpool使用的是自适应哈希索引，内部会监听判断缓存池中使用索引对查询效率是否提升，从而判断是否使用自适应哈希；
avatar
文章
阅读
粉丝