mysql索引详解

1.索引是什么？

1.官方的定义，索引是帮助mysql高效获取数据的一种排好序的数据结构。

2.索引的分类？

1.主键索引，主键是一种唯一性索引，它必须指定为primarykey,一个表只能有一个主键，但是主键可以包含多个列！
当我们指定一个主键时，mysql会帮我们自动为这个主键建立一个索引
2.唯一索引，索引列的值必须唯一，但允许有空值
创建语法：
create unique index 索引名称 on 表名(字段名);
3.普通索引，基本的索引类型，值可以为空，没有唯一性的限制
创建语法：
create index 索引名称 on 表名(字段名);
4.全文索引，全文索引的类型为FULLTEXT，全文索引可以在char,varchar和text类型的列上创建
5.组合索引，即一个索引包含多个列，专门用于组合搜索
创建语法：
create index 索引名称 on 表名(字段名1,字段名2,字段名3);

3. 为什么使用索引能提高查询效率？

我们知道二叉树也是一种数据结构，那么如下例子我们先假设mysql底层使用二叉树来存储数据，注意：mysql底层实际上是用B+Tree来存储数据的。

从上图可以看得出来，如果没用到索引，查找age=16的那条数据，那么需要查找6次才能找到正确的数据行。但是如果我们是以二叉树结构来存储数据的话，可以看到，只需要查找3次即可找到age=16的数据行，这样查询效率是不是提高了不少呢？

4. 红黑树？

1.红黑树是什么呢？其实红黑树是在二叉树的基础上做了优化，它可以说是一个平衡的多叉树
首先来看一下，如果我们以二叉树来存储1，2，3，4，5，6，7，8这几个值，结构是如下，如果我们要查找8这个值，需要从头到尾查找8次才可以查找到结果，那么这种情况用不用索引，查找次数都一样，意义不大！

再者，我们以红黑树来存储1，2，3，4，5，6，7，8这几个值，结果是如下图

我们可以看到这个时候，查找8这个元素只需要查找4次即可，这种方式比二叉树存储查找数据时效率要高！事实上查找效率是和树的高度密切相关的，红黑叔它做了自动平衡，不会让任意一边的树的高度过高，数据量多的情况下，红黑树的查询效率当然比二叉树的查询效率要高。但是mysql底层为什么没有用红黑树呢？刚才提到了数据量多的情况下，如果数据量高达数十万，百万，这时候无论红黑树再怎么平衡，数的高度必定也会慢慢变高，意味着查询效率也绝对会变低！

5. 索引的结构分类？

1.B-Tree索引

特点：
1）叶节点具有相同的高度，叶节点的指针为空
2）所有索引元素不重复
3）节点的数据索引从做左到右为依次递增

缺点：B-Tree索引相对于红黑树，查询效率比它高，那么为什么mysql底层没有使用B-Tree呢？而是使用B+Tree，这里先说一个概念-页。什么是页呢？实际上mysql将数据加载到内存中就是以页作为单位来加载的，其实从上图，17，50，80那一个整体区域的数据就可以理解为一页，mysql中，innodb存储引擎的一页的大小默认约为16kb,那么这个时候这个节点上存储的数据有什么？有索引的信息，磁盘地址信息以及data，由于data也是占据不少空间的，所以一个节点上的索引主键能保存个数并不是很多，这样，在数据量达到百万甚至千万级别的时候，其依然需要通过增加树的高度来存储数据！归根结底就是，树的高度很大程度上影响着查询效率！！！

2.B+Tree索引（常用，mysql中索引默认使用这种结构）
2.1）主键索引

2.2）非主键索引(有主键，给name加了一个普通索引，需要进行回表操作)
假设有一个student表，我们给name这个字段加了一个索引，那么它的底层存储结构是这样的

它需要根据索引主键进行回表操作，去到主键索引存储的数据里查找数据，如下图

2.3）非主键索引(没有主键，给name加了一个唯一索引，此时mysql底层依然使用B+Tree并且根据name来进行排序和存储数据)

2.4）特点
2.4.1）非叶子点不存储data，只存储冗余索引，可以存放更多的索引，可以添加更多的分支，而不需要增加树的高度
2.4.2）叶子节点包含所有索引字段
2.4.3）叶子节点用指针连接，在进行范围查找时，能提高区间访问的性能

3.Hash索引（常用）

特点：
1）对索引的key进行一次Hash运算就可以定位出数据存储的位置
2）很多时候Hash索引要比B+Tree索引效率要高
3）仅能满足=和in查询，不支持范围查询
4）hash冲突
4.full-text全文索引（不常用）
5.R-Tree索引（不常用）

6. 聚簇索引和非聚簇索引的区别？

