MySQL索引——针对练习口试

MySQL索引

什么是MySQL索引？分类有哪些？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值举行分列的一种存储结构，它是某个表中一列或多少列值的聚集相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引雷同于册本的目次，可以资助数据库管理体系快速定位到数据所在的物理位置，从而加快数据检索的速率。
索引的分类

• 按【数据结构】分类：B+tree索引、Hash索引、Full-text索引
  
• 按【物理存储】分类：聚簇索引、二级索引
  
• 按【字段特性】分类：主键索引、唯一索引、平常索引、前缀索引
  
• 按【字段个数】分类：单列索引、团结索引
  

索引的优缺点是什么？

索引的优点

• 进步查询速率：索引最紧张的优点是加快查询速率。通过索引，数据库体系可以在更短的时间内定位到数据，淘汰全表扫描的需求。
  
• 加快排序：索引可以资助数据库更快地举行排序操纵，由于索引通常是有序的，以是可以利用索引的有序性来加快排序查询。
  
• 优化分组和聚合操纵：索引可以加快分组和聚合查询的速率，尤其是当索引列是分组依据时。
  
• 包管数据的唯一性：唯一索引可以包管数据库表中的数据的唯一性，制止插入重复的数据。
  

索引的缺点

• 增长写操纵的开销：当对表举行插入、删除、更新操纵时，除了要修改表数据外，还必要同时更新索引，这会增长额外的开销，尤其是在有大量索引的表上。
  
• 占用额外的磁盘空间：索引必要占用额外的磁盘空间，尤其是对于大型表，索引文件大概会非常大。
  
• 大概低落更新表的速率：固然索引可以加快查询速率，但同时也大概低落更新表的速率，由于每次更新操纵都大概必要重修或调解索引。
  
• 索引维护开销：数据库体系必要维护索引的划一性和准确性，这在有大量数据更新的环境下大概会成为一个负担。
  
• 索引选择性标题：假如索引的列具有低选择性（即列中重复值很多），那么索引的效果大概不会很好，由于索引树的分支会很深，查询服从不会比全表扫描高多少。
  
• 索引失效：假如查询条件没有准确利用索引，大概利用了不得当索引的操纵（如利用了函数或盘算），索引大概不访问效，导致查询性能降落。
  

利用索引肯定能进步服从嘛？

• 查询范例：对于某些范例的查询，索引可以明显进步服从，特别是那些涉及到大量数据的全表扫描的查询。比方，等值查询、范围查询、排序和分组操纵通常会从索引中受益。然而，对于某些小表大概查询条件非常特定的环境，全表扫描大概比利用索引更快。
  
• 索引选择性：假如一个索引的选择性很低，即列中的重复值很多，那么这个索引大概不会提供太大的资助。由于大量重复的索引值会导致索引树的分支很深，查询服从提拔有限。
  
• 写操纵：对于写操纵频仍的表，索引大概会低落性能，由于每次插入、更新或删除操纵都必要同时更新索引。这会增长额外的开销，尤其是在有大量索引的表上。
  
• 索引维护：数据库体系必要维护索引的划一性和准确性，这在有大量数据更新的环境下大概会成为一个负担，尤其是在没有准确优化索引的环境下。
  
• 索引失效：假如查询没有准确利用索引，大概查询条件利用了不得当索引的操纵（如利用了函数或盘算），索引大概不访问效，导致查询性能降落。
  
• 复合索引的最左前缀规则：对于复合索引，假如查询没有利用索引最左边的列，那么索引大概不会被利用。
  
• 数据分布：假如数据分布不匀称，某些索引大概很少被用到，大概利用效果不佳。
  
• 查询优化器：数据库的查询优化器会决定是否利用索引以及利用哪个索引。假如优化器的决定禁绝确，大概会导致性能标题。
  

说说聚簇索引和非聚簇索引

聚簇索引（Clustered Index）

