mysql 索引面试再也不怕啦

Myth丶恋晨 2022-12-07 15:13 227阅读 0赞

## 前言 ##

mysql 索引我们在面试是必问的，这里整理下来，自己对MySQL 索引有了全面了理解，面试的时候再也不怕啦。

## 索引类型 ##

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

从应用层次划分：普通索引、唯一索引、主键索引、复合索引

从索引键值类型划分：主键索引、辅助索引（二级索引）

从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

### 准备工作 ###

首先我们来创建一张表吧，然后创建索引的操作在这张表中来进行。

DROP TABLE IF EXISTS `r_resume`;
    CREATE TABLE `r_resume` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sex` varchar(10) DEFAULT NULL COMMENT '性别',
      `birthday` varchar(30) DEFAULT NULL COMMENT '出生日期',
      `work_year` varchar(100) DEFAULT NULL COMMENT '工作年限',
      `phone` varchar(20) DEFAULT NULL COMMENT '手机号码',
      `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
      `status` varchar(80) DEFAULT NULL COMMENT '目前状态',
      `resumeName` varchar(500) DEFAULT NULL COMMENT '简历名称',
      `name` varchar(40) DEFAULT NULL,
      `createTime` datetime DEFAULT NULL COMMENT '创建日期',
      `headPic` varchar(100) DEFAULT NULL COMMENT '头像',
      `isDel` int(2) DEFAULT NULL COMMENT '是否删除 默认值0-未删除 1-已删除',
      `updateTime` datetime DEFAULT NULL COMMENT '简历更新时间',
      `userId` int(11) DEFAULT NULL COMMENT '用户ID',
      `isDefault` int(2) DEFAULT NULL COMMENT '是否为默认简历 0-默认 1-非默认',
      `highestEducation` varchar(20) DEFAULT '' COMMENT '最高学历',
      `deliverNearByConfirm` int(2) DEFAULT '0' COMMENT '投递附件简历确认 0-需要确认 1-不需要确认',
      `refuseCount` int(11) NOT NULL DEFAULT '0' COMMENT '简历被拒绝次数',
      `markCanInterviewCount` int(11) NOT NULL DEFAULT '0' COMMENT '被标记为可面试次数',
      `haveNoticeInterCount` int(11) NOT NULL DEFAULT '0' COMMENT '已通知面试次数',
      `oneWord` varchar(100) DEFAULT '' COMMENT '一句话介绍自己',
      `liveCity` varchar(100) DEFAULT '' COMMENT '居住城市',
      `resumeScore` int(3) DEFAULT NULL COMMENT '简历得分',
      `userIdentity` int(1) DEFAULT '0' COMMENT '用户身份1-学生 2-工人',
      `isOpenResume` int(1) DEFAULT '3' COMMENT '人才搜索-开放简历 0-关闭，1-打开，2-简历未达到投放标准被动关闭 3-从未设置过开放简历',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2195388 DEFAULT CHARSET=utf8;

### 普通索引 ###

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

创建普通索引的方法如下：

##方式一
    create index 索引名 on 表名(字段);
    create index index_work_year on r_resume(work_year);
    
    ##方式二
    ALTER TABLE 表名 add INDEX 索引名(字段);
    ALTER TABLE r_resume add INDEX index_sex(sex);
    
    ## 查看索引
    show index from 表名
    show index from r_resume
    
    ## 删除索引
    drop index 索引名 on 表名

![image-20200901142725941][]

### 唯一索引 ###

和普通索引的区别，索引字段的值必须唯一，但是允许有空值，在创建或者修改表是追加唯一约束，就会自动的创建对应的唯一索引。

创建唯一索引的方法如下：

##方式一
    create unique index 索引名 on 表名(字段);
    CREATE UNIQUE INDEX index_userid on r_resume(userId)
    
    ##方式二
    ALTER TABLE 表名 add INDEX 索引名(字段);
    ALTER TABLE r_resume add UNIQUE INDEX index_userid(userId);

### 主键索引 ###

它是一种特殊的唯一索引，不允许有空值，在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

alter table 表名 add primary KEY(字段名)

### 复合索引 ###

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效

创建组合索引的方法如下：

