MySQL的存储引擎、事务、索引以及优化

MySQL的存储引擎，MyISAM和InnoDB

1、MySQL默认存储引擎的变迁

在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。

2、MyISAM与InnoDB存储引擎的主要特点
注意：非聚集索引和聚集索引的区别，非聚集索引需要去两个文件文件联合查询数据（MYI和MYD），聚集索引只需要在一个文件中查找数据（IDB）；

• MyISAM存储引擎索引文件和数据文件是分离的（非聚集）

存储引擎是myisam, 在data目录下会看到3类文件：.frm、.myi、.myd

（1）*.frm–表定义，是描述表结构的文件。

（2）*.MYD–"D"数据信息文件，是表的数据文件。

（3）*.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树

MyISAM存储引擎的特点是：表锁、不支持事务和全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是使用大并发、重负荷生产系统上，表锁结构的特性就显得力不从心；

例子：select * from where Col1 = 30;

看Col1 是否是索引字段，如果是首先执行的是MYI(表索引文件)文件，拿它去根节点查找位置，从根节点锁定范围后再从子节点查找位置（也可能就存在根节点的位置），在把查到的节点位置元素取出它的data值，data值存的是元素的磁盘文件地址，根据磁盘文件地址就可以快速查到到该元素在磁盘中的数据，其首先执行的是MYI文件，再执行MYD(表的数据文件)文件。

• InnoDB存储引擎索引实现（聚集）

  表数据文件本身就是按B+Tree组织的一个索引结构文件；

  聚集索引-叶节点包含了完整的数据记录；

  为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？（mysql在开发InnoDB表底层存储引擎的时候，这张表的数据必须要有B+树的聚集索引来组织数据，必须要有主键来组织整个表数据；用整型占用空间小，其次是整型好比较大小，假如用UUID非自增(字符型)不好比较大小；根据B+树结构自增的话每次插入都会从右边增加，不自增如从中间插入后，还需要去平衡B+树结构）

  为什么非主键索引结构子节点存储的是主键值？（一致性和节省存储空间）

存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd

（1）*.frm–表结构的文件;

（2）*.ibd–表数据文件;

InnoDB存储引擎的特点是：行锁、支持事务操作、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

注意：

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “a%”。

两种类型最主要的差别就是InnoDB支持事务处理与外键和行级锁。而MyISAM不支持。

3、MyISAM与InnoDB性能测试

随着CPU核数的增加，InnoDB的吞吐量反而越好，而MyISAM，其吞吐量几乎没有什么变化，显然，MyISAM的表锁定机制降低了读和写的吞吐量。

4、事务支持与否

MyISAM是一种非事务性的引擎，使得MyISAM引擎的MySQL可以提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用；

InnoDB是事务安全的；

事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5、MyISAM与InnoDB表锁和行锁的解释

MySQL表锁：它会锁定整张表，如果当前有用户正在执行写操作并且获取了写锁，这可能导致整张表被锁定，阻塞其他用户的读写操作。如果用户执行的是读操作，则会获取读锁，此时其他用户的并发读操作将被接受，写操作会被阻塞。

InnoDB行锁：通过给索引加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。

共享锁（Table Read Lock）和排它锁（Table Write Lock）。也就是说对表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对表的写操作，则会阻塞其他用户对同一表的读和写操作。

区别：表锁的优点在于开销小、加锁快、无死锁，缺点是锁的颗粒度大，发生锁冲突的概率较高，并发能力较弱，行锁则相反。

6、是否保存数据库表中表的具体行数

InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。

7、如何选择

• MyISAM适合场景：

  （1）做很多count 的计算；

  （2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；

  （3）没有事务。

• InnoDB适合场景：

  （1）可靠性要求比较高，或者要求事务；

  （2）如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；

• 区别：

  （1）清空整个表是，或者DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；MyISAM则会重建表。

  （2）对于自增长的字段（如：主键自增），InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

注意：创建每个表格的代码是相同的，除了最后的 TYPE参数，这一参数用来指定数据引擎。

MySQL事务

1、什么事事务？

事务：是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）；

2、事务的简单操作

