MySQL索引、事物与存储引擎

一：MySQL索引

1.1：索引的分类

在数据库表中， 对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQ L 索引的建立对千MySQL的高效运行是非常重要的。下面介绍几种常见的MySQ L 索引类型。
从物理存储的角度来划分，索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的， 而非聚簇索引就不一样了； 聚簇索引能提高多行检索的速度， 而非聚簇索引对千单行的检索更快。

1:普通索引

普通索引是最基本的索引， 它没有任何限制， 也是大多数情况下用到的索引。它有以下几种创建方式。
直接创建索引：

mysql>CREATE INDEX index_name ON table_name(column(length));

column 是指定要创建索引的列名。通常可以考虑将查询语句中在JOIN 子句和WHERE 子句里经常出现的列作为索引列。

其中length 是可选项。如果忽略length 的值， 则使用整个列的值作为索引。如果指定使用列前的length 个字符来创建索引， 就是使用列的一部分来创建索引， 这样有利千减小索引文件的大小， 节省索引列所占的空间。在某些情况下， 只能对列的前缀进行索引。索引列的长度有一个最大上限255 个字节( My lSAM 和lnnoDB 表的最大上限为1000 个字节） ， 如果索引列的长度超过了这个上限， 就只能用列的前缀进行索引。另外， BLOB 或TEXT 类型的列也必须使用前缀索引。column 和length 的含义， 在下面创建索引的操作语句中意义相同。

数字类型的列不能指定其长度，字符串的可以

修改表结构的方式添加索引：

mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));

2:唯一索引 

唯一索引与普通索引类似， 不同的就是： 唯一索引的索引列的值必须唯一， 但允许有空值（注意和主键不同）。如果是组合索引， 则列值的组合必须唯一。唯一索引创建方法和普
通索引类似。
创建唯一索引：

mysql>CREATE UNIQUE INDEX index_name ON table_name(column(length));

 修改表结构的时候添加唯一索引：

mysql>ALTER TABLE table_name ADD UNIQUE index_name (column(length));

 3:主键索引

主键索引是一种特殊的唯一索引， 一个表只能有一个主键， 不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE ·table_name、(
' id' int(11) NOT NULL AUTO_INCREMENT,
'title· char(255) NOT NULL ,
PRIMARY KEY ('id')
) ;

 4：组合索引（最左前缀）

平时用的SQL 查询语句一般都有比较多的限制条件， 所以为了进一步梓取MySQL 的效率， 就要考虑建立组合索引。在组合索引的创建中， 有两种场景， 即为单列索引和多列索引。

在一个user 用户表中， 有name , age, sex 三个字段， 分别分三次建立了INDEX 普通索引。那么在select* from user whe「e name= 11 AND age= 11 AND sex=” ；数据查询语句中就会分别检索三条索引， 虽然扫描效率有所提升， 但却还未达到最优。这个时候就需要使用到组合索引（ 即多列索引） ， 如下所示。

create table user(
name varchar(9),
age int(3),
sex tinyint(1),
index user(name, age, sex)
) ;

最左前缀原则是指在复合索引中，查询条件必须从索引的最左边字段开始，才能利用到索引。否则，索引 可能无法有效发挥作用，导致查询性能下降。

5:全文索引

MySQ L 从3 .2 3 .2 3 版开始支持全文索引和全文检索。在MySQ L5.6 版本以前FULLTEXT 索引仅可用千Myl SAM 表， 在5 . 6 之后innodb 引擎也支持FULLTEXT 索引；他们可以从CHAR 、VARC HAR 或TEXT 列中作为CREATE TABLE 语句的一部分被创建，或是随后使用ALTER TABLE 或C REATEINDEX 被添加。

对于较大的数据集， 将资料输入一个没有FULLTEXT 索引的表中，然后创建索引， 其速度比把资料输入现有FULLTEXT 索引的速度更快。不过切记对千大容显的数据表， 生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。
创建表的全文索引：

