索引概述
索引是我们数据库中的一种数据结构,主要作用提高查询效率;
创建索引的列,经常作为查询条件出现,提高查询效率
索引和约束的区别
注:我们在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(查询)的写法来达到选择合适索引的目的