知识点：mysql数据库概念总结

1.MyISAM

在MySQL5.1及之前的版本中，MyISAM是默认的存储引擎。

作为mysql最早的存储引擎之一，有比较沉重的历史包袱。

MyISAM支持全文索引、压缩和空间函数等，但是不支持事务和行级锁，崩溃后无法安全恢复。

MyISAM并发时会对整张表加锁，读取时将要读的表加共享锁，写入时也对相关的表加排他锁。

适合：频繁查询，count计算

 

2.InnoDB

InnoDB是mysql如今的默认引擎，与MyISAM对比，它支持事务和行级锁，具有高效的缓存特性，能够缓存索引和数据。

支持分区，表空间，支持外键约束。

5.5版本之后开始支持全文索引。

适合：频繁的查询更新，大量并发操作，需要事务支持。

 

3.MyISAM和InnoDB对比

1).存储结构：MyISAM在磁盘上存储三个文件，文件存储表、数据文件、索引文件（.frm、.MYD、.MYI）。InnoDB表由共享表空间、日志文件组、表结构定义文件组成。表的大小受限于操作系统文件的大小。

2).事务：MyISAM不支持事务，InnoDB支持事务处理和外键。

3).锁：MyISAM只支持表锁，InnoDB支持表锁和行锁。InnoDB如果需要扫描全表，也会执行表锁。

4).记录数：MyISAM执行count行数获取记录条数的时候，直接读取，速度较快。

5).索引：MyISAM和InnoDB的索引实现都是B+树，不同的是MyISAM索引文件和数据文件分开，MyISAM的索引叶节点数据域存放的是数据记录的地址，而InnoDB分两种，聚集索引叶节点data域存放的是整行数据，非聚集索引叶节点data域存放的是主键指针。

 

4.InnoDB逻辑存储结构

InnoDB存储引擎中，表都是按照主键顺序组织存放的,所有数据存放在一个空间中，成为表空间。表空间由段、区、页构成。

表空间

表空间是Innodb存储引擎逻辑的最高层，所有的数据都存放在表空间中，默认情况下，Innodb存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间中内。如果启用了innodb_file_per_table参数，则每张表内的数据可以单独放到一个表空间内，但请注意，只有数据、索引、和插入缓冲Bitmap放入单独表内，其他数据，比如回滚(undo)信息、插入缓冲检索页、系统事物信息，二次写缓冲等还是放在原来的共享表内的。

段

从上图中可以看出表空间由段组成，常见的段有数据段、索引段、回滚段等。因为InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。数据段即为B+树的叶子结点，索引段即为B+树的非索引结点。在InnoDB存储引擎中对段的管理都是由引擎自身所完成，DBA不能也没必要对其进行控制。

区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。默认情况下，InnoDB存储引擎页的大小为16KB，一个区中一共64个连续的区。

页(块)

页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。从InnoDB1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K，8K，16K。若设置完成，则所有表中页的大小都固定，不可以对其再次修改。除非通过mysqldump导入和导出操作来产生新的库。
InnoDB存储引擎中，常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页等。

 

5.索引

InnoDB的索引中有聚集索引和非聚集索引，一个表只能有一个聚集索引。一般来说，在生成表的时候，会默认选择主键建立聚集索引，整张表的数据结构根据主键来建立。

---

按照类型可以分为普通索引、唯一索引、主键索引、组合索引。

普通索引：CREATE INDEX 没有限制

唯一索引：CREATE UNIQUE INDEX 唯一约束，允许有空值

主键索引：PRIMARY KEY 特殊的唯一索引，不允空值

组合索引：选择其中几列建立索引。

---

按照实现方式可以分为B+Tree 索引、哈希索引、全文索引、空间数据索引（R-Tree）。

B+Tree 索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。InnoDB 的 B+Tree 索引分为主索引和辅助索引，也就是聚集索引和非聚集索引。B+Tree索引是利用B+树构建的索引结构。

B+ 树是一种树数据结构，是一个n叉树，每个节点通常有多个孩子，一颗B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。 
B+ 树通常用于数据库和操作系统的文件系统中。 
NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。 
B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。 

哈希索引

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找；

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引（R-Tree）

MyISAM 存储引擎支持空间数据索引，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

 

6.锁

按照锁类型分类：排他锁（又称为写锁，x锁）、共享锁（又称为读锁，s锁）。

 

排他锁

在insert、update、delete这些操作的时候，数据库会加上排他锁，这时候其他的事务都不能再加上任何类型的锁，直到排他锁释放。在InnoDB中，select默认不加锁，因此插入的过程中，其他事务可以读。当然这个也涉及到事务的隔离级别。

共享锁

如果事务给数据加上共享锁，别的事务也可以加共享锁，但不能加排他锁，意味着别的事务只能读，不能修改数据。

---

按照锁的粒度分类：行级锁、表级锁、页级锁。

行级锁

粒度最小，开销大，发生锁冲突的概率最低，并发度最高。可能发生死锁。

表级锁

粒度最大，开销小，发生锁冲突的几率最大，并发度最低。不会出现死锁。

页级锁

开销粒度都介于行级和表级之间，可能发生死锁。

 

7.事务

事务的特征：原子性、一致性、隔离性、持久性。

原子性：事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性：事务执行前后，数据库从一个一致性状态转变到另一个一致性状态。

隔离性：不同事务之间的操作相互隔离，不受其他正在执行尚未提交的事务中的操作影响。

持久性：事务一旦提交，数据永久性的改变，数据的改变将持久化到硬盘等设备中。

关于事务的隔离，有四大隔离级别

1.Read uncommitted

一个事务可以读取另一个未提交事务的数据。也就是说某个事务正在修改数据，尚未提交的时候，其他事务就能读到。

这就造成了脏读问题。

2.Read committed

提交读，一个事务要等另一个事务提交后才能读取数据。解决了脏读问题。

可能有不可重复读问题：一个事务中前后两次读取的过程中，另一个事务对数据进行了修改，导致前后两次读取的数据不一致。

3.Repeatable read

可重复读，在事务开启的时候，不允许其他事务修改。解决了脏读、不可重复读问题。

可能有幻读问题：事务在对涉及到一张表的读取操作时，另一个事务插入了一条新数据，该事务下一个操作就可能发生冲突。

幻读和不可重复读的区别，不可重复读强调的是修改，幻读强调的是插入或者删除，也就是说，前者主要聚焦于某行数据或者满足某个条件的部分，幻读主要看的是所有可能被涉及到的数据。

4.Serializable

序列化，最高等级的隔离，解决了以上的脏读、不可重复读、幻读问题。

但是同时也会带来效率问题和性能问题。