CREATE TABLE ·table· (
' id' int(11) NOT NULL AUTO_INCREMENT ,
' title· char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
·contenf text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
' time· int(10) NULL DEFAULT NULL ,
PRIMARY KEY(、i d' ) ,
FULL TEXT (content)
) ;

1.2：创建索引的原则依据

1：数据库建立索引的原则

• 确定针对该表的操作是大量的查询操作还是大量的增删改操作；
• 尝试建立索引来帮助特定的查询。检查自己的sql 语句， 为那些频繁在where 子句中出现的字段建立索引；
• 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间， 同时复合索引也占磁盘空间；
• 对于小型的表， 建立索引可能会影响性能；
• 应该避免对具有较少值的字段进行索引；
• 避免选择大型数据类型的列作为索引。

2：索引建立的原则

在经常用作过滤器的字段上建立索引；

• 在SQL 语句中经常进行GROUP BY 、ORDER BY 的字段上建立索引；
• 在不同值较少的字段上不必要建立索引， 如性别字段；
• 对于经常存取的列避免建立索引；
• 用于联接的列（主健／外健）上建立索引；
• 在经常存取的多个列上建立复合索引， 但要注意复合索引的建立顺序要按照使用的频度来确定；
• 缺省情况下建立的是非簇集索引， 但在以下情况下最好考虑簇集索引， 如： 含有限数目（ 不是很少）唯一的列； 进行大范围的查询； 充分的利用索引可以减少表扫描1/0 的次数， 有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决千OBA 所设计的数据库结构。

1.3：查看索引

有以下两种查看当前索引的方式。

mysql>show index from tablename;
mysql>show keys from tablename;

查询结果中各字段的含义如下：

• Tab le: 表的名称。
• N on_uniq ue: 如果索引不能包括重复词，则为0 ; 如果可以， 则为1 。
• Key_name : 索引的名称。
• Seq_in_index: 索引中的列序号， 从1 开始。
• Colu mn_name : 列名称。
• Collation: 列以什么方式存储在索引中。在MySQ L 中， 有值＇A' （升序）或NULL （无分类）
• Card inality： 索引中唯一值数目的估计值。通过运行ANALYZE TABLE 或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数， 所以即使对千小型表，该值也没有必要是精确的。基数越大， 当进行联合时， MySQ L 使用该索引的机会就越大。
• Sub_pa rt： 如果列只是被部分地编入索引， 则为被编入索引的字符的数目。如果整列被编入索引， 则为NULL 。
• Packed: 指示关键字如何被压缩。如果没有被压缩， 则为NULL 。
• Null: 如果列含有NULL , 则含有YES 。如果没有， 则该列含有NO 。
• lndex_type: 用过的索引方法( BTREE, FULLTEXT, HASH, RTREE ) 。
• Comment: 备注。

1.4:删除索引

索引在创建之后， 是会占用一定的磁盘空间的，因此表内如果有不再使用的索引， 从数据库性能方面考虑， 最好是删除无用索引。索引的删除有如下两种方法。

DROP INDEX 索引名ON 表名；
ALTER TABLE 表名DROP INDEX 索引名；

二：MySQL事务

MySQL 事务主要用于处理操作量大，复杂度商的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样， 这些数据库操作语句就构成一个事务

• 在MySQL 中只有使用了lnnodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性， 保证成批的SQ L 语句要么全部执行， 要么全部不执行
• 事务用来管理i nsert , update ,delete 语句。

一般来说事务是必须满足4 个条件( ACID ) : 原子性(Atomicity , 或称不可分割性）、一致性（ Consistency ) 、隔离性（Isolation , 又称独立性） 、持久性（Durability )

