看完就懂——MySQL索引、事务与存储引擎

前言

• MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
• MySQL 事务主要用于处理操作量大，复杂度高的数据。
• 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法

一、索引（index）

1.1 索引的概念

数据库索引

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

1.2 索引的作用

• 数据库利用各种快速定位技术，能够大大加快查询速度
• 档表很大或者查询涉及到多个表时，可以成千上万倍的提高查询速度
• 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
• 通过创建唯一性索引保证数据库的唯一性
• 可以加快表与表之间的链接
• 在使用分组和排序时，可以大大减少分组和排序时间

1.3 索引的优缺点

优点

• 可以快速地找到所需要的资源

缺点

• 占用空间

1.4 索引的分类

普通索引

• 这是最基本的索引类型，而且它没有唯一性之类的限制

唯一性索引

• 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，允许为空值

主键

• 主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”
• 一个表只能有一个主键，且不允许为空值

全文索引

• MySQL从3.23.23版开始支持全文索引和全文检索
• 在 MySQL中全文索引的索引类型为 FULLTEXT
• 全文索引可以在 ARCHAR或者TEXT类型的列上创建

单列索引与多列索引（组合索引）

• 索引可以是单列上创建的索引，也可以是在多列上创建的索引
• 最左原则，从左往右依次执行

外键

• 主表中的外键是令一张表的主键

1.5 创建索引的原则依据

• 表的主键、外键必须有索引
• 数据量超过300行的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新太频繁地字段不适合创建索引
• 经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引
• 索引应该建在选择性高的字段上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

1.6 创建索引

• 根据企业需求选择了合适的索引之后，可使用 CREATE INDEX创建索引
• CREATE INDEX加上各个索引关键字便可创建各个类型的索引

1.6.1 普通索引

##create创建普通索引
CREATE INDEX indexName ON table_name (column_name);
##alter创建普通索引
ALTER table tableName ADD INDEX indexName(columnName);
##创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
##一张表同一列可以做多个索引

1.6.2 唯一索引

##create创建普通索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;
##alter创建普通索引
ALTER table mytable ADD UNIQUE [indexName] (username(length));
##创建表的时候直接指定
CREATE TABLE mytable( 
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

1.6.3 主键索引

##create创建普通索引
create index <索引的名字> on tablename(列的列表);
##alter创建普通索引
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
##创建表的时候直接指定
CREATE TABLE mytable( 
id int(4) not null  auto_increment, 
name varchar(10) not null, 
age int(3) not null, 
primary key (id)
);
##创建主键索引的方式有两种，创建表的时候指定主键索引和后期添加主键索引

1.6.4 全文索引

##create创建普通索引
create fulltext index <索引的名字> on tablename（列的列表）;
##alter创建普通索引
alter table user4 add fulltext index_content(content);
##创建表的时候直接指定
create table user4 (
id int(11) not null auto_increment,
tile char(255) character set utf8 collate utf8_general_ci not null,
content text character set utf8 collate utf8_general_ci not null,
primary key (`id`),
fulltext (content)
);

1.6.5组合索引

##创建组合索引
create table user3 (
name varchar(10) not null,
age int(3) not null,
sex tinyint(1) not null,
index user3(name,age,sex));
##查看组合索引，发现keyname统统是一样的

1.7 查看索引、删除索引

查看索引

show index from table_name;
show keys from table_name;

删除索引

drop index index_name on tablename;
alter table table_name drop index index_name;

1.8 表的文件分类

• 表的结构文件
• 表的数据文件
• 表的索引文件
• myisam不支持事务，但经常被访，被读取性能较好

二、事务（Transaction）

2.1 事务的概念

• 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
• 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
• 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
• 通过事务的整体性以保证数据的一致性
• 如果事务成功了一部分，一部分未成功，则执行回滚，回到事务的起点，重新开始操作

2.2 事务的特点

2.2.1 原子性（Atomicity）

• 事务是一个完整的操作，事务的各元素是不可分的（原子的）
• 事务中的所有元素必须作为一个整体提交或回滚
• 如果事务中的任何元素失败，则整个事务将失败

2.2.2 一致性（Consistency）

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

2.2.3隔离性（Isolation）

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

2.2.4 持久性（Durability）

• 事务持久性指不管系统是否发生故障，事务处理的结果都是永久的
• 一旦事务被提交，事务的效果会被永久地保留在数据库中

2.3 事务控制语句

默认情况下 MySQL的事务是自动提交的，当sql语句提交时事务便自动提交

2.3.1 控制事务的命令

• 使用事务处理命令控制事务
  bein或start trasnsaction：开始一个事务
  commit：提交一个事务
  rollback：回滚一个事务
  savepoint identifier：允许在事务中创建一个保存点，一个事务中可以有多个
  release savepoint indetifer：删除一个事务的保存点，当没有指定的保存点事，执行该语句会出现异常
  rollback to indetifer：把事务回滚到标记点
  set transaction：用来设置事务的隔离级别
• 使用set命令进行控制
  set autocommit=0：禁止自动提交
  set autocommit=1：开启自动提交
  注意
• rollback只能向前回滚，无法向后回滚
• commit提交后，事务结束，此时再次使用的rollback属于另一个新的事务，对于已经commit的事务没有任何作用

2.3.2 实验案例

mysql> begin;    '//开始事务'
mysql> insert into info (name,address,age) values ('wangwu','beijing',15);
mysql> select * from info;
mysql> savepoint a;  '//在上一条数据添加后 设置一个标记'
mysql> insert into info (name,address,age) values ('zhaoliu','beijing',16);
mysql> savepoint b;
mysql> select * from info;
mysql> rollback to a;  '//返回到标记时的那个状态，类似于快照'
mysql> select * from info;
mysql> rollback to b  '//因为上面已经回到a的状态了，当时还没有标记b，所以回滚不回去'
mysql> commit;	'//提交事务'   

三、存储引擎

3.1 概念介绍

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

目前 MySQL常用的两种存储引擎

• MyISAM
• InnoDB

MySQL存储引擎是 MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作（输出/输入操作)