##方式一
    create  index 索引名 on 表名(字段1,字段2);
    create index index_work_year_sex on r_resume(work_year,sex)
    
    ##方式二
    ALTER TABLE 表名 add INDEX 索引名(字段1,字段2);
    ALTER TABLE r_resume add INDEX index_work_year_sex(work_year,sex);

复合索引使用注意事项：

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

### 全文索引 ###

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下：

##方式一
    create FULLTEXT  index 索引名 on 表名(字段1);
    create fulltext index index_status on r_resume(`status`)
    
    ##方式二
    ALTER TABLE 表名 add FULLTEXT INDEX 索引名(字段1);
    ALTER TABLE r_resume add fulltext INDEX index_status(`status`);

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from r_resume where MATCH(`status`) AGAINST('我目前已离职')

![image-20200901172607641][]

全文索引使用注意事项：

show variables like '%ft%';

![image-20200901174013536][]

*  全文索引必须在字符串、文本字段上建立。
 *  全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
 *  全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
 *  全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a\*

## 索引原理 ##

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。

索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash 和 B+Tree。

B 树结构

*  索引值和data数据分布在整棵树结构中
 *  每个节点可以存放多个索引值及对应的data数据
 *  树节点中的多个索引值从左到右升序排列

![image-20200901171919051][]  
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+ 树结构

*  非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
 *  叶子节点包含了所有的索引值和data数据
 *  叶子节点用指针连接，提高区间的访问性能

![image-20200901193207221][]

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

### 聚集索引 ###

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

## 索引分析与优化 ##

### Explain ###

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN select * from r_resume where MATCH(`status`) AGAINST('我目前已离职') ;
    explain select * from r_resume where id> 2195333;

![image-20200901174118126][]

*  select\_type
    
    表示查询的类型。常用的值如下：
    
     *  SIMPLE ： 表示查询语句不包含子查询或union
     *  PRIMARY：表示此查询是最外层的查询
     *  UNION：表示此查询是UNION的第二个或后续的查询
     *  DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
     *  UNION RESULT：UNION的结果
     *  SUBQUERY：SELECT子查询语句
     *  DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

explain select * from r_resume where id> 2195333 union select * from r_resume where id=1;
    
    explain select * from r_resume where id in(select id from r_resume where sex='男');

![image-20200903134609609][]

*  type
    
    表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
    
     *  ALL：表示全表扫描，性能最差。
     *  index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
     *  range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
     *  ref：表示使用非唯一索引进行单值查询。
     *  eq\_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
     *  const：表示使用主键或唯一索引做等值查询，常量查询。
     *  NULL：表示不用访问表，速度最快。

explain select * from r_resume where id= 2195333 union select * from r_resume where id=1;
    explain select * from r_resume where id in(select id from r_resume where sex='男');
    explain select * from r_resume where sex='男';
    explain select * from r_resume where id> 2195333;
    explain select * from r_resume where id= 2195333;

*  possible\_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
 *  key：表示查询时真正使用到的索引，显示的是索引名称。
 *  rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
 *  key\_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
    
    key\_len的计算规则如下：
    
     *  字符串类型
        
        字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
        
        char(n)：n\*字符集长度
        
        varchar(n)：n \* 字符集长度 + 2字节
     *  数值类型
        
        TINYINT：1个字节
        
        SMALLINT：2个字节
        
        MEDIUMINT：3个字节
        
        INT、FLOAT：4个字节
        
        BIGINT、DOUBLE：8个字节
     *  时间类型
        
        DATE：3个字节
        
        TIMESTAMP：4个字节
        
        DATETIME：8个字节
     *  字段属性
        
        NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。
 *  Extra：表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
    
     *  Using where: 表示查询需要通过索引回表查询数据
     *  Using index: 表示查询需要通风索引就可以满足所需数据
     *  Using fifilesort：查询出来的结果需要额外的排序，数据量小在内存，大的话在磁盘，因此有Using fifilesort建议优化。
     *  Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

### 回表查询 ###

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做**回表查询**，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

### 覆盖索引 ###

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。不管是SQL-Server官网，还是MySQL官网，都表达了：**只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖**。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

### 最左前缀原则 ###

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

### LIKE 查询 ###

MySQL 在使用 like 模糊查询时，索引能不能起作用？

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

explain select * from r_resume where `status` like '%离职%';（没有用到索引）
    explain select * from r_resume where `status` like '离职%'; （用到了索引）
    explain select * from r_resume where `status` like '离职%'; （没有用到）

