Categories: 技术原创

MYSQL面试八股文-索引类型和使用相关总结

什么是索引?

索引是一种用于加速查询的数据结构。它可以使得数据库在查找数据时更快地定位到需要的数据。

索引的类型

1. 主键索引

它是一种特殊的唯一索引,不允许有空值。
一般是在建表的时候同时创建主键索引。
一个表只能有一个主键。

2. 普通索引

最基本的索引,无任何限制。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引。

3. 唯一索引

唯一索引列的值必须唯一,但允许有空值。
如果是组合索引,则列值的组合必须唯一。
可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引。
可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引。

4. 组合索引

即一个索引包含多个列,多用于避免回表查询。
可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引。
值的组合唯一时可以创建组合唯一索引,查找效率更高。

5. 全文索引

也称全文检索,是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引。
要实现中文查询需要使用ngram分词插件,如进一步需要单字索引,需要设置ngram_token_size=1。

6. 空间索引

MySQL5.7以后支持,使用r树实现,可用于快速查询地理位置数据,距离数据等。
可以通过 ALTER TABLE table_name ADD SPATIAL KEY `index_name` (`column`);创建空间索引。
实测记录计算函数不会走空间索引,需要进行距离排序最好的解决方案依然是使用Redis geohash或MongoDB。

7. 多值索引

MySQL8以后针对比较小的关系数据,可以使用JSON数据类型结合整数数组建立关系和索引,避免建立繁琐的关系表或无法走索引导致查询效率低下的问题。
多值索引限制较多,目前仅整形数组支持得比较好;优化器会根据实际情况选择索引进行使用。
可以通过 ALTER TABLE table_name ADD KEY `key_name` ((CAST(json_extract(`column`,_utf8mb4’$.data_name’) AS UNSIGNED array)));创建多值索引。

8. 哈希索引

哈希索引是一种基于哈希表实现的索引结构,用于快速查找特定键值的记录。
哈希索引适用于等值查询,即根据完整的索引键值进行查询,而不支持范围查询或排序操作。
哈希索引将索引键值通过哈希函数计算得到一个哈希码,然后将记录存储在对应的哈希槽中。
哈希索引在处理大量数据时可能会出现哈希冲突,需要解决冲突的方法,如链式法(使用链表解决冲突)或开放寻址法(使用开放地址法解决冲突)。
MySQL MEMORY 引擎默认的索引类型就是hash索引。

9. 自适应哈希索引

自适应哈希索引是MySQL InnoDB存储引擎中引入的一种索引类型,无用开发者操作。
自适应哈希索引是在内存中动态构建的,它根据查询模式和数据访问频率自动调整索引的大小和内容。
当一个表使用了自适应哈希索引后,InnoDB会在内存中维护一个哈希表,用于加速查询操作。
自适应哈希索引适用于等值查询和部分范围查询,可以提供较高的查询性能。
自适应哈希索引在内存中构建,因此对于大型表或者数据量较大的情况,可能会导致内存占用较高。

自适应即我们不需要自己处理,当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点,就会给其建立一个hash索引;
hash索引底层的数据结构是散列表(Hash表),其数据特点就是比较适合在内存中使用,自适应Hash索引存在于InnoDB架构中(不存在与磁盘架构中

10. 函数索引

支持的函数:MySQL 8函数索引支持多种内置函数,例如数学函数(如ABS、ROUND)、字符串函数(如UPPER、LOWER)、日期函数(如YEAR、MONTH)等。您还可以创建自定义函数,并将其用于函数索引。

创建函数索引:要创建函数索引,可以使用CREATE INDEX语句,并在索引表达式中使用函数。例如,以下是一个创建函数索引的示例:

1
CREATE INDEX idx_func_index ON my_table (UPPER(column_name));

使用函数索引:一旦函数索引被创建,MySQL可以在查询中自动使用该索引。例如,当执行一个包含函数表达式的查询时,MySQL会尝试使用函数索引来加速查询执行。这样可以避免在查询过程中对每个数据行都进行函数计算。

索引使用注意事项

1)适合索引的列是出现在where子句中的列,或者连接子句中指定的列;
2)基数较小的类,索引效果较差,没有必要在此列建立索引,例如用户性别这种列;
3)使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;
4)不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

