mysql索引面试必知

mysql索引面试必知

1,什么是索引
索引是一种数据结构，帮助mysql提高查询效率。

索引的优点：大大加快查询的素的
索引的缺点：

1. 维护索引需要耗费数据库资源
2. 索引需要占用磁盘空间
3. 当对表的数据进行增删改查时，因为要维护索引，速度会受到影响

2，索引的分类(必问)
-InnoDB引擎

• 主键索引：设置主键后的数据库自动建立索引，InnoDB为聚簇索引
• 单值索引（普通索引）：即一个索引只包含单个列,一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但是允许有空值
• 复合索引：即一个索引包含多个列

MYISAM引擎
Full Text 全文索引：（Mysql5.7版本之前，只能用于MYISAM引擎）
全文索引类型为 Full Text ，在定义索引的列上，支持值的全文查找，允许在这些索引列中，插入重复值和空值，全文索引 可以在 char varchar上创建，Mysql只有MYISAM存储引擎支持全文索引。

3，索引的基本操作

• 主键索引（mysql会自动创建）
• 首先创建一张表,并且创建一个主键
  -create TABLE t_user( id VARCHAR(20) primary key, NAME VARCHAR(20) );
  查看索引命令

show  index  from  t_user;

单例索引（普通索引，单值索引）
1，创建表之后，创建 单列索引

--创建单列索引,指定列的名称 name_index  是索引的名称--
create index name_index on t_user(NAME)

然后执行查看索引命令

show  index  from  t_user;

2，创建表的时候，创建 单列索引，系统默认认为索引名为字段名

 创建表的时候，创建索引
create table t_user1(
     id VARCHAR(20) primary key,
     NAME  VARCHAR(20),
     key(NAME)
 
)
查看索引
show index  from  t_user1

唯一索引
1， 建表的时候创建唯一索引

创建表的时候，创建索引
create table t_user2(
     id VARCHAR(20) primary key,
     NAME  VARCHAR(20),
     UNIQUE(NAME)
 
)
show index  from  t_user2

2，建表之后创建索引

create  unique  from t_user2(name)

复合索引

建表的时候创建复合索引

create  table t_user3(
id varchar(20)  primary key,
name varchar(20) ,
age  int,
KEY(name,age)
);
建表之后创建索引
create index  nameageindex  on  t_user(name,age)

show index  from  t_user3

复合索引面试问题：
如果复合索引的字段值有三个，分别以 name age bir 顺序创建复合索引，问 以下是否能够利用索引的编号

 1，   name  bir   age
 2，   name  age   bir
 3，   age   bir
 4，   bir  age  name
 5，   bir  age

这个问题涉及到mysql的两个知识点，
第一个：最左前缀原则（从最左边开始判断索引字段）
第二个：mysql引擎在查询时为了更好的利用索引，在查询过程中会动态调整查询字段顺序
以便利用索引
索引可以利用索引的是 ：1，2，4
不能利用索引的是 ：3，5

4，索引的底层原理

创建一个表并且导入无序的数值

create   table  t_tem (
id int   primary  key,
name     VARCHAR(20),
age      int  
);


无序插入

INSERT into  t_tem  VALUES(2,'d',26);
INSERT into  t_tem  VALUES(1,'a',21);
INSERT into  t_tem  VALUES(9,'c',24);
INSERT into  t_tem  VALUES(3,'e',25);
INSERT into  t_tem  VALUES(5,'g',27);
INSERT into  t_tem  VALUES(4,'f',22);
INSERT into  t_tem  VALUES(3,'b',23);
INSERT into  t_tem  VALUES(6,'h',29);


show  index  from  t_tem;

select *  from  t_tem;

运行结果

主键索引索引进行排序，为什么要进行排序

排序之后查询更快 索引底层结构，是一个
B+树，这个是一层的树结构的B+树图,叶子节点使用来存储数据的，非叶子节点用来存储主键和指针，而B-树的非叶子节点必须存储数据，这样就增加了索引的深度从而没有B+树更有效率。

B+Tree是在B-Tree（B树不能念作B减树）基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree树s实现其索引结构.

B+树相对于B树有几点不同

