MySQL_索引_MyISAM和InnoDB区别

1介绍

        索引：是一种可以帮助进行高效获取数据的数据结构

        (一个排好序的便于快速查找数据的数据结构)

2.3二叉树 

 2.4红黑树

注:以上都因为高度太高(因为只有二叉)不适合

 2.5Hash表

 精确:找余数,遍历就行

在MySQL中,有一种索引叫做Hash索引,底层采用Hash这种数据结构当做索引;作为Mysql用户一般不建议使用

 

B树

•  精确查找(查找对应数据,查找范围)
• --精确查找很方便
• 范围查找
• --依然要层级查找,虽然比全表扫描较快,但是还是不够方便

问题一：B树相对于平街二叉树或者红黑树，最大的优势在什么地方？
        对于B树而言，如果节点内存储的素引数量越多，那么即便B树的高度只有三层成者四层，也可以存储千万条以上的数据。（高度低，数据量大）
        一股情况下，如果B树的高度是3，那么就需要进行三次查询，也就是需要经过三次磁盘IO，查询的限速步骤主要在于磁盘io；（IO次数少，限制条件是磁盘IO速度）
问题二：如果格千万条数据都放在同一个节点内，不是只需要一次磁盘IO就可以找到对应的数据了吗？
为什么不采用这样的方式呢？

首先要清楚磁盘和内存是怎样交互的。磁盘的结构如图：

 

 因此就算把一个阶的值设置的很大，磁盘一次也只能读取4k或者其整数倍

换句话说其实就是在b+树之中每一个节点的大小是固定的；

2.6 B+树

B+树在B树之上做了优化

        1.叶子节点之间维护了一个指针,指向下一个节点(方便范围查询)

        2.所有非叶子节点都会在叶子节点中冗余一份(方便快速定位)

--两个优化解决了B树范围查找慢的缺点

•  精确查找:很方便
• 范围查找:改善之后也很方便 1 
• 3.非叶子节点不存储data，只存储key
  • 只存储key 的好处是为了可以让这个树的高度变得更低
• 4.所有的叶子节点存储一份完整的key信息以及data 信息

 3.索引的实现

在mysql中不同索引的实现于不同的存储引擎有关

3.1MyISAM

在MyISAM中索引是非聚集索引，索引文件与数据文件是分开的。

每一个MyISAM存储引擎都有三个文件

        .frm文件，表结构定义文件

        .myi 索引文件 （index）

        .myd 数据文件（data）

主键索引

        主键索引是指以主键的值来建立索引树

        key-主键的值

        data 地址值

非主键索引

         非主键索引是指以指定索引列的值来建立索引树

        key-对应列值

        data-地址值

3.3 INNODB

主键索引(聚集索引),索引与数据在一起

和非主键索引(非聚集索引,聚簇索引)索引下面是主键

        主键索引

         非主键索引

 索引值下面对应的是主键值

        .frm结构定义文件

        .ibd数据文件与索引文件

MyISAM和InnoDB的区别

• innodb支持事务,myisam不支持事务;innodb会把每一条sql语句自动封装成事务,自动提交;myisam则没有这个操作,从效率上看,myisam比较快,,但是innoDB比较安全;场景①:只有查询需求的表格:历史记录子类的可以使用MyIsam;②其余需要修改的表格建议使用innodb
• innoDB支持外键,MyiSAM不支持外键
• innoDB的主键索引是聚集索引,而MyISAM是非聚集索引:
• innoDB不保存表的行数,MyISAM会保存表的行数
• innoDB支持表锁和行锁,而MyISAM只支持表锁

 表锁的颗粒度更粗,效率更低;行锁的颗粒度更细,效率高一些

• InnoDB表必须有一个主键,由一位InnoDB表的数据是存放在主键索引之中的,如果没有主键,呢么数据就没有办法存放;默认情况下,在没有设置主键的情况下,存储引擎会设置一个隐藏的列当做主键,而MyISAM可以没有主键,因为都是指向地址值(数据文件与索引文件是分开的),即使没有索引数据文件依然可以存储;

 creat table t_name(

id int primary key auto_increment,

age int ,

name varchar(20),

index name_index(name) using btree

)engine=innodb character set utf8

增加索引: alter table dbname add index index_age(age) using btree

删除索引:alter table person drop index index_name

查询索引:show index from table_name

5.问题汇总

为什么innodb表必须有主键?(非主键指向主键索引)

myIsam的表能不能没有主键?(非聚集索引,应该可以吧,索引指向地址值就行)

innodb表的索引是不是越多越好?(不是,如果索引过多,不懂)

不是，索引在查询的时候可以增加效率，但是在进行增删改的时候都需要维护索引树，会导致变慢。所以一般索引在五个以下比较平衡。

1.索引采用的数据结构是什么？为什么采用这种结构？

B+（B之上修改：叶子节点链表，非叶子冗余一个数据）

        非叶子只有key 可以降低高度

2.数据库为什么要定义主键，而且在Mysql中推荐主键自增

        以为默认存储引擎是innodb，并且存储是依赖于主键索引树的，I

        自增的策略：在插入数据的时候对树的结构影响较小，插入的性能较高；

3.InnoDB和MyISAM区别

        1.事务✔

        2.外键

        3.行锁表锁，前者都支持，后者只有表锁✔

        4.聚集索引于非聚集索引✔

        5.存储文件数（.frm .idb ||.frm .myi .myd）✔

        6.前者不会保存表的行数，后者会

只有查询业务时可以考虑使用MyISAM✔

4.什么是回表?如何避免回表？

总结一下innodb:支持主键索引,但是在查询的时候没有使用主键,此时就会以最左第一个字段作为非主键索引查询,非主键索引下存储的是主键,找到主键之后,再去进行主键索引的查找,就查找了两次,效率很低;

如何避免回表

• 尽量避免写select * ，只写需要的列
• 尽量走主键索引
• 可以合理建立联合索引来解决回表问题
  • 联合索引：建立多个索引（这样辅助索引也有data值）

 相当于原来是name作为key,现在是name和age都作为key,

 

 5.索引是不是越多越好

从查询角度来说,当然是索引越多越好,但是在进行增删改的时候需要维护的辅助索引树就会变多;

一般来说索引是有规范的,数量在5个作用

6.索引一般简历在什么样的字段上比较合适

如果重复字段过多(status:状态),会影响B+树的查询效率