显式启动事务语句，begin或者start transaction;

提交commit;

回滚rollback;

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

3、事务的四大特性

• 原子性：

  一个事务（transaction）中的所有操作，要么全部成功，要么全部不成功，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：

  事务完成前和完成后，对数据库来说状态是没有改变的。一致性要在隔离状态下才能保证。

• 隔离性：

  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即该事务提交前对其他事物都不可见。通过锁或者MVCC实现，MVCC（多版本并发控制）在可重复读的位置举例介绍。

• 持久性：

  事务一旦被提交了，对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

4、事务日志

• 重做日志（redo log）：

  每次操作都在内存中操作，并写入重做日志中记录。一旦操作完成则表示事务提交。一段时间后，根据日志内容将操作写入硬盘。

• 撤销日志（undo log）：

保留每一次操作前的状态。以便恢复。

• 日志组：

  日志也是文件，文件中只记录操作，不记录对应的数据；多个日志文件构成日志组；日志文件是轮流使用。一个记录满以后自动记录到下个文件中。记录满的日志文件会将操作同步到硬盘。然后清空日志。

5、事务的隔离等级

mysql默认的隔离等级是可重复读，如果想要在mysql启动时就修改mysql的隔离等级，需要修改配置文件，在[mysqld]中添加如下内容：

[mysqld]
transaction-isolation = READ-COMMITTED

#查看当前事务的隔离级别
mysql>select @@tx_isolation\G;

• 脏读：

  事务A读取了事务B更新、未提交的数据，然后B回滚操作，那么A读取到的数据是脏数据(没有用的数据)。

• 幻读：

  同一事务中，两次按相同条件查询到的记录不一样。造成幻读的原因在于事务处理没有结束时，其他事务对同一数据集合增加或删除了记录。在mysql中MVCC在一定程度上解决了幻读。

• 不可重复读：

  事务 B 在事务A多次读取的过程中，对数据作了更新操作并提交，导致事务A两次读取同一数据不一致。主要针对数据更新的。

注意：不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

MySQL的索引

1、索引本质：索引是帮助mysql高效获取数据的排好序的数据结构；

2、索引的类型

​ 普通索引(INDEX)：最基本的索引，没有任何限制

​ 唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

​ 主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。

​ 全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

​ 组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

2、索引数据结构:

• 二叉树（右边的数据大于左边的数据）

• 红黑树
  

• Hash表
  
  执行过程：对索引元素的值做一次hash运算得到一个散列值，根据散列值定位到这一行的元素，理解为散列值就是磁盘地址；

  反列：select * from t where t.Col2 > 89; 如该条SQL，hash索引对于范围查找是没有用的，原因是数据没有排序，在散乱的数据的去查找范围还是要进行一次全表扫描；极少的情况用hash索引。

• B-Tree（增加横向节点的数量）key-value键值对

  叶节点具有相同的深度，叶节点的指针为空；

  所有索引元素不重复；

  节点中的数据索引从左到右递增排序；
  

• B+Tree（B-Tree变种）

  非叶子节点不存储data，只存储索引（冗余），可放更多索引；

  叶子节点包含所有索引字段；

  叶子节点用指针连接（双向指针），提高区间访问的性能，从左到右依次递增；
  
  
  区别：在相同高度的情况下，B+树的数据存储量远大于B树的数据存储量；

​ B树叶节点没有指针，每次查找都要从头结点开始，B+树叶节点是双向指针，并且从左到右逐渐递增，也可以很好的用于范围查找；

注意：MySQL数据库不用二叉树、红黑树的原因是数据量过大的时候，树的高度过高，节点过多查询次数增加；

MySQL优化

1、选取合适的字段属性

​ 数据库中表越小查询越快，选取合适的字段长度；尽可能的把字段设置为not null，在查询时就减少null的比较。

2、使用连接（JOIN）来代替子查询是（sub-Queries）

3、使用联合（union）来代替手动创建的临时表

4、使用索引

5、使用外键

​ 锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候就可以使用外键。

6、优化SQL

​ 在建有索引的字段上尽量不要使用函数进行操作；

​ 查询时尽量避免全表扫描；

··············································································