为什么要学习索引优化？

对于CPU而言：计算内存中的数据是非常快的

内存中处理数据的处理耗时几乎可以忽略不计

Mapper查询数据库操作、数据库查询操作会很耗时吗？

想数据库insert数据所需时间会受哪些因素影响？

连接：30% 向服务器发送查询：20% 解析查询：20% 插入行：10% * 行的大小 插入索引：10% * 索引数 结束：10% 可发现时间耗费最多的在客户端与服务端通信的时间，因此可以使用insert包含多个值来减少客户端和服务器之间的通信。即批量插入

网络请求或IO操作是非常耗时的操作，我们要尽量避免在循环中调用网络请求或进行IO操作，会非常消耗性能。

一个认知：一次正常的请求，最可能出现性能瓶颈的地方就是网络请求及IO操作（通常而言性能瓶颈往往出现在数据库）

优化数据查询的方向： 优化关系数据库本身，比如增加索引 借助大数据和ES，把在数据库查询压力转嫁（本质已经和关系型数据库无关了）

对于第二点，引入大数据和ES对公司的成本要求很高，所以数据查询优化时SQL优化非常重要，和SQL性能提升相比，代码层面的优化有时时非常微不足道的（在内存里） 即使有优化，归根结底还是减少、较小对数据库的请求。

总结：一个正常的请求，最可能出现性能瓶颈的地方就是网络请求及IO操作，而通常而言性能瓶颈往往出现在数据库。而数据库方面优化，归根结底还是减少、减小对数据库的请求。

Java开发，SQL优化分为几个层次：

索引优化 70% 事务及锁 20% 读写分离锁 10% 可以看出索引优化是最重要一种SQL优化的手段

索引的类型

Navicat中索引的类型

全文索引 普通索引 空间索引 唯一索引 普通索引就可以组织树的结构了，而唯一索引是在普通索引的基础上约束了索引列不能重复，主键索引是在唯一索引的基础上约束了索引列不能为空

全文索引（考虑Elastic Search），空间索引使用很少

所以实际开发中常用的索引：普通索引、唯一索引（特殊的唯一索引）

索引的实现方式 实现索引的实现方法：B+树、hash算法

对于索引的实现我们可以采取B+树的结构组织索引，也可以使用hash算法

hash索引

概念： 所谓hash索引，其实就是利用哈希算法为索引值计算得到唯一的存储地址，一般来说，这个地址是不会重复的。（重复的情况被成为hash冲突）

hash算法的特点：

不论输入的数据量有多大，输入同一个哈希算法，得到的加密算法结果固定 哈希算法确定，输入数据确定，输出数据保持不变 哈希算法确当，输入数据有变化，输出数据会改变 哈希算法不可逆

hash算法处理即使存在相关性的数据，经过hash算法映射后结果会变得毫不相关，所以在处理范围查询、模糊查询等会很难实现，无法利用边界值进行范围查询，每个数据处理后会变得没有相关性

hash索引除了不能进行范围查找外，还不能进行模糊查询

hash算法本身代表着精准定位，根据入参计算出唯一的存储地址，所以无法进行模糊匹配，使用hash索引查找XXX%，代表这个XXX%通过hash算法计算出唯一的值，而不是XXX开头的数据

但B+树可以进行模糊搜索，顺着树查找时，装有数据的节点内调用类似java中startWith的方法进行比较

hash索引的优劣势

优势：精准查询非常快，只需要一次计算即可得到地址，时间复杂度O(1)，而B+树O(logn) 劣势：不支持模糊查询、范围查询、索引排序（本身时不规则的，无法利用利用索引排序）

索引的创建

索引创建时机：

起初，建表时顺便建立索引 后期，修改表结构创建索引（一般是这样，根据后续业务做相应优化）

后期建立索引的方式

SQL语句 Navicat图形界面 ALTER TABLE moneywithdraw ADD INDEX idx_auditor_id (auditor_id);

数据量很大的表不要随便加索引，懂索引，不要乱动索引

索引的好与坏

索引的优势： 加快查询速度（包括关联查询） 加快排序速度（Order by B+树叶子节点有序链表） 加快分组速度（Group by） 索引的劣势： 创建索引是需要付出代价的，主要体现在索引维护成本、空间成本和回表成本。也就是说索引能提高查询效率，但往往会降低增删该的速度（字典新增几百个字，需要额外编排目录（维护），多占几页纸（空间）） 如果使用了联合索引，还需要考虑索引失效的问题 太多的索引会增加查询最优优化器的选择时间（选择太多也麻烦）

建索引的原则