• 原子性： 一个事务( t「ansaction ) 中的所有操作， 要么全部完成， 要么全部不完成， 不
• 会结束在中间某个环节。事务在执行过程中发生错误， 会被回滚（ Rollback ) 到事务开
• 始前的状态， 就像这个事务从来没有执行过一样；
• 一致性： 在事务开始之前和事务结束以后， 数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则， 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；
• 隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力， 隔离性可以防止多个事务并发执行时由千交叉执行而导致数据的不一致。事务隔离分为不同级别， 包括读未提交（Read uncommitted ) 、读提交灯ead commi甘ed ) 、可重复读（repeatable read ) 和串行化( Serializable ) ;
• 待久性： 事务处理结束后， 对数据的修改就是永久的， 即便系统故障也不会丢失。

在MySQL 命令行的默认设置下， 事务都是自动提交的， 即执行SQ L 语句后就会马上执行COMMIT 操作。因此要显式地开启一个事务必须使用命令BEGIN 或START  TRANSACTION , 或者执行命令SET AUTOCOMMIT=O , 用来禁止使用当前会话的自动提交。

事务控制语句包含：

• BEGIN 或START TRANSACTION: 显式地开启一个事务；
• COMMIT: 也可以使用COMMIT WORK , 不过二者是等价的。COMMIT 会提交事务，并使己对数据库进行的所有修改变为永久性的；
• ROLLBACK: 又可以使用ROLLBACK WORK , 不过二者是等价的。回滚会结束用户的事务， 并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier: SAVEPOINT 允许在事务中创建一个保存点， 一个事务中可以有多SAVEPOINT:
• RELEASE SAVEPOINT ide ntifier : 删除一个事务的保存点， 当没有指定的保存点时，执行该语句会抛出一个异常；
• 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=O 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

三：MySQL 存储引擎

MySQL 默认配置了许多不同的存储引擎， 可以预先设置或者在MySQL 服务器中启用。选择适用千服务器、数据库和表格的存储引擎， 可以在存储信息、检索数据时， 提供最大的灵活性。

关系数据库表是用千存储和组织信息的数据结构， 可以将表理解为由行和列组成的表格， 类似于Excel 电子表格的形式。有的表简单、有的表复杂、有的表根本不用来存储任何长期数据、有的表读取时非常快， 但是插入数据时却很差。在实际开发过程中， 就可能需要各种各样的表， 不同的表就意味着存储不同类型的数据， 数据的处理上也会存在着差异。那么， 对千MySQ L 来说， 它提供了很多种类型的存储引擎（ 或者说不同的表类型） ， 根据对数据处理的需求， 可以选择不同的存储引擎， 从而最大限度的利用MySQ L 强大的功能。

在MySQ L 客户端中， 使用以下命令可以查看MySQL 支持的引擎。

mysql>show engines;

1：MylSAM 存储引擎

MylSAM 存储引擎不支持事务， 也不支待外键， 特点是访问速度快， 对事务完整性没有要求， 以SELECT 、INSERT 为主的应用基本都可以使用这个引擎来创建表。

每个MylSAM 表在磁盘上存储成3 个文件， 其中文件名和表名都相同， 但是扩展名分别为：