索引失效的一些场景

1. 如果MySQL估计使用索引比全表扫描还慢,则不会使用索引。

小表:对于小型表,即使查询需要扫描整个表的数据,性能可能仍然很好。在这种情况下,临界百分比可能较高,甚至可以接近或达到100%。

中等表:对于中等大小的表,一般认为当需要扫描的数据量超过10%到20%时,可能会考虑不使用索引而执行全表扫描。

大表:对于大型表,通常建议使用索引来减少查询范围。当需要扫描的数据量超过整个表的1%到5%时,可能会考虑不使用索引而执行全表扫描。

2. 前导模糊查询不能命中索引

以下两种情况都不会走索引,如果有模糊查询需要,可以考虑使用全文索引。

1
2
SELECT name FROM USER WHERE name LIKE '%s%';
SELECT name FROM USER WHERE name LIKE '%s';

3. 部分隐式转换导致不走索引

例如:当前列类型为字符串,但是查询数字的时候没有用引号将数字括起来,将不会走索引。
但是,如果当前列为数字,使用引号将数字括起来后发生的隐式转换是会走索引的。

隐式字符编码转换,A表字符编码为UTF8,B表字符编码为UTF8MBA4,在通过varchar或其它字段进行连表查询时,即使都有索引,也不会走索引。

4. 符合索引不满足最左原则

复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中符合索引。

5. union、in、or都能够命中索引,建议使用in

union、in、or都能够命中索引,建议使用in。
但是在in的条件比较多的时候可以使用exists和not exists替换in 和not in。

查询的CPU消耗:or>in>union>exists。

6. or查询需要保证前后都符合索引列

用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。
因为or后面的条件列中没有索引,那么后面的查询肯定要走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。

7. 负向条件查询不能使用索引

负向条件有:!=、、not in、not exists、not like等。
并且,负向条件不能命中缓存,如果可以,可尽量转换为正向条件进行查询。

8. 范围条件查询可以命中索引

范围条件查询可以命中索引,但是范围过大数据过多的话优化器依然会选择使用全表扫描。
范围条件有:、>=、between等。

9. 函数计算的索引命中

MySQL8以前,函数计算不会命中索引。
MySQL8以后,新增了函数索引,创建函数索引后,可以使用函数索引加速查询。

10. 利用覆盖索引进行查询,避免回表

被查询的列,数据能从索引中取得,而不用通过行定位符row-locator再到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

11. 建立索引的列,尽量不要使用null

单列索引中的null值,在用IS NULL进行查询是可以使用索引,但在IS NOT NULL条件下就不会走索引了,而且NULL对索引的数据结构不友好,尽量避免使用NULL。

复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集,所以,请使用not null约束以及默认值。

优化过程中的一些建议

1. 更新频繁读取较少的字段上不宜建立索引:

因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。而且索引的功能主要是加速查询,如查询较少,无需建立索引。

2. 区分度不大的字段上不宜建立索引:

类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤数据,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。

3. 条件符合的情况下尽量使用唯一索引:

业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。
另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。

4. 多表关联时,要保证关联字段上一定有索引

5. 创建索引时避免以下错误观念:

索引越多越好,认为一个查询就需要建一个索引;
宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度;
抵制唯一索引,认为业务的唯一性一律需要在应用层通过“先查后插”方式解决;
过早优化,在不了解系统的情况下就开始优化。

程序猿老龚(龚杰洪)原创,版权所有,转载请注明出处.

龚杰洪

Recent Posts

GOLANG面试八股文-并发控制

背景 协程A执行过程中需要创建…

1 年 ago

MYSQL面试八股文-常见面试问题和答案整理二

索引B+树的理解和坑 MYSQ…

1 年 ago

MYSQL面试八股文-InnoDB的MVCC实现机制

背景 什么是MVCC? MVC…

1 年 ago

MYSQL面试八股文-索引优化之全文索引(解决文本搜索问题)

背景:为什么要有全文索引 在当…

1 年 ago

MYSQL面试八股文-常见面试问题和答案整理一

基础部分 1、什么是 MySQ…

1 年 ago