面试官：索引介绍一下

我：ok，先说一下**索引类型**：

- FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

- HASH：由于HASH的唯一及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

- BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

- RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

再说一下**索引种类**：

- 普通索引：仅加速查询

- 唯一索引：加速查询 + 列值唯一（可以有null）

- 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

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

- 全文索引：对文本的内容进行分词，进行搜索

- 索引合并：使用多个单列索引组合搜索

- 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

- 聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)

其次说**索引结构**：

**MyISAM**：

1. MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，同样使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

2. 在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

3. MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

**InnoDB**：

1. InnoDB的数据文件本身就是索引文件，这棵树的叶节点data域保存了完整的数据记录（聚集索引）

2. InnoDB的辅助索引data域存储相应记录主键的值而不是地址

3. 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

补充一下**为什么InnoDB索引是B+**：

- Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描

- 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。

- 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。

- 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。

- B+树：在B树的基础上，**B+树相对于B树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少**；此外将叶节点使用指针连接成链表，范围查询更加高效。B+树的**非叶子节点不保存数据**，只保存**子树的临界值**（最大或者最小），所以同样大小的节点，

补充B树：

B树（英语: B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让**查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成**。B树，概括来说是一个**一般化的二叉查找树**（binary search tree），可以拥有最多2个子节点。与自平衡二叉查找树不同，B树适用于读写相对大的数据块的存储系统，例如磁盘。

- 关键字集合分布在整颗树中；

- 任何一个关键字出现且只出现在一个结点中；

- 搜索有可能在非叶子结点结束；

- 其搜索性能等价于在关键字全集内做一次二分查找；

查询什么时候不走**索引**：

1. **模糊查询 %like**

2. **索引列参与计算,使用了函数**

3. **非最左前缀顺序**

4. **where对null判断**

5. **where不等于**

6. or操作有至少一个字段没有索引

7. 需要回表的查询结果集过大（超过配置的范围）

8. **将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描**

**索引最左原则：**

**举例子**：

如果索引列分别为A，B，C，顺序也是A，B，C：

- 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询

- 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引

- 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了

- 如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引（比如，a>= 3 and b = 4 and c = 5; A走索引，bc不走）（比如，a = 3 and b >= 4 and c = 5; a和b走，c不走）

**组合索引的底层其实按照第一个索引排序，从排序里面查第二个索引，以此类推。如果第一个索引失效，或者没有经过第一个索引，后面没发在前面的基础上查询。**

**为什么使用索引？**

- 通过创建唯一性索引，可以保证数据库表中每一行数据的**唯一性**。

- 可以大大加快数据的**检索速度**，这也是创建索引的最主要的原因。

- 帮助服务器**避免排序和临时表**。

- 将**随机IO变为顺序IO**。

- 可以**加速表和表之间的连接**，特别是在实现数据的参考完整性方面特别有意义。

但是使用索引要看一条准则--- 那就是读写比例，我们知道索引的缺点：

- 当对表中的数据进行增加、删除和修改的时候，**索引也要动态的维护**，这样就降低了数据的维护速度。

- 索引需要**占物理空间**，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。

- **创建索引和维护索引要耗费时间**，这种时间随着数据量的增加而增加

**你想，如果某个场景，发送10条请求，9条写，1条读。 加索引岂不是在浪费效率和空间？**

面试官：聊聊**explain**

我：好的，不过这一块内容好多，我只说几个关键的吧

1. id : 表示SQL执行的顺序的标识,SQL从大到小的执行

2. select_type：表示查询中每个select子句的类型

3. table：显示这一行的数据是关于哪张表的，有时不是真实的表名字

4. type：表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

5. possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6. Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。

7. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

8. ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

9. rows： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好

10. Extra：该列包含MySQL解决查询的详细信息

面试官：慢查询优化

我：我试试

打开慢查询日志

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE

2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

4. order by limit 形式的sql语句让排序的表优先查（这里要注意如果数据量大，要注意了）

5. 了解业务方使用场景

6. 加索引时参照建索引的几大原则

7. 观察结果，不符合预期继续从0分析

咱们知道，使用limit分页查询，offset越大，性能越差，比如：

select * from table order by id limit 50000,2;

受影响的行: 0

时间: 0.171s

select * from table

where id >= (select id from table order by id limit 50000 , 1)

limit 2;

受影响的行: 0

时间: 0.035s

select * from table as t1

join (select id from table order by id limit 50000, 1) as t2

where t1.id <= t2.id order by t1.id limit 2;

受影响的行: 0

时间: 0.036s

原因：因为 MySQL 并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。比如上面的(50000，2)，每次取2条，还要经过回表，发现不是想要的，舍弃。那肯定非常耗时间，而通过子查询通过id索引，只查询id，使用到了innodb的索引覆盖, 在内存缓冲区中进行检索,没有回表查询. 然后再用id >= 条件,进一步的缩小查询范围.这样就大大提高了效率。

而MyISAM，是直接索引是分离的，通过索引文件查到的数据记录地址，不需要回表，直接对应数据记录，效率也很高。