PHP面试知识整理

索引分类

从物理存储角度

聚集索引（clustered index）
非聚集索引（non-clustered index）、二级索引

从逻辑角度

主键索引：主键索引是一种特殊的唯一索引，不允许有空值
普通索引或者单列索引
多列索引（联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
唯一索引或者非唯一索引
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

从数据结构角度

B-Tree索引
Hash索引： a 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询 b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引 c 只有Memory存储引擎显示支持hash索引
FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
R-Tree索引

聚簇索引（cluster index）

聚簇索引、聚集索引一个意思。

指索引项的排序方式和表中数据记录排序方式一致的索引。一个表至少有一个聚集索引。

如果表设置了主键，则主键就是聚簇索引
如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。

由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。
由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

sql

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

聚簇索引的结构

叶子节点存储行信息

MySQL 的覆盖索引与回表

非聚簇索引结构

非聚簇索引，其叶子节点存储的是聚簇索引的的值

MySQL 的覆盖索引与回表

sql

select * from user where id =1 
select * from user where age = 20

对于聚簇索引查询，需要扫描聚簇索引，一次即可扫描到记录，

对于非聚簇索引，利用非非聚簇索引，扫到聚簇索引的值，然后会再次到聚簇索引中查找，

回表查询

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。这个过程叫做回表

索引覆盖

查询的结果字段包含所有索引信息，不会回表，比如

sql

select id,age from user where age = 10

当age索引一次扫描到聚簇索引的值的时候，正好得到了所有的结果，避免了回表操作。

实现：将被查询的字段，建立到联合索引里去。

可以通过explain ，查看extra字段。如果是use index 则使用了索引，如果是null 则进行了回表

count 优化
列查询优化
分页查询优化

索引下推

索引下推(index condition pushdown) 指的是Mysql需要一个表中检索数据的时候，会使用索引过滤掉不符合条件的数据，然后再返回给客户端

sql

"select * from user where username like '张%' and age > 10"

没有ICP

根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据

1，获取下一行，首先读索引元组，然后使用索引去查找并读取所有的行

2，根据WHERE条件部分，判断数据是否符合。根据判断结果接受或拒绝该行

使用ICP

这个过程则会变成这样：

根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据

1，获取下一行的索引元组（不是所有行）

2，根据WHERE条件部分，判断是否可以只通过索引列满足条件。如果不满足，则获取下一行索引元组

3，如果满足条件，则通过索引元组去查询并读取所有的行

4，根据遗留的WHERE子句中的条件，在当前表中进行判断，根据判断结果接受或者拒绝改行

ICP默认启动。可以通过optimizer_switch系统变量去控制它是否开启：

ini

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

索引分类 ​

聚簇索引（cluster index） ​

普通索引 ​

聚簇索引的结构 ​

非聚簇索引结构 ​

回表查询 ​

索引覆盖 ​

索引下推 ​