InnoDB的主键索引 和 辅助索引

主键索引

image.png

B+Tree索引

所有叶子节点高度相同

叶子节点有双向链表指向,上/下一个叶子节点

每一个entry保存了整行数据,表即索引

TID用于事物控制(锁)

RP用于MVCC(rollback pointer)

InnoDB clustered index 规则

  1. 主键

  2. 第一个不包含null列的唯一索引

  3. 内置的rowid

辅助索引

image.png

image.png

MyISAM主键索引

image.png

上面是索引键值,下面是索引对应行记录

MyISAM索引用的B+tree来存储数据,索引指针指向的是键值的地址(ROW ID),地址存储的是数据,如图MyISAM的主键,叶子节点,指向rowid

再看下MyISAM的辅助索引

image.png

MyISAM的主键的索引结构和辅助索引的索引结构从本质上来说没有任何的区别

逻辑上讲 主键索引必须有唯一性 辅助索引可能有唯一性也可能没有唯一性 

image.png

MyISAM索引结构,不管主键索引还是辅助索引,都是如此

外键/约束(FIREUGN KEY Constraints)

外键作用:保证数据一致性和完整性,控制两表之间的关联性外键可以使得,外键关联的表对应列可以是随之关联的删除或者设置成空值。

外键:外表的主键,在我们这个表里用的是普通索引,在外面的表用到的主键,这样才能关联起来。

外表对应关联的列必须是主键

例:有a b两个表

a表中存有客户号,客户名称

b表中存储每个客户的订单

a是主表,b是外表

有了外键后,只能在确信在b表中没有客户x的订单后,才可以在a表中删除客户x,避免客户都被删掉了,订单还存在,导致数据不一致。

外键太麻烦,带来索引效率会降低,冲突会多些。

建立外键的前提:本表的列必须与外键类型相同(外键必须是外表主键)

指定主键关键字:foreign key(列名)

引用外键关键字:referebces<外键表名<外键列名

时间触发限制:on delete 和 on update 可设参数 cascade(跟随外键改动),restrict(限制外表中的外键改动),set NULL设空值,set Default(设默认值),【默认】no action

image.png

image.png

image.png

image.png

image.png

最左索引/部分索引 (prefix indexex)

部分索引的原因有:

char/varchar太长全部做索引的话,效率太差

或者blob/text类型不能整列做索引列,因此需要使用前缀索引

例:alert table t1 add index(name(7));//name列上最左边创建7个字符长度

MySQL前缀索引能有效减小索引文件大小,提高索引的速度。

但是前缀索引也有他的坏处:MySQL不能再ORDER BY 或 GROUP BY 中使用前缀索引,也不能把他们用作覆盖索引

#全列选择性

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

#测试某一长度前缀的选择性

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

覆盖索引 covering indexes

覆盖索引又可以称为索引覆盖。

  • 解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。

  • 解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。

  • 解释三:是非聚集组合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)。

索引覆盖举例

  • 索引覆盖是指建索引的字段正好是覆盖查询条件中所涉及的字段,这里需要注意的是,必须是从第一个开始覆盖,比如:

索引字段 条件字段 有没有覆盖
a,b,c a,b 覆盖了
a,b,c b,c 没有被覆盖

第一行满足,第二行不满足

  • 例子: select<字段A,B….> from <数据表 T> where <条件字段C>。在MySQL中建立覆盖索引采用Create index idx on T(C,A,B),建立组合索引时,字段的顺序很重要,要将条件字段C放在组合索引的第一位,把它做为在索引的上层结构的主要排序对象,且仅有它包含统计数据,也就是非子叶层查找出符合的记录,然后在存放有其他字段记录的子叶层读取所需要的数据。

小结

  • 索引覆盖可以大大提高查询速度,在大数据量的时候尤其明显。

联合索引 Combined Indexes,Multiple-Column Indexes

多个列组成一个共同索引,例如 2个列同时作为检索的条件,或者 某一列作为检索条件 另一列用来做排序,这种情况适合创建联合索引 

这样在索引树里就能实现索引的筛选,过滤, 或者左边的部分用来做检索 右边的部分用来做排序 这样效率会比较高 , 而不是创建2个普通的独立索引

