彻底解决索引问题
什么是索引?
索引是一种数据结构,能够帮助我们快速的检索数据库中的数据。
为什么要用索引?(索引的优点)
可以大大加快 数据的检索速度(大大减少的检索的数据量), 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。 另外,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
索引有什么缺点?
- 创建索引和维护索引需要耗费许多时间,这种时间随着数据量的增加而增加
- 占用物理存储空间 ,索引需要使用物理文件存储,也会耗费一定空间
- 当对表中的数据进行增删改操作时,索引也要动态的维护,降低了数据的修改速度
索引有哪些数据结构?
常见的MySQL主要有两种结构:Hash索引和B+ Tree索引
InnoDB引擎,默认的是B+树。
Hash索引与B+树索引相比有什么优缺点?
优点:
- 因为Hash索引底层是哈希表,因此Hash索引在等值查询上是非常快的
缺点:
- 因为数据结构是哈希表的原因,数据在存储关系上是完全没有任何顺序关系的,因此Hash索引不支持顺序和范围查询(主要缺点)
- 哈希索引不支持联合索引的最左匹配规则
- 如果有大量重复键的情况下就容易出现哈希碰撞问题,哈希索引的效率会很低
B树和B+树区别
- B树的所有节点既存放 键(key) 也存放 数据(data);而B+树只有叶子节点存放 key 和 data,其他内节点只存放key。
- B树的叶子节点都是独立的;B+树的叶子节点之间有一条引用链指向与它相邻的叶子节点。
- B树的检索的过程相当于对范围内的每个节点的关键字做多分查找,可能还没有到达叶子节点,检索就结束了。而B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。
若用B-树作为索引结构图:
实际的B+树索引结构图:
聚集索引与非聚集索引
聚集索引:
即索引结构和数据一起存放的索引。主键索引(一级索引)属于聚集索引。
- 在 Mysql 中,InnoDB引擎的表的 .ibd文件就包含了该表的索引和数据,对于 InnoDB
引擎表来说,该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据。
非聚集索引:
即索引结构和数据分开存放的索引。
- 二级索引(非主键索引)属于非聚集索引。二级索引的叶子节点就存放的是主键,根据主键再回表查数据。
- 在 Mysql 中,MYISAM引擎的表的.MYI文件包含了表的索引,叶子节点存储索引和索引对应数据的地址,指向.MYD文件的数据。
注意:
innodb:只能有一个聚簇索引,可以有很多个非聚簇索引。向innodb插入数据到时候必须要包含一个索引的key值,这个索引的key值可以是主键,如果没有主键,那么就是第一个唯一的列,如果没有唯一列,那么数据库就自动生成一个rowid。
一级索引与二级索引访问速度
一级索引(主键索引)是一种聚集索引。一级索引树的叶子节点存储的就是查询的整行数据。
二级索引(非主键索引,辅助索引)是一种非聚集索引。树的叶子节点存的是主键的值,正常情况下(索引覆盖则是非正常情况)查到主键值后还需要拿着主键值,然后到一级索引树上查到对应的数据(这个过程称之为回表)。因此一级索引的访问速度更快。
回表过程图
两个B+树索引分别如上图:
(1)id为PK(主键),聚集索引,叶子节点存储 行记录;
(2)name为KEY,普通索引,叶子节点存储PK值,即id;
普通索引的查询过程是怎么样的呢?
通常情况下,需要扫描两棵索引树。
例如:select * from t where name=‘ls’;
- 首先存储引擎会先到name那棵索引树上找到 name=‘ls’ 的叶子节点,定位到主键值id=5
- 然后再拿着主键5到主键索引树(聚集索引),找到该主键5对应的行数据(5,ls,m,A)
某些特殊情况下是不需要回表,例如覆盖索引这种情况
覆盖索引
理解一:如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫 做覆盖索引。
理解二:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
理解三:覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。
举例说明:
以上图数据表为例
假设有上面一张表(数据库是MYSQL,存储引擎是Innodb),上面的ID字段是主键索引,age是普通索引。
对比下面两条SQL语句:
select id from usertest where age = 10;
select name from usertest where age = 10;
第一条SQL语句不会产生回表:普通索引存储的值是主键的值。也就是说age索引里面存储的结构是下面的情况
根据age查询id的时候,索引中的值完全可以覆盖查询结果集字段时,不会产生回表操作。
由此也可以看出第二条SQL语句会产生回表是因为查询的结果集无法通过索引中的值直接获取。需要根据age查询到的id值再回到主键索引里面再次查询,这个过程叫做回表。
EXPLAIN分析:
当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时,在EXPLAIN的Extra列可以看到“Using index”的信息
创建索引时注意点
最左匹配原则
最左匹配原则:在MySQL建立联合索引时会遵守最左匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
哪些情况会索引失效?
- 使用复合索引的时候,没有使用左侧的列查找,索引失效(不满足最左匹配原则)
- 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
- like查询是以%开头
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 在where当中,索引列参加了运算或者使用了函数,索引失效
索引下推
索引下推是数据库检索数据过程中为减少回表次数而做的优化
索引下推(Index Condition Pushdown) ICP 是Mysql5.6之后新增的功能,主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理,而不是像之前是放到Server层去做过滤。
索引条件下推优化(Index Condition Pushdown (ICP) )是MySQL5.6添加的,用于优化数据查询
一条sql语句执行会先经过MYSQL服务器
再到 存储引擎
,存储引擎
检索出数据之后再将数据返回给MYSQL服务器
,MYSQL服务器
再对数据进行筛选。
EXPLAN分析:
当使用explan进行分析时,如果使用了索引条件下推,Extra会显示Using index condition。