在合适的时间，合适的字段建立索引，创建索引的判断依据是什么？ 创建索引的4大原则： 索引并不是越多越好，联合索引应该优于多个单列索引（索引维护、空间、选择查询优化器时间） 索引应该建立在区分度高的字段上（通过某个字段可以缩小很大的查询范围） 尽量给查询频繁的字段创建索引，避免为修改频繁的字段创建索引 避免重复索引（联合索引中包含了单列索引的字段） 回表：一般查找的辅助索引，非主键索引，而所有的数据在存在于主键索引的叶子节点里，而辅助索引的叶子节点存储的为结果的主键值，而不是直接返回实际数据，需要通过主键索引再查询一次获取到真实的数据。

第一个原则的原因是，实际数据库一次查询只会选择最有的一颗索引树（不包括回表）查询，更专业的说话是每次查询只会选择一个执行计划。当存在很多索引时，数据库也只会选择最优的索引。而且没建一个索引，就需要维护一棵索引树。增加了维护成本

第二个原则：区分度很高的原则，比如根据性别加索引的化，那么根据性别只能过滤一半数据，剩下的结果集仍然很大，说明这个索引建的不太合适，区分度太低了。

第三个原则：比如一本编排好的字典编好目录以后，如果以后目录修改很小，以后查找数据就很方便，但如果经常需要修改汉字读音，新增词汇等目录就会经常重新编排，为了使目录能正确的定位到对应的汉字，每次增删改都需要多一个操作，重新修订目录，增加索引的维护成本。

第四个原则，再物理存储上单列索引和联合索引是两个独立的B+树，重复的索引会增加维护成本，当联合索引包含单列索引时，可以保证符合条件的时候利用到a索引。

Mysql常用引擎

Mysql和InnoDB比较

索引组织上的区别，存储数据的方式

MYISAM每张表在存储时会分为3个文件（非聚簇索引）： 表结构 表数据 索引 索引和数据是独立存在的

而InnoDB表数据在存储时，分为两个文件（聚簇索引）： 表结果 表数据 + 索引 InnoDB所有表的数据和索引在同一个文件中（所有表的数据存在于主键索引中）

聚簇索引与非聚簇索引

对于BTREE索引而言，从数据的组织形式，索引可以分为两大类： 聚簇索引 非聚簇索引（索引和数据没有聚合在一起） 聚簇索引（索引和数据）聚合在一起

对于InnoDB引擎的主键索引查询时无需回表，每一行数据都直接挂在叶子节点下，可以直接返回，也就是说，对于InnoDB主键索引而言，数据即索引，索引即数据

InnoDB索引也并不是都不需要回表，根据是否需要回表索引又可以分为两类：主键索引、辅助索引（二级索引、普通索引）

为什么要做这种区分呢？

如果每个索引都维护着数据，数据不仅会重复，而且还需要考虑不同索引树下数据的一致性。

所以InnoDB的做法是，主键索引的叶子节点挂了数据，而辅助索引只存储索引列-主键形式，必要时需要回表操作

当查询辅助索引时不能取得所需字段的值时此时就会进行回表操作 MyISAM和InnoDB索引分类 MyISAM：非聚簇索引，需要回表 InnoDB 聚簇索引：主键索引，叶子节点是表数据，不需要回表 非聚簇索引：辅助索引（唯一索引、普通索引），叶子节点是主键，必要时需要回表

所以InnoDB引擎主键索引只有一个，表数据只有一份，辅助索引可以有多个

SQL优化的本质其实就是在于减少、减小磁盘IO，而回表会增加磁盘IO次数，所以SQL优化时应考虑尽量避免回表。

所以通常情况下辅助索引查询都是需要回表的，比主键索引多扫描一棵索引树（自身+主键索引），实际编写SQL时，应该尽量走主键索引

什么情况下可以避免回表呢？

索引覆盖 索引覆盖的最大作用：避免回表 我们使用辅助索引时，在索引树上我们可以获取到索引列+主键 当辅助索引查询时，有些字段值未覆盖时，这时只能跳出当前索引树，去主键索引中获取到未覆盖的字段，这个过程被称为回表，而回表意味着多跑一趟

当辅助索引上的字段完全满足本次查询的列时，就是所谓的覆盖索引，这样就不需要回表了 SQL优化原则中经常会强调：尽量支取必要对的字段，避免SELECT * （提高覆盖索引覆盖的记录，查询的字段愈多，覆盖记录越低） 即使目前表中只有两个字段且已经索引覆盖，也不要写SELECT *，随着业务扩展，新增了字段时，SELECT * 将不再覆盖索引 索引覆盖，当索引的字段 >= 查询需要的字段 就是索引覆盖。最大的好处就是避免回表 能否索引覆盖不取决于索引单方面，需要查询配合

本文正在参与「掘金小册免费学啦！」活动, 点击查看活动详情