使用特殊存储引擎的主要优点之一在于

• 仅需提供特殊应用所需的特性
• 数据库中的系统开销较小
• 具有更有效和更高的数据库性能

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

3.2 MyISAM

3.2.1 MyISAM介绍

• MyISAM不支持事务，也不支持外键
• 访问速度快
• 对事务完整性没有要求
• MyISAM在磁盘上存储成三个文件
  • .frm文件存储表定义
  • 数据文件的扩展名为.MYD(MYData)
  • 索引文件的扩展名是.MYl (MYIndex)
• 表级锁定形式，数据在更新时锁定整个表
• 数据库在读写过程中相互阻塞
  • 会在数据写入的过程阻塞用户数据的读取
  • 也会在数据读取的过程中阻塞用户的数据写入
• 可通过key_buffer_size来设置缓存索引，提高访问性能，减少磁盘I/O的压力
• 缓存只会缓存索引文件，不会缓存数据
• MyISAM存储引擎它不支持外键约束，只支持全文索引
• 数据单独写入或读取，速度过程较快且占用资源相对少
• MylAM支持的存储格式
  • 静态表
  • 动态表
  • 压缩表

3.2.2 MylSAM适用的生产场景举例

• 公司业务不需要事务的支持
• 单方面读取或写入数据比较多的业务
• MyISAM存储引擎数据读写都比较频繁场景不适合
• 使用读写并发访问相对较低的业务
• 数据修改相对较少的业务
• 对数据业务一致性要求不是非常高的业务
• 服务器硬件资源相对比较差

3.3 InnoDB

3.3.1 InnoDB介绍

• 支持事务：支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 读写阻塞与事务隔离级别相关
• 具有非常高效的缓存特性：能缓存索引，也能缓存数据
• 表与主键以簇的方式存储
• 支持分区、表空间，类似 oracle数据库
• 支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引
• 对硬件资源要求还是比较高的场合

3.3.2 InnoDB应用场景

• 业务需要事务的支持
• 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
• 业务数据更新较为频繁的场景
  如：论坛，微博等
• 业务数据一致性要求较高
  例如：银行业务
• 硬件设备内存较大，利用 Innodb较好的缓存能力来提高内存利用率，减少磁盘I/O的压力

3.4 企业选择存储引擎的依据

• 需要考虑每个存储引擎提供的核心功能及应用场景
• 支持的字段和数据类型
  • 所有引擎都支持通用的数据类型
  • 但不是所有的引擎都支持其它的字段类型，如二进制对象
• 锁定类型:不同的存储引擎支持不同级别的锁定
  • 表锁定
  • 行锁定
• 索引的支持
  • 建立索引在搜索和恢复数据库中的数据时能显著提高性能
  • 不同的存储引擎提供不同的制作索引的技术
  • 有些存储引擎根本不支持索引
• 事务处理的支持
  • 提高在向表中更新和插入信息期间的可靠性
  • 可根据企业业务是否要支持事务选择存储引擎

3.5 修改存储引擎

修改步骤

• 查看数据库可配置的存储引擎
• 查看表正在使用的存储引擎
• 配置存储引擎为所选择的类型

使用 show engines查看系统支持的存储引擎

查看表使用的存储引擎

方法1：show table status from库名 where name=表名；
方法2：show create table表名；'//常用'
例如：
MySQL > show table status from yibiao where name='wangermazi'\G 
##使用\G代替分号表示垂直显示结果

方法一

alter table 修改；
格式：
alter table table_name engine=引擎；
例如：
MySQL> alter table user_info engin=MyISAM;

方法二

修改my.cnf,指定默认存储引擎并重启服务
格式：
default-storage-engine=InnoDB
例如：
vim my.cnf
default-storage-engine=InnoDB

方法三

create table 创建表时指定存储引擎
格式：
create table 表名 （字段） engine=引擎
例如：
MySQL> create table yibiao（id int）engine=MyISAM;

方法四

Mysql_convert_table_format转化存储引擎
格式：
Mysql_convert_table_format-user=root--password=密码--sock=/tmp/mysql.sock-engine=引擎 库名 表名
例如：
yum install perl-DBI perl-DBD-MySQL -y
/usr/local/mysql/bin/bysql_convert_table_format --user=root --passwork='123' --sock=/tmp/mysql.sock auth