Mysql索引——高效获取数据的数据结构_调用索引信息接口,获取数据

前言

本篇文章将我以前项目用到过的mysql索引知识进行了总结和片段性链接，在以后有新的内容会进
行补充更新。
1
2

B+树

B+树
1

1. B+树和B树的区别
  a. B+树节点是不保存数据的，只是一个索引作用，它的叶子节点才保存数据（主键索引保存数据，非主键索引保存的是主键值）。B树的节点是保存数据的，
  b. B+树相邻的叶子节点之间是通过链表指针连起来的，B树没有这个功能。正因为叶子节点保存了完整的数据以及有指针作为
  连接，B+树可以增加了区间访问性，提高了范围查询，而B树的范围查询相对较差
  c. B+树则需要通过索引找到叶子结点中的数据才结束（也就是说，按照三层树高，每次都要遍历完3层才结束），而B树在找到
  具体的数值以后就结束（运气好遍历第一层就结束了）
  e. B树中每个索引关键字只会出现在一个结点中，不会重复出现。而B+树可以出现多次（可重复出现）。
2. 为什么使用B+树（描述），不使用二叉树，hash表 （总分）
   mysql的索引使用了B+树的数据结构，为了在尽可能少的层级里面存储更多数据，在更少的IO次数情况下读取数据，将数据
   读取和存储变得更加高效。我们在索引系统存储的时候，首先明确就是索引的数据是要放在磁盘里面的，不可能一口气将磁
   盘中的所有数据读到内存中，因此在选择的时候需要采用分块读取的方式，而操作系统里面本身就是使用了页为单位进行操
   作的，我们可以使用页的整数倍作为某一个磁盘块进行数据的读取，我们进行数据读取的时候，需要明确，索引进行数据读
   取的时候是要有key和value值的，key表示我们指定的索引列， value表示具体的行数据，因此需要k-v形式的数据结构
   进行存储。采用k-v结构的数据结构有hash表，二叉树，B树，B+树等，那么采用 B+树的原因是：hash表是数据库中memory
   引擎使用的数据结构，需要好的hash算法，不支持范围查询，数据是无序的。二叉树存储的话如果数据量大那么树的高度就
   会变高，树的高度变高会增加我们整体io读取的次数。这是因为我们要考虑磁盘lO的影响，它相对于内存来说是很慢的，数
   据库索引是存储在磁盘上的，当数据量很大时，就不能把整个索引全部加载到内存中，只能逐一加载每一个磁盘页——对应索
   引树的节点。所以我们要减少的次数。因此我们把二叉树变为多叉树，并且要具备有序和平衡的特点。B树就产生了，B树中
   每个节点都存储数据和索引，这样一个3层的B树可以存储百万级数据，但是对于更大量的数据B树就不那么高效，因此我们
   采用B+树，在非叶子节点中只存储索引和指向子节点的指针，叶子节点存储数据，索引和双向指针。在mysql的B+树索引结
   构中，一般3层就可以支持千万数据的存储，没有固定的标准，需要通过索引字段占用的空间来进行判断，因此索引字段需要
   选择占用字节数较小的数据类型。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

page

1. 每一页空间16kb
2. 三层B+树，如果主键是int型，4字节，指针6字节
	16kb/(4+6)b=1638
	1638*1638*每页数据，能达到千万级	
3. 主键和数据形成的索引保存在一块，聚簇索引。
4. 下图中依次位页结构，多个页结构的链接，B+树的结构。
1
2
3
4
5
6

聚簇索引和非聚簇索引

1. innodb存储引擎中，数据在进行插入的时候，数据必须跟某一个索引列绑定在一起，如果表中
   有主键，那么跟主键绑定，如果没有主键，那么跟唯一键绑定，如果没有唯一键，那么选择一
   个6字节的rowid进行绑定
2. 数据跟索引放在一起的叫做聚簇索引数据跟索引分开存储的叫做非聚簇索引myisam中只有非聚
   簇索引
3. innodb中即支持聚簇索引也支持非聚簇索引——二级索引（辅助索引）
1
2
3
4
5
6

二级索引

1. 一个表可以有几个索引
  N个
2. 每一个索引都是一颗B+树，还是所有的索引公用一颗B+树
   独立，也就是说一个表中会存在多颗B+树
3. 表中的数据存储几份?
   一份
4. 数据只存储一份，其他的非聚簇索引的叶子节点中存储的是聚簇引的key值，索引innodb中也
   包含了非聚簇索引innodb中的非聚簇索引都可以称之为二级索引或者辅助索引
