MyISAM和InnoDB在索引上的差别及其它区别

 

  首先我们知道MyISM和InnoDB索引都是由B+树实现的,但在索引管理数据方式上却有所不同。

  InnoDB是聚集索引,数据文件是和(主键)索引绑在一起的,即索引 + 数据 = 整个表数据文件,通过主键索引到整个记录,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,因为辅助索引是以建索引的字段为关键字索引到主键,所以需要两次,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。话不多说上图:

  主键索引:以主键索引到整条记录

  

  辅助索引:以另一字段索引到主键

 

        MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说:InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

  主键索引:以关键字索引到记录的地址

  辅助索引:以某字段索引到记录地址

  

  从索引实现方面我们也可以看出来InnoDB表数据文件本身就是索引文件,他们是一个整体,而对于MyISAM来说数据文件和索引文件则是分开的。

  

锁方面:

  mysql支持三种锁定级别,行级、页级、表级;

  MyISAM支持表级锁定,提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)

  InnoDB支持行级锁,但值得注意的是InnoDB的行锁是加到索引上的,所以在某次查找时没有用上索引,InnoDB表同样会锁全表。

事务方面:

  InnoDB具有事务,支持4个事务隔离级别,回滚,崩溃修复能力和多版本并发的事务安全,包括ACID。如果应用中需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,以事务为单位操作可以提高多用户并发操作的性能。
  MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。如果应用中需要执行大量的SELECT查询,那么MyISAM是更好的选择

并发

  MyISAM读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读。

  InnoDB 读写阻塞与事务隔离级别相关。

 其他

  InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;

 

不使用到索引的情况:

  用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到,例如:select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上没有索引,则该查询也不会走索引

  复合索引,如果索引列不是复合索引的第一部分,则不使用索引(即不符合最左前缀),例如,复合索引为(key1,key2),则查询select * from table_name where key2='b';将不会使用索引

  如果like是以‘%’开始的,则该列上的索引不会被使用。例如select * from table_name where key1 like '%a';该查询即使key1上存在索引,也不会被使用

  如果列为字符串,则where条件中必须将字符常量值加引号,否则即使该列上存在索引,也不会被使用。例如,select * from table_name where key1=1;如果key1列保存的是字符串,即使key1上有索引,也不会被使用。

   where语句中使用 <>和 !=。

 

posted @ 2019-08-11 23:27  码出地球  阅读(1763)  评论(0编辑  收藏  举报