1.innodb存储引擎使用的是聚簇索引

2.myisam存储引擎使用的是非聚簇索引

3.两者的区别
1）聚簇索引，索引和数据是保存在同一个文件的，非聚簇索引的索引和数据是分开来保存的，所以在查询上，聚簇索引的效率比非聚簇索引高，聚簇索引查找到索引时，因为索引文件和数据文件绑定在一起的，所以找到了索引也代表着查找到数据了，而非聚簇索引需要进行回表操作。

7. 为什么innodb的表要建立主键，并且推荐使用自增的整形主键？

1.建立主键的原因
1.1）如果我们给表加了主键，那么mysql底层就会默认给我们的主键加上索引，就会通过主键索引的方式（底层使用B+Tree）来存储数据
1.2）如果我们没给表加主键，mysql会去看这个表有没有唯一索引，如果有唯一索引，就会按照唯一索引的方式（底层使用B+Tree，只是排序和主键排序不一样，排序会按照索引列来排序）来存储数据
1.3）如果一个表我们没有给它设置主键，其他字段也没有加唯一索引，那么这个时候，mysql会帮我们添加多一列rowid（整型自增）来充当主键，没必要让mysql干这么多活，有损性能，尽量在建表的时候都设置一个主键！

2.推荐使用自增整形主键的原因
2.1）方便索引的比较和排序，设想我们在使用uuid作为主键时，比较一个字符串，和比较一个整型数值，谁的效率高?
2.2）使用整型的自增主键对数的结构变动不大，设想我们使用uuid作为主键时，这时候索引主键值有可能在树的左半部分，也可能在树的右半部分，如果在树的左半部分，插入树中时，其他节点也要做出很大的变动，而使用整形自增的主键时，可以使得索引主键值稳定在树的右半部分存储，说到底，树结构变动越大，性能越低！

8. 组合索引的底层存储结构？

一.组合索引底层结构（有主建的情况下）
1.给user表的name,age和gender加一个组合索引

create index nag_index on user(name,age,gender)

2.组合索引底层结构（有主建的情况下）图

3.需要进行回表操作

二.组合索引底层结构（没有主建的情况下，组合索引唯一）
1.给user表的name,age和gender加一个唯一的组合索引

create unique index unag_index on user(name,age,gender)

2.组合索引底层结构（没有有主建,组合索引唯一的情况下）图

9. innodb存储引擎和myisam存储引擎的区别？

1）InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
2） InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3.） InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4）. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5）. InnoDB 支持表锁和行锁，MyISAM仅支持表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
6）.InnoDB不支持全文索引，MyISAM支持全文索引（5.6后）
7）.InnoDB适合大量的indert,delete和update操作，MyISAM适合大量的select操作

9. 如何排查慢sql？

一…explain
1.1 使用explain关键字可以模拟优化器执行sql语句，从而知道mysql时如何处理你的sql语句的，分析你的查询语句或者表结构的性能瓶颈
1.2 explain后的结构

1.3 字段分析
1） explain之id字段
select 查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序
分三种情况：

1.1）id全都相同，执行顺序由上至下
1.2）id全都不同，如果是子查询，id的序号会递增，id值越大，优先级就越高，越先被执行
1.3）id有些相同，有些不同的情况，id如果相同，那么可以认为它们是一组的，执行顺序由上至下，在所有组中，id越大的，优先级越高，越先被执行

2） explain之select_type字段
2.1）SIMPLE，简单的select语句，查询中不包含子查询和union
2.2）PRIMARY，查询中若包含任何复杂的子查询部分，最外层则被标志为PRIMARY
2.3）SUBQUERY，在select或者where包含了子查询
2.4）DERIVED(衍生)，在from列表中包含的子查询被标志位DERIVED（衍生），mysql会递归执行这些子查询，把结果放在临时表里
2.5）UNION，若第二个select语句出现在UNION之后，则被标志为UNION，若UNION包含在from子句的查询中，外层select被标志为DERIVED
2.6）UNION RESULT，从UNION表获取结果的select语句

3） explain之type字段
type意为访问类型，是较为重要的一个指标，一般查询来说，至少达到range,最好达到ref
以下最好到最差的顺序是？
system>const>eq_ref>ref>range>index>all
3.1）system
表中只有一条记录，等于系统表，这是const类型的特例，平时不会出现，一般忽略不计
3.2）const
表示通过索引一次就找到了，const用于比较primary key和唯一索引，因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将改查询转换为一个常量
3.3）eq_ref
唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
3.4）ref
非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单个值行，然而，它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体
3.5）range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between,<,>,in等的查询，这种范围扫描索引比全表扫描要好，因为它需要开始于索引的某一点，结束语索引的另一点，不用扫描全部索引