1. B+树非叶子节点只存储键值信息
2. 所有的叶子节点之间都有一个链指针
3. 数据记录都存放在叶子节点中

InnoDB存储引擎中的页的大小是16kb，一般表的主键类型为int（4字节）或者bigInt（8个字节），指针类型一般为4或者8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB（8B+8B）=1k个键值（估值
k取10^3）,所以一个深度为3的B+Tree的索引可以维护大概8亿条记录

实际情况中每个节点都步可能填充满，因此在数据库中B+Tree的高度在2–4层中，Mysql的InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/0操作。注意：顶层页常驻内存

5，聚簇索引和非聚簇索引

聚簇索引：将数据存储和索引放在一起，索引结构的叶子节点保存了行数据（主键索引）
聚簇索引不一定是主键索引，但是主键索引一定是聚簇索引

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点，指向了数据对应的位置。

注意：在InnoDB中，在聚簇索引之上创建的索引称为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引（普通索引），唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找

面试题：
辅助键索引的叶子节点中，为什么使用主键来二次查找所对应的数据，直接使用地址不是更快吗？

回答：如果在索引的叶子节点中直接放入到存储地址的话，那么对于数据库的增删改就会有很大的影响，这样的话，我们每次进行增删改的操作就会从新分配地址，极大的影响效率。

1，InnoDB（支持事务）

• InnoDB使用的聚簇索引，将主键组织到一颗B+树中，而行数据存储在叶子节点中，若使用where id=14这样的条件来查找主键，则按照B+树的索引算法，即可查到对应的叶节点，之后获得行数据。
• 若对name列进行条件搜索，则需要两个步骤，第一步：在辅助索引B+树中检索name，到达其叶子节点获取对应的主键，第二部使用主键，在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据（重点在于通过其他键需要建立辅助索引）
• 聚簇索引默认是主键，如果表中没有定义数据InnoDB会选择一个唯一且非空的索引来代替，如果没有这样的索引，InnoDB会隐式定义一个主键（类似与oracle中的rowId）来作为聚簇索引，如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先要删除主键，然后添加我们想要的聚簇索引,最后恢复主键设置，即可

2，MYISAM（不支持事务，偏向于查询）

• -Myisam使用的是非聚簇索引，非聚簇索引的l两颗B+树看上去没有什么不同，节点的结构完全一致，只是存储的内容不同而已，主键索引的B+树的节点存储了主键，辅助键索引B+树存储了辅助键，表数据存储在独立的地方。这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别，由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

6，聚簇索引的优势

• 由于行数据和聚簇索引的叶子节点存储在一起，同一页中有多条行数据，访问同一数据页不同行记录时，已经把页加载到buffer（缓存）中，再次访问者时，会在内存中完成访问，不必访问磁盘，这样行数据和主键是一起被载入内存中的，找到叶子节点就可以立即将数据返回了，如果按照主键来组织数据，获得的数据更快。

• 辅助索引的叶子节点，存储主键值，而不是数据的存放地址，好处是当数据放生变化时，索引树的节点也需要分裂变化，或者是我们查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就行了，另一个好处是，因为辅助索引存储的是主键值，减少了辅助索引存放的主键值，减少了辅助索引占用的存储空间大小。

7，使用聚簇索引需要注意什么

• 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid太过于离散了，不适合排序且可能出现新增加记录的uuid，会插入索引树的中间位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
• 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小，而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响IO操作，读取到的数据量。

8，为什么建议使用自增id

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即，只要是索引的是相邻的，那么对应的数据一定也是相邻的存放在磁盘上，如果主键不是自增id，那么可以想象，他会干什么，不断的调整数据的物理地址，分页，当然也有一些其他措施来减少这些操作，但却无法彻底避免，但是如果是自增的，那就简单了，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。

9，什么情况下无法利用索引

1.查询语句使用like关键字 ：在查询语句使用关键字的时候使用like关键字进行模糊查询的时候，如果匹配的字符串的第一个字符为“%”，索引将不会被使用，如果“%”不在第一个位置，索引就会被使用

2，查询语句中使用多列索引：多列索引是在表的多个字段上创建一个索引，只有查询字段中使用了这些字段中的第一个字段，索引才会被使用。

3，查询语句中使用OR关键字：查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引，如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。