07 最左前缀原则、覆盖索引

桃扇骨 2023-03-05 02:10 16阅读 0赞

#### 1. 最左前缀原则？ ####

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如果不是按照索引的最左列开始查找，则无法使用索引。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
    select * from user where name=xx ; // 可以命中索引
    select * from user where city=xx ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

#### 2. 介绍下mysql的回表和覆盖索引？ ####

*  InnoDB有两大类索引，一类是聚簇索引，一类是普通索引，聚簇索引只有一个，假设现在有一张表t，有id，name，address等字段，select \* from t where name=‘lisi’，聚集索引是根据id建立的，由于普通索引无法定位到行记录，在查询过程中是需要先扫描普通索引定位到主键，再通过聚集索引定位到具体行记录，这就是回表查询，即先定位主键值，再根据主键值定位行记录。由于要扫描2次索引，性能比较低。
 *  索引覆盖是一种避免回表查询的优化策略，具体的做法是将所有要查询的数据所为索引列建立普通索引，这样就可以直接返回索引中的数据，不需要再通过聚集索引去定位行记录，避免了回表。
 *  如果一个索引覆盖（包含）了所有需要查询的字段的值，就称这个索引为覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。
 *  另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。
 *  不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引和全文索引都不存索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。