MySQL数据库之索引、事务与存储引擎

一、MySQL索引

概念

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

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

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

• 需要额外的磁盘空间
  作用

• 数据库lion给各种快速定位技术，能够大大加快查询速率

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

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

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

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

• 在使用分组和排序时，可以大大减少分组和排序时间
  索引的分类
  1.普通索引
  特点：

• 最基本的索引类型，没有唯一性之类的限制

• 创建普通索引的方式
  create index index_name on table_name
  alter table table_name add index_name
  2.唯一性索引
  特点：

• 与“普通索引”基本相同

• 与普通索引的区别是索引列的所有制只能出现一次，即必须唯一

• 创建唯一索引的方式
  create unique index 索引名 on 表名
  alter table 索引名 add unique 表名
  3.组合索引（单列索引与多列索引）
  特点：

• 可以是单列上创建的索引，也可以是在多列上创建的索引

• 最左原则，从左往右依次执行

• 创建组合索引的方式
  create table user（name varchar（9），age int（3），index user（name，age））
  4.全文索引
  特点：

• MySQL从3.23.23版开始支持全文索引和全文检索

• 索引类型为FULLTEXT

• 可以在char、varchar或者text类型的列上创建

  例如：我们有一个文章表（article），其中有主键ID（id）、文章标题（title）、文章内容（content）三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：
  CREATE TABLE article (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(200) DEFAULT NULL,
  content text,
  PRIMARY KEY (id),
  FULLTEXT KEY title (title,content)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  上面就是在创建表的同时创建全文索引的SQL示例。此外，如果我们要给已经存在的表的指定字段创建全文索引，同样以article表为例，我们可以使用如下SQL语句进行创建：
  ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);
  在MySql中创建全文索引之后，现在就该了解如何使用了。众所周知，在数据库中进行模糊查询是使用like关键字进行查询的，例如：
  SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;
  创建索引的原则依据

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

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

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

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

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

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

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

• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
  查看索引的方法
  查看索引语法：
  show index from table_name;
  show keys from table_name;
  查看索引的示例：
  mysql>show index from player;
  mysql>show keys from player;
  删除索引的方法
  删除索引语法：
  drop index index_name on table_name;
  alter table table_name drop index_name;
  删除索引的示例：
  mysql>drop index index_puid on mapping;
  mysql>alter table mapping drop index gameid;

二、事务

概念

• 是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

• 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元

• 适用于多用户同时操作的数据库系统的场景，如银行、保险公司等等

• 通过事务的整体性以保证数据的一致性
  事务的ACID原则
  1.原子性

• 事务是一个完整的操作，事务的各元素是不可分的

• 事务中的所有元素必须作为一个整体提交或回滚

• 如果事务中的任何元素失败，则整个事务将失败

2.一致性

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

3.隔离性

• 对数据进行修改的所有并发事务时彼此隔离的，表明事务必须是独立的，他不应以任何方式依赖于或影响其他事务
• 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

4.持久性

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

• 一旦事务被提交，事务的效果会被永久地保留在数据库中
  事务控制语句
  MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交。
  事务控制语句：

• begin或start transaction:显式地开启一个事务

• commit:也可以使用commit work，不过二者是等价的。commit会提交事务，并使已对数据库进行的所有修改变为永久性的

• rollback：又可以使用rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• savepoint identifier：savepoint允许在事务中创建一个保存点，一个事务中可以有多个savepoint；

• release savepoint identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• rollback to identifier：把事务回滚到标记点

• set transaction：用来设置事务的隔离级别。lnnoDB存储引擎提供事务的隔离级别有read uncommitted、read committed、repeatable read 和serializable。
  MySQL事务处理主要有两种方法：
  （1）用begin、rollback、commit来实现
  begin 开始一个事务
  rollback 事务回滚
  commit 事务确认
  （2）直接用set来改变MySQL的自动提交模式
  set autocommit=0 禁止自动提交
  set autocommit=1 开启自动提交

三、存储引擎

存储引擎概念：

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

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

• MySQL常用的存储引擎：MyISAM、lnnoDB

• MySQL数据库中的组件，负责执行实际的数据I/O操作 （I/O=in out 读写（吞吐量））

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

• MylSAM不支持事务，也不支持外键

• 访问速度快

• 对事务完整性没有要求

• MylSAM在磁盘上存储成三个文件：

.fm文件存储表定义
数据文件的扩展名为.MYD(MYData)
索引文件的扩展名是.MYI(MYIndex)

• 表级锁定形式，数据在更新是锁定整个表
• 数据库在读写过程中互相阻塞
• 数据单独写入或读取，速度过程较快且占用资源相对少
• MyIAM支持的存储格式：

静态表
动态表
压缩表

InnoDB特点

• 支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 速写阻塞与事务隔离级别相关
• 能非常高效的缓存索引和数据
• 表与主键以簇的方式存储 （这边簇，意思就相当于格式化磁盘里的一个单元格）
• 支持分区、表空间、类似oracle数据库
• 支持外键约束，5.5之前不支持全文索引，之后支持
• 对硬件资源要求还是比较高的场合
  修改存储引擎

方法一： alter table 修改
alter table table_name engine=引擎；
方法二：修改my.cnf ,指定默认存储引擎并重启服务
default-storage-engine=lnnoDB
方法三：create table 创建表时指定存储引擎
create table 表名 （字段）engine=引擎
方法四：Mysql_convert_table_format转化存储引擎
Mysql_convert_table_format -user=root -password=密码-sock=/tmp/mysql.sock -engine=引擎 库名 表名