MySQL进阶篇之MySQL索引

今天主要学习MySQL索引，不过主要是使用Linux系统使用MySQL，主要是先在Linux环境下按照MySQL，然后演示索引的相关操作，介绍了索引的底层结构，索引的分类及语法，索引的性能分析，索引的使用规则，索引的设计原则等。

目录

1、索引概述

2、索引结构

3、索引分类

4、索引语法

5、SQL性能分析

6、索引使用

7、索引设计原则

---

Linux上安装MySQL这个参考具体的安装方法即可，安装完成并启动MyQL即可，就可以使用可视化管理工具连接远程的MySQL。

1、索引概述

无索引，全表扫描，有索引，可以快速检索。

 索引的优缺点：

2、索引结构

在不同的存储引擎中，MySQL索引有不同的结构，如下：

我们一般说的索引结构就是指的B+树的结构，三种存储引擎都支持B+树的结构，Memory支持Hash索引，MyISAM支持空间索引和全文索引。

我们先来看一下二排序叉树，可能出现在极端情况下退化为链表的问题，检索性能低，可以使用红黑树是一个近似的平衡二叉树，可以提高检索速度，但是性能还是不够。

 在看B+树之前，我们先看一下B树，B树的阶数就是指针数，每个节点最多存储(度数-1)个key，如果超出节点就要发生裂变，每次使得指针左侧的元素比根节点下，指针右侧的元素比根节点大。每次插入元素，当大于相应的阶数的时候，中间元素相上裂变。

B+树和B树不同，B+树所有节点都会出现在叶子节点，叶子节点以链表的形式链接在一起，非叶子节点只是起到索引数据的作用，每次插入元素达到相应的度数就会相上裂变，只不过会在叶子节点保留相应的裂变节点，并用指针链接起来，数据存储在叶子节点。

我们看一下MySQL中索引中的B+树结构，是在原来的B+树基础之上 增加了指向相邻叶子节点的指针，提高访问性能。

 

我们再看一下Hash索引，将key转化城hash值，映射到具体的位置上，然后存储到hash表就可以，若出现hash冲突，通过链表解决，直接该位置的链表尾部追加元素即可。

我们看一下hash索引的特点：支持等值比较，不支持范围查询，无序，一般来说检索效率高。出现hash碰撞的话，还要检索链表，效率也不一定高。

最后我们看一个面试题：为什么InnoDB引擎选用B+树索引，而不是二叉树，或者红黑树，B树呢？

3、索引分类

我们看一下索引的分类，主要包括四类：主键，唯一，常规，全文。

这也是面试常问的在InnoDB中，根据索引的存储形式可以分为聚集索引和非聚集索引，其中聚集索引将数据存储与索引放到一块，索引结构的叶子节点保存了行数据；非聚集索引将数据和索引分隔开，索引的叶子节点关联的是对应的主键。

我们结合图看一下聚集索引和非聚聚索引(二级索引),可以发现主键索引是聚集索引，数据的存储和索引放到一起，叶子节点存储的是对应的行数据，其余索引是非聚集索引，可以看到索引的数据的存储是分隔开的，叶子节点关联的是对应的主键id。

 

索引检索：回表查询，先走非聚类索引找到对应的主键值，根据主键值到聚集索引上拿到主键值对应的行数据。 

我们看几个思考题，可以看出根据id查询效率更高，直接走聚集索引对比id即可，根据name查询要先走非聚集索引找到id，再回表查询行数据。

4、索引语法

我们看一下索引的基本语法，创建索引用create index, 删除索引用 drop index,查看索引用show index，如下：

5、SQL性能分析

1）SQL执行频率，我们可以通过执行如下指令查询当前数据库的crud的访问频次，进而选取合适的SQL优化策略。

2）也可以通过开启慢查询日志定位执行效率比较低的SQL，只需要再配置文件启动开启慢查询，设置慢日志时间即可。如下： 

 3）也可以通过执行profile查看每一条SQL的耗时情况，也可以查看指定查询语句的各个阶段的查询情况，如下：

 4）使用explain可以查看各条语句的执行情况，主要关注type,优化的时候尽量使得连接类型性能好的方向优化，而避免all的情况。主键或者唯一性索引访问是const，非唯一性索引会出现ref。

6、索引使用

在使用索引之前，可以通过先验证加索引后性能是否提升，使用explain关键可以查看具体SQL居于的执行情况相关参数。

 对于复合索引的使用要满足最左前缀法则，不能缺少最左侧的列，否则索引全部失效，如果跳过索引的列，该列后的索引后面的字段索引全部失效。

 对于复合索引如果出现范围查询>或者<这种，会导致范围查询右侧索引失效。

 尽量不要在索引列上进行运算操作，会导致索引失效。

 字符串不加单引号，会导致索引失效。

使用like进行模糊查询的情况下，以%开头的模糊匹配会导致索引失效。

 使用or条件连接的情况下，只要有列没有索引，就会导致索引失效。

 如果MySQL觉得走全部扫描更快，则不走索引。

我们看一下SQL提示，是优化数据库的一个重要手段，可以指定建议使用的索引，忽略索引以及强制使用索引，如下：

我们看一下思考题，需要对select  id, username, password from 表名 where username = "张三"语句进行优化，该怎么样进行优化呢，最优的方案是根据（username,password）建立一个复合索引，因为复合索引下面的数据就是id，这样就可以避免回表查询。

使用前缀索引节约索引空间，提升索引效率，根据索引选择性 来确定索引的长度，选择性=不重复的索引值/数据表中的记录总数，索引选择性越高，则索引查询效率越高。

如果存在多个查询条件， 建议针对查询字段时建立联合索引，而非单列索引。如果是多条件的单列索引，MySQL会评估走哪个索引的查询效率高，便会选择哪一个。

 

7、索引设计原则

索引设计的七大原则：1.数据量大查询频繁建索引；2.常作为查询、分组、排序条件的建索引;3.对于区分度高的列建索引；4.字符串很长的可以使用前缀索引；5.尽量使用复合索引；6.控制索引数量；索引列不能存储控制，在建表时用非空约束。