mysql 多列合并为一列_mysql面试名词聚簇索引、二级索引、最左匹配、覆盖索引、回表

如果你对mysql性能调优比较感兴趣，可以观看详细视频合集mysql调优系列。

聚簇索引

面试题：为什么一个表只有一个聚簇索引？

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行(索引的顺序与数据的物理存放位置一致，“聚簇”表示数据行和相应的键值紧凑地存储在一起)，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。(就是说是InnoDB这种引擎选择的索引结构是B+树来决定的)。InnoDB通关过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引作为替代。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距很远。

优点

1. 可以把相关的数据保存在一起
2. 数据访问更快。聚集索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中更快
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
6. 二级索引(非聚簇索引)通常更大，因为在二级索引中包含了引用行的主键列。也是因为如此，二级索引访问需要两次索引查找，而不是一次(第一次查找二级索引得到主键，第二次根据主键在聚簇索引中查找数据行)
7. 二级索引访问需要两次索引查找，而不是一次

InnoDB和MyIsam的数据分布对比

MyIsam按照数据插入的顺序存储在磁盘上。MyIsam的主键索引与其他的索引没有什么不同，主键索引就是一个名为PRIMARY的唯一非空索引。

MyIsam主键索引

InnoDB实际上是“索引组织表”，因为在InnoDB中，聚簇索引就是表。聚簇索引的每一个叶子节点包含了主键值、事务ID,用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩余的其他列。InnoDB的二级索引和聚簇索引有很大不同。InnoDB二级索引中存储的是主键值而不是行指针。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。

InnoDB主键索引

非聚簇索引(二级索引)

非聚簇索引数据文件跟索引文件分开存放，并且都是辅助索引，像组合索引、前缀索引、唯一索引等。

二级索引

最左匹配

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

示例：

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

在对多列组合建立索引时，需要遵循「最左前缀」原则。最左前缀原则：顾名思义，就是最左优先，上例中我们创建了 (col1, col2, col3) 多列索引,相当于创建了 (col1) 单列索引，(col1, col2) 组合索引以及 (col1, col2, col3) 组合索引。所以当我们在创建多列索引时，要根据业务场景，将 where 子句中使用最频繁的一列放在最左边。

覆盖索引

通常大家设计索引都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑整个查询，而不单单是WHERE条件部分。如果一个索引中包含了所需要查询的字段的值，我们就称为“覆盖索引”，覆盖索引能够极大的提高性能，覆盖索引带来的好处有：

索引条目远小于数据行大小，能够极大地提高性能，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量因为索引是按照值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少的多一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了主键的值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

示例：

explain select id,create_time from finance where create_time>'2020-6-1';

执行计划结果图

回表

回表问题则需要和上一期总结的聚集索引(clustered index)和非聚集索引(secondary index)联系起来， 因为InnoDB的聚集索引的叶子结点上存取着具体的主键索引和数据，如果通过其他二级索引想要获取数据时，索引覆盖的列(比如select * from table_name)不能满足需求，那么就需要再去查询主键索引，这种现象就称之为回表。

索引下推

索引条件下推，称为 Index Condition Pushdown (ICP)，用一句话总结是：索引下推是数据库检索数据过程中为减少回表次数而做的优化。这是Mysql5.6的版本上推出的。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

explain select * from finance where order_no like '20200625%' and create_id>'100';

在不使用索引条件下的情况下，在使用 非主键索引(二级索引)进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。在使用索引条件下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

注意点：

innodb引擎的表，索引下推只能用于二级索引。索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。

关闭命令(默认开启)

　set optimizer_switch='index_condition_pushdown=off';

不断分享开发过程用到的技术和面试经常被问到的问题，如果您也对IT技术比较感兴趣可以「关注」我