3.6）index
full index scan,index和all的区别是index类型只遍历索引树，这通常比all快，因为索引文件比数据文件小，也就是说虽然index和all虽然都是读全表，但index是从索引中读的，all是从硬盘中读的
3.7）all
full table scan,将遍历全表以找到匹配的行
3.8）NULL

4） explain之possible_keys字段
显示可能应用在这张表的索引，一个或者多个，查询涉及到字段上若存在索引，则该索引将被列出，但不一定被实际查询使用

5） explain之key字段
实际使用的索引，如果为NULL，则代表没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。何为覆盖索引？其又被称为索引覆盖，就是select的字段都已经建立了索引，其不必再去读取数据行，mysql会利用索引返回select列表中的字段，不必根据索引再次读取数据文件。

6） explain之key_len字段
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，而key_len是根据表定义计算而得，不是通过表内检索而出的

7） explain之ref字段
显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或者常量被用于查找索引列上的值

8） explain之rows字段
根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数

9） explain之Extra字段
包含不适合在其他列中显示但十分重要的额外信息,这些信息很有可能导致sql执行得很慢
9.1）Using fileSort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序，又称之为文件排序
9.2）using temporary
使用了临时表保存了中间结果，mysql在对查询结果进行排序时使用临时表，常见于order by和group by
9.3）using index
表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率还不错。如果同时出现using where,表明索引被用来执行索引键值的查找，如果没有同时使用using where,表明索引用来读取数据并非执行查找动作
9.4）using where
表明使用了where过滤
9.5）using join buffer
表明使用了连接缓存
9.6）impossible where
表示where子句的值总是false,不能用来获取任何元组
9.7）select tables optimized away
在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
9.8）distinct
优化distinct操作，在找到第一匹配的元组后停止查找同样值的动作

二.slow query log(开启慢查询功能)
1.慢查询概念
慢查询日志时mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阙值的sql语句，具体是指运行时间超过long_query_time（开启之后，默认为10）的值，则会被记录到慢查询日志中
2.开启慢查询功能（针对当前会话有效）

3.如何永久开启慢查询功能，需要在mysql的配置文件上加

4.慢的sql如何就会被记录在atguigu-slow.log文件中
5.查看慢sql的总记录数

三.mysqldumpslow（日志分析工具）
1.查看可用参数
mysqldumpslow --help;

2.参数说明

3.事例

四.show profile
1.含义
show profile是mysql提供可以用来分析当前会话中，语句执行的资源消耗情况，可以用于sql的调优。

2.开启show profile功能

3.查看sql语句的执行时间

4.查看某条sql语句的生命周期，可以看到哪一步花费了多少时间

5.profile后面可接的参数

6.牢记使用show profile + 参数后，出现如下的这几个结果，跟sql的性能有着极大的关联！！！

10. 什么时候适合建索引，什么时候不适合建索引？

1.适合建索引的情况
1）主键自动建立唯一索引
2）频繁作为查询的字段应该加索引
3）查询中，与其他关联表的字段，外键关系建立索引
4）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
5）查询中统计或者分组字段

2.不适合建索引的情况
1）表记录太少
2）经常做增删改的表
3）数据重复且分布平均的字段，因此只为经常查询和经常排序的字段加索引

11. 索引失效的原因？

1.没有遵循最佳左前缀原则，最佳左前缀法则，如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最前列开始并且不跳过索引中的列
2.在索引列上做任何操作（计算，函数，自动或者手动类型转换），会导致索引失效而去做全表扫描
3.存储引擎不能使用索引中范围条件右边的列，比如有一个复合索引index（A，B,C），但是where后面的条件为A=x and B > x and c = x,出现范围就会导致索引失效
4.使用不等于(！=，<>)会使得索引失效，但是在mysql8.0依然有效
5.使用is null,is not null也会使得索引失效，但是在mysql8.0 is null的情况下，其还是有效
6.like以通配符%开头（%abcd…）,会使得索引失效
7.字符串不加单引号会使索引失效
8.少用or,用它来连接时会出现索引失效，如mysql8.0并没有未失效

9.总结

10.group by 和order by索引注意事项

12. 什么是覆盖索引？

1.覆盖索引，即是查询的字段和索引列相同,查询效率很高！它可以解决like以%开头时索引失效的问题！。
比如创建了一个组合索引，包含了三个字段

create index nag_index on student(name,age,gender);

覆盖索引例子：

select name,age,gender from student where name = '小明' and age = 18 and gender = '男';

13. 什么是索引下推？

1.给一个表的name和age字段建立了一个组合索引

create index na_index on user(name,age);

2.索引下推含义