Mysql数据结构+索引

我从雅楠回来了
214 阅读5分钟

二叉树

​ 是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成。

红黑树

​ 二叉平衡树，当一边节点比另一边节点高，会自己做自平衡。不会出现单边递增的情况。数据多了，树高度会很高。

Hash表

  • 查询快，但是不支持范围查询。hash冲突概率小。数据无序。
  • 会对字段进行hash运算，直接查询到索引所在行磁盘文件地址。

B-Tree

  • 叶节点具有相同的深度，叶节点的指针为空。
  • 所有索引元素不重复。
  • 节点中的数据索引从左到右递增排列。
  • 非叶子节点存储data。

B+Tree

  • 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引。
  • 叶子节点包含所有索引字段。
  • 叶子节点用指针连接，提高区间访问的性能。
B+Tree将data放在叶子节点是因为一个节点只有16k，如果存放data数据，存储2000w的数据树的高度就会很高。树的高度越高，查询次数越多。

存储引擎形容的是数据库表（不管数据库定义何种存储引擎，最终以表为准）

MyISAM

  • 索引文件和数据文件是分离的(非聚集)B+Tree组织

  • frm文件(建表信息)，MYD文件(data数据)，MYI文件(索引信息)

    1. 先去查询字段是否是索引字段，然后去MYI文件去查询当前字段的磁盘文件地址指针，然后再去MYD文件查询数据。

  • 没有事务

Innodb

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件

  • 聚集索引-叶节点包含了完整的数据记录

  • Innodb表必须有主键，并且推荐使用整形的自增主键

    1. 如果没有建主键，mysql会自己找一列可以建唯一索引的数据做主键。如果都找不到，则会生成一个隐藏列做主键。
    2. 整形比较大小快，并且存储空间小。
    3. 自增主键：永远往节点后面插入数据。不自增，则会在不同页插数据，效率会低(分库分表业务导致除外)

  • frm文件(建表信息),ibd文件(索引和data数据)

索引

  • 聚集索引

    主键，叶节点包含了完整的数据记录（索引和数据放在一个文件存储）

    特点：查询数据特别快，因为聚集索引和行数据存储在磁盘的同一页，这样可以减少磁盘I/O操作次数

    注意：主键索引应该尽量简短

  • 非聚集索引(除了聚集索引以外的其它索引都叫非聚集索引)
    1. 叶子节点除了键值以外还包含了一个bookmark，用来告诉Innodb在哪里可以找到对应的行数据，Innodb的辅助索引的bookmark就说相对应行数据的聚集索引键。也就是先获取指向主键索引的主键，然后通过主键索引来找到一个完整的行。
    2. 不要在索引上用函数和like
    3. 非聚集索引里面存储了聚集索引，最后要通过聚集索引找到行数据。(聚集索引的效率会影像其他索引)
  • 覆盖索引

    ​ 索引包含了查询语句需要的所有数据。索引的叶子节点已经包含要查询的数据，不需要回表操作，所以很快。减少了I/O操作次数。

  • 组合索引

    ​ 把多个字段组合起来创建一个索引(最多16个字段)，遵循最左前缀匹配原则。

  • 最左前缀匹配原则
    • mysql会从左向右匹配直到遇到不能使用索引的条件(>、<、!=、not 、like模糊查询的%前缀)才停止匹配。
    • 如果a,b,c三个字段创建一个组合索引(a,b,c)。a是索引的最左前缀,所以where条件必须匹配字段a，mysql优化器才会用到这个索引。在匹配字段a的前提下，才能匹配字段b；在匹配字段a的前提下，并且匹配字段b，然后才能匹配字段c。
explain命令用来查看select语句执行计划，确认该sql语句有没有使用索引，是否做全表扫描，是否使用覆盖索引等。

type:代表数据访问类型（由左至右，由最差到最好）
| All | index | range | ref | eq_ref | const | system | null |

possible_keys: 表示那些索引可能有利于高效的查找

key:显示mysql决定采用哪个索引来优化查询

key_len:显示mysql在索引里使用的字节数

ref:显示了之前的表在key列记录的索引中查找值所用的列或常量

rows: 为了找到所需的行大致需要读取的行数

extra:表示额外的信息(左边较差，右边较好)
| Using filesort | Using temporary | Using where | Using index condition | Using index|

>>Using index:使用了覆盖索引，速度很快，限于查询字段都位于同一个索引中的场景。
>>Using index condition:表示使用了ICP优化(Index Condition Pushdown(索引下推)),能减少引擎层访问基表的次数和MySqlServer访问存储引擎的次数。
>>Using where:表示在存储引擎检索后mysql服务器再进行过滤
>>Using filesort:返回结果前需要做一次外部排序(内部或硬盘),速度慢应该尽量避免
>>Using temporary:在对查询结果排序时会使用一个临时表，速度慢
索引下推

  • ​ 先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。

    ​

    nameageid
    张三201
    张一212
    李四223

  • 假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

    SELECT * from user where name like '张%'

  • 根据 "最佳左前缀" 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。

  • 问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为张，年龄为20岁的用户，此时的sql语句如下：

    SELECT * from user where name like '张%' and age=20

  • Mysql5.6之前的版本是没有索引下推这个优化的，因此会忽略age这个字段，直接通过name进行查询，在(name,age)这颗树上查找到了多个结果id，然后拿着取到的id一次次的回表查询。

  • Mysql5.6之后Innodb没有忽略age这个字段，在索引内部就判断了age是否等于20，因此在(name,age)这颗树上查找到了一个结果id，只需回表一次。

  • 索引下推在非主键索引上的优化，可以有效减少回表的次数。

avatar
文章
阅读
粉丝