一.什么是索引?
排好序的快速查找数据结构就是索引。
优势:提高了检索效率,降低了数据库IO成本,通过索引对数据进行排序,降低了CPU消耗。
劣势:大大的增加了查询效率,同时也降低了更新表的速度,每次更新删除插入都要更新索引文件
为什么使用主键自增?
①.如果我们定义主键,那么Innodb会选择主键作为聚集索引,如果没有定义主键,则Innodb会选择第一个不包含有NULL的唯一索引作为主按键索引。
②数据记录本身被存于主索引(一颗B+树)的叶子节点上,这时候就要求各条数据记录按住键顺序存放,因此每条记录插入,mysql都会根据主键插入适当的节点和位置,如果页面达到了16kb,则会开辟一个新的页(节点)。这时候要是用非自增主键,每次都会插入到索引页的某个位置,此时MYSQL 会移动数据,还有可能会被会写到磁盘,然后在读取回来。
二.非聚集索引和聚集索引 (结合MyISAM和InnoDB来说)
1.mysql储存引擎常用的myIsam和innoDB
MyISAM 索引实现?
1)主键索引:
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
由图可见:myIsam :data存的就是数据地址,索引是索引,数据是数据,索引放在XX.MYI文件中,数据放在XX.MYD文件中;
2)辅助索引(Secondarykey)
在MyISAM里面,主键索引和辅助索引在机构上没有任何区别,只不过主键索引key唯一,辅助索引key不唯一。
MyISAM的索引方式也叫做“非聚集索引”的。
2、InnoDB索引实现?
1)主键索引?
InnoDB也使用了B+Tree 作为索引结构,实现方式与MyISAM不一样。
InnoDB表数据文件本身就是按B+tree组织的一个索引结构,这棵树的叶子结点date域保存了完整的数据记录,这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
由图可见;InnoDB主键的叶子结点data域包含了数据(数据文件),这种聚集索引
2)InnoDB的辅助索引
InnoDB所有的辅助索引都引用主键作为data域、如图所示
InnoBD表示基于聚集索引创建的,因此InnoDB的索引能提供一种都非常快速的主键查找性能,不过,因此辅助索引(非主键索引)也会包含多个列。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索 两遍索引 :首先 检索辅助索引获得主键,然后 用主键到主索引中检索获得记录。
2.mysql索引结构:B树和B+树 区别?
B树: 每个节点都存储了key和data,所有节点组成了一棵树,并且叶子结点指针为NULL,叶子节点不包含任何关键字信息。
B+树:所有叶子节点存储了关键词,叶子节点本身就是顺序双向链表,B树非终点节点包含了需要查找的有效信息。
三、总结,InnoDB索引和MyISAM索引的区别:
二者区别:
InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
MyISAM是非事务安全的,InnoDB是事务安全的
MyISAM是表锁的,InnoDB是行锁的
MyISAM是支持全文类型索引,InnoDB不支持全文索引