合集 - 数据库(8)
1.MySQL索引
2.执行一条update会发生什么？3.MySQL事务隔离级别4.Redis学习笔记（一）5.JDBC学习笔记6.执行一条select语句会发生什么7.SQL聚合/多表/连接查询8.MySQL 一行记录是怎么存储的？
收起

一、索引介绍

1、索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

2、优缺点：

  • 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因
  • 创建索引和维护索引需要耗费许多时间。
  • 索引需要使用物理文件存储，也会耗费一定空间。

3、分类：

（1）按照底层数据结构分：

  • B+Tree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但 InnoDB 是聚集索引，索引结构和数据一起存放；MyISAM是非聚集索引，索引结构和数据分开存放。
  • 哈希索引：类似键值对的形式，一次即可定位。
  • RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
（2）底层的存储方式分：
  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，MySQL 的 MyISAM 引擎使用的是非聚簇索引。

回表：查询的数据不在索引里，需要通过索引中存储的地址指针到主表中查找对应的记录，这个过程就是回表。

（3）按照应用维度划分：

  • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  • 普通索引：仅加速查询。
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
使用联合索引需要遵守最左前缀匹配原则：
在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。
 

二、正确使用索引的建议

  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
 

三、索引失效的情况

1、使用最左或者左右模糊匹配时，比如 like '%abc';
2、创建了联合索引，但查询条件未遵守最左匹配原则;
3、在索引列上进行计算、函数、类型转换等操作;
4、查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
 

四、索引优化

1、前缀索引优化：在一些大字符串字段作为索引时，可以使用前缀索引来减小索引项的大小

2、覆盖索引优化：对于经常查询的字段建立联合索引，避免回表

3、主键索引最好是自增的，这样新增的数据就会按照顺序添加到索引节点的位置，不需要移动已有的数据

 

五、慢查询优化

1、索引优化：创建联合索引、覆盖索引、前缀索引等

2、sql语句优化：避免使用select * ，使用连接代替子查询，联表查询时要小表驱动大表

3、分页查询：避免一次性返回过多的数据

4、表结构优化：遵循第三范式，降低数据冗余，表过大的话分区、分表、分库、分片

5、使用redis缓存

 

分区：将一张表分散存储在多个不同的物理块上

分表：将表从逻辑上分成若干小表

分库：垂直切分，将不同业务逻辑的表分开存储在不同的数据库

分片：分表+分库