MySQL_索引
什么是索引？
索引是一种帮助 MySQL 提高查询效率的数据结构
索引分别有哪些优点和缺点？
索引的优点：
- 提高查询效率
索引的缺点：
- 虽然提高了查询速度，但却降低了更新表的速度
- 因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件
- 建立索引的索引文件会占用磁盘
MySQL 索引的注意事项
- 联合索引遵循前缀原则
- like 模糊查询，% 百分号不能在前
- 如果 MySQL 估计使用索引比全表扫描更慢，会放弃使用索引
- 如果 or 前面的条件的列有索引，后面没有，会放弃使用索引
- 列类型是字符串，查询时一定要给值加引号，否则索引失效
为什么 MySQL 官方建议使用自增主键作为表的主键？
- 页分裂问题：如果使用非自增主键作为表的主键的话，当新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据移动到新的数据页上，以确保索引有序。也就是说**会造成页分裂，大量移动数据的过程严重影响插入效率
- 存储空间问题：自增主键存储空间小，同一个结点内能存放更多的主键，降低树的高度，减少 IO 次数
索引有几种类型？分别如何创建？
MySQL 的索引有两种分类方式：逻辑分类和物理分类
按照逻辑分类，索引可分为：
- 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL
- 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引
- 普通索引：一张表可以创建多个普通索引，可以重复，允许 NULL 值插入
按照物理分类，索引可分为：
- 聚簇索引：InnoDB 的主键索引存储采用聚簇索引，主键和数据存放在一起
- 非聚簇索引：MyISAM 的主键索引存储采用非聚簇索引，主键和指向数据的指针存放在一起，数据另开空间存储
什么叫回表查询？
普通索引查询到主键索引后，回到主键索引树搜索的过程，称为回表查询
在 InnDB 中主键索引为什么比普通索引的查询性能高？
因为普通索引的查询会多执行一次回表查询操作
MySQL 复合索引应该注意什么？
MySQL 中的复合索引遵循最左匹配原则
复合索引的作用是什么？
- 多字段查询：减少建立额外索引的开销
- 覆盖索引：直接通过遍历索引取得数据，不需要回表查询，减少随机的 IO 操作
什么是最左匹配原则？它的生效原则有哪些？
- 最左匹配原则也叫最左前缀原则，在联合索引中，索引的左侧列将优先参与索引的匹配和筛选，而后续列只有在左侧列匹配的情况下才会被考虑
- 当遇到范围查询停止匹配
以下语句会走索引么？
select * from t where year(date)>2018;不会，因为在索引列上涉及到了运算
什么是前缀索引？
前缀索引也叫局部索引，比如给身份证的前 10 位添加索引。 类似这种给某列部分信息添加索引的方式叫做前缀索引
为什么要用前缀索引？
- 前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度
- 缺点：不能在 order by 或者 group by 中触发前缀索引，也不能用于覆盖索引
什么情况下适合使用前缀索引？
当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引
InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？
B 树：
- B 树的非叶子节点也保存数据，导致扇出 (节点的最大子节点数量) 减少，需要更多层级，同时增加内存和磁盘的开销，降低数据访问的效率
- B+ 树将数据全部保存在叶子节点，减少磁盘IO的开销，提高了查询效率
Hash：
- Hash 索引适用于等值查询，但不支持范围查询和排序操作
二叉树：
- 普通二叉树的高度可能不均匀
- 二叉树无法自动平衡，需要特殊的平衡操作来维护树的性能
红黑树：
- 红黑树虽然是一种自平衡二叉搜索树，但是在数据量巨大时，高度仍然可能增加，导致性能下降
MySQL 如何指定查询的索引？
在 MySQL 中可以使用 force index 强行选择一个索引
select * from t force index(index_t);在 MySQL 中指定了查询索引，为什么没有生效？
- MySQL 会根据优化器自己选择索引
- 如果 force index 指定的索引出现在候选索引上，MySQL 就不会在判断扫描的行数，直接使用指定的索引，如果没在候选索引中，即使 force index 指定了索引也是不会生效的
如何优化身份证的索引？
前 6 位代表的是地区，所以很多人的前六位都是相同的，如果我们使用前缀索引为 6 位的话，性能提升也并不是很明显，但如果设置的位数过长，那么占用的磁盘空间也越大，数据页能放下的索引值就越少，搜索效率也越低
- 使用身份证倒序存储，这样设置前六位的意义就很大了
- 使用 hash 值，新创建一个字段用于存储身份证的 hash 值
索引的使用场景
- 对于非常小的表，大部分情况下全表扫描效率更高
- 中到大型表，索引非常有效
- 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决
聚簇索引和非聚簇索引
- 聚簇索引：InnoDB 的主键索引存储采用聚簇索引，主键和数据存放在一起
- 非聚簇索引：MyISAM 的主键索引存储采用非聚簇索引，主键和指向数据的指针存放在一起，数据另开空间存储
你写过索引吗？
InnoDB 索引实现
InnoDB使用 B+Tree 作为索引结构
- 主键索引：
- MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
- InnoDB 中，表数据文件本身就是按 B+ 树组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引
- InnoDB 的辅助索引：nnoDB 的所有辅助索引都引用主键作为 data 域
MyISAM 索引实现
MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
- 主键索引：MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的data 域存放的是数据记录的地址
- 辅助索引：在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复
🌟InnoDB 聚簇索引：
- 主键索引树，根结点存的是主键 id 和对应的数据
- 辅助索引树，根结点存的是辅助索引和对应的主键索引，因此需要根据主键到主键索引树上在遍历一次得到对应的数据
🌟MyISAM 非聚簇索引：
- 主键索引树，根结点存的是主键到数据地址的映射
- 辅助索引树，根结点存的是辅助索引到数据地址的映射，也可以直接得到数据，不需要回表
主键索引是聚集索引还是非聚集索引？
在 InnoDB 下主键索引是聚集索引
在 MyISAM 下主键索引是非聚集索引
MyISAM 索引实现？
MyISAM 存储引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址
MyISAM 索引与 InnoDB 索引的区别？
- InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引
- InnoDB 的主键索引的叶子节点存储着索引和行数据，因此主键索引非常高效
- MyISAM 索引的叶子节点存储的是行数据地址，需要根据地址访问对应的地址才能得到数据
- InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效
简单说一说 drop、delete 与 truncate 的区别
- drop 用于删除数据库对象，操作不可逆
- delete 用于删除满足条件的行，可以带条件，操作可逆
- truncate 用于从表中删除所有行，但保留表的结构，操作不可逆
delete、truncate 和 drop 分别在什么场景之下使用？
- 想删除部分数据行时候，用 delete，并且带上 where 子句
- 保留表而删除所有数据的时候用 truncate
- 不再需要一张表的时候，用 drop
⭐为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？
- 范围查询和顺序访问效率高：B+ 树的叶子节点形成有序链表，使得范围查询和顺序访问非常高效
- 更少的磁盘 I/O 操作：B+ 树的非叶子节点只存储索引信息，而数据都存储在叶子节点中，在进行索引搜索时，更多的数据能够在一个页中找到，从而减少磁盘读取
- 更稳定的高度：B+ 树的高度相对较低，B 树的高度会随着数据量的增加而增加，可能导致更多的磁盘 I/O 操作
⭐Hash 索引和 B+ 树有什么区别或者说优劣呢?
- Hash 索引进行等值查询更快，但是无法进行范围查询
- Hash 索引不支持使用索引进行排序
- Hash 索引不支持模糊查询
- Hash 索引需要回表查询数据,而 B+ 树在符合聚簇索引和覆盖索引的时候可以只通过索引完成查询
- Hash 索引虽然在等值查询上较快,但是不稳定，可能发生哈希碰撞，而 B+ 树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低
非聚簇索引一定会回表查询吗?
如果联合索引就可以实现索引覆盖，就不需要回表查询
创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
MySQL 提供了 explain 命令来查看语句的执行计划