MySQL索引、事务与存储引擎

MySQL索引、事务与存储引擎

索引

索引的概念

是一个排序的列表，存储着索引值和这个值所对应的物理地址

无须对整个表进行扫描，通过物理地址就可以找到所需数据

是表中一列或者若干列值排序的方法

需要额外的磁盘空间

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率

当表很大或查询涉及到多个表时，可以成千上万倍的提高查询速度

可以降低数据库的io成本，并且还可以降低数据库的排序成本

通过创建唯一性索引来保证数据表数据的唯一性

可以加快表与表之间的连接

在使用分组和排序时，可以大大减少分组和排序时间

索引的副作用

索引需要占用额外的磁盘空间

在插入和修改数据时要花费更多的时间，因为索引也要随之变动

索引的应用场景

表的主键、外键必须要有索引

记录数超过300行的表应该有索引

经常与其他表进行连接的表，在连接字段上应该建立索引

唯一性太差的字段不适合建立索引

更新太频繁的字段不适合创建索引

经常出现在where子句中的字段，特别是大表的字段，应该建立索引

索引应该建立在选择性高的字段上

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引

1.小字段

2.唯一性强的字段

3.更新不频繁，但是查询效率很高的字段

4.表记录超过300行

5.主键、外键、唯一键

索引的分类

普通索引：针对所有字段，没有特殊的需求

唯一索引：针对唯一性的字段，仅允许出现一次空值

组合索引：多列/多字段组合形式的索引，从左往右算

全文索引：对全文进行模糊搜索

主键索引：针对唯一性字段，且不可为空，同时一张表只允许包含一个主键索引

事务

事务的概念

MySQL事务主要是用于处理操作量大，复杂度高的数据。

是一个操作序列，这些操作要么全部执行，要么都不执行，是一个不可分割的工作单位

事务的特点（ACID）

是指在可靠数据库管理系统中，事务应该具有的四个特性

原子性

事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生

事务是一个完整的操作，事务中的个元素是不可分割的，必须作为一个整体

一致性

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。当事务完成时，数据必须处于一致状态。

在事务开始前，数据库中存储的数据处于一致状态。在正在进行的事务中数据可能处于不一致的状态。当事务成功完成时，数据必须再次回到已知的一致状态。

隔离性

在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间

对数据进行修改的所有事物是彼此隔离的，表明事物是必须独立的，不应该以任何形式依赖于火影响其他事务

持久性

在事务完成之后，该事务对数据库所做的更改便持久的保存在数据库中，且不会被回滚

指不管系统是否发生故障，事务处理的结果都是永久的

一但事务被提交，事务的效果会被永久保存在数据库

事务之间的相互影响分为几种

脏读(读取未提交数据):脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据

可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最

终存在的数据，这就是脏读

不可重复读(前后多次读取，数据内容不一致):一个事务内两个相同的查询却返回了不同数

据。这是由于查询时系统中其他事务修改的提交而引起的。

幻读(前后多次读取，数据总量不一致):一个事务对一个表中的数据进行了修改，这种修改

涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中

插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好

象发生了幻觉一样。

**丢失更新:**两个事务同时读取同一条记录，A先修改记录，B也修改记录(B不知道A修改

过），B提交数据后B的修改结果覆盖了A的修改结果。

事务的隔离级别

(1) read uncommitted :读取尚未提交的数据，安全性最差但是性能最好（不使用）

(2) read committed(提交读):读取已经提交的数据，安全性较差，性能较好 是Oracle数据库默认的类型

(3) repeatable read(可重复度):重读读取:可以解决脏读和不可重复读-mysql默认的

可以重复读 安全性能较高 性能较差

(4) serializable:串行化:可以解决脏读不可重复读和虚读 安全性高，性能最差 （不使用）

事务控制语句

BEGIN或START TRANSACTIQN:显式地开启一个事务。

COMMIT 或COMMIT WORK:提交事务，并使已对数据库进行的所有修改变为永久性的。

ROLLBACK或ROLLBACK WORK:回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

SAVEPOINT S1:使用SAVEPOINT

允许在事务中创建一个回滚点，一个事务中可以有多个SAVEPOINT;"S1"代表回滚点名称。

存储引擎

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索

引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在

MysQL中称为存储引擎

存储引擎是MysQL将数据存储在文件系统中的存储方式或者存储格式

存储引擎 分类

myisam：支持静态表、动态表、压缩表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度

的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空

间通常比动态表多。

固定长度10

存储非常迅速，容器缓存，故障之后容易恢复

动态表包含可变字段(varchar)，记录不是固定长度的，这样存储的优点是占用空间较少，

但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或

myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所

以只有非常小的访问开支。

myisam适用的生产场景

适合于各方面的任务场景、同时并发量不高、对于事务要求不高的场景

死锁

myisam：表级锁定，不会出现死锁，锁定粒度大，发生锁冲突的概率高，并发度低

innodb：行级锁定，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高

当两个请求分别访问两行记录，同时有需要读取对方的记录数据，因为行锁的限制而造成阻塞

怎么解决死锁

show

查看表使用的存储引擎

方法1：show table status from 库名 where name =‘表名’\G

方法2：show create table 表名

MyISAM和 INNODB区别(差异)

• InnoDB支持事物，而MylSAM不支持事物。
• lnnoDB支持行级锁，而MylSAM支持表级锁.-
• InnoDB支持MVCC, 而MlSAM不支持。
• lnnoDB支持外键。而MyISAM不支持。
• lnnoDB全文索引，而MylSAM支持。