项目开发中,MySQL要注意的问题

在 MYSQL 5.6以后,MyISAM 与 InnoDB 的修改和查询的速度差距已经不大了,然而在新版本的 MySQL 中,我们也不推荐使用 MyISAM,更常用的是 InnoDB 和 TokuDB。因此本文内容仅针对老版本 MySQL 而言。

MyISAM

在 MyISAM 中,每个表单独保存为三个文件,因而在备份的时候非常方便,可以针对每个表进行有选择性的备份。

MyISAM 执行速度更快,但不提供事务支持,其对读操作的支持良好。

MyISAM 保存了表的具体行数,因此在执行count()时非常快。

MyISAM 支持表锁,且在执行语句之前,会自动给所有涉及的表加锁(读锁或写锁),因而 MyISAM 不会出现死锁,但支持 FULLTEXT 全文索引。

MySQL 认为写操作比读操作重要,因而如果大量的更新操作请求写锁时,查询操作无法请求到读锁,这也是为什么 MyISAM 表不适合做大量更新操作。

InnoDB

InnoDB 中所有表保存在同一个文件,当数据表或者内容比较多时,采用复制文件来备份是件很痛苦的事。

InnoDB 相比于 MyISAM 的优势在于其支持事务以及外键等, 对改、删操作的支持比MyISAM好。

当 where 的范围有效时,触发行锁,无效时触发表锁(where user like '%a%'触发表锁)。

InnoDB 只能通过 Sphinx 获得全文索引,且与 MyISAM 相比,InnoDB 需要更大的内存和空间。

索引

数据库的索引基本是由 B- Tree 或者 B+ Tree 实现的。

索引虽然可以提高查询速度,但也会导致数据库更新数据的性能下降,这是因为数据更新的同时也需要更新索引,同时也消耗更多的存储空间。

索引的类型分为聚集索引和非聚集索引,聚集索引在结点中存储了数据,而不再有另外的数据页,且数据是按照索引的顺序来存储的。

非聚集索引中,数据存储顺序与索引顺序无关,结点中包含指向数据行的指针。

与非聚集索引相比,聚集索引通常可以提供更快的访问速度。

联合索引

联合索引又叫复合索引,那么为什么我们需要联合索引而不是用单列索引呢?

  1. 建立(a, b, c)联合索引,等同于建立了(a)(a, b)(a, b, c)三个索引,节省了写操作带来的开销和存储介质的开销;
  2. where a = 1 and b = 2,单列索引在查询时会先筛选出符合a = 1的数据,再从中使用b = 2来筛选。而如果是联合索引,则会直接筛选出对应的数据,其中的差距一眼可见。
  3. 覆盖索引。在查询过程中,MySQL 直接使用索引返回查询的字段,而不用根据索引再度读取数据文件。

当并发访问同一个资源时,有可能导致结果不一致,此时就需要一种机制来保证访问顺序化,锁就是其中的一种机制。

锁的划分方式有很多种,一般我们按照粒度和使用方式来分类,

按照锁的粒度来分类,分为:表级锁、行级锁、页级锁;

按照锁的使用方式来分类,分为:乐观锁、悲观锁;

当然上述的锁还分为共享锁和排他锁,至于其他的,由于我不常接触,此处不论述。

在 MySQL 中,InnoDB、MyISAM、BDB 引擎分别对应的是行级锁、表级锁和页级锁。

行级锁在 MySQL 中是锁定粒度最小的,只对当前行加锁,大大减少了各个操作之间的冲突,但其加锁开销最大。MySQL 中的行级锁,并不是锁记录,而是锁相应的索引。

表级锁是 MySQL 中锁定粒度最大的,其实现简单、消耗资源少。

页级锁是 MySQL 中折中的一种解决方案,其锁定速度、加锁开销等都是折中,其锁定的是相邻的一组记录。

如何避免死锁

其实避免死锁的方法有很多种,这里只提及最常用的几种:

  1. 最有效的方式是升级锁的粒度,将行级锁升级为表级锁,大大降低死锁的概率;
  2. 如果不同的业务需要访问相同的表,尽量约定按照相同的顺序访问,避免死锁;

共有 1 条评论

  1. 加气块设备

    博主小站很专业

Top