MySQL索引与优化

1.什么是索引
在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

​ 简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
2.索引的分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

二级索引又被叫做非聚集索引或者辅助索引。

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或者没有合适的唯一索引，则InnoDB会自动生成一个rowID作为隐藏的聚集索引。

也就是说无论如何这个聚集索引一定是存在的。

回表查询

先走二级索引，找到对相应的主键值，然后根据主键到聚集索引找到这一行的行数据。
3.索引的优势
1）提高数据检索效率，降低磁盘IO成本
2）通过对数据的排序，降低排序成本，降低cpu的消耗
4.索引的劣势
1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系，索引列也是占空间的。
2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。
5.什么时候适合创建索引
1）主键自动建立唯一索引
2）频繁作为查询条件的字段（where后面的字段）
3）查询中与其他表关联的字段（各种join on后面的字段）
4）单值/复合索引选择？（高并发下倾向选择复合索引）
5）查询中排序的字段
6）查询中统计或分组的字段
6. 什么情况下不适合建立索引
1）表数据太少
2）频繁更新的字段
3）where后面用不到的字段
7.索引结构
MySQL的索引是在存储引擎层实现的，不同的储存引擎有不同的结构，主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询，效率很高
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES。用的也很少

8.为什么InnoDB存储引擎选择使用B+Tree索引结构？
相对于二叉树，层级更少，搜索效率更高

对于B-Tree，无论是叶子节点还是非叶子结点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

相对于Hash索引，B+Tree支持范围匹配以及排序操作

9.索引的使用
最左前缀法则
如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

也就是说，在查询的时候，比如有三个列作为联合索引，查询的时候条件三个都用是可以的，或者前两个也可以使用到该索引，但是如果不使用最前面的（最左边的）就不会使用到这个联合索引，如果使用了最前面（最左边的）条件，然后是用了最后的条件，没有使用中间那列的条件的话，会使用索引，但只有最左边的索引使用，造成部分索引失效。

范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
在业务允许的情况下，使用>=可以避免索引失效。
原因是：B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

索引列运算
不要在索引列上进行运算操作，索引将失效

字符串不加引号
字符串类型的字段使用时，不加引号，索引将失效
隐式转换–>函数操作

模糊查询

如果仅仅是尾部模糊查询匹配，索引不会失败。如果是头部模糊匹配，索引失效。
也就是比如说：

select * from 表 where user_name like '王%';

他的索引就不会失效，但是如果是：

select * from 表 where user_name like '%小明';

索引就会失效。
前后都加%也会失效。
在大数据量的情况下要规避前面加’%‘的模糊查询，这样会使得索引失效，效率大大降低。

or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
只有两侧都有索引的时候，索引才会生效。
所以想要解决这个问题，可以给没有索引的条件建立一个索引，那么索引就会生效。

数据分布影响

如果MySQL评价使用索引比全表更慢，就不使用索引。
比如使用>=某个条件，得到的结果是表中的大多数数据，那么走全表比索引更快，就不使用索引了。如果得到的结果比较少，比较精确，那么索引比全表快，那就使用索引。

覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *。

单列索引与联合索引
单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引而非单列索引。
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

10.优化
尽量在日常的使用中避免上面那些会让索引失效的情况出现，基本上就有了一个比较初级的优化思想，如何优化还得对数据库进行一些详细的了解与深入的研究。