建议:把过滤性较好的字段放在前面

MySQL目前还不支持联合索引中的多列使用不同顺序,不能同时使用一种顺序

可以用pt-duplicate-key-checker工具来检查哪些重复索引,一般就是用联合索引取代普通独立索引

对比参考ORACLE索引类型

逻辑上:

  • Single column 单列索引

  • Concatenated 多列索引

  • Unique 唯一索引

  • NonUnique 非唯一索引

  • Function-based 函数索引

  • Domain 域索引

物理上:

  • Partitioned分区索引

  • NonPartitioned非分区索引

  • B-tree:

    • Normal 正常型B树

    • Rever Key 反转型B树

    • Bitmap 位图索引

索引结构:

B-tree:

  • 适合与大量的增,删,改(OLTP)

  • 不能用于包含OR操作符的查询

  • 适合高基数的列(唯一值多)

  • 典型的树状结构

  • 每个节点都是数据块

  • 大多数都是物理上一层,两层或三层不定,逻辑上三层,mysql一般都是3层除非数据量非常大 才会变成四层

  • 叶子块数据是排序的,从左向右递增

  • 在分支块和根块中放的是索引的范围

Bitmap:

  • 适合决策支持系统

  • 做UPDATE代价非常高

  • 非常适合OR操作符的查询

  • 基数比较少的时候才能建位图索引

树形结构:

  • 索引头:

    • 开始ROWID,结束ROWID(先列出索引的最大范围)       

  • BITMAP

    • 每一个BIT对应着一个ROWID,它的值是1还是0,如果是1,表示着BIT对应的ROWID有值

对比一下MySQL的索引基本知识都有哪些

逻辑上:

  • Single column indexes单列索引

  • Combined Indexes, Multiple-Column Indexes多列索引

  • Unique 唯一索引

  • NonUnique非唯一索引

索引结构:

B-Tree:

  • 适合大量的增,删,改(OLTP)

  • 不能用包含OR操作符的查询

  • 适合高基数的列(唯一值多)

  • 典型的树状结构

  • 每个节点都是数据块

  • 大多数都是物理上一层,两层或三层不定,逻辑上三层,mysql一般都是3层除非数据量非常大 才会变成四层

  • 叶子块数据是排序的,从左向右递增

  • 在分支块和根块中放的是索引的范围

hash:

  • 适合小规模数据集快速定位

  • 适合管理内存结构体,LRU链表等

  • 不支持范围查询

  • 不支持模糊搜索

  • 不支持排序

tokudb 可以支持多个聚集索引

MySQL 聚集索引建议

MySQL 尤其是Innodb引擎,是直接把显示定义的主键选为聚集索引的依据, 其他的索引都是非聚集索引

所有的innodb表都要有个聚集索引 为了让检索更高效 锁等待的概率减小 快速定位到某一行 锁的粒度很小 对于数据库并发也很有帮助

根据聚集索引条件 不管是精确定位 还是范围检索 效果都很高 可以直接找到行数据的范围

order by 和 group by的时候只要找到第一条 就可以往下面直接走

因为聚集索引是按照顺序的方式,而且还有整个行的数据,不会导致大量的额外物理IO的产生

我们不应该找一个频繁被修改的列作为聚集索引

频繁删除修改有可能会导致大量空闲空间,碎片产生

PRIMARY KEY, 主键索引

主键的特点

  • 一个表中只能有一个主键,如果在其他字段上建立主键,则原来的主键就会取消

  • 主键的值不可重复,也不能为空(NULL)

在InnoDB中,clustered index选择原则:

  • 显示声明的主键

  • 第一个不包含null列的唯一索引

  • 内置的rowid

InnoDB中,主键特点:

  • 如果索引最后没有主键,则会在索引内部存储时加上主键(只有主键key值,而不是整个row data)

  • 如果索引最后已有主键,则会显式加上主键

  • 因此只要用户定义的索引字段中包含了主键中的字段,那么这个字段就不会再被InnoDB自动加到索引中了,如果用户的索引字段中没有完全包含主键字段,InnoDB就会把剩下的主键字段加到索引末尾