• 数据存储方式：聚簇索引决定了表中数据的物理存储序次。在聚簇索引中，索引的叶节点直接包罗了数据行，大概说，数据行实际上就是索引的一部门。
  
• 唯一性：一个表只能有一个聚簇索引，由于数据的物理存储序次只能有一种。
  
• 性能：聚簇索引可以加快查询速率，由于索引结构和数据存储在一起，淘汰了磁盘I/O。对于范围查询特别有效，由于它可以序次访问数据页。
  
• 主键索引：在InnoDB存储引擎中，主键索引就是聚簇索引。假如没有显式创建主键索引，InnoDB会自动选择一个唯一的非空索引作为聚簇索引，大概在没有如许的索引时创建一个潜伏的聚簇索引。
  

非聚簇索引（Non-Clustered Index）

• 数据存储方式：非聚簇索引与数据的物理存储序次无关。在非聚簇索引中，索引的叶节点通常包罗索引列和对应的数据行的物理所在（如行ID），而不是数据行本身。
  
• 索引与数据分离：非聚簇索引的结构独立于数据行的存储，因此一个表可以有多个非聚簇索引。
  
• 回表查询：当查询利用非聚簇索引时，大概必要通过索引找到数据行的物理所在，然后再去数据文件中检索完备的数据行，这个过程称为“回表查询”。
  
• 覆盖索引：假如查询只必要索引中的列，而不必要访问数据行，那么这种索引被称为覆盖索引，可以制止回表查询，进步查询服从。
  
• 性能：非聚簇索引对于等值查询（如利用=）非常有效，但对于范围查询大概不如聚簇索引高效，由于数据不是序次存储的。
  
• 维护本钱：非聚簇索引在插入、删除和更新操纵时维护本钱较高，由于除了更新数据行外，还必要更新索引。
  

说说主键索引和二级索引

主键索引（Primary Key Index）

• 唯一性：主键索引要求索引列的值必须唯一，且不能为空（NOT NULL），包管了数据的唯一标识。
  
• 聚簇索引：在大多数数据库体系中，包罗MySQL的InnoDB存储引擎，主键索引同时也是聚簇索引。这意味着数据行的物理存储序次与主键索引的序次是划一的。
  
• 性能：由于主键索引通常是聚簇索引，它对于范围查询非常有效，由于数据是按主键序次存储的。
  
• 限定：一个表只能有一个主键索引，由于它界说了数据的物理存储序次。
  
• 自动创建：假如在创建表时没有指定主键，某些数据库体系（如InnoDB）会自动创建一个潜伏的聚簇索引。
  

二级索引（Secondary Index）

• 非聚簇索引：二级索引通常黑白聚簇索引，这意味着索引结构和数据行是分开存储的。在InnoDB中，二级索引的叶节点包罗索引键和对应的行指针（如行ID）。
  
• 可以多个：一个表可以有多个二级索引，每个索引可以覆盖差异的列组合。
  
• 查询和毗连：二级索引实用于加快非主键列的查询，以及用于表之间的毗连操纵。
  
• 覆盖索引：假如一个查询只必要利用到索引键和行指针，而不必要访问实际的数据行，那么这个索引被称为覆盖索引，可以进步查询服从。
  
• 回表查询：对于非覆盖索引的查询，数据库体系大概必要通过二级索引找到行指针，然后再到数据行中获取所需的数据，这个过程称为回表查询。
  
• 维护本钱：与主键索引相比，二级索引在数据插入、删除和更新时的维护本钱大概更高，由于必要同时更新索引结构。
  
• 机动性：二级索引提供了更多的机动性，由于它们可以针对特定的查询模式举行优化。
  

说说覆盖索引和团结索引

覆盖索引（Covering Index）

• 界说：覆盖索引是指一个索引包罗全部查询所需的字段，即查询可以直接通过索引来完成，而不必要访问表中的实际数据行。
  