![image-20200901194149948][]

### NULL 查询 ###

如果 MySQL 表的某一列含有 NULL 值，那么包含该列的索引是否有效？

有效。

explain select * from r_resume where email is null;

![image-20200903135424353][]

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

## 查询优化 ##

### 慢查询定位 ###

1、开启慢查询

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

show variables like '%slow_query_log%';

![image-20200903140829418][]

通过如下命令开启慢查询日志：

SET global slow_query_log = ON; //开启慢查询的开关
    SET global slow_query_log_file = 'OAK-slow.log'; //修改慢查询日志存放的位置
    SET global log_queries_not_using_indexes = ON; //没有用到索引的查询就会记录
    SET long_query_time = 10;//查询时间超过10s 就会记录

2、慢日志查询方式

*  直接找到对应的文件，通过记事本查看。
    
    time：日志记录的时间
    
    User@Host：执行的用户及主机
    
    Query\_time：执行的时间
    
    Lock\_time：锁表时间
    
    Rows\_sent：发送给请求方的记录数，结果数量
    
    Rows\_examined：语句扫描的记录条数
    
    SET timestamp：语句执行的时间点
    
    select…：执行的具体的SQL语句
 *  使用mysqldumpslow查看
    
    MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。
    
    在 MySQL bin目录下执行下面命令可以查看该使用格式。
    
        perl mysqldumpslow.pl --help

### 慢查询优化 ###

慢查询原因总结

*  全表扫描：explain分析type属性all
 *  全索引扫描：explain分析type属性index
 *  索引过滤性不好：靠索引字段选型、数据量和状态、表设计
 *  频繁的回表查询开销：尽量少用select \*，使用覆盖索引

如何判断是否为慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long\_query\_time 参数做比较，如果语句的执行时间 > long\_query\_time，就会把这条执行语句记录到慢查询日志里面。long\_query\_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

如何判断是否应用了索引？

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain 命令分析查看，检查结果中的 key 值，是否为NULL。

应用了索引是否一定快？

查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

如何提高过滤性？

靠索引字段选型、数据量和状态、表设计。

假如有一个5000万记录的用户表，通过sex='男’索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

### 分页查询优化 ###

一般性分页

般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始；

第二个参数指定返回记录行的最大数目；

如果只给定一个参数，它表示返回最大的记录行数目；

如果偏移量固定，返回记录量对执行时间有什么影响？

select * from user limit 10000,1; 
    select * from user limit 10000,10; 
    select * from user limit 10000,100; 
    select * from user limit 10000,1000; 
    select * from user limit 10000,10000;

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from user limit 1,100; 
    select * from user limit 10,100; 
    select * from user limit 100,100; 
    select * from user limit 1000,100; 
    select * from user limit 10000,100;

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

**分页优化方案：**

*  利用覆盖索引优化
    
        select * from user limit 10000,100; 
        select id from user limit 10000,100;
 *  利用子查询优化
    
        select * from user limit 10000,100; 
        select * from user where id>= (select id from user limit 10000,1) limit 100;

使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

## 总结 ##

自己动手练习一遍，收获会很大。觉得有用的小伙伴赶紧收藏吧。

[image-20200901142725941]: /images/20221123/89a817766e8b4c85b135711d75d4c431.png
[image-20200901172607641]: /images/20221123/779ac96a86d44e26b1ce4cebd3cd729e.png
[image-20200901174013536]: /images/20221123/f66b60b3e586449985b2546d77345133.png
[image-20200901171919051]: /images/20221123/3c76e3a26ae1449b9d0d1a7b0ba6550e.png
[image-20200901193207221]: /images/20221123/2b36452c20c24db2acff4b7bad997ee3.png
[image-20200901174118126]: /images/20221123/e8c6905fedfe4fdc8a25f897094f2390.png
[image-20200903134609609]: /images/20221123/ccae612c51ab489d8791179f6c1d5cd5.png
[image-20200901194149948]: /images/20221123/880a64e245e24603a70b80f9b3919b02.png
[image-20200903135424353]: /images/20221123/c4d2665f06c24040b208055eb257c861.png
[image-20200903140829418]: /images/20221123/6d89fe8289964826b09eef998d282c0a.png