• frm（存储表定义）
• MYD(MYData , 存储数据）
• MYl(MYlndex, 存储索引）

数据文件和索引文件可以放置在不同的目录， 平均分配10 , 获取更快的速度。要指定数据文件和索引文件的路径， 需要在创建表的时候通过DATA DIRECTORY 和INDEX DIRECTORY 语句指定，文件路径需要使用绝对路径。

每个MylSAM 表都有一个标志， 服务器或myisamchk 程序在检查MylSAM 数据表时会对这个标志进行设置。MylSAM 表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器意外右机或崩溃， 这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行，可以在启动服务器时使用－－myisam-recover 实现。这会让服务器在每次打开一个MylSAM 数据表时自动检查数据表的标志并进行必要的修复处理。My lSAM 类型的表可能会损坏， 可以使用CHECK TABLE 语句来检查MylSAM 表的健康，并用REPAIR TABLE 语句修复一个损坏的My lSAM 表。

MylSAM 表还支持3 种不同的存储格式：

• 静态（ 固定长度）表
• 动态表
• 压缩表

其中静态表是默认的存储格式。静态表中的字段都是非可变字段， 这样每个记录都是固定长度的， 这种存储方式的优点是存储非常迅速， 容易缓存， 出现故障容易恢复； 缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格， 但是在访问的时候并不会得到这些空格， 这些空格在返回给应用之前已经去掉。同时需要注意： 在某些情况下可能需要返回字段后的空格， 而使用这种格式时后面的空格会被自动处理掉。
动态表包含可变字段， 记录不是固定长度的， 这样存储的优点是占用空间较少， 但是频繁的更新、删除记录会产生碎片， 需要定期执行OPTIMIZE TABLE 语句或myisamchk -r命令来改善性能， 并且出现故障的时候恢复相对比较困难。
压缩表由myisamchk 工具创建， 占据非常小的空间， 因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

2. lnnoDB 存储引擎

lnnoDB 是一个健壮的事务型存储引擎， 这种存储引擎已经被很多互联网公司使用， 为用户操作非常大的数据存储提供了一个强大的解决方案。MyS Q L 从5.5.5 版本开始， 默认的存储引擎为lnnoDB 。lnnoDB 存储引擎还引入了行级锁定和外键约束， 在以下场景中使用lnnoDB 存储引擎是最理想的选择：

• 更新密集的表： lnnoDB 存储引擎特别适合处理多重并发的更新请求。
• 事务： lnnoDB 存储引擎是支持事务的标准MySQ L 存储引擎。
• 自动灾难恢复与其它存储引擎不同， lnnoDB 表能够自动从灾难中恢复。
• 外键约束： MySQL 支持外键的存储引擎只有lnnoDB 。
• 支持自动增加列AUTO_INCREMENT 属性。

My lSAM 和lnnoDB 两种存储引擎的区别主要表现在以下几个方面:

• lnnoDB 支持事务， My lSAM 不支持， 这一点是非常重要的。事务是一种高级的处理方式， 如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原， 而MylSAM就不可以。
• MylSAM 适合查询、插入为主的应用， lnnoDB 适合频繁修改以及涉及到安全性较高的
• 应用。
• lnnoDB 支持外键， MylSAM 不支持。
• 从MySQ LS.5.5 以后， lnnoDB 是默认引擎。
• MyS Q L 从5.6 版本开始lnnoDB 引擎才支持FULLTEXT 类型的索引。
• lnnoDB 中不保存表的行数， 如select count(*) f「om table 时， lnnoDB 需要扫描一遍整个表来计算有多少行， 但是My lSAM 只要简单的读出保存好的行数即可。需要注意的是， 当count(*)语句包含where 条件时MylSAM 也需要扫描整个表。
• 对于自增长的字段， lnnoDB 中必须包含只有该字段的索引， 但是在MylSAM 表中可以和其他字段一起建立组合索引。
• 清空整个表时， lnnoDB 是一行一行的删除， 效率非常慢。My lSAM 则会重建表。
• lnnoDB 支持行锁（ 某些情况下还是锁整表， 如update table set a=1 where user like'%lee%'; )

3：修改默认的存储引擎

修改默认的存储引擎有四种方法， 分别如下。

( 1 ) 通过alter table 修改。

MySQL>alter table user_info engine=MylSAM;

 ( 2 ) 通过修改my.cnf, 指定默认存储引擎并重启服务。

[root@Mysql/]#vim my.cnf
default-storage-engine= I nnoDB

 ( 3 ) 通过create table 创建表时指定存储引擎。

MySQL>create table engineTest(id int) engine=MylSAM;

 ( 4 ) 通过Mysql_convert_table_format 转化存储引擎。

[ root@Mysql /]# yum -y install perl-DBI perl-DBD-MySQL
[ root@Mysql /]# /usr/loca l/mys ql/bi n/mysq l_convert_table_fo rmat
--user=root --password='123456'--sock=/tmp/mysql.sock auth