mysql存储引擎、事务、索引

MySQL进阶

存储引擎

什么是存储引擎

存储引擎是MysQL中特有的一个术语，其它数据库中没有。(oracle中有，但是不叫这个名字存储引擎这个名字）

实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎，表存储数据的方式不同。

常用存储引擎

MyISAM存储引擎

它管理的表具有一下特征：（使用三个文件表示每张表）

• 格式文件：存储表结构的定义（mytable.frm）
• 数据文件：存储表行的内容（mytable.MYD）
• 索引文件：存储表中的索引（mytable.MYI）

该存储引擎的优势：

• 可被转换为压缩、只读表节省空间

InnoDB存储引擎

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。InnoDB支持事务，支持数据库崩溃后自动恢复机制。

InnoDB存储引擎最主要的特点是：非常安全

它管理的表具有下列主要特征：

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tabiespace被用于存储表的内容
• 提供一组用来记录事务性活动的日志文件
• COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
• 提供全ACID兼容
• 在MysQL服务器崩溃后提供自动恢复
• 多版本(Mvcc)和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

InnoDB和MyISAM二者的区别？

• InnoDB支持事务，MyIASM不支持（InnoDB安全，MyISAM不安全）
• InnoDB不能压缩数据文件，不能节省空间
• InnoDB的效率不是很高

MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY 存储引擎非常快。

MEMORY存储引擎管理的表具有下列特征：

• 在数据库目录内，每个表均以.frm格式的文件表示。
• 表数据及索引被存储在内存中。（目的就是快，查询快! )
• 表级锁机制。
• 不能包含TEXT或BLOB 字段。
• MEMORY 存储引擎以前被称为HEAP 引擎。

优势：

• 基于内存存储数据，效率高
• 不安全，断电即失

事务

什么是事务

一个事务就是一个完整的业务逻辑

怎么理解提交事务 和回滚事务

在事务的执行过程中，每一条DML的操作都会记录到"事务性活动的日志文件"中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务：

• 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
• 提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务：

• 将之前所有的DM操作全部撤销，并且清空事务性活动的日志文件。
• 回滚事务标志着，事务的结束。并且是一种全部失败的结束。
• 注意：回滚事务意思是回滚到上个事务的提交点，且mysql数据库是默认自动提交事务的

事务特性

A：原子性（事务为最小工作单元，不可再分）

C：一致性（在一个事务中多个数据库DML操作，要么同时成功，要么同时失败）

I：隔离性（事物之间互不影响）

D：持久性（事务结束，将没有保存的数据持久化到磁盘）

事务的隔离级别

读未提交（read uncommitted）–该事务能够读取其他事务未提交的数据

• 事务A可以读取到事务B未提交的薮据
• 这种隔离级别存在的问题就是：脏读

读已提交（read committed）–该事务只能读取其他事务已提交的数据

• 事务A只能读取到事务B提交之后的数据。
• 这种隔离级别解决了脏读。
• 存在的问题就是不可重复读 ，在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。

可重复读（repeatable read）–该事务只能读取开启该事务时数据库那一时刻的数据 ，相当于“快照”《mysql默认的事务隔离级别》

• 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
• 这种隔离级别解决了不可重复读
• 存在的问题幻读

序列化/串行化（serializable）

• 事务一个一个执行

索引

什么是索引

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制
• 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引
• 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

对于一本字典来说，查找某个汉字有两种方式：

第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描，效率比较低

第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高

注意：

• 索引也是需要排序的
• 索引排序的底层原理类似treeset的平衡二叉树

提醒：

• 在任何数据库当中主键上都会自动添加索引对象 ，另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象
• 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
• 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYT文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中

索引的实现原理

什么条件下，我们会考虑给字段添加索引呢?

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同)

条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。条件3:该字段很少的DML(insert delete update)操作。因为DML之后，索引需要重新排序。

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

什么条件下，索引会失效？

• select age from user where name like '%T' name上即使添加了索引,也不会走索引，为什么?原因是因为模糊匹配当中以"%"开头了!
• 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
• 使用复合索引的时候,没有使用左侧的列（可以是多个左侧的列）查找，索引失效什么是复合索引?两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
• 在where当中索引列参加了运算,索引失效。explain select * from emp where sal+1 = 800 ;
• 在where当中索引列使用了函数explain select * from emp where lower(ename) = 'smith ';

索引分类

主键索引

唯一性索引

单一索引

组合（复合）索引