一、使用的锁
-
MyISAM:
myisam只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。也可以通过lock table命令来锁表,这样操作主要是可以模仿事务,但是消耗非常大,一般只在实验演示中使用。 -
InnoDB :
Innodb支持事务和行级锁,是innodb的最大特色。
事务的ACID属性:atomicity,consistent,isolation,durable。
并发事务带来的几个问题:更新丢失,脏读,不可重复读,幻读。
锁的机制可参考:MySQL锁机制
二、实现上的区别
1、InnoDB支持事务,MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
2、InnoDB支持外键,而MyISAM不支持。
3、InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
myisam允许没有任何索引和主键的表存在
innodb引擎如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)
4、InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。
5、Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;5.7以后的InnoDB支持全文索引了。
6、InnoDB支持表、行级锁(默认),而MyISAM支持表级锁。
7、InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有。
8、Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引
三、sql优化的注意点
1、使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈,就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的,是否有全表扫描等;
2、查询的条件尽量使用索引字段,如某一个表有多个条件,就尽量使用复合索引查询,复合索引使用要注意字段的先后顺序。
3、多表关联尽量用join,减少子查询的使用。表的关联字段如果能用主键就用主键,也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
4、尽量使用limit进行分页批量查询,不要一次全部获取。
5、绝对避免select *的使用,尽量select具体需要的字段,减少不必要字段的查询;
6、尽量将or 转换为 union all。
7、尽量避免使用is null或is not null。
8、要注意like的使用,前模糊和全模糊不会走索引。
9、Where后的查询字段尽量减少使用函数,因为函数会造成索引失效。
10、避免使用不等于(!=),因为它不会使用索引。
11、用exists代替in,not exists代替not in,效率会更好;
12、避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
13、千万不要 ORDER BY RAND()
参考文献
1、https://mp.weixin.qq.com/s/KVY9StAhXGTy1jshHHkawQ
2、https://www.cnblogs.com/y-rong/p/8110596.html