目录
6,索引(条件)下推-ICP(index condition pushdown)
1,MySQL索引概念
概念:索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
优点:加快搜索速度,减少查询时间。
缺点:1 ,索引是以文件存储的。如果索引过多,占磁盘空间较大。而且他影响insert ,update ,delete 执行时间。
2 ,索引中数据必须与数据表数据同步:如果索引过多,当表中数据更新的时候,索引也要同步更新,这就降低了insert、update、delete的效率。
2,索引类型
1、普通索引index/key,加快查询速度
2、唯一性索引unique,索引列的所有值都只能出现一次,即必须唯一,不能有重复的值,用来约束内容。唯一性允许有NULL值。
3、主键索引primary key,设置主键就会在该列上自动创建主键索引,每个表最多只能有一个主键。主键列要求列的所有内容必须唯一。主键索引是一种特殊的唯一索引,但不允许有空值。
4、复合索引,索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引
3,索引设计原则
索引的出现,就是为了提高数据查询的效率,就像书的目录一样。
对于数据库的表而言,索引其实就是它的“目录”。
扩展:
创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
帮助引擎层避免排序和临时表
将随机 IO 变为顺序 IO,加速表和表之间的连接。
4,索引优点
- 减少磁盘扫描。提高检索效率,避免了全面扫描
- 提高排序和分组的效率
- 将随机IO转换为顺序IO
- 提高部分聚合函数的效率
5,什么是覆盖索引和索引下推
覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖,成为覆盖索引;覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
索引下推:mysql5.6引入的索引下推优化, 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
5,1什么是回表操作
如果不是主键索引的查询,,查询索引以外的字段值,索引节点并没有存储它,所有会通过索引节点存储的主键值再去查询一次。这就是回表操作
6,索引(条件)下推-ICP(index condition pushdown)
1,索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。
2,在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。
3,在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
4,索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。
5,说白了,就是把本来应该server层做的判断交给了引擎层去做。
7,聚集索引和非聚合索引
&聚合索引:数据和索引一起存放,同时又分为:
主键索引:所有数据都存放在叶子节点
非主键索引:叶子节点只存放主键
&非聚合索引:数据和索引分开存放,从索引文件查找到数据后,会拿该数据再去数据文件中进行查找,叶子节点只存放索引列数据
8,mysql索引失效有哪些场景
- where中包含or时,可能会导致索引失效,or的前后必须都是索引列
- 使用负向查询,比如,not、!=、<>、!<、!>、not in、not like等
- 使用内置函数的时候
- 关联查询时,两个字段的编码不一致
- 对索引列进行计算的时候
- like查询以%开头时,会导致索引失效
- 联合索引中,违背最左匹配原则,一定会导致索引失效
9,mysql应该基于什么条件来创建索引
在经常搜索的列上创建索引
在经常被用在连接的列上创建索引
在经常使用where子句的列上创建索引
唯一性太差的列不适合单独创建索引,即使会频繁的作为查询条件
更新非常频繁的字段不适合创建索引