MySql索引

索引概述

索引是我们数据库中的一种数据结构，主要作用提高查询效率；
创建索引的列，经常作为查询条件出现，提高查询效率

索引和约束的区别

注：我们在mysql的优化时，时常会混淆索引和约束这两个概念，下面对这两个概念做具体的解释

约束更像是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

什么是数据完整性

概念：很容易理解，就如字面意思，我们需要保证我们存入的数据满足特定的要求。而如果是文件系统的话，我们一般通过程序端进行控制；但关系型数据库（MySQL数据库）本身能够保证数据完整性。

数据完整性有以下三种形式：
实体完整性保证表中有一个主键。可以通过Primary Key或Unique Key约束，也可以编写一个触发器。
域完整性保证数据每列的值满足特定条件。通过几种途径来保证：选择合适的数据类型；外键约束；触发器；DEAFAULT约束。
参照完整性保证两张表之间的关系。通过外键或者触发器。

分类

主键索引（PRIMARY KEY） :唯一索引，只能有一个值;不能包含null
唯一索引（UNIQUE）:唯一索引,只能有一个值,可以包含一个null;
常规索引（INDEX）:可以出现重复的值
全文索引（FULLTEXT）:使用较少

索引的准则

索引不是越多越好
-不要对经常变动的数据加索引
-小数据量的表建议不要加索引
-索引一般应加在查找条件的字段

什么时候推荐使用索引

在WHERE，ORDER BY 子句中经常使用的字段
字段的值是多个（例如性别字段则不适合）
字段内容不是经常变化的
经常变化的字段，添加索引反而降低性能
不宜过多添加索引,每添加一条索引都会占用磁盘空间
索引表:专门存储索引

什么时候不推荐使用索引

索引实现的原理

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据；目前主流的数据库引擎的所有都是B+树实现的，B+树的搜索效率，可以达到二分法的性能；B+树节点里存储的是索引（数据地址），在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得B+树高度降低，减少了磁盘IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用来链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。

InnoDB和MyISAM区别

InnoDB

InnoDB引擎的底层实现（采用聚集索引方式），所谓聚集索引方式，就是将索引和数据存储在同一个文件里，首先InnoDB会根据主键ID作为key建立索引的B+树，而B+树的叶子节点存储的是主键ID对应的数据,此种方式也是目前主流的方式

MyISAM

MyISAM引擎底层实现（非聚集索引方式），所谓非聚集索引方式，就是将数据和索引存储在两个不同的文件上。MyISAM在建表是，以主键作为key来建立主索引B+树，树的叶子节点存的是对应数据的物理地址。我们在拿到这个物理地址后，就可以找到MyISAM数据文件中直接定位到具体的数据记录了

索引优化

索引失效的原因

1、全职匹配我最爱
2、最佳左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列）口诀：带头大哥不能死，中间兄弟不能断
3、不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
4、存储引擎不能使用索引中范围条件右边的列（口诀：范围之后全失效）

索引优化的一般性建议

1、对于单键索引，尽量选择针对当前query（查询）过滤性更好的索引
2、在选择组合索引的时候，当前Query（查询）中过滤性最好的字段在索引字段中，位置越靠前越好
3、在选择组合索引的时候，尽量选择可以能够包含当前query（查询）中的where子句中更多字段的索引
4、尽可能通过分析统计信息和调整query（查询）的写法来达到选择合适索引的目的