5. id,name.age.gender四个列id是主键,name是普通索引数据跟id绑定在一起,name对应的
   B+树的叶子节点中存储的是id此时name就叫做辅助索引或者二级索引
1
2
3
4
5
6
7
8
9
10

联合索引

1. 排序会先根据最左的排序，相同的话根据第二个字段排序，往后类似
2. 形成的B+树中的叶子节点只存储联合索引的字段和对应的主键索引字段，当使用联合索引查找
    的时候那么会根据B+树查找到相对应的id,然后回表去主键索引查找数据。
1
2
3

回表

 id, name,age,genderid是主键，name是普通索引
		select * from table where name='zhangsan';
查找过程:先根据name的值去nameB+树找到对应的叶子节点，取出id值，再根据id值去idB+树
中查找全部的结果，这个过程称之为回表。
回表的效率比较低，尽可能避免回表的产生
1
2
3
4
5

索引覆盖

1.覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被
所建的索引覆盖。
2.id, name,age,genderid是主键，name是普通索引
				select id, name from table where name='zhangsan';
查找过程:先根据name的值去name的B+树查找结果，能够直接获取到id和name，不需要去id的
B+树查到数据了，这个过程叫做索引覆盖
索引的叶子节点中包含了要查询的全部数据，叫做索引覆盖推荐使用索引覆盖
1
2
3
4
5
6
7

最左匹配原则

id, name,age, gender
id是主键，name,age是组合索引，在查询的时候必须要从左向右匹配
select * from table where name= ? and age = ?             走索引
select * from table where age = ?                        不走索引
select * from table where name=?					     走索引
select * from table where age = ? and name=?(优化器会优化)  走索引
1
2
3
4
5
6

索引下推

id, name,age, gender
id是主键,name,age是组合索引，在查询的时候必须要从左向右匹配
        select * from table where name=? and age= ?;
没有索引下推之前:先根据name的值从存储引擎中拿到符合条件的数据，然后在server中对age
进行数据过滤
有了索引下推之后:直接根据name和age从存储引擎中筛选对应的数据，返回给server，不需要
做数据过滤，
1
2
3
4
5
6
7

索引优化

索引优化的时候要注意啥?
1、索引字段要尽可能少的占用存储空间
2、在满足业务系统的需求内尽可能自增
3、索引字段尽可能不要为null
  a. 在进行count()统计某列的记录数的时候（null不会统计进来）或者查询不是某个值的
  	时候（null值不会查出来），但是空值是会进行统计到其中的。当我们做一些统计表的时候
  	如果统计的数据老是不对的时候，需要考虑值为null的字段
  b. MySQL中的NULL其实是占用空间的，空值('')是不占用空间的
  c. 判断NULL 用IS NULL 或者 is not null，在sql语句中可以用
  	  IFNULL(expr1,expr2)函数，如果想判断空字符用=''或者 <>、!=''来 进行处理。
4、选择索引的时候索引的基数要尽可能的大DV/count> =80%的字段适合创建索引
 			DV(distinct value)
5、不要给所有的字段都添加索引，并不是索引越多越好.
    a 首先数据量小的表不需要建立索引,因为小的表即使建立索引也不会有大的用处,还会增
       加额外的索引开销
	b 不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义
	c 经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率
	d 索引并不是一劳永逸的,用的时间长了需要进行整理或者重建
6、尽量避免索引失效
7、索引字段尽量不要频繁修改
    
    
实际场景：
在之前做项目的时候，我们有一个表，数据量非常大，千万级别，写了一个非常简单的sql语句，
就是一个条件过滤，但是查询的效果非常差，花费了十几秒，通过查看执行计划后也确实用到了
索引，而且查询条件非常简单，只有一个，经过分析，发现我要查询的字段有四个字段，而且这
四个字段包含了查询条件，因此，考虑在查询过程中出现了大量的回表，此时将所有的查询字段
和条件组成了组合索引，达到了优化效果，执行完成之后，只需要花费1s左右就可以完成查询。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

索引失效

1、like查询的时候左边不要加%
2、索引字段上不要添加任何的表达式操作3、索引字段在使用的时候不要出现类型的隐式转换
4、索引上不要出现函数计算
5、组合索引在进行使用的时候要遵循最左匹配原则
6、in或者or在很多情况下会导致索引失效，但是要根据实际的情况来进行判断
7、在使用组合索引的时候，如果中间的某个索引列使用了范围查询，那么会导致后续的索引失效
8. order by 的时候如果order by的是组合索引，查询的是*，那么会走全表扫描。因为如果
   要走索引，那么要回表。如果将查询的字段改为组合索引的字段，那么就会走索引。
1
2
3
4
5
6
7
8