MySQL数据引擎及索引介绍

MySQL数据引擎及索引介绍

数据引擎

在MySQL中，常用的数据引擎有MyISAM和InnoDB。两种类型最主要的差别就是Innodb 支持事务处理、外键和行级锁。而MyISAM不支持。所以MyISAM往往就容易被人认为只适合在小项目中使用。这里所说的行级锁是指最小粒度的锁，也支持表级锁。MyISAM支持表级锁，不支持事务、行级锁和外键。MyISAM强调的是性能，其执行性能比InnoDB要好，在少并发的环境下，查找(下面会讲原因)、插入(由于要维护数据库的完整性)等要比InnoDB要好。建议在对事务有要求或者高并发的情况下，用InnoDB引擎，否则用MyISAM。

下面是一些具体的细节差异：

1.      InnoDB不支持FULLTEXT类型的索引。

2.      InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table, InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

3.       对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

4.       DELETEFROM table时，InnoDB不会重新建立表，而是一行一行的删除。

5.       MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

为什么MyISAM引擎查找比InnoDB引擎要快？

InnoDB使用的是聚簇索引，InnoDB数据引擎本身就是索引文件。将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

　　MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

　　为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

 

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁

(4)即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

索引：

主索引。

普通索引index。这种索引是基于B+树建立的，对于不同的数据引擎B+的结构不同，可以建立单列的索引，也可以建立多列的索引。其中索引遵循从左到右匹配的规则。

唯一索引unique_index。MySQL的唯一性约束就是基于这种索引完成的。

全文索引fulltext。这种索引主要是基于字符串的划分等。可以代替like查询，但是速度很慢，尽量少用。

数据库索引介绍

1、  索引顺序文件

以顺序文件的物理存储结构来存储的索引。即[键值,数据记录指针]对以数组的形式有序储存。

为什么数据库一般不使用顺序索引：

①     当数据库进行查找的时候，使用二分法进行查找。假设数据库索引文件的磁盘块有b个，那么需要读取log2n才能找到索引项。此时如果数据库顺序文件中含有溢出块的话，需要顺序读取多个磁盘块，查询效率明显降低。

②     随着文件的增大，索引查询性能和顺序扫描的性能都会下降。主要是文件必须按照顺序文件组织。在插入新纪录时，索引文件和数据文件都有可能进行调整。顺序索引一般是集聚索引，因为非集聚索引无法建立稀疏索引，从而导致无法建立多级索引。如果是集聚索引话，那么还需要进行数据文件的排序操作。如果通过溢出块处理，那么查找的性能会下降，如第一条分析所示。还有一种办法便是对文件进行重组。

2、 B-tree/B+tree索引

B-tree和B+tree的差别在于，B-tree的节点存储数据，而B+tree的数据存储在叶子节点中。

B+tree的每个节点为一个数据块。在查询的时候，读取一个数据库，一个数据块里可能有多个索引字段，所以在建立主键的时候，尽量选择size比较小的字段，这样一个数据块就可以存储比较多的数据项。减少I/O次数，提高查询效率。对于Innodb引擎而言，尽量选择自增字段为主键，这样可以减少插入或者更新时索引更新的复杂度。

3、  哈希索引

和哈希表类似，通过索引字段和一个hash函数映射到磁盘上。

MySQL优化：

现在知道的有三个方面：

(1)  选择的数据引擎上。如果不需要事务的支持，强烈建议用MyISAM数据引擎，查询、插入、删除都快些。

(2)  另一方面是从SQL语句上提高。建立索引后，尽量少用子查询in、exist、is NULL、like、!=、不要对where语句后面的字段使用函数等，这些操作可能会导致索引不可用而全表扫描；不要用select *返回没用的字段、先where查询再join等。

(3)  数据缓存memcached，没怎么接触过。