MySQL索引、事务与存储引擎

MySQL索引、事务与存储引擎

1. 索引

1.1 索引的概念

1.1.1 数据库索引

• 是一个排序的列表，存储着索弓|值和这个值所对应的物理地址
• 无须对整个表进行扫描，通过物理地址就可以找到所需数据
• 是表中一-列或者若干列值排序的方法
• 需要额外的磁 盘空间

1.1.2 索引的作用

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
• 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
• 可以降低数据库的I0成本，并且索引还可以降低数据库的排序成本。
• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
• 可以加快表与表之间的连接。
• 在使用分组和排序时，可大大减少分组和排序的时间。
• 建立索引在搜索和恢复数据库中的数据时能显著提高性能

索引的副作用:

• 索引需要占用额外的磁盘空间。
  对于MyISAM引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而InnoDB 引擎的表数据文件本身就是索引文件。
• 在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

1.1.3 创建索引的原则依据3.创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引
查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

• 表的主键、外键必须有索引。因为主键具有唯- -性， 外键关联的是主表的主键，查询时可以快速定位。
• 记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。
• 经常与其他表进行连接的表，在连接字段.上应该建立索引。
• l唯–性太差的字段不适合建立索引。
• 更新太频繁地字段不适合创建索引。
• 经常出现在where 子句中的字段，特别是大表的字段，应该建立索引。
• 在经常进行GROUP BY、ORDER BY的字段上建立索引;
• 索引应该建在选择性高的字段.上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

1.1.4 索引的分类和创建

(1)普通索引:最基本的索引类型，没有唯- -性之类的限制。

• 直接创建索引
  CREATE INDEX 索引名ON表名(列名[ (length)]);
  #(列名(length)): length是可选项， 下同。如果忽略length 的值，则使用整个列的值作为索引。如果指定，使用列的前length
  个字符来创建索引，这样有利于减小索引文件的大小。在不损失精确性的情况下，长度越短越好。
  #索引名建议以“_ index" 结尾。.
  create index name_ index on member (name) ;
• 修改表方式创建
  ALTER TABLE 表名 ADD INDEX 索引名(列名);
• 创建表的时候指定索引
  CREATE TABLE 表名(字段1数据类型,字段2数据类型[,…], INDEX索引名(列名))。

(2)唯一索引:与普通索引类似，但区别是唯–索引列的每个值都唯一–。唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建，则列
值的组合必须唯一-。添加唯一键将自动创建唯一索引。

• 直接创建唯一 索引:
  CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
  create unique index cardid_ index on member (cardid) ;
• 修改表方式创建
  ALTER TABLE 表名ADD UNIQUE 索引名(列名);
• 创建表的时候指定
  CREATE TABLE 表名 (字段1 数据类型, 字段2 数据类型[,…], UNIQUE 索引名 (列名))。
• 创建表的时候指定
  CREATE TABLE 表名 (字段1 数据类型, 字段2 数据类型[…], UNIQUE 索引名(列名));

(3)主键索引:是一种特殊的唯一索引， 必须指定为“PRIMARY KEY"。一个表只能有一个主键，不允许有空值。添 加主键将自动创建主键索引。

• 创建表的时候指定
  CREATE TABLE 表名([…],PRIMARY KEY (列名));
• 修改表方式创建
  ALTER TABLE表名ADD PRIMARY KEY (列名);
  alter table member add primary key (id) ;

(4)组合索引 (单列索引与多列索引) :可以是单列上创建的索引，也可以是在多列.上创建的索引。需要满足最左原则，因为select语句的

where 条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名(列名1 数据类型,列名2数据类型,列名3数据类型，INDEX 索引名 (列名1,列名2,列名3));

2. MySQL事务

2.1 事务的概念

事务的概念：

• 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一一个整体一.起向系统提交或撤销操作请求，即这
  组数据库命令要么都执行，要么都不执行。
• 事务是一一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
• 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
• 事务通过事务的整体性以保证数据的一致性。
• 事务能够提高在向表中更新和插入信息期间的可靠性。

说白了，所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是- -个不可分割的工作单位。

2.2 事物的特点

事务的ACID特点
ACID，是指在可靠数据库管理系统(DBMS) 中，事务(transaction)应该具有的四个特性:原子性(Atomicity) 、一致性 ( Consistency)、隔离性(Isolation)、持久性( Durability)。这是可靠数据库所应具备的几个特性。

• 原子性:指事务是一-个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
  事务是一个完整的操作，事务的各元素是不可分的。
  事务中的所有元素必须作为一个整体提交或回滚。
  如果事务中的任何元素失败，则整个事务将失败。

• 一致性:指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
  当事务完成时，数据必须处于一-致状态。
  在事务开始前，数据库中存储的数据处于–致状态。
  在正在进行的事务中，数据可能处于不一致的状态。
  当事务成功完成时，数据必须再次回到已知的一致状态。
  案例:
  对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

• 隔离性:指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
  对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
  修改数据的事务可在另–个使用相同数据的事务开始之前访问这些数据，或者在另–个使用相同数据的事务结束之后访问这些数据。
  也就是说并发访问数据库时，
  一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

//当多个客户端并发地访问同-一个表时，可能出现下面的一 致性问题:

• (1)脏读:当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外- -个事务也访问这个数据
  然后使用了这个数据。
• (2)不可重复读:指在一个事务内，多次读同- -数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第- -个事务
  中的两次读数据之间，由于第二个事务的修改，那么第–个事务两次读到的的数据可能是不一样的。这样就发生了在一-个事务内两次读到
  的数据是不一样的，因此称为是不可重复读。( 即不能读到相同的数据内容)
• (3)幻读: - - 个事务对一一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另-一个事务也修改这个表中的数据，这种
  修改是向表中插入一 行新数据。那么，操作前- -个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一 样。
• (4)丢失更新:两个事务同时读取同一条记录，A先修改记录，B也修改记录(B不知道A修改过)，B提交数据后B的修改结果覆盖了A的修
  改结果。

//事务的隔离级别决定了事务之间可见的级别。
MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务:

• (1)未提交读(Read Uncommitted (RU) ):
  允许脏读，即允许一个事务可以看到其他事务未提交的修改。
• (2)提交读(Read Committed (RC) ):
  允许一-个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。防止脏读。
• (3)可重复读(RepeatedRead(RR)):—mysql默认的隔离级别
  确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。可以防止脏读和不可重复读
• (4)串行读(Serializable): —相当于锁表
  完全串行化的读，将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁，读写相互都会阻塞。可以防止脏读，不可重复读取和幻读，(事务串行化)会降低数据库的效率。

mysql默认的事务处理级别是repeatable read ，而Oracle和SQL Server是read committed

• 持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
  指不管系统是否发生故障，事务处理的结果都是永久的。
  旦事务被提交，事务的效果会被永久地保留在数据库中。

总结:在事务管理中，原子性是基础，隔离性是手段，.一致性是目的，持久性是结果。

3. 存储

3.1 存储引擎概念

• MySQL中的数据用各种不同的技术存储在文件中，每
  一种技术都使用不同的存储机制、索引技巧、锁定水平,
  并最终提供不同的功能和能力，这些不同的技术以及配
  套的功能在MySQL .中称为存储引擎

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

• MySQL常用的存储引擎
  ●MyISAM
  ●InnoDB

• MySQL数据库中的组件，负责执行实际的数据/O操作

• MySQL系统中，存储引擎处于文件系统之，上，在数据
  保存到数据文件之前会传输到存储引擎，之,后按照各个
  存储引擎的存储格式进行存储