• 优点：
  
  
  
  • 淘汰I/O操纵：由于查询可以直接在索引中完成，淘汰了对数据行的访问，从而淘汰了磁盘I/O操纵。
    
  • 进步查询速率：制止了回表查询，即不必要通过索引找到数据行的物理位置后再去获取数据，从而加快了查询速率。
    
  
  
• 应用场景：实用于那些查询只涉及少数几个列，且这些列都已经包罗在索引中的环境。
  
• 示例：
  
  1. SELECT column1, column2 FROM table WHERE column1 = 'value';

  复制代码
  假如存在一个包罗column1和column2的索引，那么这个查询就是一个覆盖索引查询。
  

团结索引（Composite Index）

• 界说：团结索引，也称为复合索引，是指一个索引包罗两个或多个列，这些列共同作为一个索引键。
  
• 最左前缀匹配原则：在利用团结索引时，查询优化器会根据最左前缀匹配原则来决定是否利用索引。这意味着查询条件必须包罗索引中的第一个列，才华有效地利用团结索引。
  
• 优点：
  
  
  
  • 进步多列查询服从：当查询条件涉及多个列时，团结索引可以进步这些列的查询服从。
    
  • 优化排序和分组：假如排序或分组操纵涉及多个列，团结索引可以提供更好的性能。
    
  
  
• 应用场景：实用于那些经常必要根据多个罗列行查询、排序或分组的环境。
  
• 示例：
  
  1. CREATE INDEX idx_name_age ON users(name, age);
  2. SELECT * FROM users WHERE name = 'John' AND age = 30;

  复制代码
  在这个例子中，idx_name_age是一个团结索引，包罗了name和age两个列。只有当查询条件同时包罗name和age时，索引才华被有效利用。
  
• 注意事项：团结索引中列的序次非常紧张，由于数据库查询优化器会按照索引中列的序次来匹配查询条件。
  

什么是索引下推？

索引下推（Index Condition Pushdown，简称ICP）是MySQL 5.6及更高版本中引入的一项查询优化技能。它答应数据库引擎在索引树中尽大概早地实验过滤条件，淘汰必要回表的数据量，从而进步查询服从。以下是索引下推的一些关键点：

• 淘汰数据访问：通过在存储引擎层过滤掉不满足条件的索引记载，可以淘汰从磁盘读取的数据量。
  
• 进步查询服从：由于必要查抄的记载数目淘汰，查询实验的速率得到提拔。
  
• 低落I/O操纵：淘汰不须要的I/O操纵，特别是在处置惩罚大量数据时，可以明显进步性能。
  
• 优化内存利用：淘汰了服务器层必要处置惩罚的数据量，从而低落了内存的利用。
  
• 工作原理：在没有利用ICP的环境下，MySQL的查询会先读取索引记载，然后根据索引中的主键值读取完备的行记载，末了在服务器层检测这些记载是否满足WHERE条件。利用ICP时，存储引擎会在读索引记载的同时，判定WHERE条件部门能否用索引中的列来做查抄，条件不满足则处置惩罚下一行索引记载；条件满足，才利用索引中的主键去定位并读取完备的行记载。
  
• 应用场景：索引下推特别实用于高度选择性的过滤条件、复杂的查询以及大数据集，由于这些环境下可以明显淘汰必要从磁盘读取的数据量。
  
• 利用条件：索引下推只能用于range、ref、eq_ref、ref_or_null访问方法；只能用于InnoDB和MyISAM存储引擎及其分区表；对InnoDB存储引擎来说，索引下推只实用于二级索引。
  
• 体系参数：索引条件下推默认是开启的，可以利用体系参数optimizer_switch来控制器是否开启。
  

索引下推是MySQL中一项紧张的查询优化技能，它可以明显进步查询性能，特别是在处置惩罚复杂查询和大数据集时。在MySQL 5.6及更高版本中，索引下推是默认开启的，无需额外设置。